diff options
Diffstat (limited to 'mysql-test/t')
| -rw-r--r-- | mysql-test/t/events_2.test | 2 | ||||
| -rw-r--r-- | mysql-test/t/gis.test | 15 | ||||
| -rw-r--r-- | mysql-test/t/loaddata.test | 2 | ||||
| -rw-r--r-- | mysql-test/t/log_tables-big.test | 16 | ||||
| -rw-r--r-- | mysql-test/t/mysqldump.test | 50 | ||||
| -rw-r--r-- | mysql-test/t/subselect_exists2in.test | 28 | ||||
| -rw-r--r-- | mysql-test/t/subselect_innodb.test | 21 | ||||
| -rw-r--r-- | mysql-test/t/subselect_sj_mat.test | 93 | ||||
| -rw-r--r-- | mysql-test/t/symlink.test | 16 |
9 files changed, 234 insertions, 9 deletions
diff --git a/mysql-test/t/events_2.test b/mysql-test/t/events_2.test index 20f17966b29..12ce9210b0a 100644 --- a/mysql-test/t/events_2.test +++ b/mysql-test/t/events_2.test @@ -15,7 +15,7 @@ use events_test; # mysql.event intact checking end # -create event e_26 on schedule at '2027-01-01 00:00:00' disable do set @a = 5; +create event e_26 on schedule at '2037-01-01 00:00:00' disable do set @a = 5; select db, name, body, definer, convert_tz(execute_at, 'UTC', 'SYSTEM'), on_completion from mysql.event; drop event e_26; --error ER_WRONG_VALUE diff --git a/mysql-test/t/gis.test b/mysql-test/t/gis.test index ea4c024988d..b109b8f6b0c 100644 --- a/mysql-test/t/gis.test +++ b/mysql-test/t/gis.test @@ -1453,7 +1453,20 @@ SHOW CREATE VIEW v1; SELECT ASTEXT(p) FROM v1; DROP VIEW v1; ---echo End of 5.5 tests +# --echo End of 5.5 tests + +--echo # +--echo # Start of 10.0 tests +--echo # + +--echo # +--echo # MDEV-12495 Conditional jump depends on uninitialised value for: SELECT NULL UNION geom_expression +--echo # +SELECT AsText(g) FROM (SELECT NULL AS g UNION SELECT Point(1,1)) AS t1; + +--echo # +--echo # End 10.0 tests +--echo # SHOW CREATE TABLE information_schema.geometry_columns; SHOW CREATE TABLE information_schema.spatial_ref_sys; diff --git a/mysql-test/t/loaddata.test b/mysql-test/t/loaddata.test index 1bc7eb139b9..8f8ff3520a2 100644 --- a/mysql-test/t/loaddata.test +++ b/mysql-test/t/loaddata.test @@ -294,7 +294,7 @@ SELECT * FROM v2; DELETE FROM t1; --echo ---error ER_LOAD_DATA_INVALID_COLUMN +--error ER_NONUPDATEABLE_COLUMN LOAD DATA INFILE '../../std_data/bug35469.dat' INTO TABLE v2 FIELDS ESCAPED BY '\\' TERMINATED BY ',' diff --git a/mysql-test/t/log_tables-big.test b/mysql-test/t/log_tables-big.test index 8c956fa6f55..8936a163d73 100644 --- a/mysql-test/t/log_tables-big.test +++ b/mysql-test/t/log_tables-big.test @@ -7,6 +7,8 @@ # check that CSV engine was compiled in --source include/have_csv.inc +set @@global.log_output = 'TABLE'; + connect (con1,localhost,root,,); connect (con2,localhost,root,,); @@ -18,18 +20,20 @@ set session long_query_time=10; select get_lock('bug27638', 1); connection con2; set session long_query_time=1; -truncate table mysql.slow_log; select get_lock('bug27638', 2); -select if (query_time between '00:00:01' and '00:00:10', 'OK', 'WRONG') as qt, sql_text from mysql.slow_log; -truncate table mysql.slow_log; +select if (query_time >= '00:00:01', 'OK', 'WRONG') as qt, sql_text from mysql.slow_log + where sql_text = 'select get_lock(\'bug27638\', 2)'; select get_lock('bug27638', 60); -select if (query_time between '00:00:59' and '00:01:10', 'OK', 'WRONG') as qt, sql_text from mysql.slow_log; -truncate table mysql.slow_log; +select if (query_time >= '00:00:59', 'OK', 'WRONG') as qt, sql_text from mysql.slow_log + where sql_text = 'select get_lock(\'bug27638\', 60)'; select get_lock('bug27638', 101); -select if (query_time between '00:01:40' and '00:01:50', 'OK', 'WRONG') as qt, sql_text from mysql.slow_log; +select if (query_time >= '00:01:40', 'OK', 'WRONG') as qt, sql_text from mysql.slow_log + where sql_text = 'select get_lock(\'bug27638\', 101)'; connection con1; select release_lock('bug27638'); connection default; disconnect con1; disconnect con2; + +set @@global.log_output=default; diff --git a/mysql-test/t/mysqldump.test b/mysql-test/t/mysqldump.test index 87091d549d1..fca334a7fee 100644 --- a/mysql-test/t/mysqldump.test +++ b/mysql-test/t/mysqldump.test @@ -2485,6 +2485,56 @@ DROP TABLE t2; DROP DATABASE db_20772273; USE test; +--echo # +--echo # Bug #25717383: MYSQLDUMP MAY EXECUTE ANY ARBITRARY QUERY +--echo # + + +CREATE DATABASE bug25717383; +use bug25717383; + +CREATE TABLE `tab +one` (a int); +CREATE VIEW `view +one` as SELECT * FROM `tab +one`; + +CREATE PROCEDURE `proc +one`() SELECT * from `tab +one`; + +CREATE TEMPORARY TABLE `temp +one` (id INT); + +CREATE TRIGGER `trig +one` BEFORE INSERT ON `tab +one` FOR EACH ROW SET NEW.a = 1; + +CREATE EVENT `event +one` ON SCHEDULE AT '2030-01-01 00:00:00' DO SET @a=5; + +SHOW TABLES FROM bug25717383; +SHOW TRIGGERS FROM bug25717383; +--replace_column 6 # +SHOW EVENTS FROM bug25717383; + +SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES + WHERE ROUTINE_SCHEMA='bug25717383' AND ROUTINE_TYPE= 'PROCEDURE' + ORDER BY ROUTINE_NAME; + +--exec $MYSQL_DUMP --triggers --events --routines --add-drop-database --databases bug25717383 > $MYSQLTEST_VARDIR/tmp/bug25717383.sql + +SHOW TABLES FROM bug25717383; +SHOW TRIGGERS FROM bug25717383; +--replace_column 6 # +SHOW EVENTS FROM bug25717383; + +SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES + WHERE ROUTINE_SCHEMA='bug25717383' AND ROUTINE_TYPE= 'PROCEDURE' + ORDER BY ROUTINE_NAME; + +DROP DATABASE bug25717383; + # # MDEV-6091 mysqldump goes in a loop and segfaults if --dump-slave is specified and it cannot connect to the server # diff --git a/mysql-test/t/subselect_exists2in.test b/mysql-test/t/subselect_exists2in.test index 9450ef71494..a4fdbe5c50b 100644 --- a/mysql-test/t/subselect_exists2in.test +++ b/mysql-test/t/subselect_exists2in.test @@ -758,6 +758,34 @@ deallocate prepare stmt; drop view v1; drop table t1,t2; +--echo # +--echo #MDEV-10053: EXIST to IN transformation turned down +--echo # + +CREATE TABLE t1 ( + pk INT, f1 INT NOT NULL, f2 VARCHAR(3), f3 INT NULL, PRIMARY KEY(pk)) +ENGINE=MyISAM; +INSERT INTO t1 VALUES (1,1,'foo',8), (2,5,'bar',7); + +let $q= +SELECT STRAIGHT_JOIN sq1.f2 + FROM ( SELECT * FROM t1 ) AS sq1 + WHERE EXISTS ( SELECT * FROM t1 AS sq2 + WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 ); + +set @optimizer_switch_save=@@optimizer_switch; + +set optimizer_switch='exists_to_in=off'; +eval explain extended $q; +eval $q; +set optimizer_switch='exists_to_in=on'; +eval explain extended $q; +eval $q; + +set optimizer_switch= @optimizer_switch_save; + +DROP TABLE t1; + --echo # End of 10.0 tests #restore defaults diff --git a/mysql-test/t/subselect_innodb.test b/mysql-test/t/subselect_innodb.test index b26c5036f3f..2451bc60fee 100644 --- a/mysql-test/t/subselect_innodb.test +++ b/mysql-test/t/subselect_innodb.test @@ -503,6 +503,27 @@ drop view v1; drop table t1,t2; --echo # +--echo # MDEV-10693: cost-based choice between materialization and in-to-exists +--echo # for a subquery from the expression used in ref access +--echo # + +--source include/have_innodb.inc + +CREATE TABLE t1 (i1 INT PRIMARY KEY) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1),(2); + +CREATE TABLE t2 (i2 INT) ENGINE=InnoDB; + +CREATE TABLE t3 (i3 INT PRIMARY KEY) ENGINE=InnoDB; +INSERT INTO t3 VALUES (3); + +SELECT * FROM t1 + WHERE NULL IN ( SELECT i2 FROM t2 + WHERE i1 IN ( i2 IN ( SELECT i3 FROM t3 ) ) AND i2 = 2 ); + +DROP TABLE t1,t2,t3; + +--echo # --echo # MDEV-6041: ORDER BY+subqueries: subquery_table.key=outer_table.col is not recongized as binding --echo # create table t1(a int) engine=innodb; diff --git a/mysql-test/t/subselect_sj_mat.test b/mysql-test/t/subselect_sj_mat.test index 559b380a7dc..e421517aa21 100644 --- a/mysql-test/t/subselect_sj_mat.test +++ b/mysql-test/t/subselect_sj_mat.test @@ -1857,6 +1857,99 @@ execute stmt; drop table t1; +--echo # +--echo # MDEV-12429: IN subquery used in WHERE of EXISTS subquery +--echo # + +CREATE TABLE t1 ( + pk INT, f1 INT NOT NULL, f2 VARCHAR(3), f3 INT NULL, PRIMARY KEY(pk)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1,1,'foo',8), (2,5,'bar',7); + +SELECT sq1.f2 FROM t1 AS sq1 + WHERE EXISTS ( SELECT * FROM t1 AS sq2 + WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 ); + +set @save_optimizer_switch= @@optimizer_switch; + +set optimizer_switch='exists_to_in=off'; +EXPLAIN +SELECT sq1.f2 FROM t1 AS sq1 + WHERE EXISTS ( SELECT * FROM t1 AS sq2 + WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 ); + +--echo # this checks the result set above +set optimizer_switch= 'materialization=off,semijoin=off'; +SELECT sq1.f2 FROM t1 AS sq1 + WHERE EXISTS ( SELECT * FROM t1 AS sq2 + WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 ); + +set optimizer_switch= @save_optimizer_switch; + +DROP TABLE t1; + +--echo # +--echo # MDEV-12145: IN subquery used in WHERE of EXISTS subquery +--echo # + +CREATE TABLE t1 (f1 INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (4),(6); + +CREATE TABLE t2 (i2 INT, KEY(i2)) ENGINE=MyISAM; +INSERT INTO t2 VALUES (8),(7),(1); + +CREATE TABLE t3 (f3 INT, i3 INT, KEY(i3)) ENGINE=MyISAM; +INSERT INTO t3 VALUES (8,0),(6,3),(2,8),(3,8),(1,6),(0,0),(1,0),(1,5); + +set @save_optimizer_switch= @@optimizer_switch; + +set optimizer_switch='exists_to_in=off'; +SELECT * FROM t1 + WHERE EXISTS ( SELECT * FROM t2, t3 + WHERE i3 = i2 AND f1 IN ( SELECT f3 FROM t3 ) ); +EXPLAIN EXTENDED +SELECT * FROM t1 + WHERE EXISTS ( SELECT * FROM t2, t3 + WHERE i3 = i2 AND f1 IN ( SELECT f3 FROM t3 ) ); + +--echo # this checks the result set above +set optimizer_switch= 'materialization=off,semijoin=off'; +SELECT * FROM t1 + WHERE EXISTS ( SELECT * FROM t2, t3 + WHERE i3 = i2 AND f1 IN ( SELECT f3 FROM t3 ) ); + +set optimizer_switch= @save_optimizer_switch; + +DROP TABLE t1,t2,t3; + +--echo # +--echo # MDEV-9686: IN subquery used in WHERE of a subquery from select list +--echo # + +CREATE TABLE t1 (pk INT PRIMARY KEY, f1 INT); +INSERT INTO t1 VALUES (1, 4),(2, 3),(3, 3),(4, 6),(5, 3); + +CREATE TABLE t2 (f2 INT); +INSERT INTO t2 VALUES (1),(2),(3),(4),(5); + +--echo # t1.pk is always IN ( SELECT f2 FROM t2 ), +--echo # so the IN condition should be true for every row, +--echo # and thus COUNT(*) should always return 5 + +SELECT pk, f1, ( SELECT COUNT(*) FROM t2 + WHERE t1.pk IN ( SELECT f2 FROM t2 ) ) AS sq FROM t1; +EXPLAIN EXTENDED +SELECT pk, f1, ( SELECT COUNT(*) FROM t2 + WHERE t1.pk IN ( SELECT f2 FROM t2 ) ) AS sq FROM t1; + +--echo # this checks the result set above +set @save_optimizer_switch= @@optimizer_switch; +set optimizer_switch= 'materialization=off,semijoin=off'; +SELECT pk, f1, ( SELECT COUNT(*) FROM t2 + WHERE t1.pk IN ( SELECT f2 FROM t2 ) ) AS sq FROM t1; +set optimizer_switch= @save_optimizer_switch; + +DROP TABLE t1,t2; + --echo # End of 5.5 tests --echo # --echo # MDEV-7220: Materialization strategy is not used for REPLACE ... SELECT diff --git a/mysql-test/t/symlink.test b/mysql-test/t/symlink.test index 065be302237..e17ea07ca3c 100644 --- a/mysql-test/t/symlink.test +++ b/mysql-test/t/symlink.test @@ -300,3 +300,19 @@ select create_options from information_schema.tables where table_name='t1'; create table t2 like t1; show create table t2; drop tables t1, t2; + +--echo # +--echo # Test for bug #25514146 DB_NAME IS IGNORED WHEN CREATING TABLE +--echo # WITH DATA DIRECTORY +--echo # + +--echo # Make sure we have no current database +CREATE DATABASE x; +USE x; +DROP DATABASE x; + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +eval CREATE TABLE test.t1(id INT(11)) ENGINE MYISAM +DATA DIRECTORY "$MYSQLTEST_VARDIR/tmp"; + +DROP TABLE test.t1; |