diff options
Diffstat (limited to 'mysql-test/t')
68 files changed, 2932 insertions, 134 deletions
diff --git a/mysql-test/t/case.test b/mysql-test/t/case.test index e1c807fe32b..9d6cc7d375a 100644 --- a/mysql-test/t/case.test +++ b/mysql-test/t/case.test @@ -173,3 +173,12 @@ select t1.a, (case t1.a when 0 then 0 else t1.b end) d from t1 drop table t1, t2; --echo End of 5.0 tests + +# +# lp:839387 Assertion `(Item_result)i != TIME_RESULT' failed with CASE + datetime +# + +create table t1 (f1 time); +insert t1 values ('00:00:00'),('00:01:00'); +select case t1.f1 when '00:00:00' then 1 end from t1; +drop table t1; diff --git a/mysql-test/t/cast.test b/mysql-test/t/cast.test index 522007f6079..f5e2bc9ea57 100644 --- a/mysql-test/t/cast.test +++ b/mysql-test/t/cast.test @@ -37,7 +37,7 @@ select cast("2011-02-03 10:11:12.123456" as datetime(0)); select cast("2011-02-03 10:11:12.123456" as datetime(5)); select cast("2011-02-03 10:11:12.123456" as datetime(6)); select cast("2011-02-03 10:11:12" as datetime(6)); -select cast(cast(20010203101112.1 as double) as datetime(1)); +select cast(cast(20010203101112.5 as double) as datetime(1)); select cast(cast(010203101112.12 as double) as datetime(2)); select cast(cast(20010203101112.121314 as decimal(32,6)) as datetime(6)); select cast(20010203101112.121314 as datetime(6)); @@ -419,3 +419,8 @@ insert into t1 values ('11:22:33','2011-12-13','2011-12-13 11:22:33'); select cast(f1 as unsigned), cast(f2 as unsigned), cast(f3 as unsigned) from t1; drop table t1; +# +# CAST(... AS DATE) and invalid dates +# +SELECT CAST(TIME('10:20:30') AS DATE) + INTERVAL 1 DAY; + diff --git a/mysql-test/t/comments.test b/mysql-test/t/comments.test index 3a18a8bd483..08c74c99d0c 100644 --- a/mysql-test/t/comments.test +++ b/mysql-test/t/comments.test @@ -21,6 +21,17 @@ select 1 # The rest of the row will be ignored # End of 4.1 tests # +# Testing of MariaDB executable comments +# + +select 1 /*M! +1 */; +select 1 /*M!50000 +1 */; +select 1 /*M!50300 +1 */; +select 2 /*M!99999 +1 */; +--error ER_PARSE_ERROR +select 2 /*M!0000 +1 */; + +# # Bug#25411 (trigger code truncated) # diff --git a/mysql-test/t/derived_view.test b/mysql-test/t/derived_view.test index 72719ec9786..c1a9435ef6c 100644 --- a/mysql-test/t/derived_view.test +++ b/mysql-test/t/derived_view.test @@ -363,7 +363,9 @@ CREATE TABLE t2 (a varchar(1) , KEY (a)) ; INSERT INTO t2 VALUES ('c'), (NULL), ('r'); CREATE TABLE t3 (a varchar(1), b varchar(1)); -INSERT INTO t3 VALUES ('e', 'c'), ('c', 'c'), ('c', 'r'); +INSERT INTO t3 VALUES + ('e', 'c'), ('c', 'c'), ('c', 'r'), ('g', 'a'), ('b', 'x'), ('b', 'y'), + ('h', 'w'), ('d', 'z'), ('k', 'v'), ('j', 's'), ('m', 'p'), ('l', 'q'); CREATE VIEW v1 AS SELECT a, MIN(b) AS b FROM t3 GROUP BY a; @@ -599,7 +601,10 @@ INSERT INTO t1 VALUES (0); CREATE TABLE t2 (a varchar(32), b int, KEY (a)) ; INSERT INTO t2 VALUES ('j',28), ('c',29), ('i',26), ('c',29), ('k',27), - ('j',28), ('c',29), ('i',25), ('d',26), ('k',27); + ('j',28), ('c',29), ('i',25), ('d',26), ('k',27), + ('n',28), ('d',29), ('m',26), ('e',29), ('p',27), + ('w',28), ('x',29), ('y',25), ('z',26), ('s',27); + CREATE TABLE t3 (a varchar(32)); INSERT INTO t3 VALUES ('j'), ('c'); @@ -783,5 +788,478 @@ WHERE EXISTS (SELECT t2.a FROM t3 RIGHT JOIN t2 ON (t3.a = t2.a) DROP TABLE t1,t2,t3; +--echo # +--echo # LP bug #824463: nested outer join using a merged view +--echo # as an inner table +--echo # + +CREATE TABLE t1 (b int, a int) ; + +CREATE TABLE t2 (a int) ; +INSERT INTO t2 VALUES (5), (6); + +CREATE TABLE t3 (a int , c int) ; +INSERT INTO t3 VALUES (22,1), (23,-1); + +CREATE TABLE t4 (a int); + +CREATE TABLE t5 (d int) ; +INSERT INTO t5 VALUES (0), (7), (3), (5); + +CREATE VIEW v2 AS SELECT * FROM t2; +CREATE VIEW v3 AS SELECT * FROM t3; + +EXPLAIN EXTENDED +SELECT STRAIGHT_JOIN * + FROM ( t2 AS s2 + JOIN + ( t3 AS s3 + LEFT JOIN + ( t4 LEFT JOIN t3 ON t4.a != 0 ) + ON s3.a != 0) + ON s2.a != 0) + JOIN t5 ON s3.c != 0 AND t5.d = 0; +SELECT STRAIGHT_JOIN * + FROM ( t2 AS s2 + JOIN + ( t3 AS s3 + LEFT JOIN + ( t4 LEFT JOIN t3 ON t4.a != 0 ) + ON s3.a != 0) + ON s2.a != 0) + JOIN t5 ON s3.c != 0 AND t5.d = 0; + +EXPLAIN EXTENDED +SELECT STRAIGHT_JOIN * + FROM t2 AS s2 , t5, + (t3 LEFT JOIN (t4 LEFT JOIN t3 AS s3 ON t4.a != 0) ON t3.a != 0) + WHERE s2.a != 0 AND t3.c != 0 AND t5.d = 0; +SELECT STRAIGHT_JOIN * + FROM t2 AS s2 , t5, + (t3 LEFT JOIN (t4 LEFT JOIN t3 AS s3 ON t4.a != 0) ON t3.a != 0) + WHERE s2.a != 0 AND t3.c != 0 AND t5.d = 0; + +EXPLAIN EXTENDED +SELECT STRAIGHT_JOIN * + FROM v2 AS s2 , t5, + (t3 LEFT JOIN (t4 LEFT JOIN v3 AS s3 ON t4.a != 0) ON t3.a != 0) + WHERE s2.a != 0 AND t3.c != 0 AND t5.d = 0; +SELECT STRAIGHT_JOIN * + FROM v2 AS s2 , t5, + (t3 LEFT JOIN (t4 LEFT JOIN v3 AS s3 ON t4.a != 0) ON t3.a != 0) + WHERE s2.a != 0 AND t3.c != 0 AND t5.d = 0; + +SELECT STRAIGHT_JOIN * + FROM ( ( t2 AS s2 + LEFT JOIN + ( t3 AS s3 + LEFT JOIN + ( t4 AS s4 JOIN t3 ON s4.a != 0) + ON s3.a != 0 ) + ON s2.a != 0) + LEFT JOIN + t1 AS s1 + ON s1.a != 0) + JOIN t5 ON s3.c != 0; +SELECT STRAIGHT_JOIN * + FROM ( ( v2 AS s2 + LEFT JOIN + ( v3 AS s3 + LEFT JOIN + ( t4 AS s4 JOIN v3 ON s4.a != 0) + ON s3.a != 0 ) + ON s2.a != 0) + LEFT JOIN + t1 AS s1 + ON s1.a != 0) + JOIN t5 ON s3.c != 0; + +DROP VIEW v2,v3; +DROP TABLE t1,t2,t3,t4,t5; + +--echo # +--echo # LP bug #872735: derived used in a NOT IN subquery +--echo # + +CREATE TABLE t1 (b int NOT NULL); +INSERT INTO t1 VALUES (9), (7); + +CREATE TABLE t2 (a int NOT NULL) ; +INSERT INTO t2 VALUES (1), (2); + +CREATE TABLE t3 ( + a int NOT NULL , c int NOT NULL, d varchar(1) NOT NULL, + KEY (c,a) , PRIMARY KEY (a) +); +INSERT INTO t3 VALUES + (14,4,'a'), (15,7,'b'), (16,4,'c'), (17,1,'d'), (18,9,'e'), + (19,4,'f'), (20,8,'g'); + +SET SESSION optimizer_switch='derived_merge=on,subquery_cache=off'; + +--echo # The following two EXPLAINs must return the same execution plan +EXPLAIN +SELECT * FROM t1 , t2 + WHERE (t2.a ,t1.b) NOT IN (SELECT DISTINCT c,a FROM t3 t); +EXPLAIN +SELECT * FROM t1 , t2 + WHERE (t2.a ,t1.b) NOT IN (SELECT DISTINCT c,a FROM (SELECT * FROM t3) t); + +SELECT * FROM t1 , t2 + WHERE (t2.a ,t1.b) NOT IN (SELECT DISTINCT c,a FROM (SELECT * FROM t3) t); + +DROP TABLE t1,t2,t3; + +--echo # +--echo # LP bug #874006: materialized view used in IN subquery +--echo # + +CREATE TABLE t3 (a int NOT NULL, b varchar(1), c varchar(1)); +INSERT INTO t3 VALUES (19,NULL,NULL), (20,'r','r'); + +CREATE TABLE t1 (a int, b varchar(1) , c varchar(1)); +INSERT INTO t1 VALUES (1,NULL,NULL), (5,'r','r'), (7,'y','y'); + +CREATE TABLE t2 (a int NOT NULL , b int, c varchar(1)); +INSERT INTO t2 VALUES (4,3,'r'); + +CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1; + +SET SESSION optimizer_switch='derived_with_keys=off'; +EXPLAIN +SELECT * FROM t3 + WHERE t3.b IN (SELECT v1.b FROM v1, t2 + WHERE t2.c = v1.c AND t2.c = v1.b AND v1.b = t3.c); +SELECT * FROM t3 + WHERE t3.b IN (SELECT v1.b FROM v1, t2 + WHERE t2.c = v1.c AND t2.c = v1.b AND v1.b = t3.c); + +SET SESSION optimizer_switch='derived_with_keys=on'; +EXPLAIN +SELECT * FROM t3 + WHERE t3.b IN (SELECT v1.b FROM v1, t2 + WHERE t2.c = v1.c AND t2.c = v1.b AND v1.b = t3.c); +SELECT * FROM t3 + WHERE t3.b IN (SELECT v1.b FROM v1, t2 + WHERE t2.c = v1.c AND t2.c = v1.b AND v1.b = t3.c); + +DROP VIEW v1; +DROP TABLE t1,t2,t3; + +--echo # +--echo # LP bug #873263: materialized view used in correlated IN subquery +--echo # + +CREATE TABLE t1 (a int, b int) ; +INSERT INTO t1 VALUES (5,4), (9,8); + +CREATE TABLE t2 (a int, b int) ; +INSERT INTO t2 VALUES (4,5), (5,1); + +CREATE ALGORITHM=TEMPTABLE VIEW v2 AS SELECT * FROM t2; + +SET SESSION optimizer_switch='derived_with_keys=on'; +EXPLAIN +SELECT * FROM t1 WHERE t1.b IN (SELECT v2.a FROM v2 WHERE v2.b = t1.a); +SELECT * FROM t1 WHERE t1.b IN (SELECT v2.a FROM v2 WHERE v2.b = t1.a); + +DROP VIEW v2; +DROP TABLE t1,t2; + +--echo # +--echo # LP bug #877316: query over a view with correlated subquery in WHERE +--echo # + +CREATE TABLE t1 (a int, b int, PRIMARY KEY (a)) ; +INSERT INTO t1 VALUES (18,2), (19,9); + +CREATE TABLE t2 (a int, b int) ; +INSERT INTO t2 VALUES (10,8), (5,10); + +CREATE VIEW v1 AS SELECT * FROM t1; + +SELECT t1.a FROM t1 + WHERE EXISTS (SELECT t2.a FROM t2 WHERE t2.b < t1.b); +EXPLAIN +SELECT t1.a FROM t1 + WHERE EXISTS (SELECT t2.a FROM t2 WHERE t2.b < t1.b); + +SELECT v1.a FROM v1 + WHERE EXISTS (SELECT t2.a FROM t2 WHERE t2.b < v1.b); +EXPLAIN +SELECT v1.a FROM v1 + WHERE EXISTS (SELECT t2.a FROM t2 WHERE t2.b < v1.b); + +DROP VIEW v1; +DROP TABLE t1,t2; + +--echo # +--echo # LP bug #878199: join of two materialized views +--echo # + +CREATE TABLE t1 (a int, b varchar(1)) ; +INSERT INTO t1 VALUES (7,'c'), (3,'h'), (7,'c'); + +CREATE TABLE t2 (b varchar(1)) ; +INSERT INTO t2 VALUES ('p'), ('c'), ('j'), ('c'), ('p'); + +CREATE VIEW v1 AS SELECT * FROM t1 GROUP BY a,b; + +CREATE VIEW v2 AS SELECT * FROM t2 GROUP BY b; + +SET SESSION optimizer_switch = 'derived_with_keys=on'; + +SELECT v1.a FROM v1,v2 WHERE v2.b = v1.b ORDER BY 1; +EXPLAIN +SELECT v1.a FROM v1,v2 WHERE v2.b = v1.b ORDER BY 1; + +DROP VIEW v1,v2; +DROP TABLE t1,t2; + + +--echo # +--echo # Bug #743378: join over merged view employing BNL +--echo # + +CREATE TABLE t1 ( d varchar(1) NOT NULL) ; +INSERT INTO t1 VALUES ('j'),('v'),('c'); + +CREATE TABLE t2 (h time NOT NULL, d varchar(1) NOT NULL) ; +INSERT INTO t2 VALUES ('05:03:03','w'),('02:59:24','d'),('00:01:58','e'); + +CREATE TABLE t3 ( + b int NOT NULL, e varchar(1) NOT NULL, d varchar(1) NOT NULL, KEY (e,b) +); +INSERT INTO t3 VALUES (4,'x','x'),(9,'w','w'),(4,'d','d'),(8,'e','e'); + +CREATE TABLE t4 (i int NOT NULL, m varchar(1) NOT NULL) ; +INSERT INTO t4 VALUES (8,'m'),(9,'d'),(2,'s'),(4,'r'),(8,'m'); + +CREATE TABLE t5 ( + a int NOT NULL, c int NOT NULL, b int NOT NULL, f date NOT NULL, + g date NOT NULL, h time NOT NULL, j time NOT NULL, k datetime NOT NULL +); + +INSERT INTO t5 VALUES + (1,4,0,'0000-00-00','0000-00-00','21:22:34','21:22:34','2002-02-13 17:30'), + (2,6,8,'2004-09-18','2004-09-18','10:50:38','10:50:38','2008-09-27 00:34'); + +CREATE VIEW v3 AS SELECT t3.*, t4.i FROM t3, t4, t5; + +SET SESSION join_cache_level = 1; +SET SESSION join_buffer_size = 512; + +EXPLAIN +SELECT t2.d FROM t1,t2,v3 WHERE v3.e = t2.d AND v3.i < 3; +SELECT t2.d FROM t1,t2,v3 WHERE v3.e = t2.d AND v3.i < 3; + +SET SESSION join_cache_level = DEFAULT; +SET SESSION join_buffer_size = DEFAULT; + +DROP VIEW v3; +DROP TABLE t1,t2,t3,t4,t5; + +--echo # +--echo # Bug #879882: right join within mergeable derived table +--echo # + +CREATE TABLE t1 (a varchar(1)); +INSERT INTO t1 VALUES ('c'), ('a'); + +CREATE TABLE t2 (a int, b int, c varchar(1)); +INSERT INTO t2 VALUES (29,8,'c'), (39,7,'b'); + +CREATE TABLE t3 (b int); + +EXPLAIN EXTENDED +SELECT t.b, t.c, t1.a +FROM t1, (SELECT t2.b, t2.c FROM t3 RIGHT JOIN t2 ON t2.a = t3.b) AS t + WHERE t.b AND t.c = t1.a; +SELECT t.b, t.c, t1.a +FROM t1, (SELECT t2.b, t2.c FROM t3 RIGHT JOIN t2 ON t2.a = t3.b) AS t + WHERE t.b AND t.c = t1.a; + +EXPLAIN EXTENDED +SELECT t.b, t.c, t1.a +FROM t1, (SELECT t2.b, t2.c FROM t3 RIGHT JOIN t2 ON t2.a = t3.b) AS t + WHERE t.b <> 0 AND t.c = t1.a; +SELECT t.b, t.c, t1.a +FROM t1, (SELECT t2.b, t2.c FROM t3 RIGHT JOIN t2 ON t2.a = t3.b) AS t + WHERE t.b <> 0 AND t.c = t1.a; + +INSERT INTO t3 VALUES (100), (200); + +EXPLAIN EXTENDED +SELECT t.b, t.c, t1.a +FROM t1, (SELECT t2.b, t2.c FROM t3 RIGHT JOIN t2 ON t2.a = t3.b) AS t + WHERE t.b AND t.c = t1.a; +SELECT t.b, t.c, t1.a +FROM t1, (SELECT t2.b, t2.c FROM t3 RIGHT JOIN t2 ON t2.a = t3.b) AS t + WHERE t.b AND t.c = t1.a; + +EXPLAIN EXTENDED +SELECT t.b, t.c, t1.a +FROM t1, (SELECT t2.b, t2.c FROM t3 RIGHT JOIN t2 ON t2.a = t3.b) AS t + WHERE t.b <> 0 AND t.c = t1.a; +SELECT t.b, t.c, t1.a +FROM t1, (SELECT t2.b, t2.c FROM t3 RIGHT JOIN t2 ON t2.a = t3.b) AS t + WHERE t.b <> 0 AND t.c = t1.a; + +DROP TABLE t1,t2,t3; + +--echo # +--echo # Bug #880724: materialized const view as inner table of outer join +--echo # + +CREATE TABLE t1 (a int, b varchar(1)); +INSERT INTO t1 VALUES (9,NULL), (6,'r'), (7,'c'); + +CREATE TABLE t2 (a int); +INSERT INTO t2 VALUES (6); + +CREATE ALGORITHM=TEMPTABLE VIEW v2 AS SELECT * FROM t2; + +SET SESSION optimizer_switch = 'derived_with_keys=on'; +SET SESSION join_cache_level = 4; + +EXPLAIN +SELECT t1.b,v2.a FROM t1 LEFT JOIN v2 ON v2.a = t1.a; +SELECT t1.b,v2.a FROM t1 LEFT JOIN v2 ON v2.a = t1.a; + +CREATE TABLE t3 (a int, b varchar(1)); +INSERT INTO t3 VALUES (8,'x'), (5,'r'), (9,'y'); + +EXPLAIN +SELECT * FROM t3 + WHERE t3.b <> ANY (SELECT t1.b FROM t1 LEFT JOIN v2 ON v2.a = t1.a); +SELECT * FROM t3 + WHERE t3.b <> ANY (SELECT t1.b FROM t1 LEFT JOIN v2 ON v2.a = t1.a); + +SET SESSION join_cache_level = default; + +DROP VIEW v2; +DROP TABLE t1,t2,t3; + +--echo # +--echo # Bug #881449: OUTER JOIN usin a merged view within IN subquery +--echo # + +CREATE TABLE t1 (a varchar(1)) ; +INSERT INTO t1 VALUES ('y'), ('x'); + +CREATE TABLE t2 (a int, PRIMARY KEY (a)) ; +INSERT INTO t2 VALUES (1), (2); + +CREATE TABLE t3 (a int, b varchar(1)) ; +INSERT INTO t3 VALUES (1,'x'); + +CREATE VIEW v3 AS SELECT * FROM t3; + +SET SESSION optimizer_switch='semijoin=on'; + +EXPLAIN +SELECT * FROM t1 WHERE a IN (SELECT v3.b FROM t2 RIGHT JOIN v3 ON v3.a = t2.a); +SELECT * FROM t1 WHERE a IN (SELECT v3.b FROM t2 RIGHT JOIN v3 ON v3.a = t2.a); + +set optimizer_switch= @save_optimizer_switch; + +DROP VIEW v3; +DROP TABLE t1,t2,t3; + +--echo # +--echo # Bug #874035: view as an inner table of a materialized derived +--echo # + +CREATE TABLE t2 (a int NOT NULL); +INSERT INTO t2 VALUES (7), (4); + +CREATE TABLE t1 (b int NOT NULL); +INSERT INTO t1 VALUES (5), (7); +CREATE ALGORITHM=MERGE VIEW v1 AS SELECT * FROM t1; + +SET SESSION optimizer_switch='derived_merge=off'; + +PREPARE st1 FROM +'SELECT * FROM (SELECT * FROM t2 LEFT JOIN v1 ON t2.a = v1.b) AS t'; +EXECUTE st1; +EXECUTE st1; +DEALLOCATE PREPARE st1; + +DROP VIEW v1; +DROP TABLE t1,t2; + +SET SESSION optimizer_switch='derived_merge=on'; + +--echo # +--echo # LP bug #879939: assertion in ha_maria::enable_indexes +--echo # with derived_with_keys=on +--echo # + +CREATE TABLE t2 (a varchar(3)); +INSERT INTO t2 VALUES ('USA'), ('USA'), ('USA'), ('USA'), ('USA'); + +CREATE TABLE t1 (a varchar(3), b varchar(35)); +INSERT INTO t1 VALUES + ('USA','Lansing'), ('USA','Laredo'), ('USA','Las Vegas'), + ('USA','Lexington-Fayett'), ('USA','Lincoln'), ('USA','Little Rock'), + ('USA','Livonia'), ('USA','Long Beach'), ('USA','Los Angeles'), + ('USA','Louisville'), ('USA','Lowell'), ('USA','Lubbock'), + ('USA','Macon'), ('USA','Madison'), ('USA','Manchester'), + ('USA','McAllen'), ('USA','Memphis'), ('USA','Mesa'), + ('USA','Mesquite'), ('USA','Metairie'), ('USA','Miami'); + +CREATE TABLE t3 (a varchar(35)); +INSERT INTO t3 VALUES ('Miami'); + +SET optimizer_switch = 'derived_with_keys=on'; +SET @@tmp_table_size=1024*4; +explain SELECT * FROM (SELECT t1.* FROM t1, t2) AS t JOIN t3 ON t3.a = t.b; +SELECT * FROM (SELECT t1.* FROM t1, t2) AS t JOIN t3 ON t3.a = t.b; +SET @@tmp_table_size=1024*1024*16; +SELECT * FROM (SELECT t1.* FROM t1, t2) AS t JOIN t3 ON t3.a = t.b; +SET @@tmp_table_size=default; + +drop table t1,t2,t3; + +--echo # +--echo # BUG#882994: Crash in QUICK_RANGE_SELECT::reset with derived_with_keys +--echo # +CREATE TABLE t2 ( + pk varchar(33), + col_varchar_key varchar(3) NOT NULL, + col_varchar_nokey varchar(52) NOT NULL); + +INSERT INTO t2 VALUES ('NICSpanish','NIC','Spanish'), + ('NERHausa','NER','Hausa'),('NGAJoruba','NGA','Joruba'), + ('NIUNiue','NIU','Niue'),('NFKEnglish','NFK','English'), + ('NORNorwegian','NOR','Norwegian'),('CIVAkan','CIV','Akan'), + ('OMNArabic','OMN','Arabic'),('PAKPunjabi','PAK','Punjabi'), + ('PLWPalau','PLW','Palau'),('PANSpanish','PAN','Spanish'), + ('PNGPapuan Langua','PNG','Papuan Languages'), ('PRYSpanish','PRY','Spanish'), + ('PERSpanish','PER','Spanish'), ('PCNPitcairnese','PCN','Pitcairnese'), + ('MNPPhilippene La','MNP','Philippene Langu'),('PRTPortuguese','PRT','Portuguese'), + ('PRISpanish','PRI','Spanish'),('POLPolish','POL','Polish'),('GNQFang','GNQ','Fang'); + +CREATE TABLE t1 ( col_varchar_nokey varchar(52) NOT NULL ) ; +INSERT INTO t1 VALUES ('Chinese'),('English'),('French'),('German'), + ('Italian'),('Japanese'),('Korean'),('Polish'),('Portuguese'),('Spanish'), + ('Tagalog'),('Vietnamese'); +CREATE TABLE t3 ( col_varchar_key varchar(52)) ; +INSERT INTO t3 VALUES ('United States'); + +set @tmp_882994= @@max_heap_table_size; +--disable_warnings +set max_heap_table_size=1; +--enable_warnings + +SELECT * +FROM t3 JOIN +( SELECT t2.* FROM t1, t2 ) AS alias2 +ON ( alias2.col_varchar_nokey = t3.col_varchar_key ) +ORDER BY CONCAT(alias2.col_varchar_nokey); + +set max_heap_table_size= @tmp_882994; +drop table t1,t2,t3; + # The following command must be the last one the file set optimizer_switch=@exit_optimizer_switch; diff --git a/mysql-test/t/explain.test b/mysql-test/t/explain.test index 8620a43cea5..ece2e968b83 100644 --- a/mysql-test/t/explain.test +++ b/mysql-test/t/explain.test @@ -152,7 +152,7 @@ DROP TABLE t1; --echo # explain extended crash with subquery and ONLY_FULL_GROUP_BY sql_mode --echo # -CREATE TABLE t1 (f1 INT); +CREATE TABLE t1 (f1 INT not null); SELECT @@session.sql_mode INTO @old_sql_mode; SET SESSION sql_mode='ONLY_FULL_GROUP_BY'; diff --git a/mysql-test/t/feedback_plugin_install.opt b/mysql-test/t/feedback_plugin_install.opt new file mode 100644 index 00000000000..a711ae94e69 --- /dev/null +++ b/mysql-test/t/feedback_plugin_install.opt @@ -0,0 +1 @@ +--loose-feedback diff --git a/mysql-test/t/feedback_plugin_install.test b/mysql-test/t/feedback_plugin_install.test new file mode 100644 index 00000000000..81343c436c3 --- /dev/null +++ b/mysql-test/t/feedback_plugin_install.test @@ -0,0 +1,15 @@ +--source include/not_embedded.inc + +if (`select length('$FEEDBACK_SO') = 0`) { + skip No feedback plugin; +} + +--replace_regex /\.dll/.so/ +eval install plugin feedback soname '$FEEDBACK_SO'; +select plugin_status from information_schema.plugins where plugin_name='feedback'; +--replace_result https http +--sorted_result +select * from information_schema.feedback where variable_name like 'feed%' + and variable_name not like '%_uid'; +uninstall plugin feedback; + diff --git a/mysql-test/t/feedback_plugin_load.opt b/mysql-test/t/feedback_plugin_load.opt new file mode 100644 index 00000000000..5fbb2f83954 --- /dev/null +++ b/mysql-test/t/feedback_plugin_load.opt @@ -0,0 +1,2 @@ +--loose-feedback +--plugin-load=$FEEDBACK_SO diff --git a/mysql-test/t/feedback_plugin_load.test b/mysql-test/t/feedback_plugin_load.test new file mode 100644 index 00000000000..5ad301667b4 --- /dev/null +++ b/mysql-test/t/feedback_plugin_load.test @@ -0,0 +1,10 @@ +if (`select count(*) = 0 from information_schema.plugins where plugin_name = 'feedback' and plugin_status='active'`) +{ + --skip Feedback plugin is not active +} + +select plugin_status from information_schema.plugins where plugin_name='feedback'; +--replace_result https http +--sorted_result +select * from information_schema.feedback where variable_name like 'feed%' + and variable_name not like '%_uid'; diff --git a/mysql-test/t/feedback_plugin_send.test b/mysql-test/t/feedback_plugin_send.test new file mode 100644 index 00000000000..14765ee9543 --- /dev/null +++ b/mysql-test/t/feedback_plugin_send.test @@ -0,0 +1,23 @@ +source t/feedback_plugin_load.test; + +if (!$MTR_FEEDBACK_PLUGIN) { + skip MTR_FEEDBACK_PLUGIN is not set; +} + +# +# Yep. The plugin waits 5 minutes before sending anything, +# and there's no way to force it to send anything sooner. +# Let's wait, and hope that mtr is started with --parallel and +# is doing some work in other workers. +# +sleep 310; +source include/restart_mysqld.inc; + +replace_result https http; +perl; + $log_error= $ENV{'MYSQLTEST_VARDIR'} . '/log/mysqld.1.err'; + open(LOG, '<', $log_error) or die "open(< $log_error): $!"; + /feedback plugin:.*/ && print "$&\n" while $_=<LOG>; + close LOG; +EOF + diff --git a/mysql-test/t/fulltext.test b/mysql-test/t/fulltext.test index e49970047a2..b4b09413896 100644 --- a/mysql-test/t/fulltext.test +++ b/mysql-test/t/fulltext.test @@ -437,6 +437,8 @@ show warnings; CREATE TABLE t1 (a VARCHAR(255), b INT, FULLTEXT(a), KEY(b)); INSERT INTO t1 VALUES('test', 1),('test', 1),('test', 1),('test', 1), ('test', 1),('test', 2),('test', 3),('test', 4); +INSERT INTO t1 VALUES('test', 5),('test', 6),('test', 7),('test', 8), + ('test', 5),('test', 6),('test', 7),('test', 8); EXPLAIN SELECT * FROM t1 WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1; diff --git a/mysql-test/t/fulltext_plugin.test b/mysql-test/t/fulltext_plugin.test index 5f09992d464..0e2f53d5b15 100644 --- a/mysql-test/t/fulltext_plugin.test +++ b/mysql-test/t/fulltext_plugin.test @@ -3,7 +3,7 @@ # # BUG#39746 - Debug flag breaks struct definition (server crash) # ---replace_regex /\.dll/.so/ +--replace_result .dll .so eval INSTALL PLUGIN simple_parser SONAME '$MYPLUGLIB_SO'; CREATE TABLE t1(a TEXT, b TEXT, FULLTEXT(a) WITH PARSER simple_parser); ALTER TABLE t1 ADD FULLTEXT(b) WITH PARSER simple_parser; diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test index 77b2fd67ffa..a18ce6b5c51 100644 --- a/mysql-test/t/func_group.test +++ b/mysql-test/t/func_group.test @@ -1139,6 +1139,13 @@ INSERT INTO t1 VALUES (0000),(2001); --disable_metadata DROP TABLE t1; +--echo # +--echo # LP BUG#813418 - incorrect optimisation of max/min by index for +--echo # negated BETWEEN +CREATE TABLE t1 (a int, KEY (a)); +INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10); +SELECT MAX(a) FROM t1 WHERE a NOT BETWEEN 3 AND 9; +drop table t1; --echo # --echo End of 5.1 tests diff --git a/mysql-test/t/func_in.test b/mysql-test/t/func_in.test index 08469b37967..febec62f037 100644 --- a/mysql-test/t/func_in.test +++ b/mysql-test/t/func_in.test @@ -563,3 +563,23 @@ SELECT 1 IN (YEAR(FROM_UNIXTIME(NULL)) ,1); --echo # --echo End of 5.1 tests + +# +# lp:817966 int_column IN (string_constant) +# +# rather illogically, when BIGINT field is compared to a string, +# the string is converted to an integer, not to a double. +# When some other integer field (not BIGINT) is compared to a string, +# or when the BIGINT is not a field, but an expression, both +# operands are compared as doubles. The latter behavior is correct, +# according to the manual. +# +create table t1 (a bigint, b int); +insert t1 values (1,1),(2,2),(3,3); +select * from t1 where a in ('2.1'); +select * from t1 where b in ('2.1'); +select * from t1 where a='2.1'; +select * from t1 where b='2.1'; +select * from t1 where IF(1,a,a)='2.1'; +drop table t1; + diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test index e3c7cf8281f..7d0e5ae819a 100644 --- a/mysql-test/t/func_time.test +++ b/mysql-test/t/func_time.test @@ -18,6 +18,8 @@ select sec_to_time(9001),sec_to_time(9001)+0,time_to_sec("15:12:22"), select sec_to_time(9001.1), time_to_sec('15:12:22.123456'), time_to_sec(15.5566778899); select sec_to_time(time_to_sec('-838:59:59')); select sec_to_time('9001.1'), sec_to_time('1234567890123.123'); +select sec_to_time(-9001.1), sec_to_time(-9001.1) / 1, + sec_to_time(-9001.1) / 1e0, sec_to_time(-9001) div 1; --replace_result e+042 e+42 select sec_to_time(90011e-1), sec_to_time(1234567890123e30); select sec_to_time(1234567890123), sec_to_time('99999999999999999999999999999'); diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test index 85147e28cf9..30f5657ffdf 100644 --- a/mysql-test/t/group_by.test +++ b/mysql-test/t/group_by.test @@ -1336,3 +1336,20 @@ SELECT f1,MIN(f2),MAX(f2) FROM t1 GROUP BY 1; DROP TABLE t1; --echo #End of test#49771 +--echo # +--echo # lp:872702: Crash in add_ref_to_table_cond() when grouping by a PK +--echo # +CREATE TABLE t1 (a int, PRIMARY KEY (a)) ; +INSERT INTO t1 VALUES (14),(15),(16),(17),(18),(19),(20); + +CREATE TABLE t2 (a int) ; + +SELECT a +FROM t1 +WHERE a = ( + SELECT t2.a + FROM t2 +) OR t1.a = 73 +GROUP BY 1; +DROP TABLE t1, t2; + diff --git a/mysql-test/t/index_merge_myisam.test b/mysql-test/t/index_merge_myisam.test index 5431c6dba2b..614c6595d61 100644 --- a/mysql-test/t/index_merge_myisam.test +++ b/mysql-test/t/index_merge_myisam.test @@ -125,5 +125,29 @@ set optimizer_switch=default; drop table t0, t1; + +--echo # +--echo # BUG#834514 Assertion `!table || (!table->read_set || bitmap_is_set(...' with aggregates +--echo # +CREATE TABLE t1 ( a int , b int, c int, KEY (b), PRIMARY KEY (a)) ; +INSERT INTO t1 VALUES (1,4,0),(5,0,0),(6,7,0),(7,7,0),(8,1,0),(9,7,0),(10,1,0); + +CREATE TABLE t2 ( b int, c int, KEY (c,b)) ; +INSERT INTO t2 VALUES (7,0),(1,0),(7,0),(1,0); + +CREATE TABLE t3 ( a int ) ; + +SELECT COUNT(DISTINCT t2.b), CONCAT(t1.c) +FROM t1, t2 +WHERE (t2.c = t1.c) +AND ( + t1.b IN ( 4 ) + OR t1.a = 137 + AND EXISTS ( SELECT a FROM t3 ) +) +GROUP BY 2; + +DROP TABLE t1,t2,t3; + set optimizer_switch= @optimizer_switch_save; diff --git a/mysql-test/t/innodb_bug878769.test b/mysql-test/t/innodb_bug878769.test new file mode 100644 index 00000000000..157c7827dde --- /dev/null +++ b/mysql-test/t/innodb_bug878769.test @@ -0,0 +1,56 @@ +--source include/have_innodb.inc + +--disable_warnings +drop table if exists t1,t2; +--enable_warnings + +--echo # +--echo # Bug #878769: valgrind complains when using join cache +--echo # to join an InnoDB table without primary key +--echo # + +CREATE TABLE t1 ( + col_int_key int(11), col_time_key time, col_varchar_key varchar(1), + KEY (col_int_key), KEY (col_varchar_key,col_int_key) +) ENGINE=InnoDB; +INSERT IGNORE INTO t1 VALUES + (7,'10:19:31','d'),(1,'14:40:36','r'),(7,'04:37:47','f'),(9,'19:34:06','y'), + (2,'00:00:00','m'),(4,'00:13:25','q'),(0,'03:47:16',NULL),(4,'01:41:48','d'), + (8,'00:00:00','g'),(NULL,'22:32:04','x'),(NULL,'16:44:14','f'), + (0,'17:38:37','p'),(NULL,'08:46:48','j'),(8,'14:11:27','c'); + +CREATE TABLE t2 ( + col_int_nokey int(11), col_int_key int(11), + col_datetime_key datetime, col_datetime_nokey datetime, + col_varchar_key varchar(1), col_varchar_nokey varchar(1), + KEY (col_int_key), KEY (col_varchar_key,col_int_key) +); +INSERT IGNORE INTO t2 VALUES + (150,62,'2008-01-03 10:33:32','2008-01-03 10:33:32','v','v'), + (2,1,'2007-10-09 19:53:04','2007-10-09 19:53:04',NULL,NULL), + (5,0,'2001-11-08 21:02:12','2001-11-08 21:02:12','x','x'), + (3,7,'2003-04-01 00:00','2003-04-01 00:00','i','i'), + (1,7,'1900-01-01 00:00','1900-01-01 00:00:00','e','e'), + (NULL,7,'2005-04-04 01:21','2005-04-04 01:21','s','s'), + (2,1,'1900-01-01 00:00','1900-01-01 00:00','j','j'), + (8,0,'2004-04-28 21:44','2004-04-28 21:44','a','a'), + (6,8,'2001-04-18 00:00','2001-04-18 00:00:00','y','y'), + (8,1,'2008-12-18 19:39:55','2008-12-18 19:39:55',NULL,NULL), + (3,1,'2000-08-01 12:19:39','2000-08-01 12:19:39','r','r'), + (3,9,'2004-09-25 21:29:06','2004-09-25 21:29:06','v','v'); + +set session optimizer_switch='mrr=on,mrr_sort_keys=on'; +set session join_cache_level=6; + +EXPLAIN +SELECT t1.col_time_key, t1.col_varchar_key + FROM t2 STRAIGHT_JOIN t1 ON t1.col_int_key = t2.col_int_key +GROUP BY 1,2; +SELECT t1.col_time_key, t1.col_varchar_key + FROM t2 STRAIGHT_JOIN t1 ON t1.col_int_key = t2.col_int_key +GROUP BY 1,2; + +set session optimizer_switch=default; +set session join_cache_level=default; + +DROP TABLE t1,t2; diff --git a/mysql-test/t/join.test b/mysql-test/t/join.test index c4f2b6cb61f..82e67904e9f 100644 --- a/mysql-test/t/join.test +++ b/mysql-test/t/join.test @@ -1015,3 +1015,13 @@ SELECT STRAIGHT_JOIN * FROM t1 JOIN t2 ON t2.f2 = t1.f1 DROP TABLE t1,t2; +# +# Item_equal used cmp_item::get_comparator() incorrectly +# +create table t1 (i time key); +insert into t1 values ('1:1:1'), ('2:2:2'); +create table t2 (i time); +insert into t2 values ('1:1:1'); +select t2.i from t1 left join t2 on t2.i = t1.i where t1.i = '1:1:1'; +drop table t1,t2; + diff --git a/mysql-test/t/join_cache.test b/mysql-test/t/join_cache.test index ca0d10c3ee1..caf5af57df7 100644 --- a/mysql-test/t/join_cache.test +++ b/mysql-test/t/join_cache.test @@ -1220,7 +1220,9 @@ CREATE TABLE t2 (a int, b int, INDEX idx(a)); INSERT INTO t1 VALUES (5,30), (3,20), (7,40), (2,10), (8,30), (1,10), (4,20); INSERT INTO t2 VALUES (7,10), (1,20), (2,20), (8,20), (8,10), (1,20); INSERT INTO t2 VALUES (1,10), (4,20), (3,20), (7,20), (7,10), (1,20); - +INSERT INTO t2 VALUES (17,10), (11,20), (12,20), (18,20), (18,10), (11,20); +INSERT INTO t2 VALUES (11,10), (14,20), (13,20), (17,20), (17,10), (11,20); + set join_buffer_size=32; set join_cache_level=8; @@ -1241,6 +1243,7 @@ CREATE TABLE t1 (a int NOT NULL); INSERT INTO t1 VALUES (2), (4), (3), (5), (1); CREATE TABLE t2 (a int NOT NULL, b int NOT NULL, INDEX i_a(a)); INSERT INTO t2 VALUES (4,10), (2,10), (2,30), (2,20), (4,20); +INSERT INTO t2 VALUES (14,10), (12,10), (15,30), (12,20), (14,20); EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE t2.b IS NULL; @@ -1564,6 +1567,9 @@ create table t2 (id1 int, id2 int, index idx2 (id1)); insert into t2 values (20, 100), (30, 400), (20, 400), (30, 200), (10, 300), (10, 200), (40, 100), (40, 200), (30, 300), (10, 400), (20, 200), (20, 300); +insert into t2 values + (21, 10), (31, 400), (21, 400), (31, 200), (11, 300), (11, 200), (41, 100), + (41, 200), (31, 300), (11, 400), (21, 200), (21, 300); set join_cache_level=6; @@ -1602,14 +1608,26 @@ insert into t2 values (30, 'bbb'), (10, 'b'), (70, 'bbbbbbb'), (60, 'bbbbbb'), (31, 'bbb'), (11, 'b'), (71, 'bbbbbbb'), (61, 'bbbbbb'), (32, 'bbb'), (12, 'b'), (72, 'bbbbbbb'), (62, 'bbbbbb'); +insert into t2 values + (130, 'bbb'), (110, 'b'), (170, 'bbbbbbb'), (160, 'bbbbbb'), + (131, 'bbb'), (111, 'b'), (171, 'bbbbbbb'), (161, 'bbbbbb'), + (132, 'bbb'), (112, 'b'), (172, 'bbbbbbb'), (162, 'bbbbbb'); insert into t3 values (4000, 'dddd'), (3000, 'ddd'), (1000, 'd'), (8000, 'dddddddd'), (4001, 'dddd'), (3001, 'ddd'), (1001, 'd'), (8001, 'dddddddd'), (4002, 'dddd'), (3002, 'ddd'), (1002, 'd'), (8002, 'dddddddd'); +insert into t3 values + (14000, 'dddd'), (13000, 'ddd'), (11000, 'd'), (18000, 'dddddddd'), + (14001, 'dddd'), (13001, 'ddd'), (11001, 'd'), (18001, 'dddddddd'), + (4002, 'dddd'), (3002, 'ddd'), (1002, 'd'), (8002, 'dddddddd'); insert into t4 values (200, 'cc'), (600, 'cccccc'), (300, 'ccc'), (500, 'ccccc'), (201, 'cc'), (601, 'cccccc'), (301, 'ccc'), (501, 'ccccc'), (202, 'cc'), (602, 'cccccc'), (302, 'ccc'), (502, 'ccccc'); +insert into t4 values + (1200, 'cc'), (1600, 'cccccc'), (1300, 'ccc'), (1500, 'ccccc'), + (1201, 'cc'), (1601, 'cccccc'), (1301, 'ccc'), (1501, 'ccccc'), + (1202, 'cc'), (1602, 'cccccc'), (1302, 'ccc'), (1502, 'ccccc'); --disable_result_log --disable_warnings @@ -2264,7 +2282,9 @@ CREATE TABLE t1 (b int); INSERT INTO t1 VALUES (NULL),(3); CREATE TABLE t2 (a int, b int, KEY (b)); -INSERT INTO t2 VALUES (100,NULL),(150,200); +INSERT INTO t2 VALUES + (100,NULL),(150,200),(50,150),(250,350),(180,210),(100,150), + (101,NULL),(151,200),(51,150),(251,350),(181,210),(101,150); set join_cache_level = 5; explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b = t1.b; @@ -2292,6 +2312,7 @@ INSERT INTO t1 VALUES (NULL),("some varchar"); CREATE TABLE t2 (a int, b varchar(100), KEY (b)); INSERT INTO t2 VALUES (100,NULL),(150,"varchar"),(200,NULL),(250,"long long varchar"); +INSERT INTO t2 VALUES (100,NULL),(150,"long varchar"),(200,"varchar"),(250,"long long long varchar"); set join_cache_level = 5; explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b = t1.b; @@ -2393,6 +2414,17 @@ INSERT INTO t3 VALUES (1,0,'2002-07-13','06:34:26','v','v'), (9,3,'2003-01-03','18:07:38','m','m'), (1,5,'2006-04-02','13:55:23','z','z'), (3,9,'2006-10-19','20:32:28','n','n'), (8,1,'2005-06-08','11:57:44','d','d'), (231,107,'2006-12-26','03:10:35','a','a'); +INSERT INTO t3 VALUES + (103,108,'2008-12-04','00:00:00','a','v'), (103,108,'2009-03-28','00:00:00','b','f'), + (103,105,'1900-01-01','00:55:47','c','v'), (102,108,'2009-10-02','00:00:00','d','s'), + (100,108,'1900-01-01','20:51:59','e','a'), (100,106,'2008-06-04','09:47:27','f','p'), + (108,107,'2009-01-13','21:58:29','g','z'), (105,102,'1900-01-01','22:45:53','h','a'), + (109,105,'2008-01-28','14:06:48','i','h'), (105,107,'2004-09-18','22:17:16','j','h'), + (104,102,'2006-10-14','14:59:37','k','v'), (102,109,'1900-01-01','23:37:40','l','v'), + (1033,1142,'2000-11-28','14:14:01','m','b'), (105,103,'2008-04-04','02:54:19','n','y'), + (100,100,'2002-07-13','06:34:26','o','v'), (109,103,'2003-01-03','18:07:38','p','m'), + (100,105,'2006-04-02','13:55:23','q','z'), (103,109,'2006-10-19','20:32:28','s','n'), + (108,100,'2005-06-08','11:57:44','t','d'), (1231,1107,'2006-12-26','03:10:35','v','a'); CREATE TABLE t1 SELECT * FROM t3; DELETE FROM t1 WHERE i > 8; @@ -2425,7 +2457,15 @@ CREATE TABLE t1 ( INSERT INTO t1 VALUES (10,8,'v'), (11,8,'f'), (12,5,'v'), (13,8,'s'), (14,8,'a'), (15,6,'p'), (16,7,'z'), (17,2,'a'), (18,5,'h'), (19,7,'h'), - (25,3,'m'), (26,5,'z'), (27,9,'n'), (28,1,'d'), (29,107,'a'); + (25,3,'m'), (26,5,'a'), (27,9,'n'), (28,1,'d'), (29,107,'a'); +INSERT INTO t1 VALUES + (110,8,'x'), (111,8,'y'), (112,5,'v'), (113,8,'z'), (114,8,'i'), + (115,6,'j'), (116,7,'t'), (117,2,'b'), (118,5,'j'), (119,7,'w'), + (125,3,'q'), (126,5,'o'), (127,9,'n'), (128,1,'e'), (129,107,'c'); +INSERT INTO t1 VALUES + (210,8,'b'), (211,8,'c'), (212,5,'d'), (213,8,'e'), (214,8,'g'), + (215,6,'f'), (216,7,'h'), (217,2,'i'), (218,5,'j'), (219,7,'k'), + (225,3,'l'), (226,5,'m'), (227,9,'n'), (228,1,'o'), (229,107,'p'); CREATE TABLE t2 ( pk int NOT NULL, i int NOT NULL, v varchar(1) NOT NULL, @@ -2435,7 +2475,7 @@ INSERT INTO t2 VALUES (10,8,'v'), (11,8,'f'), (12,5,'v'), (13,8,'s'), (14,8,'a'), (15,6,'p'), (16,7,'z'), (17,2,'a'), (18,5,'h'), (19,7,'h'), (20,2,'v'), (21,9,'v'), (22,142,'b'), (23,3,'y'), (24,0,'v'), - (25,3,'m'), (26,5,'z'), (27,9,'n'), (28,1,'d'), (29,107,'a'); + (25,3,'m'), (26,5,'b'), (27,9,'n'), (28,1,'d'), (29,107,'a'); CREATE TABLE t3 ( pk int NOT NULL, i int NOT NULL, v varchar(1) NOT NULL, @@ -2449,23 +2489,29 @@ INSERT INTO t3 VALUES SET SESSION join_cache_level=1; EXPLAIN -SELECT t2.v FROM t1, t2, t3 WHERE t3.v <> t2.v AND t3.pk = t2.i AND t1.v = t3.v +SELECT t2.v FROM t1, t2, t3 +WHERE t3.v <> t2.v AND t3.pk = t2.i AND t1.v = t3.v AND t1.pk*2<100 GROUP BY t2.v ORDER BY t1.pk,t2.v; -SELECT t2.v FROM t1, t2, t3 WHERE t3.v <> t2.v AND t3.pk = t2.i AND t1.v = t3.v +SELECT t2.v FROM t1, t2, t3 +WHERE t3.v <> t2.v AND t3.pk = t2.i AND t1.v = t3.v AND t1.pk*2<100 GROUP BY t2.v ORDER BY t1.pk,t2.v; SET SESSION join_cache_level=6; EXPLAIN -SELECT t2.v FROM t1, t2, t3 WHERE t3.v <> t2.v AND t3.pk = t2.i AND t1.v = t3.v +SELECT t2.v FROM t1, t2, t3 +WHERE t3.v <> t2.v AND t3.pk = t2.i AND t1.v = t3.v AND t1.pk*2<100 GROUP BY t2.v ORDER BY t1.pk,t2.v; -SELECT t2.v FROM t1, t2, t3 WHERE t3.v <> t2.v AND t3.pk = t2.i AND t1.v = t3.v +SELECT t2.v FROM t1, t2, t3 +WHERE t3.v <> t2.v AND t3.pk = t2.i AND t1.v = t3.v AND t1.pk*2<100 GROUP BY t2.v ORDER BY t1.pk,t2.v; SET SESSION join_cache_level=4; EXPLAIN -SELECT t2.v FROM t1, t2, t3 WHERE t3.v <> t2.v AND t3.pk = t2.i AND t1.v = t3.v +SELECT t2.v FROM t1, t2, t3 +WHERE t3.v <> t2.v AND t3.pk = t2.i AND t1.v = t3.v AND t1.pk*2<100 GROUP BY t2.v ORDER BY t1.pk,t2.v; -SELECT t2.v FROM t1, t2, t3 WHERE t3.v <> t2.v AND t3.pk = t2.i AND t1.v = t3.v +SELECT t2.v FROM t1, t2, t3 +WHERE t3.v <> t2.v AND t3.pk = t2.i AND t1.v = t3.v AND t1.pk*2<100 GROUP BY t2.v ORDER BY t1.pk,t2.v; DROP TABLE t1,t2,t3; @@ -2531,6 +2577,11 @@ INSERT INTO t2 VALUES (1, 12, 102), (8, 81, 801), (7, 70, 700), (12, 120, 1200), (8, 82, 802), (1, 13, 103), (1, 14, 104), (3, 31, 301), (1, 15, 105), (8, 83, 803), (7, 71, 701); +INSERT INTO t2 VALUES + (108, 80, 800), (101, 10, 100), (101, 11, 101), (103, 30, 300), + (101, 12, 102), (108, 81, 801), (107, 70, 700), (1012, 120, 1200), + (108, 82, 802), (101, 13, 103), (101, 14, 104), (103, 31, 301), + (101, 15, 105), (108, 83, 803), (107, 71, 701); SET SESSION join_cache_level = 4; SET SESSION join_buffer_size = 256; @@ -2567,6 +2618,12 @@ INSERT INTO t2 VALUES ('abcdefjhjk',1015414784), ('or',4), ('now',0), ('abcdefjhjk',-32702464), ('abcdefjhjk',4), ('time',1078394880), ('f',4), ('m',-1845559296), ('ff', 5), ('abcdefjhjk',-1074397184); +INSERT INTO t2 VALUES + ('dig',5), ('were',-1631322112), ('is',3), ('abcdefjhjl',3), + ('abcdefjh',4), ('told',-824573952), ('tt',0),('vv',-1711013888), + ('abcdefjhjj',1015414784), ('and',4), ('here',0), ('abcdefjhjm',-32702464), + ('abcdefjhji',4), ('space',1078394880), ('fs',4), ('mn',-1845559296), + ('fq', 5), ('abcdefjhjp',-1074397184); EXPLAIN SELECT t1.v,t2.i FROM t1,t2 WHERE t2.v = t1.v; @@ -2614,6 +2671,13 @@ INSERT INTO t2 VALUES (11,6,'yes'), (12,NULL,'will'), (13,NULL,'o'), (14,NULL,'k'), (15,NULL,'she'), (16,-1450835968,'abcdefjhjkl'), (17,-975831040,'abcdefjhjkl'), (18,NULL,'z'), (19,-343932928,'t'); +INSERT INTO t2 VALUES + (101,6,'yes'), (102,NULL,'will'), (103,NULL,'o'), (104,NULL,'k'), (105,NULL,'she'), + (106,-1450835968,'abcdefjhjkl'), (107,-975831040,'abcdefjhjkl'), (108,NULL,'z'), + (100,-343932928,'t'), + (111,6,'yes'), (112,NULL,'will'), (113,NULL,'o'), (114,NULL,'k'), (115,NULL,'she'), + (116,-1450835968,'abcdefjhjkl'), (117,-975831040,'abcdefjhjkl'), (118,NULL,'z'), + (119,-343932928,'t'); CREATE TABLE t3 ( pk int NOT NULL PRIMARY KEY, @@ -2630,6 +2694,15 @@ INSERT INTO t3 VALUES (26,NULL,'all'), (27,1443168256,'c'), (28,1427046400,'right'), (31,7,'abcdefjhjkl'), (32,6,'y'), (33,NULL,'to'), (34,7,'n'), (35,7,'look'), (36,NULL,'all'), (37,1443168256,'c'), (38,1427046400,'right'); +INSERT INTO t3 VALUES +(101,7,'abcdefjhjkl'),(102,6,'y'), (103,NULL,'to'),(104,7,'n'),(105,7,'look'), +(106,NULL,'all'), (107,1443168256,'c'), (108,1427046400,'right'), +(111,7,'abcdefjhjkl'), (112,6,'y'), (113,NULL,'to'), (114,7,'n'), (115,7,'look'), +(116,NULL,'all'), (117,1443168256,'c'), (118,1427046400,'right'), +(121,7,'abcdefjhjkl'), (122,6,'y'), (123,NULL,'to'), (124,7,'n'), (125,7,'look'), +(126,NULL,'all'), (127,1443168256,'c'), (128,1427046400,'right'), +(131,7,'abcdefjhjkl'), (132,6,'y'), (133,NULL,'to'), (134,7,'n'), (135,7,'look'), +(136,NULL,'all'), (137,1443168256,'c'), (138,1427046400,'right'); SET SESSION join_buffer_size = 256; @@ -2665,6 +2738,10 @@ INSERT INTO t2 VALUES (10, 'a'), (20, 'c'), (30, 'aa'), (4, 'bb'), (11, 'a'), (21, 'c'), (31, 'aa'), (41, 'cc'), (12, 'a'), (22, 'c'), (32, 'bb'), (42, 'aa'); +INSERT INTO t2 VALUES + (110, 'a'), (120, 'c'), (130, 'aa'), (14, 'bb'), + (111, 'a'), (121, 'c'), (131, 'aa'), (141, 'cc'), + (112, 'a'), (122, 'c'), (132, 'bb'), (142, 'aa'); SELECT * FROM t1,t2 WHERE t2.a=t1.a; @@ -2766,6 +2843,9 @@ INSERT IGNORE INTO t3 VALUES (9,0,0,2), (1,0,0,7); CREATE TABLE t4 (pk int, a4 int, INDEX idx(a4)) ; INSERT IGNORE INTO t4 VALUES (2,NULL), (8,0); +INSERT IGNORE INTO t4 VALUES (12,10), (18,20); +INSERT IGNORE INTO t4 VALUES (22,11), (28,21); +INSERT IGNORE INTO t4 VALUES (32,12), (38,22); CREATE TABLE t5 (pk int, a5 int) ; INSERT IGNORE INTO t5 VALUES (2,0), (8,0); @@ -2941,7 +3021,10 @@ INSERT INTO t1 VALUES INSERT INTO t2 VALUES (1,'Bbbb'), (2,'BBB'), (3,'bbbb'), (4,'AaA'), (5,'CC'), (6,'cC'), (7,'CCC'), (8,'AAA'), (9,'bBbB'), (10,'aaaa'), - (11,'a'), (12,'dd'), (13,'EE'), (14,'ee'), (15,'D'); + (11,'a'), (12,'dd'), (13,'EE'), (14,'ee'), (15,'D'), + (101,'Bbbb'), (102,'BBB'), (103,'bbbb'), (104,'AaA'), (105,'CC'), + (106,'cC'), (107,'CCC'), (108,'AAA'), (109,'bBbB'), (110,'aaaa'), + (111,'a'), (112,'dd'), (113,'EE'), (114,'ee'), (115,'D'); SET SESSION join_cache_level = 4; @@ -3063,7 +3146,10 @@ CREATE TABLE t2 ( f1 varchar(10) , f2 int(11) , KEY (f1)); INSERT INTO t2 VALUES ('hgtofubn',1), ('GDOXZ',91), ('n',2), ('fggxgalh',88), ('hgtofu',1), ('GDO',101), ('n',3), ('fggxga',55), - ('hgtofu',3), ('GDO',33), ('nn',3), ('fggxgarrr',77); + ('hgtofu',3), ('GDO',33), ('nn',3), ('fggxgarrr',77), + ('jgtofu',3), ('JDO',33), ('mn',3), ('jggxgarrr',77), + ('igtofu',3), ('IDO',33), ('ln',3), ('iggxgarrr',77); + SET SESSION join_cache_level=3; @@ -3085,7 +3171,9 @@ INSERT INTO t1 VALUES ('o'), ('u'); CREATE TABLE t2 (a int, v varchar(1), INDEX idx (v)) ; INSERT INTO t2 VALUES (8,NULL), (10,'b'), (5,'k'), (4,NULL), - (1,NULL), (11,'u'), (7,NULL), (2,'d'); + (1,NULL), (11,'u'), (7,NULL), (2,'d'), + (18,'u'), (11,'b'), (15,'k'), (12,'d'), + (18,'x'), (11,'y'), (15,'l'), (12,'e'); SET SESSION join_buffer_size = 256; @@ -3137,5 +3225,34 @@ SET SESSION join_cache_level = DEFAULT; DROP TABLE t1, t2; +--echo # +--echo # Bug #887479: join_cache_level=3 + semijoin=on +--echo # + +CREATE TABLE t1 (a int, b int); +INSERT INTO t1 VALUES (3914,17), (3710,5), (3888,20); + +CREATE TABLE t2 (c int, KEY (c)); +INSERT INTO t2 VALUES (27), (17), (33), (20), (3), (7), (18), (2); + +SET @tmp887479_optimizer_switch=@@optimizer_switch; +SET SESSION optimizer_switch='semijoin=on'; +SET SESSION optimizer_switch='semijoin_with_cache=on'; + +SET SESSION join_cache_level=1; +EXPLAIN +SELECT * FROM t1 WHERE (t1.b) IN (SELECT c FROM t2); +SELECT * FROM t1 WHERE (t1.b) IN (SELECT c FROM t2); + +SET SESSION join_cache_level=3; +EXPLAIN +SELECT * FROM t1 WHERE (t1.b) IN (SELECT c FROM t2); +SELECT * FROM t1 WHERE (t1.b) IN (SELECT c FROM t2); + +SET SESSION join_cache_level = DEFAULT; +SET optimizer_switch=@tmp887479_optimizer_switch; + +DROP TABLE t1,t2; + # this must be the last command in the file set @@optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/t/join_nested.test b/mysql-test/t/join_nested.test index deda56eb8ee..b617331de38 100644 --- a/mysql-test/t/join_nested.test +++ b/mysql-test/t/join_nested.test @@ -454,6 +454,7 @@ SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b ON t3.a=1 AND t2.b=t4.b WHERE t1.a <= 2; +INSERT INTO t2 VALUES (-1,9,0), (-3,10,0), (-2,8,0), (-4,11,0), (-5,15,0); CREATE INDEX idx_b ON t2(b); EXPLAIN EXTENDED @@ -461,12 +462,12 @@ SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b FROM (t3,t4) LEFT JOIN (t1,t2) - ON t3.a=1 AND t3.b=t2.b AND t2.b=t4.b; + ON t3.a=1 AND t3.b=t2.b AND t2.b=t4.b AND t2.a>0; SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b FROM (t3,t4) LEFT JOIN (t1,t2) - ON t3.a=1 AND t3.b=t2.b AND t2.b=t4.b; + ON t3.a=1 AND t3.b=t2.b AND t2.b=t4.b AND t2.a>0; EXPLAIN EXTENDED SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, @@ -477,7 +478,7 @@ SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, t2 LEFT JOIN (t3, t4) - ON t3.a=1 AND t2.b=t4.b, + ON t3.a=1 AND t2.b=t4.b AND t2.a>0, t5 LEFT JOIN ( @@ -504,6 +505,8 @@ SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, (t8.b=t9.b OR t8.c IS NULL) AND (t9.a=1); +INSERT INTO t4 VALUES (-3,12,0), (-4,13,0), (-1,11,0), (-3,11,0), (-5,15,0); +INSERT INTO t5 VALUES (-3,11,0), (-2,12,0), (-3,13,0), (-4,12,0); CREATE INDEX idx_b ON t4(b); CREATE INDEX idx_b ON t5(b); @@ -516,7 +519,7 @@ SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, t2 LEFT JOIN (t3, t4) - ON t3.a=1 AND t2.b=t4.b, + ON t3.a=1 AND t2.b=t4.b AND t2.a>0 AND t4.a>0, t5 LEFT JOIN ( @@ -525,7 +528,7 @@ SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, t8 ON t7.b=t8.b AND t6.b < 10 ) - ON t6.b >= 2 AND t5.b=t7.b + ON t6.b >= 2 AND t5.b=t7.b AND t5.a>0 ) ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND (t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND @@ -543,6 +546,7 @@ SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, (t8.b=t9.b OR t8.c IS NULL) AND (t9.a=1); +INSERT INTO t8 VALUES (-3,12,0), (-1,14,0), (-5,15,0), (-1,11,0), (-4,13,0); CREATE INDEX idx_b ON t8(b); EXPLAIN EXTENDED @@ -554,16 +558,16 @@ SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, t2 LEFT JOIN (t3, t4) - ON t3.a=1 AND t2.b=t4.b, + ON t3.a=1 AND t2.b=t4.b AND t2.a>0 AND t4.a>0, t5 LEFT JOIN ( (t6, t7) LEFT JOIN t8 - ON t7.b=t8.b AND t6.b < 10 + ON t7.b=t8.b AND t6.b < 10 AND t8.a>=0 ) - ON t6.b >= 2 AND t5.b=t7.b + ON t6.b >= 2 AND t5.b=t7.b AND t5.a>0 ) ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND (t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND @@ -581,6 +585,7 @@ SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, (t8.b=t9.b OR t8.c IS NULL) AND (t9.a=1); +INSERT INTO t1 VALUES (-1,133,0), (-2,12,0), (-3,11,0), (-5,15,0); CREATE INDEX idx_b ON t1(b); CREATE INDEX idx_a ON t0(a); @@ -606,7 +611,7 @@ SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, ) ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND (t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND - (t1.a != 2), + (t1.a != 2) AND t1.a>0, t9 WHERE t0.a=1 AND t0.b=t1.b AND @@ -641,7 +646,7 @@ SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, ) ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND (t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND - (t1.a != 2), + (t1.a != 2) AND t1.a>0, t9 WHERE t0.a=1 AND t0.b=t1.b AND diff --git a/mysql-test/t/join_nested_jcl6.test b/mysql-test/t/join_nested_jcl6.test index 6b04d8d58b5..809755b1fbf 100644 --- a/mysql-test/t/join_nested_jcl6.test +++ b/mysql-test/t/join_nested_jcl6.test @@ -26,6 +26,9 @@ INSERT INTO t5 VALUES (1,1,0), (2,2,0), (3,3,0); INSERT INTO t6 VALUES (1,2,0), (3,2,0), (6,1,0); INSERT INTO t7 VALUES (1,1,0), (2,2,0); INSERT INTO t8 VALUES (0,2,0), (1,2,0); +INSERT INTO t6 VALUES (-1,12,0), (-3,13,0), (-6,11,0), (-4,14,0); +INSERT INTO t7 VALUES (-1,11,0), (-2,12,0), (-3,13,0), (-4,14,0), (-5,15,0); +INSERT INTO t8 VALUES (-3,13,0), (-1,12,0), (-2,14,0), (-5,15,0), (-4,16,0); EXPLAIN SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b @@ -35,10 +38,10 @@ SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b (t6, t7) LEFT JOIN t8 - ON t7.b=t8.b AND t6.b < 10 + ON t7.b=t8.b AND t6.b < 10 ) ON t6.b >= 2 AND t5.b=t7.b AND - (t8.a > 0 OR t8.c IS NULL); + (t8.a > 0 OR t8.c IS NULL) AND t6.a>0 AND t7.a>0; SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b FROM t5 @@ -50,7 +53,7 @@ SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b ON t7.b=t8.b AND t6.b < 10 ) ON t6.b >= 2 AND t5.b=t7.b AND - (t8.a > 0 OR t8.c IS NULL); + (t8.a > 0 OR t8.c IS NULL) AND t6.a>0 AND t7.a>0; DELETE FROM t5; DELETE FROM t6; diff --git a/mysql-test/t/join_outer.test b/mysql-test/t/join_outer.test index f98dbcdf7ac..059606d9f86 100644 --- a/mysql-test/t/join_outer.test +++ b/mysql-test/t/join_outer.test @@ -1329,6 +1329,8 @@ insert into t2 select if(t1.a is null, 10, t1.a) from t1; create table t3 (a int, b int, index idx(a)); insert into t3 values (1, 100), (3, 301), (4, 402), (1, 102), (1, 101); +insert into t3 values (11, 100), (33, 301), (44, 402), (11, 102), (11, 101); +insert into t3 values (22, 100), (53, 301), (64, 402), (22, 102), (22, 101); analyze table t1,t2,t3; @@ -1419,3 +1421,56 @@ SELECT t2.b FROM t1 LEFT JOIN t2 ON (t2.b) IN (SELECT c2 from t3) AND t2.a = 1; DROP TABLE t1,t2,t3; + +--echo # +--echo # lp:825035 second execution of PS with outer join +--echo # + +CREATE TABLE t1 (a int); +INSERT INTO t1 VALUES (1),(2),(3),(4); + +CREATE TABLE t2 (a int); + +PREPARE stmt FROM +"SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a"; + +EXECUTE stmt; +EXECUTE stmt; + +DEALLOCATE PREPARE stmt; + +DROP TABLE t1,t2; + +--echo # +--echo # lp:838633 second execution of PS with outer join +--echo # converted to inner join +--echo # + +CREATE TABLE t1 ( b int NOT NULL ) ; +INSERT INTO t1 VALUES (9),(10); + +CREATE TABLE t2 ( b int NOT NULL, PRIMARY KEY (b)) ; +INSERT INTO t2 VALUES + (75),(76),(77),(78),(79),(80),(81),(82),(83),(84),(85),(86),(87),(88),(89), + (10), (90),(91),(92),(93),(94),(95),(96),(97),(98),(99),(100); + +CREATE TABLE t3 ( a int, b int NOT NULL , PRIMARY KEY (b)) ; +INSERT INTO t3 VALUES + (0,6),(0,7),(0,8),(2,9),(0,10),(2,21),(0,22),(2,23),(2,24),(2,25); + +SET SESSION join_cache_level=4; + +EXPLAIN EXTENDED +SELECT * FROM (t2 LEFT JOIN t1 ON t1.b = t2.b) JOIN t3 ON t1.b = t3.b; + +PREPARE stmt FROM +'SELECT * FROM (t2 LEFT JOIN t1 ON t1.b = t2.b) JOIN t3 ON t1.b = t3.b'; + +EXECUTE stmt; +EXECUTE stmt; + +DEALLOCATE PREPARE stmt; + +SET SESSION join_cache_level=default; + +DROP TABLE t1,t2,t3; diff --git a/mysql-test/t/kill.test b/mysql-test/t/kill.test index 4aa765caa8c..7c6c37ecfc6 100644 --- a/mysql-test/t/kill.test +++ b/mysql-test/t/kill.test @@ -349,7 +349,7 @@ SET DEBUG_SYNC = 'RESET'; let $ID= `SELECT @id := CONNECTION_ID()`; SET DEBUG_SYNC= 'thread_end SIGNAL con1_end'; --disable_reconnect ---error ER_QUERY_INTERRUPTED +--error ER_CONNECTION_KILLED KILL @id; connection con2; @@ -604,5 +604,33 @@ drop table t2; ########################################################################### +--echo # +--echo # Test kill USER +--echo # +grant ALL on test.* to test@localhost; +grant ALL on test.* to test2@localhost; +connect (con3, localhost, test,,); +connect (con4, localhost, test2,,); +connection default; +--enable_info +kill hard query user test2@nohost; +kill soft query user test@localhost; +kill hard query user test@localhost; +kill soft connection user test2; +kill hard connection user test@localhost; +--disable_info +revoke all privileges on test.* from test@localhost; +revoke all privileges on test.* from test2@localhost; +drop user test@localhost; +drop user test2@localhost; + +connection con3; +--error 2013,2006 +select 1; +connection con4; +--error 2013,2006 +select 1; +connection default; + SET DEBUG_SYNC = 'RESET'; DROP FUNCTION MY_KILL; diff --git a/mysql-test/t/maria_mrr.test b/mysql-test/t/maria_mrr.test index 9d26a01d5e0..d67572033c8 100644 --- a/mysql-test/t/maria_mrr.test +++ b/mysql-test/t/maria_mrr.test @@ -74,7 +74,9 @@ CREATE TABLE t3( PRIMARY KEY (pk), INDEX idx (v, i) ) ENGINE=ARIA; INSERT INTO t3 SELECT * FROM t1; -INSERT INTO t3 VALUES (88, 442, 'y'), (99, 445, 'w') ; +INSERT INTO t3 VALUES + (88, 442, 'y'), (99, 445, 'w'), (87, 442, 'z'), (98, 445, 'v'), (86, 442, 'x'), + (97, 445, 't'), (85, 442, 'b'), (96, 445, 'l'), (84, 442, 'a'), (95, 445, 'k'); SELECT COUNT(t1.v) FROM t1, t2 IGNORE INDEX (idx), t3 IGNORE INDEX (idx) WHERE t3.v = t2.v AND t3.i < t2.i AND t3.pk > 0 AND t2.pk > 0; @@ -105,11 +107,8 @@ CREATE TABLE t1 ( ) ENGINE=Aria; INSERT INTO t1 VALUES -(1,'z'), -(2,'abcdefjhjkl'), -(3,'in'), -(4,'abcdefjhjkl'), -(6,'abcdefjhjkl'); + (1,'z'), (2,'abcdefjhjkl'), (3,'in'), (4,'abcdefjhjkl'), (6,'abcdefjhjkl'), + (11,'zx'), (12,'abcdefjhjm'), (13,'jn'), (14,'abcdefjhjp'), (16,'abcdefjhjr'); CREATE TABLE t2 ( col_varchar_10_latin1 varchar(10) DEFAULT NULL diff --git a/mysql-test/t/mrr_icp_extra.test b/mysql-test/t/mrr_icp_extra.test index 2d0fd527dcf..f1b21154449 100644 --- a/mysql-test/t/mrr_icp_extra.test +++ b/mysql-test/t/mrr_icp_extra.test @@ -57,6 +57,7 @@ DROP TABLE t1,t2,t3; --echo # create table t1(a int, b int, index(b)); insert into t1 values (2, 1), (1, 1), (4, NULL), (3, NULL), (6, 2), (5, 2); +insert into t1 values (2, 11), (1, 11), (4, 14), (3, 14), (6, 12), (5, 12); explain select * from t1 where b=1 or b is null order by a; select * from t1 where b=1 or b is null order by a; explain select * from t1 where b=2 or b is null order by a; diff --git a/mysql-test/t/myisam.test b/mysql-test/t/myisam.test index cab06a03498..fa82be90427 100644 --- a/mysql-test/t/myisam.test +++ b/mysql-test/t/myisam.test @@ -350,7 +350,7 @@ DROP TABLE t1; # CREATE TABLE t1 (a int not null, b int, c int, key(b), key(c), key(a,b), key(c,a)); -INSERT into t1 values (0, null, 0), (0, null, 1), (0, null, 2), (0, null,3), (1,1,4); +INSERT into t1 values (0,null,0), (0,null,1), (0,null,2), (0,null,3), (1,1,4); create table t2 (a int not null, b int, c int, key(b), key(c), key(a)); INSERT into t2 values (1,1,1), (2,2,2); optimize table t1; @@ -358,7 +358,13 @@ show index from t1; explain select * from t1,t2 where t1.a=t2.a; explain select * from t1,t2 force index(a) where t1.a=t2.a; explain select * from t1 force index(a),t2 force index(a) where t1.a=t2.a; +INSERT into t1 values (2,4,5), (7,8,4), (8,3,1), (9,7,2), (5,5,9); +optimize table t1; +show index from t1; explain select * from t1,t2 where t1.b=t2.b; +delete from t1 where t1.a>1; +optimize table t1; +show index from t1; explain select * from t1,t2 force index(c) where t1.a=t2.a; explain select * from t1 where a=0 or a=2; explain select * from t1 force index (a) where a=0 or a=2; diff --git a/mysql-test/t/myisam_icp.test b/mysql-test/t/myisam_icp.test index 66ffbfa3821..bbff6c30e56 100644 --- a/mysql-test/t/myisam_icp.test +++ b/mysql-test/t/myisam_icp.test @@ -2,11 +2,11 @@ # ICP/MyISAM tests (Index Condition Pushdown) # ---source include/icp_tests.inc - set @myisam_icp_tmp=@@optimizer_switch; set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; +--source include/icp_tests.inc + --disable_warnings drop table if exists t0, t1, t1i, t1m; --enable_warnings @@ -209,4 +209,52 @@ INSERT INTO t1 VALUES SELECT b FROM t1 WHERE a != 1 AND c IS NULL ORDER BY 1; DROP TABLE t1; +--echo # +--echo # Bug#870046: ICP for a GROUP BY query +--echo # + +CREATE TABLE t1 (a int, b varchar(1), c varchar(1), INDEX idx(b)); +INSERT INTO t1 VALUES (2,'x','x'), (5,'x','y'); + +SET SESSION optimizer_switch='index_condition_pushdown=off'; +EXPLAIN +SELECT a, MIN(c) FROM t1 WHERE b = 'x' AND c > 'x' GROUP BY a; +SELECT a, MIN(c) FROM t1 WHERE b = 'x' AND c > 'x' GROUP BY a; +SET SESSION optimizer_switch='index_condition_pushdown=on'; +EXPLAIN +SELECT a, MIN(c) FROM t1 WHERE b = 'x' AND c > 'x' GROUP BY a; +SELECT a, MIN(c) FROM t1 WHERE b = 'x' AND c > 'x' GROUP BY a; + +DROP TABLE t1; + +--echo # +--echo # BUG#887026: Wrong result with ICP, outer join, subquery in maria-5.3-icp +--echo # + +CREATE TABLE t1 (c varchar(1)); +INSERT INTO t1 VALUES ('c'), ('c'); + +CREATE TABLE t2 (c varchar(1), b int); +INSERT INTO t2 VALUES ('d', NULL),('d', NULL); + +CREATE TABLE t3 (c varchar(1)); +INSERT INTO t3 VALUES ('c'); +INSERT INTO t3 VALUES ('c'); + +CREATE TABLE t4 ( b int, c varchar(1), KEY (b)); +INSERT INTO t4 VALUES (7,'c'); +INSERT INTO t4 VALUES (7,'c'); + +--echo # Must be t1,t2,t3,t4, with t4 having Full-scan-on-NULL but not Using index condition +explain +SELECT * FROM t1 LEFT JOIN t2 ON t1.c=t2.b +WHERE + t2.b NOT IN (SELECT t4.b FROM t3 STRAIGHT_JOIN t4 WHERE t4.b <= 2 AND t4.c = t3.c); + +SELECT * FROM t1 LEFT JOIN t2 ON t1.c=t2.b +WHERE + t2.b NOT IN (SELECT t4.b FROM t3 STRAIGHT_JOIN t4 WHERE t4.b <= 2 AND t4.c = t3.c); + +DROP TABLE t1,t2,t3,t4; + set optimizer_switch=@myisam_icp_tmp; diff --git a/mysql-test/t/myisam_mrr.test b/mysql-test/t/myisam_mrr.test index 9c4c7be4fa2..1e070ec9a34 100644 --- a/mysql-test/t/myisam_mrr.test +++ b/mysql-test/t/myisam_mrr.test @@ -199,6 +199,14 @@ INSERT INTO t1 VALUES (22,142,'b','b'),(23,3,'y','y'),(24,0,'v','v'), (25,3,'m','m'),(26,5,'z','z'),(27,9,'n','n'), (28,1,'d','d'),(29,107,'a','a'); +INSERT INTO t1 VALUES + (110,8,'v','v'),(111,8,'f','f'), (112,5,'v','v'), + (113,8,'s','s'),(114,8,'a','a'),(115,6,'p','p'), + (116,7,'z','z'),(117,2,'a','a'),(118,5,'h','h'), + (119,7,'h','h'),(120,2,'v','v'),(121,9,'v','v'), + (122,142,'b','b'),(123,3,'y','y'),(124,0,'v','v'), + (125,3,'m','m'),(126,5,'z','z'),(127,9,'n','n'), + (128,1,'d','d'),(129,107,'a','a'); SELECT COUNT(*) FROM @@ -218,6 +226,47 @@ where set join_cache_level= @save_join_cache_level; set join_buffer_size= @save_join_buffer_size; -set optimizer_switch= @myisam_mrr_tmp; drop table t1; + +--echo # +--echo # BUG#730133: Wrong result with jkl = 7, BKA, ICP in maria-5.3 + compound index +--echo # +set @tmp_730133_jcl= @@join_cache_level; +set join_cache_level = 7; + +set @tmp_730133_os= @@optimizer_switch; +set optimizer_switch= 'join_cache_hashed=off,join_cache_bka=on,index_condition_pushdown=on,optimize_join_buffer_size=on'; + +CREATE TABLE t1 (f1 int, f2 int, f3 int, f4 int, f5 int, KEY (f4,f3)); +INSERT IGNORE INTO t1 VALUES ('2','9','5','0','0'),('4','7','0','0','0'), + ('6','97','190','0','0'),('7','3','6','0','0'),('11','101','186','0','0'), + ('14','194','226','0','0'),('15','148','133','0','0'), + ('16','9','6','0','0'),('17','9','3','0','0'),('18','1','8','0','0'), + ('19','1','5','0','0'),('20','5','7','0','0'); + +explain +SELECT COUNT(alias2.f2) +FROM + t1 STRAIGHT_JOIN + t1 AS alias3 STRAIGHT_JOIN + t1 AS alias2 FORCE KEY (f4) +WHERE + alias2.f4=alias3.f5 AND + alias2.f3 > alias3.f1; + +SELECT COUNT(alias2.f2) +FROM + t1 STRAIGHT_JOIN + t1 AS alias3 STRAIGHT_JOIN + t1 AS alias2 FORCE KEY (f4) +WHERE + alias2.f4=alias3.f5 AND + alias2.f3 > alias3.f1; + +set @@join_cache_level= @tmp_730133_jcl; +set @@optimizer_switch= @tmp_730133_os; +drop table t1; + +## This must be last line in the file: +set optimizer_switch= @myisam_mrr_tmp; diff --git a/mysql-test/t/mysql.test b/mysql-test/t/mysql.test index 52d9b52b26e..069cf37c3e6 100644 --- a/mysql-test/t/mysql.test +++ b/mysql-test/t/mysql.test @@ -6,7 +6,7 @@ # --disable_warnings -drop table if exists t1; +drop table if exists t1,t2,t3; --enable_warnings # @@ -124,6 +124,13 @@ drop table t1; --exec echo "use" > $file --exec $MYSQL < $file 2>&1 +# Test exceutable comments +--exec echo "SELECT 1 /*! +1 */;" > $file +--exec echo "SELECT 1 /*M! +1 */;" >> $file +--exec echo "SELECT 1 /*!00000 +1 */;" >> $file +--exec echo "SELECT 1 /*M!00000 +1 */" >> $file +--exec $MYSQL < $file 2>&1 + --remove_file $file # diff --git a/mysql-test/t/optimizer_switch_eng_cond_pushdown1-master.opt b/mysql-test/t/optimizer_switch_eng_cond_pushdown1-master.opt deleted file mode 100644 index 89aa07976ac..00000000000 --- a/mysql-test/t/optimizer_switch_eng_cond_pushdown1-master.opt +++ /dev/null @@ -1 +0,0 @@ ---optimizer-switch=engine_condition_pushdown=off --engine-condition-pushdown=1 diff --git a/mysql-test/t/optimizer_switch_eng_cond_pushdown1.test b/mysql-test/t/optimizer_switch_eng_cond_pushdown1.test deleted file mode 100644 index 187aa145408..00000000000 --- a/mysql-test/t/optimizer_switch_eng_cond_pushdown1.test +++ /dev/null @@ -1,5 +0,0 @@ -# check how --engine-condition-pushdown and --optimizer-switch -# influence each other when used together (last wins). -select @@session.engine_condition_pushdown, -@@global.engine_condition_pushdown, -@@session.optimizer_switch, @@global.optimizer_switch; diff --git a/mysql-test/t/optimizer_switch_eng_cond_pushdown2-master.opt b/mysql-test/t/optimizer_switch_eng_cond_pushdown2-master.opt deleted file mode 100644 index f48ab5b963b..00000000000 --- a/mysql-test/t/optimizer_switch_eng_cond_pushdown2-master.opt +++ /dev/null @@ -1 +0,0 @@ ---engine-condition-pushdown=1 --optimizer-switch=engine_condition_pushdown=off diff --git a/mysql-test/t/optimizer_switch_eng_cond_pushdown2.test b/mysql-test/t/optimizer_switch_eng_cond_pushdown2.test deleted file mode 100644 index 187aa145408..00000000000 --- a/mysql-test/t/optimizer_switch_eng_cond_pushdown2.test +++ /dev/null @@ -1,5 +0,0 @@ -# check how --engine-condition-pushdown and --optimizer-switch -# influence each other when used together (last wins). -select @@session.engine_condition_pushdown, -@@global.engine_condition_pushdown, -@@session.optimizer_switch, @@global.optimizer_switch; diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test index 99945a82c7f..d514ae88afd 100644 --- a/mysql-test/t/order_by.test +++ b/mysql-test/t/order_by.test @@ -440,6 +440,7 @@ drop table t1; create table t1(a int, b int, index(b)); insert into t1 values (2, 1), (1, 1), (4, NULL), (3, NULL), (6, 2), (5, 2); +insert into t1 values (12, 11), (11, 11), (14, 3), (13, 5), (16, 12), (15, 12); explain select * from t1 where b=1 or b is null order by a; select * from t1 where b=1 or b is null order by a; explain select * from t1 where b=2 or b is null order by a; diff --git a/mysql-test/t/partition_pruning.test b/mysql-test/t/partition_pruning.test index f2ca16b9259..da41b8e9428 100644 --- a/mysql-test/t/partition_pruning.test +++ b/mysql-test/t/partition_pruning.test @@ -892,6 +892,7 @@ drop table t1; create table t1 (a int not null, b int not null, key(a), key(b)) partition by hash(a) partitions 4; insert into t1 values (1,1),(2,2),(3,3),(4,4); +insert into t1 values (5,5),(6,6),(7,7),(8,8); explain partitions select * from t1 X, t1 Y diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test index 0a34bac32ba..746d6bad896 100644 --- a/mysql-test/t/range.test +++ b/mysql-test/t/range.test @@ -554,6 +554,18 @@ INSERT INTO t1 VALUES 'd8c4177d09f8b11f5.52725521'), ('d8c4177d24ccef970.14957924','d8c4177d09f8b11f5.52725521',10,11, 'd8c4177d09f8b11f5.52725521'); +INSERT INTO t1 VALUES +('d8c4177d09f8b11f5.52725522','oxrootid',1,40,'d8c4177d09f8b11f5.52725522'), +('d8c4177d151affab2.81582771','d8c4177d09f8b11f5.52725521',2,3, + 'd8c4177d09f8b11f5.52725522'), +('d8c4177d206a333d2.74422678','d8c4177d09f8b11f5.52725521',4,5, + 'd8c4177d09f8b11f5.52725522'), +('d8c4177d225791924.30714721','d8c4177d09f8b11f5.52725521',6,7, + 'd8c4177d09f8b11f5.52725522'), +('d8c4177d2380fc201.39666694','d8c4177d09f8b11f5.52725521',8,9, + 'd8c4177d09f8b11f5.52725522'), +('d8c4177d24ccef970.14957925','d8c4177d09f8b11f5.52725521',10,11, + 'd8c4177d09f8b11f5.52725522'); EXPLAIN SELECT s.oxid FROM t1 v, t1 s diff --git a/mysql-test/t/range_vs_index_merge.test b/mysql-test/t/range_vs_index_merge.test index 1f7065dcb0a..2ffa2d8eb49 100755 --- a/mysql-test/t/range_vs_index_merge.test +++ b/mysql-test/t/range_vs_index_merge.test @@ -1029,5 +1029,34 @@ SELECT * FROM t1,t2,t3 DROP TABLE t1,t2,t3; +# +# LP bug #823301: index merge union with prossible index scan +# +# + +CREATE TABLE t1 ( + a int, b int, c int, d int, + PRIMARY KEY(b), INDEX idx1(d), INDEX idx2(d,b,c) +); +INSERT INTO t1 VALUES + (0,58,7,7),(0,63,2,0),(0,64,186,8),(0,65,1,-2), (0,71,190,-3), + (0,72,321,-7),(0,73,0,3),(0,74,5,25),(0,75,5,3); + +SET SESSION optimizer_switch='index_merge_sort_union=off'; +EXPLAIN +SELECT * FROM t1 + WHERE t1.b>7 AND t1.d>1 AND t1.d<>8 OR t1.d>=7 AND t1.d<8 OR t1.d>7; +SELECT * FROM t1 + WHERE t1.b>7 AND t1.d>1 AND t1.d<>8 OR t1.d>=7 AND t1.d<8 OR t1.d>7; +SET SESSION optimizer_switch='index_merge_sort_union=on'; +EXPLAIN +SELECT * FROM t1 + WHERE t1.b>7 AND t1.d>1 AND t1.d<>8 OR t1.d>=7 AND t1.d<8 OR t1.d>7; +SELECT * FROM t1 + WHERE t1.b>7 AND t1.d>1 AND t1.d<>8 OR t1.d>=7 AND t1.d<8 OR t1.d>7; +SET SESSION optimizer_switch=DEFAULT; + +DROP TABLE t1; + #the following command must be the last one in the file set session optimizer_switch='index_merge_sort_intersection=default'; diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test index e9ae69826e5..3781f7a5d43 100644 --- a/mysql-test/t/select.test +++ b/mysql-test/t/select.test @@ -1966,6 +1966,7 @@ CREATE TABLE t2 ( a BLOB, INDEX (a(20)) ); INSERT INTO t1 VALUES ('one'),('two'),('three'),('four'),('five'); INSERT INTO t2 VALUES ('one'),('two'),('three'),('four'),('five'); +INSERT INTO t2 VALUES ('one'),('two'),('three'),('four'),('five'); EXPLAIN SELECT * FROM t1 LEFT JOIN t2 USE INDEX (a) ON t1.a=t2.a; EXPLAIN SELECT * FROM t1 LEFT JOIN t2 FORCE INDEX (a) ON t1.a=t2.a; diff --git a/mysql-test/t/select_safe.test b/mysql-test/t/select_safe.test index cca736d4c1d..c76e337cd10 100644 --- a/mysql-test/t/select_safe.test +++ b/mysql-test/t/select_safe.test @@ -58,6 +58,8 @@ SELECT * from t1; # analyze table t1; insert into t1 values (null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"); +insert into t1 values (null,"b"),(null,"b"),(null,"c"),(null,"c"),(null,"d"),(null,"d"),(null,"e"),(null,"e"),(null,"a"),(null,"e"); +insert into t1 values (null,"x"),(null,"x"),(null,"y"),(null,"y"),(null,"z"),(null,"z"),(null,"v"),(null,"v"),(null,"a"),(null,"v"); explain select STRAIGHT_JOIN * from t1,t1 as t2 where t1.b=t2.b; set MAX_SEEKS_FOR_KEY=1; explain select STRAIGHT_JOIN * from t1,t1 as t2 where t1.b=t2.b; diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 0b368f5be67..6369e4edbeb 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -14,7 +14,7 @@ drop view if exists v2; set @subselect_tmp=@@optimizer_switch; set @@optimizer_switch=ifnull(@optimizer_switch_for_subselect_test, - "semijoin=on,firstmatch=on,loosescan=on,partial_match_rowid_merge=off,partial_match_table_scan=off"); + "semijoin=on,firstmatch=on,loosescan=on,semijoin_with_cache=on,partial_match_rowid_merge=off,partial_match_table_scan=off"); set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; select (select 2); @@ -292,8 +292,9 @@ SELECT (SELECT numeropost FROM t1 HAVING numreponse=a),numreponse FROM (SELECT * SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=a) FROM (SELECT * FROM t1) as a; SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=1) FROM (SELECT * FROM t1) as a; INSERT INTO t1 (numeropost,numreponse,pseudo) VALUES (1,1,'joce'),(1,2,'joce'),(1,3,'test'); --- error ER_SUBQUERY_NO_1_ROW EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1'); +-- error ER_SUBQUERY_NO_1_ROW +SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1'); EXPLAIN EXTENDED SELECT MAX(numreponse) FROM t1 WHERE numeropost='1'; EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT MAX(numreponse) FROM t1 WHERE numeropost='1'); drop table t1; @@ -503,6 +504,54 @@ SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1; explain extended SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1; drop table t1,t2,t3; +--echo # check correct NULL Processing for normal IN/ALL/ANY +--echo # and 2 ways of max/min optimization +create table t1 (a int); +insert into t1 values (1), (100), (NULL), (1000); +create table t2 (a int not null); + +--echo # subselect returns empty set (for NULL and non-NULL left part) +select a, a in (select * from t2) from t1; +select a, a > any (select * from t2) from t1; +select a, a > all (select * from t2) from t1; +select a from t1 where a in (select * from t2); +select a from t1 where a > any (select * from t2); +select a from t1 where a > all (select * from t2); +select a from t1 where a in (select * from t2 group by a); +select a from t1 where a > any (select * from t2 group by a); +select a from t1 where a > all (select * from t2 group by a); + +insert into t2 values (1),(200); + +--echo # sebselect returns non-empty set without NULLs +select a, a in (select * from t2) from t1; +select a, a > any (select * from t2) from t1; +select a, a > all (select * from t2) from t1; +select a from t1 where a in (select * from t2); +select a from t1 where a > any (select * from t2); +select a from t1 where a > all (select * from t2); +select a from t1 where a in (select * from t2 group by a); +select a from t1 where a > any (select * from t2 group by a); +select a from t1 where a > all (select * from t2 group by a); + +drop table t2; +create table t2 (a int); +insert into t2 values (1),(NULL),(200); + +--echo # sebselect returns non-empty set with NULLs +select a, a in (select * from t2) from t1; +select a, a > any (select * from t2) from t1; +select a, a > all (select * from t2) from t1; +select a from t1 where a in (select * from t2); +select a from t1 where a > any (select * from t2); +select a from t1 where a > all (select * from t2); +select a from t1 where a in (select * from t2 group by a); +select a from t1 where a > any (select * from t2 group by a); +select a from t1 where a > all (select * from t2 group by a); + + +drop table t1, t2; + #LIMIT is not supported now create table t1 (a float); -- error ER_NOT_SUPPORTED_YET @@ -934,7 +983,7 @@ drop table t1,t2; # # correct ALL optimisation # -create table t2 (a int, b int); +create table t2 (a int, b int not null); create table t3 (a int); insert into t3 values (6),(7),(3); select * from t3 where a >= all (select b from t2); @@ -1817,6 +1866,9 @@ drop table t1; # Bug#11867 queries with ROW(,elems>) IN (SELECT DISTINCT <cols> FROM ...) # +set @tmp11867_optimizer_switch=@@optimizer_switch; +set optimizer_switch='semijoin_with_cache=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'); @@ -1844,6 +1896,7 @@ explain extended SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FR 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_switch=@tmp11867_optimizer_switch; # # Bug#12392 where cond with IN predicate for rows and NULL values in table @@ -4722,6 +4775,87 @@ SELECT 1 as foo FROM t1 WHERE a < SOME DROP TABLE t1; +# +# LP BUG#823169 NULLs with ALL/ANY and maxmin optimization +# +CREATE TABLE t1 (a int(11), b varchar(1)); +INSERT INTO t1 VALUES (2,NULL),(5,'d'),(7,'g'); + +SELECT a FROM t1 WHERE b < ANY ( SELECT b FROM t1 GROUP BY b ); +SELECT a FROM t1 WHERE b < ANY ( SELECT b FROM t1 ); +SELECT a FROM t1 WHERE b > ANY ( SELECT b FROM t1 GROUP BY b ); +SELECT a FROM t1 WHERE b > ANY ( SELECT b FROM t1 ); +SELECT a FROM t1 WHERE b <= ANY ( SELECT b FROM t1 GROUP BY b ); +SELECT a FROM t1 WHERE b <= ANY ( SELECT b FROM t1 ); +SELECT a FROM t1 WHERE b >= ANY ( SELECT b FROM t1 GROUP BY b ); +SELECT a FROM t1 WHERE b >= ANY ( SELECT b FROM t1 ); +SELECT a FROM t1 WHERE b = ANY ( SELECT b FROM t1 ); +SELECT a FROM t1 WHERE b = ANY ( SELECT b FROM t1 GROUP BY b ); +SELECT a FROM t1 WHERE b <> ANY ( SELECT b FROM t1 ); +SELECT a FROM t1 WHERE b <> ANY ( SELECT b FROM t1 GROUP BY b ); + +SELECT a FROM t1 WHERE b < ALL ( SELECT b FROM t1 GROUP BY b ); +SELECT a FROM t1 WHERE b < ALL ( SELECT b FROM t1 ); +SELECT a FROM t1 WHERE b > ALL ( SELECT b FROM t1 GROUP BY b ); +SELECT a FROM t1 WHERE b > ALL ( SELECT b FROM t1 ); +SELECT a FROM t1 WHERE b <= ALL ( SELECT b FROM t1 GROUP BY b ); +SELECT a FROM t1 WHERE b <= ALL ( SELECT b FROM t1 ); +SELECT a FROM t1 WHERE b >= ALL ( SELECT b FROM t1 GROUP BY b ); +SELECT a FROM t1 WHERE b >= ALL ( SELECT b FROM t1 ); +SELECT a FROM t1 WHERE b = ALL ( SELECT b FROM t1 ); +SELECT a FROM t1 WHERE b = ALL ( SELECT b FROM t1 GROUP BY b ); +SELECT a FROM t1 WHERE b <> ALL ( SELECT b FROM t1 ); +SELECT a FROM t1 WHERE b <> ALL ( SELECT b FROM t1 GROUP BY b ); + +delete from t1; +INSERT INTO t1 VALUES (2,NULL),(5,'d'),(7,'g'); + +SELECT a FROM t1 WHERE b < ANY ( SELECT b FROM t1 GROUP BY b ); +SELECT a FROM t1 WHERE b < ANY ( SELECT b FROM t1 ); +SELECT a FROM t1 WHERE b > ANY ( SELECT b FROM t1 GROUP BY b ); +SELECT a FROM t1 WHERE b > ANY ( SELECT b FROM t1 ); +SELECT a FROM t1 WHERE b <= ANY ( SELECT b FROM t1 GROUP BY b ); +SELECT a FROM t1 WHERE b <= ANY ( SELECT b FROM t1 ); +SELECT a FROM t1 WHERE b >= ANY ( SELECT b FROM t1 GROUP BY b ); +SELECT a FROM t1 WHERE b >= ANY ( SELECT b FROM t1 ); +SELECT a FROM t1 WHERE b = ANY ( SELECT b FROM t1 ); +SELECT a FROM t1 WHERE b = ANY ( SELECT b FROM t1 GROUP BY b ); +SELECT a FROM t1 WHERE b <> ANY ( SELECT b FROM t1 ); +SELECT a FROM t1 WHERE b <> ANY ( SELECT b FROM t1 GROUP BY b ); + +SELECT a FROM t1 WHERE b < ALL ( SELECT b FROM t1 GROUP BY b ); +SELECT a FROM t1 WHERE b < ALL ( SELECT b FROM t1 ); +SELECT a FROM t1 WHERE b > ALL ( SELECT b FROM t1 GROUP BY b ); +SELECT a FROM t1 WHERE b > ALL ( SELECT b FROM t1 ); +SELECT a FROM t1 WHERE b <= ALL ( SELECT b FROM t1 GROUP BY b ); +SELECT a FROM t1 WHERE b <= ALL ( SELECT b FROM t1 ); +SELECT a FROM t1 WHERE b >= ALL ( SELECT b FROM t1 GROUP BY b ); +SELECT a FROM t1 WHERE b >= ALL ( SELECT b FROM t1 ); +SELECT a FROM t1 WHERE b = ALL ( SELECT b FROM t1 ); +SELECT a FROM t1 WHERE b = ALL ( SELECT b FROM t1 GROUP BY b ); +SELECT a FROM t1 WHERE b <> ALL ( SELECT b FROM t1 ); +SELECT a FROM t1 WHERE b <> ALL ( SELECT b FROM t1 GROUP BY b ); + +drop table t1; + +--echo # +--echo # Fix of lp:780386 (NULL left part with empty ALL subquery). +--echo # +CREATE TABLE t1 ( f11 int) ; +INSERT IGNORE INTO t1 VALUES (0),(0); + +CREATE TABLE t2 ( f3 int, f10 int, KEY (f10,f3)) ; +INSERT IGNORE INTO t2 VALUES (NULL,NULL),(5,0); + +DROP TABLE IF EXISTS t3; +CREATE TABLE t3 ( f3 int) ; +INSERT INTO t3 VALUES (0),(0); + +SELECT a1.f3 AS r FROM t2 AS a1 , t1 WHERE a1.f3 < ALL ( SELECT f3 FROM t3 WHERE f3 = 1 ) ; +DROP TABLE t1, t2, t3; + +--echo End of 5.2 tests + --echo # --echo # BUG#779885: Crash in eliminate_item_equal with materialization=on in --echo # maria-5.3 @@ -4748,20 +4882,29 @@ WHERE DROP TABLE t1,t2,t3; --echo # ---echo # Fix of LP BUG#780386 (NULL left part with empty ALL subquery). +--echo # BUG lp:813473: Wrong result with outer join + NOT IN subquery +--echo # This bug is a duplicate of Bug#11764086 whose test case is added below --echo # -CREATE TABLE t1 ( f11 int) ; -INSERT IGNORE INTO t1 VALUES (0),(0); -CREATE TABLE t2 ( f3 int, f10 int, KEY (f10,f3)) ; -INSERT IGNORE INTO t2 VALUES (NULL,NULL),(5,0); +CREATE TABLE t1 (c int) ; +INSERT INTO t1 VALUES (5),(6); -DROP TABLE IF EXISTS t3; -CREATE TABLE t3 ( f3 int) ; -INSERT INTO t3 VALUES (0),(0); +CREATE TABLE t2 (a int, b int) ; +INSERT INTO t2 VALUES (20,9),(20,9); -SELECT a1.f3 AS r FROM t2 AS a1 , t1 WHERE a1.f3 < ALL ( SELECT f3 FROM t3 WHERE f3 = 1 ) ; -DROP TABLE t1, t2, t3; +create table t3 (d int, e int); +insert into t3 values (2, 9), (3,10); + +EXPLAIN +SELECT t2.b , t1.c +FROM t2 LEFT JOIN t1 ON t1.c < 3 +WHERE (t2.b , t1.c) NOT IN (SELECT * from t3); + +SELECT t2.b , t1.c +FROM t2 LEFT JOIN t1 ON t1.c < 3 +WHERE (t2.b, t1.c) NOT IN (SELECT * from t3); + +drop table t1, t2, t3; --echo End of 5.3 tests @@ -4860,4 +5003,64 @@ eval explain $query; DROP TABLE t1,t2; +--echo # +--echo # lp:826279: assertion failure with GROUP BY a result of subquery +--echo # + +CREATE TABLE t1 (a int); +INSERT INTO t1 VALUES (0), (0); + +CREATE TABLE t2 (a int, b int, c int); +INSERT INTO t2 VALUES (10,7,0), (0,7,0); + +CREATE TABLE t3 (a int, b int); +INSERT INTO t3 VALUES (10,7), (0,7); + +SELECT SUM(DISTINCT b), + (SELECT t2.a FROM t1 JOIN t2 ON t2.c != 0 + WHERE t.a != 0 AND t2.a != 0) + FROM (SELECT * FROM t3) AS t +GROUP BY 2; + +SELECT SUM(DISTINCT b), + (SELECT t2.a FROM t1,t2 WHERE t.a != 0 or 1=2 LIMIT 1) + FROM (SELECT * FROM t3) AS t +GROUP BY 2; + +DROP TABLE t1,t2,t3; + +--echo # +--echo # Bug#12329653 +--echo # EXPLAIN, UNION, PREPARED STATEMENT, CRASH, SQL_FULL_GROUP_BY +--echo # + +CREATE TABLE t1(a1 int); +INSERT INTO t1 VALUES (1),(2); + +SELECT @@session.sql_mode INTO @old_sql_mode; +SET SESSION sql_mode='ONLY_FULL_GROUP_BY'; + +## First a simpler query, illustrating the transformation +## '1 < some (...)' => '1 < max(...)' +SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t1); + +## The query which made the server crash. +PREPARE stmt FROM +'SELECT 1 UNION ALL +SELECT 1 FROM t1 +ORDER BY +(SELECT 1 FROM t1 AS t1_0 + WHERE 1 < SOME (SELECT a1 FROM t1) +)' ; + +--error ER_SUBQUERY_NO_1_ROW +EXECUTE stmt ; +--error ER_SUBQUERY_NO_1_ROW +EXECUTE stmt ; + +SET SESSION sql_mode=@old_sql_mode; + +DEALLOCATE PREPARE stmt; +DROP TABLE t1; + set optimizer_switch=@subselect_tmp; diff --git a/mysql-test/t/subselect3.test b/mysql-test/t/subselect3.test index 1e92a147bcd..13c4b532c74 100644 --- a/mysql-test/t/subselect3.test +++ b/mysql-test/t/subselect3.test @@ -3,7 +3,7 @@ drop table if exists t0, t1, t2, t3, t4, t5, t11, t12, t21, t22; --enable_warnings set @subselect3_tmp= @@optimizer_switch; -set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on'; +set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on,semijoin_with_cache=on'; # # 1. Subquery with GROUP/HAVING @@ -81,6 +81,7 @@ insert into t1 values (2, 3), (2, NULL), (3, NULL); +insert into t1 values (5, 7), (8, 9), (4, 1); create table t2 (a int, oref int); insert into t2 values (1, 1), (2,2), (NULL, 3), (NULL, 4); @@ -1186,6 +1187,52 @@ CALL p1; DROP PROCEDURE p1; DROP TABLE t1, t2; +--echo # +--echo # fix of lp:824425 (prohibiting subqueries in row in +--echo # left part of IN/ALL/ANY) +--echo # + +CREATE TABLE t1 ( a int) ; +INSERT INTO t1 VALUES (20),(30); + +CREATE TABLE t2 (a int) ; +INSERT INTO t2 VALUES (3),(9); + +CREATE TABLE t3 ( a int, b int) ; +INSERT INTO t3 VALUES (20,5),(30,6); + +set @optimizer_switch_save=@@optimizer_switch; +SET SESSION optimizer_switch='semijoin=OFF,in_to_exists=OFF,materialization=ON,partial_match_rowid_merge=ON,partial_match_table_scan=OFF'; + +SELECT * FROM t1 +WHERE ( + ( SELECT a FROM t2 WHERE a = 9 ) +) NOT IN ( + SELECT b + FROM t3 +); +explain extended +SELECT * FROM t1 +WHERE ( + ( SELECT a FROM t2 WHERE a = 9 ) +) NOT IN ( + SELECT b + FROM t3 +); + +--error ER_NOT_SUPPORTED_YET +SELECT * FROM t1 +WHERE ( + ( SELECT a FROM t2 WHERE a = 9 ), + ( SELECT a FROM t2 WHERE a = 3 ) +) NOT IN ( + SELECT b , a + FROM t3 +); +set optimizer_switch=@optimizer_switch_save; + +drop table t1,t2,t3; + --echo End of 5.3 tests --echo # diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test index b31ba9b8f89..41ca1762fef 100644 --- a/mysql-test/t/subselect4.test +++ b/mysql-test/t/subselect4.test @@ -7,6 +7,7 @@ drop view if exists v1, v2; set @subselect4_tmp= @@optimizer_switch; set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on'; +set optimizer_switch='semijoin_with_cache=on'; set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; --echo # @@ -239,35 +240,6 @@ set @@session.optimizer_switch = @old_optimizer_switch, DROP TABLE t1; --echo # ---echo # BUG#45863 "Assertion failed: (fixed == 0), function fix_fields(), ---echo # file item.cc, line 4448" ---echo # ---disable_warnings -DROP TABLE IF EXISTS C, BB; ---enable_warnings - -CREATE TABLE C ( - varchar_nokey varchar(1) NOT NULL -); -INSERT INTO C VALUES - ('k'),('a'),(''),('u'),('e'),('v'),('i'), - ('t'),('u'),('f'),('u'),('m'),('j'),('f'), - ('v'),('j'),('g'),('e'),('h'),('z'); -CREATE TABLE BB ( - varchar_nokey varchar(1) NOT NULL -); -INSERT INTO BB VALUES ('i'),('t'); --- error ER_OPERAND_COLUMNS -SELECT varchar_nokey FROM C -WHERE (varchar_nokey, OUTR) IN (SELECT varchar_nokey - FROM BB); --- error ER_BAD_FIELD_ERROR -SELECT varchar_nokey FROM C -WHERE (varchar_nokey, OUTR) IN (SELECT varchar_nokey, varchar_nokey - FROM BB); -DROP TABLE C,BB; - ---echo # --echo # During work with BUG#45863 I had problems with a query that was --echo # optimized differently in regular and prepared mode. --echo # Because there was a bug in one of the selected strategies, I became @@ -1591,5 +1563,72 @@ EXECUTE st2; drop table t1, t2; +--echo # +--echo # LP BUG#825018: Crash in check_and_do_in_subquery_rewrites() with corrlated subquery in select list +--echo # + +CREATE TABLE t1 (a int, b int); +INSERT INTO t1 VALUES (10,1),(11,7); + +CREATE TABLE t2 (a int); +INSERT INTO t2 VALUES (2),(3); + +CREATE TABLE t3 (a int, b int); +INSERT INTO t3 VALUES (1,1); + +CREATE PROCEDURE sp1 () LANGUAGE SQL +SELECT (SELECT t1.a + FROM t1 + WHERE t1.b = t3.b + AND t1.b IN ( SELECT a FROM t2 )) sq +FROM t3 +GROUP BY 1; +CALL sp1(); +CALL sp1(); +drop procedure sp1; + +prepare st1 from " +SELECT (SELECT t1.a + FROM t1 + WHERE t1.b = t3.b + AND t1.b IN ( SELECT a FROM t2 )) sq +FROM t3 +GROUP BY 1"; +execute st1; +execute st1; +deallocate prepare st1; + +drop table t1, t2, t3; set optimizer_switch=@subselect4_tmp; + +--echo # +--echo # LP BUG#833702 Wrong result with nested IN and singlerow subqueries and equality propagation +--echo # + +CREATE TABLE t2 (c int , a int, b int); +INSERT INTO t2 VALUES (10,7,0); + +CREATE TABLE t3 (a int, b int) ; +INSERT INTO t3 VALUES (5,0),(7,0); + +CREATE TABLE t4 (a int); +INSERT INTO t4 VALUES (2),(8); + +set @@optimizer_switch='semijoin=off,in_to_exists=on,materialization=off,subquery_cache=off'; + +SELECT * FROM t2 +WHERE t2.b IN (SELECT b FROM t3 WHERE t3.a = t2.a AND a < SOME (SELECT * FROM t4)) + OR ( t2.c > 242 ); + +EXPLAIN SELECT * FROM t2 +WHERE t2.b IN (SELECT t3.b FROM t3 WHERE t3.a < ANY (SELECT t4.a FROM t4) and t3.a = 7); +SELECT * FROM t2 +WHERE t2.b IN (SELECT t3.b FROM t3 WHERE t3.a < ANY (SELECT t4.a FROM t4) and t3.a = 7); + +drop table t2, t3, t4; + +set optimizer_switch=@subselect4_tmp; + +SET optimizer_switch= @@global.optimizer_switch; +set @@tmp_table_size= @@global.tmp_table_size; diff --git a/mysql-test/t/subselect_cache.test b/mysql-test/t/subselect_cache.test index e4671cf7b2a..d06bf06756a 100644 --- a/mysql-test/t/subselect_cache.test +++ b/mysql-test/t/subselect_cache.test @@ -1,6 +1,8 @@ --disable_warnings drop table if exists t0,t1,t2,t3,t4,t5,t6,t7,t8,t9; +drop view if exists v1; --enable_warnings + set optimizer_switch='subquery_cache=on'; create table t1 (a int, b int); @@ -1666,5 +1668,33 @@ FROM t1 WHERE t2.f1 OR t2.f3 ); drop tables t1, t2, t3; +--echo # +--echo # Test of LP BUG#872775 view with "outer references" bug +--echo # +set @@optimizer_switch= default; +set optimizer_switch='subquery_cache=on'; +CREATE TABLE t1 (a int) ; + +CREATE TABLE t2 (b int, c varchar(1) NOT NULL ) ; +INSERT INTO t2 VALUES (1,'x'),(2,'y'); + +CREATE TABLE t3 (a int) ; + +CREATE TABLE t4 ( pk int(11) NOT NULL , b int(11) NOT NULL ) ; +INSERT INTO t4 VALUES (26,9),(27,5),(28,0),(29,3); + +CREATE OR REPLACE VIEW v1 AS +SELECT t2.b +FROM t1 +JOIN t2 +WHERE t2 .c > ( + SELECT t2.c FROM t3 + ); + +SELECT * FROM t4 WHERE b NOT IN ( SELECT * FROM v1 ); + +drop view v1; +drop table t1,t2,t3,t4; + --echo # restore default set @@optimizer_switch= default; diff --git a/mysql-test/t/subselect_extra.test b/mysql-test/t/subselect_extra.test index 466f254e6ad..b76f3f70516 100644 --- a/mysql-test/t/subselect_extra.test +++ b/mysql-test/t/subselect_extra.test @@ -9,11 +9,12 @@ --disable_warnings drop table if exists t1,t2,t3,t4; +drop view if exists v1,v2,v3; --enable_warnings set @subselect_extra_tmp=@@optimizer_switch; -set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on'; - +set @@optimizer_switch=ifnull(@optimizer_switch_for_subselect_extra_test, + "semijoin=on,firstmatch=on,loosescan=on,semijoin_with_cache=on,partial_match_rowid_merge=off,partial_match_table_scan=off"); --echo # From explain.test: --echo # @@ -273,6 +274,122 @@ EXPLAIN SELECT 1 FROM t2 WHERE a IN (SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2)); DROP TABLE t1, t2; +--echo # +--echo # From derived_view.test +--echo # +set @tmp_subselect_extra_derived=@@optimizer_switch; +set optimizer_switch='derived_merge=on,derived_with_keys=on'; + +--echo # +--echo # LP bug #806504: right join over a view/derived table +--echo # + +CREATE TABLE t1 (a int, b int) ; +INSERT INTO t1 VALUES (0,0); + +CREATE TABLE t2 (a int) ; +INSERT INTO t2 VALUES (0), (0); + +CREATE VIEW v1 AS SELECT * FROM t1; + +SELECT * FROM t2 RIGHT JOIN (SELECT * FROM t1) AS t ON t.a != 0 + WHERE t.a IN (SELECT b FROM t1); +EXPLAIN EXTENDED +SELECT * FROM t2 RIGHT JOIN (SELECT * FROM t1) AS t ON t.a != 0 + WHERE t.a IN (SELECT b FROM t1); + +SELECT * FROM t2 RIGHT JOIN v1 AS t ON t.a != 0 + WHERE t.a IN (SELECT b FROM t1); +EXPLAIN EXTENDED +SELECT * FROM t2 RIGHT JOIN v1 AS t ON t.a != 0 + WHERE t.a IN (SELECT b FROM t1); + +DROP VIEW v1; +DROP TABLE t1,t2; + +--echo # +--echo # LP bug #793448: materialized view accessed by two-component key +--echo # + +CREATE TABLE t1 (a int, b int); +INSERT INTO t1 VALUES (9,3), (2,5); + +CREATE TABLE t2 (a int, b int); +INSERT INTO t2 VALUES (9,3), (3,7), (9,1), (2,5), (2,4), (3,8); + +CREATE TABLE t3 (a int, b int); +INSERT INTO t3 VALUES (10,3), (9,7), (9,1), (2,4); + +CREATE VIEW v1(a,b) AS SELECT a, MAX(b) FROM t2 GROUP BY a; +CREATE VIEW v2(a,b) AS SELECT a,b FROM t2 UNION SELECT a,b FROM t3; + +SELECT * FROM v1; +SELECT a FROM t1 WHERE (a,b) IN (SELECT * FROM v1); +EXPLAIN +SELECT a FROM t1 WHERE (a,b) IN (SELECT * FROM v1); + +SELECT * FROM v2; +SELECT a FROM t1 WHERE (a,b) IN (SELECT * FROM v2); +EXPLAIN +SELECT a FROM t1 WHERE (a,b) IN (SELECT * FROM v2); + +DROP VIEW v1,v2; +DROP TABLE t1,t2,t3; + +--echo # +--echo # LP bug #874006: materialized view used in IN subquery +--echo # + +CREATE TABLE t3 (a int NOT NULL, b varchar(1), c varchar(1)); +INSERT INTO t3 VALUES (19,NULL,NULL), (20,'r','r'); + +CREATE TABLE t1 (a int, b varchar(1) , c varchar(1)); +INSERT INTO t1 VALUES (1,NULL,NULL), (5,'r','r'), (7,'y','y'); + +CREATE TABLE t2 (a int NOT NULL , b int, c varchar(1)); +INSERT INTO t2 VALUES (4,3,'r'); + +CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1; + +SET SESSION optimizer_switch='derived_with_keys=off'; +EXPLAIN +SELECT * FROM t3 + WHERE t3.b IN (SELECT v1.b FROM v1, t2 + WHERE t2.c = v1.c AND t2.c = v1.b AND v1.b = t3.c); +SELECT * FROM t3 + WHERE t3.b IN (SELECT v1.b FROM v1, t2 + WHERE t2.c = v1.c AND t2.c = v1.b AND v1.b = t3.c); + +SET SESSION optimizer_switch='derived_with_keys=on'; +EXPLAIN +SELECT * FROM t3 + WHERE t3.b IN (SELECT v1.b FROM v1, t2 + WHERE t2.c = v1.c AND t2.c = v1.b AND v1.b = t3.c); +SELECT * FROM t3 + WHERE t3.b IN (SELECT v1.b FROM v1, t2 + WHERE t2.c = v1.c AND t2.c = v1.b AND v1.b = t3.c); + +DROP VIEW v1; +DROP TABLE t1,t2,t3; + +--echo # +--echo # LP bug #873263: materialized view used in correlated IN subquery +--echo # + +CREATE TABLE t1 (a int, b int) ; +INSERT INTO t1 VALUES (5,4), (9,8); + +CREATE TABLE t2 (a int, b int) ; +INSERT INTO t2 VALUES (4,5), (5,1); + +CREATE ALGORITHM=TEMPTABLE VIEW v2 AS SELECT * FROM t2; + +SET SESSION optimizer_switch='derived_with_keys=on'; +EXPLAIN +SELECT * FROM t1 WHERE t1.b IN (SELECT v2.a FROM v2 WHERE v2.b = t1.a); +SELECT * FROM t1 WHERE t1.b IN (SELECT v2.a FROM v2 WHERE v2.b = t1.a); -set optimizer_switch= @subselect_extra_tmp; +DROP VIEW v2; +DROP TABLE t1,t2; +set optimizer_switch= @tmp_subselect_extra_derived; diff --git a/mysql-test/t/subselect_extra_no_semijoin.test b/mysql-test/t/subselect_extra_no_semijoin.test new file mode 100644 index 00000000000..8aba3dde72b --- /dev/null +++ b/mysql-test/t/subselect_extra_no_semijoin.test @@ -0,0 +1,9 @@ +set @subselect_extra_no_sj_tmp=@@optimizer_switch; + +set @optimizer_switch_for_subselect_extra_test='semijoin=off,firstmatch=off,loosescan=off,semijoin_with_cache=off'; + +--source t/subselect_extra.test + +set optimizer_switch= @subselect_extra_no_sj_tmp; + +set @optimizer_switch_for_subselect_extra_test=null;
\ No newline at end of file diff --git a/mysql-test/t/subselect_innodb.test b/mysql-test/t/subselect_innodb.test index 3e2914eaef4..27ec36854f4 100644 --- a/mysql-test/t/subselect_innodb.test +++ b/mysql-test/t/subselect_innodb.test @@ -1,5 +1,7 @@ -- source include/have_innodb.inc +# Note: the tests uses only non-semijoin subqueries so semi-join switch +# settings are not relevant. set @subselect_innodb_tmp=@@optimizer_switch; set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; --disable_warnings @@ -250,5 +252,61 @@ INSERT INTO t1 VALUES ('2011-05-13', 0); SELECT * FROM t1 WHERE b < (SELECT CAST(a as date) FROM t1 GROUP BY a); DROP TABLE t1; -# Cleanups +--echo # +--echo # lp:827416 Crash in select_describe() on EXPLAIN with DISTINCT in nested subqueries +--echo # + +CREATE TABLE t3 ( b int) ENGINE=InnoDB; +CREATE TABLE t2 ( c int) ENGINE=InnoDB; +CREATE TABLE t1 ( a int NOT NULL , PRIMARY KEY (a)) ENGINE=InnoDB; + +EXPLAIN SELECT * +FROM t1 +WHERE t1.a = ( + SELECT SUM( c ) + FROM t2 + WHERE (SELECT DISTINCT b FROM t3) > 0); +SELECT * +FROM t1 +WHERE t1.a = ( + SELECT SUM( c ) + FROM t2 + WHERE (SELECT DISTINCT b FROM t3) > 0); + +DROP TABLE t1, t2, t3; + + +--echo # +--echo # lp:858148 Fourth crash in select_describe() with nested subqueries +--echo # + +CREATE TABLE t1 ( f1 int(11)) ENGINE=InnoDB; +CREATE TABLE t2 ( f1 int(11), f2 int(11), PRIMARY KEY (f1)) ; +CREATE TABLE t3 ( f3 int(11)) ENGINE=InnoDB; + +EXPLAIN +SELECT MAX( f1 ) FROM t2 +WHERE f2 >= ( + SELECT SUM( f1 ) + FROM t1 + WHERE EXISTS ( + SELECT f3 + FROM t3 + GROUP BY 1 + ) +); + +SELECT MAX( f1 ) FROM t2 +WHERE f2 >= ( + SELECT SUM( f1 ) + FROM t1 + WHERE EXISTS ( + SELECT f3 + FROM t3 + GROUP BY 1 + ) +); + +drop table t1, t2, t3; + set optimizer_switch=@subselect_innodb_tmp; diff --git a/mysql-test/t/subselect_mat.test b/mysql-test/t/subselect_mat.test index a70fb4783c5..09c6b3e1747 100644 --- a/mysql-test/t/subselect_mat.test +++ b/mysql-test/t/subselect_mat.test @@ -226,3 +226,31 @@ SELECT MAX(t1.b) AS max_res FROM t1 WHERE (9) IN (SELECT a FROM t2); DROP TABLE t1,t2; +--echo # +--echo # LPBUG#825095: Wrong result with materialization and NOT IN with 2 expressions +--echo # + +CREATE TABLE t1 (a int,b int); +INSERT INTO t1 VALUES (4,4),(4,2); + +CREATE TABLE t2 (b int, a int); +INSERT INTO t2 VALUES (4,3),(8,4); + +set @@optimizer_switch='semijoin=off,in_to_exists=off,materialization=on,partial_match_rowid_merge=on,partial_match_table_scan=off'; + +EXPLAIN SELECT * +FROM t1 +WHERE (a, b) NOT IN (SELECT a, b FROM t2); + +SELECT * +FROM t1 +WHERE (a, b) NOT IN (SELECT a, b FROM t2); + +EXPLAIN +SELECT a, b, (a, b) NOT IN (SELECT a, b FROM t2) as sq +FROM t1; + +SELECT a, b, (a, b) NOT IN (SELECT a, b FROM t2) as sq +FROM t1; + +drop table t1, t2; diff --git a/mysql-test/t/subselect_mat_cost-master.opt b/mysql-test/t/subselect_mat_cost-master.opt new file mode 100644 index 00000000000..dc7ac6cc205 --- /dev/null +++ b/mysql-test/t/subselect_mat_cost-master.opt @@ -0,0 +1 @@ +--log-output=TABLE,FILE --log --log-slow-queries --slow-query-log=1 diff --git a/mysql-test/t/subselect_mat_cost.test b/mysql-test/t/subselect_mat_cost.test index 8a0d1ac702d..5a1fb550ca4 100644 --- a/mysql-test/t/subselect_mat_cost.test +++ b/mysql-test/t/subselect_mat_cost.test @@ -15,6 +15,11 @@ set @subselect_mat_cost=@@optimizer_switch; set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; +# +# Test logging to slow log (there was some errors in the log files about +# the slow log when running under valgrind, so better to get this tested) +# +set long_query_time=0.1; -- echo TEST GROUP 1: @@ -222,13 +227,15 @@ select count(*) from CountryLanguage where (Language, Country) NOT IN (SELECT City.Name, Country.Code - FROM City LEFT JOIN Country ON (Country = Code and City.Population < 10000)); + FROM City LEFT JOIN Country ON (Country = Code and City.Population < 10000)) + AND Language IN ('English','Spanish'); select count(*) from CountryLanguage where (Language, Country) NOT IN (SELECT City.Name, Country.Code - FROM City LEFT JOIN Country ON (Country = Code and City.Population < 10000)); + FROM City LEFT JOIN Country ON (Country = Code and City.Population < 10000)) + AND Language IN ('English','Spanish'); -- echo Q2.3m: -- echo MATERIALIZATION with the PARTIAL_MATCH_MERGE strategy, because the HAVING diff --git a/mysql-test/t/subselect_mat_cost_bugs.test b/mysql-test/t/subselect_mat_cost_bugs.test index 37c7b617760..2e5dd71f9af 100644 --- a/mysql-test/t/subselect_mat_cost_bugs.test +++ b/mysql-test/t/subselect_mat_cost_bugs.test @@ -89,6 +89,12 @@ CREATE TABLE t1 ( INSERT INTO t1 VALUES (10,7,8,'v','v'); INSERT INTO t1 VALUES (11,1,9,'r','r'); INSERT INTO t1 VALUES (12,5,9,'a','a'); +INSERT INTO t1 VALUES (13,7,18,'v','v'); +INSERT INTO t1 VALUES (14,1,19,'r','r'); +INSERT INTO t1 VALUES (15,5,29,'a','a'); +INSERT INTO t1 VALUES (17,7,38,'v','v'); +INSERT INTO t1 VALUES (18,1,39,'r','r'); +INSERT INTO t1 VALUES (19,5,49,'a','a'); create table t1a like t1; insert into t1a select * from t1; @@ -348,4 +354,63 @@ SELECT * FROM t1 WHERE (f1) IN (SELECT f1 FROM t2) LIMIT 0; +set @@optimizer_switch='default'; +drop table t1, t2; + +--echo # +--echo # LP BUG#834492: Crash in fix_semijoin_strategies_for_picked_join_order +--echo # with nested subqueries and LooseScan=ON +--echo # + +CREATE TABLE t3 (b int) ; +INSERT INTO t3 VALUES (0),(0); + +CREATE TABLE t4 (a int, b int, c int, d int, PRIMARY KEY (a)) ; +INSERT INTO t4 VALUES (28,0,0,0),(29,3,0,0); + +CREATE TABLE t5 (a int, b int, c int, d int, KEY (c,b)) ; +INSERT INTO t5 VALUES (28,0,0,0),(29,3,0,0); + +SET @@optimizer_switch='semijoin=ON,loosescan=ON,firstmatch=OFF,materialization=OFF'; + +EXPLAIN SELECT * +FROM t3 +WHERE t3.b > ALL ( + SELECT c + FROM t4 + WHERE t4.a >= t3.b + AND a = SOME (SELECT b FROM t5)); + +SELECT * +FROM t3 +WHERE t3.b > ALL ( + SELECT c + FROM t4 + WHERE t4.a >= t3.b + AND a = SOME (SELECT b FROM t5)); + +set @@optimizer_switch='default'; +drop table t3, t4, t5; + +--echo # +--echo # LP BUG#858038 The result of a query with NOT IN subquery depends on the state of the optimizer switch +--echo # + +create table t1 (c1 char(2) not null, c2 char(2)); +create table t2 (c3 char(2), c4 char(2)); + +insert into t1 values ('a1', 'b1'); +insert into t1 values ('a2', 'b2'); + +insert into t2 values ('x1', 'y1'); +insert into t2 values ('a2', null); + +set @@optimizer_switch='in_to_exists=off,materialization=on,partial_match_rowid_merge=off,partial_match_table_scan=on'; +explain select * from t1 where c1 = 'a2' and (c1, c2) not in (select * from t2); +select * from t1 where c1 = 'a2' and (c1, c2) not in (select * from t2); + +set @@optimizer_switch='in_to_exists=off,materialization=on,partial_match_rowid_merge=on,partial_match_table_scan=off'; +explain select * from t1 where c1 = 'a2' and (c1, c2) not in (select * from t2); +select * from t1 where c1 = 'a2' and (c1, c2) not in (select * from t2); + drop table t1, t2; diff --git a/mysql-test/t/subselect_no_opts.test b/mysql-test/t/subselect_no_opts.test index 005b2f041fa..724cbab6310 100644 --- a/mysql-test/t/subselect_no_opts.test +++ b/mysql-test/t/subselect_no_opts.test @@ -3,7 +3,7 @@ # (test in-to-exists) -set @optimizer_switch_for_subselect_test='materialization=off,semijoin=off,mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; +set @optimizer_switch_for_subselect_test='materialization=off,semijoin=off,subquery_cache=off,mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; --source t/subselect.test set @optimizer_switch_for_subselect_test=null; diff --git a/mysql-test/t/subselect_scache.test b/mysql-test/t/subselect_no_scache.test index 0032b148643..fe8ff749a59 100644 --- a/mysql-test/t/subselect_scache.test +++ b/mysql-test/t/subselect_no_scache.test @@ -2,7 +2,7 @@ # Run subselect.test without semi-join optimization (test materialize) # select @@optimizer_switch like '%subquery_cache=on%'; -set optimizer_switch='subquery_cache=on'; +set optimizer_switch='subquery_cache=off'; --source t/subselect.test diff --git a/mysql-test/t/subselect_nulls.test b/mysql-test/t/subselect_nulls.test index 6de7820872c..4b08e773b17 100644 --- a/mysql-test/t/subselect_nulls.test +++ b/mysql-test/t/subselect_nulls.test @@ -5,6 +5,9 @@ drop table if exists x1; drop table if exists x2; --enable_warnings +set @tmp_subselect_nulls=@@optimizer_switch; +set optimizer_switch='semijoin=off'; + create table x1(k int primary key, d1 int, d2 int); create table x2(k int primary key, d1 int, d2 int); @@ -90,5 +93,7 @@ where exists (select * from x2 where x1.d1=x2.d1 and x1.d2=x2.d2); +set optimizer_switch= @tmp_subselect_nulls; + drop table x1; drop table x2; diff --git a/mysql-test/t/subselect_partial_match.test b/mysql-test/t/subselect_partial_match.test index 8d1fbd9b8d7..be78360c76b 100644 --- a/mysql-test/t/subselect_partial_match.test +++ b/mysql-test/t/subselect_partial_match.test @@ -10,12 +10,14 @@ set @save_optimizer_switch=@@optimizer_switch; --echo ------------------------------- --echo Default for all tests. -set @@optimizer_switch="materialization=on,in_to_exists=off,semijoin=off,subquery_cache=off"; +set @@optimizer_switch = "materialization=on,in_to_exists=off,semijoin=off,subquery_cache=off"; +set @test_default_opt_switch = @@optimizer_switch; +set @in_exists = "materialization=off,in_to_exists=on,semijoin=off,subquery_cache=off"; ---echo +--echo ------------------------------------------------------------------------- --echo Schema requires partial matching, but data analysis discoveres there is --echo no need. This is possible only if all outer columns are not NULL. ---echo +--echo ------------------------------------------------------------------------- create table t1 (a1 char(8) not null, a2 char(8) not null); create table t2 (b1 char(8), b2 char(8)); @@ -36,9 +38,9 @@ select a1, a2, (a1, a2) not in (select * from t2) as in_res from t1; drop table t1, t2; ---echo +--echo ------------------------------------------------------------------------- --echo NULLs in the outer columns, no NULLs in the suqbuery ---echo +--echo ------------------------------------------------------------------------- create table t1 (a1 char(8), a2 char(8)); create table t2 (b1 char(8) not null, b2 char(8) not null); @@ -63,21 +65,366 @@ select a1, a2, (a1, a2) in (select * from t2) as in_res from t1; drop table t1, t2; ---echo +--echo ------------------------------------------------------------------------- +--echo NULLs in the outer column, NULLs in the subquery, there is +--echo no value match in any column, but there is a partial match +--echo such that some of the matching NULLs are in the outer columns, +--echo the other NULLs are in the inner columns. +--echo ------------------------------------------------------------------------- + +create table t1 (a1 char(1), a2 char(1), a3 char(1)); +create table t2 (b1 char(1), b2 char(1), b3 char(1)); + +insert into t1 values ('c', NULL, 'a'); +insert into t2 values (NULL, 'x', NULL); +insert into t2 values (NULL, 'y', NULL); +insert into t2 values ('o', 'z', 'p'); + +set @@optimizer_switch='partial_match_rowid_merge=on,partial_match_table_scan=off'; +EXPLAIN +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +EXPLAIN +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); + +set @@optimizer_switch='partial_match_rowid_merge=off,partial_match_table_scan=on'; +EXPLAIN +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +EXPLAIN +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); + +set @@optimizer_switch=@in_exists; +EXPLAIN +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +EXPLAIN +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); + +set @@optimizer_switch = @test_default_opt_switch; + +drop table t1, t2; + +create table t1 (a1 char(1), a2 char(1) not null, a3 char(1)); +create table t2 (b1 char(1), b2 char(1), b3 char(1)); + +insert into t1 values (NULL, 'y', NULL); +insert into t2 values ('v', 'x', NULL); +insert into t2 values (NULL, 'y', 'w'); + +set @@optimizer_switch='partial_match_rowid_merge=on,partial_match_table_scan=off'; +EXPLAIN +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +EXPLAIN +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); + +set @@optimizer_switch='partial_match_rowid_merge=off,partial_match_table_scan=on'; +EXPLAIN +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +EXPLAIN +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); + +set @@optimizer_switch=@in_exists; +EXPLAIN +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +EXPLAIN +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); + +set @@optimizer_switch = @test_default_opt_switch; + +drop table t1, t2; + +--echo ------------------------------------------------------------------------- +--echo There is only one column in the subquery to complement the NULLs in the +--echo outer reference. It is a NULL column, so a match is guaranteed. +--echo ------------------------------------------------------------------------- + +create table t1 (a1 char(1), a2 char(1), a3 char(1)); +create table t2 (b1 char(1), b2 char(1), b3 char(1)); + +insert into t1 values (NULL, 'g', NULL); +insert into t2 values ('z', NULL, 'y'); +insert into t2 values (NULL, 'z', 'y'); + +set @@optimizer_switch='partial_match_rowid_merge=on,partial_match_table_scan=off'; +EXPLAIN +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +EXPLAIN +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); + +set @@optimizer_switch='partial_match_rowid_merge=off,partial_match_table_scan=on'; +EXPLAIN +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +EXPLAIN +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); + +set @@optimizer_switch=@in_exists; +EXPLAIN +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +EXPLAIN +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); + +set @@optimizer_switch = @test_default_opt_switch; + +drop table t1, t2; + +--echo ------------------------------------------------------------------------- +--echo The intersection of the NULL bitmaps is empty because the ranges +--echo of NULL bits do not overlap. +--echo ------------------------------------------------------------------------- + +create table t1 (a1 char(1), a2 char(1), a3 char(1)); +create table t2 (b1 char(1), b2 char(1), b3 char(1)); + +insert into t1 values ('b', 'g', NULL); +insert into t2 values ('z', NULL, 'y'); +insert into t2 values (NULL, 'z', 'y'); + +set @@optimizer_switch='partial_match_rowid_merge=on,partial_match_table_scan=off'; +EXPLAIN +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +EXPLAIN +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); + +set @@optimizer_switch='partial_match_rowid_merge=off,partial_match_table_scan=on'; +EXPLAIN +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +EXPLAIN +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); + +set @@optimizer_switch=@in_exists; +EXPLAIN +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +EXPLAIN +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); + +drop table t1, t2; + +--echo ------------------------------------------------------------------------- +--echo The intersection of the NULL bitmaps is non-empty, and there is a +--echo non-NULL column. +--echo ------------------------------------------------------------------------- + +create table t1 (a1 char(1), a2 char(1), a3 char(1) not null, a4 char(10)); +create table t2 (b1 char(1), b2 char(1), b3 char(1), b4 char(1)); + +insert into t1 values ('a', 'g', 'x', NULL); +insert into t2 values ('z', NULL, 'y', 'x'); +insert into t2 values (NULL, NULL, 'x', 'y'); +insert into t2 values ('x', 'w', 'z', NULL); + +set @@optimizer_switch='partial_match_rowid_merge=on,partial_match_table_scan=off'; +EXPLAIN +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +EXPLAIN +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); + +set @@optimizer_switch='partial_match_rowid_merge=off,partial_match_table_scan=on'; +EXPLAIN +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +EXPLAIN +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); + +set @@optimizer_switch=@in_exists; +EXPLAIN +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +EXPLAIN +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); + +drop table t1, t2; + +--echo ------------------------------------------------------------------------- +--echo Value match in one row, but the NULL complement match in another. +--echo The result must be false. +--echo ------------------------------------------------------------------------- + +create table t1 (a1 char(2), a2 char(1), a3 char(1) not null, a4 char(10)); +create table t2 (b1 char(2), b2 char(1), b3 char(1), b4 char(1)); + +insert into t1 values ('99', NULL, 'j', 'f'); + +insert into t2 values ('01', NULL, 'y', NULL); +insert into t2 values ('02', NULL, 'x', 'y'); +insert into t2 values ('03', 'q', 'y', 'x'); +insert into t2 values (NULL, 'q', 'm', 'p'); +insert into t2 values ('m', 'z', 'j', NULL); +insert into t2 values (NULL, 'z', 'b', NULL); +insert into t2 values (NULL, 'z', 'a', NULL); +insert into t2 values ('34', 'q', 'y', 'x'); + +set @@optimizer_switch='partial_match_rowid_merge=on,partial_match_table_scan=off'; +EXPLAIN +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +EXPLAIN +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); + +set @@optimizer_switch='partial_match_rowid_merge=off,partial_match_table_scan=on'; +EXPLAIN +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +EXPLAIN +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); + +set @@optimizer_switch=@in_exists; +EXPLAIN +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +EXPLAIN +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); + +drop table t1, t2; + +--echo ------------------------------------------------------------------------- +--echo Test the intersection of larger number of rows with NULL, such that +--echo the number is at the boundary 32. This test is based on the implementation +--echo of MY_BITMAP which uses 32 bit words, and the intersection operation works +--echo by intersecting the bitmap word by word. +--echo ------------------------------------------------------------------------- + +create table t1 (a1 char(2), a2 char(2), a3 char(1), a4 char(10)) ENGINE=MyISAM; +create table t2 (b1 char(2), b2 char(2), b3 char(1), b4 char(1)) ENGINE=MyISAM; + +insert into t1 values (NULL, '98', NULL, 'b'); +insert into t1 values (NULL, '99', NULL, 'c'); + +insert into t2 values ('00', 'rr', 'y', NULL); +insert into t2 values ('01', 'rr', 'y', NULL); +insert into t2 values ('02', NULL, 'x', 'y'); +insert into t2 values ('03', 'qq', 'y', 'x'); +insert into t2 values ('04', 'qq', 'm', 'p'); +insert into t2 values ('05', 'rr', 'y', NULL); +insert into t2 values ('06', NULL, 'x', 'y'); +insert into t2 values ('07', 'qq', 'y', 'x'); +insert into t2 values ('08', 'qq', 'm', 'q'); +insert into t2 values ('09', 'rr', 'y', NULL); +insert into t2 values ('10', NULL, 'x', 'y'); +insert into t2 values ('11', 'qq', 'y', 'x'); +insert into t2 values ('12', 'qq', 'm', 'k'); +insert into t2 values ('13', 'rr', 'y', NULL); +insert into t2 values ('14', NULL, 'x', 'y'); +insert into t2 values ('15', 'qq', 'y', 'x'); +insert into t2 values ('16', 'qq', 'm', 's'); +insert into t2 values ('17', 'rr', 'y', NULL); +insert into t2 values ('18', NULL, 'x', 'y'); +insert into t2 values ('19', 'qq', 'y', 'x'); +insert into t2 values ('20', 'qq', 'm', 't'); +insert into t2 values ('21', 'rr', 'y', NULL); +insert into t2 values ('22', NULL, 'x', 'y'); +insert into t2 values ('23', 'qq', 'y', 'x'); +insert into t2 values ('24', 'qq', 'm', 'u'); +insert into t2 values ('25', 'rr', 'y', NULL); +insert into t2 values ('26', NULL, 'x', 'y'); +insert into t2 values ('27', 'qq', 'y', 'x'); +insert into t2 values ('28', 'qq', 'm', 'y'); +insert into t2 values ('29', 'rr', 'y', NULL); +insert into t2 values ('30', NULL, 'x', 'z'); +insert into t2 values ('31', 'ss', 'h', NULL); +insert into t2 values ('32', 'vv', 'i', NULL); +--echo the only partial matching row +insert into t2 values ('33', NULL, 'j', NULL); +insert into t2 values ('34', 'qq', 'y', 'x'); + +set @@optimizer_switch='partial_match_rowid_merge=on,partial_match_table_scan=off'; +EXPLAIN +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +EXPLAIN +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); + +set @@optimizer_switch='partial_match_rowid_merge=off,partial_match_table_scan=on'; +EXPLAIN +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +EXPLAIN +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); + +set @@optimizer_switch=@in_exists; +EXPLAIN +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +EXPLAIN +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); + +--echo change the mathcing row to be the last one in the first bitmap word +update t2 set b2 = 'zz' where b1 = 33; +update t2 set b2 = NULL where b1 = 31; + +set @@optimizer_switch='partial_match_rowid_merge=on,partial_match_table_scan=off'; +EXPLAIN +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +EXPLAIN +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); + +set @@optimizer_switch='partial_match_rowid_merge=off,partial_match_table_scan=on'; +EXPLAIN +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +EXPLAIN +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); + +set @@optimizer_switch=@in_exists; +EXPLAIN +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +EXPLAIN +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); + +drop table t1, t2; + + +--echo ------------------------------------------------------------------------- --echo All columns require partial matching (no non-null columns) ---echo +--echo ------------------------------------------------------------------------- --echo TODO ---echo +--echo ------------------------------------------------------------------------- --echo Both non-NULL columns and columns with NULLs ---echo +--echo ------------------------------------------------------------------------- --echo TODO ---echo +--echo ------------------------------------------------------------------------- --echo Covering NULL rows ---echo +--echo ------------------------------------------------------------------------- create table t1 (a1 char(8), a2 char(8)); create table t2 (b1 char(8), b2 char(8)); @@ -110,9 +457,9 @@ select a1, a2, (a1, a2) in (select * from t2) as in_res from t1; drop table t1, t2; ---echo +--echo ------------------------------------------------------------------------- --echo Covering NULL columns ---echo +--echo ------------------------------------------------------------------------- --echo this case affects only the rowid-merge algorithm set @@optimizer_switch="partial_match_rowid_merge=on,partial_match_table_scan=off"; @@ -161,9 +508,9 @@ select *, (a1, a2, a3) in (select * from t2) as in_res from t1; drop table t1, t2; ---echo +--echo ------------------------------------------------------------------------- --echo Covering NULL row, and a NULL column ---echo +--echo ------------------------------------------------------------------------- create table t1 (a1 char(8) not null, a2 char(8), a3 char(8)); create table t2 (b1 char(8), b2 char(8), b3 char(8)); @@ -188,9 +535,9 @@ select *, (a1, a2, a3) in (select * from t2) as in_res from t1; drop table t1, t2; ---echo +--echo ------------------------------------------------------------------------- --echo Covering NULL row, and covering NULL columns ---echo +--echo ------------------------------------------------------------------------- create table t1 (a1 char(8) not null, a2 char(8), a3 char(8)); create table t2 (b1 char(8), b2 char(8), b3 char(8)); @@ -213,6 +560,34 @@ select *, (a1, a2, a3) in (select * from t2) as in_res from t1; drop table t1, t2; +--echo ------------------------------------------------------------------------- +--echo Small buffer for the rowid_merge partial match algorithm that forces +--echo reverting to table scan partial match. +--echo ------------------------------------------------------------------------- + +set @save_rowid_merge_buff_size = @@rowid_merge_buff_size; +set @@rowid_merge_buff_size = 0; + +create table t1 (a1 char(2), a2 char(2), a3 char(1), a4 char(10)); +create table t2 (b1 char(2), b2 char(2), b3 char(1), b4 char(1)); + +insert into t1 values (NULL, '98', NULL, 'b'); +insert into t1 values (NULL, '99', NULL, 'c'); + +insert into t2 values ('00', 'rr', 'y', NULL); +insert into t2 values ('01', 'rr', 'y', NULL); +insert into t2 values ('02', NULL, 'x', 'y'); +insert into t2 values ('03', 'qq', 'y', 'x'); + +set @@optimizer_switch='partial_match_rowid_merge=on,partial_match_table_scan=off'; +EXPLAIN +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; + +drop table t1, t2; + +set @@rowid_merge_buff_size = @save_rowid_merge_buff_size; + --echo ------------------------------- --echo Part 2: Test cases for bugs. @@ -354,4 +729,50 @@ SELECT * FROM t1 WHERE (6, 4 ) NOT IN (SELECT a, b FROM t2); drop table t1, t2; +--echo # +--echo # LP BUG#856152 Wrong result with NOT IN subquery and partial_match_rowid_merge +--echo # + +CREATE TABLE t1 ( f1 integer NOT NULL , f2 integer) ; +INSERT INTO t1 VALUES (3,3),(48,NULL),(49,1); + +CREATE TABLE t2 ( f3 int) ; +INSERT INTO t2 VALUES (5); + +set @@optimizer_switch='in_to_exists=off,materialization=on,partial_match_rowid_merge=on,partial_match_table_scan=off'; +EXPLAIN SELECT * FROM t2 WHERE ( 3 , 1 ) NOT IN ( SELECT f1 , f2 FROM t1 ); +SELECT * FROM t2 WHERE ( 3 , 1 ) NOT IN ( SELECT f1 , f2 FROM t1 ); + +set @@optimizer_switch='in_to_exists=on,materialization=off'; +EXPLAIN SELECT * FROM t2 WHERE ( 3 , 1 ) NOT IN ( SELECT f1 , f2 FROM t1 ); +SELECT * FROM t2 WHERE ( 3 , 1 ) NOT IN ( SELECT f1 , f2 FROM t1 ); + +drop table t1, t2; + +--echo # +--echo # LP BUG#869036 Wrong result with in_to_exists=ON and NOT IN +--echo # + +create table outer_sq (f1 char(1), f2 char(1)); +insert into outer_sq values (NULL, 'c'), ('g', 'c'); + +create table inner_sq (f3 char(1), f4 char(1)); +insert into inner_sq values(null, 'i'), ('v', null); + +--echo All three strategies below must produce the same result. + +set @@optimizer_switch='in_to_exists=off,materialization=on,partial_match_rowid_merge=on,partial_match_table_scan=off'; +SELECT outer_sq.*, (f1, f2) IN (select * from inner_sq) from outer_sq; +SELECT * from outer_sq where (f1, f2) NOT IN (select * from inner_sq); + +set @@optimizer_switch='in_to_exists=off,materialization=on,partial_match_rowid_merge=off,partial_match_table_scan=on'; +SELECT outer_sq.*, (f1, f2) IN (select * from inner_sq) from outer_sq; +SELECT * from outer_sq where (f1, f2) NOT IN (select * from inner_sq); + +set @@optimizer_switch='in_to_exists=on,materialization=off'; +SELECT outer_sq.*, (f1, f2) IN (select * from inner_sq) from outer_sq; +SELECT * from outer_sq where (f1, f2) NOT IN (select * from inner_sq); + +drop table outer_sq, inner_sq; + set @@optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/t/subselect_sj.test b/mysql-test/t/subselect_sj.test index 66c7b1bc549..740289ffcc8 100644 --- a/mysql-test/t/subselect_sj.test +++ b/mysql-test/t/subselect_sj.test @@ -2,7 +2,7 @@ # Nested Loops semi-join subquery evaluation tests # --disable_warnings -drop table if exists t0, t1, t2, t3, t4, t10, t11, t12; +drop table if exists t0, t1, t2, t3, t4, t5, t10, t11, t12; drop view if exists v1, v2, v3, v4; drop procedure if exists p1; --enable_warnings @@ -776,6 +776,7 @@ eval EXPLAIN EXTENDED $query; eval $query; DROP TABLE t1, t2; + --echo # End of Bug#48213 --echo # @@ -1571,5 +1572,313 @@ SELECT * FROM t1 WHERE c1 IN ( SELECT t3.c1 FROM t3 LEFT JOIN t2 ON t2 .c1 = t3 DROP TABLE t1, t2, t3; +--echo # +--echo # BUG#834534: Assertion `0' failed in replace_where_subcondition with semijoin subquery in HAVING +--echo # +CREATE TABLE t1 ( d int ); +INSERT INTO t1 VALUES (2),(2),(0),(2),(2); + +CREATE TABLE t2 ( b int ); +INSERT INTO t2 VALUES (4),(3),(3); + +CREATE TABLE t3 ( a int ); + +SELECT * +FROM t3 +WHERE (t3.a) IN ( + SELECT t1.d + FROM t1 + HAVING ( 4 ) IN ( + SELECT t2.b + FROM t2 + ) +); +drop table t1, t2,t3; + +--echo # +--echo # BUG#834758: Wrong result with innner join, LooseScan, two-column IN() predicate +--echo # + +set @tmp835758=@@optimizer_switch; +set optimizer_switch='semijoin=on,loosescan=on,materialization=off,firstmatch=off'; + +CREATE TABLE t1 (b int) ; +INSERT INTO t1 VALUES (1),(5); + +CREATE TABLE t2 (a int, PRIMARY KEY (a)) ; +INSERT INTO t2 VALUES (6),(10); + +CREATE TABLE t3 (a int, b int, KEY (b)) ; +INSERT INTO t3 VALUES (6,5),(6,2),(8,0),(9,1),(6,5); + +--echo # This used to incorrectly pick a join order of (t1, LooseScan(t3), t2): +explain +SELECT * FROM t1, t2 WHERE (t2.a , t1.b) IN (SELECT a, b FROM t3); +SELECT * FROM t1, t2 WHERE (t2.a , t1.b) IN (SELECT a, b FROM t3); + +DROP TABLE t1, t2, t3; +set @@optimizer_switch= @tmp835758; + +--echo # +--echo # BUG#834739: Wrong result with 3-way inner join, LooseScan,multipart keys +--echo # +set @tmp834739=@@optimizer_switch; +set optimizer_switch='semijoin=on,loosescan=on,materialization=off,firstmatch=off'; +CREATE TABLE t2 ( b int, c int, KEY (b)) ; +INSERT INTO t2 VALUES (1,0),(1,0),(9,0),(1,0),(5,0); +INSERT INTO t2 VALUES (2,0),(3,0),(8,0),(6,0),(5,0); + +CREATE TABLE t3 ( a int); +INSERT INTO t3 VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0); + +CREATE TABLE t4 ( a int); +INSERT INTO t4 VALUES (0),(0),(0); + +CREATE TABLE t5 ( b int, a int , KEY (a,b)) ; +INSERT INTO t5 VALUES (7,0),(9,0); + +explain +SELECT * FROM t3 WHERE t3.a IN (SELECT t5.a FROM t2, t4, t5 WHERE t2.c = t5.a AND t2.b = t5.b); +SELECT * FROM t3 WHERE t3.a IN (SELECT t5.a FROM t2, t4, t5 WHERE t2.c = t5.a AND t2.b = t5.b); + +DROP TABLE t2, t3, t4, t5; +set @@optimizer_switch=@tmp834739; + +--echo # +--echo # BUG#830993: Crash in end_read_record with derived table +--echo # +set @tmp_830993=@@optimizer_switch; +set optimizer_switch='semijoin=on,loosescan=off,materialization=off,firstmatch=off'; +set @tmp_830993_jbs= @@join_buffer_size; +--disable_warnings +set join_buffer_size=160; +--enable_warnings +CREATE TABLE t1 ( + a int(11) NOT NULL AUTO_INCREMENT, + b int(11) DEFAULT NULL, + c int(11) DEFAULT NULL, + d time DEFAULT NULL, + e varchar(1) DEFAULT NULL, + f varchar(1) DEFAULT NULL, + PRIMARY KEY (a), + KEY c (c), + KEY d (d), + KEY e (e,c) +); +INSERT INTO t1 VALUES (10,NULL,8,'22:55:23','x','x'), + (11,8,7,'10:19:31','d','d'),(12,1,1,'14:40:36','r','r'), + (13,9,7,'04:37:47','f','f'),(14,4,9,'19:34:06','y','y'), + (15,3,NULL,'20:35:33','u','u'),(16,2,1,NULL,'m','m'), + (17,NULL,9,'14:43:37',NULL,NULL),(18,2,2,'02:23:09','o','o'), + (19,NULL,9,'01:22:45','w','w'),(20,6,2,'00:00:00','m','m'), + (21,7,4,'00:13:25','q','q'),(22,2,0,'03:47:16',NULL,NULL), + (23,5,4,'01:41:48','d','d'),(24,7,8,'00:00:00','g','g'), + (25,6,NULL,'22:32:04','x','x'),(26,6,NULL,'16:44:14','f','f'), + (27,2,0,'17:38:37','p','p'),(28,9,NULL,'08:46:48','j','j'), + (29,6,8,'14:11:27','c','c'); + +CREATE TABLE t2 like t1; +INSERT INTO t2 VALUES (1,2,4,'22:34:09','v','v'), + (2,150,62,'14:26:02','v','v'),(3,NULL,7,'14:03:03','c','c'), + (4,2,1,'01:46:09',NULL,NULL),(5,5,0,'16:21:18','x','x'), + (6,3,7,'18:56:33','i','i'),(7,1,7,NULL,'e','e'), + (8,4,1,'09:29:08','p','p'),(9,NULL,7,'19:11:10','s','s'), + (10,2,1,'11:57:26','j','j'),(11,6,5,'00:39:46','z','z'), + (12,6,2,'03:28:15','c','c'),(13,8,0,'06:44:18','a','a'), + (14,2,1,'14:36:39','q','q'),(15,6,8,'18:42:45','y','y'), + (16,8,1,'02:57:29',NULL,NULL),(17,3,1,'16:46:13','r','r'), + (18,3,9,'19:39:02','v','v'),(19,9,1,NULL,NULL,NULL), + (20,6,5,'20:58:33','r','r'); + +explain +SELECT + alias1.a, alias1.b, alias1.c, alias1.d, alias1.e, alias1.f, + alias2.a as a2_a, alias2.b as a2_b, alias2.c as a2_c, alias2.d as a2_d, + alias2.e as a2_e, alias2.f as a2_f, + t2.a as t2_a, t2.b as t2_b, t2.c as t2_c, t2.d as t2_d, t2.e as t2_e, t2.f as t2_f +FROM + (SELECT * FROM t2) AS alias1, + t1 AS alias2, + t2 +WHERE + alias1.c IN (SELECT SQ3_alias1.b + FROM t2 AS SQ3_alias1 STRAIGHT_JOIN t2 AS SQ3_alias2) +LIMIT 100; + +create table t3 as +SELECT + alias1.a, alias1.b, alias1.c, alias1.d, alias1.e, alias1.f, + alias2.a as a2_a, alias2.b as a2_b, alias2.c as a2_c, alias2.d as a2_d, + alias2.e as a2_e, alias2.f as a2_f, + t2.a as t2_a, t2.b as t2_b, t2.c as t2_c, t2.d as t2_d, t2.e as t2_e, t2.f as t2_f +FROM + (SELECT * FROM t2) AS alias1, + t1 AS alias2, + t2 +WHERE + alias1.c IN (SELECT SQ3_alias1.b + FROM t2 AS SQ3_alias1 STRAIGHT_JOIN t2 AS SQ3_alias2) +LIMIT 100; + +drop table t1,t2, t3; +set optimizer_switch=@tmp_830993; +set join_buffer_size= @tmp_830993_jbs; + +--echo # +--echo # BUG##849717: Crash in Item_func::fix_fields on second execution of a prepared statement with semijoin +--echo # +CREATE TABLE t1 (a int); +CREATE TABLE t2 (a int); +CREATE TABLE t3 (a int, b int) ; + +PREPARE st1 FROM "SELECT * FROM t2 LEFT JOIN t1 ON t2.a != 0 AND ('j','r') IN ( SELECT b,a FROM t3)"; +EXECUTE st1; +EXECUTE st1; + +DROP TABLE t1, t2, t3; + +--echo # +--echo # BUG#849776: Wrong result with semijoin + "Impossible where" +--echo # +CREATE TABLE t1 ( b varchar(1), a integer) ; +INSERT INTO t1 VALUES ('z',8); + +CREATE TABLE t2 ( a integer, b varchar(1)) ; + +CREATE TABLE t4 ( a integer, b varchar(1)) ; + +CREATE TABLE t5 ( a integer) ; +INSERT INTO t5 VALUES (8); + +select * from t5 where (a) in ( + SELECT t1.a + FROM t1 LEFT JOIN t2 ON t1.a = t2.a + WHERE t2.b NOT IN (SELECT t4.b FROM t4 WHERE t4.b < t1.b) +); + +DROP TABLE t1, t2, t4, t5; + +--echo # +--echo # BUG#861147: Assertion `fixed == 1' failed in Item_func_eq::val_int() with semijoin + materialization + max_join_size +--echo # +CREATE TABLE t1 ( f2 int) ; +CREATE TABLE t2 ( f1 int, f3 int, f4 varchar(3), f5 varchar(35)) ; +INSERT INTO t2 VALUES (4057,9,'USA','Visalia'),(3993,11,'USA','Waco'), + (3948,14,'USA','Warren'),(3813,57,'USA','Washington'), + (4010,11,'USA','Waterbury'),(4017,11,'USA','West Covina'), + (4004,11,'USA','West Valley City'),(4033,10,'USA','Westminster'), + (3842,34,'USA','Wichita'),(4018,10,'USA','Wichita Falls'), + (3899,19,'USA','Winston-Salem'),(3914,17,'USA','Worcester'), + (3888,20,'USA','Yonkers'); + +CREATE TABLE t3 ( f3 int, f4 varchar(3)) ; +INSERT INTO t3 VALUES (86,'USA'); + +CREATE TABLE t4 ( f3 int, f4 varchar(3), f5 varchar(52)) ; +INSERT INTO t4 VALUES (0,'RUS','Belorussian'),(0,'USA','Portuguese'); + +CREATE TABLE t5 ( f2 int) ; + +CREATE TABLE t6 ( f4 varchar(3)); +INSERT INTO t6 VALUES ('RUS'),('USA'); + +set @tmp_mjs_861147= @@max_join_size; +SET max_join_size=10; +set @tmp_os_861147= @@optimizer_switch; +set @@optimizer_switch='semijoin=on,materialization=on'; + +--error ER_TOO_BIG_SELECT +SELECT * +FROM t1 +WHERE ( 1 , 3 ) IN ( + SELECT t2.f1 , MAX( t3.f3 ) + FROM t2 + JOIN t3 + WHERE t3.f4 IN ( + SELECT t4.f5 + FROM t4 + STRAIGHT_JOIN t5 + WHERE t4.f4 < t2.f5 + ) +) AND ( 'p' , 'k' ) IN ( + SELECT f4 , f4 FROM t6 +); +set max_join_size= @tmp_mjs_861147; +set optimizer_switch= @tmp_os_861147; + +DROP TABLE t1,t2,t3,t4,t5,t6; + +--echo # +--echo # BUG#877288: Wrong result with semijoin + materialization + multipart key +--echo # +set @tmp_877288=@@optimizer_switch; +set optimizer_switch='semijoin=ON,materialization=ON'; +CREATE TABLE t1 ( a int) ; +INSERT INTO t1 VALUES (19),(19),(19),(20),(20),(20),(20),(20),(20); + +CREATE TABLE t2 ( b int NOT NULL , c int NOT NULL , KEY (b,c)) ; +INSERT INTO t2 VALUES (14,1),(15,1),(16,1),(17,1),(18,1),(19,1),(20,1); + +CREATE TABLE t3 ( a int, d int) ; +INSERT INTO t3 VALUES (19,1),(7,1),(3,1),(3,1),(20,1),(3,1),(16,1),(17,1),(9,1),(4,1),(6,1),(15,1),(17,1); + +explain +SELECT * FROM t1 WHERE (a) IN (SELECT a FROM t2 JOIN t3 ON b = a); +SELECT * FROM t1 WHERE (a) IN (SELECT a FROM t2 JOIN t3 ON b = a); +DROP TABLE t1,t2,t3; +set optimizer_switch=@tmp_877288; + +--echo # +--echo # BUG#878753: Assertion '0' failed in replace_where_subcondition with derived_merge +--echo # +set @tmp878753= @@optimizer_switch; +set optimizer_switch= 'semijoin=on,derived_merge=on'; +CREATE TABLE t1 (b int(11)) ; +CREATE TABLE t2 (c int, b int, d varchar(52) NOT NULL) ; +CREATE TABLE t3 (b int(11)) ; + +PREPARE st1 FROM ' + SELECT * FROM t1 + JOIN ( + SELECT t2.* FROM t2 + WHERE t2.d <> "a" + AND t2.c IN ( + SELECT t3.b + FROM t3 + ) + ) AS alias2 + ON ( alias2.b = t1.b ); +'; +EXECUTE st1; +DROP TABLE t1,t2,t3; +set optimizer_switch=@tmp878753; + +--echo # +--echo # Bug #889750: semijoin=on + firstmatch=off + semijoin_with_cache=off +--echo # + +create table t1 (a int); +insert into t1 values (7), (1), (5), (3); +create table t2 (a int); +insert into t2 values (4), (1), (8), (3), (9), (2); + +set @tmp_otimizer_switch= @@optimizer_switch; +set optimizer_switch='semijoin=on'; +set optimizer_switch='firstmatch=off'; + +set optimizer_switch='semijoin_with_cache=on'; +explain +select * from t1 where t1.a in (select t2.a from t2); +select * from t1 where t1.a in (select t2.a from t2); + +set optimizer_switch='semijoin_with_cache=off'; +explain +select * from t1 where t1.a in (select t2.a from t2); +select * from t1 where t1.a in (select t2.a from t2); + +set optimizer_switch= @tmp_otimizer_switch; + +drop table t1,t2; + # The following command must be the last one the file set optimizer_switch=@subselect_sj_tmp; diff --git a/mysql-test/t/subselect_sj2.test b/mysql-test/t/subselect_sj2.test index 7972ff50450..0fd8cab1a04 100644 --- a/mysql-test/t/subselect_sj2.test +++ b/mysql-test/t/subselect_sj2.test @@ -28,6 +28,7 @@ create table t2 ( key(b) ); insert into t2 select a, a/2 from t0; +insert into t2 select a+10, a+10/2 from t0; select * from t1; select * from t2; @@ -43,6 +44,8 @@ create table t3 ( primary key(pk1, pk2, pk3) ) engine=innodb; insert into t3 select a,a, a,a,a from t0; +insert into t3 select a,a, a+100,a+100,a+100 from t0; + explain select * from t3 where b in (select a from t1); select * from t3 where b in (select a from t1); @@ -578,6 +581,7 @@ insert into t0 values (0),(1),(2),(3),(4); create table t1 (a int, b int, key(a)); insert into t1 select a,a from t0; +insert into t1 select a+5,a from t0; create table t2 (a int, b int, primary key(a)); insert into t2 select * from t1; @@ -947,4 +951,123 @@ WHERE alias2.f11 IN ( GROUP BY field2; drop table t1, t2, t3; +--echo # +--echo # BUG#849763: Wrong result with second execution of prepared statement with semijoin + view +--echo # +CREATE TABLE t1 ( c varchar(1)) engine=innodb; +INSERT INTO t1 VALUES ('r'); + +CREATE TABLE t2 ( a integer, b varchar(1), c varchar(1)) engine=innodb; +INSERT INTO t2 VALUES (1,'r','r'); + +CREATE OR REPLACE VIEW v1 AS SELECT * FROM t1; + +PREPARE st1 FROM 'SELECT * FROM t2 WHERE a = SOME (SELECT a FROM v1 WHERE v1.c = t2.c)'; +EXECUTE st1; +EXECUTE st1; + +DROP VIEW v1; +DROP TABLE t1, t2; + +--echo # +--echo # BUG#858732: Wrong result with semijoin + loosescan + comma join +--echo # +CREATE TABLE t1 (f13 int(11) NOT NULL , PRIMARY KEY (f13)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (16),(24); + +CREATE TABLE t2 (f14 int(11) NOT NULL, f12 varchar(1) NOT NULL, KEY (f12,f14)) ENGINE=InnoDB; +INSERT INTO t2 VALUES (6,'y'); + +CREATE TABLE t3 (f12 varchar(1) NOT NULL) ENGINE=InnoDB; +INSERT INTO t3 VALUES ('r'),('s'),('t'),('v'),('w'),('x'),('y'); + +--echo # The following must use LooseScan but not join buffering +explain +SELECT * FROM t3 +WHERE f12 IN (SELECT alias2.f12 FROM t1 AS alias1, t2 AS alias2, t1 WHERE alias1.f13 = 24); + +SELECT * FROM t3 +WHERE f12 IN (SELECT alias2.f12 FROM t1 AS alias1, t2 AS alias2, t1 WHERE alias1.f13 = 24); + +DROP TABLE t1,t2,t3; + +--echo # +--echo # BUG#869012: Wrong result with semijoin + materialization + AND in WHERE +--echo # +CREATE TABLE t1 (f3 varchar(1) , f4 varchar(1) ) engine=InnoDB; +INSERT IGNORE INTO t1 VALUES ('x','x'),('x','x'); +CREATE TABLE t2 ( f4 varchar(1) ) ; +INSERT IGNORE INTO t2 VALUES ('g'); +CREATE TABLE t3 (f4 varchar(1) ) Engine=InnoDB; +INSERT IGNORE INTO t3 VALUES ('x'); + +set @tmp_869012=@@optimizer_switch; +SET optimizer_switch='semijoin=on,materialization=on'; +SELECT * +FROM t1 , t2 +WHERE ( t1.f4 ) IN ( SELECT f4 FROM t3 ) +AND t2.f4 != t1.f3 ; +set optimizer_switch= @tmp_869012; + +DROP TABLE t1,t2,t3; + + +--echo # +--echo # BUG#869001: Wrong result with semijoin + materialization + firstmatch + multipart key +--echo # +set @tmp869001_jcl= @@join_cache_level; +set @tmp869001_os= @@optimizer_switch; +SET join_cache_level=0; +SET optimizer_switch='materialization=on,semijoin=on,firstmatch=on,loosescan=off'; + +CREATE TABLE t1 ( f2 int, f3 varchar(1), KEY (f3,f2)) engine=innodb; +INSERT INTO t1 VALUES (8,'x'),(NULL,'x'),(8,'c'); + +CREATE TABLE t2 ( f4 varchar(1)) engine=innodb; +INSERT INTO t2 VALUES ('x'); + +CREATE TABLE t3 ( f1 int) engine=innodb; +INSERT INTO t3 VALUES (8),(6),(2),(9),(6); + +CREATE TABLE t4 ( f3 varchar(1)) engine=innodb; +INSERT INTO t4 VALUES ('p'),('j'),('c'); + +SELECT * +FROM t1 JOIN t2 ON (t2.f4 = t1.f3 ) +WHERE ( 8 ) IN ( + SELECT t3.f1 FROM t3 , t4 +); + +DROP TABLE t1, t2, t3, t4; +set join_cache_level= @tmp869001_jcl; +set optimizer_switch= @tmp869001_os; + +--echo # +--echo # Bug #881318: join cache + duplicate elimination + left join +--echo # with empty materialized derived inner table +--echo # + +CREATE TABLE t1 (b varchar(1)) ENGINE=InnoDB; + +CREATE TABLE t2 (a varchar(1)) ENGINE=InnoDB; +INSERT INTO t2 VALUES ('a'); + +CREATE TABLE t3 (a varchar(1), b varchar(1)) ENGINE=InnoDB; +INSERT INTO t3 VALUES ('c','c'); + +CREATE TABLE t4 (b varchar(1)) ENGINE=InnoDB; +INSERT INTO t4 VALUES ('c'), ('b'); + +CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1; + +EXPLAIN +SELECT * FROM t3 LEFT JOIN (v1,t2) ON t3.a = t2.a + WHERE t3.b IN (SELECT b FROM t4); +SELECT * FROM t3 LEFT JOIN (v1,t2) ON t3.a = t2.a + WHERE t3.b IN (SELECT b FROM t4); + +DROP VIEW v1; +DROP TABLE t1,t2,t3,t4; + +--echo # This must be the last in the file: set optimizer_switch=@subselect_sj2_tmp; diff --git a/mysql-test/t/subselect_sj_jcl6.test b/mysql-test/t/subselect_sj_jcl6.test index 44ee52686b3..e475adbf9a8 100644 --- a/mysql-test/t/subselect_sj_jcl6.test +++ b/mysql-test/t/subselect_sj_jcl6.test @@ -22,6 +22,8 @@ CREATE TABLE t0 (a INT); INSERT INTO t0 VALUES (0),(1),(2),(3),(4); CREATE TABLE t1 (a INT, b INT, KEY(a)); INSERT INTO t1 SELECT a, a from t0; +INSERT INTO t1 SELECT a+5, a from t0; +INSERT INTO t1 SELECT a+10, a from t0; CREATE TABLE t2 (a INT, b INT, PRIMARY KEY(a)); INSERT INTO t2 SELECT * FROM t1; UPDATE t1 SET a=3, b=11 WHERE a=4; diff --git a/mysql-test/t/subselect_sj_mat.test b/mysql-test/t/subselect_sj_mat.test index 0ea1c3873eb..a72128bf5ed 100644 --- a/mysql-test/t/subselect_sj_mat.test +++ b/mysql-test/t/subselect_sj_mat.test @@ -4,12 +4,12 @@ # set @subselect_sj_mat_tmp= @@optimizer_switch; -set optimizer_switch=ifnull(@subselect_mat_test_optimizer_switch_value, 'semijoin=on,firstmatch=on,loosescan=on'); +set optimizer_switch=ifnull(@subselect_mat_test_optimizer_switch_value, 'semijoin=on,firstmatch=on,loosescan=on,semijoin_with_cache=on'); set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; set @optimizer_switch_local_default= @@optimizer_switch; --disable_warnings -drop table if exists t1, t2, t3, t1i, t2i, t3i; +drop table if exists t1, t2, t3, t4, t5, t1i, t2i, t3i; drop table if exists columns; drop table if exists t1_16, t2_16, t3_16; drop view if exists v1, v2, v1m, v2m; @@ -1242,5 +1242,190 @@ select left(a1,7), left(a2,7) from t1_1024 where (a1,3) in (select substring(b1, drop table t1_1024, t2_1024; +--echo # +--echo # BUG##836491: Crash in Item_field::Item_field from add_ref_to_table_cond() with semijoin+materialization +--echo # + +CREATE TABLE t1 (c int, d varchar(1), KEY(d)) ; +INSERT INTO t1 VALUES (2,'x'),(2,'x'),(2,'j'),(2,'c'); + +CREATE TABLE t2 (a int, d varchar(1)) ; +INSERT INTO t2 VALUES (1,'x'); + +CREATE TABLE t3 (d varchar(1)) ; +INSERT INTO t3 VALUES ('x'),('x'),('j'),('c'); + +SELECT t2.a, t1.c +FROM t1, t2 +WHERE t2.d IN ( SELECT d FROM t3 ) +AND t1.d = t2.d +GROUP BY 1 , 2; + +drop table t1,t2,t3; + +--echo # +--echo # BUG#836523: Crash in JOIN::get_partial_cost_and_fanout with semijoin+materialization +--echo # +CREATE TABLE t1 (a varchar(1)); +INSERT INTO t1 VALUES ('a'),('a'); + +CREATE TABLE t2 (a varchar(1)); + +CREATE TABLE t3 (a int); +INSERT INTO t3 VALUES (1),(2); + +CREATE TABLE t4 (a varchar(1)); +INSERT INTO t4 VALUES ('a'),('a'); + +SELECT t1.a +FROM t1 +WHERE t1.a IN ( + SELECT t2.a + FROM t2, t3 +) +HAVING a IN ( + SELECT a + FROM t4 +); +DROP TABLE t1, t2, t3, t4; + +--echo # +--echo # BUG#836507: Crash in setup_sj_materialization_part1() with semijoin+materialization +--echo # +CREATE TABLE t1 (a int) ; +INSERT IGNORE INTO t1 VALUES (1),(1); + +CREATE TABLE t2 (a int); +INSERT INTO t2 VALUES (1); + +CREATE TABLE t3 (a int); + +CREATE TABLE t4 (a int); +INSERT INTO t4 VALUES (2),(2); + +CREATE TABLE t5 (a int); +INSERT INTO t5 VALUES (1); + +SELECT * FROM t1 +WHERE (a) IN ( + SELECT t5.a + FROM ( + t2 + LEFT JOIN ( t3 , t4 ) + ON 1 = 1 + ) + JOIN t5 +); + +DROP TABLE t1,t2,t3,t4,t5; + +--echo # +--echo # BUG#836532: Crash in Item_equal_fields_iterator::get_curr_field with semijoin+materialization +--echo # + +CREATE TABLE t2 (a int); +INSERT INTO t2 VALUES ('a'),('a'); + +CREATE TABLE t4 (a varchar(1)); +INSERT INTO t4 VALUES ('m'),('o'); + +CREATE TABLE t3 (a varchar(1) , b varchar(1) ) ; +INSERT INTO t3 VALUES ('b','b'); + +CREATE TABLE t5 (a varchar(1), KEY (a)) ; +INSERT INTO t5 VALUES ('d'),('e'); + +SELECT * +FROM t2 +WHERE t2.a = ALL ( + SELECT t4.a + FROM t4 + WHERE t4.a IN ( + SELECT t3.a + FROM t3 , t5 + WHERE ( t5.a = t3.b ) + ) +); + +DROP TABLE t2,t3,t4,t5; + +--echo # +--echo # BUG#860300: Second crash with get_fanout_with_deps() with semijoin + materialization +--echo # +set @tmp_860300=@@optimizer_switch; +set optimizer_switch='semijoin=on,materialization=on,loosescan=off,firstmatch=off'; +CREATE TABLE t1 (f2 int); +INSERT INTO t1 VALUES (9),(6); +CREATE TABLE t3 (f4 int); +CREATE TABLE t4 (f6 varchar(1)); + +SELECT * +FROM t3 +WHERE 'h' IN (SELECT f6 + FROM t4 + WHERE 5 IN (SELECT f2 FROM t1) + GROUP BY t4.f6); +DROP TABLE t1,t3,t4; +set optimizer_switch=@tmp_860300; + +--echo # +--echo # BUG#860535: Assertion `keypart_map' failed in mi_rkey with semijoin +--echo # +set @tmp_860535=@@optimizer_switch; +set optimizer_switch='semijoin=on,materialization=on,loosescan=off,firstmatch=off'; +CREATE TABLE t1 (f3 int) ; +INSERT INTO t1 VALUES (1),(7); + +CREATE TABLE t2 (f3 int , f5 varchar(1), KEY (f3)) ; +INSERT INTO t2 VALUES (7,'b'); + +CREATE TABLE t3 (f3 int , f4 varchar(1) , KEY(f3), KEY (f4,f3)) ; +INSERT INTO t3 VALUES (1,'t'),(7,'g'); + +CREATE TABLE t4 +SELECT f3 +FROM t1 WHERE ( f3 ) NOT IN ( + SELECT f3 + FROM t2 + WHERE f5 IN ( + SELECT f4 + FROM t3 + WHERE t3.f3 < 3 + ) +); +SELECT * FROM t4; +DROP TABLE t1, t2, t3, t4; +set optimizer_switch=@tmp_860535; + +--echo # +--echo # BUG#860553: Crash in create_ref_for_key with semijoin + materialization +--echo # +CREATE TABLE t1 (f1 int) ; +CREATE TABLE t2 (f5 varchar(52) NOT NULL) ; + +CREATE TABLE t3 (f1 varchar(3), f4 varchar(52) , KEY (f4), PRIMARY KEY (f1)); + +CREATE TABLE t4 (f3 int, KEY (f3)); +INSERT INTO t4 VALUES (17),(20); + +CREATE TABLE t5 (f2 int); +INSERT INTO t5 VALUES (0),(0); + +SELECT * +FROM t1 +JOIN t2 +ON ( t2.f5 ) IN ( + SELECT t3.f4 + FROM t3 + WHERE ( 1 ) IN ( + SELECT t4.f3 + FROM t4 , t5 + ) +); + +DROP TABLE t1, t2, t3, t4, t5; + + +--echo # This must be at the end: set optimizer_switch=@subselect_sj_mat_tmp; diff --git a/mysql-test/t/table_elim.test b/mysql-test/t/table_elim.test index 5576362b396..3b584ce2b38 100644 --- a/mysql-test/t/table_elim.test +++ b/mysql-test/t/table_elim.test @@ -223,7 +223,8 @@ create table t1 (a char(10) primary key); insert into t1 values ('foo'),('bar'); create table t2 (a char(10), unique key(a(2))); -insert into t2 values ('foo'),('bar'); +insert into t2 values + ('foo'),('bar'),('boo'),('car'),('coo'),('par'),('doo'),('tar'); explain select t1.* from t1 left join t2 on t2.a=t1.a; @@ -499,3 +500,21 @@ WHERE t3.f2 ; DROP TABLE t1,t2,t3,t4,t5; +--echo # +--echo # BUG#884184: Wrong result with RIGHT JOIN + derived_merge +--echo # +CREATE TABLE t1 (a int(11), b varchar(1)) ; +INSERT IGNORE INTO t1 VALUES (0,'g'); + +CREATE TABLE t3 ( a varchar(1)) ; +INSERT IGNORE INTO t3 VALUES ('g'); + +CREATE TABLE t2 ( a int(11) NOT NULL, PRIMARY KEY (a)) ; +create view v1 as SELECT t1.* FROM t1 LEFT JOIN t2 ON ( t1.a = t2.a ) WHERE t2.a <> 0; + +SELECT alias1.* FROM t3 LEFT JOIN v1 as alias1 ON ( t3.a = alias1.b ); +EXPLAIN SELECT alias1.* FROM t3 LEFT JOIN v1 as alias1 ON ( t3.a = alias1.b ); + +drop view v1; +DROP TABLE t1,t2,t3; + diff --git a/mysql-test/t/table_elim_debug.test b/mysql-test/t/table_elim_debug.test index 9f793169e4f..6400f798195 100644 --- a/mysql-test/t/table_elim_debug.test +++ b/mysql-test/t/table_elim_debug.test @@ -1,7 +1,8 @@ # # Table elimination (MWL#17) tests that need debug build # ---source include/have_debug.inc +# In MariaDB 5.3, one can switch table_elimination on/off without debug, too: +## --source include/have_debug.inc --disable_warnings drop table if exists t1, t2; diff --git a/mysql-test/t/trigger.test b/mysql-test/t/trigger.test index 85cf01a607f..1bd637f477c 100644 --- a/mysql-test/t/trigger.test +++ b/mysql-test/t/trigger.test @@ -2406,6 +2406,29 @@ USE test; --echo End of 5.1 tests. +# +# Test that using a trigger will not open mysql.proc +# +create table t1 (i int); +create table t2 (i int); +flush tables; +flush status; +delimiter //; +CREATE DEFINER=`root`@`localhost` TRIGGER trg AFTER DELETE ON t1 FOR EACH ROW BEGIN DELETE FROM t2 WHERE t2.i = OLD.i; END // +delimiter ;// +insert into t1 values (1),(2); +insert into t2 values (1),(2); +delete from t1 where i=1; +# +# If mysql.proc would be used we would have 4 here. 3 is the correct number. +# (CREATE TRIGGER will open t1 and then flush it) +# +show status like 'Opened_tables'; +select * from t1; +select * from t2; +drop table t1,t2; + +--echo End of 5.2 tests. --echo # --echo # Bug#34453 Can't change size of file (Errcode: 1224) @@ -2521,8 +2544,6 @@ select trigger_name from information_schema.triggers drop temporary table t1; drop table t1; - ---echo --echo # --echo # Bug #12362125: SP INOUT HANDLING IS BROKEN FOR TEXT TYPE. --echo # diff --git a/mysql-test/t/user_limits-master.opt b/mysql-test/t/user_limits-master.opt new file mode 100644 index 00000000000..107b2e4a27f --- /dev/null +++ b/mysql-test/t/user_limits-master.opt @@ -0,0 +1 @@ +--max-user-connections=1000 diff --git a/mysql-test/t/user_limits.test b/mysql-test/t/user_limits.test index 1bf3f9a3103..a8b549ca774 100644 --- a/mysql-test/t/user_limits.test +++ b/mysql-test/t/user_limits.test @@ -1,3 +1,4 @@ +skip enable when max_user_connections can be negative # # Test behavior of various per-account limits (aka quotas) # @@ -8,6 +9,8 @@ # Save the initial number of concurrent sessions --source include/count_sessions.inc +set @my_max_user_connections= @@global.max_user_connections; + # Prepare play-ground --disable_warnings drop table if exists t1; @@ -120,8 +123,18 @@ select * from t1; --replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK --error ER_USER_LIMIT_REACHED connect (muc5, localhost, mysqltest_1,,); -# Clean up + connection default; +# Test with negative max_user_connections +grant usage on *.* to mysqltest_1@localhost with max_user_connections -1; +show grants for mysqltest_1@localhost; +flush user_resources; +show grants for mysqltest_1@localhost; +--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK +--error ER_USER_LIMIT_REACHED +connect (muc5, localhost, mysqltest_1,,); + +# Clean up disconnect muc2; disconnect muc3; disconnect muc4; @@ -165,12 +178,37 @@ disconnect muca1; disconnect muca2; disconnect muca3; set global max_user_connections= 0; -drop user mysqltest_1@localhost; --enable_ps_protocol +# +# Test setting negative values of max_user_connections +# +grant usage on *.* to mysqltest_1@localhost with max_user_connections 0; +set global max_user_connections=-1; +show variables like "max_user_user_connections"; +select @@max_user_connections; +select @@global.max_user_connections; +# Check that we can't connect anymore except as root +--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK +--error ER_TOO_MANY_USER_CONNECTIONS +connect (muca2, localhost, mysqltest_1,,); +connect (muca2, localhost, root,,); +disconnect muca2; +connection default; +set global max_user_connections=1; +# Check that we can connect one time, not two +connect (muca2, localhost, mysqltest_1,,); +--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK +--error ER_TOO_MANY_USER_CONNECTIONS +connect (muca3, localhost, mysqltest_1,,); +disconnect muca2; +connection default; +drop user mysqltest_1@localhost; + # Final cleanup drop table t1; # Wait till all disconnects are completed --source include/wait_until_count_sessions.inc +set global max_user_connections= @my_max_user_connections; diff --git a/mysql-test/t/variables-master.opt b/mysql-test/t/variables-master.opt new file mode 100644 index 00000000000..e4b213a0323 --- /dev/null +++ b/mysql-test/t/variables-master.opt @@ -0,0 +1 @@ +--max-user-connections=1 diff --git a/mysql-test/t/variables.test b/mysql-test/t/variables.test index 3f7c4541a43..e9b428ff26c 100644 --- a/mysql-test/t/variables.test +++ b/mysql-test/t/variables.test @@ -23,6 +23,7 @@ set @my_max_delayed_threads =@@global.max_delayed_threads; set @my_max_heap_table_size =@@global.max_heap_table_size; set @my_max_insert_delayed_threads=@@global.max_insert_delayed_threads; set @my_max_join_size =@@global.max_join_size; +set @my_max_user_connections =@@global.max_user_connections; set @my_myisam_data_pointer_size =@@global.myisam_data_pointer_size; set @my_myisam_max_sort_file_size =@@global.myisam_max_sort_file_size; set @my_net_buffer_length =@@global.net_buffer_length; @@ -36,6 +37,7 @@ set @my_thread_cache_size =@@global.thread_cache_size; set @my_max_allowed_packet =@@global.max_allowed_packet; set @my_delay_key_write =@@global.delay_key_write; set @my_join_buffer_size =@@global.join_buffer_size; +set @my_log_warnings =@@global.log_warnings; # case insensitivity tests (new in 5.0) set @`test`=1; select @test, @`test`, @TEST, @`TEST`, @"teSt"; @@ -817,7 +819,7 @@ set global max_delayed_threads =@my_max_delayed_threads; set global max_heap_table_size =@my_max_heap_table_size; set global max_insert_delayed_threads=@my_max_insert_delayed_threads; set global max_join_size =@my_max_join_size; -set global max_user_connections =default; +set global max_user_connections =@my_max_user_connections; set global max_write_lock_count =default; set global myisam_data_pointer_size =@my_myisam_data_pointer_size; set global myisam_max_sort_file_size =@my_myisam_max_sort_file_size; @@ -832,6 +834,7 @@ set global thread_cache_size =@my_thread_cache_size; set global max_allowed_packet = default; set global delay_key_write =@my_delay_key_write; set global join_buffer_size =@my_join_buffer_size; +set global log_warnings =@my_log_warnings; # # Bug#28580 Repeatation of status variables diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index f464239b81e..df47526ef21 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -4465,7 +4465,27 @@ DROP TABLE t1; disconnect con2; disconnect con3; - # Check that all connections opened by test cases in this file are really # gone so execution of other tests won't be affected by their presence. --source include/wait_until_count_sessions.inc + +--echo # +--echo # lp:833600 Wrong result with view + outer join + uncorrelated subquery (non-semijoin) +--echo # + +CREATE TABLE t1 ( a int, b int ); +INSERT INTO t1 VALUES (0,0),(0,0); + +CREATE TABLE t2 ( a int, b int ); +INSERT IGNORE INTO t2 VALUES (1,0),(1,0); + +CREATE TABLE t3 ( b int ); +INSERT IGNORE INTO t3 VALUES (0),(0); + +CREATE OR REPLACE VIEW v2 AS SELECT * FROM t2; +SELECT * FROM t1 RIGHT JOIN v2 ON ( v2.a = t1.a ) WHERE v2.b IN ( SELECT b FROM t3 ) AND t1.b IS NULL ; + +SELECT * FROM t1 RIGHT JOIN v2 ON ( v2.a = t1.a ) WHERE v2.b IN ( SELECT b FROM t3 ) AND t1.b IS NULL ; + +DROP VIEW v2; +DROP TABLE t1, t2, t3; diff --git a/mysql-test/t/xtradb_mrr.test b/mysql-test/t/xtradb_mrr.test index 0653f9859c3..37a0159babd 100644 --- a/mysql-test/t/xtradb_mrr.test +++ b/mysql-test/t/xtradb_mrr.test @@ -420,6 +420,7 @@ DROP TABLE t1,t2; --echo # --echo # Testcase backport: Bug#43249 +--echo # (Note: Fixed by patch for BUG#42580) --echo # CREATE TABLE t1(c1 TIME NOT NULL, c2 TIME NULL, c3 DATE, PRIMARY KEY(c1), UNIQUE INDEX(c2)) engine=innodb; INSERT INTO t1 VALUES('8:29:45',NULL,'2009-02-01'); |