diff options
| author | Sergei Golubchik <serg@mariadb.org> | 2015-09-25 10:24:14 +0200 |
|---|---|---|
| committer | Sergei Golubchik <serg@mariadb.org> | 2015-09-25 10:24:14 +0200 |
| commit | 9554342d16a6567329cf6269129f9db4ea0be8c7 (patch) | |
| tree | 09b948047b1b95ea4d964857cc09a33ba8fa835f /mysql-test/r | |
| parent | 5448df084b885ab744711888037bcc83bc2545f0 (diff) | |
| parent | 4070ce00dc95e320c68e9d9b623893b034fb913e (diff) | |
| download | mariadb-git-9554342d16a6567329cf6269129f9db4ea0be8c7.tar.gz | |
Merge branch '10.1' into pull-request-97
Merge downstream Debian packaging (MDEV-6284)
Diffstat (limited to 'mysql-test/r')
70 files changed, 2611 insertions, 157 deletions
diff --git a/mysql-test/r/analyze_format_json.result b/mysql-test/r/analyze_format_json.result index f77db650866..14ab125b9ac 100644 --- a/mysql-test/r/analyze_format_json.result +++ b/mysql-test/r/analyze_format_json.result @@ -467,3 +467,116 @@ ANALYZE } } drop table t0, t1; +# +# MDEV-7970: EXPLAIN FORMAT=JSON does not print HAVING +# +create table t0(a int); +insert into t0 values (0),(1),(2),(3); +create table t1(a int); +insert into t1 select A.a + B.a* 10 + C.a * 100 from t0 A, t0 B, t0 C; +create table t2 ( +a int, +b int, +key (a) +); +insert into t2 select A.a*1000 + B.a, A.a*1000 + B.a from t0 A, t1 B; +# normal HAVING +analyze format=json select a, max(b) as TOP from t2 group by a having TOP > a; +ANALYZE +{ + "query_block": { + "select_id": 1, + "r_loops": 1, + "volatile parameter": "REPLACED", + "having_condition": "(TOP > a)", + "filesort": { + "r_loops": 1, + "volatile parameter": "REPLACED", + "r_used_priority_queue": false, + "r_output_rows": 0, + "volatile parameter": "REPLACED", + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "r_loops": 1, + "rows": 256, + "r_rows": 256, + "volatile parameter": "REPLACED", + "filtered": 100, + "r_filtered": 100 + } + } + } + } +} +# HAVING is always TRUE (not printed) +analyze format=json select a, max(b) as TOP from t2 group by a having 1<>2; +ANALYZE +{ + "query_block": { + "select_id": 1, + "r_loops": 1, + "volatile parameter": "REPLACED", + "filesort": { + "r_loops": 1, + "volatile parameter": "REPLACED", + "r_used_priority_queue": false, + "r_output_rows": 256, + "volatile parameter": "REPLACED", + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "r_loops": 1, + "rows": 256, + "r_rows": 256, + "volatile parameter": "REPLACED", + "filtered": 100, + "r_filtered": 100 + } + } + } + } +} +# HAVING is always FALSE (intercepted by message) +analyze format=json select a, max(b) as TOP from t2 group by a having 1=2; +ANALYZE +{ + "query_block": { + "select_id": 1, + "table": { + "message": "Impossible HAVING" + } + } +} +# HAVING is absent +analyze format=json select a, max(b) as TOP from t2 group by a; +ANALYZE +{ + "query_block": { + "select_id": 1, + "r_loops": 1, + "volatile parameter": "REPLACED", + "filesort": { + "r_loops": 1, + "volatile parameter": "REPLACED", + "r_used_priority_queue": false, + "r_output_rows": 256, + "volatile parameter": "REPLACED", + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "r_loops": 1, + "rows": 256, + "r_rows": 256, + "volatile parameter": "REPLACED", + "filtered": 100, + "r_filtered": 100 + } + } + } + } +} +drop table t0, t1, t2; diff --git a/mysql-test/r/bug13633383.result b/mysql-test/r/bug13633383.result index 3b533f89df2..a6f5bab5260 100644 --- a/mysql-test/r/bug13633383.result +++ b/mysql-test/r/bug13633383.result @@ -49,5 +49,66 @@ col435 0.00000000000000000 0.00000000000000000 0.00000000000000000 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '\x00\x00\x00\x00\x01\x01\x00\x00\x00\x00\x00\x00\x00\x00k\xBF\xC0\x00\x00\x00\x00\x00j\xF8@' +Warning 1292 Truncated incorrect DOUBLE value: '\x00\x00\x00\x00\x01\x01\x00\x00\x006\x0E\xFD\xB9PVh,;b\xC2\xBA\xF6$\xEE\xB0' +Warning 1292 Truncated incorrect DOUBLE value: '\x00\x00\x00\x00\x01\x01\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\xC0\xAE\xDB\xC0' +Warning 1292 Truncated incorrect DOUBLE value: '\x00\x00\x00\x00\x01\x01\x00\x00\x00\x00\x00\x00\x00\x00j\xF8@\x00\x00\x00\x00\x00\x00\x00\x00' +Warning 1292 Truncated incorrect DOUBLE value: '\x00\x00\x00\x00\x01\x01\x00\x00\x00\x00\x00\x00\x00\x00k\xBF\xC0\x00\x00\x00\x00\x00j\xF8@' +Warning 1292 Truncated incorrect DOUBLE value: '\x00\x00\x00\x00\x01\x01\x00\x00\x006\x0E\xFD\xB9PVh,;b\xC2\xBA\xF6$\xEE\xB0' +Warning 1292 Truncated incorrect DOUBLE value: '\x00\x00\x00\x00\x01\x01\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\xC0\xAE\xDB\xC0' +Warning 1292 Truncated incorrect DOUBLE value: '\x00\x00\x00\x00\x01\x01\x00\x00\x00\x00\x00\x00\x00\x00j\xF8@\x00\x00\x00\x00\x00\x00\x00\x00' +Warning 1292 Truncated incorrect DOUBLE value: '\x00\x00\x00\x00\x01\x01\x00\x00\x00\x00\x00\x00\x00\x00k\xBF\xC0\x00\x00\x00\x00\x00j\xF8@' +Warning 1292 Truncated incorrect DOUBLE value: '\x00\x00\x00\x00\x01\x01\x00\x00\x006\x0E\xFD\xB9PVh,;b\xC2\xBA\xF6$\xEE\xB0' +Warning 1292 Truncated incorrect DOUBLE value: '\x00\x00\x00\x00\x01\x01\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\xC0\xAE\xDB\xC0' +Warning 1292 Truncated incorrect DOUBLE value: '\x00\x00\x00\x00\x01\x01\x00\x00\x00\x00\x00\x00\x00\x00j\xF8@\x00\x00\x00\x00\x00\x00\x00\x00' +Warning 1292 Truncated incorrect DOUBLE value: '\x00\x00\x00\x00\x01\x01\x00\x00\x00\x00\x00\x00\x00\x00k\xBF\xC0\x00\x00\x00\x00\x00j\xF8@' +Warning 1292 Truncated incorrect DOUBLE value: '\x00\x00\x00\x00\x01\x01\x00\x00\x006\x0E\xFD\xB9PVh,;b\xC2\xBA\xF6$\xEE\xB0' +Warning 1292 Truncated incorrect DOUBLE value: '\x00\x00\x00\x00\x01\x01\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\xC0\xAE\xDB\xC0' +Warning 1292 Truncated incorrect DOUBLE value: '\x00\x00\x00\x00\x01\x01\x00\x00\x00\x00\x00\x00\x00\x00j\xF8@\x00\x00\x00\x00\x00\x00\x00\x00' +Warning 1292 Truncated incorrect DOUBLE value: '\x00\x00\x00\x00\x01\x01\x00\x00\x00\x00\x00\x00\x00\x00k\xBF\xC0\x00\x00\x00\x00\x00j\xF8@' +Warning 1292 Truncated incorrect DOUBLE value: '\x00\x00\x00\x00\x01\x01\x00\x00\x006\x0E\xFD\xB9PVh,;b\xC2\xBA\xF6$\xEE\xB0' +Warning 1292 Truncated incorrect DOUBLE value: '\x00\x00\x00\x00\x01\x01\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\xC0\xAE\xDB\xC0' +Warning 1292 Truncated incorrect DOUBLE value: '\x00\x00\x00\x00\x01\x01\x00\x00\x00\x00\x00\x00\x00\x00j\xF8@\x00\x00\x00\x00\x00\x00\x00\x00' +Warning 1292 Truncated incorrect DOUBLE value: '\x00\x00\x00\x00\x01\x01\x00\x00\x00\x00\x00\x00\x00\x00k\xBF\xC0\x00\x00\x00\x00\x00j\xF8@' +Warning 1292 Truncated incorrect DOUBLE value: '\x00\x00\x00\x00\x01\x01\x00\x00\x006\x0E\xFD\xB9PVh,;b\xC2\xBA\xF6$\xEE\xB0' +Warning 1292 Truncated incorrect DOUBLE value: '\x00\x00\x00\x00\x01\x01\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\xC0\xAE\xDB\xC0' +Warning 1292 Truncated incorrect DOUBLE value: '\x00\x00\x00\x00\x01\x01\x00\x00\x00\x00\x00\x00\x00\x00j\xF8@\x00\x00\x00\x00\x00\x00\x00\x00' +Warning 1292 Truncated incorrect DOUBLE value: '\x00\x00\x00\x00\x01\x01\x00\x00\x00\x00\x00\x00\x00\x00k\xBF\xC0\x00\x00\x00\x00\x00j\xF8@' +Warning 1292 Truncated incorrect DOUBLE value: '\x00\x00\x00\x00\x01\x01\x00\x00\x006\x0E\xFD\xB9PVh,;b\xC2\xBA\xF6$\xEE\xB0' +Warning 1292 Truncated incorrect DOUBLE value: '\x00\x00\x00\x00\x01\x01\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\xC0\xAE\xDB\xC0' +Warning 1292 Truncated incorrect DOUBLE value: '\x00\x00\x00\x00\x01\x01\x00\x00\x00\x00\x00\x00\x00\x00j\xF8@\x00\x00\x00\x00\x00\x00\x00\x00' +Warning 1292 Truncated incorrect DOUBLE value: '\x00\x00\x00\x00\x01\x01\x00\x00\x00\x00\x00\x00\x00\x00k\xBF\xC0\x00\x00\x00\x00\x00j\xF8@' +Warning 1292 Truncated incorrect DOUBLE value: '\x00\x00\x00\x00\x01\x01\x00\x00\x006\x0E\xFD\xB9PVh,;b\xC2\xBA\xF6$\xEE\xB0' +Warning 1292 Truncated incorrect DOUBLE value: '\x00\x00\x00\x00\x01\x01\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\xC0\xAE\xDB\xC0' +Warning 1292 Truncated incorrect DOUBLE value: '\x00\x00\x00\x00\x01\x01\x00\x00\x00\x00\x00\x00\x00\x00j\xF8@\x00\x00\x00\x00\x00\x00\x00\x00' +Warning 1292 Truncated incorrect DOUBLE value: '\x00\x00\x00\x00\x01\x01\x00\x00\x00\x00\x00\x00\x00\x00k\xBF\xC0\x00\x00\x00\x00\x00j\xF8@' +Warning 1292 Truncated incorrect DOUBLE value: '\x00\x00\x00\x00\x01\x01\x00\x00\x006\x0E\xFD\xB9PVh,;b\xC2\xBA\xF6$\xEE\xB0' +Warning 1292 Truncated incorrect DOUBLE value: '\x00\x00\x00\x00\x01\x01\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\xC0\xAE\xDB\xC0' +Warning 1292 Truncated incorrect DOUBLE value: '\x00\x00\x00\x00\x01\x01\x00\x00\x00\x00\x00\x00\x00\x00j\xF8@\x00\x00\x00\x00\x00\x00\x00\x00' +Warning 1292 Truncated incorrect DOUBLE value: '\x00\x00\x00\x00\x01\x01\x00\x00\x00\x00\x00\x00\x00\x00k\xBF\xC0\x00\x00\x00\x00\x00j\xF8@' +Warning 1292 Truncated incorrect DOUBLE value: '\x00\x00\x00\x00\x01\x01\x00\x00\x006\x0E\xFD\xB9PVh,;b\xC2\xBA\xF6$\xEE\xB0' +Warning 1292 Truncated incorrect DOUBLE value: '\x00\x00\x00\x00\x01\x01\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\xC0\xAE\xDB\xC0' +Warning 1292 Truncated incorrect DOUBLE value: '\x00\x00\x00\x00\x01\x01\x00\x00\x00\x00\x00\x00\x00\x00j\xF8@\x00\x00\x00\x00\x00\x00\x00\x00' +Warning 1292 Truncated incorrect DOUBLE value: '\x00\x00\x00\x00\x01\x01\x00\x00\x00\x00\x00\x00\x00\x00k\xBF\xC0\x00\x00\x00\x00\x00j\xF8@' +Warning 1292 Truncated incorrect DOUBLE value: '\x00\x00\x00\x00\x01\x01\x00\x00\x006\x0E\xFD\xB9PVh,;b\xC2\xBA\xF6$\xEE\xB0' +Warning 1292 Truncated incorrect DOUBLE value: '\x00\x00\x00\x00\x01\x01\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\xC0\xAE\xDB\xC0' +Warning 1292 Truncated incorrect DOUBLE value: '\x00\x00\x00\x00\x01\x01\x00\x00\x00\x00\x00\x00\x00\x00j\xF8@\x00\x00\x00\x00\x00\x00\x00\x00' +Warning 1292 Truncated incorrect DOUBLE value: '\x00\x00\x00\x00\x01\x01\x00\x00\x00\x00\x00\x00\x00\x00k\xBF\xC0\x00\x00\x00\x00\x00j\xF8@' +Warning 1292 Truncated incorrect DOUBLE value: '\x00\x00\x00\x00\x01\x01\x00\x00\x006\x0E\xFD\xB9PVh,;b\xC2\xBA\xF6$\xEE\xB0' +Warning 1292 Truncated incorrect DOUBLE value: '\x00\x00\x00\x00\x01\x01\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\xC0\xAE\xDB\xC0' +Warning 1292 Truncated incorrect DOUBLE value: '\x00\x00\x00\x00\x01\x01\x00\x00\x00\x00\x00\x00\x00\x00j\xF8@\x00\x00\x00\x00\x00\x00\x00\x00' +Warning 1292 Truncated incorrect DOUBLE value: '\x00\x00\x00\x00\x01\x01\x00\x00\x00\x00\x00\x00\x00\x00k\xBF\xC0\x00\x00\x00\x00\x00j\xF8@' +Warning 1292 Truncated incorrect DOUBLE value: '\x00\x00\x00\x00\x01\x01\x00\x00\x006\x0E\xFD\xB9PVh,;b\xC2\xBA\xF6$\xEE\xB0' +Warning 1292 Truncated incorrect DOUBLE value: '\x00\x00\x00\x00\x01\x01\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\xC0\xAE\xDB\xC0' +Warning 1292 Truncated incorrect DOUBLE value: '\x00\x00\x00\x00\x01\x01\x00\x00\x00\x00\x00\x00\x00\x00j\xF8@\x00\x00\x00\x00\x00\x00\x00\x00' +Warning 1292 Truncated incorrect DOUBLE value: '\x00\x00\x00\x00\x01\x01\x00\x00\x00\x00\x00\x00\x00\x00k\xBF\xC0\x00\x00\x00\x00\x00j\xF8@' +Warning 1292 Truncated incorrect DOUBLE value: '\x00\x00\x00\x00\x01\x01\x00\x00\x006\x0E\xFD\xB9PVh,;b\xC2\xBA\xF6$\xEE\xB0' +Warning 1292 Truncated incorrect DOUBLE value: '\x00\x00\x00\x00\x01\x01\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\xC0\xAE\xDB\xC0' +Warning 1292 Truncated incorrect DOUBLE value: '\x00\x00\x00\x00\x01\x01\x00\x00\x00\x00\x00\x00\x00\x00j\xF8@\x00\x00\x00\x00\x00\x00\x00\x00' +Warning 1292 Truncated incorrect DOUBLE value: '\x00\x00\x00\x00\x01\x01\x00\x00\x00\x00\x00\x00\x00\x00k\xBF\xC0\x00\x00\x00\x00\x00j\xF8@' +Warning 1292 Truncated incorrect DOUBLE value: '\x00\x00\x00\x00\x01\x01\x00\x00\x006\x0E\xFD\xB9PVh,;b\xC2\xBA\xF6$\xEE\xB0' +Warning 1292 Truncated incorrect DOUBLE value: '\x00\x00\x00\x00\x01\x01\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\xC0\xAE\xDB\xC0' +Warning 1292 Truncated incorrect DOUBLE value: '\x00\x00\x00\x00\x01\x01\x00\x00\x00\x00\x00\x00\x00\x00j\xF8@\x00\x00\x00\x00\x00\x00\x00\x00' set session sort_buffer_size= default; DROP TABLE t1, t2, t3; diff --git a/mysql-test/r/cast.result b/mysql-test/r/cast.result index 5ea8f2b6d8c..4c9f257fdf2 100644 --- a/mysql-test/r/cast.result +++ b/mysql-test/r/cast.result @@ -13,7 +13,7 @@ select CAST('10 ' as unsigned integer); CAST('10 ' as unsigned integer) 10 Warnings: -Warning 1292 Truncated incorrect INTEGER value: '10 ' +Note 1292 Truncated incorrect INTEGER value: '10 ' select cast(-5 as unsigned) | 1, cast(-5 as unsigned) & -1; cast(-5 as unsigned) | 1 cast(-5 as unsigned) & -1 18446744073709551611 18446744073709551611 diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result index 664832e9905..e5b3acacec8 100644 --- a/mysql-test/r/create.result +++ b/mysql-test/r/create.result @@ -494,7 +494,7 @@ a b c d e f g h dd 1 -7 7 2000-01-01 b 2000-01-01 00:00:00 05:04:03 yet another binary data 02:00:00 2 -2 2 1825-12-14 a 2003-01-01 03:02:01 04:03:02 binary data 02:00:00 drop table t1, t2; -create table t1 (a tinyint, b smallint, c mediumint, d int, e bigint, f float(3,2), g double(4,3), h decimal(5,4), i year, j date, k timestamp, l datetime, m enum('a','b'), n set('a','b'), o char(10)); +create table t1 (a tinyint, b smallint, c mediumint, d int, e bigint, f float(3,2), g double(4,3), h decimal(5,4), i year, j date, k timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, l datetime, m enum('a','b'), n set('a','b'), o char(10)); create table t2 select ifnull(a,a), ifnull(b,b), ifnull(c,c), ifnull(d,d), ifnull(e,e), ifnull(f,f), ifnull(g,g), ifnull(h,h), ifnull(i,i), ifnull(j,j), ifnull(k,k), ifnull(l,l), ifnull(m,m), ifnull(n,n), ifnull(o,o) from t1; show create table t2; Table Create Table @@ -1926,7 +1926,8 @@ t1 CREATE TABLE `t1` ( `MEMORY_USED` int(7) NOT NULL DEFAULT '0', `EXAMINED_ROWS` int(7) NOT NULL DEFAULT '0', `QUERY_ID` bigint(4) NOT NULL DEFAULT '0', - `INFO_BINARY` blob + `INFO_BINARY` blob, + `TID` bigint(4) NOT NULL DEFAULT '0' ) DEFAULT CHARSET=utf8 drop table t1; create temporary table t1 like information_schema.processlist; @@ -1948,7 +1949,8 @@ t1 CREATE TEMPORARY TABLE `t1` ( `MEMORY_USED` int(7) NOT NULL DEFAULT '0', `EXAMINED_ROWS` int(7) NOT NULL DEFAULT '0', `QUERY_ID` bigint(4) NOT NULL DEFAULT '0', - `INFO_BINARY` blob + `INFO_BINARY` blob, + `TID` bigint(4) NOT NULL DEFAULT '0' ) DEFAULT CHARSET=utf8 drop table t1; create table t1 like information_schema.character_sets; @@ -1970,14 +1972,14 @@ DROP TABLE IF EXISTS t1; DROP TABLE IF EXISTS t2; DROP TABLE IF EXISTS t3; -CREATE TABLE t1(c1 TIMESTAMP, c2 TIMESTAMP); +CREATE TABLE t1(c1 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, c2 TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00'); SET sql_mode = NO_ZERO_DATE; CREATE TABLE t2(c1 TIMESTAMP, c2 TIMESTAMP DEFAULT 0); ERROR 42000: Invalid default value for 'c2' -CREATE TABLE t2(c1 TIMESTAMP, c2 TIMESTAMP); +CREATE TABLE t2(c1 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, c2 TIMESTAMP NOT NULL); ERROR 42000: Invalid default value for 'c2' # -- Check that NULL column still can be created. diff --git a/mysql-test/r/ctype_binary.result b/mysql-test/r/ctype_binary.result index 2dbf783ff27..ac79a158943 100644 --- a/mysql-test/r/ctype_binary.result +++ b/mysql-test/r/ctype_binary.result @@ -2076,7 +2076,7 @@ t2 CREATE TABLE `t2` ( `concat(a)` varbinary(64) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1, t2; -create table t1 (a timestamp); +create table t1 (a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); insert into t1 values (0); insert into t1 values (20010203040506); insert into t1 values (19800203040506); @@ -2388,7 +2388,7 @@ hex(a) 0000000000000001 drop table t1; drop view v1; -create table t1 (a timestamp); +create table t1 (a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); insert into t1 values (0); insert into t1 values (20010203040506); insert into t1 values (19800203040506); diff --git a/mysql-test/r/ctype_cp1251.result b/mysql-test/r/ctype_cp1251.result index 92678f200bd..08a840f7749 100644 --- a/mysql-test/r/ctype_cp1251.result +++ b/mysql-test/r/ctype_cp1251.result @@ -2470,7 +2470,7 @@ t2 CREATE TABLE `t2` ( `concat(a)` varbinary(64) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1, t2; -create table t1 (a timestamp); +create table t1 (a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); insert into t1 values (0); insert into t1 values (20010203040506); insert into t1 values (19800203040506); @@ -2782,7 +2782,7 @@ hex(a) 0000000000000001 drop table t1; drop view v1; -create table t1 (a timestamp); +create table t1 (a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); insert into t1 values (0); insert into t1 values (20010203040506); insert into t1 values (19800203040506); @@ -3345,12 +3345,18 @@ INSERT INTO t1 VALUES (' 1'),('`1'); SELECT * FROM t1 WHERE a IN (1,2,3); a 1 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '`1' SELECT * FROM t1 WHERE a IN (1,2,3) AND a=' 1'; a 1 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '`1' SELECT * FROM t1 WHERE a IN (1,2,3,'4') AND a=' 1'; a 1 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '`1' EXPLAIN EXTENDED SELECT * FROM t1 WHERE a IN (1,2,3) AND a=' 1'; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where diff --git a/mysql-test/r/ctype_latin1.result b/mysql-test/r/ctype_latin1.result index 2d2b3473d57..48475923138 100644 --- a/mysql-test/r/ctype_latin1.result +++ b/mysql-test/r/ctype_latin1.result @@ -2752,7 +2752,7 @@ t2 CREATE TABLE `t2` ( `concat(a)` varbinary(64) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1, t2; -create table t1 (a timestamp); +create table t1 (a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); insert into t1 values (0); insert into t1 values (20010203040506); insert into t1 values (19800203040506); @@ -3064,7 +3064,7 @@ hex(a) 0000000000000001 drop table t1; drop view v1; -create table t1 (a timestamp); +create table t1 (a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); insert into t1 values (0); insert into t1 values (20010203040506); insert into t1 values (19800203040506); @@ -8168,5 +8168,18 @@ SELECT * FROM t1 WHERE a=_latin1'a' AND a=_latin1'A'; a DROP TABLE t1; # +# MDEV-8253 EXPLAIN SELECT prints unexpected characters +# +SET NAMES latin1; +CREATE TABLE t1 (a DECIMAL(10,1),b DECIMAL(10,1),c VARCHAR(10),d VARCHAR(10)); +INSERT INTO t1 VALUES (1.5,1.5,'1','1'),(3.5,3.5,'3','3'); +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE COALESCE(c,0)='3 ' AND COALESCE(d,0)=COALESCE(c,0); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` where ((coalesce(`test`.`t1`.`c`,0) = '3 ') and (coalesce(`test`.`t1`.`d`,0) = '3 ')) +DROP TABLE t1; +# # End of 10.1 tests # diff --git a/mysql-test/r/ctype_many.result b/mysql-test/r/ctype_many.result index f0f98b15e11..59443a3a2d5 100644 --- a/mysql-test/r/ctype_many.result +++ b/mysql-test/r/ctype_many.result @@ -1694,6 +1694,7 @@ DO CONVERT(CAST(SUBSTRING_INDEX(FORMAT(1,'1111'), FORMAT('','Zpq'),1) AS BINARY(0)) USING utf8); Warnings: Warning 1292 Truncated incorrect INTEGER value: 'Zpq' +Warning 1292 Truncated incorrect DOUBLE value: '' Warning 1292 Truncated incorrect BINARY(0) value: '1.' # # End of 5.1 tests diff --git a/mysql-test/r/ctype_ucs.result b/mysql-test/r/ctype_ucs.result index 4da9e95a806..97617f6c3df 100644 --- a/mysql-test/r/ctype_ucs.result +++ b/mysql-test/r/ctype_ucs.result @@ -3685,7 +3685,7 @@ t2 CREATE TABLE `t2` ( `concat(a)` varbinary(64) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1, t2; -create table t1 (a timestamp); +create table t1 (a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); insert into t1 values (0); insert into t1 values (20010203040506); insert into t1 values (19800203040506); @@ -3997,7 +3997,7 @@ hex(a) 0000000000000001 drop table t1; drop view v1; -create table t1 (a timestamp); +create table t1 (a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); insert into t1 values (0); insert into t1 values (20010203040506); insert into t1 values (19800203040506); @@ -5340,7 +5340,7 @@ Warning 1292 Truncated incorrect INTEGER value: '' CREATE TABLE t1 (a DECIMAL(2,0)); SET sql_mode='strict_all_tables'; INSERT INTO t1 VALUES (CONVERT('9e99999999' USING ucs2)); -ERROR 22007: Incorrect decimal value: '9e99999999' for column 'a' at row 1 +ERROR 22003: Out of range value for column 'a' at row 1 SET sql_mode=DEFAULT; INSERT INTO t1 VALUES (CONVERT('aaa' USING ucs2)); Warnings: @@ -5627,5 +5627,18 @@ a 1 DROP TABLE t1; # +# MDEV-8253 EXPLAIN SELECT prints unexpected characters +# +SET NAMES latin1, character_set_connection=ucs2; +CREATE TABLE t1 (a DECIMAL(10,1),b DECIMAL(10,1),c VARCHAR(10),d VARCHAR(10)); +INSERT INTO t1 VALUES (1.5,1.5,'1','1'),(3.5,3.5,'3','3'); +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE COALESCE(c,0)='3 ' AND COALESCE(d,0)=COALESCE(c,0); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` where ((coalesce(`test`.`t1`.`c`,0) = '3 ') and (coalesce(`test`.`t1`.`d`,0) = '3 ')) +DROP TABLE t1; +# # End of 10.1 tests # diff --git a/mysql-test/r/ctype_utf16.result b/mysql-test/r/ctype_utf16.result index bfcd65044d4..77941ab4906 100644 --- a/mysql-test/r/ctype_utf16.result +++ b/mysql-test/r/ctype_utf16.result @@ -1254,6 +1254,8 @@ insert into t1 values ('-1234.1e2xxxx'); Warnings: Warning 1265 Data truncated for column 'a' at row 1 insert into t1 values ('-1234.1e2 '); +Warnings: +Note 1265 Data truncated for column 'a' at row 1 select * from t1; a -123410 @@ -1262,6 +1264,8 @@ a drop table t1; create table t1 (a int); insert into t1 values ('1 '); +Warnings: +Note 1265 Data truncated for column 'a' at row 1 insert into t1 values ('1 x'); Warnings: Warning 1265 Data truncated for column 'a' at row 1 diff --git a/mysql-test/r/ctype_utf16le.result b/mysql-test/r/ctype_utf16le.result index f11d1986b26..a2d3ffb793f 100644 --- a/mysql-test/r/ctype_utf16le.result +++ b/mysql-test/r/ctype_utf16le.result @@ -1474,6 +1474,8 @@ INSERT INTO t1 VALUES ('-1234.1e2xxxx'); Warnings: Warning 1265 Data truncated for column 'a' at row 1 INSERT INTO t1 VALUES ('-1234.1e2 '); +Warnings: +Note 1265 Data truncated for column 'a' at row 1 INSERT INTO t1 VALUES ('123'); INSERT INTO t1 VALUES ('-124'); INSERT INTO t1 VALUES ('+125'); @@ -1520,6 +1522,8 @@ DROP TABLE t1; # CREATE TABLE t1 (a int); INSERT INTO t1 VALUES ('1 '); +Warnings: +Note 1265 Data truncated for column 'a' at row 1 INSERT INTO t1 VALUES ('1 x'); Warnings: Warning 1265 Data truncated for column 'a' at row 1 diff --git a/mysql-test/r/ctype_utf32.result b/mysql-test/r/ctype_utf32.result index 5840d23f3e0..16e045d0fbb 100644 --- a/mysql-test/r/ctype_utf32.result +++ b/mysql-test/r/ctype_utf32.result @@ -1252,6 +1252,8 @@ insert into t1 values ('-1234.1e2xxxx'); Warnings: Warning 1265 Data truncated for column 'a' at row 1 insert into t1 values ('-1234.1e2 '); +Warnings: +Note 1265 Data truncated for column 'a' at row 1 select * from t1; a -123410 @@ -1260,6 +1262,8 @@ a drop table t1; create table t1 (a int); insert into t1 values ('1 '); +Warnings: +Note 1265 Data truncated for column 'a' at row 1 insert into t1 values ('1 x'); Warnings: Warning 1265 Data truncated for column 'a' at row 1 diff --git a/mysql-test/r/ctype_utf8.result b/mysql-test/r/ctype_utf8.result index b1325ffe9ed..66db7df555f 100644 --- a/mysql-test/r/ctype_utf8.result +++ b/mysql-test/r/ctype_utf8.result @@ -4527,7 +4527,7 @@ t2 CREATE TABLE `t2` ( `concat(a)` varbinary(64) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1, t2; -create table t1 (a timestamp); +create table t1 (a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); insert into t1 values (0); insert into t1 values (20010203040506); insert into t1 values (19800203040506); @@ -4839,7 +4839,7 @@ hex(a) 0000000000000001 drop table t1; drop view v1; -create table t1 (a timestamp); +create table t1 (a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); insert into t1 values (0); insert into t1 values (20010203040506); insert into t1 values (19800203040506); @@ -10174,12 +10174,18 @@ INSERT INTO t1 VALUES ('1e1'),('1ë1'); SELECT * FROM t1 WHERE a IN (1,2); a 1ë1 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '1ë1' SELECT * FROM t1 WHERE a IN (1,2) AND a='1ë1'; a 1ë1 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '1ë1' SELECT * FROM t1 WHERE a IN (1,2,'x') AND a='1ë1'; a 1ë1 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '1ë1' EXPLAIN EXTENDED SELECT * FROM t1 WHERE a IN (1,2) AND a='1ë1'; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where @@ -10192,5 +10198,20 @@ Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` = '1ë1') and (`test`.`t1`.`a` in (1,2,'x'))) DROP TABLE IF EXISTS t1; # +# MDEV-8816 Equal field propagation is not applied for WHERE varbinary_column>=_utf8'a' COLLATE utf8_swedish_ci AND varbinary_column='A'; +# +CREATE TABLE t1 (c VARBINARY(10)); +INSERT INTO t1 VALUES ('a'),('A'); +SELECT * FROM t1 WHERE c>=_utf8'a' COLLATE utf8_general_ci AND c='A'; +c +A +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE c>=_utf8'a' COLLATE utf8_general_ci AND c='A'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`c` AS `c` from `test`.`t1` where (`test`.`t1`.`c` = 'A') +DROP TABLE t1; +# # End of 10.1 tests # diff --git a/mysql-test/r/enforce_storage_engine.result b/mysql-test/r/enforce_storage_engine.result index 4a0c3317765..6b5e1f6958b 100644 --- a/mysql-test/r/enforce_storage_engine.result +++ b/mysql-test/r/enforce_storage_engine.result @@ -122,3 +122,33 @@ DROP TABLE t1; set global sql_mode=default; SET SESSION enforce_storage_engine=NULL; SET GLOBAL enforce_storage_engine=NULL; +CREATE TABLE t3 (c1 INT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=MyISAM; +INSERT INTO t3 values (NULL, 'test'); +SET SESSION enforce_storage_engine=Memory; +ALTER TABLE t3 ENGINE=MyISAM; +Warnings: +Note 1266 Using storage engine MEMORY for table 't3' +SHOW CREATE TABLE t3; +Table Create Table +t3 CREATE TABLE `t3` ( + `c1` int(11) NOT NULL AUTO_INCREMENT, + `c2` varchar(10) DEFAULT NULL, + PRIMARY KEY (`c1`) +) ENGINE=MEMORY AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 +DROP TABLE t3; +SET SESSION enforce_storage_engine=NULL; +CREATE TABLE t3 (c1 INT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=MyISAM; +INSERT INTO t3 values (NULL, 'test'); +SET SESSION enforce_storage_engine=Memory; +ALTER TABLE t3 ADD COLUMN c3 INT; +SHOW CREATE TABLE t3; +Table Create Table +t3 CREATE TABLE `t3` ( + `c1` int(11) NOT NULL AUTO_INCREMENT, + `c2` varchar(10) DEFAULT NULL, + `c3` int(11) DEFAULT NULL, + PRIMARY KEY (`c1`) +) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 +DROP TABLE t3; +SET SESSION enforce_storage_engine=NULL; +SET GLOBAL enforce_storage_engine=NULL; diff --git a/mysql-test/r/explain_json.result b/mysql-test/r/explain_json.result index 07ff72b4208..a42f5af114c 100644 --- a/mysql-test/r/explain_json.result +++ b/mysql-test/r/explain_json.result @@ -799,6 +799,7 @@ EXPLAIN { "query_block": { "select_id": 2, + "having_condition": "trigcond(<is_not_null_test>(t1.a))", "full-scan-on-null_key": { "table": { "table_name": "t1", @@ -1110,3 +1111,435 @@ EXPLAIN } } DROP TABLE t1; +# +# MDEV-7970: EXPLAIN FORMAT=JSON does not print HAVING +# +create table t0(a int); +insert into t0 values (0),(1),(2),(3); +create table t1(a int); +insert into t1 select A.a + B.a* 10 + C.a * 100 from t0 A, t0 B, t0 C; +create table t2 ( +a int, +b int, +key (a) +); +insert into t2 select A.a*1000 + B.a, A.a*1000 + B.a from t0 A, t1 B; +# normal HAVING +explain format=json select a, max(b) as TOP from t2 group by a having TOP > a; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "(TOP > t2.a)", + "filesort": { + "temporary_table": { + "function": "buffer", + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 256, + "filtered": 100 + } + } + } + } +} +# HAVING is always TRUE (not printed) +explain format=json select a, max(b) as TOP from t2 group by a having 1<>2; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "temporary_table": { + "function": "buffer", + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 256, + "filtered": 100 + } + } + } + } +} +# HAVING is always FALSE (intercepted by message) +explain format=json select a, max(b) as TOP from t2 group by a having 1=2; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "message": "Impossible HAVING" + } + } +} +# HAVING is absent +explain format=json select a, max(b) as TOP from t2 group by a; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "temporary_table": { + "function": "buffer", + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 256, + "filtered": 100 + } + } + } + } +} +drop table t0, t1, t2; +# +# MDEV-8829: Assertion `0' failed in Explain_table_access::tag_to_json +# +# Check ET_CONST_ROW_NOT_FOUND +create table t1 (i int) engine=myisam; +explain +select * from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 system NULL NULL NULL NULL 0 const row not found +explain format=json +select * from t1; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "system", + "rows": 0, + "filtered": 0, + "const_row_not_found": true + } + } +} +analyze format=json +select * from t1; +ANALYZE +{ + "query_block": { + "select_id": 1, + "table": { + "message": "no matching row in const table" + } + } +} +drop table t1; +# Check ET_IMPOSSIBLE_ON_CONDITION +create table t1 (a int); +create table t2 (pk int primary key); +insert into t1 values (1),(2); +insert into t2 values (1),(2); +explain +select * from t1 left join t2 on t2.pk > 10 and t2.pk < 0; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 const PRIMARY NULL NULL NULL 1 Impossible ON condition +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 +explain format=json +select * from t1 left join t2 on t2.pk > 10 and t2.pk < 0; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "const_condition": "1", + "table": { + "table_name": "t2", + "access_type": "const", + "possible_keys": ["PRIMARY"], + "rows": 1, + "filtered": 100, + "impossible_on_condition": true + }, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 2, + "filtered": 100 + } + } +} +analyze format=json +select * from t1 left join t2 on t2.pk > 10 and t2.pk < 0; +ANALYZE +{ + "query_block": { + "select_id": 1, + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "const_condition": "1", + "table": { + "table_name": "t2", + "access_type": "const", + "possible_keys": ["PRIMARY"], + "r_loops": 0, + "rows": 1, + "r_rows": null, + "filtered": 100, + "r_filtered": null, + "impossible_on_condition": true + }, + "table": { + "table_name": "t1", + "access_type": "ALL", + "r_loops": 1, + "rows": 2, + "r_rows": 2, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100 + } + } +} +# Check ET_NOT_EXISTS: +explain +select * from t1 left join t2 on t2.pk=t1.a where t2.pk is null; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using where; Using index; Not exists +explain format=json +select * from t1 left join t2 on t2.pk=t1.a where t2.pk is null; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 2, + "filtered": 100 + }, + "table": { + "table_name": "t2", + "access_type": "eq_ref", + "possible_keys": ["PRIMARY"], + "key": "PRIMARY", + "key_length": "4", + "used_key_parts": ["pk"], + "ref": ["test.t1.a"], + "rows": 1, + "filtered": 100, + "attached_condition": "(trigcond(isnull(t2.pk)) and trigcond(trigcond((t1.a is not null))))", + "using_index": true, + "not_exists": true + } + } +} +analyze format=json +select * from t1 left join t2 on t2.pk=t1.a where t2.pk is null; +ANALYZE +{ + "query_block": { + "select_id": 1, + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "table": { + "table_name": "t1", + "access_type": "ALL", + "r_loops": 1, + "rows": 2, + "r_rows": 2, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100 + }, + "table": { + "table_name": "t2", + "access_type": "eq_ref", + "possible_keys": ["PRIMARY"], + "key": "PRIMARY", + "key_length": "4", + "used_key_parts": ["pk"], + "ref": ["test.t1.a"], + "r_loops": 2, + "rows": 1, + "r_rows": 1, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100, + "attached_condition": "(trigcond(isnull(t2.pk)) and trigcond(trigcond((t1.a is not null))))", + "using_index": true, + "not_exists": true + } + } +} +# Check ET_DISTINCT +explain +select distinct t1.a from t1 join t2 on t2.pk=t1.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where; Using temporary +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using index; Distinct +explain format=json +select distinct t1.a from t1 join t2 on t2.pk=t1.a; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "temporary_table": { + "function": "buffer", + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 2, + "filtered": 100, + "attached_condition": "(t1.a is not null)" + }, + "table": { + "table_name": "t2", + "access_type": "eq_ref", + "possible_keys": ["PRIMARY"], + "key": "PRIMARY", + "key_length": "4", + "used_key_parts": ["pk"], + "ref": ["test.t1.a"], + "rows": 1, + "filtered": 100, + "using_index": true, + "distinct": true + } + } + } +} +analyze format=json +select distinct t1.a from t1 join t2 on t2.pk=t1.a; +ANALYZE +{ + "query_block": { + "select_id": 1, + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "r_loops": 1, + "rows": 2, + "r_rows": 2, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100, + "attached_condition": "(t1.a is not null)" + }, + "table": { + "table_name": "t2", + "access_type": "eq_ref", + "possible_keys": ["PRIMARY"], + "key": "PRIMARY", + "key_length": "4", + "used_key_parts": ["pk"], + "ref": ["test.t1.a"], + "r_loops": 2, + "rows": 1, + "r_rows": 1, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100, + "using_index": true, + "distinct": true + } + } + } +} +drop table t1,t2; +# Check ET_USING_INDEX_CONDITION_BKA +create table t1(a int); +insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t2(a int); +insert into t2 select A.a + B.a* 10 + C.a * 100 from t1 A, t1 B, t1 C; +create table t3(a int, b int); +insert into t3 select a,a from t1; +create table t4(a int, b int, c int, filler char(100), key (a,b)); +insert into t4 select a,a,a, 'filler-data' from t2; +set @tmp_optimizer_switch=@@optimizer_switch; +set @tmp_join_cache_level=@@join_cache_level; +set optimizer_switch='mrr=on'; +set join_cache_level=6; +explain +select * from t3,t4 where t3.a=t4.a and (t4.b+1 <= t3.b+1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t3 ALL NULL NULL NULL NULL 10 Using where +1 SIMPLE t4 ref a a 5 test.t3.a 1 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan +explain format=json +select * from t3,t4 where t3.a=t4.a and (t4.b+1 <= t3.b+1); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t3", + "access_type": "ALL", + "rows": 10, + "filtered": 100, + "attached_condition": "(t3.a is not null)" + }, + "block-nl-join": { + "table": { + "table_name": "t4", + "access_type": "ref", + "possible_keys": ["a"], + "key": "a", + "key_length": "5", + "used_key_parts": ["a"], + "ref": ["test.t3.a"], + "rows": 1, + "filtered": 100, + "index_condition_bka": "((t4.b + 1) <= (t3.b + 1))" + }, + "buffer_type": "flat", + "buffer_size": "256Kb", + "join_type": "BKA", + "mrr_type": "Rowid-ordered scan" + } + } +} +analyze format=json +select * from t3,t4 where t3.a=t4.a and (t4.b+1 <= t3.b+1); +ANALYZE +{ + "query_block": { + "select_id": 1, + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "table": { + "table_name": "t3", + "access_type": "ALL", + "r_loops": 1, + "rows": 10, + "r_rows": 10, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100, + "attached_condition": "(t3.a is not null)" + }, + "block-nl-join": { + "table": { + "table_name": "t4", + "access_type": "ref", + "possible_keys": ["a"], + "key": "a", + "key_length": "5", + "used_key_parts": ["a"], + "ref": ["test.t3.a"], + "r_loops": 0, + "rows": 1, + "r_rows": null, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": null, + "index_condition_bka": "((t4.b + 1) <= (t3.b + 1))" + }, + "buffer_type": "flat", + "buffer_size": "256Kb", + "join_type": "BKA", + "mrr_type": "Rowid-ordered scan", + "r_filtered": 100 + } + } +} +set optimizer_switch=@tmp_optimizer_switch; +set join_cache_level=@tmp_join_cache_level; +drop table t1,t2,t3,t4; diff --git a/mysql-test/r/func_compress.result b/mysql-test/r/func_compress.result index 4763d878cc6..e9c8193685b 100644 --- a/mysql-test/r/func_compress.result +++ b/mysql-test/r/func_compress.result @@ -73,6 +73,9 @@ set @@global.max_allowed_packet=1048576*100; select compress(repeat('aaaaaaaaaa', IF(XXX, 10, 10000000))) is null; compress(repeat('aaaaaaaaaa', IF(XXX, 10, 10000000))) is null 0 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: XXX +Warning 1292 Truncated incorrect DOUBLE value: XXX set @@global.max_allowed_packet=default; create table t1(a blob); insert into t1 values(NULL), (compress('a')); diff --git a/mysql-test/r/func_math.result b/mysql-test/r/func_math.result index 73e486972ab..c49e55d503a 100644 --- a/mysql-test/r/func_math.result +++ b/mysql-test/r/func_math.result @@ -476,8 +476,13 @@ CREATE TABLE t1(f1 LONGTEXT) engine=myisam; INSERT INTO t1 VALUES ('a'); SELECT 1 FROM (SELECT ROUND(f1) AS a FROM t1) AS s WHERE a LIKE 'a'; 1 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: 'a' SELECT 1 FROM (SELECT ROUND(f1, f1) AS a FROM t1) AS s WHERE a LIKE 'a'; 1 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: 'a' +Warning 1292 Truncated incorrect INTEGER value: 'a' DROP TABLE t1; End of 5.0 tests SELECT 1e308 + 1e308; diff --git a/mysql-test/r/func_set.result b/mysql-test/r/func_set.result index dfc3faf3fac..96af966d367 100644 --- a/mysql-test/r/func_set.result +++ b/mysql-test/r/func_set.result @@ -172,8 +172,8 @@ INTERVAL( 9, 1, DATE_ADD( pk, INTERVAL pk MINUTE_SECOND ), 9, 8, 3, 5, 2, 1 ) 8 8 Warnings: -Warning 1292 Incorrect datetime value: '10' -Warning 1292 Incorrect datetime value: '11' +Warning 1292 Incorrect datetime value: '10' for column 'pk' at row 1 +Warning 1292 Incorrect datetime value: '11' for column 'pk' at row 2 DROP TABLE t1; # # End of 5.3 tests @@ -233,8 +233,8 @@ INTERVAL( 9, 1, DATE_ADD( pk, INTERVAL pk MINUTE_SECOND ), 9, 8, 3, 5, 2, 1 ) 8 8 Warnings: -Warning 1292 Incorrect datetime value: '10' -Warning 1292 Incorrect datetime value: '11' +Warning 1292 Incorrect datetime value: '10' for column 'pk' at row 1 +Warning 1292 Incorrect datetime value: '11' for column 'pk' at row 2 DROP TABLE t1; # # End of 5.3 tests diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result index c37fa7a654a..d392da84ea7 100644 --- a/mysql-test/r/func_str.result +++ b/mysql-test/r/func_str.result @@ -2893,6 +2893,8 @@ NULL SELECT ((127.1) not in ((rpad(1.0,2048,1)),(''),(-1.1))); ((127.1) not in ((rpad(1.0,2048,1)),(''),(-1.1))) 1 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '' SELECT ((0xf3) * (rpad(1.0,2048,1)) << (0xcc)); ((0xf3) * (rpad(1.0,2048,1)) << (0xcc)) 0 diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result index bbdbfe0c549..c0cf6d45ae2 100644 --- a/mysql-test/r/func_time.result +++ b/mysql-test/r/func_time.result @@ -1714,6 +1714,7 @@ min(timestampadd(month, 1>'', from_days('%Z'))) NULL Warnings: Warning 1292 Truncated incorrect INTEGER value: '%Z' +Warning 1292 Truncated incorrect DOUBLE value: '' Warning 1292 Incorrect datetime value: '0000-00-00' create table t1(a time); insert into t1 values ('00:00:00'),('00:01:00'); @@ -1823,8 +1824,6 @@ create table t1 (f1 datetime, key (f1)); insert into t1 values ('2000-09-12 00:00:00'), ('2007-04-25 05:08:49'); select * from t1 where f1 > time('-23:00:06'); f1 -2000-09-12 00:00:00 -2007-04-25 05:08:49 drop table t1; select maketime(20,61,10)+0; maketime(20,61,10)+0 @@ -1835,25 +1834,14 @@ select last_day(f2) from t1; last_day(f2) NULL NULL -Warnings: -Warning 1292 Incorrect datetime value: '0' -Warning 1292 Incorrect datetime value: '0' select last_day(f2) from t1 where last_day(f2) is null; last_day(f2) NULL NULL -Warnings: -Warning 1292 Incorrect datetime value: '0' -Warning 1292 Incorrect datetime value: '0' -Warning 1292 Incorrect datetime value: '0' -Warning 1292 Incorrect datetime value: '0' select * from t1 order by last_day (f2); f2 0 0 -Warnings: -Warning 1292 Incorrect datetime value: '0' -Warning 1292 Incorrect datetime value: '0' drop table t1; SET timestamp=unix_timestamp('2001-02-03 10:20:30'); select convert_tz(timediff('0000-00-00 00:00:00', cast('2008-03-26 07:09:06' as datetime)), 'UTC', 'Europe/Moscow'); @@ -1909,8 +1897,6 @@ insert ignore t1 values ('04:38:11','0000-00-00 00:00:00',0,'0000-00-00 00:00:00 select least(greatest(f3, f2, f4), f5) from t1; least(greatest(f3, f2, f4), f5) 0000-00-00 00:00:00 -Warnings: -Warning 1292 Incorrect datetime value: '0' drop table t1; select day(coalesce(null)); day(coalesce(null)) diff --git a/mysql-test/r/gis-debug.result b/mysql-test/r/gis-debug.result index 8593f434c2b..5e8b4747f6d 100644 --- a/mysql-test/r/gis-debug.result +++ b/mysql-test/r/gis-debug.result @@ -267,6 +267,8 @@ POLYGON # Bug#13358363 - ASSERTION: N > 0 && N < SINUSES_CALCULATED*2+1 | GET_N_SINCOS/ADD_EDGE_BUFFER # DO ST_BUFFER(ST_GEOMCOLLFROMTEXT('linestring(1 1,2 2)'),''); +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '' SELECT ST_WITHIN( LINESTRINGFROMTEXT(' LINESTRING(3 8,9 2,3 8,3 3,7 6,4 7,4 7,8 1) '), ST_BUFFER(MULTIPOLYGONFROMTEXT(' MULTIPOLYGON(((3 5,2 5,2 4,3 4,3 5)),((2 2,2 8,8 8,8 2,2 2),(4 4,4 6,6 6,6 4,4 4)),((0 5,3 5,3 2,1 2,1 1,3 1,3 0,0 0,0 3,2 3,2 4,0 4,0 5))) '), diff --git a/mysql-test/r/gis-precise.result b/mysql-test/r/gis-precise.result index 73aac9dbe05..3824ba6afbb 100644 --- a/mysql-test/r/gis-precise.result +++ b/mysql-test/r/gis-precise.result @@ -754,6 +754,8 @@ POLYGON # Bug#13358363 - ASSERTION: N > 0 && N < SINUSES_CALCULATED*2+1 | GET_N_SINCOS/ADD_EDGE_BUFFER # DO ST_BUFFER(ST_GEOMCOLLFROMTEXT('linestring(1 1,2 2)'),''); +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '' SELECT ST_WITHIN( LINESTRINGFROMTEXT(' LINESTRING(3 8,9 2,3 8,3 3,7 6,4 7,4 7,8 1) '), ST_BUFFER(MULTIPOLYGONFROMTEXT(' MULTIPOLYGON(((3 5,2 5,2 4,3 4,3 5)),((2 2,2 8,8 8,8 2,2 2),(4 4,4 6,6 6,6 4,4 4)),((0 5,3 5,3 2,1 2,1 1,3 1,3 0,0 0,0 3,2 3,2 4,0 4,0 5))) '), diff --git a/mysql-test/r/gis.result b/mysql-test/r/gis.result index 0d8f30d125f..9e337f6b5aa 100644 --- a/mysql-test/r/gis.result +++ b/mysql-test/r/gis.result @@ -1816,3 +1816,15 @@ disjoint select ST_IsRing(NULL); ST_IsRing(NULL) -1 +# +# MDEV-8675 Different results of GIS functions on NULL vs NOT NULL columns +# +CREATE TABLE t1 (g1 GEOMETRY NOT NULL,g2 GEOMETRY NULL); +CREATE TABLE t2 AS SELECT WITHIN(g1,g1) as w1,WITHIN(g2,g2) AS w2 FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `w1` int(1) DEFAULT NULL, + `w2` int(1) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1,t2; diff --git a/mysql-test/r/information_schema_linux.result b/mysql-test/r/information_schema_linux.result new file mode 100644 index 00000000000..43095b76151 --- /dev/null +++ b/mysql-test/r/information_schema_linux.result @@ -0,0 +1,7 @@ +# +# MDEV-6756: map a linux pid (child pid) to a connection id shown in +# the output of SHOW PROCESSLIST +# +SELECT max(tid) != min(tid) FROM information_schema.processlist; +max(tid) != min(tid) +1 diff --git a/mysql-test/r/insert.result b/mysql-test/r/insert.result index 82f3977e231..59be24dbddc 100644 --- a/mysql-test/r/insert.result +++ b/mysql-test/r/insert.result @@ -20,7 +20,7 @@ insert into t1 values (0,"mysql a"); insert into t1 values (0,"r1manic"); insert into t1 values (0,"r1man"); drop table t1; -create table t1 (a int not null auto_increment, primary key (a), t timestamp, c char(10) default "hello", i int); +create table t1 (a int not null auto_increment, primary key (a), t timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, c char(10) default "hello", i int); insert into t1 values (default,default,default,default), (default,default,default,default), (4,0,"a",5),(default,default,default,default); select a,t>0,c,i from t1; a t>0 c i @@ -92,14 +92,14 @@ create table t1(number int auto_increment primary key, original_value varchar(50 set @value= "aa"; insert into t1 values(null,@value,@value,@value,@value,@value,@value,@value,@value,@value); Warnings: -Warning 1265 Data truncated for column 'f_double' at row 1 -Warning 1265 Data truncated for column 'f_float' at row 1 -Warning 1265 Data truncated for column 'f_double_7_2' at row 1 -Warning 1265 Data truncated for column 'f_float_4_3' at row 1 -Warning 1265 Data truncated for column 'f_double_u' at row 1 -Warning 1265 Data truncated for column 'f_float_u' at row 1 -Warning 1265 Data truncated for column 'f_double_15_1_u' at row 1 -Warning 1265 Data truncated for column 'f_float_3_1_u' at row 1 +Warning 1366 Incorrect double value: 'aa' for column 'f_double' at row 1 +Warning 1366 Incorrect double value: 'aa' for column 'f_float' at row 1 +Warning 1366 Incorrect double value: 'aa' for column 'f_double_7_2' at row 1 +Warning 1366 Incorrect double value: 'aa' for column 'f_float_4_3' at row 1 +Warning 1366 Incorrect double value: 'aa' for column 'f_double_u' at row 1 +Warning 1366 Incorrect double value: 'aa' for column 'f_float_u' at row 1 +Warning 1366 Incorrect double value: 'aa' for column 'f_double_15_1_u' at row 1 +Warning 1366 Incorrect double value: 'aa' for column 'f_float_3_1_u' at row 1 select * from t1 where number =last_insert_id(); number 1 original_value aa @@ -136,14 +136,14 @@ f_float_3_1_u 1.0 set @value= "aa1"; insert into t1 values(null,@value,@value,@value,@value,@value,@value,@value,@value,@value); Warnings: -Warning 1265 Data truncated for column 'f_double' at row 1 -Warning 1265 Data truncated for column 'f_float' at row 1 -Warning 1265 Data truncated for column 'f_double_7_2' at row 1 -Warning 1265 Data truncated for column 'f_float_4_3' at row 1 -Warning 1265 Data truncated for column 'f_double_u' at row 1 -Warning 1265 Data truncated for column 'f_float_u' at row 1 -Warning 1265 Data truncated for column 'f_double_15_1_u' at row 1 -Warning 1265 Data truncated for column 'f_float_3_1_u' at row 1 +Warning 1366 Incorrect double value: 'aa1' for column 'f_double' at row 1 +Warning 1366 Incorrect double value: 'aa1' for column 'f_float' at row 1 +Warning 1366 Incorrect double value: 'aa1' for column 'f_double_7_2' at row 1 +Warning 1366 Incorrect double value: 'aa1' for column 'f_float_4_3' at row 1 +Warning 1366 Incorrect double value: 'aa1' for column 'f_double_u' at row 1 +Warning 1366 Incorrect double value: 'aa1' for column 'f_float_u' at row 1 +Warning 1366 Incorrect double value: 'aa1' for column 'f_double_15_1_u' at row 1 +Warning 1366 Incorrect double value: 'aa1' for column 'f_float_3_1_u' at row 1 select * from t1 where number =last_insert_id(); number 3 original_value aa1 diff --git a/mysql-test/r/key.result b/mysql-test/r/key.result index bc4d69e72eb..9b3dcedf7be 100644 --- a/mysql-test/r/key.result +++ b/mysql-test/r/key.result @@ -418,7 +418,7 @@ create table t1 ( c1 int, c2 char(12), c3 varchar(123), -c4 timestamp, +c4 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, index (c1), index i1 (c1), index i2 (c2), diff --git a/mysql-test/r/loaddata.result b/mysql-test/r/loaddata.result index 2d67d24bedd..8cd3b0a7ac8 100644 --- a/mysql-test/r/loaddata.result +++ b/mysql-test/r/loaddata.result @@ -43,8 +43,11 @@ drop table t1; create table t1 (a int, b char(10)); load data infile '../../std_data/loaddata3.dat' into table t1 fields terminated by '' enclosed by '' ignore 1 lines; Warnings: +Note 1265 Data truncated for column 'a' at row 1 +Note 1265 Data truncated for column 'a' at row 2 Warning 1366 Incorrect integer value: 'error ' for column 'a' at row 3 Warning 1262 Row 3 was truncated; it contained more data than there were input columns +Note 1265 Data truncated for column 'a' at row 4 Warning 1366 Incorrect integer value: 'wrong end ' for column 'a' at row 5 Warning 1262 Row 5 was truncated; it contained more data than there were input columns select * from t1; @@ -57,6 +60,9 @@ a b truncate table t1; load data infile '../../std_data/loaddata4.dat' into table t1 fields terminated by '' enclosed by '' lines terminated by '' ignore 1 lines; Warnings: +Note 1265 Data truncated for column 'a' at row 1 +Note 1265 Data truncated for column 'a' at row 2 +Note 1265 Data truncated for column 'a' at row 3 Warning 1366 Incorrect integer value: ' ' for column 'a' at row 4 Warning 1261 Row 4 doesn't contain data for all columns @@ -73,6 +79,8 @@ insert into t1 values(0); select * from t1; id 0 +Warnings: +Note 1265 Data truncated for column 'id' at row 1 select * from t1; id 0 @@ -180,6 +188,13 @@ NULL 10 10 NULL 15 15 truncate table t1; load data infile '../../std_data/loaddata5.dat' into table t1 fields terminated by '' enclosed by '' (a, b) set c="Wow"; +Warnings: +Note 1265 Data truncated for column 'a' at row 1 +Note 1265 Data truncated for column 'b' at row 1 +Note 1265 Data truncated for column 'a' at row 2 +Note 1265 Data truncated for column 'b' at row 2 +Note 1265 Data truncated for column 'a' at row 3 +Note 1265 Data truncated for column 'b' at row 3 select * from t1; a b c 1 2 Wow @@ -187,6 +202,13 @@ a b c 5 6 Wow truncate table t1; load data infile '../../std_data/loaddata5.dat' into table t1 fields terminated by '' enclosed by '' (a, b) set c=concat(a,"+",b,"+",@c,"+",b,"+",if(c is null,"NIL",c)); +Warnings: +Note 1265 Data truncated for column 'a' at row 1 +Note 1265 Data truncated for column 'b' at row 1 +Note 1265 Data truncated for column 'a' at row 2 +Note 1265 Data truncated for column 'b' at row 2 +Note 1265 Data truncated for column 'a' at row 3 +Note 1265 Data truncated for column 'b' at row 3 select * from t1; a b c 1 2 1+2+123+2+NIL @@ -234,7 +256,9 @@ f1 2 delete from t1; Warnings: +Note 1265 Data truncated for column 'f1' at row 1 Warning 1261 Row 1 doesn't contain data for all columns +Note 1265 Data truncated for column 'f1' at row 2 Warning 1261 Row 2 doesn't contain data for all columns select f1 from t1 where f2 <> '0000-00-00 00:00:00' order by f1; f1 diff --git a/mysql-test/r/metadata.result b/mysql-test/r/metadata.result index fcaeb3359f0..ffdab244f31 100644 --- a/mysql-test/r/metadata.result +++ b/mysql-test/r/metadata.result @@ -8,7 +8,7 @@ def hello 253 5 5 N 1 31 8 def NULL 6 0 0 Y 32896 0 63 1 1.0 -1 hello NULL 1 1.0 -1 hello NULL -create table t1 (a tinyint, b smallint, c mediumint, d int, e bigint, f float(3,2), g double(4,3), h decimal(5,4), i year, j date, k timestamp, l datetime, m enum('a','b'), n set('a','b'), o char(10)); +create table t1 (a tinyint, b smallint, c mediumint, d int, e bigint, f float(3,2), g double(4,3), h decimal(5,4), i year, j date, k timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, l datetime, m enum('a','b'), n set('a','b'), o char(10)); select * from t1; Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr def test t1 t1 a a 1 4 0 Y 32768 0 63 @@ -239,7 +239,7 @@ dcol_uns double unsigned, # date/time types date_col date, time_col time, -timestamp_col timestamp, +timestamp_col timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, year_col year, datetime_col datetime, # string types diff --git a/mysql-test/r/mix2_myisam.result b/mysql-test/r/mix2_myisam.result index e824db8928a..bf706b1445d 100644 --- a/mysql-test/r/mix2_myisam.result +++ b/mysql-test/r/mix2_myisam.result @@ -608,7 +608,7 @@ update t1 set sca_pic="test" where sca_pic is null; delete from t1 where sca_code='pd'; drop table t1; set @a:=now(); -CREATE TABLE t1 (a int not null, b timestamp not null, primary key (a)) engine=MyISAM; +CREATE TABLE t1 (a int not null, b timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, primary key (a)) engine=MyISAM; insert into t1 (a) values(1),(2),(3); select t1.a from t1 natural join t1 as t2 where t1.b >= @a order by t1.a; a @@ -843,9 +843,9 @@ number bigint(20) NOT NULL default '0', cname char(15) NOT NULL default '', carrier_id smallint(6) NOT NULL default '0', privacy tinyint(4) NOT NULL default '0', -last_mod_date timestamp NOT NULL, +last_mod_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, last_mod_id smallint(6) NOT NULL default '0', -last_app_date timestamp NOT NULL, +last_app_date timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', last_app_id smallint(6) default '-1', version smallint(6) NOT NULL default '0', assigned_scps int(11) default '0', @@ -862,9 +862,9 @@ number bigint(20) NOT NULL default '0', cname char(15) NOT NULL default '', carrier_id smallint(6) NOT NULL default '0', privacy tinyint(4) NOT NULL default '0', -last_mod_date timestamp NOT NULL, +last_mod_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, last_mod_id smallint(6) NOT NULL default '0', -last_app_date timestamp NOT NULL, +last_app_date timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', last_app_id smallint(6) default '-1', version smallint(6) NOT NULL default '0', assigned_scps int(11) default '0', diff --git a/mysql-test/r/multi_update.result b/mysql-test/r/multi_update.result index b0265119eef..c680339536b 100644 --- a/mysql-test/r/multi_update.result +++ b/mysql-test/r/multi_update.result @@ -181,8 +181,8 @@ ERROR HY000: You are using safe update mode and you tried to update a table with set sql_safe_updates=0; drop table t1,t2; set timestamp=1038401397; -create table t1 (n int(10) not null primary key, d int(10), t timestamp); -create table t2 (n int(10) not null primary key, d int(10), t timestamp); +create table t1 (n int(10) not null primary key, d int(10), t timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); +create table t2 (n int(10) not null primary key, d int(10), t timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); insert into t1 values(1,1,NULL); insert into t2 values(1,10,NULL),(2,20,NULL); set timestamp=1038000000; diff --git a/mysql-test/r/mysql_upgrade.result b/mysql-test/r/mysql_upgrade.result index c34707383b0..673c241de11 100644 --- a/mysql-test/r/mysql_upgrade.result +++ b/mysql-test/r/mysql_upgrade.result @@ -459,4 +459,64 @@ even_longer_user_name_number_3_to_test_the_grantor_and_definer_field_length@loca DROP USER very_long_user_name_number_1, very_long_user_name_number_2, even_longer_user_name_number_3_to_test_the_grantor_and_definer_field_length@localhost; DROP PROCEDURE test.pr; set sql_mode=default; +# Droping the previously created mysql_upgrade_info file.. +create table test.t1(a int) engine=MyISAM; +# Trying to enforce InnoDB for all tables +SET GLOBAL enforce_storage_engine=InnoDB; +Phase 1/6: Checking and upgrading mysql database +Processing databases +mysql +mysql.column_stats OK +mysql.columns_priv OK +mysql.db OK +mysql.event OK +mysql.func OK +mysql.gtid_slave_pos OK +mysql.help_category OK +mysql.help_keyword OK +mysql.help_relation OK +mysql.help_topic OK +mysql.host OK +mysql.index_stats OK +mysql.innodb_index_stats OK +mysql.innodb_table_stats OK +mysql.plugin OK +mysql.proc OK +mysql.procs_priv OK +mysql.proxies_priv OK +mysql.roles_mapping OK +mysql.servers OK +mysql.table_stats OK +mysql.tables_priv OK +mysql.time_zone OK +mysql.time_zone_leap_second OK +mysql.time_zone_name OK +mysql.time_zone_transition OK +mysql.time_zone_transition_type OK +mysql.user OK +Phase 2/6: Fixing views +Phase 3/6: Running 'mysql_fix_privilege_tables' +Phase 4/6: Fixing table and database names +Phase 5/6: Checking and upgrading tables +Processing databases +information_schema +mtr +mtr.global_suppressions OK +mtr.test_suppressions OK +performance_schema +test +test.t1 OK +Phase 6/6: Running 'FLUSH PRIVILEGES' +OK +# Should return 2 +SELECT count(*) FROM information_schema.tables where ENGINE="InnoDB"; +count(*) +2 +SHOW CREATE TABLE test.t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE test.t1; +SET GLOBAL enforce_storage_engine=NULL; End of tests diff --git a/mysql-test/r/mysqld--help.result b/mysql-test/r/mysqld--help.result index e429bff03e4..1ed098ec12a 100644 --- a/mysql-test/r/mysqld--help.result +++ b/mysql-test/r/mysqld--help.result @@ -197,6 +197,11 @@ The following options may be given as the first argument: If non-zero, binary logs will be purged after expire_logs_days days; possible purges happen at startup and at binary log rotation + --explicit-defaults-for-timestamp + This option causes CREATE TABLE to create all TIMESTAMP + columns as NULL with DEFAULT NULL attribute, Without this + option, TIMESTAMP columns are NOT NULL and have implicit + DEFAULT clauses. The old behavior is deprecated. --external-locking Use system (external) locking (disabled by default). With this option enabled you can run myisamchk to test (not repair) tables while the MySQL server is running. @@ -1179,6 +1184,7 @@ enforce-storage-engine (No default value) event-scheduler OFF expensive-subquery-limit 100 expire-logs-days 0 +explicit-defaults-for-timestamp FALSE external-locking FALSE extra-max-connections 1 extra-port 0 diff --git a/mysql-test/r/mysqldump.result b/mysql-test/r/mysqldump.result index ae4377533cc..bbece88bb98 100644 --- a/mysql-test/r/mysqldump.result +++ b/mysql-test/r/mysqldump.result @@ -2891,7 +2891,7 @@ drop table t1; # Bug#13052 mysqldump timestamp reloads broken # drop table if exists t1; -create table t1 (`d` timestamp, unique (`d`)); +create table t1 (`d` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, unique (`d`)); set time_zone='+00:00'; insert into t1 values ('2003-10-25 22:00:00'),('2003-10-25 23:00:00'); select * from t1; diff --git a/mysql-test/r/ps_1general.result b/mysql-test/r/ps_1general.result index 2ed38dc5c11..08485eb95aa 100644 --- a/mysql-test/r/ps_1general.result +++ b/mysql-test/r/ps_1general.result @@ -17,7 +17,8 @@ create table t9 c1 tinyint, c2 smallint, c3 mediumint, c4 int, c5 integer, c6 bigint, c7 float, c8 double, c9 double precision, c10 real, c11 decimal(7, 4), c12 numeric(8, 4), -c13 date, c14 datetime, c15 timestamp, c16 time, +c13 date, c14 datetime, c15 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP +ON UPDATE CURRENT_TIMESTAMP, c16 time, c17 year, c18 tinyint, c19 bool, c20 char, c21 char(10), c22 varchar(30), c23 tinyblob, c24 tinytext, c25 blob, c26 text, c27 mediumblob, c28 mediumtext, diff --git a/mysql-test/r/ps_2myisam.result b/mysql-test/r/ps_2myisam.result index 194d61aaaf8..bdd8040f584 100644 --- a/mysql-test/r/ps_2myisam.result +++ b/mysql-test/r/ps_2myisam.result @@ -10,7 +10,8 @@ create table t9 c1 tinyint, c2 smallint, c3 mediumint, c4 int, c5 integer, c6 bigint, c7 float, c8 double, c9 double precision, c10 real, c11 decimal(7, 4), c12 numeric(8, 4), -c13 date, c14 datetime, c15 timestamp, c16 time, +c13 date, c14 datetime, c15 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP +ON UPDATE CURRENT_TIMESTAMP, c16 time, c17 year, c18 tinyint, c19 bool, c20 char, c21 char(10), c22 varchar(30), c23 tinyblob, c24 tinytext, c25 blob, c26 text, c27 mediumblob, c28 mediumtext, diff --git a/mysql-test/r/ps_3innodb.result b/mysql-test/r/ps_3innodb.result index c9e7d042508..9d478acdccd 100644 --- a/mysql-test/r/ps_3innodb.result +++ b/mysql-test/r/ps_3innodb.result @@ -10,7 +10,8 @@ create table t9 c1 tinyint, c2 smallint, c3 mediumint, c4 int, c5 integer, c6 bigint, c7 float, c8 double, c9 double precision, c10 real, c11 decimal(7, 4), c12 numeric(8, 4), -c13 date, c14 datetime, c15 timestamp, c16 time, +c13 date, c14 datetime, c15 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP +ON UPDATE CURRENT_TIMESTAMP, c16 time, c17 year, c18 tinyint, c19 bool, c20 char, c21 char(10), c22 varchar(30), c23 tinyblob, c24 tinytext, c25 blob, c26 text, c27 mediumblob, c28 mediumtext, diff --git a/mysql-test/r/ps_4heap.result b/mysql-test/r/ps_4heap.result index 5549446d85e..d1483df63b0 100644 --- a/mysql-test/r/ps_4heap.result +++ b/mysql-test/r/ps_4heap.result @@ -11,7 +11,8 @@ create table t9 c1 tinyint, c2 smallint, c3 mediumint, c4 int, c5 integer, c6 bigint, c7 float, c8 double, c9 double precision, c10 real, c11 decimal(7, 4), c12 numeric(8, 4), -c13 date, c14 datetime, c15 timestamp, c16 time, +c13 date, c14 datetime, c15 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP +ON UPDATE CURRENT_TIMESTAMP, c16 time, c17 year, c18 tinyint, c19 bool, c20 char, c21 char(10), c22 varchar(30), c23 varchar(100), c24 varchar(100), c25 varchar(100), c26 varchar(100), c27 varchar(100), c28 varchar(100), diff --git a/mysql-test/r/ps_5merge.result b/mysql-test/r/ps_5merge.result index 059d4e6e6ab..c42ab154fa3 100644 --- a/mysql-test/r/ps_5merge.result +++ b/mysql-test/r/ps_5merge.result @@ -12,7 +12,8 @@ create table t9 c1 tinyint, c2 smallint, c3 mediumint, c4 int, c5 integer, c6 bigint, c7 float, c8 double, c9 double precision, c10 real, c11 decimal(7, 4), c12 numeric(8, 4), -c13 date, c14 datetime, c15 timestamp, c16 time, +c13 date, c14 datetime, c15 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP +ON UPDATE CURRENT_TIMESTAMP, c16 time, c17 year, c18 tinyint, c19 bool, c20 char, c21 char(10), c22 varchar(30), c23 tinyblob, c24 tinytext, c25 blob, c26 text, c27 mediumblob, c28 mediumtext, @@ -32,7 +33,8 @@ create table t9 c1 tinyint, c2 smallint, c3 mediumint, c4 int, c5 integer, c6 bigint, c7 float, c8 double, c9 double precision, c10 real, c11 decimal(7, 4), c12 numeric(8, 4), -c13 date, c14 datetime, c15 timestamp, c16 time, +c13 date, c14 datetime, c15 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP +ON UPDATE CURRENT_TIMESTAMP, c16 time, c17 year, c18 tinyint, c19 bool, c20 char, c21 char(10), c22 varchar(30), c23 tinyblob, c24 tinytext, c25 blob, c26 text, c27 mediumblob, c28 mediumtext, @@ -52,7 +54,7 @@ create table t9 c1 tinyint, c2 smallint, c3 mediumint, c4 int, c5 integer, c6 bigint, c7 float, c8 double, c9 double precision, c10 real, c11 decimal(7, 4), c12 numeric(8, 4), -c13 date, c14 datetime, c15 timestamp, c16 time, +c13 date, c14 datetime, c15 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, c16 time, c17 year, c18 tinyint, c19 bool, c20 char, c21 char(10), c22 varchar(30), c23 tinyblob, c24 tinytext, c25 blob, c26 text, c27 mediumblob, c28 mediumtext, @@ -3406,7 +3408,7 @@ create table t9 c1 tinyint, c2 smallint, c3 mediumint, c4 int, c5 integer, c6 bigint, c7 float, c8 double, c9 double precision, c10 real, c11 decimal(7, 4), c12 numeric(8, 4), -c13 date, c14 datetime, c15 timestamp, c16 time, +c13 date, c14 datetime, c15 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, c16 time, c17 year, c18 tinyint, c19 bool, c20 char, c21 char(10), c22 varchar(30), c23 tinyblob, c24 tinytext, c25 blob, c26 text, c27 mediumblob, c28 mediumtext, diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index 97ab9173c2d..9dbf6e0c46b 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -2111,7 +2111,7 @@ INSERT INTO t1 (pseudo) VALUES ('test1'); SELECT 1 as rnd1 from t1 where rand() > 2; rnd1 DROP TABLE t1; -CREATE TABLE t1 (gvid int(10) unsigned default NULL, hmid int(10) unsigned default NULL, volid int(10) unsigned default NULL, mmid int(10) unsigned default NULL, hdid int(10) unsigned default NULL, fsid int(10) unsigned default NULL, ctid int(10) unsigned default NULL, dtid int(10) unsigned default NULL, cost int(10) unsigned default NULL, performance int(10) unsigned default NULL, serialnumber bigint(20) unsigned default NULL, monitored tinyint(3) unsigned default '1', removed tinyint(3) unsigned default '0', target tinyint(3) unsigned default '0', dt_modified timestamp NOT NULL, name varchar(255) binary default NULL, description varchar(255) default NULL, UNIQUE KEY hmid (hmid,volid)) ENGINE=MyISAM; +CREATE TABLE t1 (gvid int(10) unsigned default NULL, hmid int(10) unsigned default NULL, volid int(10) unsigned default NULL, mmid int(10) unsigned default NULL, hdid int(10) unsigned default NULL, fsid int(10) unsigned default NULL, ctid int(10) unsigned default NULL, dtid int(10) unsigned default NULL, cost int(10) unsigned default NULL, performance int(10) unsigned default NULL, serialnumber bigint(20) unsigned default NULL, monitored tinyint(3) unsigned default '1', removed tinyint(3) unsigned default '0', target tinyint(3) unsigned default '0', dt_modified timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, name varchar(255) binary default NULL, description varchar(255) default NULL, UNIQUE KEY hmid (hmid,volid)) ENGINE=MyISAM; INSERT INTO t1 VALUES (200001,2,1,1,100,1,1,1,0,0,0,1,0,1,20020425060057,'\\\\ARKIVIO-TESTPDC\\E$',''),(200002,2,2,1,101,1,1,1,0,0,0,1,0,1,20020425060057,'\\\\ARKIVIO-TESTPDC\\C$',''),(200003,1,3,2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,0,1,20020425060427,'c:',NULL); CREATE TABLE t2 ( hmid int(10) unsigned default NULL, volid int(10) unsigned default NULL, sampletid smallint(5) unsigned default NULL, sampletime datetime default NULL, samplevalue bigint(20) unsigned default NULL, KEY idx1 (hmid,volid,sampletid,sampletime)) ENGINE=MyISAM; INSERT INTO t2 VALUES (1,3,10,'2002-06-01 08:00:00',35),(1,3,1010,'2002-06-01 12:00:01',35); @@ -3742,7 +3742,7 @@ WHERE ID_better=1 AND ID1_with_null IS NULL AND id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where DROP TABLE t1; -CREATE TABLE t1 (a INT, ts TIMESTAMP, KEY ts(ts)); +CREATE TABLE t1 (a INT, ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY ts(ts)); INSERT INTO t1 VALUES (30,"2006-01-03 23:00:00"), (31,"2006-01-03 23:00:00"); ANALYZE TABLE t1; Table Op Msg_type Msg_text @@ -4482,7 +4482,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra Warnings: Note 1003 select '2001-01-01' AS `a` from dual where 1 DROP TABLE t1; -CREATE TABLE t1(a TIMESTAMP NOT NULL); +CREATE TABLE t1(a TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); INSERT INTO t1 VALUES('2001-01-01'); SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00'; a diff --git a/mysql-test/r/select_jcl6.result b/mysql-test/r/select_jcl6.result index 601fcc116b7..81e52929881 100644 --- a/mysql-test/r/select_jcl6.result +++ b/mysql-test/r/select_jcl6.result @@ -2122,7 +2122,7 @@ INSERT INTO t1 (pseudo) VALUES ('test1'); SELECT 1 as rnd1 from t1 where rand() > 2; rnd1 DROP TABLE t1; -CREATE TABLE t1 (gvid int(10) unsigned default NULL, hmid int(10) unsigned default NULL, volid int(10) unsigned default NULL, mmid int(10) unsigned default NULL, hdid int(10) unsigned default NULL, fsid int(10) unsigned default NULL, ctid int(10) unsigned default NULL, dtid int(10) unsigned default NULL, cost int(10) unsigned default NULL, performance int(10) unsigned default NULL, serialnumber bigint(20) unsigned default NULL, monitored tinyint(3) unsigned default '1', removed tinyint(3) unsigned default '0', target tinyint(3) unsigned default '0', dt_modified timestamp NOT NULL, name varchar(255) binary default NULL, description varchar(255) default NULL, UNIQUE KEY hmid (hmid,volid)) ENGINE=MyISAM; +CREATE TABLE t1 (gvid int(10) unsigned default NULL, hmid int(10) unsigned default NULL, volid int(10) unsigned default NULL, mmid int(10) unsigned default NULL, hdid int(10) unsigned default NULL, fsid int(10) unsigned default NULL, ctid int(10) unsigned default NULL, dtid int(10) unsigned default NULL, cost int(10) unsigned default NULL, performance int(10) unsigned default NULL, serialnumber bigint(20) unsigned default NULL, monitored tinyint(3) unsigned default '1', removed tinyint(3) unsigned default '0', target tinyint(3) unsigned default '0', dt_modified timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, name varchar(255) binary default NULL, description varchar(255) default NULL, UNIQUE KEY hmid (hmid,volid)) ENGINE=MyISAM; INSERT INTO t1 VALUES (200001,2,1,1,100,1,1,1,0,0,0,1,0,1,20020425060057,'\\\\ARKIVIO-TESTPDC\\E$',''),(200002,2,2,1,101,1,1,1,0,0,0,1,0,1,20020425060057,'\\\\ARKIVIO-TESTPDC\\C$',''),(200003,1,3,2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,0,1,20020425060427,'c:',NULL); CREATE TABLE t2 ( hmid int(10) unsigned default NULL, volid int(10) unsigned default NULL, sampletid smallint(5) unsigned default NULL, sampletime datetime default NULL, samplevalue bigint(20) unsigned default NULL, KEY idx1 (hmid,volid,sampletid,sampletime)) ENGINE=MyISAM; INSERT INTO t2 VALUES (1,3,10,'2002-06-01 08:00:00',35),(1,3,1010,'2002-06-01 12:00:01',35); @@ -3753,7 +3753,7 @@ WHERE ID_better=1 AND ID1_with_null IS NULL AND id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where DROP TABLE t1; -CREATE TABLE t1 (a INT, ts TIMESTAMP, KEY ts(ts)); +CREATE TABLE t1 (a INT, ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY ts(ts)); INSERT INTO t1 VALUES (30,"2006-01-03 23:00:00"), (31,"2006-01-03 23:00:00"); ANALYZE TABLE t1; Table Op Msg_type Msg_text @@ -4493,7 +4493,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra Warnings: Note 1003 select '2001-01-01' AS `a` from dual where 1 DROP TABLE t1; -CREATE TABLE t1(a TIMESTAMP NOT NULL); +CREATE TABLE t1(a TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); INSERT INTO t1 VALUES('2001-01-01'); SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00'; a diff --git a/mysql-test/r/select_pkeycache.result b/mysql-test/r/select_pkeycache.result index 97ab9173c2d..9dbf6e0c46b 100644 --- a/mysql-test/r/select_pkeycache.result +++ b/mysql-test/r/select_pkeycache.result @@ -2111,7 +2111,7 @@ INSERT INTO t1 (pseudo) VALUES ('test1'); SELECT 1 as rnd1 from t1 where rand() > 2; rnd1 DROP TABLE t1; -CREATE TABLE t1 (gvid int(10) unsigned default NULL, hmid int(10) unsigned default NULL, volid int(10) unsigned default NULL, mmid int(10) unsigned default NULL, hdid int(10) unsigned default NULL, fsid int(10) unsigned default NULL, ctid int(10) unsigned default NULL, dtid int(10) unsigned default NULL, cost int(10) unsigned default NULL, performance int(10) unsigned default NULL, serialnumber bigint(20) unsigned default NULL, monitored tinyint(3) unsigned default '1', removed tinyint(3) unsigned default '0', target tinyint(3) unsigned default '0', dt_modified timestamp NOT NULL, name varchar(255) binary default NULL, description varchar(255) default NULL, UNIQUE KEY hmid (hmid,volid)) ENGINE=MyISAM; +CREATE TABLE t1 (gvid int(10) unsigned default NULL, hmid int(10) unsigned default NULL, volid int(10) unsigned default NULL, mmid int(10) unsigned default NULL, hdid int(10) unsigned default NULL, fsid int(10) unsigned default NULL, ctid int(10) unsigned default NULL, dtid int(10) unsigned default NULL, cost int(10) unsigned default NULL, performance int(10) unsigned default NULL, serialnumber bigint(20) unsigned default NULL, monitored tinyint(3) unsigned default '1', removed tinyint(3) unsigned default '0', target tinyint(3) unsigned default '0', dt_modified timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, name varchar(255) binary default NULL, description varchar(255) default NULL, UNIQUE KEY hmid (hmid,volid)) ENGINE=MyISAM; INSERT INTO t1 VALUES (200001,2,1,1,100,1,1,1,0,0,0,1,0,1,20020425060057,'\\\\ARKIVIO-TESTPDC\\E$',''),(200002,2,2,1,101,1,1,1,0,0,0,1,0,1,20020425060057,'\\\\ARKIVIO-TESTPDC\\C$',''),(200003,1,3,2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,0,1,20020425060427,'c:',NULL); CREATE TABLE t2 ( hmid int(10) unsigned default NULL, volid int(10) unsigned default NULL, sampletid smallint(5) unsigned default NULL, sampletime datetime default NULL, samplevalue bigint(20) unsigned default NULL, KEY idx1 (hmid,volid,sampletid,sampletime)) ENGINE=MyISAM; INSERT INTO t2 VALUES (1,3,10,'2002-06-01 08:00:00',35),(1,3,1010,'2002-06-01 12:00:01',35); @@ -3742,7 +3742,7 @@ WHERE ID_better=1 AND ID1_with_null IS NULL AND id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where DROP TABLE t1; -CREATE TABLE t1 (a INT, ts TIMESTAMP, KEY ts(ts)); +CREATE TABLE t1 (a INT, ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY ts(ts)); INSERT INTO t1 VALUES (30,"2006-01-03 23:00:00"), (31,"2006-01-03 23:00:00"); ANALYZE TABLE t1; Table Op Msg_type Msg_text @@ -4482,7 +4482,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra Warnings: Note 1003 select '2001-01-01' AS `a` from dual where 1 DROP TABLE t1; -CREATE TABLE t1(a TIMESTAMP NOT NULL); +CREATE TABLE t1(a TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); INSERT INTO t1 VALUES('2001-01-01'); SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00'; a diff --git a/mysql-test/r/selectivity_no_engine.result b/mysql-test/r/selectivity_no_engine.result index a14832f9c0a..31037e90a84 100644 --- a/mysql-test/r/selectivity_no_engine.result +++ b/mysql-test/r/selectivity_no_engine.result @@ -276,6 +276,23 @@ id select_type table type possible_keys key key_len ref rows filtered Extra Warnings: Note 1003 select `test`.`ta`.`a` AS `a`,`test`.`tb`.`a` AS `a` from `test`.`t1` `ta` join `test`.`t2` `tb` where ((`test`.`tb`.`a` = `test`.`ta`.`a`) and (`test`.`ta`.`a` < 40) and (`test`.`ta`.`a` < 100)) drop table t0,t1,t2; +# +# MDEV-8779: mysqld got signal 11 in sql/opt_range_mrr.cc:100(step_down_to) +# +set @tmp_mdev8779=@@optimizer_use_condition_selectivity; +set optimizer_use_condition_selectivity=5; +CREATE TABLE t1 ( +i int(10) unsigned NOT NULL AUTO_INCREMENT, +n varchar(2048) NOT NULL, +d tinyint(1) unsigned NOT NULL, +p int(10) unsigned NOT NULL, +PRIMARY KEY (i) +) DEFAULT CHARSET=utf8; +insert into t1 values (1,'aaa',1,1), (2,'bbb',2,2); +SELECT * FROM t1 WHERE t1.d = 0 AND t1.p = '1' AND t1.i != '-1' AND t1.n = 'some text'; +i n d p +set optimizer_use_condition_selectivity= @tmp_mdev8779; +DROP TABLE t1; # # End of the test file # diff --git a/mysql-test/r/show_check.result b/mysql-test/r/show_check.result index c281650ecf5..6d1cbb21ac7 100644 --- a/mysql-test/r/show_check.result +++ b/mysql-test/r/show_check.result @@ -316,7 +316,7 @@ type_numeric numeric(5,2), empty_char char(0), type_char char(2), type_varchar varchar(10), -type_timestamp timestamp not null, +type_timestamp timestamp not null default current_timestamp on update current_timestamp, type_date date not null default '0000-00-00', type_time time not null default '00:00:00', type_datetime datetime not null default '0000-00-00 00:00:00', diff --git a/mysql-test/r/sp-error.result b/mysql-test/r/sp-error.result index 4373925b8ac..1f38e3888a0 100644 --- a/mysql-test/r/sp-error.result +++ b/mysql-test/r/sp-error.result @@ -1758,7 +1758,7 @@ CAST('10 ' as unsigned integer) CAST('10 ' as unsigned integer) 10 Warnings: -Warning 1292 Truncated incorrect INTEGER value: '10 ' +Note 1292 Truncated incorrect INTEGER value: '10 ' DROP PROCEDURE p1; DROP PROCEDURE p2; @@ -1767,7 +1767,7 @@ DROP PROCEDURE p2; CREATE PROCEDURE p1() BEGIN DECLARE c INT DEFAULT 0; -DECLARE CONTINUE HANDLER FOR SQLWARNING SET c = c + 1; +DECLARE CONTINUE HANDLER FOR SQLSTATE '22007' SET c = c + 1; CALL p2(); CALL p3(); CALL p4(); @@ -1797,7 +1797,7 @@ END| CREATE PROCEDURE P6() BEGIN DECLARE c INT DEFAULT 0; -DECLARE CONTINUE HANDLER FOR SQLWARNING SET c = c + 1; +DECLARE CONTINUE HANDLER FOR SQLSTATE '22007' SET c = c + 1; CALL p5(); SELECT c; END| @@ -1821,7 +1821,7 @@ CALL p6(); CAST('10 ' as unsigned integer) 10 Level Code Message -Warning 1292 Truncated incorrect INTEGER value: '10 ' +Note 1292 Truncated incorrect INTEGER value: '10 ' c 1 DROP PROCEDURE p1; @@ -1982,9 +1982,9 @@ Level Code Message Warning 1264 Out of range value for column 'x' at row 1 Warning 1264 Out of range value for column 'y' at row 1 Warning 1264 Out of range value for column 'z' at row 1 -Warning 1292 Truncated incorrect INTEGER value: '111111 ' +Note 1292 Truncated incorrect INTEGER value: '111111 ' Warning 1264 Out of range value for column 'a' at row 1 -Warning 1292 Truncated incorrect INTEGER value: '222222 ' +Note 1292 Truncated incorrect INTEGER value: '222222 ' Warning 1264 Out of range value for column 'b' at row 1 Error 1048 Column 'c' cannot be null @@ -2744,14 +2744,14 @@ END| CALL p6()| Level Code Message -Warning 1292 Truncated incorrect INTEGER value: '1 ' -Warning 1292 Truncated incorrect INTEGER value: '1999999 ' +Note 1292 Truncated incorrect INTEGER value: '1 ' +Note 1292 Truncated incorrect INTEGER value: '1999999 ' Warning 1264 Out of range value for column 'a' at row 1 -Warning 1292 Truncated incorrect INTEGER value: '2 ' -Warning 1292 Truncated incorrect INTEGER value: '2999999 ' +Note 1292 Truncated incorrect INTEGER value: '2 ' +Note 1292 Truncated incorrect INTEGER value: '2999999 ' Warning 1264 Out of range value for column 'b' at row 1 -Warning 1292 Truncated incorrect INTEGER value: '3 ' -Warning 1292 Truncated incorrect INTEGER value: '3999999 ' +Note 1292 Truncated incorrect INTEGER value: '3 ' +Note 1292 Truncated incorrect INTEGER value: '3999999 ' Warning 1264 Out of range value for column 'c' at row 1 Msg Handler for 1292 diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index fe81c96e811..51c1b1924d1 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -7102,7 +7102,7 @@ CAST('10 ' as UNSIGNED INTEGER) 1 Warnings: Warning 1265 Data truncated for column 'a' at row 1 -Warning 1292 Truncated incorrect INTEGER value: '10 ' +Note 1292 Truncated incorrect INTEGER value: '10 ' DROP PROCEDURE p1; DROP PROCEDURE IF EXISTS p1; DROP PROCEDURE IF EXISTS p2; @@ -7128,7 +7128,7 @@ CAST('10 ' as UNSIGNED INTEGER) 2 2 Warnings: -Warning 1292 Truncated incorrect INTEGER value: '10 ' +Note 1292 Truncated incorrect INTEGER value: '10 ' DROP PROCEDURE p1; DROP PROCEDURE p2; DROP PROCEDURE p3; diff --git a/mysql-test/r/sql_mode.result b/mysql-test/r/sql_mode.result index 4fdac6b9cea..d71748bd9b1 100644 --- a/mysql-test/r/sql_mode.result +++ b/mysql-test/r/sql_mode.result @@ -144,7 +144,7 @@ ERROR 42000: Variable 'sql_mode' can't be set to the value of 'NULL' set session sql_mode=ansi; create table t1 (f1 integer auto_increment primary key, -f2 timestamp default current_timestamp on update current_timestamp); +f2 timestamp not null default current_timestamp on update current_timestamp); show create table t1; Table Create Table t1 CREATE TABLE "t1" ( diff --git a/mysql-test/r/strict.result b/mysql-test/r/strict.result index caec188ce74..9dcd5975411 100644 --- a/mysql-test/r/strict.result +++ b/mysql-test/r/strict.result @@ -778,7 +778,7 @@ ERROR 22003: Out of range value for column 'col1' at row 1 INSERT INTO t1 VALUES ('-100E+1'); ERROR 22003: Out of range value for column 'col1' at row 1 INSERT INTO t1 VALUES ('-100E'); -ERROR 22007: Incorrect decimal value: '-100E' for column 'col1' at row 1 +ERROR 01000: Data truncated for column 'col1' at row 1 UPDATE t1 SET col1 =col1 * 50000 WHERE col1 =11; ERROR 22003: Out of range value for column 'col1' at row 6 UPDATE t1 SET col1 =col1 / 0 WHERE col1 > 0; @@ -790,10 +790,10 @@ ERROR 22007: Incorrect decimal value: '' for column 'col1' at row 1 INSERT INTO t1 (col1) VALUES ('a59b'); ERROR 22007: Incorrect decimal value: 'a59b' for column 'col1' at row 1 INSERT INTO t1 (col1) VALUES ('1a'); -ERROR 22007: Incorrect decimal value: '1a' for column 'col1' at row 1 +ERROR 01000: Data truncated for column 'col1' at row 1 INSERT IGNORE INTO t1 (col1) VALUES ('2a'); Warnings: -Note 1265 Data truncated for column 'col1' at row 1 +Warning 1265 Data truncated for column 'col1' at row 1 INSERT IGNORE INTO t1 values (1/0); Warnings: Warning 1365 Division by 0 @@ -853,9 +853,9 @@ ERROR 22012: Division by 0 UPDATE t1 SET col2= MOD(col2,0) WHERE col2 > 0; ERROR 22012: Division by 0 INSERT INTO t1 (col1) VALUES (''); -ERROR 01000: Data truncated for column 'col1' at row 1 +ERROR 22007: Incorrect double value: '' for column 'col1' at row 1 INSERT INTO t1 (col1) VALUES ('a59b'); -ERROR 01000: Data truncated for column 'col1' at row 1 +ERROR 22007: Incorrect double value: 'a59b' for column 'col1' at row 1 INSERT INTO t1 (col1) VALUES ('1a'); ERROR 01000: Data truncated for column 'col1' at row 1 INSERT IGNORE INTO t1 (col1) VALUES ('2a'); @@ -903,9 +903,9 @@ ERROR 22012: Division by 0 UPDATE t1 SET col2= MOD(col2,0) WHERE col2 > 0; ERROR 22012: Division by 0 INSERT INTO t1 (col1) VALUES (''); -ERROR 01000: Data truncated for column 'col1' at row 1 +ERROR 22007: Incorrect double value: '' for column 'col1' at row 1 INSERT INTO t1 (col1) VALUES ('a59b'); -ERROR 01000: Data truncated for column 'col1' at row 1 +ERROR 22007: Incorrect double value: 'a59b' for column 'col1' at row 1 INSERT INTO t1 (col1) VALUES ('1a'); ERROR 01000: Data truncated for column 'col1' at row 1 INSERT IGNORE INTO t1 (col1) VALUES ('2a'); @@ -1125,6 +1125,8 @@ ERROR 22007: Truncated incorrect INTEGER value: '10a' insert into t1 (col2) values (cast('10' as unsigned integer)); insert into t1 (col2) values (cast('10' as signed integer)); insert into t1 (col2) values (10E+0 + '0 '); +Warnings: +Note 1292 Truncated incorrect DOUBLE value: '0 ' select * from t1; col1 col2 NULL 10 @@ -1265,7 +1267,7 @@ d 2000-10-01 drop table t1; set @@sql_mode='traditional'; -create table t1(a int, b timestamp); +create table t1(a int, b timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); alter table t1 add primary key(a); show create table t1; Table Create Table @@ -1275,7 +1277,7 @@ t1 CREATE TABLE `t1` ( PRIMARY KEY (`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; -create table t1(a int, b timestamp default 20050102030405); +create table t1(a int, b timestamp not null default 20050102030405); alter table t1 add primary key(a); show create table t1; Table Create Table diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 6b3663489b0..689edfa6dab 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -6312,12 +6312,22 @@ SELECT 1 FROM t1 WHERE a = ORDER BY b ); 1 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: 'a' +Warning 1292 Truncated incorrect DOUBLE value: 'b' +Warning 1292 Truncated incorrect DOUBLE value: 'a' +Warning 1292 Truncated incorrect DOUBLE value: 'b' SELECT 1 FROM t1 WHERE a = (SELECT 1 FROM t2 WHERE b = (SELECT 1 FROM t1 t11 WHERE c = 1 OR t1.a = 1 AND 1 = 2) GROUP BY b ); 1 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: 'a' +Warning 1292 Truncated incorrect DOUBLE value: 'b' +Warning 1292 Truncated incorrect DOUBLE value: 'a' +Warning 1292 Truncated incorrect DOUBLE value: 'b' DROP TABLE t1, t2; # # BUG#12616253 - WRONG RESULT WITH EXISTS(SUBQUERY) (MISSING ROWS) diff --git a/mysql-test/r/subselect_innodb.result b/mysql-test/r/subselect_innodb.result index 07d00e96549..cfbe5d41418 100644 --- a/mysql-test/r/subselect_innodb.result +++ b/mysql-test/r/subselect_innodb.result @@ -296,8 +296,6 @@ INSERT INTO t1 VALUES ('2011-05-13', 0); SELECT * FROM t1 WHERE b < (SELECT CAST(a as date) FROM t1 GROUP BY a); a b 2011-05-13 0 -Warnings: -Warning 1292 Incorrect datetime value: '0' DROP TABLE t1; # # Bug #11766300 59387: FAILING ASSERTION: CURSOR->POS_STATE == 1997660512 (BTR_PCUR_IS_POSITIONE diff --git a/mysql-test/r/subselect_no_exists_to_in.result b/mysql-test/r/subselect_no_exists_to_in.result index b6984cac86f..195f377e7ff 100644 --- a/mysql-test/r/subselect_no_exists_to_in.result +++ b/mysql-test/r/subselect_no_exists_to_in.result @@ -6312,12 +6312,22 @@ SELECT 1 FROM t1 WHERE a = ORDER BY b ); 1 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: 'a' +Warning 1292 Truncated incorrect DOUBLE value: 'b' +Warning 1292 Truncated incorrect DOUBLE value: 'a' +Warning 1292 Truncated incorrect DOUBLE value: 'b' SELECT 1 FROM t1 WHERE a = (SELECT 1 FROM t2 WHERE b = (SELECT 1 FROM t1 t11 WHERE c = 1 OR t1.a = 1 AND 1 = 2) GROUP BY b ); 1 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: 'a' +Warning 1292 Truncated incorrect DOUBLE value: 'b' +Warning 1292 Truncated incorrect DOUBLE value: 'a' +Warning 1292 Truncated incorrect DOUBLE value: 'b' DROP TABLE t1, t2; # # BUG#12616253 - WRONG RESULT WITH EXISTS(SUBQUERY) (MISSING ROWS) diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result index a0922b95e35..26794b452cc 100644 --- a/mysql-test/r/subselect_no_mat.result +++ b/mysql-test/r/subselect_no_mat.result @@ -6307,12 +6307,22 @@ SELECT 1 FROM t1 WHERE a = ORDER BY b ); 1 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: 'a' +Warning 1292 Truncated incorrect DOUBLE value: 'b' +Warning 1292 Truncated incorrect DOUBLE value: 'a' +Warning 1292 Truncated incorrect DOUBLE value: 'b' SELECT 1 FROM t1 WHERE a = (SELECT 1 FROM t2 WHERE b = (SELECT 1 FROM t1 t11 WHERE c = 1 OR t1.a = 1 AND 1 = 2) GROUP BY b ); 1 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: 'a' +Warning 1292 Truncated incorrect DOUBLE value: 'b' +Warning 1292 Truncated incorrect DOUBLE value: 'a' +Warning 1292 Truncated incorrect DOUBLE value: 'b' DROP TABLE t1, t2; # # BUG#12616253 - WRONG RESULT WITH EXISTS(SUBQUERY) (MISSING ROWS) diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result index 9b38ab43d26..9a43e64554e 100644 --- a/mysql-test/r/subselect_no_opts.result +++ b/mysql-test/r/subselect_no_opts.result @@ -6303,12 +6303,22 @@ SELECT 1 FROM t1 WHERE a = ORDER BY b ); 1 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: 'a' +Warning 1292 Truncated incorrect DOUBLE value: 'b' +Warning 1292 Truncated incorrect DOUBLE value: 'a' +Warning 1292 Truncated incorrect DOUBLE value: 'b' SELECT 1 FROM t1 WHERE a = (SELECT 1 FROM t2 WHERE b = (SELECT 1 FROM t1 t11 WHERE c = 1 OR t1.a = 1 AND 1 = 2) GROUP BY b ); 1 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: 'a' +Warning 1292 Truncated incorrect DOUBLE value: 'b' +Warning 1292 Truncated incorrect DOUBLE value: 'a' +Warning 1292 Truncated incorrect DOUBLE value: 'b' DROP TABLE t1, t2; # # BUG#12616253 - WRONG RESULT WITH EXISTS(SUBQUERY) (MISSING ROWS) diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result index 672999922fa..0a88e26bd2d 100644 --- a/mysql-test/r/subselect_no_scache.result +++ b/mysql-test/r/subselect_no_scache.result @@ -6318,12 +6318,22 @@ SELECT 1 FROM t1 WHERE a = ORDER BY b ); 1 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: 'a' +Warning 1292 Truncated incorrect DOUBLE value: 'b' +Warning 1292 Truncated incorrect DOUBLE value: 'a' +Warning 1292 Truncated incorrect DOUBLE value: 'b' SELECT 1 FROM t1 WHERE a = (SELECT 1 FROM t2 WHERE b = (SELECT 1 FROM t1 t11 WHERE c = 1 OR t1.a = 1 AND 1 = 2) GROUP BY b ); 1 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: 'a' +Warning 1292 Truncated incorrect DOUBLE value: 'b' +Warning 1292 Truncated incorrect DOUBLE value: 'a' +Warning 1292 Truncated incorrect DOUBLE value: 'b' DROP TABLE t1, t2; # # BUG#12616253 - WRONG RESULT WITH EXISTS(SUBQUERY) (MISSING ROWS) diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result index 5247de1144e..142f9f679bf 100644 --- a/mysql-test/r/subselect_no_semijoin.result +++ b/mysql-test/r/subselect_no_semijoin.result @@ -6303,12 +6303,22 @@ SELECT 1 FROM t1 WHERE a = ORDER BY b ); 1 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: 'a' +Warning 1292 Truncated incorrect DOUBLE value: 'b' +Warning 1292 Truncated incorrect DOUBLE value: 'a' +Warning 1292 Truncated incorrect DOUBLE value: 'b' SELECT 1 FROM t1 WHERE a = (SELECT 1 FROM t2 WHERE b = (SELECT 1 FROM t1 t11 WHERE c = 1 OR t1.a = 1 AND 1 = 2) GROUP BY b ); 1 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: 'a' +Warning 1292 Truncated incorrect DOUBLE value: 'b' +Warning 1292 Truncated incorrect DOUBLE value: 'a' +Warning 1292 Truncated incorrect DOUBLE value: 'b' DROP TABLE t1, t2; # # BUG#12616253 - WRONG RESULT WITH EXISTS(SUBQUERY) (MISSING ROWS) diff --git a/mysql-test/r/trigger.result b/mysql-test/r/trigger.result index e71090b8528..9dfa5897af1 100644 --- a/mysql-test/r/trigger.result +++ b/mysql-test/r/trigger.result @@ -415,6 +415,13 @@ i j k @b 15 NULL 15 Fired set @b:=""; load data infile '../../std_data/loaddata5.dat' into table t1 fields terminated by '' enclosed by '' (i, j); +Warnings: +Note 1265 Data truncated for column 'i' at row 1 +Note 1265 Data truncated for column 'j' at row 1 +Note 1265 Data truncated for column 'i' at row 2 +Note 1265 Data truncated for column 'j' at row 2 +Note 1265 Data truncated for column 'i' at row 3 +Note 1265 Data truncated for column 'j' at row 3 select *, @b from t1; i j k @b 10 NULL 10 Fired diff --git a/mysql-test/r/type_date.result b/mysql-test/r/type_date.result index aa9a8193d56..1de9b989887 100644 --- a/mysql-test/r/type_date.result +++ b/mysql-test/r/type_date.result @@ -748,5 +748,59 @@ Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` DROP TABLE t1; SET timestamp=DEFAULT; # +# MDEV-8795 Equal expression propagation does not work for temporal literals +# +CREATE TABLE t1 (a DATE); +INSERT INTO t1 VALUES ('2001-01-01'),('2001-01-02'); +EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(a)=DATE'2001-01-01' AND COALESCE(a)>=DATE'2001-01-01'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (coalesce(`test`.`t1`.`a`) = DATE'2001-01-01') +DROP TABLE t1; +# +# MDEV-8658 DATE(zerofill_column) and DATE(COALESCE(zerofill_column)) return different results +# +CREATE TABLE t1 (a INT(6) ZEROFILL, b DECIMAL(6) ZEROFILL, c DOUBLE(6,0) ZEROFILL); +INSERT INTO t1 VALUES (1,1,1); +INSERT INTO t1 VALUES (10101,10101,10101); +SELECT DATE(a), DATE(b), DATE(c) FROM t1; +DATE(a) DATE(b) DATE(c) +NULL NULL NULL +2001-01-01 2001-01-01 2001-01-01 +Warnings: +Warning 1292 Incorrect datetime value: '1' for column 'a' at row 1 +Warning 1292 Incorrect datetime value: '1' for column 'b' at row 1 +Warning 1292 Incorrect datetime value: '1' for column 'c' at row 1 +SELECT DATE(COALESCE(a)), DATE(COALESCE(b)), DATE(COALESCE(c)) FROM t1; +DATE(COALESCE(a)) DATE(COALESCE(b)) DATE(COALESCE(c)) +NULL NULL NULL +2001-01-01 2001-01-01 2001-01-01 +Warnings: +Warning 1292 Incorrect datetime value: '1' +Warning 1292 Incorrect datetime value: '1' +Warning 1292 Incorrect datetime value: '1' +DROP TABLE t1; +CREATE TABLE t1 (a INT(6), b DECIMAL(6), c DOUBLE(6,0)); +INSERT INTO t1 VALUES (1,1,1); +INSERT INTO t1 VALUES (10101,10101,10101); +SELECT DATE(a), DATE(b), DATE(c) FROM t1; +DATE(a) DATE(b) DATE(c) +NULL NULL NULL +2001-01-01 2001-01-01 2001-01-01 +Warnings: +Warning 1292 Incorrect datetime value: '1' for column 'a' at row 1 +Warning 1292 Incorrect datetime value: '1' for column 'b' at row 1 +Warning 1292 Incorrect datetime value: '1' for column 'c' at row 1 +SELECT DATE(COALESCE(a)), DATE(COALESCE(b)), DATE(COALESCE(c)) FROM t1; +DATE(COALESCE(a)) DATE(COALESCE(b)) DATE(COALESCE(c)) +NULL NULL NULL +2001-01-01 2001-01-01 2001-01-01 +Warnings: +Warning 1292 Incorrect datetime value: '1' +Warning 1292 Incorrect datetime value: '1' +Warning 1292 Incorrect datetime value: '1' +DROP TABLE t1; +# # End of 10.1 tests # diff --git a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result index 9b01f3b00ef..a05ba88d175 100644 --- a/mysql-test/r/type_datetime.result +++ b/mysql-test/r/type_datetime.result @@ -57,7 +57,7 @@ select * from t1; t 0000-00-00 00:00:00 drop table t1; -CREATE TABLE t1 (a timestamp, b date, c time, d datetime); +CREATE TABLE t1 (a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, b date, c time, d datetime); insert into t1 (b,c,d) values(now(),curtime(),now()); Warnings: Note 1265 Data truncated for column 'b' at row 1 @@ -229,7 +229,7 @@ ERROR 42000: Invalid default value for 'da' create table t1 (t time default '916:00:00 a'); ERROR 42000: Invalid default value for 't' set @@sql_mode= @org_mode; -create table t1 (f1 date, f2 datetime, f3 timestamp); +create table t1 (f1 date, f2 datetime, f3 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); insert into t1(f1) values(curdate()); select curdate() < now(), f1 < now(), cast(f1 as date) < now() from t1; curdate() < now() f1 < now() cast(f1 as date) < now() @@ -266,7 +266,7 @@ f1 f2 f1 > f2 f1 = f2 f1 < f2 2001-04-15 2001-04-15 00:00:00 0 1 0 2001-05-20 2001-05-20 01:01:01 0 0 1 drop table t1; -create table t1 (f1 date, f2 datetime, f3 timestamp); +create table t1 (f1 date, f2 datetime, f3 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); insert into t1 values('2001-01-01','2001-01-01 01:01:01','2001-01-01 01:01:01'); insert into t1 values('2001-02-05','2001-02-05 00:00:00','2001-02-05 01:01:01'); insert into t1 values('2001-03-10','2001-03-09 01:01:01','2001-03-10 01:01:01'); @@ -1085,5 +1085,16 @@ Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` = TIME'00:00:00') and (<cache>(length(TIMESTAMP'2001-01-01 00:00:00')) = (40 + rand()))) DROP TABLE t1; # +# MDEV-8795 Equal expression propagation does not work for temporal literals +# +CREATE TABLE t1 (a DATETIME); +INSERT INTO t1 VALUES ('2001-01-01 00:00:00'),('2001-01-02 00:00:00'); +EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(a)=TIMESTAMP'2001-01-01 00:00:00' AND COALESCE(a)>=TIMESTAMP'2001-01-01 00:00:00'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (coalesce(`test`.`t1`.`a`) = TIMESTAMP'2001-01-01 00:00:00') +DROP TABLE t1; +# # End of 10.1 tests # diff --git a/mysql-test/r/type_decimal.result b/mysql-test/r/type_decimal.result index cde8816dee4..85ea43752a5 100644 --- a/mysql-test/r/type_decimal.result +++ b/mysql-test/r/type_decimal.result @@ -181,7 +181,7 @@ Note 1265 Data truncated for column 'a' at row 3 Warning 1366 Incorrect decimal value: '1e-9223372036854775809' for column 'a' at row 4 insert into t1 values ("123.4e"),("123.4e+2"),("123.4e-2"),("123e1"),("123e+0"); Warnings: -Note 1265 Data truncated for column 'a' at row 1 +Warning 1265 Data truncated for column 'a' at row 1 Note 1265 Data truncated for column 'a' at row 3 select * from t1; a @@ -246,7 +246,7 @@ Note 1265 Data truncated for column 'a' at row 2 Warning 1264 Out of range value for column 'a' at row 3 insert into t1 values ("123.4e"),("123.4e+2"),("123.4e-2"),("123e1"),("123e+0"); Warnings: -Note 1265 Data truncated for column 'a' at row 1 +Warning 1265 Data truncated for column 'a' at row 1 Note 1265 Data truncated for column 'a' at row 3 select * from t1; a @@ -305,7 +305,7 @@ Note 1265 Data truncated for column 'a' at row 2 Warning 1264 Out of range value for column 'a' at row 3 insert into t1 values ("123.4e"),("123.4e+2"),("123.4e-2"),("123e1"),("123e+0"); Warnings: -Note 1265 Data truncated for column 'a' at row 1 +Warning 1265 Data truncated for column 'a' at row 1 Note 1265 Data truncated for column 'a' at row 3 select * from t1; a @@ -358,6 +358,9 @@ insert into t1 values (123.4e0),(123.4e+2),(123.4e-2),(123e1),(123e+0); Warnings: Note 1265 Data truncated for column 'a' at row 3 insert into t1 values (MID("987",1,2)),("987 "),("987.6e+2 "); +Warnings: +Note 1265 Data truncated for column 'a' at row 2 +Note 1265 Data truncated for column 'a' at row 3 select * from t1; a 0.00 diff --git a/mysql-test/r/type_float.result b/mysql-test/r/type_float.result index 21734b30c8c..e7267f012ae 100644 --- a/mysql-test/r/type_float.result +++ b/mysql-test/r/type_float.result @@ -458,7 +458,7 @@ Warnings: Warning 1265 Data truncated for column 'f' at row 1 INSERT INTO t1 VALUES ('.'); Warnings: -Warning 1265 Data truncated for column 'f' at row 1 +Warning 1366 Incorrect double value: '.' for column 'f' at row 1 SELECT * FROM t1 ORDER BY f; f 0 diff --git a/mysql-test/r/type_newdecimal.result b/mysql-test/r/type_newdecimal.result index b498910f13c..1942d07c9ab 100644 --- a/mysql-test/r/type_newdecimal.result +++ b/mysql-test/r/type_newdecimal.result @@ -2042,33 +2042,19 @@ SELECT t1.* FROM t1 JOIN t2 USING(a); a 10:20:30.000000 10:20:30.000000 -Warnings: -Note 1292 Truncated incorrect time value: '102030.0000000000' -Note 1292 Truncated incorrect time value: '102030.0000000010' SELECT t1.* FROM t1 LEFT JOIN t2 USING(a); a 10:20:30.000000 10:20:30.000000 -Warnings: -Note 1292 Truncated incorrect time value: '102030.0000000000' -Note 1292 Truncated incorrect time value: '102030.0000000000' -Note 1292 Truncated incorrect time value: '102030.0000000010' ALTER TABLE t2 ADD PRIMARY KEY(a); SELECT t1.* FROM t1 JOIN t2 USING(a); a 10:20:30.000000 10:20:30.000000 -Warnings: -Note 1292 Truncated incorrect time value: '102030.0000000000' -Note 1292 Truncated incorrect time value: '102030.0000000010' SELECT t1.* FROM t1 LEFT JOIN t2 USING(a); a 10:20:30.000000 10:20:30.000000 -Warnings: -Note 1292 Truncated incorrect time value: '102030.0000000000' -Note 1292 Truncated incorrect time value: '102030.0000000000' -Note 1292 Truncated incorrect time value: '102030.0000000010' # t2 should NOT be eliminated EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 USING(a); id select_type table type possible_keys key key_len ref rows Extra @@ -2156,5 +2142,14 @@ Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = 2010.0) DROP TABLE t1; # +# MDEV-8635 Redundant warnings on WHERE decimal_column='ax' +# +CREATE TABLE t1 (a DECIMAL, KEY(a)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1),(2),(3),(4),(5); +EXPLAIN SELECT * FROM t1 WHERE a='ax' ORDER BY a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref a a 6 const 1 Using where; Using index; Using filesort +DROP TABLE t1; +# # End of 10.1 tests # diff --git a/mysql-test/r/type_num.result b/mysql-test/r/type_num.result new file mode 100644 index 00000000000..cbac7b6f445 --- /dev/null +++ b/mysql-test/r/type_num.result @@ -0,0 +1,1222 @@ +# +# Start of 10.0 tests +# +# +# MDEV-8468 CAST and INSERT work differently for DECIMAL/INT vs DOUBLE for a string with trailing spaces +# +SET sql_mode='STRICT_ALL_TABLES'; +CREATE TABLE t1 (a FLOAT); +INSERT INTO t1 VALUES ('1 '); +Warnings: +Note 1265 Data truncated for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a DOUBLE); +INSERT INTO t1 VALUES ('1 '); +Warnings: +Note 1265 Data truncated for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a TINYINT); +INSERT INTO t1 VALUES ('1 '); +Warnings: +Note 1265 Data truncated for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a SMALLINT); +INSERT INTO t1 VALUES ('1 '); +Warnings: +Note 1265 Data truncated for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES ('1 '); +Warnings: +Note 1265 Data truncated for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a BIGINT); +INSERT INTO t1 VALUES ('1 '); +Warnings: +Note 1265 Data truncated for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a DECIMAL); +INSERT INTO t1 VALUES ('1 '); +Warnings: +Note 1265 Data truncated for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a FLOAT); +INSERT INTO t1 VALUES (''); +ERROR 22007: Incorrect double value: '' for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a DOUBLE); +INSERT INTO t1 VALUES (''); +ERROR 22007: Incorrect double value: '' for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a TINYINT); +INSERT INTO t1 VALUES (''); +ERROR 22007: Incorrect integer value: '' for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a SMALLINT); +INSERT INTO t1 VALUES (''); +ERROR 22007: Incorrect integer value: '' for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (''); +ERROR 22007: Incorrect integer value: '' for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a BIGINT); +INSERT INTO t1 VALUES (''); +ERROR 22007: Incorrect integer value: '' for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a DECIMAL); +INSERT INTO t1 VALUES (''); +ERROR 22007: Incorrect decimal value: '' for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a FLOAT); +INSERT INTO t1 VALUES ('x'); +ERROR 22007: Incorrect double value: 'x' for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a DOUBLE); +INSERT INTO t1 VALUES ('x'); +ERROR 22007: Incorrect double value: 'x' for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a TINYINT); +INSERT INTO t1 VALUES ('x'); +ERROR 22007: Incorrect integer value: 'x' for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a SMALLINT); +INSERT INTO t1 VALUES ('x'); +ERROR 22007: Incorrect integer value: 'x' for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES ('x'); +ERROR 22007: Incorrect integer value: 'x' for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a BIGINT); +INSERT INTO t1 VALUES ('x'); +ERROR 22007: Incorrect integer value: 'x' for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a DECIMAL); +INSERT INTO t1 VALUES ('x'); +ERROR 22007: Incorrect decimal value: 'x' for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a FLOAT); +INSERT INTO t1 VALUES (' x'); +ERROR 22007: Incorrect double value: ' x' for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a DOUBLE); +INSERT INTO t1 VALUES (' x'); +ERROR 22007: Incorrect double value: ' x' for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a TINYINT); +INSERT INTO t1 VALUES (' x'); +ERROR 22007: Incorrect integer value: ' x' for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a SMALLINT); +INSERT INTO t1 VALUES (' x'); +ERROR 22007: Incorrect integer value: ' x' for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (' x'); +ERROR 22007: Incorrect integer value: ' x' for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a BIGINT); +INSERT INTO t1 VALUES (' x'); +ERROR 22007: Incorrect integer value: ' x' for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a DECIMAL); +INSERT INTO t1 VALUES (' x'); +ERROR 22007: Incorrect decimal value: ' x' for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a FLOAT); +INSERT INTO t1 VALUES ('.'); +ERROR 22007: Incorrect double value: '.' for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a DOUBLE); +INSERT INTO t1 VALUES ('.'); +ERROR 22007: Incorrect double value: '.' for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a TINYINT); +INSERT INTO t1 VALUES ('.'); +ERROR 22007: Incorrect integer value: '.' for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a SMALLINT); +INSERT INTO t1 VALUES ('.'); +ERROR 22007: Incorrect integer value: '.' for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES ('.'); +ERROR 22007: Incorrect integer value: '.' for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a BIGINT); +INSERT INTO t1 VALUES ('.'); +ERROR 22007: Incorrect integer value: '.' for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a DECIMAL); +INSERT INTO t1 VALUES ('.'); +ERROR 22007: Incorrect decimal value: '.' for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a FLOAT); +INSERT INTO t1 VALUES ('-'); +ERROR 22007: Incorrect double value: '-' for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a DOUBLE); +INSERT INTO t1 VALUES ('-'); +ERROR 22007: Incorrect double value: '-' for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a TINYINT); +INSERT INTO t1 VALUES ('-'); +ERROR 22007: Incorrect integer value: '-' for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a SMALLINT); +INSERT INTO t1 VALUES ('-'); +ERROR 22007: Incorrect integer value: '-' for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES ('-'); +ERROR 22007: Incorrect integer value: '-' for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a BIGINT); +INSERT INTO t1 VALUES ('-'); +ERROR 22007: Incorrect integer value: '-' for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a DECIMAL); +INSERT INTO t1 VALUES ('-'); +ERROR 22007: Incorrect decimal value: '-' for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a FLOAT); +INSERT INTO t1 VALUES ('+'); +ERROR 22007: Incorrect double value: '+' for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a DOUBLE); +INSERT INTO t1 VALUES ('+'); +ERROR 22007: Incorrect double value: '+' for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a TINYINT); +INSERT INTO t1 VALUES ('+'); +ERROR 22007: Incorrect integer value: '+' for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a SMALLINT); +INSERT INTO t1 VALUES ('+'); +ERROR 22007: Incorrect integer value: '+' for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES ('+'); +ERROR 22007: Incorrect integer value: '+' for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a BIGINT); +INSERT INTO t1 VALUES ('+'); +ERROR 22007: Incorrect integer value: '+' for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a DECIMAL); +INSERT INTO t1 VALUES ('+'); +ERROR 22007: Incorrect decimal value: '+' for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a FLOAT); +INSERT INTO t1 VALUES ('1x'); +ERROR 01000: Data truncated for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a DOUBLE); +INSERT INTO t1 VALUES ('1x'); +ERROR 01000: Data truncated for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a TINYINT); +INSERT INTO t1 VALUES ('1x'); +ERROR 01000: Data truncated for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a SMALLINT); +INSERT INTO t1 VALUES ('1x'); +ERROR 01000: Data truncated for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES ('1x'); +ERROR 01000: Data truncated for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a BIGINT); +INSERT INTO t1 VALUES ('1x'); +ERROR 01000: Data truncated for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a DECIMAL); +INSERT INTO t1 VALUES ('1x'); +ERROR 01000: Data truncated for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a FLOAT); +INSERT INTO t1 VALUES ('1e'); +ERROR 01000: Data truncated for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a DOUBLE); +INSERT INTO t1 VALUES ('1e'); +ERROR 01000: Data truncated for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a TINYINT); +INSERT INTO t1 VALUES ('1e'); +ERROR 01000: Data truncated for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a SMALLINT); +INSERT INTO t1 VALUES ('1e'); +ERROR 01000: Data truncated for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES ('1e'); +ERROR 01000: Data truncated for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a BIGINT); +INSERT INTO t1 VALUES ('1e'); +ERROR 01000: Data truncated for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a DECIMAL); +INSERT INTO t1 VALUES ('1e'); +ERROR 01000: Data truncated for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a FLOAT); +INSERT INTO t1 VALUES ('1e+'); +ERROR 01000: Data truncated for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a DOUBLE); +INSERT INTO t1 VALUES ('1e+'); +ERROR 01000: Data truncated for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a TINYINT); +INSERT INTO t1 VALUES ('1e+'); +ERROR 01000: Data truncated for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a SMALLINT); +INSERT INTO t1 VALUES ('1e+'); +ERROR 01000: Data truncated for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES ('1e+'); +ERROR 01000: Data truncated for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a BIGINT); +INSERT INTO t1 VALUES ('1e+'); +ERROR 01000: Data truncated for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a DECIMAL); +INSERT INTO t1 VALUES ('1e+'); +ERROR 01000: Data truncated for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a FLOAT); +INSERT INTO t1 VALUES ('1E-'); +ERROR 01000: Data truncated for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a DOUBLE); +INSERT INTO t1 VALUES ('1E-'); +ERROR 01000: Data truncated for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a TINYINT); +INSERT INTO t1 VALUES ('1E-'); +ERROR 01000: Data truncated for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a SMALLINT); +INSERT INTO t1 VALUES ('1E-'); +ERROR 01000: Data truncated for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES ('1E-'); +ERROR 01000: Data truncated for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a BIGINT); +INSERT INTO t1 VALUES ('1E-'); +ERROR 01000: Data truncated for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a DECIMAL); +INSERT INTO t1 VALUES ('1E-'); +ERROR 01000: Data truncated for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a FLOAT); +INSERT INTO t1 VALUES ('1Ex'); +ERROR 01000: Data truncated for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a DOUBLE); +INSERT INTO t1 VALUES ('1Ex'); +ERROR 01000: Data truncated for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a TINYINT); +INSERT INTO t1 VALUES ('1Ex'); +ERROR 01000: Data truncated for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a SMALLINT); +INSERT INTO t1 VALUES ('1Ex'); +ERROR 01000: Data truncated for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES ('1Ex'); +ERROR 01000: Data truncated for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a BIGINT); +INSERT INTO t1 VALUES ('1Ex'); +ERROR 01000: Data truncated for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a DECIMAL); +INSERT INTO t1 VALUES ('1Ex'); +ERROR 01000: Data truncated for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a FLOAT); +INSERT INTO t1 VALUES ('1e+x'); +ERROR 01000: Data truncated for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a DOUBLE); +INSERT INTO t1 VALUES ('1e+x'); +ERROR 01000: Data truncated for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a TINYINT); +INSERT INTO t1 VALUES ('1e+x'); +ERROR 01000: Data truncated for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a SMALLINT); +INSERT INTO t1 VALUES ('1e+x'); +ERROR 01000: Data truncated for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES ('1e+x'); +ERROR 01000: Data truncated for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a BIGINT); +INSERT INTO t1 VALUES ('1e+x'); +ERROR 01000: Data truncated for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a DECIMAL); +INSERT INTO t1 VALUES ('1e+x'); +ERROR 01000: Data truncated for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a FLOAT); +INSERT INTO t1 VALUES ('1e1000'); +ERROR 22003: Out of range value for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a DOUBLE); +INSERT INTO t1 VALUES ('1e1000'); +ERROR 22003: Out of range value for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a TINYINT); +INSERT INTO t1 VALUES ('1e1000'); +ERROR 22003: Out of range value for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a SMALLINT); +INSERT INTO t1 VALUES ('1e1000'); +ERROR 22003: Out of range value for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES ('1e1000'); +ERROR 22003: Out of range value for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a BIGINT); +INSERT INTO t1 VALUES ('1e1000'); +ERROR 22003: Out of range value for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a DECIMAL); +INSERT INTO t1 VALUES ('1e1000'); +ERROR 22003: Out of range value for column 'a' at row 1 +DROP TABLE t1; +SET sql_mode=DEFAULT; +CREATE TABLE t1 ( +f4 FLOAT, +f8 DOUBLE, +i1 TINYINT, +i2 SMALLINT, +i4 INT, +i8 BIGINT, +d DECIMAL +); +INSERT INTO t1 VALUES ('1 ','1 ','1 ','1 ','1 ','1 ','1 '); +Warnings: +Note 1265 Data truncated for column 'f4' at row 1 +Note 1265 Data truncated for column 'f8' at row 1 +Note 1265 Data truncated for column 'i1' at row 1 +Note 1265 Data truncated for column 'i2' at row 1 +Note 1265 Data truncated for column 'i4' at row 1 +Note 1265 Data truncated for column 'i8' at row 1 +Note 1265 Data truncated for column 'd' at row 1 +INSERT INTO t1 VALUES ('','','','','','',''); +Warnings: +Warning 1366 Incorrect double value: '' for column 'f4' at row 1 +Warning 1366 Incorrect double value: '' for column 'f8' at row 1 +Warning 1366 Incorrect integer value: '' for column 'i1' at row 1 +Warning 1366 Incorrect integer value: '' for column 'i2' at row 1 +Warning 1366 Incorrect integer value: '' for column 'i4' at row 1 +Warning 1366 Incorrect integer value: '' for column 'i8' at row 1 +Warning 1366 Incorrect decimal value: '' for column 'd' at row 1 +INSERT INTO t1 VALUES ('x','x','x','x','x','x','x'); +Warnings: +Warning 1366 Incorrect double value: 'x' for column 'f4' at row 1 +Warning 1366 Incorrect double value: 'x' for column 'f8' at row 1 +Warning 1366 Incorrect integer value: 'x' for column 'i1' at row 1 +Warning 1366 Incorrect integer value: 'x' for column 'i2' at row 1 +Warning 1366 Incorrect integer value: 'x' for column 'i4' at row 1 +Warning 1366 Incorrect integer value: 'x' for column 'i8' at row 1 +Warning 1366 Incorrect decimal value: 'x' for column 'd' at row 1 +INSERT INTO t1 VALUES (' x',' x',' x',' x',' x',' x',' x'); +Warnings: +Warning 1366 Incorrect double value: ' x' for column 'f4' at row 1 +Warning 1366 Incorrect double value: ' x' for column 'f8' at row 1 +Warning 1366 Incorrect integer value: ' x' for column 'i1' at row 1 +Warning 1366 Incorrect integer value: ' x' for column 'i2' at row 1 +Warning 1366 Incorrect integer value: ' x' for column 'i4' at row 1 +Warning 1366 Incorrect integer value: ' x' for column 'i8' at row 1 +Warning 1366 Incorrect decimal value: ' x' for column 'd' at row 1 +INSERT INTO t1 VALUES ('.','.','.','.','.','.','.'); +Warnings: +Warning 1366 Incorrect double value: '.' for column 'f4' at row 1 +Warning 1366 Incorrect double value: '.' for column 'f8' at row 1 +Warning 1366 Incorrect integer value: '.' for column 'i1' at row 1 +Warning 1366 Incorrect integer value: '.' for column 'i2' at row 1 +Warning 1366 Incorrect integer value: '.' for column 'i4' at row 1 +Warning 1366 Incorrect integer value: '.' for column 'i8' at row 1 +Warning 1366 Incorrect decimal value: '.' for column 'd' at row 1 +INSERT INTO t1 VALUES ('-','-','-','-','-','-','-'); +Warnings: +Warning 1366 Incorrect double value: '-' for column 'f4' at row 1 +Warning 1366 Incorrect double value: '-' for column 'f8' at row 1 +Warning 1366 Incorrect integer value: '-' for column 'i1' at row 1 +Warning 1366 Incorrect integer value: '-' for column 'i2' at row 1 +Warning 1366 Incorrect integer value: '-' for column 'i4' at row 1 +Warning 1366 Incorrect integer value: '-' for column 'i8' at row 1 +Warning 1366 Incorrect decimal value: '-' for column 'd' at row 1 +INSERT INTO t1 VALUES ('+','+','+','+','+','+','+'); +Warnings: +Warning 1366 Incorrect double value: '+' for column 'f4' at row 1 +Warning 1366 Incorrect double value: '+' for column 'f8' at row 1 +Warning 1366 Incorrect integer value: '+' for column 'i1' at row 1 +Warning 1366 Incorrect integer value: '+' for column 'i2' at row 1 +Warning 1366 Incorrect integer value: '+' for column 'i4' at row 1 +Warning 1366 Incorrect integer value: '+' for column 'i8' at row 1 +Warning 1366 Incorrect decimal value: '+' for column 'd' at row 1 +INSERT INTO t1 VALUES ('1x','1x','1x','1x','1x','1x','1x'); +Warnings: +Warning 1265 Data truncated for column 'f4' at row 1 +Warning 1265 Data truncated for column 'f8' at row 1 +Warning 1265 Data truncated for column 'i1' at row 1 +Warning 1265 Data truncated for column 'i2' at row 1 +Warning 1265 Data truncated for column 'i4' at row 1 +Warning 1265 Data truncated for column 'i8' at row 1 +Warning 1265 Data truncated for column 'd' at row 1 +INSERT INTO t1 VALUES ('1e','1e','1e','1e','1e','1e','1e'); +Warnings: +Warning 1265 Data truncated for column 'f4' at row 1 +Warning 1265 Data truncated for column 'f8' at row 1 +Warning 1265 Data truncated for column 'i1' at row 1 +Warning 1265 Data truncated for column 'i2' at row 1 +Warning 1265 Data truncated for column 'i4' at row 1 +Warning 1265 Data truncated for column 'i8' at row 1 +Warning 1265 Data truncated for column 'd' at row 1 +INSERT INTO t1 VALUES ('1e-','1e-','1e-','1e-','1e-','1e-','1e-'); +Warnings: +Warning 1265 Data truncated for column 'f4' at row 1 +Warning 1265 Data truncated for column 'f8' at row 1 +Warning 1265 Data truncated for column 'i1' at row 1 +Warning 1265 Data truncated for column 'i2' at row 1 +Warning 1265 Data truncated for column 'i4' at row 1 +Warning 1265 Data truncated for column 'i8' at row 1 +Warning 1265 Data truncated for column 'd' at row 1 +INSERT INTO t1 VALUES ('1E+','1E+','1E+','1E+','1E+','1E+','1E+'); +Warnings: +Warning 1265 Data truncated for column 'f4' at row 1 +Warning 1265 Data truncated for column 'f8' at row 1 +Warning 1265 Data truncated for column 'i1' at row 1 +Warning 1265 Data truncated for column 'i2' at row 1 +Warning 1265 Data truncated for column 'i4' at row 1 +Warning 1265 Data truncated for column 'i8' at row 1 +Warning 1265 Data truncated for column 'd' at row 1 +INSERT INTO t1 VALUES ('1e1000','1e1000','1e1000','1e1000','1e1000','1e1000','1e1000'); +Warnings: +Warning 1264 Out of range value for column 'f4' at row 1 +Warning 1264 Out of range value for column 'f4' at row 1 +Warning 1264 Out of range value for column 'f8' at row 1 +Warning 1264 Out of range value for column 'i1' at row 1 +Warning 1264 Out of range value for column 'i2' at row 1 +Warning 1264 Out of range value for column 'i4' at row 1 +Warning 1264 Out of range value for column 'i8' at row 1 +Warning 1264 Out of range value for column 'd' at row 1 +DELETE FROM t1; +INSERT INTO t1 VALUES (0,0,0,0,0,0,0); +INSERT INTO t1 VALUES (0,0,0,0,0,0,0); +INSERT INTO t1 VALUES (1,1,1,1,1,1,1); +INSERT INTO t1 VALUES (1,1,1,1,1,1,1); +INSERT INTO t1 VALUES (2,2,2,2,2,2,2); +INSERT INTO t1 VALUES (2,2,2,2,2,2,2); +INSERT INTO t1 VALUES (3,3,3,3,3,3,3); +INSERT INTO t1 VALUES (3,3,3,3,3,3,3); +INSERT INTO t1 VALUES (4,4,4,4,4,4,4); +INSERT INTO t1 VALUES (4,4,4,4,4,4,4); +INSERT INTO t1 VALUES (5,5,5,5,5,5,5); +INSERT INTO t1 VALUES (5,5,5,5,5,5,5); +SELECT COUNT(*) FROM t1 WHERE f4='1 '; +COUNT(*) +2 +Warnings: +Note 1292 Truncated incorrect DOUBLE value: '1 ' +SELECT COUNT(*) FROM t1 WHERE f8='1 '; +COUNT(*) +2 +Warnings: +Note 1292 Truncated incorrect DOUBLE value: '1 ' +SELECT COUNT(*) FROM t1 WHERE i1='1 '; +COUNT(*) +2 +Warnings: +Note 1292 Truncated incorrect DOUBLE value: '1 ' +SELECT COUNT(*) FROM t1 WHERE i2='1 '; +COUNT(*) +2 +Warnings: +Note 1292 Truncated incorrect DOUBLE value: '1 ' +SELECT COUNT(*) FROM t1 WHERE i4='1 '; +COUNT(*) +2 +Warnings: +Note 1292 Truncated incorrect DOUBLE value: '1 ' +SELECT COUNT(*) FROM t1 WHERE i8='1 '; +COUNT(*) +2 +Warnings: +Note 1292 Truncated incorrect DOUBLE value: '1 ' +SELECT COUNT(*) FROM t1 WHERE d='1 '; +COUNT(*) +2 +Warnings: +Note 1292 Truncated incorrect DOUBLE value: '1 ' +SELECT COUNT(*) FROM t1 WHERE f4=''; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '' +SELECT COUNT(*) FROM t1 WHERE f8=''; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '' +SELECT COUNT(*) FROM t1 WHERE i1=''; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '' +SELECT COUNT(*) FROM t1 WHERE i2=''; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '' +SELECT COUNT(*) FROM t1 WHERE i4=''; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '' +SELECT COUNT(*) FROM t1 WHERE i8=''; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '' +SELECT COUNT(*) FROM t1 WHERE d=''; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '' +SELECT COUNT(*) FROM t1 WHERE f4='x'; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: 'x' +SELECT COUNT(*) FROM t1 WHERE f8='x'; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: 'x' +SELECT COUNT(*) FROM t1 WHERE i1='x'; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: 'x' +SELECT COUNT(*) FROM t1 WHERE i2='x'; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: 'x' +SELECT COUNT(*) FROM t1 WHERE i4='x'; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: 'x' +SELECT COUNT(*) FROM t1 WHERE i8='x'; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: 'x' +SELECT COUNT(*) FROM t1 WHERE d='x'; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: 'x' +SELECT COUNT(*) FROM t1 WHERE f4=' x'; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: ' x' +SELECT COUNT(*) FROM t1 WHERE f8=' x'; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: ' x' +SELECT COUNT(*) FROM t1 WHERE i1=' x'; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: ' x' +SELECT COUNT(*) FROM t1 WHERE i2=' x'; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: ' x' +SELECT COUNT(*) FROM t1 WHERE i4=' x'; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: ' x' +SELECT COUNT(*) FROM t1 WHERE i8=' x'; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: ' x' +SELECT COUNT(*) FROM t1 WHERE d=' x'; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: ' x' +SELECT COUNT(*) FROM t1 WHERE f4='.'; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '.' +SELECT COUNT(*) FROM t1 WHERE f8='.'; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '.' +SELECT COUNT(*) FROM t1 WHERE i1='.'; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '.' +SELECT COUNT(*) FROM t1 WHERE i2='.'; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '.' +SELECT COUNT(*) FROM t1 WHERE i4='.'; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '.' +SELECT COUNT(*) FROM t1 WHERE i8='.'; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '.' +SELECT COUNT(*) FROM t1 WHERE d='.'; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '.' +SELECT COUNT(*) FROM t1 WHERE f4='-'; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '-' +SELECT COUNT(*) FROM t1 WHERE f8='-'; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '-' +SELECT COUNT(*) FROM t1 WHERE i1='-'; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '-' +SELECT COUNT(*) FROM t1 WHERE i2='-'; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '-' +SELECT COUNT(*) FROM t1 WHERE i4='-'; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '-' +SELECT COUNT(*) FROM t1 WHERE i8='-'; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '-' +SELECT COUNT(*) FROM t1 WHERE d='-'; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '-' +SELECT COUNT(*) FROM t1 WHERE f4='+'; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '+' +SELECT COUNT(*) FROM t1 WHERE f8='+'; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '+' +SELECT COUNT(*) FROM t1 WHERE i1='+'; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '+' +SELECT COUNT(*) FROM t1 WHERE i2='+'; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '+' +SELECT COUNT(*) FROM t1 WHERE i4='+'; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '+' +SELECT COUNT(*) FROM t1 WHERE i8='+'; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '+' +SELECT COUNT(*) FROM t1 WHERE d='+'; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '+' +SELECT COUNT(*) FROM t1 WHERE f4='1x'; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '1x' +SELECT COUNT(*) FROM t1 WHERE f8='1x'; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '1x' +SELECT COUNT(*) FROM t1 WHERE i1='1x'; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '1x' +SELECT COUNT(*) FROM t1 WHERE i2='1x'; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '1x' +SELECT COUNT(*) FROM t1 WHERE i4='1x'; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '1x' +SELECT COUNT(*) FROM t1 WHERE i8='1x'; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '1x' +SELECT COUNT(*) FROM t1 WHERE d='1x'; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '1x' +SELECT COUNT(*) FROM t1 WHERE f4='1e'; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '1e' +SELECT COUNT(*) FROM t1 WHERE f8='1e'; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '1e' +SELECT COUNT(*) FROM t1 WHERE i1='1e'; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '1e' +SELECT COUNT(*) FROM t1 WHERE i2='1e'; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '1e' +SELECT COUNT(*) FROM t1 WHERE i4='1e'; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '1e' +SELECT COUNT(*) FROM t1 WHERE i8='1e'; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '1e' +SELECT COUNT(*) FROM t1 WHERE d='1e'; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '1e' +SELECT COUNT(*) FROM t1 WHERE f4='1e+'; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '1e+' +SELECT COUNT(*) FROM t1 WHERE f8='1e+'; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '1e+' +SELECT COUNT(*) FROM t1 WHERE i1='1e+'; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '1e+' +SELECT COUNT(*) FROM t1 WHERE i2='1e+'; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '1e+' +SELECT COUNT(*) FROM t1 WHERE i4='1e+'; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '1e+' +SELECT COUNT(*) FROM t1 WHERE i8='1e+'; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '1e+' +SELECT COUNT(*) FROM t1 WHERE d='1e+'; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '1e+' +SELECT COUNT(*) FROM t1 WHERE f4='1E-'; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '1E-' +SELECT COUNT(*) FROM t1 WHERE f8='1E-'; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '1E-' +SELECT COUNT(*) FROM t1 WHERE i1='1E-'; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '1E-' +SELECT COUNT(*) FROM t1 WHERE i2='1E-'; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '1E-' +SELECT COUNT(*) FROM t1 WHERE i4='1E-'; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '1E-' +SELECT COUNT(*) FROM t1 WHERE i8='1E-'; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '1E-' +SELECT COUNT(*) FROM t1 WHERE d='1E-'; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '1E-' +SELECT COUNT(*) FROM t1 WHERE f4='1Ex'; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '1Ex' +SELECT COUNT(*) FROM t1 WHERE f8='1Ex'; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '1Ex' +SELECT COUNT(*) FROM t1 WHERE i1='1Ex'; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '1Ex' +SELECT COUNT(*) FROM t1 WHERE i2='1Ex'; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '1Ex' +SELECT COUNT(*) FROM t1 WHERE i4='1Ex'; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '1Ex' +SELECT COUNT(*) FROM t1 WHERE i8='1Ex'; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '1Ex' +SELECT COUNT(*) FROM t1 WHERE d='1Ex'; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '1Ex' +SELECT COUNT(*) FROM t1 WHERE f4='1e+x'; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '1e+x' +SELECT COUNT(*) FROM t1 WHERE f8='1e+x'; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '1e+x' +SELECT COUNT(*) FROM t1 WHERE i1='1e+x'; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '1e+x' +SELECT COUNT(*) FROM t1 WHERE i2='1e+x'; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '1e+x' +SELECT COUNT(*) FROM t1 WHERE i4='1e+x'; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '1e+x' +SELECT COUNT(*) FROM t1 WHERE i8='1e+x'; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '1e+x' +SELECT COUNT(*) FROM t1 WHERE d='1e+x'; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '1e+x' +SELECT COUNT(*) FROM t1 WHERE f4='1e1000'; +COUNT(*) +0 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '1e1000' +SELECT COUNT(*) FROM t1 WHERE f8='1e1000'; +COUNT(*) +0 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '1e1000' +SELECT COUNT(*) FROM t1 WHERE i1='1e1000'; +COUNT(*) +0 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '1e1000' +SELECT COUNT(*) FROM t1 WHERE i2='1e1000'; +COUNT(*) +0 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '1e1000' +SELECT COUNT(*) FROM t1 WHERE i4='1e1000'; +COUNT(*) +0 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '1e1000' +SELECT COUNT(*) FROM t1 WHERE i8='1e1000'; +COUNT(*) +0 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '1e1000' +SELECT COUNT(*) FROM t1 WHERE d='1e1000'; +COUNT(*) +0 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '1e1000' +ALTER TABLE t1 +ADD KEY f4(f4), +ADD KEY f8(f8), +ADD KEY i1(i1), +ADD KEY i2(i2), +ADD KEY i4(i4), +ADD KEY i8(i8), +ADD KEY d(d); +SELECT COUNT(*) FROM t1 FORCE INDEX(f4) WHERE f4='1 '; +COUNT(*) +2 +Warnings: +Note 1292 Truncated incorrect DOUBLE value: '1 ' +SELECT COUNT(*) FROM t1 FORCE INDEX(f8) WHERE f8='1 '; +COUNT(*) +2 +SELECT COUNT(*) FROM t1 FORCE INDEX(i1) WHERE i1='1 '; +COUNT(*) +2 +SELECT COUNT(*) FROM t1 FORCE INDEX(i2) WHERE i2='1 '; +COUNT(*) +2 +SELECT COUNT(*) FROM t1 FORCE INDEX(i4) WHERE i4='1 '; +COUNT(*) +2 +SELECT COUNT(*) FROM t1 FORCE INDEX(i8) WHERE i8='1 '; +COUNT(*) +2 +Warnings: +Note 1292 Truncated incorrect DOUBLE value: '1 ' +SELECT COUNT(*) FROM t1 FORCE INDEX(d) WHERE d='1 '; +COUNT(*) +2 +SELECT COUNT(*) FROM t1 FORCE INDEX(f4) WHERE f4=''; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '' +SELECT COUNT(*) FROM t1 FORCE INDEX(f8) WHERE f8=''; +COUNT(*) +2 +SELECT COUNT(*) FROM t1 FORCE INDEX(i1) WHERE i1=''; +COUNT(*) +2 +SELECT COUNT(*) FROM t1 FORCE INDEX(i2) WHERE i2=''; +COUNT(*) +2 +SELECT COUNT(*) FROM t1 FORCE INDEX(i4) WHERE i4=''; +COUNT(*) +2 +SELECT COUNT(*) FROM t1 FORCE INDEX(i8) WHERE i8=''; +COUNT(*) +2 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '' +SELECT COUNT(*) FROM t1 FORCE INDEX(d) WHERE d=''; +COUNT(*) +2 +DROP TABLE t1; +SELECT +CAST('1 ' AS SIGNED), +CAST('1 ' AS UNSIGNED), +CAST('1 ' AS DECIMAL), +CAST('1 ' AS DOUBLE); +CAST('1 ' AS SIGNED) CAST('1 ' AS UNSIGNED) CAST('1 ' AS DECIMAL) CAST('1 ' AS DOUBLE) +1 1 1 1 +Warnings: +Note 1292 Truncated incorrect INTEGER value: '1 ' +Note 1292 Truncated incorrect INTEGER value: '1 ' +Note 1292 Truncated incorrect DECIMAL value: '1 ' +Note 1292 Truncated incorrect DOUBLE value: '1 ' +SELECT +CAST('' AS SIGNED), +CAST('' AS UNSIGNED), +CAST('' AS DECIMAL), +CAST('' AS DOUBLE); +CAST('' AS SIGNED) CAST('' AS UNSIGNED) CAST('' AS DECIMAL) CAST('' AS DOUBLE) +0 0 0 0 +Warnings: +Warning 1292 Truncated incorrect INTEGER value: '' +Warning 1292 Truncated incorrect INTEGER value: '' +Warning 1292 Truncated incorrect DECIMAL value: '' +Warning 1292 Truncated incorrect DOUBLE value: '' +SELECT +CAST('x' AS SIGNED), +CAST('x' AS UNSIGNED), +CAST('x' AS DECIMAL), +CAST('x' AS DOUBLE); +CAST('x' AS SIGNED) CAST('x' AS UNSIGNED) CAST('x' AS DECIMAL) CAST('x' AS DOUBLE) +0 0 0 0 +Warnings: +Warning 1292 Truncated incorrect INTEGER value: 'x' +Warning 1292 Truncated incorrect INTEGER value: 'x' +Warning 1292 Truncated incorrect DECIMAL value: 'x' +Warning 1292 Truncated incorrect DOUBLE value: 'x' +SELECT +CAST(' x' AS SIGNED), +CAST(' x' AS UNSIGNED), +CAST(' x' AS DECIMAL), +CAST(' x' AS DOUBLE); +CAST(' x' AS SIGNED) CAST(' x' AS UNSIGNED) CAST(' x' AS DECIMAL) CAST(' x' AS DOUBLE) +0 0 0 0 +Warnings: +Warning 1292 Truncated incorrect INTEGER value: ' x' +Warning 1292 Truncated incorrect INTEGER value: ' x' +Warning 1292 Truncated incorrect DECIMAL value: ' x' +Warning 1292 Truncated incorrect DOUBLE value: ' x' +SELECT +CAST('.' AS SIGNED), +CAST('.' AS UNSIGNED), +CAST('.' AS DECIMAL), +CAST('.' AS DOUBLE); +CAST('.' AS SIGNED) CAST('.' AS UNSIGNED) CAST('.' AS DECIMAL) CAST('.' AS DOUBLE) +0 0 0 0 +Warnings: +Warning 1292 Truncated incorrect INTEGER value: '.' +Warning 1292 Truncated incorrect INTEGER value: '.' +Warning 1292 Truncated incorrect DECIMAL value: '.' +Warning 1292 Truncated incorrect DOUBLE value: '.' +SELECT +CAST('-' AS SIGNED), +CAST('-' AS UNSIGNED), +CAST('-' AS DECIMAL), +CAST('-' AS DOUBLE); +CAST('-' AS SIGNED) CAST('-' AS UNSIGNED) CAST('-' AS DECIMAL) CAST('-' AS DOUBLE) +0 0 0 0 +Warnings: +Warning 1292 Truncated incorrect INTEGER value: '-' +Warning 1292 Truncated incorrect INTEGER value: '-' +Warning 1292 Truncated incorrect DECIMAL value: '-' +Warning 1292 Truncated incorrect DOUBLE value: '-' +SELECT +CAST('+' AS SIGNED), +CAST('+' AS UNSIGNED), +CAST('+' AS DECIMAL), +CAST('+' AS DOUBLE); +CAST('+' AS SIGNED) CAST('+' AS UNSIGNED) CAST('+' AS DECIMAL) CAST('+' AS DOUBLE) +0 0 0 0 +Warnings: +Warning 1292 Truncated incorrect INTEGER value: '+' +Warning 1292 Truncated incorrect INTEGER value: '+' +Warning 1292 Truncated incorrect DECIMAL value: '+' +Warning 1292 Truncated incorrect DOUBLE value: '+' +SELECT +CAST('1x' AS SIGNED), +CAST('1x' AS UNSIGNED), +CAST('1x' AS DECIMAL), +CAST('1x' AS DOUBLE); +CAST('1x' AS SIGNED) CAST('1x' AS UNSIGNED) CAST('1x' AS DECIMAL) CAST('1x' AS DOUBLE) +1 1 1 1 +Warnings: +Warning 1292 Truncated incorrect INTEGER value: '1x' +Warning 1292 Truncated incorrect INTEGER value: '1x' +Warning 1292 Truncated incorrect DECIMAL value: '1x' +Warning 1292 Truncated incorrect DOUBLE value: '1x' +SELECT +CAST('1e' AS SIGNED), +CAST('1e' AS UNSIGNED), +CAST('1e' AS DECIMAL), +CAST('1e' AS DOUBLE); +CAST('1e' AS SIGNED) CAST('1e' AS UNSIGNED) CAST('1e' AS DECIMAL) CAST('1e' AS DOUBLE) +1 1 1 1 +Warnings: +Warning 1292 Truncated incorrect INTEGER value: '1e' +Warning 1292 Truncated incorrect INTEGER value: '1e' +Warning 1292 Truncated incorrect DECIMAL value: '1e' +Warning 1292 Truncated incorrect DOUBLE value: '1e' +SELECT +CAST('1e-' AS SIGNED), +CAST('1e-' AS UNSIGNED), +CAST('1e-' AS DECIMAL), +CAST('1e-' AS DOUBLE); +CAST('1e-' AS SIGNED) CAST('1e-' AS UNSIGNED) CAST('1e-' AS DECIMAL) CAST('1e-' AS DOUBLE) +1 1 1 1 +Warnings: +Warning 1292 Truncated incorrect INTEGER value: '1e-' +Warning 1292 Truncated incorrect INTEGER value: '1e-' +Warning 1292 Truncated incorrect DECIMAL value: '1e-' +Warning 1292 Truncated incorrect DOUBLE value: '1e-' +SELECT +CAST('1E+' AS SIGNED), +CAST('1E+' AS UNSIGNED), +CAST('1E+' AS DECIMAL), +CAST('1E+' AS DOUBLE); +CAST('1E+' AS SIGNED) CAST('1E+' AS UNSIGNED) CAST('1E+' AS DECIMAL) CAST('1E+' AS DOUBLE) +1 1 1 1 +Warnings: +Warning 1292 Truncated incorrect INTEGER value: '1E+' +Warning 1292 Truncated incorrect INTEGER value: '1E+' +Warning 1292 Truncated incorrect DECIMAL value: '1E+' +Warning 1292 Truncated incorrect DOUBLE value: '1E+' +# +# End of 10.0 tests +# diff --git a/mysql-test/r/type_ranges.result b/mysql-test/r/type_ranges.result index 7f42c6d343e..c998c28ebd9 100644 --- a/mysql-test/r/type_ranges.result +++ b/mysql-test/r/type_ranges.result @@ -15,7 +15,7 @@ ushort smallint(5) unsigned zerofill DEFAULT '00000' NOT NULL, umedium mediumint(8) unsigned DEFAULT '0' NOT NULL, ulong int(11) unsigned DEFAULT '0' NOT NULL, ulonglong bigint(13) unsigned DEFAULT '0' NOT NULL, -time_stamp timestamp, +time_stamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, date_field date, time_field time, date_time datetime, diff --git a/mysql-test/r/type_time.result b/mysql-test/r/type_time.result index 7f748275809..59de00fa416 100644 --- a/mysql-test/r/type_time.result +++ b/mysql-test/r/type_time.result @@ -958,5 +958,253 @@ Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` DROP TABLE t1; SET timestamp=DEFAULT; # +# MDEV-8795 Equal expression propagation does not work for temporal literals +# +CREATE TABLE t1 (a TIME); +INSERT INTO t1 VALUES ('00:00:01'),('00:00:02'); +EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(a)=TIME'00:00:01' AND COALESCE(a)>=TIME'00:00:01'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (coalesce(`test`.`t1`.`a`) = TIME'00:00:01') +DROP TABLE t1; +# +# MDEV-8793 Wrong result set for SELECT ... WHERE COALESCE(time_column)=TIME('00:00:00') AND COALESCE(time_column)=DATE('2015-09-11') +# +SET timestamp=UNIX_TIMESTAMP('2015-09-11 20:20:20'); +CREATE TABLE t1 (a TIME); +INSERT INTO t1 VALUES('10:20:30'),('00:00:00'); +SELECT * FROM t1 WHERE COALESCE(a)=TIME('00:00:00'); +a +00:00:00 +SELECT * FROM t1 WHERE COALESCE(a)=DATE('2015-09-11'); +a +00:00:00 +# TIME cast + DATE cast +SELECT * FROM t1 WHERE COALESCE(a)=TIME('00:00:00') AND COALESCE(a)=DATE('2015-09-11'); +a +00:00:00 +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE COALESCE(a)=TIME('00:00:00') AND COALESCE(a)=DATE('2015-09-11'); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (coalesce(`test`.`t1`.`a`) = <cache>(00:00:00)) +# TIME cast + DATE literal +SELECT * FROM t1 WHERE COALESCE(a)=TIME('00:00:00') AND COALESCE(a)=DATE'2015-09-11'; +a +00:00:00 +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE COALESCE(a)=TIME('00:00:00') AND COALESCE(a)=DATE'2015-09-11'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (coalesce(`test`.`t1`.`a`) = <cache>(00:00:00)) +# TIME literal + DATE cast +SELECT * FROM t1 WHERE COALESCE(a)=TIME'00:00:00' AND COALESCE(a)=DATE('2015-09-11'); +a +00:00:00 +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE COALESCE(a)=TIME'00:00:00' AND COALESCE(a)=DATE('2015-09-11'); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (coalesce(`test`.`t1`.`a`) = TIME'00:00:00') +# TIME literal + DATE literal +SELECT * FROM t1 WHERE COALESCE(a)=TIME'00:00:00' AND COALESCE(a)=DATE'2015-09-11'; +a +00:00:00 +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE COALESCE(a)=TIME'00:00:00' AND COALESCE(a)=DATE'2015-09-11'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (coalesce(`test`.`t1`.`a`) = TIME'00:00:00') +# TIME-alike string literal + DATE cast +SELECT * FROM t1 WHERE COALESCE(a)='00:00:00' AND COALESCE(a)=DATE('2015-09-11'); +a +00:00:00 +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE COALESCE(a)='00:00:00' AND COALESCE(a)=DATE('2015-09-11'); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((coalesce(`test`.`t1`.`a`) = '00:00:00') and (coalesce(`test`.`t1`.`a`) = <cache>(2015-09-11 00:00:00))) +# TIME-alike string literal + DATE literal +SELECT * FROM t1 WHERE COALESCE(a)='00:00:00' AND COALESCE(a)=DATE'2015-09-11'; +a +00:00:00 +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE COALESCE(a)='00:00:00' AND COALESCE(a)=DATE'2015-09-11'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((coalesce(`test`.`t1`.`a`) = '00:00:00') and (coalesce(`test`.`t1`.`a`) = DATE'2015-09-11')) +# TIME-alike integer literal + DATE cast +SELECT * FROM t1 WHERE COALESCE(a)=0 AND COALESCE(a)=DATE('2015-09-11'); +a +00:00:00 +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE COALESCE(a)=0 AND COALESCE(a)=DATE('2015-09-11'); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((coalesce(`test`.`t1`.`a`) = 0) and (coalesce(`test`.`t1`.`a`) = <cache>(2015-09-11 00:00:00))) +# TIME-alike integer literal + DATE literal +SELECT * FROM t1 WHERE COALESCE(a)=0 AND COALESCE(a)=DATE'2015-09-11'; +a +00:00:00 +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE COALESCE(a)=0 AND COALESCE(a)=DATE'2015-09-11'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((coalesce(`test`.`t1`.`a`) = 0) and (coalesce(`test`.`t1`.`a`) = DATE'2015-09-11')) +# DATE cast + TIME cast +SELECT * FROM t1 WHERE COALESCE(a)=DATE('2015-09-11') AND COALESCE(a)=TIME('00:00:00'); +a +00:00:00 +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE COALESCE(a)=DATE('2015-09-11') AND COALESCE(a)=TIME('00:00:00'); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (coalesce(`test`.`t1`.`a`) = <cache>(2015-09-11 00:00:00)) +# DATE cast + TIME literal +SELECT * FROM t1 WHERE COALESCE(a)=DATE('2015-09-11') AND COALESCE(a)=TIME'00:00:00'; +a +00:00:00 +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE COALESCE(a)=DATE('2015-09-11') AND COALESCE(a)=TIME'00:00:00'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (coalesce(`test`.`t1`.`a`) = <cache>(2015-09-11 00:00:00)) +# DATE cast + TIME-alike string literal +SELECT * FROM t1 WHERE COALESCE(a)=DATE('2015-09-11') AND COALESCE(a)='00:00:00'; +a +00:00:00 +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE COALESCE(a)=DATE('2015-09-11') AND COALESCE(a)='00:00:00'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((coalesce(`test`.`t1`.`a`) = <cache>(2015-09-11 00:00:00)) and (coalesce(`test`.`t1`.`a`) = '00:00:00')) +# DATE cast + TIME-alike integer literal +SELECT * FROM t1 WHERE COALESCE(a)=DATE('2015-09-11') AND COALESCE(a)=0; +a +00:00:00 +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE COALESCE(a)=DATE('2015-09-11') AND COALESCE(a)=0; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((coalesce(`test`.`t1`.`a`) = <cache>(2015-09-11 00:00:00)) and (coalesce(`test`.`t1`.`a`) = 0)) +# DATE literal + TIME cast +SELECT * FROM t1 WHERE COALESCE(a)=DATE'2015-09-11' AND COALESCE(a)=TIME('00:00:00'); +a +00:00:00 +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE COALESCE(a)=DATE'2015-09-11' AND COALESCE(a)=TIME('00:00:00'); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (coalesce(`test`.`t1`.`a`) = DATE'2015-09-11') +# DATE literal + TIME literal +SELECT * FROM t1 WHERE COALESCE(a)=DATE'2015-09-11' AND COALESCE(a)=TIME'00:00:00'; +a +00:00:00 +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE COALESCE(a)=DATE'2015-09-11' AND COALESCE(a)=TIME'00:00:00'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (coalesce(`test`.`t1`.`a`) = DATE'2015-09-11') +# DATE literal + TIME-alike string literal +SELECT * FROM t1 WHERE COALESCE(a)=DATE'2015-09-11' AND COALESCE(a)='00:00:00'; +a +00:00:00 +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE COALESCE(a)=DATE'2015-09-11' AND COALESCE(a)='00:00:00'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((coalesce(`test`.`t1`.`a`) = DATE'2015-09-11') and (coalesce(`test`.`t1`.`a`) = '00:00:00')) +# DATE literal + TIME-alike integer literal +SELECT * FROM t1 WHERE COALESCE(a)=DATE'2015-09-11' AND COALESCE(a)=0; +a +00:00:00 +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE COALESCE(a)=DATE'2015-09-11' AND COALESCE(a)=0; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((coalesce(`test`.`t1`.`a`) = DATE'2015-09-11') and (coalesce(`test`.`t1`.`a`) = 0)) +DROP TABLE t1; +SET timestamp=DEFAULT; +# +# MDEV-8814 Wrong result for WHERE datetime_column > TIME('00:00:00') +# +CREATE TABLE t1 (a DATETIME); +INSERT INTO t1 VALUES ('2000-09-12 00:00:00'), ('2007-04-25 05:08:49'); +SELECT * FROM t1 WHERE a>TIME'00:00:00'; +a +SELECT * FROM t1 WHERE a>TIME('00:00:00'); +a +DROP TABLE t1; +# +# MDEV-8660 TIME(int_zerofill_column) returns a wrong result +# +CREATE TABLE t1 (a BIGINT(15), b BIGINT(15) ZEROFILL); +INSERT INTO t1 VALUES (9,9); +SELECT TIME(a),TIME(b) FROM t1; +TIME(a) TIME(b) +00:00:09 00:00:09 +DROP TABLE t1; +CREATE TABLE t1 (a BIGINT); +INSERT INTO t1 VALUES (-9223372036854775808); +SELECT CAST(a AS TIME), CAST(-9223372036854775808 AS TIME) FROM t1; +CAST(a AS TIME) CAST(-9223372036854775808 AS TIME) +-838:59:59 -838:59:59 +Warnings: +Warning 1292 Incorrect time value: '-9223372036854775808' for column 'a' at row 1 +Warning 1292 Truncated incorrect time value: '-9223372036854775808' +DROP TABLE t1; +CREATE TABLE t1 (a INT, b DECIMAL, c DOUBLE); +INSERT INTO t1 VALUES (-9000000,-9000000,-9000000); +INSERT INTO t1 VALUES (-1,-1,-1); +INSERT INTO t1 VALUES (0,0,0),(1,1,1),(9,9,9); +INSERT INTO t1 VALUES (9000000,9000000,9000000); +SELECT a, TIME(a),TIME(b),TIME(c) FROM t1 ORDER BY a; +a TIME(a) TIME(b) TIME(c) +-9000000 -838:59:59 -838:59:59 -838:59:59.999999 +-1 -00:00:01 -00:00:01 -00:00:01.000000 +0 00:00:00 00:00:00 00:00:00.000000 +1 00:00:01 00:00:01 00:00:01.000000 +9 00:00:09 00:00:09 00:00:09.000000 +9000000 838:59:59 838:59:59 838:59:59.999999 +Warnings: +Warning 1292 Incorrect time value: '-9000000' for column 'a' at row 1 +Warning 1292 Incorrect time value: '-9000000' for column 'b' at row 1 +Warning 1292 Incorrect time value: '-9000000' for column 'c' at row 1 +Warning 1292 Incorrect time value: '9000000' for column 'a' at row 6 +Warning 1292 Incorrect time value: '9000000' for column 'b' at row 6 +Warning 1292 Incorrect time value: '9000000' for column 'c' at row 6 +DROP TABLE t1; +CREATE TABLE t1 (a INT, b DECIMAL, c DOUBLE); +INSERT INTO t1 VALUES (0,0,0),(1,1,1),(9,9,9); +INSERT INTO t1 VALUES (9000000,9000000,9000000); +SELECT a, TIME(a),TIME(b),TIME(c) FROM t1 ORDER BY a; +a TIME(a) TIME(b) TIME(c) +0 00:00:00 00:00:00 00:00:00.000000 +1 00:00:01 00:00:01 00:00:01.000000 +9 00:00:09 00:00:09 00:00:09.000000 +9000000 838:59:59 838:59:59 838:59:59.999999 +Warnings: +Warning 1292 Incorrect time value: '9000000' for column 'a' at row 4 +Warning 1292 Incorrect time value: '9000000' for column 'b' at row 4 +Warning 1292 Incorrect time value: '9000000' for column 'c' at row 4 +DROP TABLE t1; +# # End of 10.1 tests # diff --git a/mysql-test/r/type_timestamp.result b/mysql-test/r/type_timestamp.result index 95e89e22f2b..fcdef627ded 100644 --- a/mysql-test/r/type_timestamp.result +++ b/mysql-test/r/type_timestamp.result @@ -1,6 +1,6 @@ drop table if exists t1,t2; set time_zone="+03:00"; -CREATE TABLE t1 (a int, t timestamp); +CREATE TABLE t1 (a int, t timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); CREATE TABLE t2 (a int, t datetime); SET TIMESTAMP=1234; insert into t1 values(1,NULL); @@ -27,7 +27,7 @@ a t 9 1970-01-01 03:20:38 drop table t1,t2; SET TIMESTAMP=1234; -CREATE TABLE t1 (value TEXT NOT NULL, id VARCHAR(32) NOT NULL, stamp timestamp, PRIMARY KEY (id)); +CREATE TABLE t1 (value TEXT NOT NULL, id VARCHAR(32) NOT NULL, stamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id)); INSERT INTO t1 VALUES ("my value", "myKey","1999-04-02 00:00:00"); SELECT stamp FROM t1 WHERE id="myKey"; stamp @@ -41,13 +41,13 @@ SELECT stamp FROM t1 WHERE id="myKey"; stamp 1999-04-02 00:00:00 drop table t1; -create table t1 (a timestamp); +create table t1 (a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); insert into t1 values (now()); select date_format(a,"%Y %y"),year(a),year(now()) from t1; date_format(a,"%Y %y") year(a) year(now()) 1970 70 1970 1970 drop table t1; -create table t1 (ix timestamp); +create table t1 (ix timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); insert into t1 values (19991101000000),(19990102030405),(19990630232922),(19990601000000),(19990930232922),(19990531232922),(19990501000000),(19991101000000),(19990501000000); select ix+0 from t1; ix+0 @@ -69,7 +69,7 @@ ix+0 19990630232922 19990601000000 drop table t1; -CREATE TABLE t1 (date date, date_time datetime, time_stamp timestamp); +CREATE TABLE t1 (date date, date_time datetime, time_stamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); INSERT INTO t1 VALUES ("1998-12-31","1998-12-31 23:59:59",19981231235959); INSERT INTO t1 VALUES ("1999-01-01","1999-01-01 00:00:00",19990101000000); INSERT INTO t1 VALUES ("1999-09-09","1999-09-09 23:59:59",19990909235959); @@ -97,7 +97,7 @@ date date_time time_stamp 2005-01-01 2005-01-01 00:00:00 2005-01-01 00:00:00 2030-01-01 2030-01-01 00:00:00 2030-01-01 00:00:00 drop table t1; -create table t1 (ix timestamp); +create table t1 (ix timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); insert into t1 values (0),(20030101010160),(20030101016001),(20030101240101),(20030132010101),(20031301010101),(20031200000000),(20030000000000); Warnings: Warning 1265 Data truncated for column 'ix' at row 2 @@ -157,7 +157,7 @@ create table t1 (t1 timestamp default now(), t2 timestamp on update now()); drop table t1; create table t1 (t1 timestamp on update now(), t2 timestamp default now() on update now()); drop table t1; -create table t1 (t1 timestamp default '2003-01-01 00:00:00', t2 datetime, t3 timestamp); +create table t1 (t1 timestamp not null default '2003-01-01 00:00:00', t2 datetime, t3 timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'); SET TIMESTAMP=1000000000; insert into t1 values (); SET TIMESTAMP=1000000001; @@ -181,7 +181,7 @@ t1 timestamp NO 2003-01-01 00:00:00 t2 datetime YES NULL t3 timestamp NO 0000-00-00 00:00:00 drop table t1; -create table t1 (t1 timestamp default now(), t2 datetime, t3 timestamp); +create table t1 (t1 timestamp not null default now(), t2 datetime, t3 timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'); SET TIMESTAMP=1000000002; insert into t1 values (); SET TIMESTAMP=1000000003; @@ -205,7 +205,7 @@ t1 timestamp NO CURRENT_TIMESTAMP t2 datetime YES NULL t3 timestamp NO 0000-00-00 00:00:00 drop table t1; -create table t1 (t1 timestamp default '2003-01-01 00:00:00' on update now(), t2 datetime); +create table t1 (t1 timestamp not null default '2003-01-01 00:00:00' on update now(), t2 datetime); SET TIMESTAMP=1000000004; insert into t1 values (); select * from t1; @@ -230,7 +230,7 @@ Field Type Null Key Default Extra t1 timestamp NO 2003-01-01 00:00:00 on update CURRENT_TIMESTAMP t2 datetime YES NULL drop table t1; -create table t1 (t1 timestamp default now() on update now(), t2 datetime); +create table t1 (t1 timestamp not null default now() on update now(), t2 datetime); SET TIMESTAMP=1000000006; insert into t1 values (); select * from t1; @@ -255,7 +255,7 @@ Field Type Null Key Default Extra t1 timestamp NO CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP t2 datetime YES NULL drop table t1; -create table t1 (t1 timestamp, t2 datetime, t3 timestamp); +create table t1 (t1 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, t2 datetime, t3 timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'); SET TIMESTAMP=1000000007; insert into t1 values (); select * from t1; @@ -282,7 +282,7 @@ t1 timestamp NO CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP t2 datetime YES NULL t3 timestamp NO 0000-00-00 00:00:00 drop table t1; -create table t1 (t1 timestamp default current_timestamp on update current_timestamp, t2 datetime); +create table t1 (t1 timestamp not null default current_timestamp on update current_timestamp, t2 datetime); SET TIMESTAMP=1000000009; insert into t1 values (); select * from t1; @@ -318,7 +318,7 @@ select * from t1; t1 t2 2004-04-03 00:00:00 2004-04-01 00:00:00 drop table t1; -create table t1 (pk int primary key, t1 timestamp default current_timestamp on update current_timestamp, bulk int); +create table t1 (pk int primary key, t1 timestamp not null default current_timestamp on update current_timestamp, bulk int); insert into t1 values (1, '2004-04-01 00:00:00', 10); SET TIMESTAMP=1000000013; replace into t1 set pk = 1, bulk= 20; @@ -326,7 +326,7 @@ select * from t1; pk t1 bulk 1 2001-09-09 04:46:53 20 drop table t1; -create table t1 (pk int primary key, t1 timestamp default '2003-01-01 00:00:00' on update current_timestamp, bulk int); +create table t1 (pk int primary key, t1 timestamp not null default '2003-01-01 00:00:00' on update current_timestamp, bulk int); insert into t1 values (1, '2004-04-01 00:00:00', 10); SET TIMESTAMP=1000000014; replace into t1 set pk = 1, bulk= 20; @@ -334,7 +334,7 @@ select * from t1; pk t1 bulk 1 2003-01-01 00:00:00 20 drop table t1; -create table t1 (pk int primary key, t1 timestamp default current_timestamp, bulk int); +create table t1 (pk int primary key, t1 timestamp not null default current_timestamp on update current_timestamp, bulk int); insert into t1 values (1, '2004-04-01 00:00:00', 10); SET TIMESTAMP=1000000015; replace into t1 set pk = 1, bulk= 20; @@ -342,7 +342,7 @@ select * from t1; pk t1 bulk 1 2001-09-09 04:46:55 20 drop table t1; -create table t1 (t1 timestamp default current_timestamp on update current_timestamp); +create table t1 (t1 timestamp not null default current_timestamp on update current_timestamp); insert into t1 values ('2004-04-01 00:00:00'); SET TIMESTAMP=1000000016; alter table t1 add i int default 10; @@ -397,13 +397,13 @@ drop table t1; create table t1 (a bigint, b bigint); insert into t1 values (NULL, NULL), (20030101000000, 20030102000000); set timestamp=1000000019; -alter table t1 modify a timestamp, modify b timestamp; +alter table t1 modify a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, modify b timestamp NOT NULL DEFAULT '0000-00-00 00:00:0'; select * from t1; a b 2001-09-09 04:46:59 2001-09-09 04:46:59 2003-01-01 00:00:00 2003-01-02 00:00:00 drop table t1; -create table t1 (a char(2), t timestamp); +create table t1 (a char(2), t timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); insert into t1 values ('a', '2004-01-01 00:00:00'), ('a', '2004-01-01 01:00:00'), ('b', '2004-02-01 00:00:00'); select max(t) from t1 group by a; @@ -421,7 +421,7 @@ t1 CREATE TABLE "t1" ( ) set sql_mode=''; drop table t1; -create table t1 (a int auto_increment primary key, b int, c timestamp); +create table t1 (a int auto_increment primary key, b int, c timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); insert into t1 (a, b, c) values (1, 0, '2001-01-01 01:01:01'), (2, 0, '2002-02-02 02:02:02'), (3, 0, '2003-03-03 03:03:03'); select * from t1; @@ -486,8 +486,8 @@ is_nullable NO drop table t1; CREATE TABLE t1 ( f1 INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, -f2 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, -f3 TIMESTAMP); +f2 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, +f3 TIMESTAMP NOT NULL default '0000-00-00 00:00:00'); INSERT INTO t1 (f2,f3) VALUES (NOW(), "0000-00-00 00:00:00"); INSERT INTO t1 (f2,f3) VALUES (NOW(), NULL); INSERT INTO t1 (f2,f3) VALUES (NOW(), ASCII(NULL)); @@ -507,7 +507,7 @@ End of 5.0 tests # Bug #55779: select does not work properly in mysql server # Version "5.1.42 SUSE MySQL RPM" # -CREATE TABLE t1 (a TIMESTAMP, KEY (a)); +CREATE TABLE t1 (a TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY (a)); INSERT INTO t1 VALUES ('2000-01-01 00:00:00'), ('2000-01-01 00:00:00'), ('2000-01-01 00:00:01'), ('2000-01-01 00:00:01'); SELECT a FROM t1 WHERE a >= 20000101000000; @@ -604,7 +604,7 @@ Warning 1292 Incorrect datetime value: 'abc' Bug#50888 valgrind warnings in Field_timestamp::val_str SET TIMESTAMP=0; -CREATE TABLE t1(a timestamp); +CREATE TABLE t1(a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); INSERT INTO t1 VALUES ('2008-02-23 09:23:45'), ('2010-03-05 11:08:02'); FLUSH TABLES t1; SELECT MAX(a) FROM t1; diff --git a/mysql-test/r/type_timestamp_hires.result b/mysql-test/r/type_timestamp_hires.result index 21e72b98146..573ebbeb709 100644 --- a/mysql-test/r/type_timestamp_hires.result +++ b/mysql-test/r/type_timestamp_hires.result @@ -50,7 +50,7 @@ a 2010-12-11 03:04:05.789 2010-12-11 15:47:11.123 drop table t1; -create table t1 (a timestamp(4)) engine=innodb; +create table t1 (a timestamp(4)NOT NULL DEFAULT CURRENT_TIMESTAMP(4) ON UPDATE CURRENT_TIMESTAMP(4)) engine=innodb; insert t1 values ('2010-12-11 01:02:03.456789'); select * from t1; a @@ -216,7 +216,7 @@ decimal5_f4_timestamp 2010-11-12 11:14:17.7654 bigint_f5_timestamp 2010-11-12 11:14:17.00000 varchar_f6_timestamp 2010-11-12 11:14:17.765432 drop table t1, t2; -create table t1 (a timestamp(6), b timestamp(6)); +create table t1 (a timestamp(6)NOT NULL DEFAULT '0000-00-00 00:00:00.000000', b timestamp(6)NOT NULL DEFAULT '0000-00-00 00:00:00.000000'); create procedure foo(x timestamp, y timestamp(4)) insert into t1 values (x, y); call foo('2010-02-03 4:5:6.789123', '2010-02-03 4:5:6.789123'); select * from t1; @@ -266,7 +266,7 @@ drop table t1, t2; SET timestamp=DEFAULT; set time_zone='+03:00'; set timestamp=unix_timestamp('2011-01-01 01:01:01') + 0.123456; -create table t1 (a timestamp(5)); +create table t1 (a timestamp(5) DEFAULT CURRENT_TIMESTAMP); insert t1 values (); select * from t1; a diff --git a/mysql-test/r/type_varchar.result b/mysql-test/r/type_varchar.result index 936f48e6122..e15b029e9c6 100644 --- a/mysql-test/r/type_varchar.result +++ b/mysql-test/r/type_varchar.result @@ -499,6 +499,7 @@ SELECT 5 = a FROM t1; 0 Warnings: Warning 1292 Truncated incorrect DOUBLE value: 's' +Warning 1292 Truncated incorrect DOUBLE value: '' DROP TABLE t1; CREATE TABLE t1 (a CHAR(16)); INSERT INTO t1 VALUES ('5'), ('s'), (''); @@ -509,6 +510,7 @@ SELECT 5 = a FROM t1; 0 Warnings: Warning 1292 Truncated incorrect DOUBLE value: 's ' +Warning 1292 Truncated incorrect DOUBLE value: ' ' DROP TABLE t1; # # Start of 10.0 tests diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result index d3498c27f13..4711b8983e3 100644 --- a/mysql-test/r/union.result +++ b/mysql-test/r/union.result @@ -1342,7 +1342,7 @@ id 5 99 drop table t1; -create table t1(f1 char(1), f2 char(5), f3 binary(1), f4 binary(5), f5 timestamp, f6 varchar(1) character set utf8 collate utf8_general_ci, f7 text); +create table t1(f1 char(1), f2 char(5), f3 binary(1), f4 binary(5), f5 timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', f6 varchar(1) character set utf8 collate utf8_general_ci, f7 text); create table t2 as select *, f6 as f8 from t1 union select *, f7 from t1; show create table t2; Table Create Table diff --git a/mysql-test/r/varbinary.result b/mysql-test/r/varbinary.result index b5f6d065174..58cab5ad1ca 100644 --- a/mysql-test/r/varbinary.result +++ b/mysql-test/r/varbinary.result @@ -175,6 +175,8 @@ b'' 0+b'' select x'', 0+x''; x'' 0+x'' 0 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '' select 0x; ERROR 42S22: Unknown column '0x' in 'field list' select 0b; diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index cfb7eb1cbdc..eaef3f96ee0 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -1350,8 +1350,11 @@ a b delete from t1; load data infile '../../std_data/loaddata3.dat' ignore into table v1 fields terminated by '' enclosed by '' ignore 1 lines; Warnings: +Note 1265 Data truncated for column 'a' at row 1 +Note 1265 Data truncated for column 'a' at row 2 Warning 1366 Incorrect integer value: 'error ' for column 'a' at row 3 Warning 1369 CHECK OPTION failed 'test.v1' +Note 1265 Data truncated for column 'a' at row 3 Warning 1366 Incorrect integer value: 'wrong end ' for column 'a' at row 4 Warning 1369 CHECK OPTION failed 'test.v1' select * from t1 order by a,b; @@ -5604,8 +5607,12 @@ INSERT INTO t1 VALUES ('DD'), ('ZZ'), ('ZZ'), ('KK'), ('FF'), ('HH'),('MM'),('`1 CREATE VIEW v1 AS SELECT * FROM t1; SELECT * FROM t1 WHERE a <> 0 AND a = ' 1'; a +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '`1' SELECT * FROM v1 WHERE a <> 0 AND a = ' 1'; a +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '`1' DROP VIEW v1; DROP TABLE t1; CREATE TABLE t1 (a ENUM('5','6')); @@ -5627,8 +5634,12 @@ INSERT INTO t1 VALUES ('DD'), ('ZZ'), ('ZZ'), ('KK'), ('FF'), ('HH'),('MM'),('`1 CREATE VIEW v1 AS SELECT * FROM t1; SELECT * FROM t1 WHERE a <> 0 AND a = ' 1'; a +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '`1' SELECT * FROM v1 WHERE a <> 0 AND a = ' 1'; a +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '`1' DROP VIEW v1; DROP TABLE t1; CREATE TABLE t1 (a ENUM('5','6')); diff --git a/mysql-test/r/xml.result b/mysql-test/r/xml.result index 24b95f0e204..b6f0f048c68 100644 --- a/mysql-test/r/xml.result +++ b/mysql-test/r/xml.result @@ -1161,6 +1161,8 @@ SET NAMES latin1; SELECT UPDATEXML(CONVERT('' USING swe7), TRUNCATE('',1), 0); UPDATEXML(CONVERT('' USING swe7), TRUNCATE('',1), 0) NULL +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '' # # Bug#12375190: UPDATEXML CRASHES ON SIMPLE INPUTS # |