| Bug #37822 | Correlated subquery with IN and IS UNKNOWN provides wrong result | ||
|---|---|---|---|
| Submitted: | 2 Jul 2008 20:00 | Modified: | 12 Mar 2009 18:31 |
| Reporter: | Roy Lyseng | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 5.0,5.1,6.0 | OS: | Any |
| Assigned to: | Sergey Petrunya | CPU Architecture: | Any |
[5 Jul 2008 9:04]
Sergey Petrunya
The bug affects 5.1 also:
mysql> select *
-> from t1n
-> where v in(select v
-> from t2n
-> where t1n.g=t2n.g) is unknown;
+----+------+------+------+
| id | g | v | s |
+----+------+------+------+
| 41 | 40 | NULL | l |
| 51 | 50 | NULL | l |
| 61 | 60 | NULL | l |
| 90 | 90 | NULL | l |
+----+------+------+------+
4 rows in set (0.00 sec)
mysql> select version();
+---------------------+
| version() |
+---------------------+
| 5.1.25-rc-debug-log |
+---------------------+
1 row in set (0.00 sec)
[17 Jul 2008 13:17]
Susanne Ebrecht
Verfied as described. It effects all main bzr trees.
[19 Jul 2008 13:26]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/50077 2708 Sergey Petrunia 2008-07-19 BUG#37822: Correlated subquery with IN and IS UNKNOWN provides wrong result - Make Item_in_optimizer::is_null() evaluate the subquery, not just return this->null_value
[28 Jan 2009 15:18]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/64327 2751 Sergey Petrunia 2009-01-28 BUG#37822: Correlated subquery with IN and IS UNKNOWN provides wrong result Item_in_optimizer::is_null() evaluated "NULL IN (SELECT ...)" to NULL regardless of whether subquery produced any records, this was a documented limitation. The limitation has been removed (see bugs 8804, 24085, 24127) now Item_in_optimizer::val_int() correctly handles all cases with NULLs. Make Item_in_optimizer::is_null() invoke val_int() to return correct values for "NULL IN (SELECT ...)".
[28 Jan 2009 19:18]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/64374 2751 Sergey Petrunia 2009-01-28 BUG#37822: Correlated subquery with IN and IS UNKNOWN provides wrong result Item_in_optimizer::is_null() evaluated "NULL IN (SELECT ...)" to NULL regardless of whether subquery produced any records, this was a documented limitation. The limitation has been removed (see bugs 8804, 24085, 24127) now Item_in_optimizer::val_int() correctly handles all cases with NULLs. Make Item_in_optimizer::is_null() invoke val_int() to return correct values for "NULL IN (SELECT ...)".
[29 Jan 2009 13:57]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/64495 2985 Georgi Kodinov 2009-01-29 [merge] fixed a non-updated test result of bug #37822 causing failures in test suite. modified: mysql-test/lib/mtr_report.pl mysql-test/r/subselect_no_opts.result === modified file 'mysql-test/lib/mtr_report.pl' --- a/mysql-test/lib/mtr_report.pl 2009-01-06 16:52:32 +0000 +++ b/mysql-test/lib/mtr_report.pl 2009-01-29 13:55:56 +0000 @@ -497,6 +497,11 @@ sub mtr_report_stats ($) { # this test is expected to print warnings ($testname eq 'main.innodb_bug39438') or + # Bug#39886, logs 'Table full' error message + (($testname eq 'main.almost_full' or + $testname eq 'main.myisam_data_pointer_size_func') and + (/The table '.*' is full/ + )) or # maria-recovery.test has warning about missing log file /File '.*maria_log.000.*' not found \(Errcode: 2\)/ or # and about marked-corrupted table === modified file 'mysql-test/r/subselect_no_opts.result' --- a/mysql-test/r/subselect_no_opts.result 2008-12-29 16:40:21 +0000 +++ b/mysql-test/r/subselect_no_opts.result 2009-01-29 13:55:56 +0000 @@ -4663,7 +4663,6 @@ SELECT t1.a, (SELECT 1 FROM t2 WHERE t2. a incorrect 1 1 DROP TABLE t1,t2,t3; -End of 5.1 tests. CREATE TABLE t1( a INT ); INSERT INTO t1 VALUES (1),(2); CREATE TABLE t2( a INT, b INT ); @@ -4925,6 +4924,43 @@ ERROR 42000: You have an error in your S SELECT * FROM t1 WHERE a IN ( SELECT 1 INTO @v ); ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @v )' at line 1 DROP TABLE t1, t2; +# +# BUG#37822: Correlated subquery with IN and IS UNKNOWN provides wrong result +# +create table t1(id integer primary key, g integer, v integer, s char(1)); +create table t2(id integer primary key, g integer, v integer, s char(1)); +insert into t1 values +(10, 10, 10, 'l'), +(20, 20, 20, 'l'), +(40, 40, 40, 'l'), +(41, 40, null, 'l'), +(50, 50, 50, 'l'), +(51, 50, null, 'l'), +(60, 60, 60, 'l'), +(61, 60, null, 'l'), +(70, 70, 70, 'l'), +(90, 90, null, 'l'); +insert into t2 values +(10, 10, 10, 'r'), +(30, 30, 30, 'r'), +(50, 50, 50, 'r'), +(60, 60, 60, 'r'), +(61, 60, null, 'r'), +(70, 70, 70, 'r'), +(71, 70, null, 'r'), +(80, 80, 80, 'r'), +(81, 80, null, 'r'), +(100,100,null, 'r'); +select * +from t1 +where v in(select v +from t2 +where t1.g=t2.g) is unknown; +id g v s +51 50 NULL l +61 60 NULL l +drop table t1, t2; +End of 5.1 tests. set optimizer_switch=''; show variables like 'optimizer_switch'; Variable_name Value -- MySQL Code Commits Mailing List For list archives: http://lists.mysql.com/commits To unsubscribe: http://lists.mysql.com/commits?unsub=commits@bugs.mysql.com
[3 Feb 2009 9:40]
Bugs System
Pushed into 5.1.32 (revid:joro@sun.com-20090203090549-gos3v4320vimrzg6) (version source revid:sergefp@mysql.com-20090128191827-dkgdesnqxuvdtc4i) (merge vers: 5.1.32) (pib:6)
[4 Feb 2009 11:15]
Bugs System
Pushed into 6.0.10-alpha (revid:kostja@sun.com-20090204104420-mw1i2u9lum4bxjo6) (version source revid:davi.arnaut@sun.com-20090129184519-mgp2vr1rna9nk8li) (merge vers: 6.0.10-alpha) (pib:6)
[16 Feb 2009 11:20]
Sergey Petrunya
Reference to the documented limitation: Item_in_optimizer::is_null() evaluated "NULL IN (SELECT ...)" to NULL regardless of whether subquery produced any records, this was a documented limitation. Mention of it in the manual is at the top of subquery-restrictions.html page: In MySQL 5.1 before 5.1.16, if you compare a NULL value to a subquery using ALL, ANY, or SOME, and the subquery returns an empty result, the comparison might evaluate to the non-standard result of NULL rather than to TRUE or FALSE.
[16 Feb 2009 12:09]
Sergey Petrunya
And this bug was about that we've fixed everything, except for the case where the subquery predicate is inside IS NULL. If one runs a query which a subquery: ... expr IN (SELECT ...) IS NULL ... then he'll get the old, pre-5.1.16 behavior, i.e. "NULL IN (SELECT ...)" will always evaluate to NULL. This fix was to make the subqueries inside IS NULL (and IS UNKNOWN, they are internally the same) produce correct results.
[17 Feb 2009 14:55]
Bugs System
Pushed into 5.1.32-ndb-6.3.23 (revid:tomas.ulin@sun.com-20090217131017-6u8qz1edkjfiobef) (version source revid:tomas.ulin@sun.com-20090203133556-9rclp06ol19bmzs4) (merge vers: 5.1.32-ndb-6.3.22) (pib:6)
[17 Feb 2009 16:43]
Bugs System
Pushed into 5.1.32-ndb-6.4.3 (revid:tomas.ulin@sun.com-20090217134419-5ha6xg4dpedrbmau) (version source revid:tomas.ulin@sun.com-20090203133556-9rclp06ol19bmzs4) (merge vers: 5.1.32-ndb-6.3.22) (pib:6)
[17 Feb 2009 18:19]
Bugs System
Pushed into 5.1.32-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090217134216-5699eq74ws4oxa0j) (version source revid:tomas.ulin@sun.com-20090202111723-1zzwax187rtls913) (merge vers: 5.1.32-ndb-6.2.17) (pib:6)
[12 Mar 2009 18:31]
Paul DuBois
Noted in 5.1.32, 6.0.10 changelogs. For comparison of NULL to a subquery result inside IS NULL, the comparison could evaluate to NULL rather than to TRUE or FALSE. This occurred for expressions such as: SELECT ... WHERE NULL IN (SELECT ...) IS NULL Also updated "restrictions" section.
Description: A correlated subquery with IN and IS UNKNOWN is expected to return 2 rows but returns 4. How to repeat: create table t1n(id integer primary key, g integer, v integer, s char(1)); create table t2n(id integer primary key, g integer, v integer, s char(1)); insert into t1n values(10, 10, 10, 'l'); insert into t2n values(10, 10, 10, 'r'); insert into t1n values(20, 20, 20, 'l'); insert into t2n values(30, 30, 30, 'r'); insert into t1n values(40, 40, 40, 'l'); insert into t1n values(41, 40, null, 'l'); insert into t1n values(50, 50, 50, 'l'); insert into t1n values(51, 50, null, 'l'); insert into t2n values(50, 50, 50, 'r'); insert into t1n values(60, 60, 60, 'l'); insert into t1n values(61, 60, null, 'l'); insert into t2n values(60, 60, 60, 'r'); insert into t2n values(61, 60, null, 'r'); insert into t1n values(70, 70, 70, 'l'); insert into t2n values(70, 70, 70, 'r'); insert into t2n values(71, 70, null, 'r'); insert into t2n values(80, 80, 80, 'r'); insert into t2n values(81, 80, null, 'r'); insert into t1n values(90, 90, null, 'l'); insert into t2n values(100,100,null, 'r'); select * from t1n where v in(select v from t2n where t1n.g=t2n.g) is unknown;