Bug #17895 IN-subselect from DUAL with impossible WHERE erroneously returns true
Submitted: 3 Mar 2006 12:41 Modified: 28 Jan 2016 11:01
Reporter: Aaron Crane Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.0.26-BK, 5.0.18, 5.1.43-bzr OS:Linux (Linux, Windows)
Assigned to: CPU Architecture:Any

[3 Mar 2006 12:41] Aaron Crane
Description:
Using 'IN (SELECT ... FROM DUAL WHERE (impossible))' seems to incorrectly match the impossibly-selected value from DUAL.

How to repeat:
mysql> select 17 from dual where false;
Empty set (0.00 sec)

mysql> select 23 in (select 17 from dual where false) AS found;
+-------+
| found |
+-------+
|     0 |
+-------+
1 row in set (0.00 sec)

mysql> select 17 in (select 17 from dual where false) AS found;
+-------+
| found |
+-------+
|     1 |
+-------+
1 row in set (0.00 sec)

I expected the result to be 0 for both of the queries with subselects.

Using a real table name instead of DUAL correctly yields 0 in both cases.
[3 Mar 2006 14:54] Valeriy Kravchuk
Thank you for a bug report. Verified just as described also on 5.0.20-BK.
[18 Apr 2006 16:24] Sergei Glukhov
Duplicated with bug#13496
[18 Apr 2006 17:41] Aaron Crane
This bug has been marked as a duplicate of bug#13496, which has been classified as a "Feature request" and "To be fixed later".

I find it extremely hard to think of this bug#17895 as a feature request.  Using 5.0.18 as before:

  mysql> select 17 from dual where false;
  Empty set (0.00 sec)

  mysql> select 23 from dual where false;
  Empty set (0.00 sec)

Those two queries produce empty result sets, as expected.

  mysql> select 23 in (select 17 from dual where false) as found;
  +-------+
  | found |
  +-------+
  |     0 |
  +-------+
  1 row in set (0.00 sec)

That query produces a false value, as expected.  But using a different empty query in the IN expression erroneously produces a true value:

  mysql> select 23 in (select 23 from dual where false) as found;
  +-------+
  | found |
  +-------+
  |     1 |
  +-------+
  1 row in set (0.00 sec)

I don't have an opinion on whether bug#17895 is a duplicate of bug#13496.  But it seems clear to me that this is a bug, not a feature request: empty subqueries can yield different results depending on the source of the empty set.
[20 Sep 2006 19:56] Michael Muryn
Hi, I was about to send a bug that both covered the bug #13496 and this bug #17895.

However even while I was describing what would have been a duplicate of #17895, I find the two issue quite different.

#13496 -- is about wheter or not dual contain item or not (and if count(*) should therefore return 1 row).

#17895 as to do with "in" and an empty result set coming from different source ("from a real table" or from "dual").  IMHO it doesn't matter if it comes from dual or from a real table... (maybe that would just suggest us to create our own dummy table, but we do not want this.)

Therefore I am against the identification of #17895 as a duplicate of #13496 as they are both different issue (even if or if not in the code they are "related").

We can go further and even think if the "select <values> where <conditions>" without a from should be an allowed syntax.  But that is another issue.
[21 Sep 2006 11:03] Valeriy Kravchuk
This is still a duplicate. The question is: is "dual" a real table with one row (as in Oracle), or "select expression from dual [where ...]" is the same as "select expression" (as it seems). Bug #13496 is a verified feature request to make it a real table (or make it behave as a real table) with one row. Adding that feature will fix the problem described here. So, it is a duplicate (as needs the same fix).
[21 Sep 2006 12:14] Aaron Crane
> This is still a duplicate. The question is: is "dual" a real table with
> one row (as in Oracle), or "select expression from dual [where ...]" is
> the same as "select expression" (as it seems). Bug #13496 is a verified
> feature request to make it a real table (or make it behave as a real
> table) with one row. Adding that feature will fix the problem described
> here. So, it is a duplicate (as needs the same fix)

I'm afraid I don't understand your reasoning.

From the user's point of view (that is, mine):

- Bug #13496 is a feature request about changing the behaviour of DUAL

- Bug #17895 is a bug report demonstrating that IN (SELECT 1 FROM DUAL WHERE FALSE) and IN (SELECT 1 FROM some_real_table WHERE FALSE) behave differently

The fact that implementing the feature desired by #13496 would also fix the bug described by #17895 seems irrelevant, from this point of view.

It makes no sense to me that the internals of MySQL should make the bug I've reported into a duplicate of a feature request.
[21 Sep 2006 15:36] Michael Muryn
Here is a similar problem described with some queries:
--
/*#1*/ select (select 1 from some_real_table where false) from dual; -- return "null"
/*#2*/ select (select 1 from dual where false) from dual; -- return 1 ** looks like a problem **
/*#3*/ select 1 from dual; -- return 1
/*#4*/ select 1 from dual where false; -- return no row
/*#5*/ select 1 where false; -- sql syntax error (we cannot leave the from clause unspecified at first glance)
--
maybe there is a reasoning I have not done with "dual" table (and I am never putting this aside, sometime there is just item's logic we don't get "obviously").

however, if we support the "where clause", should not it have an effect (subquery or not)?

The query #4 return no row... however #2 looks like it completely ignore the "where false" clause... it shouldn't return 1 logically (at less at first thought).

Also for the two bugs there, the question is not that "a fix will fix both problem", the question is "are they different problem" or really one is the cause of the other?

With my query #2 and #4 that somehow behave differently (one that return a result, one not, while it is the SAME QUERY) -- I would suppose there is a problem with dual and where clause in subquery, am I wrong?

The subqueries should behave the same as if they were used on an higher level...

------------

Here are some more queries I was about to submit (till I found that the bugs I wanted to report had already been submitted:

-- I also tested with a subquery with exists and not exists:

select 1 from dual where not exists (select 1 from dual where 1=0); -- return 1 (expected)
select 1 from dual where not exists (select 1 from dual where 1=1); -- return empty result set (expected)
select 1 from dual where exists (select 1 from dual where 1=0); -- return empty result set (expected)
select 1 from dual where exists (select 1 from dual where 1=1); -- return 1 (expected)

-- And now all the test case with "in" and "not in":

select 1 from dual where 1 in (select 1 from dual where 1=0); -- return 1 (unexpected)
select 1 from dual where 1 in (select 1 from dual where 1=1); -- return 1 (expected)
select 1 from dual where 1 not in (select 1 from dual where 1=0); -- return empty result set (unexpected)
select 1 from dual where 1 not in (select 1 from dual where 1=1); -- return empty result set (expected)

-- Conclusion?

When using "exists", it seems to take in consideration the where clause even with dual, as changing the where condition changes the result.

When doing the same exercice with "in", it seems to totally ignore the where clause when the subquery is done with dual.

------------

Like I said, it mights be a duplicate (and there may be some logical reason that make it duplicate even thought at first, by an user-point-of-view, it looks a bit different).  I will rethink about wheter or not that make sense... but because of my query #2 and #4, that leaves me wondering if they are not after-all different kind of problem.
[21 Sep 2006 15:59] Michael Muryn
Possible work-arounds for people wanting something "clean":

-----

1. That won't cover all the possible usage, but a simple work-around would be to translate:

"where x not in ()" to "where true"
"where x in ()" to "where false"

"in ()" is "in" with an empty resultset (no matter where it come from, an empty subquery or an user empty generated list of value)

of course if x is null, then this will be equivalent to "where null" (but that will give you the same "result set" in the end -- in the main query -- if I am not mistaken).

Of course that will work very well if your x is not nullable and if you use that subqueries in a where condition.

-----

2. Create your own "dummy" table. (I guess the reason to have "1 row" in it is to make sure we can "select something from it", else it will yield always an empty result set)

-----

I simply created "the dummy table" this way:

create table test (i int);

SELECT count(*) FROM test; -- 0
SELECT count(*) FROM dual; -- 0
SELECT count(*); -- 0

SELECT count(*) FROM test WHERE false; -- 0
SELECT count(*) FROM dual WHERE false; -- no result set
SELECT count(*) WHERE false; -- syntax error

And now running my "[not] exists" and "[not] in" test cases with the dummy table only (result with dual are available in my previous post):

-- with exists and not exists:

select 1 from test where not exists (select 1 from test where 1=0); -- return empty result set
select 1 from test where not exists (select 1 from test where 1=1); -- return empty result set
select 1 from test where exists (select 1 from test where 1=0); -- return empty result set
select 1 from test where exists (select 1 from test where 1=1); -- return empty result set

-- with "in" and "not in":

select 1 from test where 1 in (select 1 from test where 1=0); -- return empty result set
select 1 from test where 1 in (select 1 from test where 1=1); -- return empty result set
select 1 from test where 1 not in (select 1 from test where 1=0); -- return empty result set
select 1 from test where 1 not in (select 1 from test where 1=1); -- return empty result set

Now, if I insert 1 value into it, it [of course] changes the result:

insert into test values(1);

-- with exists and not exists:

select 1 from test where not exists (select 1 from test where 1=0); -- return 1 (expected)
select 1 from test where not exists (select 1 from test where 1=1); -- return empty result set (expected)
select 1 from test where exists (select 1 from test where 1=0); -- return empty result set (expected)
select 1 from test where exists (select 1 from test where 1=1); -- return 1 (expected)

-- with "in" and "not in":

select 1 from test where 1 in (select 1 from test where 1=0); -- return empty result set (expected)
select 1 from test where 1 in (select 1 from test where 1=1); -- return 1 (expected)
select 1 from test where 1 not in (select 1 from test where 1=0); -- return 1 (expected)
select 1 from test where 1 not in (select 1 from test where 1=1); -- return empty result set (expected)

You may want to look at bug #13496 for how to be more related to the oracle's behavior.

Personally, I only thought of this to get an "emtpy result set" and I have work-arounds idea.
Of course to get an empty result set, I could just create my own dummy table as suggested there and use it (or use an equivalent code translation).
[22 Sep 2006 7:38] Valeriy Kravchuk
I agree with you that we should have EXACTLY same behaviour with DUAL as in Oracle (where it is a real table with one row and one column), that is, as with similar real table. Othervise it is a bug (or improperly added feature). Period. Let the developers comment and decide.
[22 Sep 2006 15:09] Michael Muryn
Valeriy -- I did not have a so strong opinion about that to be honnest (since it is a new problem for me), however that is possibly the "right way".  There is probably a reason it has been done like this with oracle and if that is exactly what people need... then they can just create the table.

The reason there is problem is either:
- it works well but understanding why it doesn't work as expected in some case is not obvious [to me or like it seems to lot of people] (it is like some error we do because we do the wrong thinking]
- the implementation is not perfect, etc. and I am sure the developer will eventually take care of the problem (but in practice even if they fix it, it doesn't mean us user will have access to the fix right-away)
  - this is most likely the case because in some case it does take in consideration the where clause and in other it doesn't (and with my example queries, I probably figured out why they created a table with one row, see my previous explanation if it is not obvious yet)

Now for people that still want a work-around without creating a physical dummy table, here is another alternative:

-- work around
select 1 from dual where 1 not in (select 1 from (select 1) dummy where false); -- return 1
select 1 from dual where 1 not in (select 1 from (select 1) dummy where true); -- return no row

-- query to see the difference
select (select 1 from (select 1) dummy where false), -- return null
       (select 1 from (select 1) dummy where true),  -- return 1
       (select 1 from dual where false), -- return 1
       (select 1 from dual where true)   -- return 1
;

-----

Of course there is maybe other reason of all this behavior if it is not a bug, and like you said the mysql's developers will be best placed to judge of this (if not already done!)
[25 Sep 2006 14:04] Peter Gulutzan
Thanks for the clarifications. However:
Since there is a workaround, and since the problem
will disappear when MySQL handles DUAL differently,
I am marking it "to be fixed later".
[13 Jan 2016 0:11] Yosel Bart
Bug #13496 is marked as fixed, but I'm still seeing this bug on 5.5.46.
[28 Jan 2016 11:01] Gleb Shchepa
The bug has been fixed in 5.7.8.