diff options
| author | Sergei Golubchik <sergii@pisem.net> | 2013-09-18 13:07:31 +0200 |
|---|---|---|
| committer | Sergei Golubchik <sergii@pisem.net> | 2013-09-18 13:07:31 +0200 |
| commit | 4ec2e9d7eda78d409d1b017ef4d8928fe9055438 (patch) | |
| tree | 6c3a74a740d3c1c5f3a7d1f8154d8a791b435b3f /mysql-test/t | |
| parent | 1a2a9d74fe1256554eceb09bbc6752a6376df87d (diff) | |
| parent | 197bdbae4db78ba65f3668803bebd3c4a4509ae5 (diff) | |
| download | mariadb-git-4ec2e9d7eda78d409d1b017ef4d8928fe9055438.tar.gz | |
5.5 merge and fixes for compiler/test errors
Diffstat (limited to 'mysql-test/t')
28 files changed, 879 insertions, 28 deletions
diff --git a/mysql-test/t/bad_frm_crash_5029.test b/mysql-test/t/bad_frm_crash_5029.test new file mode 100644 index 00000000000..b15c37fbc6d --- /dev/null +++ b/mysql-test/t/bad_frm_crash_5029.test @@ -0,0 +1,11 @@ +# +# MDEV-5029 Crash in MariaDB 5.5.33 with .frm from older MariaDB release +# +# a.k.a. fail to open an frm with indexes: + +let $datadir=`select @@datadir`; +copy_file std_data/mdev5029.frm $datadir/test/t1.frm; +--error ER_UNKNOWN_STORAGE_ENGINE +show create table t1; +remove_file $datadir/test/t1.frm; + diff --git a/mysql-test/t/client_xml.test b/mysql-test/t/client_xml.test index 0847e2b366b..86ca0a920ad 100644 --- a/mysql-test/t/client_xml.test +++ b/mysql-test/t/client_xml.test @@ -23,7 +23,7 @@ insert into t1 values (1, 2, 'a&b a<b a>b'); --source include/count_sessions.inc --exec $MYSQL --xml test -e "select * from t1" ---exec $MYSQL_DUMP --xml --skip-create test +--exec $MYSQL_DUMP --xml --skip-create-options test --exec $MYSQL --xml test -e "select count(*) from t1" --exec $MYSQL --xml test -e "select 1 < 2 from dual" diff --git a/mysql-test/t/derived.test b/mysql-test/t/derived.test index 381c87c635c..008af317aec 100644 --- a/mysql-test/t/derived.test +++ b/mysql-test/t/derived.test @@ -320,6 +320,37 @@ DROP TABLE t1; --echo # End of 5.0 tests +--echo # +--echo # MDEV-5005: Subquery in Procedure somehow affecting temporary table +--echo # + +create temporary table if not exists t1 (id int not null); + +select A.* from ( select tt.* from t1 tt ) A; + +prepare stmt from "select A.* from ( select tt.* from t1 tt ) A "; +execute stmt; +deallocate prepare stmt; + +drop temporary table t1; + +--delimiter | +CREATE PROCEDURE p () +BEGIN + select A.* from ( select tt.* from t1 tt ) A ; +END | +--delimiter ; + +create temporary table if not exists t1 (id int not null); + +CALL p(); +CALL p(); + +drop procedure p; + +drop temporary table t1; + +--echo # End of 5.3 tests --echo # --echo # Bug#58730 Assertion failed: table->key_read == 0 in close_thread_table, @@ -342,7 +373,6 @@ SELECT 1 FROM t1 JOIN v1 ON 1 > (SELECT 1 FROM v2); DROP TABLE t1, t2; DROP VIEW v1, v2; -set optimizer_switch=@save_derived_optimizer_switch; # # MDEV-614 lp:1050806 - different result for a query using subquery between 5.5.25 and 5.5.27 @@ -358,3 +388,17 @@ select * from ( where d1 < '2012-12-12 12:12:12' and n in (2085, 2084) order by d2 asc ) as calculated_result; drop table t1; + +# +# MDEV-5012 Server crashes in Item_ref::real_item on EXPLAIN with select subqueries or views, constant table, derived_merge+derived_with_keys +# +SET optimizer_switch = 'derived_merge=on,derived_with_keys=on,in_to_exists=on'; +CREATE TABLE t1 (a INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (8); +CREATE TABLE t2 (b INT) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1),(7); +EXPLAIN SELECT * FROM (SELECT * FROM t1) AS table1, + (SELECT DISTINCT * FROM t2) AS table2 WHERE b = a AND a <> ANY (SELECT 9); +DROP TABLE t1, t2; + +set optimizer_switch=@save_derived_optimizer_switch; diff --git a/mysql-test/t/drop_bad_db_type.test b/mysql-test/t/drop_bad_db_type.test new file mode 100644 index 00000000000..69e1a889b18 --- /dev/null +++ b/mysql-test/t/drop_bad_db_type.test @@ -0,0 +1,25 @@ + +--source include/have_debug.inc + +if (!$HA_ARCHIVE_SO) { + skip Needs Archive plugin; +} + +let $mysqld_datadir= `select @@datadir`; + +set debug_dbug='+d,unstable_db_type'; + +install soname 'ha_archive'; +create table t1 (a int) engine=archive; +insert t1 values (1),(2),(3); +flush tables; +uninstall soname 'ha_archive'; + +install soname 'ha_archive'; +--list_files $mysqld_datadir/test +drop table t1; +--list_files $mysqld_datadir/test +uninstall soname 'ha_archive'; + +set debug_dbug='-d,unstable_db_type'; + diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test index a5c35c0dff2..0c2f28ab25d 100644 --- a/mysql-test/t/func_group.test +++ b/mysql-test/t/func_group.test @@ -1403,6 +1403,26 @@ select z from (select count(*) as z from t1) v; select z from (select count(*) as z from t1) v group by 1; drop table t1; +# +# Bug mdev-5015: Degenerate OR condition in an aggregated join +# + +CREATE TABLE t1 (i1 int, INDEX(i1)); +INSERT INTO t1 VALUES (9),(8); + +CREATE TABLE t2 (i2 int); +INSERT INTO t2 VALUES (8),(4); + + +CREATE TABLE t3 (i3 int, INDEX(i3)); +INSERT INTO t3 VALUES (9),(8); + +SELECT MAX(t3.i3) FROM t3, t2, t1 WHERE t1.i1 = t2.i2 AND ( 0 OR t3.i3 = t2.i2 ); + +SELECT MAX(t3.i3) FROM t3, t2, t1 WHERE t1.i1 = t2.i2 AND t3.i3 = t2.i2; + +DROP TABLE t1,t2,t3; + --echo # end of 5.3 tests --echo # diff --git a/mysql-test/t/func_str.test b/mysql-test/t/func_str.test index 1a60b0c222a..0715fca7d96 100644 --- a/mysql-test/t/func_str.test +++ b/mysql-test/t/func_str.test @@ -6,6 +6,8 @@ drop table if exists t1,t2; --enable_warnings +let $mysqld_datadir= `select @@datadir`; + set names latin1; select 'hello',"'hello'",'""hello""','''h''e''l''l''o''',"hel""lo",'hel\'lo'; @@ -1379,9 +1381,11 @@ SELECT CONVERT(('' IN (REVERSE(CAST(('') AS DECIMAL)), '')), CHAR(3)); --echo # and other crashes --echo # CREATE TABLE t1 ( a TEXT ); -SELECT 'aaaaaaaaaaaaaa' INTO OUTFILE 'bug58165.txt'; +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval SELECT 'aaaaaaaaaaaaaa' INTO OUTFILE '$MYSQLTEST_VARDIR/tmp/bug58165.txt'; SELECT insert( substring_index( 'a', 'a', 'b' ), 1, 0, 'x' ); -LOAD DATA INFILE 'bug58165.txt' INTO TABLE t1; +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval LOAD DATA INFILE '$MYSQLTEST_VARDIR/tmp/bug58165.txt' INTO TABLE t1; SELECT * FROM t1; DROP TABLE t1; diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test index 6f9359e6095..045b91b3e7b 100644 --- a/mysql-test/t/func_time.test +++ b/mysql-test/t/func_time.test @@ -1194,3 +1194,246 @@ SELECT 1 FROM DUAL WHERE SECOND(TIMEDIFF(NULL, '12:12:12')); SET TIME_ZONE='+02:00'; SELECT UNIX_TIMESTAMP(STR_TO_DATE('2020','%Y')); SET TIME_ZONE=DEFAULT; + + +--echo # +--echo # MDEV-4863 COALESCE(time_or_datetime) returns wrong results in numeric context +--echo # +CREATE TABLE t1 (a TIMESTAMP(3)); +INSERT INTO t1 VALUES ('2001-01-01 10:20:30.999'); +SELECT CAST(COALESCE(a,a) AS SIGNED) AS c1, CAST(COALESCE(a,a) AS DECIMAL(25,3)) AS c2, ROUND(COALESCE(a,a)) AS c2 FROM t1; +DROP TABLE t1; +CREATE TABLE t1 (a TIME(3)); +INSERT INTO t1 VALUES ('10:20:30.999'); +SELECT CAST(COALESCE(a,a) AS SIGNED) AS c1, CAST(COALESCE(a,a) AS DECIMAL(25,3)) AS c2, ROUND(COALESCE(a,a)) AS c2 FROM t1; +DROP TABLE t1; + +SELECT + CAST(COALESCE(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30')) AS SIGNED) AS c1, + CAST(COALESCE(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30')) AS DECIMAL(25,4)) AS c2, + COALESCE(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))+0e0 AS c3, + CONCAT(COALESCE(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c4, + TIME(COALESCE(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c5, + DATE(COALESCE(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c6, + TIMESTAMP(COALESCE(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c7; + +SELECT + CAST(COALESCE(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01')) AS SIGNED) AS c1, + CAST(COALESCE(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01')) AS DECIMAL(25,4)) AS c2, + COALESCE(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))+0e0 AS c3, + CONCAT(COALESCE(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))) AS c4, + TIME(COALESCE(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))) AS c5, + DATE(COALESCE(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))) AS c6, + TIMESTAMP(COALESCE(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))) AS c7; + +SELECT + CAST(IFNULL(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30')) AS SIGNED) AS c1, + CAST(IFNULL(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30')) AS DECIMAL(25,4)) AS c2, + IFNULL(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))+0e0 AS c3, + CONCAT(IFNULL(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c4, + TIME(IFNULL(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c5, + DATE(IFNULL(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c6, + TIMESTAMP(IFNULL(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c7; + +SELECT + CAST(IFNULL(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01')) AS SIGNED) AS c1, + CAST(IFNULL(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01')) AS DECIMAL(25,4)) AS c2, + IFNULL(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))+0e0 AS c3, + CONCAT(IFNULL(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))) AS c4, + TIME(IFNULL(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))) AS c5, + DATE(IFNULL(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))) AS c6, + TIMESTAMP(IFNULL(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))) AS c7; + +SELECT + CAST(IF(1,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30')) AS SIGNED) AS c1, + CAST(IF(1,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30')) AS DECIMAL(25,4)) AS c2, + IF(1,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))+0e0 AS c3, + CONCAT(IF(1,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c4, + TIME(IF(1,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c5, + DATE(IF(1,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c6, + TIMESTAMP(IF(1,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c7; + +SELECT + CAST(IF(0,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30')) AS SIGNED) AS c1, + CAST(IF(0,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30')) AS DECIMAL(25,4)) AS c2, + IF(0,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))+0e0 AS c3, + CONCAT(IF(0,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c4, + TIME(IF(0,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c5, + DATE(IF(0,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c6, + TIMESTAMP(IF(0,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c7; + +SELECT + CAST(CASE WHEN 1 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END AS SIGNED) AS c1, + CAST(CASE WHEN 1 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END AS DECIMAL(25,4)) AS c2, + CASE WHEN 1 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END+0e0 AS c3, + CONCAT(CASE WHEN 1 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END) AS c4, + TIME(CASE WHEN 1 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END) AS c5, + DATE(CASE WHEN 1 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END) AS c6, + TIMESTAMP(CASE WHEN 1 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END) AS c7; + +SELECT + CAST(CASE WHEN 0 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END AS SIGNED) AS c1, + CAST(CASE WHEN 0 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END AS DECIMAL(25,4)) AS c2, + CASE WHEN 0 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END+0e0 AS c3, + CONCAT(CASE WHEN 0 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END) AS c4, + TIME(CASE WHEN 0 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END) AS c5, + DATE(CASE WHEN 0 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END) AS c6, + TIMESTAMP(CASE WHEN 0 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END) AS c7; + +CREATE TABLE t1 AS SELECT + CONCAT(COALESCE(TIME(101010),TIME(101010))) AS c1, + CONCAT(IF(0,TIME(101010),TIME(101010))) AS c2, + CONCAT(IFNULL(TIME(101010),TIME(101010))) AS c3, + CONCAT(CASE WHEN 1 THEN TIME(101010) ELSE TIME(101010) END) AS c4; +SHOW CREATE TABLE t1; +DROP TABLE t1; + + +--echo # +--echo # MDEV-4870 Wrong values of CASE, COALESCE, IFNULL on a combination of different temporal types +--echo # +CREATE TABLE t1 (dt2 DATETIME(2), t3 TIME(3), d DATE); +INSERT INTO t1 VALUES ('2001-01-01 00:00:00.12', '00:00:00.567', '2002-01-01'); +SELECT CASE WHEN 0 THEN dt2 ELSE t3 END FROM t1; +CREATE TABLE t2 AS SELECT CASE WHEN 0 THEN dt2 ELSE t3 END FROM t1; +SELECT * FROM t2; +SHOW COLUMNS FROM t2; +DROP TABLE t2; +SELECT CASE WHEN 1 THEN dt2 ELSE t3 END FROM t1; +SELECT CONCAT(CASE WHEN 1 THEN dt2 ELSE t3 END) FROM t1; +SELECT CONCAT(CASE WHEN 0 THEN t3 ELSE dt2 END) FROM t1; +SELECT CONCAT(CASE WHEN 1 THEN d ELSE t3 END) FROM t1; +SELECT CASE WHEN 1 THEN t3 ELSE d END FROM t1; +SELECT COALESCE(d, t3) FROM t1; +SELECT CONCAT(COALESCE(d, t3)) FROM t1; +SELECT COALESCE(dt2, t3) FROM t1; +SELECT CONCAT(COALESCE(dt2, t3)) FROM t1; +SELECT IFNULL(dt2, t3), CONCAT(IFNULL(dt2, t3)) FROM t1; +SELECT IFNULL(d, t3), CONCAT(IFNULL(d, t3)) FROM t1; +DROP TABLE t1; + + +--echo # +--echo # MDEV-4724 Some temporal functions do not preserve microseconds +--echo # +SELECT MAKETIME(10,10,10.231); +SELECT MAKETIME(0, 0, 59.9); +CREATE TABLE t1 AS SELECT + MAKETIME(10,00,00), + MAKETIME(10,00,00.1), + MAKETIME(10,00,00.12), + MAKETIME(10,00,00.123), + MAKETIME(10,00,00.1234), + MAKETIME(10,00,00.12345), + MAKETIME(10,00,00.123456); +SHOW COLUMNS FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 AS SELECT + TIME('10:00:00'), + TIME('10:00:00.1'), + TIME('10:00:00.12'), + TIME('10:00:00.123'), + TIME('10:00:00.1234'), + TIME('10:00:00.12345'), + TIME('10:00:00.12346'); +SHOW COLUMNS FROM t1; +DROP TABLE t1; + +SET TIME_ZONE='+00:00'; +SET TIMESTAMP=UNIX_TIMESTAMP('2012-10-16 22:46:17'); +SELECT NOW(), UNIX_TIMESTAMP(), UNIX_TIMESTAMP(NOW()),UNIX_TIMESTAMP('2012-10-16 22:46:17'); +SET TIMESTAMP=UNIX_TIMESTAMP('1970-01-02 03:04:05.123456'); +SELECT @@timestamp, FROM_UNIXTIME(@@timestamp); +SET TIME_ZONE=DEFAULT; +SET TIMESTAMP=DEFAULT; + +SELECT TIME('2012-10-16 15:54:16.12'); +SELECT TIMESTAMP('2012-10-16 15:54:16.12'); +SELECT TIMEDIFF('10:10:10.1','00:00:00'); +SELECT TIME_TO_SEC('10:10:10'); +SELECT ADDTIME(TIME('10:10:10.1'),'10:10:10.12'); +SELECT ADDTIME(TIMESTAMP('2001-01-01 10:10:10.1'),'10:10:10.12'); +SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1 SECOND); +SELECT DATE_ADD('2001-01-01 00:00:00.1', INTERVAL 1 SECOND); +SELECT DATE_ADD('2001-01-01 00:00:00.12', INTERVAL 1 SECOND); +SELECT DATE_ADD('2001-01-01 00:00:00.123', INTERVAL 1 SECOND); +SELECT DATE_ADD('2001-01-01 00:00:00.1234', INTERVAL 1 SECOND); +SELECT DATE_ADD('2001-01-01 00:00:00.12345', INTERVAL 1 SECOND); +SELECT DATE_ADD('2001-01-01 00:00:00.123456', INTERVAL 1 SECOND); +SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1 SECOND); +SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.1 SECOND); +SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.12 SECOND); +SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.123 SECOND); +SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.1234 SECOND); +SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.12345 SECOND); +SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.123456 SECOND); +SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL -1 SECOND); +SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.1 SECOND); +SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.12 SECOND); +SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.123 SECOND); +SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.1234 SECOND); +SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.12345 SECOND); +SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.123456 SECOND); +SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 'xxx' SECOND); +SELECT CONVERT_TZ('2001-01-01 10:20:30.12','+00:00','+01:00'); + +--echo # +--echo # MDEV-4861 TIME/DATETIME arithmetics does not preserve INTERVAL precision +--echo # +CREATE TABLE t1 (t0 TIME); +INSERT INTO t1 VALUES ('00:00:00'); +SELECT t0 + INTERVAL 1.1 SECOND FROM t1; +CREATE TABLE t2 AS SELECT t0 + INTERVAL 1.1 SECOND FROM t1; +SHOW COLUMNS FROM t2; +DROP TABLE t1,t2; + +CREATE TABLE t1 (t0 DATETIME); +INSERT INTO t1 VALUES ('2001-01-01 00:00:00'); +SELECT t0 + INTERVAL 1.1 SECOND FROM t1; +CREATE TABLE t2 AS SELECT t0 + INTERVAL 1.1 SECOND FROM t1; +SHOW COLUMNS FROM t2; +DROP TABLE t1, t2; + + +--echo # +--echo # MDEV-4843 Wrong data type for TIMESTAMP('2001-01-01','10:10:10') +--echo # +CREATE TABLE t1 AS SELECT + TIMESTAMP('2001-01-01','10:10:10'), + TIMESTAMP('2001-01-01','10:10:10.1'), + TIMESTAMP('2001-01-01','10:10:10.12'), + TIMESTAMP('2001-01-01','10:10:10.123'), + TIMESTAMP('2001-01-01','10:10:10.1234'), + TIMESTAMP('2001-01-01','10:10:10.12345'), + TIMESTAMP('2001-01-01','10:10:10.123456'), + TIMESTAMP('2001-01-01','10:10:10.1234567'); +SHOW COLUMNS FROM t1; +SELECT * FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 AS SELECT + TIMESTAMP('2001-01-01 00:00:00','10:10:10'), + TIMESTAMP('2001-01-01 00:00:00.1','10:10:10'), + TIMESTAMP('2001-01-01 00:00:00.12','10:10:10'), + TIMESTAMP('2001-01-01 00:00:00.123','10:10:10'), + TIMESTAMP('2001-01-01 00:00:00.1234','10:10:10'), + TIMESTAMP('2001-01-01 00:00:00.12345','10:10:10'), + TIMESTAMP('2001-01-01 00:00:00.123456','10:10:10'), + TIMESTAMP('2001-01-01 00:00:00.1234567','10:10:10'); +SHOW COLUMNS FROM t1; +SELECT * FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 AS SELECT + TIMESTAMP('00:00:00','10:10:10'), + TIMESTAMP(TIME('00:00:00'),'10:10:10'); +SHOW COLUMNS FROM t1; +SELECT * FROM t1; +DROP TABLE t1; + +--echo # +--echo # MDEV-4869 Wrong result of MAKETIME(0, 0, -0.1) +--echo # +SELECT MAKETIME(0, 0, -0.1); + diff --git a/mysql-test/t/group_min_max.test b/mysql-test/t/group_min_max.test index 3c286026726..ef1ee58c97e 100644 --- a/mysql-test/t/group_min_max.test +++ b/mysql-test/t/group_min_max.test @@ -1379,6 +1379,8 @@ EXPLAIN SELECT a, COUNT(DISTINCT a), SUM(DISTINCT a) FROM t2 SELECT a, COUNT(DISTINCT a), SUM(DISTINCT a) FROM t2 WHERE b = 13 AND c = 42 GROUP BY a; +# This query could have been resolved using loose index scan since the second +# part of count(..) is defined by a constant predicate EXPLAIN SELECT COUNT(DISTINCT a, b), SUM(DISTINCT a) FROM t2 WHERE b = 42; SELECT COUNT(DISTINCT a, b), SUM(DISTINCT a) FROM t2 WHERE b = 42; diff --git a/mysql-test/t/information_schema.test b/mysql-test/t/information_schema.test index b6ee712ec28..8c88be5c603 100644 --- a/mysql-test/t/information_schema.test +++ b/mysql-test/t/information_schema.test @@ -51,7 +51,7 @@ create view v1 (c) as SELECT table_name FROM information_schema.TABLES WHERE table_schema IN ('mysql', 'INFORMATION_SCHEMA', 'test', 'mysqltest') AND table_name not like 'ndb_%' AND table_name not like 'innodb_%' AND - table_name not like 'pbxt_%' AND table_name not like 'xtradb_%'; + table_name not like 'xtradb_%'; --sorted_result select * from v1; diff --git a/mysql-test/t/insert.test b/mysql-test/t/insert.test index 22b5ff9127c..f9b9fcf266d 100644 --- a/mysql-test/t/insert.test +++ b/mysql-test/t/insert.test @@ -518,7 +518,9 @@ set sql_mode='error_for_division_by_zero'; --echo # --enable_info insert t1 (data) values ('letter'), (1/0); +--disable_ps_protocol update t1 set data='envelope' where 1/0 or 1; +--enable_ps_protocol insert t1 (data) values (default), (1/0), ('dead beef'); --disable_info diff --git a/mysql-test/t/join.test b/mysql-test/t/join.test index 73b40715676..54b2a3c82ea 100644 --- a/mysql-test/t/join.test +++ b/mysql-test/t/join.test @@ -1141,6 +1141,22 @@ DROP TABLE t1,t2,t3,t4,t5; --error ER_PARSE_ERROR SELECT * FROM t5 JOIN (t1 JOIN t2 UNION SELECT * FROM t3 JOIN t4); +--echo # +--echo # MDEV-4959: join of const table with NULL fields +--echo # + +CREATE TABLE t1 (i1 int) ENGINE=MyISAM; +INSERT INTO t1 VALUES (NULL); +CREATE TABLE t2 (i2 int, a int, b int) ENGINE=MyISAM; +CREATE ALGORITHM=TEMPTABLE VIEW v2 AS SELECT * FROM t2; +INSERT INTO t2 VALUES (NULL,1,2),(NULL,2,3); + +SELECT * FROM t1 JOIN v2 ON i1 = i2 WHERE a < b; +EXPLAIN EXTENDED +SELECT * FROM t1 JOIN v2 ON i1 = i2 WHERE a < b; + +DROP VIEW v2; +DROP TABLE t1,t2; SET optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/t/join_outer.test b/mysql-test/t/join_outer.test index b0000b2b943..7a70c413e8d 100644 --- a/mysql-test/t/join_outer.test +++ b/mysql-test/t/join_outer.test @@ -1686,5 +1686,95 @@ explain select * from t1 left join t0 on t0.a=t1.pk where t0.person_id='fooo' or drop table t0, t1; +--echo # +--echo # MDEV-4836: Wrong result on <not null date column> IS NULL (old documented hack stopped working) +--echo # (this is a regression after fix for MDEV-4817) +--echo # +CREATE TABLE t1 (id INT, d DATE NOT NULL); +INSERT INTO t1 VALUES (1,'0000-00-00'),(2,'0000-00-00'); +CREATE TABLE t2 (i INT); +SELECT * FROM t1 LEFT JOIN t2 ON (id=i) WHERE NULL OR d IS NULL; +DROP TABLE t1,t2; + + +CREATE TABLE t1 (i1 INT, d1 DATE NOT NULL); +INSERT INTO t1 VALUES (1,'2012-12-21'),(2,'0000-00-00'); + +CREATE TABLE t2 (i2 INT, j2 INT); +INSERT INTO t2 VALUES (1,10),(2,20); + +SELECT * FROM t1 LEFT JOIN t2 ON i1 = j2 WHERE d1 IS NULL AND 1 OR i1 = i2; +DROP TABLE t1,t2; + +--echo # Another testcase +CREATE TABLE t1 (i1 INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (NULL); + +CREATE TABLE t2 (i2 INT, a INT, b INT) ENGINE=MyISAM; +CREATE ALGORITHM=TEMPTABLE VIEW v2 AS SELECT * FROM t2; + +INSERT INTO t2 VALUES (NULL,1,2),(NULL,2,3); +SELECT * FROM t1 LEFT JOIN v2 ON i1 = i2 WHERE a < b; +SELECT * FROM t1 LEFT JOIN t2 ON i1 = i2 WHERE a < b; + +drop view v2; +drop table t1,t2; + +--echo # +--echo # Bug mdev-4942: LEFT JOIN with conjunctive +--echo # <non-nullable datetime field> IS NULL in WHERE +--echo # causes an assert failure +--echo # + +CREATE TABLE t1 ( i1 int, d1 date ); +INSERT INTO t1 VALUES (1,'2001-06-26'), (2,'2000-11-16'); + +CREATE TABLE t2 ( i2 int, d2 date NOT NULL ); +INSERT INTO t2 VALUES (3,'2000-03-06'), (4,'2007-09-25'); + +SELECT * FROM t1 LEFT JOIN t2 ON i1 = i2 WHERE d1 IS NULL AND d2 IS NULL; + +DROP TABLE t1,t2; + +--echo # +--echo # Bug mdev-4952: LEFT JOIN with disjunctive +--echo # <non-nullable datetime field> IS NULL in WHERE +--echo # causes an assert failure +--echo # + +CREATE TABLE t1 (a1 int, b1 int NOT NULL) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1, 10), (2, 11); + +CREATE TABLE t2 (dt datetime NOT NULL, a2 int, b2 int) ENGINE=MyISAM; +INSERT INTO t2 VALUES +('2006-10-08 09:34:54', 1, 100), ('2001-01-19 01:04:43', 2, 200); + +SELECT * FROM t1 LEFT JOIN t2 ON a1 = a2 + WHERE ( dt IS NULL OR FALSE ) AND b2 IS NULL; + +DROP TABLE t1,t2; + +--echo # +--echo # Bug mdev-4962: nested outer join with +--echo # <non-nullable datetime field> IS NULL in WHERE +--echo # causes an assert failure +--echo # + +CREATE TABLE t1 (i1 int) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1),(2); + +CREATE TABLE t2 (i2 int) ENGINE=MyISAM; +INSERT INTO t2 VALUES (10),(20); + +CREATE TABLE t3 (i3 int, d3 datetime NOT NULL) ENGINE=MyISAM; +INSERT INTO t3 VALUES (8,'2008-12-04 17:53:42'),(9,'2012-12-21 12:12:12'); + +SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON i2 = i3 ON i1 = i3 + WHERE d3 IS NULL; +EXPLAIN EXTENDED +SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON i2 = i3 ON i1 = i3 + WHERE d3 IS NULL; + +DROP TABLE t1,t2,t3; SET optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/t/myisam_optimize.test b/mysql-test/t/myisam_optimize.test new file mode 100644 index 00000000000..2d630f7dbd0 --- /dev/null +++ b/mysql-test/t/myisam_optimize.test @@ -0,0 +1,47 @@ +# +# test OPTIMIZE and a concurrent SELECT of a MyISAM table +# +source include/have_debug_sync.inc; + +call mtr.add_suppression(" marked as crashed and should be repaired"); + +create table t1 (a int auto_increment primary key, b text); +insert t1 (b) select repeat("A", 100); +insert t1 (b) select repeat("B", 200) from t1; +insert t1 (b) select repeat("C", 300) from t1; + +delete from t1 where a < 3; +explain select a, left(b,10) from t1 order by a; +select left(b,10), a from t1 order by a; + +set debug_sync='myisam_before_repair_by_sort wait_for go'; +send optimize table t1; + +connect (con1,localhost,root,,); +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state like "%myisam_before_repair_by_sort%"; +--source include/wait_condition.inc + +send select a, left(b,10) from t1 order by a; + +connect (con2,localhost,root,,); +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table metadata lock"; +--source include/wait_condition.inc + +set debug_sync='now signal go'; + +connection default; +reap; + +connection con1; +reap; + +disconnect con1; +disconnect con2; +connection default; + +drop table t1; + diff --git a/mysql-test/t/mysqlbinlog.test b/mysql-test/t/mysqlbinlog.test index d492af92cb2..c90feb9194d 100644 --- a/mysql-test/t/mysqlbinlog.test +++ b/mysql-test/t/mysqlbinlog.test @@ -598,6 +598,7 @@ eval SET GLOBAL SERVER_ID = $old_server_id; --echo # MDEV-4645: Incorrect reads of frozen binlog events; --echo # FDE corrupted in relay log --echo # +--let TZ=Europe/Helsinki --exec $MYSQL_BINLOG --hexdump std_data/mdev-4645-binlog_checksum.binlog --exec $MYSQL_BINLOG --hexdump std_data/mdev-4645-binlog_group_id.binlog --exec $MYSQL_BINLOG --hexdump std_data/mdev-4645-binlog_group_id_checksum.binlog diff --git a/mysql-test/t/mysqld--help.test b/mysql-test/t/mysqld--help.test index 571a097c3f7..aacf3affe10 100644 --- a/mysql-test/t/mysqld--help.test +++ b/mysql-test/t/mysqld--help.test @@ -26,7 +26,7 @@ perl; @plugins=qw/innodb ndb archive blackhole federated partition ndbcluster feedback debug temp-pool ssl des-key-file xtradb sequence thread-concurrency super-large-pages mutex-deadlock-detector - null-audit aria pbxt oqgraph sphinx thread-handling + null-audit aria oqgraph sphinx thread-handling test-sql-discovery rpl-semi-sync query-cache-info query-response-time locales/; diff --git a/mysql-test/t/mysqldump.test b/mysql-test/t/mysqldump.test index eaa2e7a8840..5af27607483 100644 --- a/mysql-test/t/mysqldump.test +++ b/mysql-test/t/mysqldump.test @@ -36,7 +36,7 @@ drop view if exists v1, v2, v3; CREATE TABLE t1(a INT, KEY (a)) KEY_BLOCK_SIZE=1024; INSERT INTO t1 VALUES (1), (2); ---exec $MYSQL_DUMP --skip-create --skip-comments -X test t1 +--exec $MYSQL_DUMP --skip-create-options --skip-comments -X test t1 DROP TABLE t1; --echo # @@ -79,14 +79,14 @@ SET SQL_MODE=@OLD_SQL_MODE; # check how mysqldump make quoting --exec $MYSQL_DUMP --compact test t1 ---exec $MYSQL_DUMP --compact --skip-create test t1 ---exec $MYSQL_DUMP --skip-create --skip-comments test t1 +--exec $MYSQL_DUMP --compact --skip-create-options test t1 +--exec $MYSQL_DUMP --skip-create-options --skip-comments test t1 --exec $MYSQL_DUMP --skip-opt --extended-insert --skip-comments test t1 DROP TABLE t1; CREATE TABLE t1(a int, b text, c varchar(3)); INSERT INTO t1 VALUES (1, "test", "tes"), (2, "TEST", "TES"); ---exec $MYSQL_DUMP --skip-create --compact -X test t1 +--exec $MYSQL_DUMP --skip-create-options --compact -X test t1 DROP TABLE t1; --echo # @@ -95,7 +95,7 @@ DROP TABLE t1; CREATE TABLE t1 (`a"b"` char(2)); INSERT INTO t1 VALUES ("1\""), ("\"2"); ---exec $MYSQL_DUMP --compact --skip-create -X test t1 +--exec $MYSQL_DUMP --compact --skip-create-options -X test t1 DROP TABLE t1; --echo # @@ -582,8 +582,8 @@ INSERT INTO t1 VALUES (1), (2); INSERT INTO t2 VALUES (1), (2); --exec $MYSQL_DUMP --skip-comments --no-data mysqldump_test_db --exec $MYSQL_DUMP --skip-comments --no-data mysqldump_test_db t1 t2 ---exec $MYSQL_DUMP --skip-comments --skip-create --xml --no-data mysqldump_test_db ---exec $MYSQL_DUMP --skip-comments --skip-create --xml --no-data mysqldump_test_db t1 t2 +--exec $MYSQL_DUMP --skip-comments --skip-create-options --xml --no-data mysqldump_test_db +--exec $MYSQL_DUMP --skip-comments --skip-create-options --xml --no-data mysqldump_test_db t1 t2 DROP TABLE t1, t2; DROP DATABASE mysqldump_test_db; @@ -1634,7 +1634,7 @@ drop database mysqldump_test_db; CREATE TABLE t1 (c1 INT, c2 LONGBLOB); INSERT INTO t1 SET c1=11, c2=REPEAT('q',509); ---exec $MYSQL_DUMP --skip-create --compact --hex-blob test t1 +--exec $MYSQL_DUMP --skip-create-options --compact --hex-blob test t1 DROP TABLE t1; --echo # @@ -2216,7 +2216,7 @@ DROP TABLE t1, t2; --echo # CREATE TABLE `comment_table` (i INT COMMENT 'FIELD COMMENT') COMMENT = 'TABLE COMMENT'; ---exec $MYSQL_DUMP --compact --skip-create --xml test +--exec $MYSQL_DUMP --compact --skip-create-options --xml test DROP TABLE `comment_table`; --echo # @@ -2385,7 +2385,7 @@ CREATE TABLE b12809202_db.t2 (c1 INT); INSERT INTO b12809202_db.t1 VALUES (1), (2), (3); INSERT INTO b12809202_db.t2 VALUES (1), (2), (3); ---echo # Starting mysqldump with --single-transaction & --flush-log options.. +--echo # Starting mysqldump with --single-transaction & --flush-logs options.. --echo # Note : In the following dump the transaction --echo # should start only after the logs are --echo # flushed, as 'flush logs' causes implicit @@ -2397,7 +2397,7 @@ INSERT INTO b12809202_db.t2 VALUES (1), (2), (3); # mixing of normal (stdout) and --verbose (stderr) output will happen in random # order depending on stdio internal buffer size. --replace_regex /-- Server version.*// /-- MySQL dump .*// /-- Dump completed on .*/-- Dump completed/ ---exec $MYSQL_DUMP --verbose --single-transaction --flush-log b12809202_db 2>&1 > $MYSQLTEST_VARDIR/tmp/bug61854.sql +--exec $MYSQL_DUMP --verbose --single-transaction --flush-logs b12809202_db 2>&1 > $MYSQLTEST_VARDIR/tmp/bug61854.sql --echo --echo #### Dump ends here #### diff --git a/mysql-test/t/null.test b/mysql-test/t/null.test index 2878b54c357..4a45240ec68 100644 --- a/mysql-test/t/null.test +++ b/mysql-test/t/null.test @@ -255,3 +255,31 @@ DESCRIBE t2; DROP TABLE t1, t2; --echo # End of 5.0 tests + +--echo # +--echo # MDEV-4895 Valgrind warnings (Conditional jump or move depends on uninitialised value) in Field_datetime::get_date on GREATEST(..) IS NULL +--echo # + +CREATE TABLE t1 (dt DATETIME NOT NULL); +INSERT INTO t1 VALUES (NOW()),(NOW()); + +EXPLAIN +SELECT * FROM t1 WHERE concat( dt, '2012-12-21 12:12:12' ) IS NULL; +SELECT * FROM t1 WHERE concat( dt, '2012-12-21 12:12:12' ) IS NULL; + +DROP TABLE t1; +CREATE TABLE t1 (dt INT NOT NULL); +INSERT INTO t1 VALUES (1),(2); +EXPLAIN +SELECT * FROM t1 WHERE concat( dt, '1' ) IS NULL; +SELECT * FROM t1 WHERE concat( dt, '1' ) IS NULL; + +DROP TABLE t1; +CREATE TABLE t1 (dt INT NOT NULL); +INSERT INTO t1 VALUES (1),(2); + +EXPLAIN +SELECT * FROM t1 WHERE NOT (concat( dt, '1' ) IS NOT NULL); +SELECT * FROM t1 WHERE NOT (concat( dt, '1' ) IS NOT NULL); + +DROP TABLE t1; diff --git a/mysql-test/t/openssl_1.test b/mysql-test/t/openssl_1.test index 61e0dcc7197..0c8f81e4712 100644 --- a/mysql-test/t/openssl_1.test +++ b/mysql-test/t/openssl_1.test @@ -196,16 +196,16 @@ CREATE TABLE t1(a int); INSERT INTO t1 VALUES (1), (2); # Run mysqldump ---exec $MYSQL_DUMP --skip-create --skip-comments --ssl-key=$MYSQL_TEST_DIR/std_data/client-key.pem --ssl-cert=$MYSQL_TEST_DIR/std_data/client-cert.pem test t1 +--exec $MYSQL_DUMP --skip-create-options --skip-comments --ssl-key=$MYSQL_TEST_DIR/std_data/client-key.pem --ssl-cert=$MYSQL_TEST_DIR/std_data/client-cert.pem test t1 ---exec $MYSQL_DUMP --skip-create --skip-comments --ssl-ca=$MYSQL_TEST_DIR/std_data/cacert.pem --ssl-key=$MYSQL_TEST_DIR/std_data/client-key.pem --ssl-cert=$MYSQL_TEST_DIR/std_data/client-cert.pem test +--exec $MYSQL_DUMP --skip-create-options --skip-comments --ssl-ca=$MYSQL_TEST_DIR/std_data/cacert.pem --ssl-key=$MYSQL_TEST_DIR/std_data/client-key.pem --ssl-cert=$MYSQL_TEST_DIR/std_data/client-cert.pem test ---exec $MYSQL_DUMP --skip-create --skip-comments --ssl --ssl-ca=$MYSQL_TEST_DIR/std_data/cacert.pem --ssl-key=$MYSQL_TEST_DIR/std_data/client-key.pem --ssl-cert=$MYSQL_TEST_DIR/std_data/client-cert.pem test +--exec $MYSQL_DUMP --skip-create-options --skip-comments --ssl --ssl-ca=$MYSQL_TEST_DIR/std_data/cacert.pem --ssl-key=$MYSQL_TEST_DIR/std_data/client-key.pem --ssl-cert=$MYSQL_TEST_DIR/std_data/client-cert.pem test # With wrong parameters --replace_result $MYSQL_TEST_DIR MYSQL_TEST_DIR mysqldump.exe mysqldump --error 2 ---exec $MYSQL_DUMP --skip-create --skip-comments --ssl --ssl-cert=$MYSQL_TEST_DIR/std_data/client-cert.pem test 2>&1 +--exec $MYSQL_DUMP --skip-create-options --skip-comments --ssl --ssl-cert=$MYSQL_TEST_DIR/std_data/client-cert.pem test 2>&1 DROP TABLE t1; --remove_file $MYSQLTEST_VARDIR/tmp/test.sql diff --git a/mysql-test/t/partition_myisam.test b/mysql-test/t/partition_myisam.test index 49c5d793169..db4b07e5b1f 100644 --- a/mysql-test/t/partition_myisam.test +++ b/mysql-test/t/partition_myisam.test @@ -87,11 +87,9 @@ FLUSH TABLES; CHECK TABLE t1; --error ER_FAILED_READ_FROM_PAR_FILE SELECT * FROM t1; ---echo # Note that it is currently impossible to drop a partitioned table ---echo # without the .par file ---error ER_BAD_TABLE_ERROR +--echo # Note that we will remove the frm file when we detect that +--echo # .par file has been deleted. DROP TABLE t1; ---remove_file $MYSQLD_DATADIR/test/t1.frm --remove_file $MYSQLD_DATADIR/test/t1#P#p0.MYI --remove_file $MYSQLD_DATADIR/test/t1#P#p0.MYD diff --git a/mysql-test/t/plugin.test b/mysql-test/t/plugin.test index 94030670d9f..40d3c2f2f97 100644 --- a/mysql-test/t/plugin.test +++ b/mysql-test/t/plugin.test @@ -175,6 +175,58 @@ SET @@SQL_MODE=@OLD_SQL_MODE; select 1; UNINSTALL PLUGIN example; +--echo # +--echo # Bug #16194302 SUPPORT FOR FLOATING-POINT SYSTEM +--echo # VARIABLES USING THE PLUGIN INTERFACE. +--echo # + +--replace_regex /\.dll/.so/ +eval INSTALL PLUGIN example SONAME 'ha_example'; + +SET GLOBAL example_double_var = -0.1; +SELECT @@GLOBAL.example_double_var; + +SET GLOBAL example_double_var = 0.000001; +SELECT @@GLOBAL.example_double_var; + +SET GLOBAL example_double_var = 0.4; +SELECT @@GLOBAL.example_double_var; + +SET GLOBAL example_double_var = 123.456789; +SELECT @@GLOBAL.example_double_var; + +SET GLOBAL example_double_var = 500; +SELECT @@GLOBAL.example_double_var; + +SET GLOBAL example_double_var = 999.999999; +SELECT @@GLOBAL.example_double_var; + +SET GLOBAL example_double_var = 1000.51; +SELECT @@GLOBAL.example_double_var; + +SET SESSION example_double_thdvar = -0.1; +SELECT @@SESSION.example_double_thdvar; + +SET SESSION example_double_thdvar = 0.000001; +SELECT @@SESSION.example_double_thdvar; + +SET SESSION example_double_thdvar = 0.4; +SELECT @@SESSION.example_double_thdvar; + +SET SESSION example_double_thdvar = 123.456789; +SELECT @@SESSION.example_double_thdvar; + +SET SESSION example_double_thdvar = 500; +SELECT @@SESSION.example_double_thdvar; + +SET SESSION example_double_thdvar = 999.999999; +SELECT @@SESSION.example_double_thdvar; + +SET SESSION example_double_thdvar = 1000.51; +SELECT @@SESSION.example_double_thdvar; + +UNINSTALL PLUGIN example; + # # MDEV-4573 UNINSTALL PLUGIN misleading error message for non-dynamic plugins # diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test index a5ff9cc0096..dc9ca0012fe 100644 --- a/mysql-test/t/range.test +++ b/mysql-test/t/range.test @@ -570,12 +570,12 @@ INSERT INTO t1 VALUES EXPLAIN SELECT s.oxid FROM t1 v, t1 s - WHERE s.oxrootid = 'd8c4177d09f8b11f5.52725521' AND + WHERE v.oxrootid ='d8c4177d09f8b11f5.52725521' AND s.oxleft > v.oxleft AND s.oxleft < v.oxright; SELECT s.oxid FROM t1 v, t1 s - WHERE s.oxrootid = 'd8c4177d09f8b11f5.52725521' AND + WHERE v.oxrootid ='d8c4177d09f8b11f5.52725521' AND s.oxleft > v.oxleft AND s.oxleft < v.oxright; @@ -1481,6 +1481,40 @@ SELECT * FROM t1 IGNORE INDEX(PRIMARY) WHERE F1 BETWEEN 'A ' AND DROP TABLE t1; +--echo # +--echo # mdev-4894: Poor performance with unnecessary +--echo # (bug#70021) 'Range checked for each record' +--echo # + +create table t1( key1 int not null, INDEX i1(key1) ); +insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8); +insert into t1 select key1+8 from t1; +insert into t1 select key1+16 from t1; +insert into t1 select key1+32 from t1; +insert into t1 select key1+64 from t1; +insert into t1 select key1+128 from t1; +insert into t1 select key1+256 from t1; +insert into t1 select key1+512 from t1; + +alter table t1 add key2 int not null, add index i2(key2); +update t1 set key2=key1; +analyze table t1; + +create table t2 (a int); +insert into t2 values (1),(2),(3),(4),(5),(6),(7),(8); +insert into t2 select a+16 from t2; +insert into t2 select a+32 from t2; +insert into t2 select a+64 from t2; + +explain +select count(*) from t2 left join t1 on (t1.key1 < 3 or t1.key1 > 1020) and t1.key2 < 1000; +select count(*) from t2 left join t1 on (t1.key1 < 3 or t1.key1 > 1020) and t1.key2 < 1000; +explain +select count(*) from t2 left join t1 on (t1.key1 < 3 or t1.key1 > 1020) and t1.key2 < t2.a; +select count(*) from t2 left join t1 on (t1.key1 < 3 or t1.key1 > 1020) and t1.key2 < t2.a; + +drop table t1,t2; + --echo End of 5.1 tests --echo # diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test index 80ca862b74e..609a27f3d34 100644 --- a/mysql-test/t/select.test +++ b/mysql-test/t/select.test @@ -4500,7 +4500,7 @@ SELECT * FROM t1 INNER JOIN t2 ON ( c = a ) DROP TABLE t1,t2; --echo # ---echo # Bug mdev-4413: another manifestations of bug mdev-2474 +--echo # Bug mdev-4413: another manifestations of bug mdev-4274 --echo # (valgrind complains) --echo # @@ -4516,6 +4516,95 @@ SELECT * FROM t1, t2 DROP TABLE t1, t2; +--echo # +--echo # Bug mdev-4355: equalities from the result of simplification of OR +--echo # are not propagated to lower AND levels +--echo # + +CREATE TABLE t1 (a INT, b INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1,101),(2,102),(3,103),(4,104),(5,11); + +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE (1 != 1 OR a = 5) AND (b != 1 OR a = 1); +SELECT * FROM t1 WHERE (1 != 1 OR a = 5) AND (b != 1 OR a = 1); + +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE (b != 1 OR a = 1) AND (1 != 1 OR a = 5); +SELECT * FROM t1 WHERE (b != 1 OR a = 1) AND (1 != 1 OR a = 5); + +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE (b != 1 OR a = 1) AND (a = 5 OR 1 != 1); +SELECT * FROM t1 WHERE (b != 1 OR a = 1) AND (a = 5 OR 1 != 1); + +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE (b = 1 OR a = 1) AND (b = 5 AND a = 5 OR 1 != 1); +SELECT * FROM t1 WHERE (b = 1 OR a = 1) AND (b = 5 AND a = 5 OR 1 != 1); + +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE (b = 1 OR a = 5) AND (b = 5 AND a = 5 OR 1 != 1); +SELECT * FROM t1 WHERE (b = 1 OR a = 5) AND (b = 5 AND a = 5 OR 1 != 1); + +DROP TABLE t1; + +--echo # +--echo # Bug mdev-4418: impossible multiple equality in OR formula +--echo # after row substitution +--echo # + +CREATE TABLE t1 (a int, b varchar(1)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (0,'j'), (8,'v'); + +CREATE TABLE t2 (c varchar(1), d varchar(1)) ENGINE=MyISAM; +INSERT INTO t2 VALUES ('k','k'); + +EXPLAIN EXTENDED +SELECT * FROM t1, t2 WHERE c=b AND (1=2 OR ((b='h' OR a=136) AND d=b)); +SELECT * FROM t1, t2 WHERE c=b AND (1=2 OR ((b='h' OR a=136) AND d=b)); + +DROP TABLE t1,t2; + +--echo # +--echo # Bug mdev-4944: range conditition in OR formula with fields +--echo # belonging to multiple equalities +--echo # + +CREATE TABLE t1 (i1 int, j1 int) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1,8); + +CREATE TABLE t2 (i2 int, INDEX idx (i2)) ENGINE=MyISAM; +INSERT INTO t2 VALUES (8), (9); + +EXPLAIN EXTENDED +SELECT * FROM t1, t2 + WHERE i1 = i2 AND ( FALSE OR ( j1 > 27 AND j1 < 100 OR j1 <= 3 ) AND j1 = i2 ); +SELECT * FROM t1, t2 + WHERE i1 = i2 AND ( FALSE OR ( j1 > 27 AND j1 < 100 OR j1 <= 3 ) AND j1 = i2 ); + +DROP TABLE t1,t2; + +--echo # +--echo # Bug mdev-4971: equality propagation after merging degenerate +--echo # disjunction into embedding AND level +--echo # + +CREATE TABLE t1 (pk1 int, a1 int, b1 int, PRIMARY KEY(pk1)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1,10,100), (2,20,200) ; + +CREATE TABLE t2 (pk2 int, a2 int, PRIMARY KEY(pk2)) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1,1); + +SELECT * FROM t1, t2 + WHERE a1 = pk2 AND ( ( b1 = 6 OR a2 > 4 ) AND pk2 = a2 OR pk1 IS NULL ); +EXPLAIN EXTENDED +SELECT * FROM t1, t2 + WHERE a1 = pk2 AND ( ( b1 = 6 OR a2 > 4 ) AND pk2 = a2 OR pk1 IS NULL ); + +INSERT INTO t1 VALUES (3,1,6); +SELECT * FROM t1, t2 + WHERE a1 = pk2 AND ( ( b1 = 6 OR a2 > 4 ) AND pk2 = a2 OR pk1 IS NULL ); + +DROP TABLE t1,t2; + --echo End of 5.3 tests --echo # diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test index 0fb3474f877..b8d87a2cf60 100644 --- a/mysql-test/t/sp.test +++ b/mysql-test/t/sp.test @@ -9271,4 +9271,35 @@ drop procedure p2; drop table t1; +--echo # +--echo # MDEV-4978 - Server cursor is broken with blobs in the select list, +--echo # ORDER BY does not work +--echo # +CREATE TABLE t1(a INT, b BLOB); +INSERT INTO t1 VALUES(1,REPEAT('a',4835)),(2,'b'),(3,'c'),(4,'d'),(5,REPEAT('e',805)),(6,'f'); + +DELIMITER |; +CREATE PROCEDURE p1() +BEGIN + DECLARE done INT DEFAULT 0; + DECLARE v1 INT; + DECLARE v2 BLOB; + DECLARE c1 CURSOR FOR SELECT * FROM t1 ORDER BY a; + DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1; + OPEN c1; + REPEAT + FETCH c1 INTO v1, v2; + IF NOT done THEN + SELECT v1; + END IF; + UNTIL done END REPEAT; + CLOSE c1; +END| +DELIMITER ;| + +CALL p1; + +DROP PROCEDURE p1; +DROP TABLE t1; + --echo # End of 5.5 test diff --git a/mysql-test/t/subselect2.test b/mysql-test/t/subselect2.test index 68894ad18cb..b3c1322184d 100644 --- a/mysql-test/t/subselect2.test +++ b/mysql-test/t/subselect2.test @@ -260,6 +260,29 @@ DROP TABLE t1,t2,t3; set optimizer_switch=@tmp_mdev567; --echo # +--echo # MDEV-4996: degenerate OR formula in WHERE of a subquery +--echo # + +CREATE TABLE t1 (a int, c1 varchar(1)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (2,'x'), (8,'d'); + +CREATE TABLE t2 (m int, n int, c2 varchar(1)) ENGINE=MyISAM; +INSERT INTO t2 VALUES (0, 5, 'x'), (1, 4,'p'); + +SELECT * FROM t1 WHERE c1 NOT IN ( + SELECT t2a.c2 FROM t2 AS t2a, t2 AS t2b, t2 AS t2c + WHERE t2c.c2 = t2b.c2 AND ( t2a.m = t2b.n OR 0 ) AND + ( t2b.m != a OR t2b.m = t2a.m )); + +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE c1 NOT IN ( + SELECT t2a.c2 FROM t2 AS t2a, t2 AS t2b, t2 AS t2c + WHERE t2c.c2 = t2b.c2 AND ( t2a.m = t2b.n OR 0 ) AND + ( t2b.m != a OR t2b.m = t2a.m )); + +DROP TABLE t1,t2; + +--echo # --echo # MDEV-614, also MDEV-536, also LP:1050806: --echo # different result for a query using subquery between 5.5.25 and 5.5.27 --echo # @@ -315,6 +338,26 @@ set optimizer_switch=@tmp_mdev614; DROP TABLE t1; +--echo # +--echo # MDEV-4420: non-expensive single-value subquery used as +--echo # used as an access key to join a table +--echo # + +create table t1 (a varchar(3)); +insert into t1 values ('USA'), ('FRA'); +create table t2 select * from t1; +insert into t2 values ('RUS'); +create table t3 select * from t2; +create index idx on t3(a); + +explain extended +select * from t1, t2 left join t3 on ( t2.a = t3.a ) +where t1.a = t2.a and ( t1.a = ( select min(a) from t1 ) or 0 ); + +select * from t1, t2 left join t3 on ( t2.a = t3.a ) +where t1.a = t2.a and ( t1.a = ( select min(a) from t1 ) or 0 ); + +drop table t1,t2,t3; set optimizer_switch=@subselect2_test_tmp; diff --git a/mysql-test/t/subselect_sj_mat.test b/mysql-test/t/subselect_sj_mat.test index 711f2f4f9b6..e9226b8884f 100644 --- a/mysql-test/t/subselect_sj_mat.test +++ b/mysql-test/t/subselect_sj_mat.test @@ -1700,3 +1700,29 @@ drop table t1,t2; set optimizer_switch=@subselect_sj_mat_tmp; set join_cache_level=@save_join_cache_level; +--echo # +--echo # MDEV-4908: Assertion `((Item_cond *) cond)->functype() == +--echo # ((Item_cond *) new_item)->functype()' fails on a query with +--echo # IN and equal conditions, AND/OR, materialization+semijoin +--echo # + + +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch = 'materialization=on,semijoin=on'; + +CREATE TABLE t1 (pk INT, a INT, b INT, PRIMARY KEY(pk)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1,3,5),(2,4,6); +SELECT * FROM t1 WHERE 8 IN ( SELECT MIN(pk) FROM t1 ) AND ( pk = a OR pk = b ); + +drop table t1; +SET optimizer_switch=@save_optimizer_switch; + +--echo # +--echo # MDEV-5011: ERROR Plugin 'MEMORY' has ref_count=1 after shutdown for SJM queries +--echo # +CREATE TABLE t1 (pk INT, a INT, b INT, PRIMARY KEY(pk)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1,3,5),(2,4,6); +SELECT * FROM t1 WHERE 8 IN (SELECT MIN(pk) FROM t1) AND (pk = a OR pk = b); +DROP TABLE t1; + +--echo # End of 5.3 tests diff --git a/mysql-test/t/table_elim.test b/mysql-test/t/table_elim.test index 2cca6f501f0..357953290c4 100644 --- a/mysql-test/t/table_elim.test +++ b/mysql-test/t/table_elim.test @@ -561,4 +561,21 @@ EXPLAIN EXTENDED DROP TABLE t1,t2; +--echo # +--echo # MDEV-4840: Wrong result (missing rows) on LEFT JOIN with InnoDB tables +--echo # +CREATE TABLE t1 (alpha3 VARCHAR(3)); +INSERT INTO t1 VALUES ('USA'),('CAN'); + +CREATE TABLE t2 ( t3_code VARCHAR(3), name VARCHAR(64)); +INSERT INTO t2 VALUES ('USA','Austin'),('USA','Boston'); + +CREATE TABLE t3 ( code VARCHAR(3), name VARCHAR(64), PRIMARY KEY (code), UNIQUE KEY (name)); +INSERT INTO t3 VALUES ('CAN','Canada'),('USA','United States'); + +SELECT * FROM t1 LEFT JOIN ( t2 LEFT JOIN t3 ON t2.t3_code = t3.code ) ON t1.alpha3 = t3.code; +SELECT t1.alpha3 FROM t1 LEFT JOIN ( t2 LEFT JOIN t3 ON t2.t3_code = t3.code ) ON t1.alpha3 = t3.code; + +DROP TABLE t1, t2, t3; + SET optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/t/type_date.test b/mysql-test/t/type_date.test index af8681abff3..6cfd9711c3e 100644 --- a/mysql-test/t/type_date.test +++ b/mysql-test/t/type_date.test @@ -300,6 +300,28 @@ drop table t1; SELECT CONVERT_TZ(GREATEST(DATE('2021-00-00'),DATE('2022-00-00')),'+00:00','+7:5'); --echo # +--echo # MDEV-4804 Date comparing false result +--echo # +SET @h0="20111107"; +SET @h1="0"; +SET @@timestamp=UNIX_TIMESTAMP('2013-08-19 20:30:00'); +SELECT + COALESCE(DATE(@h0),DATE("1901-01-01")) AS h0d, + COALESCE(DATE(@h1),DATE(NOW())) AS h1d, + COALESCE(DATE(@h0),DATE("1901-01-01"))>COALESCE(DATE(@h1),DATE(NOW())) AS compare_h0_gt_h1; +SELECT + DATE('20011107'), + DATE('0'), + COALESCE(DATE('0'),CURRENT_DATE) AS d1, + DATE('20011107')>COALESCE(DATE('0'),CURRENT_DATE) AS cmp; +SELECT + DATE('20011107'), + DATE('0'), + IFNULL(DATE('0'),CURRENT_DATE) AS d1, + DATE('20011107')>IFNULL(DATE('0'),CURRENT_DATE) AS cmp; +SET @@timestamp=DEFAULT; + +--echo # --echo # End of 5.3 tests --echo # diff --git a/mysql-test/t/view_grant.test b/mysql-test/t/view_grant.test index 663501a0a88..847153d19f4 100644 --- a/mysql-test/t/view_grant.test +++ b/mysql-test/t/view_grant.test @@ -1595,6 +1595,12 @@ disconnect root; connection default; +# +# MDEV-4951 drop user leaves privileges +# +#verify that no privileges were left after the above +select * from information_schema.table_privileges; + --echo End of 5.0 tests. |