#At file:///home/tsk/mprog/src/5.3-subqueries/ based on
revid:tim...@askmonty.org-20100315195258-nhomb3anbb1tv3mi
2780 tim...@askmonty.org 2010-03-16
MWL#68: Subquery optimization: Efficient NOT IN execution with NULLs
Fix for the PBXT copy of subselect.test.
modified:
mysql-test/suite/pbxt/r/subselect.result
mysql-test/suite/pbxt/t/subselect.test
=== modified file 'mysql-test/suite/pbxt/r/subselect.result'
--- a/mysql-test/suite/pbxt/r/subselect.result 2010-02-23 09:22:02 +0000
+++ b/mysql-test/suite/pbxt/r/subselect.result 2010-03-15 22:41:30 +0000
@@ -876,6 +876,8 @@ select (select a+1) from t1;
4.5
NULL
drop table t1;
+set @save_optimizer_switch=@@optimizer_switch;
+set
@@optimizer_switch="partial_match_rowid_merge=off,partial_match_table_scan=off";
CREATE TABLE t1 (a int(11) NOT NULL default '0', PRIMARY KEY (a));
CREATE TABLE t2 (a int(11) default '0', INDEX (a));
INSERT INTO t1 VALUES (1),(2),(3),(4);
@@ -1771,6 +1773,7 @@ id select_type table type possible_keys
Warnings:
Note 1003 select `test`.`a`.`id` AS `id`,`test`.`a`.`text` AS
`text`,`test`.`b`.`id` AS `id`,`test`.`b`.`text` AS `text`,`test`.`c`.`id` AS
`id`,`test`.`c`.`text` AS `text` from `test`.`t1` `a` left join `test`.`t2` `b`
on(((`test`.`b`.`id` = `test`.`a`.`id`) or isnull(`test`.`b`.`id`))) join
`test`.`t1` `c` where (if(isnull(`test`.`b`.`id`),1000,`test`.`b`.`id`) =
`test`.`c`.`id`)
drop table t1,t2;
+set @@optimizer_swit...@save_optimizer_switch;
create table t1 (a int);
insert into t1 values (1);
explain select benchmark(1000, (select a from t1 where a=sha(rand())));
@@ -2750,6 +2753,8 @@ select * from (select max(fld) from t1)
max(fld)
1
drop table t1;
+set @save_optimizer_switch=@@optimizer_switch;
+set
@@optimizer_switch="partial_match_rowid_merge=off,partial_match_table_scan=off";
CREATE TABLE t1 (one int, two int, flag char(1));
CREATE TABLE t2 (one int, two int, flag char(1));
INSERT INTO t1 VALUES(1,2,'Y'),(2,3,'Y'),(3,4,'Y'),(5,6,'N'),(7,8,'N');
@@ -2834,6 +2839,7 @@ id select_type table type possible_keys
Warnings:
Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS
`two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select
`test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where
(`test`.`t2`.`flag` = '0') group by `test`.`t2`.`one`,`test`.`t2`.`two` having
(trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or
isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) =
`test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))) and
trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and
trigcond(<is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1`
DROP TABLE t1,t2;
+set @@optimizer_swit...@save_optimizer_switch;
CREATE TABLE t1 (a char(5), b char(5));
INSERT INTO t1 VALUES (NULL,'aaa'), ('aaa','aaa');
SELECT * FROM t1 WHERE (a,b) IN (('aaa','aaa'), ('aaa','bbb'));
@@ -3004,6 +3010,8 @@ field1 field2
1 1
1 3
DROP TABLE t1, t2;
+set @save_optimizer_switch=@@optimizer_switch;
+set
@@optimizer_switch="partial_match_rowid_merge=off,partial_match_table_scan=off";
CREATE TABLE t1(a int, INDEX (a));
INSERT INTO t1 VALUES (1), (3), (5), (7);
INSERT INTO t1 VALUES (NULL);
@@ -3019,6 +3027,7 @@ a a IN (SELECT a FROM t1)
2 NULL
3 1
DROP TABLE t1,t2;
+set @@optimizer_swit...@save_optimizer_switch;
CREATE TABLE t1 (a DATETIME);
INSERT INTO t1 VALUES ('1998-09-23'), ('2003-03-25');
CREATE TABLE t2 AS SELECT
=== modified file 'mysql-test/suite/pbxt/t/subselect.test'
--- a/mysql-test/suite/pbxt/t/subselect.test 2009-11-06 17:22:32 +0000
+++ b/mysql-test/suite/pbxt/t/subselect.test 2010-03-15 22:41:30 +0000
@@ -477,6 +477,9 @@ drop table t1;
# Null with keys
#
+set @save_optimizer_switch=@@optimizer_switch;
+set
@@optimizer_switch="partial_match_rowid_merge=off,partial_match_table_scan=off";
+
CREATE TABLE t1 (a int(11) NOT NULL default '0', PRIMARY KEY (a));
CREATE TABLE t2 (a int(11) default '0', INDEX (a));
INSERT INTO t1 VALUES (1),(2),(3),(4);
@@ -1121,6 +1124,8 @@ select * from t1 a left join t2 b on (a.
explain extended select * from t1 a left join t2 b on (a.id=b.id or b.id is
null) join t1 c on (if(isnull(b.id), 1000, b.id)=c.id);
drop table t1,t2;
+set @@optimizer_swit...@save_optimizer_switch;
+
#
# Static tables & rund() in subqueries
#
@@ -1784,6 +1789,9 @@ drop table t1;
# Bug #11867: queries with ROW(,elems>) IN (SELECT DISTINCT <cols> FROM ...)
#
+set @save_optimizer_switch=@@optimizer_switch;
+set
@@optimizer_switch="partial_match_rowid_merge=off,partial_match_table_scan=off";
+
CREATE TABLE t1 (one int, two int, flag char(1));
CREATE TABLE t2 (one int, two int, flag char(1));
INSERT INTO t1 VALUES(1,2,'Y'),(2,3,'Y'),(3,4,'Y'),(5,6,'N'),(7,8,'N');
@@ -1811,6 +1819,9 @@ explain extended SELECT one,two from t1
explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE
flag = '0' group by one,two) as 'test' from t1;
DROP TABLE t1,t2;
+set @@optimizer_swit...@save_optimizer_switch;
+
+
#
# Bug #12392: where cond with IN predicate for rows and NULL values in table
#
@@ -1972,6 +1983,9 @@ DROP TABLE t1, t2;
# with possible NULL values by index access from the outer query
#
+set @save_optimizer_switch=@@optimizer_switch;
+set
@@optimizer_switch="partial_match_rowid_merge=off,partial_match_table_scan=off";
+
CREATE TABLE t1(a int, INDEX (a));
INSERT INTO t1 VALUES (1), (3), (5), (7);
INSERT INTO t1 VALUES (NULL);
@@ -1984,6 +1998,8 @@ SELECT a, a IN (SELECT a FROM t1) FROM t
DROP TABLE t1,t2;
+set @@optimizer_swit...@save_optimizer_switch;
+
#
# Bug #11302: getObject() returns a String for a sub-query of type datetime
#
@@ -3096,6 +3112,7 @@ SELECT a,b FROM t1 WHERE b IN (SELECT a
DROP TABLE t1,t2;
+
#
# Bug #32400: Complex SELECT query returns correct result only on some
# occasions
# Bazaar merge directive format 2 (Bazaar 0.90)
# revision_id: tim...@askmonty.org-20100315224130-321rym1lsuwz2j5z
# target_branch: file:///home/tsk/mprog/src/5.3-subqueries/
# testament_sha1: 6ad93b8494362c5c17ad8c37f369282c36de4f5c
# timestamp: 2010-03-16 00:41:38 +0200
# source_branch: file:///home/tsk/mprog/src/5.3-mwl68/
# base_revision_id: tim...@askmonty.org-20100315195258-\
# nhomb3anbb1tv3mi
#
# Begin bundle
IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWe2bdJkAAu9fgEAQWGf//1Mn
zsC////wUAWMXs7CkZ3ejVbXW4KEkkJ6jJoaYamRojTSAMnqGGmmjQCVMkwCTFNpGgNAAAAAABIi
agJpqmIyZqZMgDQAZDQBoIqmk1P1TeiZIeUA0PU00A0AAaABJIEyAEGJExop+pPUGh5T1BtRo9J6
kzgsRO1XnTTc/AyYGcM7h8EITPTJk9oY6bY5Eka6X7ZtFm7XVhx551g79RVBShQo4F7jIYk232da
X6tdfGGek7TbdNz33DKyRaJpzYIh4vCMJJZrF7tYh3ZiDm9cfTqt3K9XbR2/fhPq6Yz+QHYxsIZL
Vnr4wtqtrG0Z5Ng/S6T8gUCiurjQjf13vFxWVQZZoT1WBgKWHGi7MdzTJ2bezAnJYL8kby2PEpfs
YC0YBrUgdFp1b3CbBQJ1KQMmIwFMNI9jSvFVE3biErz8FKlkQcvdNlmsWjJxAsgZSWiY2FiFUSQ1
uhUSmiOBGKNAj2VS9jheoyvrFIufaMZMECO552PxT09xaplOjjviTGPbM9gNAOIua4TH4Ifxrpw3
Rbg+pkgsrklICt1VAIyKMBzNiK0lbeMOYsrJQR8AGqkFcoF+t7wJDMSYlI5Eki4aUAJLKAuBkoEF
MtLMVV/ynBEdS3CDqvQZnztYPHRAlmZF9tBRbS4xRS9kwwMhc6W4OsbIgL8MDOK+pEMngUUOz4EM
iEpGIaDz64HA6nCeBrqYZTg8vdDWdg6zKh0FCBC/zunFmUKlhM/tKVxJIFNRbCPdjEIllAgISV9R
WWhiaZc50pRxK2OyMfsFmYc0kriDsVhOy8kSIDkuIrBs8hWhRK2jHpvjMFm+ZCDGiUepMUqYGybw
zLw53mLjRGwMrcChFXHGcQ2222xdHvPk0udnJ5ngdJBGAuIltnoe+MsCgVhbOtdOk2gqYn2i3ApX
skP8BBIKQWUeMyblwdGzc+f+r5SHex6+0XP68sE2acj5AuQnAn6cknikasaiSgLxGF9p0DxzERvr
pqKU50a2NI9DRi6aQS9HyVsqBtkLzXxJL8zQzMl+JeBf7niyXQXkyRcQ9LT5GBndgdZIgL0FIuq5
CS6mBfu7NjocAJtn5B6ECsIBiO2KCXMJHfabL/O7wIsNPiTJWwwMDQ7jHtXqBVB6yqNHAE4VGYcl
xss6896VIw0RKmFHRkYSWIqjr1BqLGJUDUVSQMg6oIj6y368AW32GzjqKjUYE8FmX9i4nQUKzW4N
22wEbUpHajJovuEyQhhM6lSp91wBtLmJgVplPd7geopkC6qZzVslzDgRS73Sz72YYLtKaaZdhDGq
48v7Cg5ICtPIDCKo3YBWIwoXbqcD9YKFyW0UqUcQs0R1AQasXYBkbAcrJgOjbqL3Xgqxw5EZwcNr
qT3G8woThhuUrmdIs48QYmfPiK5ivQoU73DnoBzt0VKJIxSNw7gMwzDGXAnwLBzztcgUotbplddo
DeL3FtepQx7MWIbFIAFd+7WQvwZdKtBMJJ6JbSA0V/TeYsnzEqATZy0fmDCrBRIsMzHeB/TyoZtT
rBd1BYpeBbrkIK7RPVS7UDGCFcHCpKSLX7mGbdFRuAoJKkBB1Qye4m9dqUiR5BMUbxJRBQv0C4LJ
O49ok43pJXmJkOBja69GIqMFm9PehND3ZL7/I7vCAZAoOArU4KZVb7UHYLDRuyficU4krd9gGGYV
dFznITM9yD4+f6NkB4gcaxASj30Nf/F3JFOFCQ7Zt0mQ
_______________________________________________
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help : https://help.launchpad.net/ListHelp
