Mysql query returns NULLs on IS NOT NULL statement

Question and Answers
1

SELECT *
FROM results
WHERE (po_num IS NOT NULL AND po_num = ‘{{report_po.text}}’)
UNION
SELECT * FROM results WHERE
date_start IS NOT NULL AND date_start BETWEEN ‘{{report_start_date.formattedDate}}’ AND ‘{{report_end_date.formattedDate}}’;

This still returns NULLs, will only work if not in union or and OR statement.

2

Hey @Csoliva! Welcome to the Appsmith community. I think this question is also being discussed on Discord.

To debug this, can you turn off the prepared statements and check the evaluated value? Be sure to share the screen shot. I simply want to understand how the query is being triggered against the DB.

3

the prepared statements was off, the is the only way app smith allows IS in a statement

4

I want to see the evaluated value of the query. Also, try this - copy the evaluated query and try to run it. You’ll be able to figure out which part of the query is not working out.

5

SELECT *
FROM results
WHERE (po_num IS NOT NULL AND po_num = ‘21985’)
UNION ALL
SELECT * FROM results WHERE
date_start IS NOT NULL AND date_start BETWEEN ‘’ AND ‘’;

the date part works, it returns the correct number of records

6

Hey! So, as I conveyed over intercom about this as well. In this query the first part works as expected. Although, although because the second part doesn’t have any NOT NULL clause it collects all the results and unifies both queries, hence you were getting those results. If you want intersection of records where both the conditions are met, you should use JOIN.