From 6b20342651bb5207b6c125d2d11b664a1bebcc41 Mon Sep 17 00:00:00 2001 From: Monty Date: Tue, 18 Aug 2015 00:42:08 +0300 Subject: Ensure that fields declared with NOT NULL doesn't have DEFAULT values if not specified and if not timestamp or auto_increment In original code, sometimes one got an automatic DEFAULT value in some cases, in other cases not. For example: create table t1 (a int primary key) - No default create table t2 (a int, primary key(a)) - DEFAULT 0 create table t1 SELECT .... - Default for all fields, even if they where defined as NOT NULL ALTER TABLE ... MODIFY could sometimes add an unexpected DEFAULT value. The patch is quite big because we had some many test cases that used CREATE ... SELECT or CREATE ... (...PRIMARY KEY(xxx)) which doesn't have an automatic DEFAULT anymore. Other things: - Removed warnings from InnoDB when waiting from semaphore (got this when testing things with --big) --- mysql-test/r/subselect_no_scache.result | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'mysql-test/r/subselect_no_scache.result') diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result index c70db6f8cc0..f52129d7cfb 100644 --- a/mysql-test/r/subselect_no_scache.result +++ b/mysql-test/r/subselect_no_scache.result @@ -1261,7 +1261,7 @@ a SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( - `a` bigint(20) NOT NULL DEFAULT '0' + `a` bigint(20) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 (a int); -- cgit v1.2.1 From 39b46ae934bfa886314f918068d1e195970fe65e Mon Sep 17 00:00:00 2001 From: Alexander Barkov Date: Wed, 9 Sep 2015 15:39:09 +0400 Subject: MDEV-8706 Wrong result for SELECT..WHERE time_column=TIMESTAMP'2015-08-30 00:00:00' AND time_column='00:00:00' --- mysql-test/r/subselect_no_scache.result | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'mysql-test/r/subselect_no_scache.result') diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result index f52129d7cfb..672999922fa 100644 --- a/mysql-test/r/subselect_no_scache.result +++ b/mysql-test/r/subselect_no_scache.result @@ -408,13 +408,13 @@ EXPLAIN EXTENDED SELECT DISTINCT date FROM t1 WHERE date='2002-08-03'; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 index NULL PRIMARY 43 NULL 2 100.00 Using where; Using index Warnings: -Note 1003 select distinct `test`.`t1`.`date` AS `date` from `test`.`t1` where (`test`.`t1`.`date` = '2002-08-03') +Note 1003 select distinct `test`.`t1`.`date` AS `date` from `test`.`t1` where (`test`.`t1`.`date` = DATE'2002-08-03') EXPLAIN EXTENDED SELECT (SELECT DISTINCT date FROM t1 WHERE date='2002-08-03'); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2 SUBQUERY t1 index NULL PRIMARY 43 NULL 2 100.00 Using where; Using index Warnings: -Note 1003 select (select distinct `test`.`t1`.`date` from `test`.`t1` where (`test`.`t1`.`date` = '2002-08-03')) AS `(SELECT DISTINCT date FROM t1 WHERE date='2002-08-03')` +Note 1003 select (select distinct `test`.`t1`.`date` from `test`.`t1` where (`test`.`t1`.`date` = DATE'2002-08-03')) AS `(SELECT DISTINCT date FROM t1 WHERE date='2002-08-03')` SELECT DISTINCT date FROM t1 WHERE date='2002-08-03'; date 2002-08-03 -- cgit v1.2.1 From d9b25ae3db8584bde809c0ab3230cbe151fa489b Mon Sep 17 00:00:00 2001 From: Alexander Barkov Date: Thu, 17 Sep 2015 11:05:07 +0400 Subject: MDEV-8466 CAST works differently for DECIMAL/INT vs DOUBLE for empty strings MDEV-8468 CAST and INSERT work differently for DECIMAL/INT vs DOUBLE for a string with trailing spaces --- mysql-test/r/subselect_no_scache.result | 10 ++++++++++ 1 file changed, 10 insertions(+) (limited to 'mysql-test/r/subselect_no_scache.result') 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) -- cgit v1.2.1