Below is the list of changes that have just been committed into a local
5.0 repository of psergey. When psergey does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html
ChangeSet@stripped, 2006-12-13 15:05:27+03:00, sergefp@stripped +4 -0
BUG#24127: (a,b) IN (SELECT c,d ...) can produce wrong results if a and/or b are NULLs:
Post-review fixes:
- since index_subuquery now has HAVING clause, show it in EXPLAIN EXTENDED.
mysql-test/r/subselect.result@stripped, 2006-12-13 15:05:24+03:00, sergefp@stripped +5 -5
BUG#24127: (a,b) IN (SELECT c,d ...) can produce wrong results if a and/or b are NULLs:
Post-review fixes:
- since index_subuquery now has HAVING clause, show it in EXPLAIN EXTENDED.
mysql-test/r/subselect3.result@stripped, 2006-12-13 15:05:24+03:00, sergefp@stripped +12 -3
BUG#24127: (a,b) IN (SELECT c,d ...) can produce wrong results if a and/or b are NULLs:
Post-review fixes:
- since index_subuquery now has HAVING clause, show it in EXPLAIN EXTENDED.
mysql-test/t/subselect3.test@stripped, 2006-12-13 15:05:25+03:00, sergefp@stripped +4 -1
BUG#24127: (a,b) IN (SELECT c,d ...) can produce wrong results if a and/or b are NULLs:
Post-review fixes:
- since index_subuquery now has HAVING clause, show it in EXPLAIN EXTENDED.
sql/item_subselect.cc@stripped, 2006-12-13 15:05:25+03:00, sergefp@stripped +6 -1
BUG#24127: (a,b) IN (SELECT c,d ...) can produce wrong results if a and/or b are NULLs:
Post-review fixes:
- since index_subuquery now has HAVING clause, show it in EXPLAIN EXTENDED.
# This is a BitKeeper patch. What follows are the unified diffs for the
# set of deltas contained in the patch. The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User: sergefp
# Host: pylon.mylan
# Root: /home/psergey/mysql-5.0-bug8804-r10
--- 1.169/mysql-test/r/subselect.result 2006-12-13 15:05:32 +03:00
+++ 1.170/mysql-test/r/subselect.result 2006-12-13 15:05:32 +03:00
@@ -892,7 +892,7 @@
1 PRIMARY t1 index NULL PRIMARY 4 NULL 4 Using index
2 DEPENDENT SUBQUERY t2 index_subquery a a 5 func 2 Using index
Warnings:
-Note 1003 select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`a`) in t2 on a checking NULL))) AS `t1.a in (select t2.a from t2)` from `test`.`t1`
+Note 1003 select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`a`) in t2 on a checking NULL having <is_not_null_test>(`test`.`t2`.`a`)))) AS `t1.a in (select t2.a from t2)` from `test`.`t1`
CREATE TABLE t3 (a int(11) default '0');
INSERT INTO t3 VALUES (1),(2),(3);
SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1;
@@ -1464,25 +1464,25 @@
1 PRIMARY t1 index NULL s1 6 NULL 3 Using index
2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 Using index; Full scan on NULL key
Warnings:
-Note 1003 select `test`.`t1`.`s1` AS `s1`,not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL)))) AS `s1 NOT IN (SELECT s1 FROM t2)` from `test`.`t1`
+Note 1003 select `test`.`t1`.`s1` AS `s1`,not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL having trigcond(<is_not_null_test>(`test`.`t2`.`s1`)))))) AS `s1 NOT IN (SELECT s1 FROM t2)` from `test`.`t1`
explain extended select s1, s1 = ANY (SELECT s1 FROM t2) from t1;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 index NULL s1 6 NULL 3 Using index
2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 Using index; Full scan on NULL key
Warnings:
-Note 1003 select `test`.`t1`.`s1` AS `s1`,<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL))) AS `s1 = ANY (SELECT s1 FROM t2)` from `test`.`t1`
+Note 1003 select `test`.`t1`.`s1` AS `s1`,<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL having trigcond(<is_not_null_test>(`test`.`t2`.`s1`))))) AS `s1 = ANY (SELECT s1 FROM t2)` from `test`.`t1`
explain extended select s1, s1 <> ALL (SELECT s1 FROM t2) from t1;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 index NULL s1 6 NULL 3 Using index
2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 Using index; Full scan on NULL key
Warnings:
-Note 1003 select `test`.`t1`.`s1` AS `s1`,not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL)))) AS `s1 <> ALL (SELECT s1 FROM t2)` from `test`.`t1`
+Note 1003 select `test`.`t1`.`s1` AS `s1`,not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL having trigcond(<is_not_null_test>(`test`.`t2`.`s1`)))))) AS `s1 <> ALL (SELECT s1 FROM t2)` from `test`.`t1`
explain extended select s1, s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2') from t1;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 index NULL s1 6 NULL 3 Using index
2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 Using index; Using where; Full scan on NULL key
Warnings:
-Note 1003 select `test`.`t1`.`s1` AS `s1`,not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL where (`test`.`t2`.`s1` < _latin1'a2'))))) AS `s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2')` from `test`.`t1`
+Note 1003 select `test`.`t1`.`s1` AS `s1`,not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL where (`test`.`t2`.`s1` < _latin1'a2') having trigcond(<is_not_null_test>(`test`.`t2`.`s1`)))))) AS `s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2')` from `test`.`t1`
drop table t1,t2;
create table t2 (a int, b int);
create table t3 (a int);
--- 1.143/sql/item_subselect.cc 2006-12-13 15:05:32 +03:00
+++ 1.144/sql/item_subselect.cc 2006-12-13 15:05:32 +03:00
@@ -2261,10 +2261,15 @@
str->append(key_info->name);
if (check_null)
str->append(STRING_WITH_LEN(" checking NULL"));
- if (cond)
+ if (cond)
{
str->append(STRING_WITH_LEN(" where "));
cond->print(str);
+ }
+ if (having)
+ {
+ str->append(STRING_WITH_LEN(" having "));
+ having->print(str);
}
str->append(')');
}
--- 1.4/mysql-test/r/subselect3.result 2006-12-13 15:05:32 +03:00
+++ 1.5/mysql-test/r/subselect3.result 2006-12-13 15:05:32 +03:00
@@ -92,7 +92,7 @@
2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 2 Using where; Full scan on NULL key
Warnings:
Note 1276 Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1
-Note 1003 select `test`.`t2`.`oref` AS `oref`,`test`.`t2`.`a` AS `a`,<in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a checking NULL where (`test`.`t1`.`oref` = `test`.`t2`.`oref`)))) AS `Z` from `test`.`t2`
+Note 1003 select `test`.`t2`.`oref` AS `oref`,`test`.`t2`.`a` AS `a`,<in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a checking NULL where (`test`.`t1`.`oref` = `test`.`t2`.`oref`) having trigcond(<is_not_null_test>(`test`.`t1`.`a`))))) AS `Z` from `test`.`t2`
flush status;
select oref, a from t2 where a in (select a from t1 where oref=t2.oref);
oref a
@@ -241,7 +241,7 @@
2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 2 Using where; Full scan on NULL key
Warnings:
Note 1276 Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1
-Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`oref` AS `oref`,<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`b`),<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a checking NULL where ((`test`.`t1`.`c` = `test`.`t2`.`oref`) and trigcond(((<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`))) and trigcond(((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`b`) or isnull(`test`.`t1`.`b`))))))) AS `Z` from `test`.`t2`
+Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`oref` AS `oref`,<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`b`),<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a checking NULL where ((`test`.`t1`.`c` = `test`.`t2`.`oref`) and trigcond(((<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`))) and trigcond(((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`b`) or isnull(`test`.`t1`.`b`)))) having (trigcond(<is_not_null_test>(`test`.`t1`.`a`)) and trigcond(<is_not_null_test>(`test`.`t1`.`b`)))))) AS `Z` from `test`.`t2`
select a,b, oref, (a,b) in (select a,b from t1 where c=t2.oref) Z from t2;
a b oref Z
NULL 1 100 0
@@ -265,7 +265,7 @@
a b oref Z
NULL 1 100 0
NULL 2 100 NULL
-drop table t1,t2,t3;
+drop table t1,t2,t3,t4;
create table t1 (oref char(4), grp int, ie1 int, ie2 int);
insert into t1 (oref, grp, ie1, ie2) values
('aa', 10, 2, 1),
@@ -287,6 +287,7 @@
('bb', NULL, NULL),
('aa', 1, 1),
('dd', 1, NULL);
+alter table t1 add index idx(ie1,ie2);
select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2 where a=3 and b is null ;
oref a b Z
cc 3 NULL NULL
@@ -295,4 +296,12 @@
select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2 where a=10 and b=10;
oref a b Z
new1 10 10 NULL
+explain extended
+select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2 where a=10 and b=10;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 8 Using where
+2 DEPENDENT SUBQUERY t1 index_subquery idx idx 5 func 4 Using where; Full scan on NULL key
+Warnings:
+Note 1276 Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1
+Note 1003 select `test`.`t2`.`oref` AS `oref`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`b`),<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on idx checking NULL where ((`test`.`t1`.`oref` = `test`.`t2`.`oref`) and trigcond(((<cache>(`test`.`t2`.`a`) = `test`.`t1`.`ie1`) or isnull(`test`.`t1`.`ie1`))) and trigcond(((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`ie2`) or isnull(`test`.`t1`.`ie2`)))) having (trigcond(<is_not_null_test>(`test`.`t1`.`ie1`)) and trigcond(<is_not_null_test>(`test`.`t1`.`ie2`)))))) AS `Z` from `test`.`t2` where ((`test`.`t2`.`b` = 10) and (`test`.`t2`.`a` = 10))
drop table t1, t2;
--- 1.4/mysql-test/t/subselect3.test 2006-12-13 15:05:32 +03:00
+++ 1.5/mysql-test/t/subselect3.test 2006-12-13 15:05:32 +03:00
@@ -229,7 +229,7 @@
(a,b) in (select a,b from t1,t4 where c=t2.oref) Z
from t2;
-drop table t1,t2,t3;
+drop table t1,t2,t3,t4;
# More tests for tricky multi-column cases, where some of pushed-down
# equalities are used for index lookups and some arent.
@@ -256,12 +256,15 @@
('bb', NULL, NULL),
('aa', 1, 1),
('dd', 1, NULL);
+alter table t1 add index idx(ie1,ie2);
--cc 3 NULL NULL
select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2 where a=3 and b is null ;
insert into t2 values ('new1', 10,10);
insert into t1 values ('new1', 1234, 10, NULL);
-- new1, 10, 10, NULL,
+select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2 where a=10 and b=10;
+explain extended
select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2 where a=10 and b=10;
drop table t1, t2;
| Thread |
|---|
| • bk commit into 5.0 tree (sergefp:1.2306) BUG#24127 | Sergey Petrunia | 13 Dec |
