1

We have some code that goes through a SQL generator to produce parameterized IN clauses of the form WHERE id IN (?, ?, ?, ...) AND ....

If the list is empty, the generator cannot issue IN () since that is invalid SQL. Instead it issues WHERE id IN (SELECT NULL WHERE FALSE) to provide the same semantics.

I would expect that MYSQL can reduce this to WHERE FALSE. Interestingly though, it fails to do this and actually produces a full table scan!

Example query (mytable is a simple test table I created with 50K rows id as the PK)

select *
from mytable
where id in (select null where false);

Plan: enter image description here

Interestingly, if I replace the subquery with one that hits an actual table, I get a much better plan:

Query:

select *
from mytable
where id in (select null from information_schema.tables where false);

Plan: enter image description here

My question is, is there some reason why it makes sense for MySQL to behave this way (e.g. some particular semantic of the first query?) is this a known bug?

UPDATE: I filed this with MySQL and they've acknowledged the oddity and accepted it as a feature request.

Improve this question
11
  • Why doesn't the generator just output NULL? What value is the sub query providing?
    – Martin Smith
    Commented May 24 at 21:45
  • @MartinSmith WHERE id IN NULL is not valid SQL. The way this particular generator works lets you write something like WHERE id IN @ids (similar to Dapper if you're familiar with the .NET stack), so the generator gets to generate the parenthetical list but can't opt out of the IN syntax. I'm not saying it is a perfect design; more just curious about the optimizer behavior and trying to motivate the reason behind these otherwise-odd queries :-)
    – ChaseMedallion
    Commented May 24 at 21:49
  • In parentheses it is perfectly valid. So WHERE id IN (NULL) like any other value rather than some convoluted sub query. Though it still doesn't give an "impossible WHERE"! In SQL Server both variants are recognized as contradictions and replaced with a constant scan dbfiddle.uk/pvrO7Jq0 - For MySQL maybe the transformation to an EXISTSquery referencing DUAL messes with the simplification logic when no table is referenced dbfiddle.uk/10AWFfC4
    – Martin Smith
    Commented May 24 at 23:17
  • 1
    But also the same difference exists if the manual rewrite is done for both variants. The one referencing DUAL still ends up less optimised than the one referencing the real table! dbfiddle.uk/Rbd9-JLt
    – Martin Smith
    Commented May 25 at 1:00
  • 1
    @Lennart-SlavaUkraini the IN (SELECT 1 WHERE FALSE) version produces the "bad" plan
    – ChaseMedallion
    Commented Jun 10 at 15:47

1 Answer 1

Reset to default
3

Tbe "generator" should remove the clause from the WHERE. If necessary, remove the WHERE. It should not stick some potentially inefficient thing in its place.

If you don't have control over the "generator", my condolences.

MySQL has always been "lean and mean". That is it implements enough to get by, but does not dwell on edge cases or speed, only on correctness.

Two decades ago (plus or minus), MySQL implemented these features:

  • ALTER TABLE via a simple "copy". A few years ago, they got around to optimizing most cases for speed.
  • PARTITION -- but remains mostly useless.
  • A LEFT JOIN that it is not used could be ignored. MySQL continues to perform the join, while MariaDB has added an Optimization to to ignore the clause. (Such can easily sneak in in a VIEW.)
  • IN ( SELECT... ) was first implemented in a terribly inefficient way. Much later some cases were optimized. I suggest your example was not caught.
Improve this answer
1
  • 1
    I filed it as a bug against MySQL and they've acknowledged/accepted!
    – ChaseMedallion
    Commented Jun 7 at 20:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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