2

I'm seeing a lot of where NULL IS NULL type conditions in code and I'm curious about the overhead that may be involved in this.

So given SELECT * FROM table WHERE field = 'TERM' AND (NULL IS NULL OR anotherField = NULL) on a table > 10+ million records. Would there be enough concern to enforce the removal of the NULL IS NULL condition or the difference is negligible?

Developers are using this syntax to write generic SQL that handles optional query parameters. Personally I feel it should be left off the query if the parameter is not provided as it seems redundant.

I've done a little bit of Googling on this and I can't seem to find the answer, maybe my search terms are off as it seems common.

  • what rdbms is this? – Edgar Allan Bayron May 15 '18 at 4:32
  • Optimizer in most RDBMSs detects and evaluates constants only once. WHERE something = 'TERM' AND NULL IS NULL => WHERE something = 'TERM' AND True => WHERE something = 'TERM'. I think You have no chance to detect the difference in execution time. – Akina May 15 '18 at 4:37
  • @DecafDb You put AND, not OR, between partial filter conditions. Is that right? – Akina May 15 '18 at 5:26
  • @Akina Yes sorry, I have updated the query. – DecafDb May 15 '18 at 7:54
  • @EdgarAllanBayron Postgresql – DecafDb May 15 '18 at 7:55
2

I've seen this before and I did a quick search to verify it. I'll tell you what I think you're looking just as a guess, from use-the-index-luke

SELECT first_name, last_name, subsidiary_id, employee_id
  FROM employees
  WHERE ( subsidiary_id    = :sub_id OR :sub_id IS NULL )
    AND ( employee_id      = :emp_id OR :emp_id IS NULL )
    AND ( UPPER(last_name) = :name   OR :name   IS NULL )

The query uses named bind variables for better readability. All possible filter expressions are statically coded in the statement. Whenever a filter isn't needed, you just use NULL instead of a search term: it disables the condition via the OR logic.

So I think you're looking at is dynamically generated SQL. You have

SELECT * FROM table WHERE something = 'TERM' AND NULL IS NULL

I don't think that's right, I think that's a placeholder or prepared statement that looks like this,

SELECT * FROM table WHERE ($1 = 'TERM' OR $1 IS NULL);

And ultimately that's whole statement gets left empty so it looks like,

SELECT * FROM table WHERE (null = 'TERM' OR null IS NULL);

That makes it so the conditional loses all selectivity and is pruned in query compilation. That's all background,

Would there be enough concern to enforce the removal of the NULL IS NULL condition or the difference is negligible?

Yes, the null IS NULL gets constant folded with true (in every database I know about), and both false OR true and true OR false gets simplified to true.

  • thanks @Evan Carrol. Yes sorry I've updated the example. So it's more like SELECT * FROM table WHERE field = 'TERM' AND (NULL IS NULL OR anotherField = NULL). My concern is that AND (NULL IS NULL OR anotherField = NULL) could be left off during the query string generation and possibly has undesired effects at reasonable scale. – DecafDb May 15 '18 at 7:59
  • There are no undesired side effects so long as you're not worried about the costs of query parsing. Clearly, more to parse = more time. But this is easily optimized where it matters and if most ORMs generate pretty shit code so this is the least of your concerns. – Evan Carroll May 15 '18 at 8:00
  • Oh yeah I stay the hell away from ORMs. I have always build the string myself and always left off anything redundant and kept queries specific and fairly atomic, which has always worked really well for me even up to quite a large scale. But this project is full these types of generic queries which could have 4-5 optional parameters like this. So it got me a little concerned. – DecafDb May 16 '18 at 0:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.