diff options
Diffstat (limited to 'mysql-test/t')
34 files changed, 1774 insertions, 120 deletions
diff --git a/mysql-test/t/archive.test b/mysql-test/t/archive.test index 9ecbee1ac9f..6f788fc3cc6 100644 --- a/mysql-test/t/archive.test +++ b/mysql-test/t/archive.test @@ -1713,3 +1713,17 @@ INSERT INTO t1 SELECT t1.* FROM t1,t1 t2,t1 t3,t1 t4,t1 t5,t1 t6; SELECT * FROM t1 ORDER BY f LIMIT 1; DROP TABLE t1; SET sort_buffer_size=DEFAULT; + +--echo # +--echo # BUG#11758979 - 51252: ARCHIVE TABLES CAUSE 100% CPU USAGE +--echo # AND HANG IN SHOW TABLE STATUS +--echo # (to be executed with valgrind) +CREATE TABLE t1(a BLOB, b VARCHAR(200)) ENGINE=ARCHIVE; +INSERT INTO t1 VALUES(NULL, ''); +FLUSH TABLE t1; +--echo # we need this select to workaround BUG#11764364 +SELECT * FROM t1; +CHECKSUM TABLE t1 EXTENDED; +FLUSH TABLE t1; +OPTIMIZE TABLE t1; +DROP TABLE t1; diff --git a/mysql-test/t/ctype_recoding.test b/mysql-test/t/ctype_recoding.test index 1f55aea414a..ee07ef24def 100644 --- a/mysql-test/t/ctype_recoding.test +++ b/mysql-test/t/ctype_recoding.test @@ -210,3 +210,21 @@ drop table t1; create table t1(a char character set latin1 default _cp1251 0xFF); --echo End of 4.1 tests + +SET CHARACTER SET DEFAULT; +--echo # +--echo # LP BUG#944504 Item_func_conv_charset tries to execute subquery constant +--echo # +SET optimizer_switch = 'in_to_exists=on'; +SET character_set_connection = utf8; +CREATE TABLE t1 ( a VARCHAR(1) ); +INSERT INTO t1 VALUES ('m'),('n'); +CREATE VIEW v1 AS SELECT 'w' ; +--error ER_CANT_AGGREGATE_2COLLATIONS +SELECT * FROM t1 WHERE a < ALL ( SELECT * FROM v1 ); +drop view v1; +drop table t1; +SET character_set_connection = default; +SET optimizer_switch= default; + +--echo #End of 5.3 tests diff --git a/mysql-test/t/derived_opt.test b/mysql-test/t/derived_opt.test index 42f3ce296e1..c2f831036e1 100644 --- a/mysql-test/t/derived_opt.test +++ b/mysql-test/t/derived_opt.test @@ -202,5 +202,15 @@ RIGHT JOIN ( drop table t1,t2,t3,t4; +--echo # +--echo # LP BUG#910123 MariaDB 5.3.3 causes 1093 error on Drupal +--echo # Fix: force materialization in case of conflict +--echo # +SET optimizer_switch='derived_merge=on'; +CREATE TABLE t1 ( i INT ); +INSERT INTO t1 VALUES ( (SELECT 1 FROM ( SELECT * FROM t1 ) as a) ); +drop table t1; +set optimizer_switch=@save_optimizer_switch; + # The following command must be the last one the file set optimizer_switch=@exit_optimizer_switch; diff --git a/mysql-test/t/derived_view.test b/mysql-test/t/derived_view.test index b674350af0a..9660dd1e5f5 100644 --- a/mysql-test/t/derived_view.test +++ b/mysql-test/t/derived_view.test @@ -1192,7 +1192,7 @@ 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 @save_optimizer_switch=@@optimizer_switch; SET SESSION optimizer_switch='derived_merge=off'; PREPARE st1 FROM @@ -1201,10 +1201,12 @@ EXECUTE st1; EXECUTE st1; DEALLOCATE PREPARE st1; +set SESSION optimizer_switch= @save_optimizer_switch; + 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 @@ -1226,7 +1228,7 @@ INSERT INTO t1 VALUES CREATE TABLE t3 (a varchar(35)); INSERT INTO t3 VALUES ('Miami'); - +SET @save_optimizer_switch=@@optimizer_switch; 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; @@ -1234,7 +1236,7 @@ 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; - +set SESSION optimizer_switch= @save_optimizer_switch; drop table t1,t2,t3; --echo # @@ -1277,6 +1279,74 @@ ORDER BY CONCAT(alias2.col_varchar_nokey); set max_heap_table_size= @tmp_882994; drop table t1,t2,t3; +--echo # +--echo # LP bug #917990: Bad estimate of #rows for derived table with LIMIT +--echo # + +CREATE TABLE t1 (a int); +INSERT INTO t1 VALUES + (8), (3), (4), (7), (9), (5), (1), (2); + +SELECT * FROM (SELECT * FROM t1 LIMIT 3) t; +EXPLAIN +SELECT * FROM (SELECT * FROM t1 LIMIT 3) t; + +DROP TABLE t1; + +--echo # +--echo # LP BUG#921878 incorrect check of items during columns union types +--echo # aggregation for merged derived tables +--echo # +SET @save_optimizer_switch=@@optimizer_switch; +SET SESSION optimizer_switch='derived_merge=on'; +CREATE TABLE t1 ( a ENUM( 'x', 'y' ) ); +insert into t1 values ('x'); +CREATE TABLE t2 LIKE t1; +insert into t1 values ('y'); +CREATE TABLE t3 LIKE t1; +INSERT INTO t3 + SELECT * FROM ( SELECT * FROM t1 ) AS A + UNION SELECT * FROM t2; +select * from t3; + +drop table t1,t2,t3; + +set SESSION optimizer_switch= @save_optimizer_switch; + +--echo # +--echo # LP BUG#944782: derived table from an information schema table +--echo # + +SET @save_optimizer_switch=@@optimizer_switch; +SET SESSION optimizer_switch='derived_merge=on'; +SET SESSION optimizer_switch='derived_with_keys=on'; + +CREATE TABLE t1 (c1 int PRIMARY KEY, c2 char(5)); + +EXPLAIN +SELECT COUNT(*) > 0 + FROM INFORMATION_SCHEMA.COLUMNS + INNER JOIN + (SELECT TABLE_SCHEMA, + GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX ASC) AS COL_NAMES + FROM INFORMATION_SCHEMA.STATISTICS + GROUP BY TABLE_SCHEMA) AS UNIQUES + ON ( COLUMNS.TABLE_SCHEMA = UNIQUES.TABLE_SCHEMA); + +# this query crashed in mariadb-5.5.20 +SELECT COUNT(*) > 0 + FROM INFORMATION_SCHEMA.COLUMNS + INNER JOIN + (SELECT TABLE_SCHEMA, + GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX ASC) AS COL_NAMES + FROM INFORMATION_SCHEMA.STATISTICS + GROUP BY TABLE_SCHEMA) AS UNIQUES + ON ( COLUMNS.TABLE_SCHEMA = UNIQUES.TABLE_SCHEMA); + +DROP TABLE t1; + +set SESSION optimizer_switch= @save_optimizer_switch; + # The following command must be the last one the file set optimizer_switch=@exit_optimizer_switch; set join_cache_level=@exit_join_cache_level; diff --git a/mysql-test/t/disabled.def b/mysql-test/t/disabled.def index 84c981c08c1..d8ef026914d 100644 --- a/mysql-test/t/disabled.def +++ b/mysql-test/t/disabled.def @@ -12,4 +12,3 @@ kill : Bug#11748945 2008-12-03 HHunger need some changes to be robust enough for pushbuild. read_many_rows_innodb : Bug#11748886 2010-11-15 mattiasj report already exists main.log_tables-big : Bug#11756699 2010-11-15 mattiasj report already exists - diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test index de0eac10927..f6091883168 100644 --- a/mysql-test/t/func_group.test +++ b/mysql-test/t/func_group.test @@ -1154,6 +1154,53 @@ drop table t1; --echo End of 5.1 tests --echo # +--echo # Bug #904345: MIN/MAX optimization with constant FALSE condition +--echo # + +CREATE TABLE t1 (a int NOT NULL, KEY(a)); +INSERT INTO t1 VALUES (10), (8), (11), (7), (15), (12), (9); + +CREATE TABLE t2 (a int, b int); +INSERT INTO t2 VALUES + (8,2), (6,9), (8,4), (5,3), (9,1); + +EXPLAIN EXTENDED +SELECT MAX(a) FROM t1 WHERE (1,2) IN (SELECT 3,4) AND a<10; +SELECT MAX(a) FROM t1 WHERE (1,2) IN (SELECT 3,4) AND a<10; + +EXPLAIN EXTENDED +SELECT MAX(a) FROM t1 WHERE (1,2) IN (SELECT a,b FROM t2 WHERE b<5) and a<10; +SELECT MAX(a) FROM t1 WHERE (1,2) IN (SELECT a,b FROM t2 WHERE b<5) and a<10; + +EXPLAIN EXTENDED +SELECT MAX(a) FROM t1 WHERE RAND()*0<>0 AND a<10; +SELECT MAX(a) FROM t1 WHERE RAND()*0<>0 AND a<10; + +DROP TABLE t1,t2; + +--echo # +--echo # Bug #879860: MIN/MAX for subquery returning empty set +--echo # + +CREATE TABLE t1 (a int PRIMARY KEY); +INSERT INTO t1 VALUES (1); + +CREATE TABLE t2 (a int NOT NULL); +INSERT INTO t2 VALUES (10); + +CREATE TABLE t3 ( a int, b int); +INSERT INTO t3 VALUES (19,1), (20,5); + +EXPLAIN EXTENDED +SELECT (SELECT MIN(t1.a) FROM t1,t2 WHERE t2.a = t3.b) FROM t3; +SELECT (SELECT MIN(t1.a) FROM t1,t2 WHERE t2.a = t3.b) FROM t3; + +DROP TABLE t1,t2,t3; + +--echo # +--echo End of 5.2 tests + +--echo # --echo # BUG#46680 - Assertion failed in file item_subselect.cc, --echo # line 305 crashing on HAVING subquery --echo # @@ -1292,5 +1339,3 @@ set @@optimizer_switch=@save_optimizer_switch; --echo # DROP TABLE IF EXISTS t1,t2,t3,empty1; -### ---echo End of 6.0 tests diff --git a/mysql-test/t/having.test b/mysql-test/t/having.test index 01342fdf5fa..3edf449c92d 100644 --- a/mysql-test/t/having.test +++ b/mysql-test/t/having.test @@ -589,10 +589,18 @@ ORDER BY t1.f1; DROP TABLE t1,t2; - --echo End of 5.1 tests --echo # +--echo # LP bug:938518 HAVING does not reject the result of aggregation +--echo # +CREATE TABLE t1 (pk INT PRIMARY KEY, a INT); +INSERT INTO t1 VALUES (2,7), (4,7), (6,2), (17,0); +SELECT MIN(t.pk) FROM t1, t1 as t WHERE t1.pk = 1; +SELECT MIN(t.pk) FROM t1, t1 as t WHERE t1.pk = 1 HAVING MIN(t.pk) < 10; +drop table t1; + +--echo # --echo # LP bug #791761: MAX over an empty join + HAVING --echo # @@ -616,3 +624,36 @@ SELECT MAX(t1.b) AS f FROM t1 JOIN t2 ON t2.a != 0 WHERE (SELECT f3 FROM t3) <> 0 HAVING f <> 6 ; DROP TABLE t1,t2,t3; + +--echo # +--echo # LP bug:806955 HAVING not observed with aggregate +subquery +--echo # + +CREATE TABLE t1 (f3 int, f10 varchar(1), f11 int, KEY (f10) ); +INSERT INTO t1 VALUES (NULL,'a',0),(8,'b',0); + +CREATE TABLE t2 (f2 int); +INSERT INTO t2 VALUES (7); + +CREATE TABLE t3 (f3 int); +INSERT INTO t3 VALUES (0),(8); + +set @save_optimizer_switch=@@optimizer_switch; +set optimizer_switch='semijoin=off,materialization=off'; + +SELECT MIN( t1.f10 ) AS field1 +FROM t1 , t2 +WHERE t2.f2 IN ( SELECT f3 FROM t3 ) +HAVING field1 < 's'; + +explain extended +SELECT MIN( t1.f10 ) AS field1 +FROM t1 , t2 +WHERE t2.f2 IN ( SELECT f3 FROM t3 ) +HAVING field1 < 's'; + +set optimizer_switch=@save_optimizer_switch; + +drop table t1,t2,t3; + +--echo End of 5.2 tests diff --git a/mysql-test/t/information_schema_all_engines.test b/mysql-test/t/information_schema_all_engines.test index b20ce60985c..9887845cb7e 100644 --- a/mysql-test/t/information_schema_all_engines.test +++ b/mysql-test/t/information_schema_all_engines.test @@ -9,6 +9,7 @@ use INFORMATION_SCHEMA; --replace_result Tables_in_INFORMATION_SCHEMA Tables_in_information_schema +--sorted_result show tables; # @@ -28,7 +29,7 @@ SELECT t.table_name, c1.column_name WHERE c2.table_schema = t.table_schema AND c2.table_name = t.table_name AND c2.column_name LIKE '%SCHEMA%' - ); + ) order by t.table_name; SELECT t.table_name, c1.column_name FROM information_schema.tables t INNER JOIN @@ -42,7 +43,7 @@ SELECT t.table_name, c1.column_name WHERE c2.table_schema = 'information_schema' AND c2.table_name = t.table_name AND c2.column_name LIKE '%SCHEMA%' - ); + ) order by t.table_name; # # Bug#24630 Subselect query crashes mysqld @@ -70,8 +71,11 @@ group by t.table_name order by num1, t.table_name; # # Bug #19147: mysqlshow INFORMATION_SCHEMA does not work # +--sorted_result --exec $MYSQL_SHOW information_schema +--sorted_result --exec $MYSQL_SHOW INFORMATION_SCHEMA +--sorted_result --exec $MYSQL_SHOW inf_rmation_schema # diff --git a/mysql-test/t/join.test b/mysql-test/t/join.test index bfe5f085e93..51bb72e1239 100644 --- a/mysql-test/t/join.test +++ b/mysql-test/t/join.test @@ -941,7 +941,6 @@ SELECT f1 , MIN(f2) FROM v2 GROUP BY f1; drop table t1,t2; drop view v1,v2; - --echo # --echo # BUG#47217 Lost optimization caused slowdown & wrong result. --echo # @@ -964,6 +963,46 @@ DROP TABLE t1, t2; --echo End of 5.1 tests --echo # +--echo # Bug #43368: STRAIGHT_JOIN DOESN'T WORK FOR NESTED JOINS +--echo # + +create table t1(c1 int primary key, c2 char(10)) engine=myisam; +create table t2(c1 int primary key, c2 char(10), ref_t1 int) engine=myisam; +create table t3(c1 int primary key, c2 char(10), ref_t1 int) engine=myisam; +create table t4(c1 int primary key, c2 char(10), ref_t1 int) engine=myisam; +insert into t1 values(1,'a'); +insert into t2 values(1,'a', 1); +insert into t3 values(1,'a', 1); +insert into t3 values(2,'b',2); +insert into t4 values(1,'a', 1); +insert into t4 values(2,'a', 2); +insert into t4 values(3,'a', 3); +insert into t4 values(4,'a', 4); +insert into t1 values(2,'b'); +insert into t1 values(3,'c'); + +EXPLAIN +SELECT * +FROM t4 JOIN + (t1 JOIN t3 ON t3.ref_t1=t1.c1 JOIN t2 ON t2.ref_t1=t1.c1) + ON t4.ref_t1=t1.c1; + +EXPLAIN +SELECT STRAIGHT_JOIN * +FROM t4 JOIN + (t1 JOIN t3 ON t3.ref_t1=t1.c1 JOIN t2 ON t2.ref_t1=t1.c1) + ON t4.ref_t1=t1.c1; + +EXPLAIN +SELECT * +FROM t4 STRAIGHT_JOIN + (t1 JOIN t3 ON t3.ref_t1=t1.c1 JOIN t2 ON t2.ref_t1=t1.c1) + ON t4.ref_t1=t1.c1; + +drop table t1,t2,t3,t4; +--echo End of 5.2 tests + +--echo # --echo # BUG#724275: Crash in JOIN::optimize in maria-5.3 --echo # diff --git a/mysql-test/t/join_cache.test b/mysql-test/t/join_cache.test index e39dcb72916..0962f1bf766 100644 --- a/mysql-test/t/join_cache.test +++ b/mysql-test/t/join_cache.test @@ -1526,12 +1526,14 @@ insert into t2 values (3,1, 'qwerty'),(3,4, 'qwerty'); insert into t2 values (4,1, 'qwerty'),(4,2, 'qwerty'),(4,3, 'qwerty'), (4,4, 'qwerty'); +flush status; set join_cache_level=5; select t2.f1, t2.f2, t2.f3 from t1,t2 where t1.f1=t2.f1 and t2.f2 between t1.f1 and t1.f2 and t2.f2 + 1 >= t1.f1 + 1; explain select t2.f1, t2.f2, t2.f3 from t1,t2 where t1.f1=t2.f1 and t2.f2 between t1.f1 and t2.f2; +show status like "Handler_icp%"; set join_cache_level=6; select t2.f1, t2.f2, t2.f3 from t1,t2 @@ -1539,6 +1541,7 @@ where t1.f1=t2.f1 and t2.f2 between t1.f1 and t1.f2 and t2.f2 + 1 >= t1.f1 + 1; explain select t2.f1, t2.f2, t2.f3 from t1,t2 where t1.f1=t2.f1 and t2.f2 between t1.f1 and t2.f2; +show status like "Handler_icp%"; set join_cache_level=7; select t2.f1, t2.f2, t2.f3 from t1,t2 @@ -1546,6 +1549,7 @@ where t1.f1=t2.f1 and t2.f2 between t1.f1 and t1.f2 and t2.f2 + 1 >= t1.f1 + 1; explain select t2.f1, t2.f2, t2.f3 from t1,t2 where t1.f1=t2.f1 and t2.f2 between t1.f1 and t2.f2; +show status like "Handler_icp%"; set join_cache_level=8; select t2.f1, t2.f2, t2.f3 from t1,t2 @@ -1553,6 +1557,7 @@ where t1.f1=t2.f1 and t2.f2 between t1.f1 and t1.f2 and t2.f2 + 1 >= t1.f1 + 1; explain select t2.f1, t2.f2, t2.f3 from t1,t2 where t1.f1=t2.f1 and t2.f2 between t1.f1 and t2.f2; +show status like "Handler_icp%"; drop table t1,t2; set join_cache_level=default; @@ -3423,5 +3428,164 @@ SET optimizer_switch=@tmp_optimizer_switch; DROP TABLE t1,t2,t3,t4; +--echo # +--echo # Bug#53305 Duplicate weedout + join buffer (join cache --level=7,8) +--echo # + +create table t1 (uid int, fid int, index(uid)); +insert into t1 values + (1,1), (1,2), (1,3), (1,4), + (2,5), (2,6), (2,7), (2,8), + (3,1), (3,2), (3,9); + +create table t2 (uid int primary key, name varchar(128), index(name)); +insert into t2 values + (1, "A"), (2, "B"), (3, "C"), (4, "D"), (5, "E"), + (6, "F"), (7, "G"), (8, "H"), (9, "I"); + +create table t3 (uid int, fid int, index(uid)); +insert into t3 values + (1,1), (1,2), (1,3),(1,4), + (2,5), (2,6), (2,7), (2,8), + (3,1), (3,2), (3,9); + +set @tmp_optimizer_switch=@@optimizer_switch; +set @@optimizer_switch='semijoin=on'; +set optimizer_switch='materialization=off'; +set optimizer_switch='loosescan=off,firstmatch=off'; +set optimizer_switch='mrr_sort_keys=off'; +set join_cache_level=7; + +create table t4 (uid int primary key, name varchar(128), index(name)); +insert into t4 values + (1, "A"), (2, "B"), (3, "C"), (4, "D"), (5, "E"), + (6, "F"), (7, "G"), (8, "H"), (9, "I"); + +explain select name from t2, t1 + where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid) + and t2.uid=t1.fid; + +--sorted_result +select name from t2, t1 + where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid) + and t2.uid=t1.fid; + +set join_cache_level = default; +set optimizer_switch=@tmp_optimizer_switch; + +drop table t1,t2,t3,t4; + +--echo # +--echo # Bug#50358 - semijoin execution of subquery with outerjoin +--echo # emplying join buffer +--echo # + +CREATE TABLE t1 (i int); +CREATE TABLE t2 (i int); +CREATE TABLE t3 (i int); +INSERT INTO t1 VALUES (1), (2); +INSERT INTO t2 VALUES (6); +INSERT INTO t3 VALUES (1), (2); + +set @tmp_optimizer_switch=@@optimizer_switch; +set @@optimizer_switch='semijoin=on'; +set optimizer_switch='materialization=on'; + +set join_cache_level=0; +EXPLAIN +SELECT * FROM t1 WHERE t1.i IN + (SELECT t3.i FROM t3 LEFT JOIN t2 ON t2.i=t3.i); +SELECT * FROM t1 WHERE t1.i IN + (SELECT t3.i FROM t3 LEFT JOIN t2 ON t2.i=t3.i); + +set join_cache_level=2; +EXPLAIN +SELECT * FROM t1 WHERE t1.i IN + (SELECT t3.i FROM t3 LEFT JOIN t2 ON t2.i=t3.i); +SELECT * FROM t1 WHERE t1.i IN + (SELECT t3.i FROM t3 LEFT JOIN t2 ON t2.i=t3.i); + +set join_cache_level = default; +set optimizer_switch=@tmp_optimizer_switch; + +DROP TABLE t1,t2,t3; + +--echo # +--echo # Bug #12546542: missing row with semijoin=off + join cache +--echo # (LP bug #922971) +--echo # + +CREATE TABLE t1 (a varchar(1024)); +INSERT INTO t1 VALUES ('v'), ('we'); +CREATE TABLE t2 ( + a varchar(1024) CHARACTER SET utf8 DEFAULT NULL, b int, c int +); +INSERT INTO t2 VALUES ('we',4,NULL), ('v',1305673728,6); +CREATE TABLE t3 (b int, c int); +INSERT INTO t3 VALUES (4,4); + +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='semijoin=off'; +set optimizer_switch='materialization=off'; + +set join_cache_level=0; +EXPLAIN +SELECT * FROM t1 + WHERE a IN (SELECT t2.a FROM t2 LEFT JOIN t3 ON t2.b = t3.b + WHERE t2.c < 10 OR t3.c > 1); + +SELECT * FROM t1 + WHERE a IN (SELECT t2.a FROM t2 LEFT JOIN t3 ON t2.b = t3.b + WHERE t2.c < 10 OR t3.c > 1); + +set join_cache_level=2; +EXPLAIN +SELECT * FROM t1 + WHERE a IN (SELECT t2.a FROM t2 LEFT JOIN t3 ON t2.b = t3.b + WHERE t2.c < 10 OR t3.c > 1); +SELECT * FROM t1 + WHERE a IN (SELECT t2.a FROM t2 LEFT JOIN t3 ON t2.b = t3.b + WHERE t2.c < 10 OR t3.c > 1); + +set join_cache_level = default; +set optimizer_switch=@tmp_optimizer_switch; + +DROP TABLE t1,t2,t3; + +--echo # +--echo # Bug #925985: LEFT JOIN with optimize_join_buffer_size=off + +--echo # join_buffer_size > join_buffer_space_limit +--echo # + +CREATE TABLE t1 (a int); +INSERT INTO t1 VALUES (5), (3); + +CREATE TABLE t2 (a int, b int); +INSERT INTO t2 VALUES + (3,30), (1,10), (7,70), (2,20), + (3,31), (1,11), (7,71), (2,21), + (3,32), (1,12), (7,72), (2,22); + +CREATE TABLE t3 (b int, c int); +INSERT INTO t3 VALUES (32, 302), (42,400), (30,300); + +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='optimize_join_buffer_size=off'; +set join_buffer_space_limit=4096; +set join_buffer_size=4096*2; +set join_cache_level=2; +set optimizer_switch='outer_join_with_cache=on'; + +EXPLAIN +SELECT * FROM t1, t2 LEFT JOIN t3 ON t2.b=t3.b WHERE t1.a=t2.a; +SELECT * FROM t1, t2 LEFT JOIN t3 ON t2.b=t3.b WHERE t1.a=t2.a; + +set join_buffer_space_limit=default; +set join_buffer_size=default; +set join_cache_level=default; +set optimizer_switch=@tmp_optimizer_switch; + +DROP TABLE t1,t2,t3; + # this must be the last command in the file set @@optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/t/join_outer.test b/mysql-test/t/join_outer.test index 04816402205..3fae21ac738 100644 --- a/mysql-test/t/join_outer.test +++ b/mysql-test/t/join_outer.test @@ -1221,4 +1221,65 @@ SET SESSION join_cache_level=default; DROP TABLE t1,t2,t3; +--echo # +--echo # LP bug #943543: LEFT JOIN converted to JOIN with +--echo # ORed IS NULL(primary key) in WHERE clause +--echo # + +CREATE TABLE t1 ( + a int, b int NOT NULL, pk int NOT NULL, + PRIMARY KEY (pk), INDEX idx(b) +); +INSERT INTO t1 VALUES + (NULL,1,1), (6,2,2), (5,3,3), (NULL,4,4), + (1,9,6), (8,5,7), (NULL,8,8), (8,1,5); + +CREATE TABLE t2 (pk int PRIMARY KEY); +INSERT INTO t2 VALUES (3), (8), (5); + +EXPLAIN EXTENDED +SELECT t1.pk FROM t2 JOIN t1 ON t2.pk = t1.a + WHERE t1.b BETWEEN 5 AND 6 AND t1.pk IS NULL OR t1.pk = 5 +ORDER BY t1.pk; +SELECT t1.pk FROM t2 JOIN t1 ON t2.pk = t1.a + WHERE t1.b BETWEEN 5 AND 6 AND t1.pk IS NULL OR t1.pk = 5 +ORDER BY t1.pk; + +EXPLAIN EXTENDED +SELECT t1.pk FROM t2 LEFT JOIN t1 ON t2.pk = t1.a + WHERE t1.b BETWEEN 5 AND 6 AND t1.pk IS NULL OR t1.pk = 5 +ORDER BY t1.pk; +SELECT t1.pk FROM t2 LEFT JOIN t1 ON t2.pk = t1.a + WHERE t1.b BETWEEN 5 AND 6 AND t1.pk IS NULL OR t1.pk = 5 +ORDER BY t1.pk; + +DROP TABLE t2; + +CREATE TABLE t2 (c int, d int, KEY (c)); +INSERT INTO t2 VALUES + (3,30), (8,88), (5,50), (8,81), + (4,40), (9,90), (7,70), (9,90), + (13,130), (18,188), (15,150), (18,181), + (14,140), (19,190), (17,170), (19,190); + +INSERT INTO t1 VALUES (8,5,9); + +EXPLAIN EXTENDED +SELECT t1.b, t2.c, t2.d FROM t2 JOIN t1 ON t2.c = t1.a + WHERE t1.pk BETWEEN 5 AND 6 AND t1.b IS NULL OR t1.b = 5 +ORDER BY t1.b; +SELECT t1.b, t2.c, t2.d FROM t2 JOIN t1 ON t2.c = t1.a + WHERE t1.pk BETWEEN 5 AND 6 AND t1.b IS NULL OR t1.b = 5 +ORDER BY t1.b; + +EXPLAIN EXTENDED +SELECT t1.b, t2.c, t2.d FROM t2 LEFT JOIN t1 ON t2.c = t1.a + WHERE t1.pk BETWEEN 5 AND 6 AND t1.b IS NULL OR t1.b = 5 +ORDER BY t1.b; +SELECT t1.b, t2.c, t2.d FROM t2 LEFT JOIN t1 ON t2.c = t1.a + WHERE t1.pk BETWEEN 5 AND 6 AND t1.b IS NULL OR t1.b = 5 +ORDER BY t1.b; + +DROP TABLE t1,t2; + SET optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/t/join_outer_innodb.test b/mysql-test/t/join_outer_innodb.test index 40add7f488f..8d6fa71bc8f 100644 --- a/mysql-test/t/join_outer_innodb.test +++ b/mysql-test/t/join_outer_innodb.test @@ -24,3 +24,85 @@ SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id WHERE t1.name LIKE 'A%' OR FALSE; DROP TABLE t1,t2; + +# +# Bug #848652: crash with RIGHT JOIN and GROUP BY +# + +CREATE TABLE t1(a int, b int, KEY (a), PRIMARY KEY (b)) ENGINE=InnoDB; + +CREATE TABLE t2 (b int, PRIMARY KEY (b)); +INSERT INTO t2 VALUES (4),(9); + +SELECT STRAIGHT_JOIN t1.a FROM t1 RIGHT JOIN t2 ON t1.b = t2.b + WHERE (t1.b NOT BETWEEN 1 AND 7 OR t1.a IS NULL AND t1.b = t2.b) AND t2.b = 4 +GROUP BY 1; + +DROP TABLE t1,t2; + +--echo # +--echo Bug #59487: WRONG RESULT WITH STRAIGHT_JOIN AND RIGHT JOIN +--echo # + +CREATE TABLE t1 ( + pk int(11) NOT NULL, + col_varchar_10_latin1_key varchar(10) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +INSERT INTO t1 VALUES (1,'1'); +CREATE TABLE t2 ( + pk int(11) NOT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +INSERT INTO t2 VALUES (1); +CREATE TABLE t3 ( + pk int(11) NOT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +INSERT INTO t3 VALUES (1); +CREATE TABLE t4 ( + pk int(11) NOT NULL, + col_int int(11) DEFAULT NULL, + col_int_key int(11) DEFAULT NULL, + col_varchar_10_latin1_key varchar(10) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +INSERT INTO t4 VALUES (1,1,1,'1'); +CREATE TABLE t5 ( + col_int int(11) DEFAULT NULL, + col_varchar_10_utf8_key varchar(10) CHARACTER SET utf8 DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +INSERT INTO t5 VALUES (1,'1'); +CREATE TABLE t6 ( + col_int_key int(11) DEFAULT NULL, + col_varchar_10_latin1_key varchar(10) DEFAULT NULL, + pk int(11) NOT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +INSERT INTO t6 VALUES (1,'1',1); + +# EXPLAIN of query above (t2 is before t5 in plan) + +let $rest_of_query=t6a.pk, t2.pk +FROM t6 AS t6a + LEFT JOIN + ( + t2 + RIGHT JOIN + ( + (t1 LEFT JOIN (t4 JOIN t3 ON t4.col_int) ON t4.col_int_key = t1.pk) + LEFT JOIN + (t5 JOIN t6 AS t6b + ON t5.col_varchar_10_utf8_key = t6b.col_varchar_10_latin1_key) + ON t1.pk = t5.col_int + ) + ON t4.col_varchar_10_latin1_key = t1.col_varchar_10_latin1_key + AND t5.col_varchar_10_utf8_key = 0 + ) + ON t6a.pk IS TRUE +WHERE t6b.col_int_key IS TRUE; + +eval SELECT STRAIGHT_JOIN $rest_of_query; +eval EXPLAIN SELECT STRAIGHT_JOIN $rest_of_query; + +# right result (same query, just remove STRAIGHT_JOIN): + +eval SELECT $rest_of_query; +eval EXPLAIN SELECT $rest_of_query; + +drop table t1,t2,t3,t4,t5,t6; diff --git a/mysql-test/t/key_cache.test b/mysql-test/t/key_cache.test index f415d670bf1..c19509ef945 100644 --- a/mysql-test/t/key_cache.test +++ b/mysql-test/t/key_cache.test @@ -251,6 +251,22 @@ DROP TABLE t1; # End of 4.1 tests +--echo # +--echo # Bug#12361113: crash when load index into cache +--echo # + +--echo # Note that this creates an empty disabled key cache! +SET GLOBAL key_cache_none.key_cache_block_size = 1024; +CREATE TABLE t1 (a INT, b INTEGER NOT NULL, KEY (b) ) ENGINE = MYISAM; +INSERT INTO t1 VALUES (1, 1); +--error ER_UNKNOWN_KEY_CACHE +CACHE INDEX t1 in key_cache_none; +--echo # The bug crashed the server at LOAD INDEX below. Now it will succeed +--echo # since the default cache is used due to CACHE INDEX failed for +--echo # key_cache_none. +LOAD INDEX INTO CACHE t1; +DROP TABLE t1; + # End of 5.1 tests # diff --git a/mysql-test/t/lock_multi.test b/mysql-test/t/lock_multi.test index f05821af5a8..044af931489 100644 --- a/mysql-test/t/lock_multi.test +++ b/mysql-test/t/lock_multi.test @@ -5,6 +5,7 @@ --disable_warnings drop table if exists t1,t2; +drop DATABASE if exists mysqltest_1; --enable_warnings # Test to see if select will get the lock ahead of low priority update diff --git a/mysql-test/t/merge.test b/mysql-test/t/merge.test index 90386a97fc6..eecf892869f 100644 --- a/mysql-test/t/merge.test +++ b/mysql-test/t/merge.test @@ -1848,9 +1848,13 @@ CREATE TABLE t1(a INT); --echo # Test reattach merge failure LOCK TABLES m1 READ; --echo # Replace 't1' with 't3' table using file operations. -remove_file $MYSQLD_DATADIR/test/t1.frm; -remove_file $MYSQLD_DATADIR/test/t1.MYI; -remove_file $MYSQLD_DATADIR/test/t1.MYD; +# move + remove is a work around for windows. +move_file $MYSQLD_DATADIR/test/t1.frm $MYSQLD_DATADIR/test/oldt1.frm; +move_file $MYSQLD_DATADIR/test/t1.MYI $MYSQLD_DATADIR/test/oldt1.MYI; +move_file $MYSQLD_DATADIR/test/t1.MYD $MYSQLD_DATADIR/test/oldt1.MYD; +remove_file $MYSQLD_DATADIR/test/oldt1.frm; +remove_file $MYSQLD_DATADIR/test/oldt1.MYI; +remove_file $MYSQLD_DATADIR/test/oldt1.MYD; copy_file $MYSQLD_DATADIR/test/t3.frm $MYSQLD_DATADIR/test/t1.frm; copy_file $MYSQLD_DATADIR/test/t3.MYI $MYSQLD_DATADIR/test/t1.MYI; copy_file $MYSQLD_DATADIR/test/t3.MYD $MYSQLD_DATADIR/test/t1.MYD; diff --git a/mysql-test/t/myisam-big.test b/mysql-test/t/myisam-big.test new file mode 100644 index 00000000000..2fec2450ecd --- /dev/null +++ b/mysql-test/t/myisam-big.test @@ -0,0 +1,64 @@ +# +# Test bugs in the MyISAM code that require more space/time +--source include/big_test.inc + +# Initialise +--disable_warnings +drop table if exists t1,t2; +--enable_warnings + +# +# BUG#925377: +# Querying myisam table metadata while 'alter table..enable keys' is +# running may corrupt the table +# +create table t1 (id int, sometext varchar(100)) engine=myisam; +insert into t1 values (1, "hello"),(2, "hello2"),(4, "hello3"),(4, "hello4"); +create table t2 like t1; +insert into t1 select * from t1; +insert into t2 select * from t1; +insert into t1 select * from t1; +insert into t2 select * from t1; +insert into t1 select * from t1; +insert into t2 select * from t1; +insert into t1 select * from t1; +insert into t2 select * from t1; +insert into t1 select * from t1; +insert into t2 select * from t1; +insert into t1 select * from t1; +insert into t2 select * from t1; +insert into t1 select * from t1; +insert into t2 select * from t1; +insert into t1 select * from t1; +insert into t2 select * from t1; +insert into t1 select * from t1; +insert into t2 select * from t1; +insert into t1 select * from t1; +insert into t2 select * from t1; +insert into t1 select * from t1; +insert into t2 select * from t1; +insert into t1 select * from t1; +insert into t2 select * from t1; +insert into t1 select * from t1; +insert into t2 select * from t1; +insert into t1 select * from t1; +insert into t2 select * from t1; +insert into t1 select * from t1; +select count(*) from t1; +connect (con2,localhost,root,,); +connection con2; +alter table t1 add index (id), add index(sometext), add index(sometext,id); +alter table t1 disable keys; +send alter table t1 enable keys; +connection default; +--sleep 1 +--disable_query_log +--disable_result_log +show table status like 't1'; +--enable_query_log +--enable_result_log +connection con2; +reap; +disconnect con2; +connection default; +drop table t1,t2; diff --git a/mysql-test/t/myisam_icp.test b/mysql-test/t/myisam_icp.test index 0e306a850c5..5bee6e5acaa 100644 --- a/mysql-test/t/myisam_icp.test +++ b/mysql-test/t/myisam_icp.test @@ -263,3 +263,4 @@ SET optimizer_switch=@save_optimizer_switch; DROP TABLE t1,t2,t3,t4; set optimizer_switch=@myisam_icp_tmp; + diff --git a/mysql-test/t/myisam_icp_notembedded.test b/mysql-test/t/myisam_icp_notembedded.test new file mode 100644 index 00000000000..139a9edf648 --- /dev/null +++ b/mysql-test/t/myisam_icp_notembedded.test @@ -0,0 +1,139 @@ +-- source include/not_embedded.inc + +--disable_warnings +drop table if exists t1,t2; +--enable_warnings + +set @myisam_icp_notembedded_tmp=@@optimizer_switch; +set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; + +--echo # +--echo # BUG#933412: Server crashes in _mi_put_key_in_record on KILL QUERY with ICP, STRAIGHT_JOIN +--echo # +CREATE TABLE t1 ( + b INT, + c VARCHAR(1) NOT NULL, + d DATETIME, + KEY (c, b) +) ENGINE=MyISAM; +--echo # INSERT some data +--disable_query_log +INSERT INTO t1 ( b, d, c ) VALUES +(4,'2005-01-08 00:00:00','f'), +(1,'2004-05-20 10:45:51','c'),(2,'2006-08-11 21:33:49','e'), +(5,'2003-05-19 00:20:40','a'),(3,'2005-01-03 06:18:39','a'), +(7,'2008-11-25 18:18:14','b'),(6,'2008-11-09 15:53:46','b'), +(9,'2003-03-01 03:40:36','c'),(8,'2003-09-25 23:14:09','d'), +(0,'2007-01-17 09:18:31','f'),(9,'2008-09-08 09:52:24','c'), +(2,'2008-03-10 00:00:00','a'),(0,'2003-03-14 09:31:07','c'), +(4,'2005-04-25 00:00:00','h'),(6,'2001-08-01 05:55:55','e'), +(3,'2005-04-09 01:22:48','f'),(7,'2009-11-12 13:27:22','r'), +(0,'2009-03-28 05:05:28','h'),(15,'2005-05-16 04:35:41','f'), +(7,'2006-03-26 05:19:58','c'),(9,'2002-10-06 02:17:00','g'), +(4,'2007-01-28 03:28:20','b'),(1,'2009-04-22 10:16:40','c'), +(2,'2003-01-01 19:39:00','f'),(0,'2008-05-03 19:16:29','t'), +(2,'2005-01-28 00:00:00','j'),(8,'2004-01-10 00:00:00','w'), +(8,'2000-06-13 21:56:37','a'),(5,'2001-03-21 19:24:49','o'), +(99,'2003-12-20 21:29:06','f'),(0,'1900-01-01 00:00:00','w'), +(7,'2000-12-19 00:00:00','c'),(0,'2000-03-03 06:10:19','l'), +(3,'2000-08-11 00:00:00','q'),(0,'2007-05-25 03:46:41','e'), +(241,'2005-05-17 00:00:00','j'),(4,'2005-11-02 00:44:06','r'), +(43,'2001-07-11 00:00:00','a'),(1,'2008-12-01 18:30:27','z'), +(4,'2004-10-25 00:00:00','i'),(5,'2000-04-08 12:12:01','c'), +(0,'1900-01-01 00:00:00','f'),(9,'2002-05-13 22:47:02','p'), +(1,'2008-10-09 15:39:40','d'),(3,'2004-06-24 00:00:00','d'), +(0,'2008-03-06 00:00:00','r'),(9,'2007-04-16 18:40:03','i'), +(3,'2008-03-16 19:49:37','t'),(7,'2003-07-15 08:11:21','d'), +(8,'2005-02-11 00:04:53','r'),(0,'2002-09-21 00:00:00','y'), +(3,'2004-11-03 00:37:21','z'),(6,'2007-10-18 00:00:00','e'), +(6,'2007-01-21 10:42:56','o'),(5,'2000-03-26 21:21:04','b'), +(9,'2001-03-15 08:08:21','e'),(1,'2001-10-16 12:56:59','a'), +(6,'2004-05-01 23:45:55','o'),(4,'2000-03-04 00:00:00','f'), +(9,'2002-12-03 16:48:28','e'),(8,'2003-01-09 00:36:07','m'), +(1,'2006-06-22 04:32:41','s'),(8,'2008-09-20 05:01:48','q'), +(4,'2006-06-02 22:15:31','g'),(2,'2002-05-14 07:07:42','e'), +(7,'2005-06-05 01:30:42','r'),(127,'2004-05-11 01:56:48','a'), +(210,'2003-11-05 00:41:34','z'),(5,'1900-01-01 00:00:00','h'), +(1,'2006-04-16 00:00:00','f'),(7,'2000-12-17 00:00:00','x'), +(8,'2009-05-09 20:43:07','b'),(175,'2008-11-26 16:33:09','p'), +(0,'2002-05-09 21:18:44','v'),(8,'2002-06-01 11:32:25','k'), +(1,'2008-11-09 23:56:00','a'),(0,'2008-01-08 10:18:46','c'), +(2,'2005-04-16 00:00:00','o'),(5,'2002-08-25 00:00:00','b'), +(64,'2005-12-05 21:51:52','b'),(4,'2005-08-10 00:00:00','i'), +(6,'2006-03-23 00:00:00','d'),(9,'2007-01-27 00:00:00','i'), +(8,'2008-08-16 00:00:00','a'),(7,'2003-01-16 12:13:18','k'), +(0,'2003-06-22 00:00:00','v'),(5,'2008-06-20 05:43:56','u'), +(8,'2004-09-23 18:57:17','e'),(1,'2000-12-26 00:00:00','y'), +(4,'2009-06-01 13:00:28','e'),(1,'2009-11-18 06:28:48','m'), +(0,'2004-06-12 10:01:10','e'),(2,'2005-10-16 01:48:55','e'), +(5,'2001-12-23 09:50:21','l'),(6,'1900-01-01 00:00:00','a'), +(1,'2001-10-28 00:00:00','d'),(1,'2008-07-12 23:30:19','s'), +(0,'2002-10-11 16:51:16','r'),(4,'2007-09-18 06:27:10','x'), +(1,'2007-02-21 12:28:14','e'),(6,'2001-09-16 00:00:00','f'), +(0,'2007-09-20 02:25:45','c'),(0,'2006-08-07 03:25:56','j'), +(8,'2006-12-04 20:20:32','t'),(7,'2007-09-05 10:13:10','i'), +(9,'2006-04-12 17:59:57','t'),(2,'2009-04-28 00:06:09','b'), +(8,'2000-01-07 00:00:00','b'),(7,'2000-03-25 10:04:41','k'), +(4,'2000-07-10 00:44:55','w'),(9,'2007-09-22 14:26:26','j'), +(9,'2003-09-11 22:41:17','a'),(0,'2004-06-07 13:52:32','c'), +(8,'2008-10-09 00:00:00','p'),(1,'2007-04-01 00:00:00','c'), +(9,'2000-12-05 00:00:00','i'),(3,'1900-01-01 00:00:00','a'), +(3,'2005-12-24 21:50:54','e'),(8,'2009-07-21 19:34:55','n'), +(9,'2005-11-13 17:57:56','d'),(7,'2004-10-07 06:41:39','l'), +(1,'2004-11-20 08:05:08','u'),(3,'2005-05-25 00:00:00','r'), +(1,'2006-09-02 14:16:41','u'),(8,'2006-01-07 00:00:00','a'), +(9,'2003-04-05 00:54:20','w'),(2,'2003-12-22 00:00:00','a'), +(9,'2006-04-16 17:31:40','e'),(6,'2005-02-10 14:22:46','e'), +(7,'2004-04-27 05:54:52','p'),(1,'2005-12-07 00:00:00','t'), +(5,'2004-04-03 20:56:28','d'),(4,'2000-09-07 05:17:16','h'), +(2,'2004-08-04 16:10:42','i'),(1,'2007-03-04 00:00:00','b'), +(9,'1900-01-01 00:00:00','d'),(1,'2000-05-12 23:02:50','m'), +(2,'1900-01-01 00:00:00','l'),(1,'1900-01-01 00:00:00','k'), +(4,'2000-07-14 01:25:18','d'),(5,'2009-08-21 00:00:00','w'), +(6,'2009-05-25 13:33:54','f'),(7,'2006-06-13 00:00:00','e'), +(8,'1900-01-01 00:00:00','a'),(6,'2004-02-24 00:00:00','j'), +(0,'2003-05-21 07:03:46','k'),(9,'1900-01-01 00:00:00','e'), +(2,'1900-01-01 00:00:00','y'),(2,'2000-12-22 00:00:00','e'), +(3,'2003-09-26 00:00:00','f'),(2,'2001-01-13 08:20:19','h'), +(9,'2008-09-23 20:03:28','n'),(5,'2007-03-20 02:41:38','s'), +(1,'2009-02-14 10:27:18','a'),(0,'2001-08-10 17:44:05','s'), +(3,'2008-01-20 12:49:54','v'),(1,'2001-05-05 09:09:59','r'); +--enable_query_log +CREATE TABLE t2 ( a INT ) ENGINE=MyISAM; +INSERT INTO t2 VALUES + (7),(3),(7),(3); + +# 'con2' will be the connection that will run all the KILLable statements +--connect (con2,127.0.0.1,root,,test) + +--let $run = 300 +--let $con_id = `SELECT CONNECTION_ID()` + +--echo # Now run a number of ICP queries while trying to kill them +--disable_query_log +--disable_result_log +while ($run) +{ + --send + SELECT * FROM t1 AS alias1 STRAIGHT_JOIN t1 AS alias2 + ON alias2.c = alias1.c + WHERE alias2.b >= 9; + + --connect (con1,127.0.0.1,root,,test) + --eval KILL QUERY $con_id + --disconnect con1 + + --dec $run + + --connection con2 + --error 0,ER_QUERY_INTERRUPTED + --reap +} +--enable_result_log +--enable_query_log +--disconnect con2 +--connection default +DROP TABLE t1,t2; + +set optimizer_switch=@myisam_icp_notembedded_tmp; + diff --git a/mysql-test/t/myisam_mrr.test b/mysql-test/t/myisam_mrr.test index 1e070ec9a34..28385c61b0f 100644 --- a/mysql-test/t/myisam_mrr.test +++ b/mysql-test/t/myisam_mrr.test @@ -268,5 +268,58 @@ set @@join_cache_level= @tmp_730133_jcl; set @@optimizer_switch= @tmp_730133_os; drop table t1; +--echo # +--echo # Test of MRR handler counters +--echo # +flush status; +show status like 'Handler_mrr%'; +create table t0 (a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1 (a int, b int, filler char(200), key(a)); +insert into t1 +select A.a+10*B.a+100*C.a+1000*D.a, 123,'filler' from t0 A, t0 B, t0 C, t0 D; + +explain select sum(b) from t1 where a < 10; +--echo # This should show one MRR scan and no re-fills: +flush status; +select sum(b) from t1 where a < 10; +show status like 'handler_mrr%'; + +set @mrr_buffer_size_save= @@mrr_buffer_size; +--disable_warnings +set mrr_buffer_size=128; +--enable_warnings + +explain select sum(b) from t1 where a < 1600; +--echo # This should show one MRR scan and one extra rowid sort: +flush status; +select sum(b) from t1 where a < 1600; +show status like 'handler_mrr%'; +set @@mrr_buffer_size= @mrr_buffer_size_save; + +--echo #Now, let's check BKA: +set @join_cache_level_save= @@join_cache_level; +set @join_buffer_size_save= @@join_buffer_size; +set join_cache_level=6; + +explain select sum(t1.b) from t0,t1 where t0.a=t1.a; +flush status; +select sum(t1.b) from t0,t1 where t0.a=t1.a; +show status like 'handler_mrr%'; + +--disable_warnings +set join_buffer_size=10; +--enable_warnings +explain select sum(t1.b) from t0,t1 where t0.a=t1.a; +flush status; +select sum(t1.b) from t0,t1 where t0.a=t1.a; +--replace_result 1 1or2 2 1or2 +show status like 'handler_mrr%'; + +set join_cache_level= @join_cache_level_save; +set join_buffer_size= @join_buffer_size_save; + +drop table t0, t1; + ## This must be last line in the file: set optimizer_switch= @myisam_mrr_tmp; diff --git a/mysql-test/t/mysqlcheck.test b/mysql-test/t/mysqlcheck.test index ce6e3dc8bf7..fe4745624ce 100644 --- a/mysql-test/t/mysqlcheck.test +++ b/mysql-test/t/mysqlcheck.test @@ -139,6 +139,7 @@ SET NAMES DEFAULT; call mtr.add_suppression("@003f.frm' \\(errno: 22\\)"); --echo mysqlcheck --default-character-set="latin1" --databases test # Error returned depends on platform, replace it with "Table doesn't exist" +call mtr.add_suppression("Can't find file: '..test.@003f.frm'"); --replace_result "Can't find file: './test/@003f.frm' (errno: 22)" "Table doesn't exist" "Table 'test.?' doesn't exist" "Table doesn't exist" --exec $MYSQL_CHECK --default-character-set="latin1" --databases test --echo mysqlcheck --default-character-set="utf8" --databases test diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test index c7a958b293a..425bef6c679 100644 --- a/mysql-test/t/order_by.test +++ b/mysql-test/t/order_by.test @@ -1560,3 +1560,32 @@ DROP TABLE t1; select 1 order by max(1) + min(1); --echo End of 5.1 tests + +--echo # +--echo # Fix of LP BUG#793589 Wrong result with double ORDER BY +--echo # +CREATE TABLE t1 ( b int) ; +INSERT INTO t1 VALUES (8),(9); + +CREATE TABLE t2 ( a int, b int, PRIMARY KEY (a)) ; +INSERT INTO t2 VALUES (6,7),(7,7),(8,1),(9,7),(10,1),(11,5),(12,2),(13,0),(14,1),(15,8),(16,1),(17,1),(18,9),(19,1),(20,5); + +SELECT t2.b AS field1 FROM t1, t2 WHERE t1.b = t2.a GROUP BY field1 ORDER BY t1.b, field1; +SELECT t2.b, t1.b FROM t1, t2 WHERE t1.b = t2.a GROUP BY t2.b ORDER BY t1.b, t2.b; +SELECT t2.b,t1.b FROM t1, t2 WHERE t1.b = t2.a GROUP BY t2.b ORDER BY t1.b; +SELECT t2.b FROM t1, t2 WHERE t1.b = t2.a GROUP BY t2.b ORDER BY t1.b; + +--echo # field1 removed from ORDER BY +explain extended +SELECT t2.b AS field1 FROM t1, t2 WHERE t1.b = t2.a GROUP BY field1 ORDER BY t1.b, field1; +explain extended +SELECT t2.b, t1.b FROM t1, t2 WHERE t1.b = t2.a GROUP BY t2.b ORDER BY t1.b, t2.b; +explain extended +SELECT t2.b,t1.b FROM t1, t2 WHERE t1.b = t2.a GROUP BY t2.b ORDER BY t1.b; +explain extended +SELECT t2.b FROM t1, t2 WHERE t1.b = t2.a GROUP BY t2.b ORDER BY t1.b; + + +drop table t1,t2; + +--echo End of 5.2 tests diff --git a/mysql-test/t/partition_pruning.test b/mysql-test/t/partition_pruning.test index f1ec8964769..f12593d746f 100644 --- a/mysql-test/t/partition_pruning.test +++ b/mysql-test/t/partition_pruning.test @@ -1086,12 +1086,14 @@ show status like 'Handler_read_key'; flush status; delete from t2 where b > 5; show status like 'Handler_read_rnd_next'; +show status like 'Handler_read_rnd_deleted'; show status like 'Handler_read_key'; show status like 'Handler_read_prev'; show status like 'Handler_read_next'; flush status; delete from t2 where b < 5 or b > 3; show status like 'Handler_read_rnd_next'; +show status like 'Handler_read_rnd_deleted'; show status like 'Handler_read_key'; show status like 'Handler_read_prev'; show status like 'Handler_read_next'; diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test index 746d6bad896..e4e26da7031 100644 --- a/mysql-test/t/range.test +++ b/mysql-test/t/range.test @@ -1406,6 +1406,20 @@ DROP TABLE t1; --echo End of 5.1 tests +--echo # +--echo # LP Bug #533117: Wrong use_count in SEL_ARG trees +--echo # (Bug #58731) +--echo # + +create table t1 (a int, b int, c int, key idx (a,b,c)); +insert into t1 values (0,0,0), (2,2,0), (1,1,1), (2,2,1); + +explain +select * from t1 force index (idx) where a >=1 and c <= 1 and a=b and b > 1; +select * from t1 force index (idx) where a >=1 and c <= 1 and a=b and b > 1; + +drop table t1; + # # lp:750117 Bogus warning with aggregate and datetime column # @@ -1459,4 +1473,3 @@ SELECT * FROM t1 ignore index(d) WHERE d = 'q' OR d >= 'q' OR (d IN ( 'j' , 's' SELECT * FROM t1 force index(d) WHERE d = 'q' OR d >= 'q' OR (d IN ( 'j' , 's' , 'i' ) AND ( b = 102 )); DROP TABLE t1; - diff --git a/mysql-test/t/status.test b/mysql-test/t/status.test index 46e454363cc..8cc938c923e 100644 --- a/mysql-test/t/status.test +++ b/mysql-test/t/status.test @@ -368,6 +368,16 @@ show status like 'Handler%'; show status like '%tmp%'; drop table t1; +# +# Test of handler status counts +# +CREATE TABLE t1 (i int(11) DEFAULT NULL, KEY i (i) ) ENGINE=MyISAM; +insert into t1 values (1),(2),(3),(4),(5); +flush status; +select * from t1 where i=5 union select * from t1 where i=5; +show status like "handler%"; +drop table t1; + # End of 5.3 tests # Restore global concurrent_insert value. Keep in the end of the test file. diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 9d87cbc486c..6e2b2ef86ae 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -3521,6 +3521,8 @@ SET SESSION sql_mode='ONLY_FULL_GROUP_BY'; ## First a simpler query, illustrating the transformation ## '1 < some (...)' => '1 < max(...)' +EXPLAIN EXTENDED +SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t1); SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t1); ## The query which made the server crash. @@ -4207,8 +4209,6 @@ WHERE t1.a = d1.a; DROP TABLE t1; ---echo End of 5.1 tests. - # # Bug #47904 Incorrect results w/ table subquery, derived SQs, and LEFT JOIN on index # @@ -4568,11 +4568,141 @@ 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 # +--echo # Bug#12763207 - ASSERT IN SUBSELECT::SINGLE_VALUE_TRANSFORMER +--echo # + +CREATE TABLE t1(a1 int); +INSERT INTO t1 VALUES (1),(2); + +CREATE TABLE t2(a1 int); +INSERT INTO t2 VALUES (3); + +SELECT @@session.sql_mode INTO @old_sql_mode; +SET SESSION sql_mode='ONLY_FULL_GROUP_BY'; + +## All these are subject to the transformation +## '1 < some (...)' => '1 < max(...)' +SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2 FROM t2); +SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2.0 FROM t2); +SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 'a' FROM t2); +SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t2); + +SET SESSION sql_mode=@old_sql_mode; + +DROP TABLE t1, t2; + +create table t2(i int); +insert into t2 values(0); + +SELECT @@session.sql_mode INTO @old_sql_mode; +SET SESSION sql_mode='ONLY_FULL_GROUP_BY'; + +CREATE VIEW v1 AS +SELECT 'f' FROM t2 UNION SELECT 'x' FROM t2 +; + +CREATE TABLE t1 ( + pk int NOT NULL, + col_varchar_key varchar(1) DEFAULT NULL, + PRIMARY KEY (pk), + KEY col_varchar_key (col_varchar_key) +); + +SELECT t1.pk +FROM t1 +WHERE t1.col_varchar_key < ALL ( SELECT * FROM v1 ) +; + +SET SESSION sql_mode=@old_sql_mode; + +drop table t2, t1; +drop view v1; + +--echo # +--echo # BUG#50257: Missing info in REF column of the EXPLAIN +--echo # lines for subselects +--echo # + +CREATE TABLE t1 (a INT, b INT, INDEX (a)); +INSERT INTO t1 VALUES (3, 10), (2, 20), (7, 10), (5, 20); + +EXPLAIN SELECT * FROM (SELECT * FROM t1 WHERE a=7) t; +EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7); + +DROP TABLE t1; + +--echo # +--echo # BUG#12616253 - WRONG RESULT WITH EXISTS(SUBQUERY) (MISSING ROWS) +--echo # (duplicate of LP bug #888456) +--echo # + +CREATE TABLE t1 (f1 varchar(1)); +INSERT INTO t1 VALUES ('v'),('s'); + +CREATE TABLE t2 (f1_key varchar(1), KEY (f1_key)); +INSERT INTO t2 VALUES ('j'),('v'),('c'),('m'),('d'), +('d'),('y'),('t'),('d'),('s'); + +EXPLAIN +SELECT table1.f1, table2.f1_key FROM t1 AS table1, t2 AS table2 + WHERE EXISTS (SELECT DISTINCT f1_key FROM t2 + WHERE f1_key != table2.f1_key AND f1_key >= table1.f1); +SELECT table1.f1, table2.f1_key FROM t1 AS table1, t2 AS table2 + WHERE EXISTS (SELECT DISTINCT f1_key FROM t2 + WHERE f1_key != table2.f1_key AND f1_key >= table1.f1); + +DROP TABLE t1,t2; + +--echo # +--echo # LP bug 919427: EXPLAIN for a query over a single-row table +--echo # with IN subquery in WHERE condition +--echo # + +CREATE TABLE ot ( + col_int_nokey int(11), + col_varchar_nokey varchar(1) +) ; +INSERT INTO ot VALUES (1,'x'); + +CREATE TABLE it1( + col_int_key int(11), + col_varchar_key varchar(1), + KEY idx_cvk_cik (col_varchar_key,col_int_key) +); +INSERT INTO it1 VALUES (NULL,'x'), (NULL,'f'); + +CREATE TABLE it2 ( + col_int_key int(11), + col_varchar_key varchar(1), + col_varchar_key2 varchar(1), + KEY idx_cvk_cvk2_cik (col_varchar_key, col_varchar_key2, col_int_key), + KEY idx_cvk_cik (col_varchar_key, col_int_key) +); +INSERT INTO it2 VALUES (NULL,'x','x'), (NULL,'f','f'); + +EXPLAIN +SELECT col_int_nokey FROM ot + WHERE col_varchar_nokey IN + (SELECT col_varchar_key FROM it1 WHERE col_int_key IS NULL); +SELECT col_int_nokey FROM ot + WHERE col_varchar_nokey IN + (SELECT col_varchar_key FROM it1 WHERE col_int_key IS NULL); + +EXPLAIN +SELECT col_int_nokey FROM ot + WHERE (col_varchar_nokey, 'x') IN + (SELECT col_varchar_key, col_varchar_key2 FROM it2); +SELECT col_int_nokey FROM ot + WHERE (col_varchar_nokey, 'x') IN + (SELECT col_varchar_key, col_varchar_key2 FROM it2); + +DROP TABLE ot,it1,it2; + --echo End of 5.2 tests --echo # --echo # BUG#779885: Crash in eliminate_item_equal with materialization=on in ---echo # maria-5.3 --echo # CREATE TABLE t1 ( f1 int ); @@ -4625,32 +4755,6 @@ SET optimizer_switch=@save_optimizer_switch; drop table t1, t2, t3; ---echo End of 5.3 tests - ---echo # ---echo # Bug#12763207 - ASSERT IN SUBSELECT::SINGLE_VALUE_TRANSFORMER ---echo # - -CREATE TABLE t1(a1 int); -INSERT INTO t1 VALUES (1),(2); - -CREATE TABLE t2(a1 int); -INSERT INTO t2 VALUES (3); - -SELECT @@session.sql_mode INTO @old_sql_mode; -SET SESSION sql_mode='ONLY_FULL_GROUP_BY'; - -## All these are subject to the transformation -## '1 < some (...)' => '1 < max(...)' -SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2 FROM t2); -SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2.0 FROM t2); -SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 'a' FROM t2); -SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t2); - -SET SESSION sql_mode=@old_sql_mode; - -DROP TABLE t1, t2; - --echo # --echo # Bug#11764086: Null left operand to NOT IN in WHERE clause --echo # behaves differently than real NULL @@ -4701,25 +4805,6 @@ DROP TABLE parent, child; --echo # End of test for bug#11764086. --echo # ---echo # BUG#50257: Missing info in REF column of the EXPLAIN ---echo # lines for subselects ---echo # - -CREATE TABLE t1 (a INT, b INT, INDEX (a)); -INSERT INTO t1 VALUES (3, 10), (2, 20), (7, 10), (5, 20); - ---echo -set @tmp_optimizer_switch=@@optimizer_switch; -set optimizer_switch='derived_merge=off,derived_with_keys=off'; -EXPLAIN SELECT * FROM (SELECT * FROM t1 WHERE a=7) t; -set optimizer_switch=@tmp_optimizer_switch; ---echo -EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7); - ---echo -DROP TABLE t1; - ---echo # --echo # Bug 11765699 - 58690: !TABLE || (!TABLE->READ_SET || --echo # BITMAP_IS_SET(TABLE->READ_SET, FIELD_INDEX --echo # @@ -4749,30 +4834,6 @@ SELECT 1 FROM t1 WHERE a = DROP TABLE t1, t2; --echo # ---echo # BUG#12616253 - WRONG RESULT WITH EXISTS(SUBQUERY) (MISSING ROWS) ---echo # - -CREATE TABLE t1 (f1 varchar(1)); -INSERT INTO t1 VALUES ('v'),('s'); - -CREATE TABLE t2 (f1_key varchar(1), KEY (f1_key)); -INSERT INTO t2 VALUES ('j'),('v'),('c'),('m'),('d'), -('d'),('y'),('t'),('d'),('s'); - -let $query=SELECT table1.f1, table2.f1_key -FROM t1 AS table1, t2 AS table2 -WHERE EXISTS -( -SELECT DISTINCT f1_key -FROM t2 -WHERE f1_key != table2.f1_key AND f1_key >= table1.f1 ); - -eval $query; -eval explain $query; - -DROP TABLE t1,t2; - ---echo # --echo # LP bug #826279: assertion failure with GROUP BY a result of subquery --echo # @@ -4990,5 +5051,45 @@ SELECT * FROM t1 WHERE a IN (SELECT a AS field1 FROM t1 GROUP BY field1); set @@optimizer_switch=@old_optimizer_switch; drop table t1; +--echo # +--echo # LP BUG#908269 incorrect condition in case of subqueries depending +--echo # on constant tables +--echo # +CREATE TABLE t1 ( a INT ); +INSERT INTO t1 VALUES (1),(5); + +# t2 must be MyISAM or Aria and contain 1 row +CREATE TABLE t2 ( b INT ) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1); + +CREATE TABLE t3 ( c INT ); +INSERT INTO t3 VALUES (4),(5); + +SET optimizer_switch='subquery_cache=off'; + +SELECT ( SELECT b FROM t2 WHERE b = a OR EXISTS ( SELECT c FROM t3 WHERE c = b ) ) FROM t1; + +# This query just for example, it should return the same as above (1 and NULL) +SELECT ( SELECT b FROM t2 WHERE b = a OR b * 0) FROM t1; + +# example with "random" +SELECT ( SELECT b FROM t2 WHERE b = a OR rand() * 0) FROM t1; + +drop table t1,t2,t3; +set optimizer_switch=@subselect_tmp; + +--echo # +--echo # LP BUG#905353 Wrong non-empty result with a constant table, +--echo # aggregate function in subquery, MyISAM or Aria +--echo # + +CREATE TABLE t1 ( a INT ) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1); + +SELECT a FROM t1 WHERE ( SELECT MIN(a) = 100 ); + +drop table t1; + + --echo # return optimizer switch changed in the beginning of this test set optimizer_switch=@subselect_tmp; diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test index b2e15c457d7..371ee016fb8 100644 --- a/mysql-test/t/subselect4.test +++ b/mysql-test/t/subselect4.test @@ -1485,39 +1485,6 @@ WHERE t2.f2 = (SELECT f2 FROM t3 drop table t1,t2,t3,t4; --echo # ---echo # LP BUG#806943 Second crash with select_describe with nested subqueries in maria-5.3 ---echo # - -CREATE TABLE t1 ( f4 int) ; -INSERT INTO t1 VALUES (0),(0); - -CREATE TABLE t2 ( f2 int) ; - -CREATE TABLE t3 ( f1 int NOT NULL ); - -CREATE TABLE t4 ( f2 int, f3 int) ; -INSERT INTO t4 VALUES (8,0),(3,0); - -EXPLAIN SELECT * -FROM t2, t3 -WHERE t3.f1 = ( - SELECT SUM( f2 ) - FROM t4 - WHERE EXISTS ( - SELECT DISTINCT f4 - FROM t1)); -SELECT * -FROM t2, t3 -WHERE t3.f1 = ( - SELECT SUM( f2 ) - FROM t4 - WHERE EXISTS ( - SELECT DISTINCT f4 - FROM t1)); - -drop table t1, t2, t3, t4; - ---echo # --echo # LP BUG#611690 Crash in select_describe() with nested subqueries --echo # @@ -1779,6 +1746,22 @@ WHERE t2.b IN (SELECT t3.b FROM t3 WHERE t3.a < ANY (SELECT t4.a FROM t4) and t3 drop table t2, t3, t4; +--echo # +--echo # BUG#934597: Assertion `! is_set()' failed in Diagnostics_area::set_ok_status(THD... +--echo # +CREATE TABLE t1 ( a VARCHAR(1) ); +INSERT INTO t1 VALUES ('u'),('k'); +--error ER_SUBQUERY_NO_1_ROW +CREATE TABLE t2 AS + SELECT a AS field1 FROM t1 + WHERE ( SELECT alias1.a + FROM t1 AS alias1 + ) IS NOT NULL; +--error ER_BAD_TABLE_ERROR +DROP TABLE t2; +DROP TABLE t1; + + set optimizer_switch=@subselect4_tmp; SET optimizer_switch= @@global.optimizer_switch; diff --git a/mysql-test/t/subselect_sj.test b/mysql-test/t/subselect_sj.test index aee61e9bca3..6b8a757b9e8 100644 --- a/mysql-test/t/subselect_sj.test +++ b/mysql-test/t/subselect_sj.test @@ -1136,6 +1136,262 @@ WHERE (int_nokey, pk) DROP TABLE t1, t2, t3; +--echo # +--echo # BUG#53060: LooseScan semijoin strategy does not return all rows +--echo # + +set @save_optimizer_switch=@@optimizer_switch; +set optimizer_switch='semijoin=on,materialization=off'; +set optimizer_switch='firstmatch=off,loosescan=on'; + +CREATE TABLE t1 (i INTEGER); +INSERT INTO t1 VALUES (1), (2), (3), (4), (5); +CREATE TABLE t2 (i INTEGER, j INTEGER, KEY k(i, j)); +INSERT INTO t2 VALUES (1, 0), (1, 1), (2, 0), (2, 1); + +EXPLAIN +SELECT * FROM t1 WHERE (i) IN (SELECT i FROM t2 where j > 0); +SELECT * FROM t1 WHERE (i) IN (SELECT i FROM t2 where j > 0); + +DROP TABLE t1, t2; + +set optimizer_switch=@save_optimizer_switch; + +--echo # +--echo # BUG#49453: re-execution of prepared statement with view +--echo # and semijoin crashes +--echo # + +CREATE TABLE t1 (city VARCHAR(50), country_id INT); +CREATE TABLE t2 (country_id INT, country VARCHAR(50)); + +INSERT INTO t1 VALUES + ('Batna',2),('Bchar',2),('Skikda',2),('Tafuna',3),('Algeria',2) ; +INSERT INTO t2 VALUES (2,'Algeria'),(2,'AlgeriaDup'),(3,'XAmerican Samoa'); + +CREATE VIEW v1 AS + SELECT country_id as vf_country_id + FROM t2 + WHERE LEFT(country,1) = "A"; + +PREPARE stmt FROM " +SELECT city, country_id +FROM t1 +WHERE country_id IN (SELECT vf_country_id FROM v1); +"; + +--echo +EXECUTE stmt; +EXECUTE stmt; + +DROP TABLE t1,t2; +DROP VIEW v1; + +--echo # +--echo # Bug#54437 Extra rows with LEFT JOIN + semijoin +--echo # + +create table t1 (a int); +create table t2 (a int); +create table t3 (a int); +insert into t1 values(1),(1); +insert into t2 values(1),(1),(1),(1); +insert into t3 values(2),(2); + +set @save_optimizer_switch=@@optimizer_switch; + +set optimizer_switch='materialization=off'; + +set optimizer_switch='semijoin=off'; +explain +select * from t1 where t1.a in (select t2.a from t2 left join t3 on t2.a=t3.a); +select * from t1 where t1.a in (select t2.a from t2 left join t3 on t2.a=t3.a); + +set optimizer_switch='semijoin=on'; +explain +select * from t1 where t1.a in (select t2.a from t2 left join t3 on t2.a=t3.a); +select * from t1 where t1.a in (select t2.a from t2 left join t3 on t2.a=t3.a); + +set optimizer_switch=@save_optimizer_switch; + +drop table t1,t2,t3; + +--echo # +--echo # Bug#55955: crash in MEMORY engine with IN(LEFT JOIN (JOIN)) +--echo # + +CREATE TABLE t1 (a INT); +CREATE TABLE t2 (a INT); +CREATE TABLE t3 (a INT); +INSERT INTO t1 VALUES(1),(1); +INSERT INTO t2 VALUES(1),(1); +INSERT INTO t3 VALUES(2),(2); + +set @save_optimizer_switch=@@optimizer_switch; + +set optimizer_switch='semijoin=off,materialization=off'; +EXPLAIN +SELECT * FROM t1 +WHERE t1.a IN (SELECT t2.a + FROM t2 LEFT JOIN (t2 AS t2inner, t3) ON t2.a=t3.a); +SELECT * FROM t1 +WHERE t1.a IN (SELECT t2.a + FROM t2 LEFT JOIN (t2 AS t2inner, t3) ON t2.a=t3.a); + +set optimizer_switch='semijoin=off,materialization=on'; +EXPLAIN +SELECT * FROM t1 +WHERE t1.a IN (SELECT t2.a + FROM t2 LEFT JOIN (t2 AS t2inner, t3) ON t2.a=t3.a); +SELECT * FROM t1 +WHERE t1.a IN (SELECT t2.a + FROM t2 LEFT JOIN (t2 AS t2inner, t3) ON t2.a=t3.a); + +set optimizer_switch='semijoin=on,materialization=off'; +EXPLAIN +SELECT * FROM t1 +WHERE t1.a IN (SELECT t2.a + FROM t2 LEFT JOIN (t2 AS t2inner, t3) ON t2.a=t3.a); +SELECT * FROM t1 +WHERE t1.a IN (SELECT t2.a + FROM t2 LEFT JOIN (t2 AS t2inner, t3) ON t2.a=t3.a); + +set optimizer_switch=@save_optimizer_switch; + +DROP TABLE t1,t2,t3; + +--echo # +--echo # BUG#52329 - Wrong result: subquery materialization, IN, +--echo # non-null field followed by nullable +--echo # + +CREATE TABLE t1 (a1 CHAR(8) NOT NULL, a2 char(8) NOT NULL); + +CREATE TABLE t2a (b1 char(8), b2 char(8)); +CREATE TABLE t2b (b1 CHAR(8), b2 char(8) NOT NULL); +CREATE TABLE t2c (b1 CHAR(8) NOT NULL, b2 char(8)); + +INSERT INTO t1 VALUES ('1 - 12', '2 - 22'); + +INSERT INTO t2a VALUES ('1 - 11', '2 - 21'), + ('1 - 11', '2 - 21'), + ('1 - 12', '2 - 22'), + ('1 - 12', '2 - 22'), + ('1 - 13', '2 - 23'); + +INSERT INTO t2b SELECT * FROM t2a; +INSERT INTO t2c SELECT * FROM t2a; + +set @save_optimizer_switch=@@optimizer_switch; + +set optimizer_switch='semijoin=off,materialization=on'; + +SELECT * FROM t1 +WHERE (a1, a2) IN ( + SELECT b1, b2 FROM t2c WHERE b1 > '0' GROUP BY b1, b2); + +SELECT * FROM t1 +WHERE (a1, a2) IN ( + SELECT b1, b2 FROM t2a WHERE b1 > '0'); + + +SELECT * FROM t1 +WHERE (a1, a2) IN ( + SELECT b1, b2 FROM t2b WHERE b1 > '0'); + + +SELECT * FROM t1 +WHERE (a1, a2) IN ( + SELECT b1, b2 FROM t2c WHERE b1 > '0'); + +set optimizer_switch=@save_optimizer_switch; + +DROP TABLE t1,t2a,t2b,t2c; + +--echo # +--echo # Bug#57623: subquery within before insert trigger causes crash (sj=on) +--echo # + +CREATE TABLE ot1(a INT); +CREATE TABLE ot2(a INT); +CREATE TABLE ot3(a INT); +CREATE TABLE it1(a INT); + +INSERT INTO ot1 VALUES(0),(1),(2),(3),(4),(5),(6),(7); +INSERT INTO ot2 VALUES(0),(2),(4),(6); +INSERT INTO ot3 VALUES(0),(3),(6); +INSERT INTO it1 VALUES(0),(1),(2),(3),(4),(5),(6),(7); + +let $query= +SELECT * +FROM ot1 + LEFT JOIN + (ot2 JOIN ot3 on ot2.a=ot3.a) + ON ot1.a=ot2.a AND ot1.a IN (SELECT a from it1); + +set @save_optimizer_switch=@@optimizer_switch; + +set optimizer_switch='semijoin=on'; + +set optimizer_switch='materialization=off'; +eval explain $query; +eval $query; +eval prepare s from '$query'; +execute s; +execute s; +deallocate prepare s; + +set optimizer_switch='materialization=on'; +eval explain $query; +eval $query; +eval prepare s from '$query'; +execute s; +execute s; +deallocate prepare s; + +set optimizer_switch=@save_optimizer_switch; + +DROP TABLE ot1, ot2, ot3, it1; + +--echo # +--echo # Bug#59919/11766739: Crash in tmp_table_param::init() with semijoin=on +--echo # + +CREATE TABLE t1 (f1 INTEGER) ENGINE=MyISAM; +CREATE TABLE t2 (f1 INTEGER, f2 INTEGER) ENGINE=MyISAM; +CREATE TABLE t3 (f1 INTEGER, f2 INTEGER) ENGINE=MyISAM; + +INSERT INTO t1 VALUES (1); +INSERT INTO t2 VALUES (1,1), (2,1); +INSERT INTO t3 VALUES + (1,1), (2,1), (5,4), (7,3), (8,2), (8,1), (7,3), + (9,5), (4,3), (7,2), (7,7), (3,1), (5,8), (9,7); + +set @save_optimizer_switch=@@optimizer_switch; + +set optimizer_switch='semijoin=off,materialization=on'; +EXPLAIN +SELECT * FROM t3 +WHERE f2 IN (SELECT t1.f1 + FROM t1 LEFT OUTER JOIN (t2 AS b1 JOIN t2 AS b2 ON TRUE) ON TRUE); +SELECT * FROM t3 +WHERE f2 IN (SELECT t1.f1 + FROM t1 LEFT OUTER JOIN (t2 AS b1 JOIN t2 AS b2 ON TRUE) ON TRUE); + +set optimizer_switch='semijoin=on,materialization=on'; +EXPLAIN +SELECT * FROM t3 +WHERE f2 IN (SELECT t1.f1 + FROM t1 LEFT OUTER JOIN (t2 AS b1 JOIN t2 AS b2 ON TRUE) ON TRUE); +SELECT * FROM t3 +WHERE f2 IN (SELECT t1.f1 + FROM t1 LEFT OUTER JOIN (t2 AS b1 JOIN t2 AS b2 ON TRUE) ON TRUE); + +set optimizer_switch=@save_optimizer_switch; + +DROP TABLE t1, t2, t3 ; + +--echo # --echo # --echo # BUG#784723: Wrong result with semijoin + nested subqueries in maria-5.3 --echo # @@ -1997,6 +2253,83 @@ set optimizer_switch= @os_091399; DROP TABLE t1,t2; +--echo # +--echo # BUG#912510: Crash in do_copy_not_null with semijoin=ON, firstmatch=ON, aggregate ... +--echo # +CREATE TABLE t1 ( a VARCHAR(1) NOT NULL ); +INSERT INTO t1 VALUES ('k'),('l'); + +CREATE TABLE t2 ( b VARCHAR(1) NOT NULL, KEY(b) ); +INSERT INTO t2 VALUES ('k'),('l'); + +CREATE TABLE t3 ( c VARCHAR(1) NOT NULL, KEY(c) ); +INSERT INTO t3 VALUES ('m'),('n'); + +SELECT a, COUNT(*) FROM t1 + WHERE a IN ( + SELECT b FROM t2 force index(b), t3 force index(c) + WHERE c = b AND b = a + ); + +DROP TABLE t1, t2, t3; + +--echo # +--echo # BUG#920255: Wrong result (extra rows) with loosescan and IN subquery +--echo # +CREATE TABLE t1 ( a INT PRIMARY KEY, b INT, KEY(b) ); +INSERT INTO t1 VALUES + (1,2),(2,1),(3,3),(4,2),(5,5), + (6,3),(7,1),(8,4),(9,3),(10,2); + +CREATE TABLE t2 ( c INT, d INT, UNIQUE KEY(c) ); +INSERT INTO t2 VALUES + (1,2),(2,1),(3,3),(4,2),(5,5),(6,3),(7,1); + +SELECT a, b, d FROM t1, t2 +WHERE ( b, d ) IN + ( SELECT b, d FROM t1, t2 WHERE b = c ); + +DROP TABLE t1, t2; + +--echo # +--echo # BUG#920713: Wrong result (missing rows) with firstmatch+BNL, IN subquery, ... +--echo # +# t1 should be MyISAM or InnoDB +CREATE TABLE t1 ( a VARCHAR(1) ) ENGINE=MyISAM; +INSERT INTO t1 VALUES ('e'),('w'),('a'),('h'),('x'),('k'),('g'); +CREATE TABLE t2 ( b INT, c VARCHAR(1) ); +INSERT INTO t2 VALUES (0,'j'),(8,'v'); + +#SET debug_optimizer_prefer_join_prefix= 'alias2,alias4,alias1,alias3'; + +SELECT * FROM t1 alias1, t2 alias2 +WHERE alias2.c IN ( + SELECT alias4.c FROM t1 alias3, t2 alias4 +); + +DROP TABLE t1, t2; + +--echo # +--echo # BUG#923246: Loosescan reports different result than other semijoin methods +--echo # +set @tmp_923246= @@optimizer_switch; +set optimizer_switch='mrr=on,materialization=off'; + +create table t0 (a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + +create table t1 (kp1 int, kp2 int, c int, filler char(100), key(kp1, kp2)); +insert into t1 select A.a+10*(B.a+10*C.a), 0, 0, 'filler' from t0 A, t0 B, t0 C; +insert into t1 select * from t1 where kp1 < 20; + +create table t3 (a int); +insert into t3 select A.a + 10*B.a from t0 A, t0 B; + +select * from t3 where a in (select kp1 from t1 where kp1<20); +explain select * from t3 where a in (select kp1 from t1 where kp1<20); + +drop table t0,t1,t3; +set optimizer_switch= @tmp_923246; # 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 9a664ee9881..30dc20087d1 100644 --- a/mysql-test/t/subselect_sj2.test +++ b/mysql-test/t/subselect_sj2.test @@ -1076,5 +1076,19 @@ SELECT * FROM t3 LEFT JOIN (v1,t2) ON t3.a = t2.a DROP VIEW v1; DROP TABLE t1,t2,t3,t4; +--echo # +--echo # BUG#912538: Wrong result (missing rows) with semijoin=on, firstmatch=on, ... +--echo # +CREATE TABLE t1 ( a INT NOT NULL, UNIQUE KEY(a) ); +INSERT INTO t1 VALUES (1),(2),(3),(4); + +# t2 needs to be InnoDB +CREATE TABLE t2 ( b INT, c INT ) ENGINE=InnoDB; +INSERT INTO t2 VALUES (1,1); + +SELECT * FROM t1, t2 WHERE c IN (SELECT c FROM t1, t2 WHERE a = b); + +DROP TABLE t1,t2; + --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 4eeaa465b11..e86ed017951 100644 --- a/mysql-test/t/subselect_sj_jcl6.test +++ b/mysql-test/t/subselect_sj_jcl6.test @@ -88,6 +88,124 @@ set join_cache_level=default; DROP TABLE t1,t2,t3; +--echo # +--echo # BUG#912513: Wrong result (missing rows) with join_cache_hashed+materialization+semijoin=on +--echo # +set @os_912513= @@optimizer_switch; +set @jcl_912513= @@join_cache_level; +SET optimizer_switch = 'semijoin=on,materialization=on,join_cache_hashed=on'; +SET join_cache_level = 3; + +CREATE TABLE t1 ( a INT, b INT, KEY(a) ); +INSERT INTO t1 VALUES + (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7); + +CREATE TABLE t2 ( c INT ); +INSERT INTO t2 VALUES (1),(2),(3),(4),(5),(6),(7); + +SELECT alias1.* FROM + t1 AS alias1 INNER JOIN t1 AS alias2 + ON alias2.a = alias1.b +WHERE alias1.b IN ( + SELECT a FROM t1, t2 + ); + +DROP table t1, t2; +set @@optimizer_switch= @os_912513; +set @@join_cache_level= @jcl_912513; + +--echo # End + +--echo # +--echo # BUG#934342: outer join + semijoin materialization +--echo # + join_cache_level > 2 +--echo # + +CREATE TABLE t1 (a varchar(1), b varchar(1), INDEX idx_a(a) ); +INSERT INTO t1 VALUES ('v','v'), ('w','w'), ('t','t'); + +CREATE TABLE t2 (c varchar(1), INDEX idx_c(c) ); +INSERT INTO t2 VALUES ('v'), ('v'), ('s'), ('j'); + +CREATE TABLE t3 (c varchar(1), d varchar(1), INDEX idx_c(c) ); +INSERT INTO t3 VALUES ('v','v'), ('v','v'), ('s','s'), ('j','j'); +INSERT INTO t3 VALUES ('m','m'), ('d','d'), ('k','k'), ('m','m'); + +set @tmp_otimizer_switch= @@optimizer_switch; +set @tmp_join_cache_level=@@join_cache_level; +set optimizer_switch = 'materialization=on,semijoin=on,join_cache_hashed=on'; + +set join_cache_level=0; + +EXPLAIN +SELECT * FROM t1 LEFT JOIN t2 ON (c = b) + WHERE (a, b) IN (SELECT a, b FROM t1 t); +SELECT * FROM t1 LEFT JOIN t2 ON (c = b) + WHERE (a, b) IN (SELECT a, b FROM t1 t); + +EXPLAIN +SELECT * FROM t1 LEFT JOIN t3 ON (c = b) + WHERE (a, b) IN (SELECT a, b FROM t1 t); +SELECT * FROM t1 LEFT JOIN t3 ON (c = b) + WHERE (a, b) IN (SELECT a, b FROM t1 t); + +set join_cache_level=6; + +EXPLAIN +SELECT * FROM t1 LEFT JOIN t2 ON (c = b) + WHERE (a, b) IN (SELECT a, b FROM t1 t); +SELECT * FROM t1 LEFT JOIN t2 ON (c = b) + WHERE (a, b) IN (SELECT a, b FROM t1 t); + +EXPLAIN +SELECT * FROM t1 LEFT JOIN t3 ON (c = b) + WHERE (a, b) IN (SELECT a, b FROM t1 t); +SELECT * FROM t1 LEFT JOIN t3 ON (c = b) + WHERE (a, b) IN (SELECT a, b FROM t1 t); + +set optimizer_switch=@tmp_optimizer_switch; +set join_cache_level=@tmp_join_cache_level; + +DROP TABLE t1,t2,t3; + +--echo # End + +--echo # +--echo # BUG#934348: GROUP BY with HAVING + semijoin materialization +--echo # + join_cache_level > 2 +--echo # + +CREATE TABLE t1 (a varchar(1), INDEX idx_a(a)); +INSERT INTO t1 VALUES ('c'), ('v'), ('c'); + +CREATE TABLE t2 (b varchar(1)); +INSERT INTO t2 VALUES ('v'), ('c'); + +set @tmp_otimizer_switch= @@optimizer_switch; +set @tmp_join_cache_level=@@join_cache_level; +set optimizer_switch = 'materialization=on,semijoin=on,join_cache_hashed=on'; + +set join_cache_level=0; + +EXPLAIN +SELECT a FROM t1 t WHERE a IN (SELECT b FROM t1, t2 WHERE b = a) + GROUP BY a HAVING a != 'z'; +SELECT a FROM t1 t WHERE a IN (SELECT b FROM t1, t2 WHERE b = a) + GROUP BY a HAVING a != 'z'; + +set join_cache_level=6; + +EXPLAIN +SELECT a FROM t1 t WHERE a IN (SELECT b FROM t1, t2 WHERE b = a) + GROUP BY a HAVING a != 'z'; +SELECT a FROM t1 t WHERE a IN (SELECT b FROM t1, t2 WHERE b = a) + GROUP BY a HAVING a != 'z'; + +set optimizer_switch=@tmp_optimizer_switch; +set join_cache_level=@tmp_join_cache_level; + +DROP TABLE t1,t2; + --echo # End set join_cache_level=default; diff --git a/mysql-test/t/subselect_sj_mat.test b/mysql-test/t/subselect_sj_mat.test index 2a5b0f56877..ba260b7d8c3 100644 --- a/mysql-test/t/subselect_sj_mat.test +++ b/mysql-test/t/subselect_sj_mat.test @@ -1510,6 +1510,138 @@ SELECT * FROM t1 LEFT JOIN t2 ON ( a = b ) DROP TABLE t1,t2; +--echo # +--echo # BUG#922254: Assertion `0' failed at item_cmpfunc.cc:5899: Item* Item_equal::get_first(JOIN_TAB*, Item*) +--echo # +CREATE TABLE t1 ( a VARCHAR(3) ); +CREATE TABLE t2 ( b VARCHAR(3), c VARCHAR(8), KEY(c) ); +INSERT INTO t2 VALUES ('USA','Abilene'),('USA','Akron'); + +EXPLAIN +SELECT * FROM + ( SELECT * FROM t1 ) AS alias1, + t2 AS alias2 +WHERE b = a AND a IN ( + SELECT alias3.c + FROM t2 AS alias3, t2 AS alias4 + WHERE alias4.c = alias3.b +); + +DROP TABLE t1,t2; + +--echo # +--echo # BUG#928048: Query containing IN subquery with OR in the where clause returns a wrong result +--echo # +create table t1 (a int, b int); +insert into t1 values (7,5), (3,3), (5,4), (9,3); + +create table t2 (a int, b int, index i_a(a)); + +insert into t2 values + (4,2), (7,9), (7,4), (3,1), (5,3), (3,1), (9,4), (8,1); + +explain select * from t1 where t1.a in (select a from t2 where t2.a=7 or t2.b<=1); +select * from t1 where t1.a in (select a from t2 where t2.a=7 or t2.b<=1); + +drop table t1,t2; + +--echo # +--echo # BUG#933407: Valgrind warnings in mark_as_null_row with materialization+semijoin, STRAIGHT_JOIN, impossible WHERE +--echo # +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (0),(8); + +SELECT STRAIGHT_JOIN MIN(a) FROM t1 +WHERE a IN ( + SELECT a FROM t1 + WHERE 'condition'='impossible' + ); + +DROP TABLE t1; + +--echo # +--echo # BUG#938131: Subquery materialization is not used in CREATE TABLE SELECT +--echo # +CREATE TABLE t1(a int); +INSERT INTO t1 values(1),(2); +CREATE TABLE t2(a int); +INSERT INTO t2 values(1),(2); +--echo # Should use Materialization: +EXPLAIN SELECT * FROM t1 WHERE a IN (SELECT * FROM t2 GROUP BY a HAVING a > 1); +flush status; +CREATE TABLE t3 SELECT * FROM t1 WHERE a IN (SELECT * FROM t2 GROUP BY a HAVING a > 1); +SHOW STATUS LIKE 'Created_tmp_tables'; +DROP TABLE t1,t2,t3; + +--echo # +--echo # BUG#939009: Crash with aggregate function in IN subquery +--echo # + +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='materialization=on,semijoin=on'; + +CREATE TABLE t1 (a int, b int); +INSERT INTO t1 VALUES (7,1), (4,2), (7,7); + +CREATE TABLE t2 ( c INT ); +INSERT INTO t2 VALUES (4), (7), (6); + +EXPLAIN EXTENDED +SELECT * FROM t1 + WHERE a IN (SELECT MAX(c) FROM t2) AND b=7 AND (a IS NULL OR a=b); +SELECT * FROM t1 + WHERE a IN (SELECT MAX(c) FROM t2) AND b=7 AND (a IS NULL OR a=b); + +EXPLAIN +SELECT * FROM t1 + WHERE a IN (SELECT MAX(c) FROM t2 WHERE c < 4) AND b=7 AND (a IS NULL OR a=b); +SELECT * FROM t1 + WHERE a IN (SELECT MAX(c) FROM t2 WHERE c < 4) AND b=7 AND (a IS NULL OR a=b); + +SET optimizer_switch=@save_optimizer_switch; + +DROP TABLE t1,t2; + +--echo # +--echo # BUG#946055: Crash with semijoin IN subquery when hash join is used +--echo # + +CREATE TABLE t1 (a int); +INSERT INTO t1 VALUES (7); + +CREATE TABLE t2 (b int, c int, d varchar(1), e varchar(1), KEY (c), KEY (d, c)); + +INSERT INTO t2 VALUES + (4,2,'v','v'), (6,1,'v','v'), (0,5,'x','x'), (7,1,'x','x'), + (7,3,'i','i'), (7,1,'e','e'), (1,4,'p','p'), (1,2,'j','j'); + +SET @save_optimizer_switch=@@optimizer_switch; +SET @save_join_cache_level=@@join_cache_level; + +SET join_cache_level=2; +EXPLAIN +SELECT a, c FROM t1, t2 + WHERE (a, c) IN (SELECT s1.b, s1.c FROM t2 AS s1, t2 AS s2 + WHERE s2.d = s1.e AND s1.e = (SELECT MAX(e) FROM t2)); +SELECT a, c FROM t1, t2 + WHERE (a, c) IN (SELECT s1.b, s1.c FROM t2 AS s1, t2 AS s2 + WHERE s2.d = s1.e AND s1.e = (SELECT MAX(e) FROM t2)); + +SET optimizer_switch='join_cache_hashed=on'; +SET join_cache_level=4; +EXPLAIN +SELECT a, c FROM t1, t2 + WHERE (a, c) IN (SELECT s1.b, s1.c FROM t2 AS s1, t2 AS s2 + WHERE s2.d = s1.e AND s1.e = (SELECT MAX(e) FROM t2)); +SELECT a, c FROM t1, t2 + WHERE (a, c) IN (SELECT s1.b, s1.c FROM t2 AS s1, t2 AS s2 + WHERE s2.d = s1.e AND s1.e = (SELECT MAX(e) FROM t2)); + +SET optimizer_switch=@save_optimizer_switch; +SET join_cache_level=@save_join_cache_level; + +DROP TABLE t1,t2; + --echo # This must be at the end: set optimizer_switch=@subselect_sj_mat_tmp; set join_cache_level=@save_join_cache_level; diff --git a/mysql-test/t/sum_distinct.test b/mysql-test/t/sum_distinct.test index c58155a8e25..633a72fddc8 100644 --- a/mysql-test/t/sum_distinct.test +++ b/mysql-test/t/sum_distinct.test @@ -93,3 +93,15 @@ SELECT SUM(DISTINCT id) FROM t1; SELECT SUM(DISTINCT id % 11) FROM t1; DROP TABLE t1; + +--echo # +--echo # Bug #777654: empty subselect in FROM clause returning +--echo # SUM(DISTINCT) over non-nullable field +--echo # + +CREATE TABLE t1 (a int NOT NULL) ; + +SELECT SUM(DISTINCT a) FROM t1; +SELECT * FROM (SELECT SUM(DISTINCT a) FROM t1) AS t; + +DROP TABLE t1; diff --git a/mysql-test/t/type_timestamp.test b/mysql-test/t/type_timestamp.test index 533a45ba7fe..674f22cd1c2 100644 --- a/mysql-test/t/type_timestamp.test +++ b/mysql-test/t/type_timestamp.test @@ -410,3 +410,10 @@ SHOW STATUS LIKE 'Handler_read_next'; DROP TABLE t1, t2; --echo End of 5.1 tests + +--echo # +--echo # lp:923429 Crash in decimal_cmp on using UNIX_TIMESTAMP with a wrongly formatted timestamp +--echo # +SELECT UNIX_TIMESTAMP('abc') > 0; +SELECT UNIX_TIMESTAMP('abc'); + diff --git a/mysql-test/t/type_year.test b/mysql-test/t/type_year.test index de38306e88f..53cfd3a7c30 100644 --- a/mysql-test/t/type_year.test +++ b/mysql-test/t/type_year.test @@ -163,3 +163,20 @@ DROP TABLE t1; --echo # --echo End of 5.1 tests +# +# fun with convert_const_to_int +# in some cases 00 is equal to 2000, in others it is not. +# +create function y2k() returns int deterministic return 2000; +create table t1 (a year(2), b int); +insert t1 values (0,2000); +select a from t1 where a=2000; # constant. +select a from t1 where a=1000+1000; # still a constant. +select a from t1 where a=(select 2000); # even this is a constant +select a from t1 where a=(select 2000 from dual where 1); # constant, but "expensive" +select a from t1 where a=y2k(); # constant, but "expensive" +select a from t1 where a=b; # not a constant +drop table t1; +drop function y2k; + + diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index 821bbe055e6..2a9bfd89f3b 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -3980,6 +3980,59 @@ drop table t1,t2; --echo # ----------------------------------------------------------------- --echo # -- End of 5.1 tests. --echo # ----------------------------------------------------------------- +--echo # +--echo # Bug #794005: crash in st_table::mark_virtual_columns_for_write +--echo # + +CREATE TABLE t1 (a int); +insert into t1 values (1); +CREATE TABLE t2 (a int); +insert into t2 values (1); + +CREATE VIEW v2 AS SELECT * FROM t2; +CREATE VIEW v1 AS SELECT * FROM v2; +CREATE VIEW v3 AS SELECT t2.a,v1.a as b FROM t2,v1 where t2.a=v1.a; +CREATE OR REPLACE ALGORITHM = TEMPTABLE VIEW v2 AS SELECT * FROM t1; + +--error ER_NON_UPDATABLE_TABLE +UPDATE v1 SET a = 10; +--error ER_NON_INSERTABLE_TABLE +REPLACE v1 SET a = 10; +--error ER_NON_INSERTABLE_TABLE +INSERT into v1 values (20); +--error ER_NON_UPDATABLE_TABLE +DELETE from v1; +--error ER_NON_UPDATABLE_TABLE +UPDATE v3 SET b= 10; +--error ER_NON_INSERTABLE_TABLE +REPLACE v3 SET b= 10; +--error ER_NON_INSERTABLE_TABLE +INSERT into v3(b) values (20); +--error ER_VIEW_DELETE_MERGE_VIEW +DELETE from v3 where b=20; +--error ER_VIEW_DELETE_MERGE_VIEW +DELETE from v3 where a=20; +--error ER_NON_UPDATABLE_TABLE +DELETE v1 from v1,t1 where v1.a=t1.a; +UPDATE v3 SET a = 10; +REPLACE v3 SET a = 11; +INSERT INTO v3(a) values (20); + +select * from t1; +select * from t2; + +CREATE OR REPLACE ALGORITHM = MERGE VIEW v2 AS SELECT * FROM t2; +DELETE from v1 where a=11; +DELETE v1 from v1,t1 where v1.a=t1.a; +select * from t1; +select * from t2; + +DROP VIEW v1,v2,v3; +DROP TABLE t1,t2; + +--echo # ----------------------------------------------------------------- +--echo # -- End of 5.2 tests. +--echo # ----------------------------------------------------------------- --echo # --echo # Bug #59696 Optimizer does not use equalities for conditions over view @@ -4312,4 +4365,8 @@ SELECT * FROM t1 RIGHT JOIN v2 ON ( v2.a = t1.a ) WHERE v2.b IN ( SELECT b FROM DROP VIEW v2; DROP TABLE t1, t2, t3; +--echo # ----------------------------------------------------------------- +--echo # -- End of 5.3 tests. +--echo # ----------------------------------------------------------------- + SET optimizer_switch=@save_optimizer_switch; |