summaryrefslogtreecommitdiff
path: root/mysql-test/r
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/r')
-rw-r--r--mysql-test/r/derived_opt.result70
-rw-r--r--mysql-test/r/group_by.result11
-rw-r--r--mysql-test/r/information_schema.result6
-rw-r--r--mysql-test/r/innodb_ext_key.result20
-rw-r--r--mysql-test/r/limit_rows_examined.result4
-rw-r--r--mysql-test/r/order_by.result76
-rw-r--r--mysql-test/r/partition.result6
-rw-r--r--mysql-test/r/range_vs_index_merge.result147
-rw-r--r--mysql-test/r/range_vs_index_merge_innodb,innodb_plugin.rdiff13
-rw-r--r--mysql-test/r/range_vs_index_merge_innodb.result147
-rw-r--r--mysql-test/r/subselect.result114
-rw-r--r--mysql-test/r/subselect2.result99
-rw-r--r--mysql-test/r/subselect3.result2
-rw-r--r--mysql-test/r/subselect3_jcl6.result2
-rw-r--r--mysql-test/r/subselect_no_mat.result114
-rw-r--r--mysql-test/r/subselect_no_opts.result114
-rw-r--r--mysql-test/r/subselect_no_scache.result114
-rw-r--r--mysql-test/r/subselect_no_semijoin.result114
-rw-r--r--mysql-test/r/user_var.result32
19 files changed, 1188 insertions, 17 deletions
diff --git a/mysql-test/r/derived_opt.result b/mysql-test/r/derived_opt.result
index 11216a32e61..22e2ab8d676 100644
--- a/mysql-test/r/derived_opt.result
+++ b/mysql-test/r/derived_opt.result
@@ -282,4 +282,74 @@ CREATE TABLE t1 ( i INT );
INSERT INTO t1 VALUES ( (SELECT 1 FROM ( SELECT * FROM t1 ) as a) );
drop table t1;
set optimizer_switch=@save_optimizer_switch;
+#
+# MDEV-3801 Reproducible sub select join crash on 5.3.8 and 5.3.9
+#
+CREATE TABLE t1 (
+pk int(10) unsigned NOT NULL AUTO_INCREMENT,
+a char(2) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY a (a)
+) ENGINE=MyISAM;
+INSERT INTO t1 (a)
+VALUES (NULL),(NULL),(NULL),('AB'),(NULL),('CD'),(NULL),(NULL);
+INSERT INTO t1 SELECT NULL, a1.a FROM t1 a1, t1 a2, t1 a3, t1 a4, t1 a5;
+CREATE TABLE t2 (
+pk int(10) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL);
+INSERT INTO t2 SELECT NULL FROM t2 a1, t2 a2, t2 a3, t2 a4, t2 a5;
+CREATE TABLE t3 (
+pk int(10) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY
+) ENGINE=MyISAM;
+INSERT INTO t3 VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL);
+INSERT INTO t3 SELECT NULL FROM t3 a1, t3 a2, t3 a3, t3 a4, t3 a5;
+CREATE TABLE t4 (
+a char(2) NOT NULL DEFAULT '',
+PRIMARY KEY (a)
+) ENGINE=MyISAM;
+INSERT INTO t4 VALUES ('CD');
+set @@tmp_table_size=8192;
+EXPLAIN
+SELECT * FROM t3 AS tx JOIN t2 AS ty ON (tx.pk = ty.pk)
+WHERE
+tx.pk IN
+(SELECT *
+FROM (SELECT DISTINCT ta.pk
+FROM t3 AS ta
+JOIN t2 AS tb ON (ta.pk = tb.pk)
+JOIN t1 AS tc ON (tb.pk = tc.pk)
+JOIN t4 AS td ON tc.a = td.a) tu)
+limit 10;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL #
+1 PRIMARY tx eq_ref PRIMARY PRIMARY 4 tu.pk # Using index
+1 PRIMARY ty eq_ref PRIMARY PRIMARY 4 tu.pk # Using index
+2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL #
+3 DERIVED td system PRIMARY NULL NULL NULL # Using temporary
+3 DERIVED tc ref PRIMARY,a a 3 const #
+3 DERIVED ta eq_ref PRIMARY PRIMARY 4 test.tc.pk # Using index
+3 DERIVED tb eq_ref PRIMARY PRIMARY 4 test.tc.pk # Using index; Distinct
+SELECT * FROM t3 AS tX JOIN t2 AS tY ON (tX.pk = tY.pk)
+WHERE
+tX.pk IN
+(SELECT *
+FROM (SELECT DISTINCT tA.pk
+FROM t3 AS tA
+JOIN t2 AS tB ON (tA.pk = tB.pk)
+JOIN t1 AS tC ON (tB.pk = tC.pk)
+JOIN t4 AS tD ON tC.a = tD.a) tU)
+limit 10;
+pk pk
+6 6
+16 16
+24 24
+32 32
+40 40
+48 48
+56 56
+64 64
+72 72
+80 80
+drop table t1, t2, t3, t4;
set optimizer_switch=@exit_optimizer_switch;
diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result
index 222977e5106..9f942747594 100644
--- a/mysql-test/r/group_by.result
+++ b/mysql-test/r/group_by.result
@@ -2119,6 +2119,17 @@ FROM t2
GROUP BY 1;
a
DROP TABLE t1, t2;
+FLUSH STATUS;
+CREATE TABLE t1 (f1 INT, f2 decimal(20,1), f3 blob);
+INSERT INTO t1 values(11,NULL,'blob'),(11,NULL,'blob');
+SELECT f3, MIN(f2) FROM t1 GROUP BY f1 LIMIT 1;
+f3 MIN(f2)
+blob NULL
+DROP TABLE t1;
+the value below *must* be 1
+show status like 'Created_tmp_disk_tables';
+Variable_name Value
+Created_tmp_disk_tables 1
# End of 5.3 tests
#
# Bug#49771: Incorrect MIN (date) when minimum value is 0000-00-00
diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result
index fcb40dae4ff..9d993e723c2 100644
--- a/mysql-test/r/information_schema.result
+++ b/mysql-test/r/information_schema.result
@@ -1678,6 +1678,12 @@ SELECT length(CAST(b AS CHAR)) FROM ubig;
length(CAST(b AS CHAR))
20
DROP TABLE ubig;
+select 1 from information_schema.tables where table_schema=repeat('a', 2000);
+1
+grant usage on *.* to mysqltest_1@localhost;
+select 1 from information_schema.tables where table_schema=repeat('a', 2000);
+1
+drop user mysqltest_1@localhost;
End of 5.1 tests.
#
# Additional test for WL#3726 "DDL locking for all metadata objects"
diff --git a/mysql-test/r/innodb_ext_key.result b/mysql-test/r/innodb_ext_key.result
index d2fb29a023c..0b7b042a0b2 100644
--- a/mysql-test/r/innodb_ext_key.result
+++ b/mysql-test/r/innodb_ext_key.result
@@ -613,6 +613,26 @@ Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_deleted 0
Handler_read_rnd_next 0
+#
+# Bug mdev-3851: ref access used instead of expected eq_ref access
+# when extended_keys=on
+#
+create table t0 (a int);
+insert into t0 values (1), (2), (3), (4), (5);
+create index i_p_size on part(p_size);
+set optimizer_switch='extended_keys=on';
+explain
+select * from t0, part ignore index (primary)
+where p_partkey=t0.a and p_size=1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t0 ALL NULL NULL NULL NULL 5 Using where
+1 SIMPLE part eq_ref i_p_size i_p_size 9 const,dbt3_s001.t0.a 1
+select * from t0, part ignore index (primary)
+where p_partkey=t0.a and p_size=1;
+a p_partkey p_name p_mfgr p_brand p_type p_size p_container p_retailprice p_comment
+2 2 blush rosy metallic lemon navajo Manufacturer#1 Brand#13 LARGE BRUSHED BRASS 1 LG CASE 902 final platelets hang f
+drop table t0;
+drop index i_p_size on part;
DROP DATABASE dbt3_s001;
use test;
#
diff --git a/mysql-test/r/limit_rows_examined.result b/mysql-test/r/limit_rows_examined.result
index f4242f17a14..a51798a5883 100644
--- a/mysql-test/r/limit_rows_examined.result
+++ b/mysql-test/r/limit_rows_examined.result
@@ -318,7 +318,7 @@ LIMIT ROWS EXAMINED 9;
c1
bb
Warnings:
-Warning 1931 Query execution was interrupted. The query examined at least 10 rows, which exceeds LIMIT ROWS EXAMINED (9). The query result may be incomplete.
+Warning 1931 Query execution was interrupted. The query examined at least 12 rows, which exceeds LIMIT ROWS EXAMINED (9). The query result may be incomplete.
Same as above, without subquery cache
set @@optimizer_switch='subquery_cache=off';
select * from t1
@@ -347,7 +347,7 @@ LIMIT ROWS EXAMINED 5;
c1
bb
Warnings:
-Warning 1931 Query execution was interrupted. The query examined at least 6 rows, which exceeds LIMIT ROWS EXAMINED (5). The query result may be incomplete.
+Warning 1931 Query execution was interrupted. The query examined at least 7 rows, which exceeds LIMIT ROWS EXAMINED (5). The query result may be incomplete.
Subqueries with materialization
set @@optimizer_switch='semijoin=off,in_to_exists=off,materialization=on,subquery_cache=on';
explain
diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result
index 53fed519a15..94e7d5e757a 100644
--- a/mysql-test/r/order_by.result
+++ b/mysql-test/r/order_by.result
@@ -1945,3 +1945,79 @@ f0 f1 f2
set sort_buffer_size= @save_sort_buffer_size;
DROP TABLE t1;
End of 5.3 tests
+#
+# Bug 54599: discarded fast range scan for query with
+# GROUP BY + ORDER BY + LIMIT
+#
+create table t0 (a int);
+insert into t0 values (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);
+create table t1 (a int, b int, index idx1(a,b), index idx2(b,a));
+insert into t1
+select 1000*s4.a+100*s3.a+10*s2.a + s1.a, 1000*s4.a+100*s3.a+10*s2.a+s1.a
+from t0 s1, t0 s2, t0 s3, t0 s4;
+analyze table t1;
+explain
+select b, count(*) num_cnt from t1
+where a > 9750 group by b order by num_cnt;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range idx1 idx1 5 NULL 502 Using where; Using index; Using temporary; Using filesort
+flush status;
+select b, count(*) num_cnt from t1
+where a > 9750 group by b order by num_cnt;
+show status like '%Handler_read%';
+Variable_name Value
+Handler_read_first 0
+Handler_read_key 250
+Handler_read_last 0
+Handler_read_next 249
+Handler_read_prev 0
+Handler_read_rnd 249
+Handler_read_rnd_deleted 0
+Handler_read_rnd_next 250
+explain
+select b, count(*) num_cnt from t1
+where a > 9750 group by b order by num_cnt limit 1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range idx1 idx1 5 NULL 502 Using where; Using index; Using temporary; Using filesort
+flush status;
+select b, count(*) num_cnt from t1
+where a > 9750 group by b order by num_cnt limit 1;
+show status like '%Handler_read%';
+Variable_name Value
+Handler_read_first 0
+Handler_read_key 250
+Handler_read_last 0
+Handler_read_next 249
+Handler_read_prev 0
+Handler_read_rnd 1
+Handler_read_rnd_deleted 0
+Handler_read_rnd_next 250
+drop table t0, t1;
+#
+# LP bug #1002508 : the number of expected rows to be examined is off
+# (bug #13528826)
+#
+CREATE TABLE t1(a int PRIMARY KEY, b int) ENGINE=myisam;
+INSERT INTO t1 VALUES
+(5, 10), (2, 70), (7, 80), (6, 20), (1, 50), (9, 40), (8, 30), (3, 60);
+CREATE TABLE t2 (p int, a int, INDEX i_a(a)) ENGINE=myisam;
+INSERT INTO t2 VALUES
+(103, 7), (109, 3), (102, 3), (108, 1), (106, 3),
+(107, 7), (105, 1), (101, 3), (100, 7), (110, 1);
+EXPLAIN
+SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY t1.a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL PRIMARY 4 NULL 8 Using index
+1 SIMPLE t2 ref i_a i_a 5 test.t1.a 2 Using index
+EXPLAIN
+SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY t1.a LIMIT 8;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL PRIMARY 4 NULL 4 Using index
+1 SIMPLE t2 ref i_a i_a 5 test.t1.a 2 Using index
+EXPLAIN
+SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY t1.a LIMIT 100;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL PRIMARY 4 NULL 8 Using index
+1 SIMPLE t2 ref i_a i_a 5 test.t1.a 2 Using index
+DROP TABLE t1,t2;
+End of 5.5 tests
diff --git a/mysql-test/r/partition.result b/mysql-test/r/partition.result
index 40586b8d54b..86425825601 100644
--- a/mysql-test/r/partition.result
+++ b/mysql-test/r/partition.result
@@ -2394,6 +2394,12 @@ HAVING b > geomfromtext("")
);
1
DROP TABLE t1;
+
+MDEV-612 Valgrind error in ha_maria::check_if_incompatible_data
+
+CREATE TABLE t1 (a INT, b INT, KEY(a)) ENGINE=Aria PARTITION BY KEY(a) PARTITIONS 2;
+ALTER TABLE t1 ADD KEY (b);
+drop table t1;
End of 5.1 tests
#
# BUG#55385: UPDATE statement throws an error, but still updates
diff --git a/mysql-test/r/range_vs_index_merge.result b/mysql-test/r/range_vs_index_merge.result
index faaa6d2429e..cc8a345a2ff 100644
--- a/mysql-test/r/range_vs_index_merge.result
+++ b/mysql-test/r/range_vs_index_merge.result
@@ -1221,6 +1221,153 @@ Lugansk UKR 469000
Seattle USA 563374
Caracas VEN 1975294
set optimizer_switch=@save_optimizer_switch;
+#
+# Bug mdev-585: range vs index-merge with ORDER BY ... LIMIT n
+# (LP bug #637962)
+#
+DROP INDEX CountryPopulation ON City;
+DROP INDEX CountryName ON City;
+DROP INDEX CityName on City;
+CREATE INDEX Name ON City(Name);
+CREATE INDEX Population ON City(Population);
+EXPLAIN
+SELECT * FROM City
+WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H'))
+AND (Population >= 100000 AND Population < 120000);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE City index_merge Country,Name,Population Name,Country 35,3 NULL # Using sort_union(Name,Country); Using where
+FLUSH STATUS;
+SELECT * FROM City
+WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H'))
+AND (Population >= 100000 AND Population < 120000);
+ID Name Country Population
+384 Cabo Frio BRA 119503
+387 Camaragibe BRA 118968
+403 Catanduva BRA 107761
+412 Cachoeirinha BRA 103240
+508 Watford GBR 113080
+509 Ipswich GBR 114000
+510 Slough GBR 112000
+511 Exeter GBR 111000
+512 Cheltenham GBR 106000
+513 Gloucester GBR 107000
+514 Saint Helens GBR 106293
+515 Sutton Coldfield GBR 106001
+516 York GBR 104425
+517 Oldham GBR 103931
+518 Basildon GBR 100924
+519 Worthing GBR 100000
+635 Mallawi EGY 119283
+636 Bilbays EGY 113608
+637 Mit Ghamr EGY 101801
+638 al-Arish EGY 100447
+701 Tarragona ESP 113016
+702 Lleida (Lérida) ESP 112207
+703 Jaén ESP 109247
+704 Ourense (Orense) ESP 109120
+705 Mataró ESP 104095
+706 Algeciras ESP 103106
+707 Marbella ESP 101144
+759 Gonder ETH 112249
+869 Cabuyao PHL 106630
+870 Calapan PHL 105910
+873 Cauayan PHL 103952
+903 Serekunda GMB 102600
+909 Sohumi GEO 111700
+913 Tema GHA 109975
+914 Sekondi-Takoradi GHA 103653
+924 Villa Nueva GTM 101295
+1844 Cape Breton CAN 114733
+1847 Cambridge CAN 109186
+2406 Herakleion GRC 116178
+2407 Kallithea GRC 114233
+2408 Larisa GRC 113090
+2908 Cajamarca PER 108009
+3002 Besançon FRA 117733
+3003 Caen FRA 113987
+3004 Orléans FRA 113126
+3005 Mulhouse FRA 110359
+3006 Rouen FRA 106592
+3007 Boulogne-Billancourt FRA 106367
+3008 Perpignan FRA 105115
+3009 Nancy FRA 103605
+3411 Ceyhan TUR 102412
+3567 Carúpano VEN 119639
+3568 Catia La Mar VEN 117012
+3571 Calabozo VEN 107146
+3786 Cam Ranh VNM 114041
+3792 Tartu EST 101246
+4002 Carrollton USA 109576
+4027 Cape Coral USA 102286
+4032 Cambridge USA 101355
+SHOW STATUS LIKE 'Handler_read_%';
+Variable_name Value
+Handler_read_first 0
+Handler_read_key 2
+Handler_read_last 0
+Handler_read_next 385
+Handler_read_prev 0
+Handler_read_rnd 377
+Handler_read_rnd_deleted 0
+Handler_read_rnd_next 0
+EXPLAIN
+SELECT * FROM City
+WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H'))
+AND (Population >= 100000 AND Population < 120000)
+ORDER BY Population LIMIT 5;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE City range Country,Name,Population Population 4 NULL # Using where
+FLUSH STATUS;
+SELECT * FROM City
+WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H'))
+AND (Population >= 100000 AND Population < 120000)
+ORDER BY Population LIMIT 5;
+ID Name Country Population
+519 Worthing GBR 100000
+638 al-Arish EGY 100447
+518 Basildon GBR 100924
+707 Marbella ESP 101144
+3792 Tartu EST 101246
+SHOW STATUS LIKE 'Handler_read_%';
+Variable_name Value
+Handler_read_first 0
+Handler_read_key 1
+Handler_read_last 0
+Handler_read_next 59
+Handler_read_prev 0
+Handler_read_rnd 0
+Handler_read_rnd_deleted 0
+Handler_read_rnd_next 0
+set optimizer_switch='index_merge=off';
+EXPLAIN
+SELECT * FROM City
+WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H'))
+AND (Population >= 100000 AND Population < 120000)
+ORDER BY Population LIMIT 5;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE City range Country,Name,Population Population 4 NULL # Using index condition; Using where
+FLUSH STATUS;
+SELECT * FROM City
+WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H'))
+AND (Population >= 100000 AND Population < 120000)
+ORDER BY Population LIMIT 5;
+ID Name Country Population
+519 Worthing GBR 100000
+638 al-Arish EGY 100447
+518 Basildon GBR 100924
+707 Marbella ESP 101144
+3792 Tartu EST 101246
+SHOW STATUS LIKE 'Handler_read_%';
+Variable_name Value
+Handler_read_first 0
+Handler_read_key 1
+Handler_read_last 0
+Handler_read_next 59
+Handler_read_prev 0
+Handler_read_rnd 0
+Handler_read_rnd_deleted 0
+Handler_read_rnd_next 0
+set optimizer_switch=@save_optimizer_switch;
DROP DATABASE world;
use test;
CREATE TABLE t1 (
diff --git a/mysql-test/r/range_vs_index_merge_innodb,innodb_plugin.rdiff b/mysql-test/r/range_vs_index_merge_innodb,innodb_plugin.rdiff
index 5913434caae..ecae2c809c1 100644
--- a/mysql-test/r/range_vs_index_merge_innodb,innodb_plugin.rdiff
+++ b/mysql-test/r/range_vs_index_merge_innodb,innodb_plugin.rdiff
@@ -1,5 +1,5 @@
---- r/range_vs_index_merge_innodb.result 2012-03-24 17:12:02.124422000 +0100
-+++ r/range_vs_index_merge_innodb,innodb_plugin.reject 2012-03-24 18:00:13.647902620 +0100
+--- ./r/range_vs_index_merge_innodb.result 2012-11-21 19:35:14.000000000 +0100
++++ ./r/range_vs_index_merge_innodb,innodb_plugin.reject 2012-11-21 20:56:00.000000000 +0100
@@ -50,14 +50,14 @@
WHERE (Population >= 100000 OR Name LIKE 'P%') AND Country='CAN' OR
(Population < 100000 OR Name Like 'T%') AND Country='ARG';
@@ -269,3 +269,12 @@
SELECT Name, Country, Population FROM City WHERE
(Name='Manila' AND Country='PHL') OR
(Name='Addis Abeba' AND Country='ETH') OR
+@@ -1346,7 +1346,7 @@
+ AND (Population >= 100000 AND Population < 120000)
+ ORDER BY Population LIMIT 5;
+ id select_type table type possible_keys key key_len ref rows Extra
+-1 SIMPLE City range Country,Name,Population Population 4 NULL # Using index condition; Using where
++1 SIMPLE City range Country,Name,Population Population 4 NULL # Using where
+ FLUSH STATUS;
+ SELECT * FROM City
+ WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H'))
diff --git a/mysql-test/r/range_vs_index_merge_innodb.result b/mysql-test/r/range_vs_index_merge_innodb.result
index df3a2af0753..67e341192da 100644
--- a/mysql-test/r/range_vs_index_merge_innodb.result
+++ b/mysql-test/r/range_vs_index_merge_innodb.result
@@ -1222,6 +1222,153 @@ Lugansk UKR 469000
Seattle USA 563374
Caracas VEN 1975294
set optimizer_switch=@save_optimizer_switch;
+#
+# Bug mdev-585: range vs index-merge with ORDER BY ... LIMIT n
+# (LP bug #637962)
+#
+DROP INDEX CountryPopulation ON City;
+DROP INDEX CountryName ON City;
+DROP INDEX CityName on City;
+CREATE INDEX Name ON City(Name);
+CREATE INDEX Population ON City(Population);
+EXPLAIN
+SELECT * FROM City
+WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H'))
+AND (Population >= 100000 AND Population < 120000);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE City index_merge Country,Name,Population Name,Country 35,3 NULL # Using sort_union(Name,Country); Using where
+FLUSH STATUS;
+SELECT * FROM City
+WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H'))
+AND (Population >= 100000 AND Population < 120000);
+ID Name Country Population
+384 Cabo Frio BRA 119503
+387 Camaragibe BRA 118968
+403 Catanduva BRA 107761
+412 Cachoeirinha BRA 103240
+508 Watford GBR 113080
+509 Ipswich GBR 114000
+510 Slough GBR 112000
+511 Exeter GBR 111000
+512 Cheltenham GBR 106000
+513 Gloucester GBR 107000
+514 Saint Helens GBR 106293
+515 Sutton Coldfield GBR 106001
+516 York GBR 104425
+517 Oldham GBR 103931
+518 Basildon GBR 100924
+519 Worthing GBR 100000
+635 Mallawi EGY 119283
+636 Bilbays EGY 113608
+637 Mit Ghamr EGY 101801
+638 al-Arish EGY 100447
+701 Tarragona ESP 113016
+702 Lleida (Lérida) ESP 112207
+703 Jaén ESP 109247
+704 Ourense (Orense) ESP 109120
+705 Mataró ESP 104095
+706 Algeciras ESP 103106
+707 Marbella ESP 101144
+759 Gonder ETH 112249
+869 Cabuyao PHL 106630
+870 Calapan PHL 105910
+873 Cauayan PHL 103952
+903 Serekunda GMB 102600
+909 Sohumi GEO 111700
+913 Tema GHA 109975
+914 Sekondi-Takoradi GHA 103653
+924 Villa Nueva GTM 101295
+1844 Cape Breton CAN 114733
+1847 Cambridge CAN 109186
+2406 Herakleion GRC 116178
+2407 Kallithea GRC 114233
+2408 Larisa GRC 113090
+2908 Cajamarca PER 108009
+3002 Besançon FRA 117733
+3003 Caen FRA 113987
+3004 Orléans FRA 113126
+3005 Mulhouse FRA 110359
+3006 Rouen FRA 106592
+3007 Boulogne-Billancourt FRA 106367
+3008 Perpignan FRA 105115
+3009 Nancy FRA 103605
+3411 Ceyhan TUR 102412
+3567 Carúpano VEN 119639
+3568 Catia La Mar VEN 117012
+3571 Calabozo VEN 107146
+3786 Cam Ranh VNM 114041
+3792 Tartu EST 101246
+4002 Carrollton USA 109576
+4027 Cape Coral USA 102286
+4032 Cambridge USA 101355
+SHOW STATUS LIKE 'Handler_read_%';
+Variable_name Value
+Handler_read_first 0
+Handler_read_key 2
+Handler_read_last 0
+Handler_read_next 385
+Handler_read_prev 0
+Handler_read_rnd 377
+Handler_read_rnd_deleted 0
+Handler_read_rnd_next 0
+EXPLAIN
+SELECT * FROM City
+WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H'))
+AND (Population >= 100000 AND Population < 120000)
+ORDER BY Population LIMIT 5;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE City range Country,Name,Population Population 4 NULL # Using where
+FLUSH STATUS;
+SELECT * FROM City
+WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H'))
+AND (Population >= 100000 AND Population < 120000)
+ORDER BY Population LIMIT 5;
+ID Name Country Population
+519 Worthing GBR 100000
+638 al-Arish EGY 100447
+518 Basildon GBR 100924
+707 Marbella ESP 101144
+3792 Tartu EST 101246
+SHOW STATUS LIKE 'Handler_read_%';
+Variable_name Value
+Handler_read_first 0
+Handler_read_key 1
+Handler_read_last 0
+Handler_read_next 59
+Handler_read_prev 0
+Handler_read_rnd 0
+Handler_read_rnd_deleted 0
+Handler_read_rnd_next 0
+set optimizer_switch='index_merge=off';
+EXPLAIN
+SELECT * FROM City
+WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H'))
+AND (Population >= 100000 AND Population < 120000)
+ORDER BY Population LIMIT 5;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE City range Country,Name,Population Population 4 NULL # Using index condition; Using where
+FLUSH STATUS;
+SELECT * FROM City
+WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H'))
+AND (Population >= 100000 AND Population < 120000)
+ORDER BY Population LIMIT 5;
+ID Name Country Population
+519 Worthing GBR 100000
+638 al-Arish EGY 100447
+518 Basildon GBR 100924
+707 Marbella ESP 101144
+3792 Tartu EST 101246
+SHOW STATUS LIKE 'Handler_read_%';
+Variable_name Value
+Handler_read_first 0
+Handler_read_key 1
+Handler_read_last 0
+Handler_read_next 59
+Handler_read_prev 0
+Handler_read_rnd 0
+Handler_read_rnd_deleted 0
+Handler_read_rnd_next 0
+set optimizer_switch=@save_optimizer_switch;
DROP DATABASE world;
use test;
CREATE TABLE t1 (
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index a4bad836d1f..14347e9b899 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -6060,6 +6060,116 @@ WHERE (col_varchar_nokey, 'x') IN
col_int_nokey
1
DROP TABLE ot,it1,it2;
+#
+# MDEV-746
+# Bug#13651009 WRONG RESULT FROM DERIVED TABLE IF THE SUBQUERY
+# HAS AN EMPTY RESULT
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+col_time_key time NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_time_key (col_time_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=MyISAM;
+CREATE TABLE t2 (
+pk int NOT NULL AUTO_INCREMENT,
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+col_time_key time NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_time_key (col_time_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1,4,4,'00:00:00','b','b');
+SET @var2:=4, @var3:=8;
+
+Testcase without inner subquery
+EXPLAIN SELECT @var3:=12, sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR
+sq4_alias1.col_varchar_key = @var3;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+SELECT @var3:=12, sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR
+sq4_alias1.col_varchar_key = @var3;
+@var3:=12 pk col_int_nokey col_int_key col_time_key col_varchar_key col_varchar_nokey
+SELECT @var3;
+@var3
+8
+EXPLAIN SELECT * FROM ( SELECT @var3:=12, sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR
+sq4_alias1.col_varchar_key = @var3 ) AS alias3;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE <derived2> system NULL NULL NULL NULL 0 const row not found
+2 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table
+SELECT * FROM ( SELECT @var3:=12, sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR
+sq4_alias1.col_varchar_key = @var3 ) AS alias3;
+@var3:=12 pk col_int_nokey col_int_key col_time_key col_varchar_key col_varchar_nokey
+SELECT @var3;
+@var3
+8
+
+Testcase with inner subquery; crashed WL#6095
+SET @var3=8;
+EXPLAIN SELECT sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key , sq4_alias1.col_varchar_nokey)
+NOT IN
+(SELECT c_sq1_alias1.col_varchar_key AS c_sq1_field1,
+c_sq1_alias1.col_varchar_nokey AS c_sq1_field2
+FROM t2 AS c_sq1_alias1
+WHERE (c_sq1_alias1.col_int_nokey != @var2
+OR c_sq1_alias1.pk != @var3));
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+2 DEPENDENT SUBQUERY c_sq1_alias1 system PRIMARY NULL NULL NULL 1
+SELECT sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key , sq4_alias1.col_varchar_nokey)
+NOT IN
+(SELECT c_sq1_alias1.col_varchar_key AS c_sq1_field1,
+c_sq1_alias1.col_varchar_nokey AS c_sq1_field2
+FROM t2 AS c_sq1_alias1
+WHERE (c_sq1_alias1.col_int_nokey != @var2
+OR c_sq1_alias1.pk != @var3));
+pk col_int_nokey col_int_key col_time_key col_varchar_key col_varchar_nokey
+EXPLAIN SELECT * FROM ( SELECT sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key , sq4_alias1.col_varchar_nokey)
+NOT IN
+(SELECT c_sq1_alias1.col_varchar_key AS c_sq1_field1,
+c_sq1_alias1.col_varchar_nokey AS c_sq1_field2
+FROM t2 AS c_sq1_alias1
+WHERE (c_sq1_alias1.col_int_nokey != @var2
+OR c_sq1_alias1.pk != @var3)) ) AS alias3;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+3 DEPENDENT SUBQUERY c_sq1_alias1 system PRIMARY NULL NULL NULL 1
+SELECT * FROM ( SELECT sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key , sq4_alias1.col_varchar_nokey)
+NOT IN
+(SELECT c_sq1_alias1.col_varchar_key AS c_sq1_field1,
+c_sq1_alias1.col_varchar_nokey AS c_sq1_field2
+FROM t2 AS c_sq1_alias1
+WHERE (c_sq1_alias1.col_int_nokey != @var2
+OR c_sq1_alias1.pk != @var3)) ) AS alias3;
+pk col_int_nokey col_int_key col_time_key col_varchar_key col_varchar_nokey
+DROP TABLE t1,t2;
End of 5.2 tests
#
# BUG#779885: Crash in eliminate_item_equal with materialization=on in
@@ -6860,7 +6970,7 @@ INSERT INTO t2 VALUES (45),(17),(20);
EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
-2 SUBQUERY t1 index a a 5 NULL 1 Using where; Using index
+2 SUBQUERY t1 index a a 5 NULL 2 Using where; Using index
2 SUBQUERY t2 ref b b 5 test.t1.a 2 Using index
DROP TABLE t1,t2;
#
@@ -6894,6 +7004,6 @@ INSERT INTO t2 VALUES (45),(17),(20);
EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
-2 SUBQUERY t1 index a a 5 NULL 1 Using where; Using index
+2 SUBQUERY t1 index a a 5 NULL 2 Using where; Using index
2 SUBQUERY t2 ref b b 5 test.t1.a 2 Using index
DROP TABLE t1,t2;
diff --git a/mysql-test/r/subselect2.result b/mysql-test/r/subselect2.result
index 7eff7f949a8..4fd303dfd44 100644
--- a/mysql-test/r/subselect2.result
+++ b/mysql-test/r/subselect2.result
@@ -180,6 +180,32 @@ SET optimizer_switch=@tmp_optimizer_switch;
DROP VIEW v1;
DROP TABLE t1,t2,t3;
#
+# MDEV-536: LP:1050806 - different result for a query using subquery
+#
+DROP TABLE IF EXISTS `t1`;
+Warnings:
+Note 1051 Unknown table 't1'
+CREATE TABLE `t1` (
+`node_uid` bigint(20) unsigned DEFAULT NULL,
+`date` datetime DEFAULT NULL,
+`mirror_date` datetime DEFAULT NULL,
+KEY `date` (`date`)
+) ENGINE=MyISAM;
+INSERT INTO `t1` VALUES (2085,'2012-01-01 00:00:00','2013-01-01 00:00:00');
+INSERT INTO `t1` VALUES (2084,'2012-02-01 00:00:00','2013-01-01 00:00:00');
+INSERT INTO `t1` VALUES (2088,'2012-03-01 00:00:00','2013-01-01 00:00:00');
+SELECT * FROM (
+SELECT node_uid, date, mirror_date, @result := 0 AS result
+FROM t1
+WHERE date < '2012-12-12 12:12:12'
+ AND node_uid in (2085, 2084)
+ORDER BY mirror_date ASC
+) AS calculated_result;
+node_uid date mirror_date result
+2085 2012-01-01 00:00:00 2013-01-01 00:00:00 0
+2084 2012-02-01 00:00:00 2013-01-01 00:00:00 0
+DROP TABLE t1;
+#
# MDEV-567: Wrong result from a query with correlated subquery if ICP is allowed
#
CREATE TABLE t1 (a int, b int, INDEX idx(a));
@@ -197,5 +223,76 @@ a b
1 0
1 1
1 3
-DROP TABLE t1, t2, t3;
+set @tmp_mdev567=@@optimizer_switch;
+set optimizer_switch='mrr=off';
+SELECT * FROM t3
+WHERE a = (SELECT COUNT(DISTINCT t2.b) FROM t1, t2
+WHERE t1.a = t2.a AND t2.a BETWEEN 7 AND 9
+AND t3.b = t1.b
+GROUP BY t1.b);
+a b
+1 0
+1 1
+1 3
+DROP TABLE t1,t2,t3;
+set optimizer_switch=@tmp_mdev567;
+#
+# MDEV-614, also MDEV-536, also LP:1050806:
+# different result for a query using subquery between 5.5.25 and 5.5.27
+#
+CREATE TABLE `t1` (
+`node_uid` bigint(20) unsigned DEFAULT NULL,
+`date` datetime DEFAULT NULL,
+`mirror_date` datetime DEFAULT NULL,
+KEY `date` (`date`)
+) ENGINE=MyISAM;
+INSERT INTO `t1` VALUES (2085,'2012-01-01 00:00:00','2013-01-01 00:00:00');
+INSERT INTO `t1` VALUES (2084,'2012-02-01 00:00:00','2013-01-01 00:00:00');
+INSERT INTO `t1` VALUES (2088,'2012-03-01 00:00:00','2013-01-01 00:00:00');
+explain
+SELECT * FROM (
+SELECT node_uid, date, mirror_date, @result := 0 AS result
+FROM t1
+WHERE date < '2012-12-12 12:12:12'
+ AND node_uid in (2085, 2084)
+ORDER BY mirror_date ASC
+) AS calculated_result;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE <derived2> ALL NULL NULL NULL NULL 2
+2 DERIVED t1 range date date 9 NULL 2 Using index condition; Using where; Rowid-ordered scan; Using filesort
+SELECT * FROM (
+SELECT node_uid, date, mirror_date, @result := 0 AS result
+FROM t1
+WHERE date < '2012-12-12 12:12:12'
+ AND node_uid in (2085, 2084)
+ORDER BY mirror_date ASC
+) AS calculated_result;
+node_uid date mirror_date result
+2085 2012-01-01 00:00:00 2013-01-01 00:00:00 0
+2084 2012-02-01 00:00:00 2013-01-01 00:00:00 0
+set @tmp_mdev614=@@optimizer_switch;
+set optimizer_switch='mrr=off';
+explain
+SELECT * FROM (
+SELECT node_uid, date, mirror_date, @result := 0 AS result
+FROM t1
+WHERE date < '2012-12-12 12:12:12'
+ AND node_uid in (2085, 2084)
+ORDER BY mirror_date ASC
+) AS calculated_result;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE <derived2> ALL NULL NULL NULL NULL 2
+2 DERIVED t1 range date date 9 NULL 2 Using index condition; Using where; Using filesort
+SELECT * FROM (
+SELECT node_uid, date, mirror_date, @result := 0 AS result
+FROM t1
+WHERE date < '2012-12-12 12:12:12'
+ AND node_uid in (2085, 2084)
+ORDER BY mirror_date ASC
+) AS calculated_result;
+node_uid date mirror_date result
+2085 2012-01-01 00:00:00 2013-01-01 00:00:00 0
+2084 2012-02-01 00:00:00 2013-01-01 00:00:00 0
+set optimizer_switch=@tmp_mdev614;
+DROP TABLE t1;
set optimizer_switch=@subselect2_test_tmp;
diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result
index b33e7e113f2..049c4d14b1a 100644
--- a/mysql-test/r/subselect3.result
+++ b/mysql-test/r/subselect3.result
@@ -126,7 +126,7 @@ Handler_read_next 0
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_deleted 0
-Handler_read_rnd_next 50
+Handler_read_rnd_next 41
select 'No key lookups, seq reads: 29= 5 reads from t2 + 4 * 6 reads from t1.' Z;
Z
No key lookups, seq reads: 29= 5 reads from t2 + 4 * 6 reads from t1.
diff --git a/mysql-test/r/subselect3_jcl6.result b/mysql-test/r/subselect3_jcl6.result
index 4660cd60603..815a8985d44 100644
--- a/mysql-test/r/subselect3_jcl6.result
+++ b/mysql-test/r/subselect3_jcl6.result
@@ -136,7 +136,7 @@ Handler_read_next 0
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_deleted 0
-Handler_read_rnd_next 50
+Handler_read_rnd_next 41
select 'No key lookups, seq reads: 29= 5 reads from t2 + 4 * 6 reads from t1.' Z;
Z
No key lookups, seq reads: 29= 5 reads from t2 + 4 * 6 reads from t1.
diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result
index cb9847a0d99..c307a68f64d 100644
--- a/mysql-test/r/subselect_no_mat.result
+++ b/mysql-test/r/subselect_no_mat.result
@@ -6059,6 +6059,116 @@ WHERE (col_varchar_nokey, 'x') IN
col_int_nokey
1
DROP TABLE ot,it1,it2;
+#
+# MDEV-746
+# Bug#13651009 WRONG RESULT FROM DERIVED TABLE IF THE SUBQUERY
+# HAS AN EMPTY RESULT
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+col_time_key time NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_time_key (col_time_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=MyISAM;
+CREATE TABLE t2 (
+pk int NOT NULL AUTO_INCREMENT,
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+col_time_key time NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_time_key (col_time_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1,4,4,'00:00:00','b','b');
+SET @var2:=4, @var3:=8;
+
+Testcase without inner subquery
+EXPLAIN SELECT @var3:=12, sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR
+sq4_alias1.col_varchar_key = @var3;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+SELECT @var3:=12, sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR
+sq4_alias1.col_varchar_key = @var3;
+@var3:=12 pk col_int_nokey col_int_key col_time_key col_varchar_key col_varchar_nokey
+SELECT @var3;
+@var3
+8
+EXPLAIN SELECT * FROM ( SELECT @var3:=12, sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR
+sq4_alias1.col_varchar_key = @var3 ) AS alias3;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE <derived2> system NULL NULL NULL NULL 0 const row not found
+2 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table
+SELECT * FROM ( SELECT @var3:=12, sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR
+sq4_alias1.col_varchar_key = @var3 ) AS alias3;
+@var3:=12 pk col_int_nokey col_int_key col_time_key col_varchar_key col_varchar_nokey
+SELECT @var3;
+@var3
+8
+
+Testcase with inner subquery; crashed WL#6095
+SET @var3=8;
+EXPLAIN SELECT sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key , sq4_alias1.col_varchar_nokey)
+NOT IN
+(SELECT c_sq1_alias1.col_varchar_key AS c_sq1_field1,
+c_sq1_alias1.col_varchar_nokey AS c_sq1_field2
+FROM t2 AS c_sq1_alias1
+WHERE (c_sq1_alias1.col_int_nokey != @var2
+OR c_sq1_alias1.pk != @var3));
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+2 DEPENDENT SUBQUERY c_sq1_alias1 system PRIMARY NULL NULL NULL 1
+SELECT sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key , sq4_alias1.col_varchar_nokey)
+NOT IN
+(SELECT c_sq1_alias1.col_varchar_key AS c_sq1_field1,
+c_sq1_alias1.col_varchar_nokey AS c_sq1_field2
+FROM t2 AS c_sq1_alias1
+WHERE (c_sq1_alias1.col_int_nokey != @var2
+OR c_sq1_alias1.pk != @var3));
+pk col_int_nokey col_int_key col_time_key col_varchar_key col_varchar_nokey
+EXPLAIN SELECT * FROM ( SELECT sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key , sq4_alias1.col_varchar_nokey)
+NOT IN
+(SELECT c_sq1_alias1.col_varchar_key AS c_sq1_field1,
+c_sq1_alias1.col_varchar_nokey AS c_sq1_field2
+FROM t2 AS c_sq1_alias1
+WHERE (c_sq1_alias1.col_int_nokey != @var2
+OR c_sq1_alias1.pk != @var3)) ) AS alias3;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+3 DEPENDENT SUBQUERY c_sq1_alias1 system PRIMARY NULL NULL NULL 1
+SELECT * FROM ( SELECT sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key , sq4_alias1.col_varchar_nokey)
+NOT IN
+(SELECT c_sq1_alias1.col_varchar_key AS c_sq1_field1,
+c_sq1_alias1.col_varchar_nokey AS c_sq1_field2
+FROM t2 AS c_sq1_alias1
+WHERE (c_sq1_alias1.col_int_nokey != @var2
+OR c_sq1_alias1.pk != @var3)) ) AS alias3;
+pk col_int_nokey col_int_key col_time_key col_varchar_key col_varchar_nokey
+DROP TABLE t1,t2;
End of 5.2 tests
#
# BUG#779885: Crash in eliminate_item_equal with materialization=on in
@@ -6858,7 +6968,7 @@ INSERT INTO t2 VALUES (45),(17),(20);
EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
-2 SUBQUERY t1 index a a 5 NULL 1 Using where; Using index
+2 SUBQUERY t1 index a a 5 NULL 2 Using where; Using index
2 SUBQUERY t2 ref b b 5 test.t1.a 2 Using index
DROP TABLE t1,t2;
#
@@ -6891,7 +7001,7 @@ INSERT INTO t2 VALUES (45),(17),(20);
EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
-2 SUBQUERY t1 index a a 5 NULL 1 Using where; Using index
+2 SUBQUERY t1 index a a 5 NULL 2 Using where; Using index
2 SUBQUERY t2 ref b b 5 test.t1.a 2 Using index
DROP TABLE t1,t2;
set optimizer_switch=default;
diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result
index 63eeb816b38..d1590b0df51 100644
--- a/mysql-test/r/subselect_no_opts.result
+++ b/mysql-test/r/subselect_no_opts.result
@@ -6055,6 +6055,116 @@ WHERE (col_varchar_nokey, 'x') IN
col_int_nokey
1
DROP TABLE ot,it1,it2;
+#
+# MDEV-746
+# Bug#13651009 WRONG RESULT FROM DERIVED TABLE IF THE SUBQUERY
+# HAS AN EMPTY RESULT
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+col_time_key time NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_time_key (col_time_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=MyISAM;
+CREATE TABLE t2 (
+pk int NOT NULL AUTO_INCREMENT,
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+col_time_key time NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_time_key (col_time_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1,4,4,'00:00:00','b','b');
+SET @var2:=4, @var3:=8;
+
+Testcase without inner subquery
+EXPLAIN SELECT @var3:=12, sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR
+sq4_alias1.col_varchar_key = @var3;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+SELECT @var3:=12, sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR
+sq4_alias1.col_varchar_key = @var3;
+@var3:=12 pk col_int_nokey col_int_key col_time_key col_varchar_key col_varchar_nokey
+SELECT @var3;
+@var3
+8
+EXPLAIN SELECT * FROM ( SELECT @var3:=12, sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR
+sq4_alias1.col_varchar_key = @var3 ) AS alias3;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE <derived2> system NULL NULL NULL NULL 0 const row not found
+2 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table
+SELECT * FROM ( SELECT @var3:=12, sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR
+sq4_alias1.col_varchar_key = @var3 ) AS alias3;
+@var3:=12 pk col_int_nokey col_int_key col_time_key col_varchar_key col_varchar_nokey
+SELECT @var3;
+@var3
+8
+
+Testcase with inner subquery; crashed WL#6095
+SET @var3=8;
+EXPLAIN SELECT sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key , sq4_alias1.col_varchar_nokey)
+NOT IN
+(SELECT c_sq1_alias1.col_varchar_key AS c_sq1_field1,
+c_sq1_alias1.col_varchar_nokey AS c_sq1_field2
+FROM t2 AS c_sq1_alias1
+WHERE (c_sq1_alias1.col_int_nokey != @var2
+OR c_sq1_alias1.pk != @var3));
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+2 DEPENDENT SUBQUERY c_sq1_alias1 system PRIMARY NULL NULL NULL 1
+SELECT sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key , sq4_alias1.col_varchar_nokey)
+NOT IN
+(SELECT c_sq1_alias1.col_varchar_key AS c_sq1_field1,
+c_sq1_alias1.col_varchar_nokey AS c_sq1_field2
+FROM t2 AS c_sq1_alias1
+WHERE (c_sq1_alias1.col_int_nokey != @var2
+OR c_sq1_alias1.pk != @var3));
+pk col_int_nokey col_int_key col_time_key col_varchar_key col_varchar_nokey
+EXPLAIN SELECT * FROM ( SELECT sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key , sq4_alias1.col_varchar_nokey)
+NOT IN
+(SELECT c_sq1_alias1.col_varchar_key AS c_sq1_field1,
+c_sq1_alias1.col_varchar_nokey AS c_sq1_field2
+FROM t2 AS c_sq1_alias1
+WHERE (c_sq1_alias1.col_int_nokey != @var2
+OR c_sq1_alias1.pk != @var3)) ) AS alias3;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+3 DEPENDENT SUBQUERY c_sq1_alias1 system PRIMARY NULL NULL NULL 1
+SELECT * FROM ( SELECT sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key , sq4_alias1.col_varchar_nokey)
+NOT IN
+(SELECT c_sq1_alias1.col_varchar_key AS c_sq1_field1,
+c_sq1_alias1.col_varchar_nokey AS c_sq1_field2
+FROM t2 AS c_sq1_alias1
+WHERE (c_sq1_alias1.col_int_nokey != @var2
+OR c_sq1_alias1.pk != @var3)) ) AS alias3;
+pk col_int_nokey col_int_key col_time_key col_varchar_key col_varchar_nokey
+DROP TABLE t1,t2;
End of 5.2 tests
#
# BUG#779885: Crash in eliminate_item_equal with materialization=on in
@@ -6855,7 +6965,7 @@ INSERT INTO t2 VALUES (45),(17),(20);
EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
-2 SUBQUERY t1 index a a 5 NULL 1 Using where; Using index
+2 SUBQUERY t1 index a a 5 NULL 2 Using where; Using index
2 SUBQUERY t2 ref b b 5 test.t1.a 2 Using index
DROP TABLE t1,t2;
#
@@ -6889,7 +6999,7 @@ INSERT INTO t2 VALUES (45),(17),(20);
EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
-2 SUBQUERY t1 index a a 5 NULL 1 Using where; Using index
+2 SUBQUERY t1 index a a 5 NULL 2 Using where; Using index
2 SUBQUERY t2 ref b b 5 test.t1.a 2 Using index
DROP TABLE t1,t2;
set @optimizer_switch_for_subselect_test=null;
diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result
index 6fd21f8d0b0..b6b5572815a 100644
--- a/mysql-test/r/subselect_no_scache.result
+++ b/mysql-test/r/subselect_no_scache.result
@@ -6066,6 +6066,116 @@ WHERE (col_varchar_nokey, 'x') IN
col_int_nokey
1
DROP TABLE ot,it1,it2;
+#
+# MDEV-746
+# Bug#13651009 WRONG RESULT FROM DERIVED TABLE IF THE SUBQUERY
+# HAS AN EMPTY RESULT
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+col_time_key time NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_time_key (col_time_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=MyISAM;
+CREATE TABLE t2 (
+pk int NOT NULL AUTO_INCREMENT,
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+col_time_key time NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_time_key (col_time_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1,4,4,'00:00:00','b','b');
+SET @var2:=4, @var3:=8;
+
+Testcase without inner subquery
+EXPLAIN SELECT @var3:=12, sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR
+sq4_alias1.col_varchar_key = @var3;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+SELECT @var3:=12, sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR
+sq4_alias1.col_varchar_key = @var3;
+@var3:=12 pk col_int_nokey col_int_key col_time_key col_varchar_key col_varchar_nokey
+SELECT @var3;
+@var3
+8
+EXPLAIN SELECT * FROM ( SELECT @var3:=12, sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR
+sq4_alias1.col_varchar_key = @var3 ) AS alias3;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE <derived2> system NULL NULL NULL NULL 0 const row not found
+2 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table
+SELECT * FROM ( SELECT @var3:=12, sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR
+sq4_alias1.col_varchar_key = @var3 ) AS alias3;
+@var3:=12 pk col_int_nokey col_int_key col_time_key col_varchar_key col_varchar_nokey
+SELECT @var3;
+@var3
+8
+
+Testcase with inner subquery; crashed WL#6095
+SET @var3=8;
+EXPLAIN SELECT sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key , sq4_alias1.col_varchar_nokey)
+NOT IN
+(SELECT c_sq1_alias1.col_varchar_key AS c_sq1_field1,
+c_sq1_alias1.col_varchar_nokey AS c_sq1_field2
+FROM t2 AS c_sq1_alias1
+WHERE (c_sq1_alias1.col_int_nokey != @var2
+OR c_sq1_alias1.pk != @var3));
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+2 DEPENDENT SUBQUERY c_sq1_alias1 system PRIMARY NULL NULL NULL 1
+SELECT sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key , sq4_alias1.col_varchar_nokey)
+NOT IN
+(SELECT c_sq1_alias1.col_varchar_key AS c_sq1_field1,
+c_sq1_alias1.col_varchar_nokey AS c_sq1_field2
+FROM t2 AS c_sq1_alias1
+WHERE (c_sq1_alias1.col_int_nokey != @var2
+OR c_sq1_alias1.pk != @var3));
+pk col_int_nokey col_int_key col_time_key col_varchar_key col_varchar_nokey
+EXPLAIN SELECT * FROM ( SELECT sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key , sq4_alias1.col_varchar_nokey)
+NOT IN
+(SELECT c_sq1_alias1.col_varchar_key AS c_sq1_field1,
+c_sq1_alias1.col_varchar_nokey AS c_sq1_field2
+FROM t2 AS c_sq1_alias1
+WHERE (c_sq1_alias1.col_int_nokey != @var2
+OR c_sq1_alias1.pk != @var3)) ) AS alias3;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+3 DEPENDENT SUBQUERY c_sq1_alias1 system PRIMARY NULL NULL NULL 1
+SELECT * FROM ( SELECT sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key , sq4_alias1.col_varchar_nokey)
+NOT IN
+(SELECT c_sq1_alias1.col_varchar_key AS c_sq1_field1,
+c_sq1_alias1.col_varchar_nokey AS c_sq1_field2
+FROM t2 AS c_sq1_alias1
+WHERE (c_sq1_alias1.col_int_nokey != @var2
+OR c_sq1_alias1.pk != @var3)) ) AS alias3;
+pk col_int_nokey col_int_key col_time_key col_varchar_key col_varchar_nokey
+DROP TABLE t1,t2;
End of 5.2 tests
#
# BUG#779885: Crash in eliminate_item_equal with materialization=on in
@@ -6866,7 +6976,7 @@ INSERT INTO t2 VALUES (45),(17),(20);
EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
-2 SUBQUERY t1 index a a 5 NULL 1 Using where; Using index
+2 SUBQUERY t1 index a a 5 NULL 2 Using where; Using index
2 SUBQUERY t2 ref b b 5 test.t1.a 2 Using index
DROP TABLE t1,t2;
#
@@ -6900,7 +7010,7 @@ INSERT INTO t2 VALUES (45),(17),(20);
EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
-2 SUBQUERY t1 index a a 5 NULL 1 Using where; Using index
+2 SUBQUERY t1 index a a 5 NULL 2 Using where; Using index
2 SUBQUERY t2 ref b b 5 test.t1.a 2 Using index
DROP TABLE t1,t2;
set optimizer_switch=default;
diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result
index b924a18ca8f..34cdb17e23e 100644
--- a/mysql-test/r/subselect_no_semijoin.result
+++ b/mysql-test/r/subselect_no_semijoin.result
@@ -6055,6 +6055,116 @@ WHERE (col_varchar_nokey, 'x') IN
col_int_nokey
1
DROP TABLE ot,it1,it2;
+#
+# MDEV-746
+# Bug#13651009 WRONG RESULT FROM DERIVED TABLE IF THE SUBQUERY
+# HAS AN EMPTY RESULT
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+col_time_key time NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_time_key (col_time_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=MyISAM;
+CREATE TABLE t2 (
+pk int NOT NULL AUTO_INCREMENT,
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+col_time_key time NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_time_key (col_time_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1,4,4,'00:00:00','b','b');
+SET @var2:=4, @var3:=8;
+
+Testcase without inner subquery
+EXPLAIN SELECT @var3:=12, sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR
+sq4_alias1.col_varchar_key = @var3;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+SELECT @var3:=12, sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR
+sq4_alias1.col_varchar_key = @var3;
+@var3:=12 pk col_int_nokey col_int_key col_time_key col_varchar_key col_varchar_nokey
+SELECT @var3;
+@var3
+8
+EXPLAIN SELECT * FROM ( SELECT @var3:=12, sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR
+sq4_alias1.col_varchar_key = @var3 ) AS alias3;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE <derived2> system NULL NULL NULL NULL 0 const row not found
+2 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table
+SELECT * FROM ( SELECT @var3:=12, sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR
+sq4_alias1.col_varchar_key = @var3 ) AS alias3;
+@var3:=12 pk col_int_nokey col_int_key col_time_key col_varchar_key col_varchar_nokey
+SELECT @var3;
+@var3
+8
+
+Testcase with inner subquery; crashed WL#6095
+SET @var3=8;
+EXPLAIN SELECT sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key , sq4_alias1.col_varchar_nokey)
+NOT IN
+(SELECT c_sq1_alias1.col_varchar_key AS c_sq1_field1,
+c_sq1_alias1.col_varchar_nokey AS c_sq1_field2
+FROM t2 AS c_sq1_alias1
+WHERE (c_sq1_alias1.col_int_nokey != @var2
+OR c_sq1_alias1.pk != @var3));
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+2 DEPENDENT SUBQUERY c_sq1_alias1 system PRIMARY NULL NULL NULL 1
+SELECT sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key , sq4_alias1.col_varchar_nokey)
+NOT IN
+(SELECT c_sq1_alias1.col_varchar_key AS c_sq1_field1,
+c_sq1_alias1.col_varchar_nokey AS c_sq1_field2
+FROM t2 AS c_sq1_alias1
+WHERE (c_sq1_alias1.col_int_nokey != @var2
+OR c_sq1_alias1.pk != @var3));
+pk col_int_nokey col_int_key col_time_key col_varchar_key col_varchar_nokey
+EXPLAIN SELECT * FROM ( SELECT sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key , sq4_alias1.col_varchar_nokey)
+NOT IN
+(SELECT c_sq1_alias1.col_varchar_key AS c_sq1_field1,
+c_sq1_alias1.col_varchar_nokey AS c_sq1_field2
+FROM t2 AS c_sq1_alias1
+WHERE (c_sq1_alias1.col_int_nokey != @var2
+OR c_sq1_alias1.pk != @var3)) ) AS alias3;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+3 DEPENDENT SUBQUERY c_sq1_alias1 system PRIMARY NULL NULL NULL 1
+SELECT * FROM ( SELECT sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key , sq4_alias1.col_varchar_nokey)
+NOT IN
+(SELECT c_sq1_alias1.col_varchar_key AS c_sq1_field1,
+c_sq1_alias1.col_varchar_nokey AS c_sq1_field2
+FROM t2 AS c_sq1_alias1
+WHERE (c_sq1_alias1.col_int_nokey != @var2
+OR c_sq1_alias1.pk != @var3)) ) AS alias3;
+pk col_int_nokey col_int_key col_time_key col_varchar_key col_varchar_nokey
+DROP TABLE t1,t2;
End of 5.2 tests
#
# BUG#779885: Crash in eliminate_item_equal with materialization=on in
@@ -6855,7 +6965,7 @@ INSERT INTO t2 VALUES (45),(17),(20);
EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
-2 SUBQUERY t1 index a a 5 NULL 1 Using where; Using index
+2 SUBQUERY t1 index a a 5 NULL 2 Using where; Using index
2 SUBQUERY t2 ref b b 5 test.t1.a 2 Using index
DROP TABLE t1,t2;
#
@@ -6889,7 +6999,7 @@ INSERT INTO t2 VALUES (45),(17),(20);
EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
-2 SUBQUERY t1 index a a 5 NULL 1 Using where; Using index
+2 SUBQUERY t1 index a a 5 NULL 2 Using where; Using index
2 SUBQUERY t2 ref b b 5 test.t1.a 2 Using index
DROP TABLE t1,t2;
set @optimizer_switch_for_subselect_test=null;
diff --git a/mysql-test/r/user_var.result b/mysql-test/r/user_var.result
index e98dda46061..9c4fd02fcdd 100644
--- a/mysql-test/r/user_var.result
+++ b/mysql-test/r/user_var.result
@@ -498,4 +498,36 @@ DROP TABLE t1;
#
SET @bug12408412=1;
SELECT GROUP_CONCAT(@bug12408412 ORDER BY 1) INTO @bug12408412;
+CREATE TABLE t1(a INT);
+INSERT INTO t1 VALUES (0);
+SELECT DISTINCT POW(COUNT(*), @a:=(SELECT 1 FROM t1 LEFT JOIN t1 AS t2 ON @a))
+AS b FROM t1 GROUP BY a;
+b
+1
+SELECT @a;
+@a
+1
+DROP TABLE t1;
+CREATE TABLE t1(f1 INT, f2 INT);
+INSERT INTO t1 VALUES (1,2),(2,3),(3,1);
+CREATE TABLE t2(a INT);
+INSERT INTO t2 VALUES (1);
+SET @var=NULL;
+SELECT @var:=(SELECT f2 FROM t2 WHERE @var) FROM t1 GROUP BY f1 ORDER BY f2 DESC
+LIMIT 1;
+@var:=(SELECT f2 FROM t2 WHERE @var)
+NULL
+SELECT @var;
+@var
+NULL
+DROP TABLE t1, t2;
+CREATE TABLE t1(a INT);
+INSERT INTO t1 VALUES (0),(1),(3);
+SELECT DISTINCT POW(COUNT(distinct a), @a:=(SELECT 1 FROM t1 LEFT JOIN t1 AS t2 ON @a limit 1)) AS b FROM t1 GROUP BY a;
+b
+1
+SELECT @a;
+@a
+1
+DROP TABLE t1;
End of 5.5 tests
View Lorry Controller Status