diff options
Diffstat (limited to 'mysql-test')
-rw-r--r-- | mysql-test/r/create.result | 30 | ||||
-rw-r--r-- | mysql-test/r/insert_select.result | 29 | ||||
-rw-r--r-- | mysql-test/r/temp_table.result | 21 | ||||
-rw-r--r-- | mysql-test/r/type_float.result | 14 | ||||
-rw-r--r-- | mysql-test/r/union.result | 4 | ||||
-rw-r--r-- | mysql-test/t/insert_select.test | 27 | ||||
-rw-r--r-- | mysql-test/t/temp_table.test | 15 |
7 files changed, 115 insertions, 25 deletions
diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result index ab9e6762d21..77eb2bb025f 100644 --- a/mysql-test/r/create.result +++ b/mysql-test/r/create.result @@ -455,21 +455,21 @@ create table t2 select ifnull(a,a), ifnull(b,b), ifnull(c,c), ifnull(d,d), ifnul show create table t2; Table Create Table t2 CREATE TABLE `t2` ( - `ifnull(a,a)` tinyint(4) DEFAULT NULL, - `ifnull(b,b)` smallint(6) DEFAULT NULL, - `ifnull(c,c)` mediumint(8) DEFAULT NULL, - `ifnull(d,d)` int(11) DEFAULT NULL, - `ifnull(e,e)` bigint(20) DEFAULT NULL, - `ifnull(f,f)` float(24,2) DEFAULT NULL, - `ifnull(g,g)` double(53,3) DEFAULT NULL, - `ifnull(h,h)` decimal(5,4) DEFAULT NULL, - `ifnull(i,i)` year(4) DEFAULT NULL, - `ifnull(j,j)` date DEFAULT NULL, - `ifnull(k,k)` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', - `ifnull(l,l)` datetime DEFAULT NULL, - `ifnull(m,m)` varchar(1) DEFAULT NULL, - `ifnull(n,n)` varchar(3) DEFAULT NULL, - `ifnull(o,o)` varchar(10) DEFAULT NULL + `ifnull(a,a)` tinyint(4) default NULL, + `ifnull(b,b)` smallint(6) default NULL, + `ifnull(c,c)` mediumint(8) default NULL, + `ifnull(d,d)` int(11) default NULL, + `ifnull(e,e)` bigint(20) default NULL, + `ifnull(f,f)` float(3,2) default NULL, + `ifnull(g,g)` double(4,3) default NULL, + `ifnull(h,h)` decimal(5,4) default NULL, + `ifnull(i,i)` year(4) default NULL, + `ifnull(j,j)` date default NULL, + `ifnull(k,k)` timestamp NOT NULL default '0000-00-00 00:00:00', + `ifnull(l,l)` datetime default NULL, + `ifnull(m,m)` varchar(1) default NULL, + `ifnull(n,n)` varchar(3) default NULL, + `ifnull(o,o)` varchar(10) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1,t2; create table t1(str varchar(10) default 'def',strnull varchar(10),intg int default '10',rel double default '3.14'); diff --git a/mysql-test/r/insert_select.result b/mysql-test/r/insert_select.result index 18a0ed1a1cb..57339b2e29f 100644 --- a/mysql-test/r/insert_select.result +++ b/mysql-test/r/insert_select.result @@ -730,3 +730,32 @@ f1 f2 2 2 10 10 DROP TABLE t1, t2; +SET SQL_MODE='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'; +CREATE TABLE t1 (c VARCHAR(30), INDEX ix_c (c(10))); +CREATE TABLE t2 (d VARCHAR(10)); +INSERT INTO t1 (c) VALUES ('7_chars'), ('13_characters'); +EXPLAIN +SELECT (SELECT SUM(LENGTH(c)) FROM t1 WHERE c='13_characters') FROM t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 +2 SUBQUERY t1 ref ix_c ix_c 13 const 1 Using where +SELECT (SELECT SUM(LENGTH(c)) FROM t1 WHERE c='13_characters') FROM t1; +(SELECT SUM(LENGTH(c)) FROM t1 WHERE c='13_characters') +13 +13 +INSERT INTO t2 (d) +SELECT (SELECT SUM(LENGTH(c)) FROM t1 WHERE c='13_characters') FROM t1; +INSERT INTO t2 (d) +SELECT (SELECT SUM(LENGTH(c)) FROM t1 WHERE c='7_chars') FROM t1; +INSERT INTO t2 (d) +SELECT (SELECT SUM(LENGTH(c)) FROM t1 WHERE c IN (SELECT t1.c FROM t1)) +FROM t1; +SELECT * FROM t2; +d +13 +13 +7 +7 +20 +20 +DROP TABLE t1,t2; diff --git a/mysql-test/r/temp_table.result b/mysql-test/r/temp_table.result index 6c9a389c1f4..9cb4a6b2427 100644 --- a/mysql-test/r/temp_table.result +++ b/mysql-test/r/temp_table.result @@ -152,6 +152,27 @@ SELECT * FROM t1; i DROP TABLE t1; End of 4.1 tests. +CREATE TABLE t1 ( c FLOAT( 20, 14 ) ); +INSERT INTO t1 VALUES( 12139 ); +CREATE TABLE t2 ( c FLOAT(30,18) ); +INSERT INTO t2 VALUES( 123456 ); +SELECT AVG( c ) FROM t1 UNION SELECT 1; +AVG( c ) +12139 +1 +SELECT 1 UNION SELECT AVG( c ) FROM t1; +1 +1 +12139 +SELECT 1 UNION SELECT * FROM t2 UNION SELECT 1; +1 +1 +123456 +SELECT c/1 FROM t1 UNION SELECT 1; +c/1 +12139 +1 +DROP TABLE t1, t2; create temporary table t1 (a int); insert into t1 values (4711); select * from t1; diff --git a/mysql-test/r/type_float.result b/mysql-test/r/type_float.result index dbe60aff3d9..de782f9a5e4 100644 --- a/mysql-test/r/type_float.result +++ b/mysql-test/r/type_float.result @@ -91,10 +91,10 @@ col1 col2 col3 col4 show create table t2; Table Create Table t2 CREATE TABLE `t2` ( - `col1` double DEFAULT NULL, - `col2` double(53,5) DEFAULT NULL, - `col3` double DEFAULT NULL, - `col4` double DEFAULT NULL + `col1` double default NULL, + `col2` double(22,5) default NULL, + `col3` double default NULL, + `col4` double default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1,t2; create table t1 (a float); @@ -232,12 +232,12 @@ insert into t2 values ("1.23456780"); create table t3 select * from t2 union select * from t1; select * from t3; d -1.234567800 -100000000.000000000 +1.2345678 +100000000 show create table t3; Table Create Table t3 CREATE TABLE `t3` ( - `d` double(22,9) DEFAULT NULL + `d` double default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1, t2, t3; create table t1 select 105213674794682365.00 + 0.0 x; diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result index 447a8e032cb..2c8fa5cb896 100644 --- a/mysql-test/r/union.result +++ b/mysql-test/r/union.result @@ -554,7 +554,7 @@ aa show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `a` varbinary(20) NOT NULL DEFAULT '' + `a` varbinary(2) NOT NULL default '' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 SELECT 12 as a UNION select 12.2 as a; @@ -655,7 +655,7 @@ f show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `f` varbinary(24) DEFAULT NULL + `f` varbinary(12) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 SELECT y from t2 UNION select da from t2; diff --git a/mysql-test/t/insert_select.test b/mysql-test/t/insert_select.test index 0f9a0ca4872..95a55b732c5 100644 --- a/mysql-test/t/insert_select.test +++ b/mysql-test/t/insert_select.test @@ -293,4 +293,29 @@ INSERT INTO t2 (f1, f2) SELECT * FROM t2; DROP TABLE t1, t2; - +# +# Bug #26207: inserts don't work with shortened index +# +SET SQL_MODE='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'; + +CREATE TABLE t1 (c VARCHAR(30), INDEX ix_c (c(10))); +CREATE TABLE t2 (d VARCHAR(10)); +INSERT INTO t1 (c) VALUES ('7_chars'), ('13_characters'); + +EXPLAIN + SELECT (SELECT SUM(LENGTH(c)) FROM t1 WHERE c='13_characters') FROM t1; + +SELECT (SELECT SUM(LENGTH(c)) FROM t1 WHERE c='13_characters') FROM t1; + +INSERT INTO t2 (d) + SELECT (SELECT SUM(LENGTH(c)) FROM t1 WHERE c='13_characters') FROM t1; + +INSERT INTO t2 (d) + SELECT (SELECT SUM(LENGTH(c)) FROM t1 WHERE c='7_chars') FROM t1; + +INSERT INTO t2 (d) + SELECT (SELECT SUM(LENGTH(c)) FROM t1 WHERE c IN (SELECT t1.c FROM t1)) + FROM t1; + +SELECT * FROM t2; +DROP TABLE t1,t2; diff --git a/mysql-test/t/temp_table.test b/mysql-test/t/temp_table.test index d06fde87b34..e10fd386bd3 100644 --- a/mysql-test/t/temp_table.test +++ b/mysql-test/t/temp_table.test @@ -164,6 +164,21 @@ DROP TABLE t1; --echo End of 4.1 tests. +# +# Bug #24791: Union with AVG-groups generates wrong results +# +CREATE TABLE t1 ( c FLOAT( 20, 14 ) ); +INSERT INTO t1 VALUES( 12139 ); + +CREATE TABLE t2 ( c FLOAT(30,18) ); +INSERT INTO t2 VALUES( 123456 ); + +SELECT AVG( c ) FROM t1 UNION SELECT 1; +SELECT 1 UNION SELECT AVG( c ) FROM t1; +SELECT 1 UNION SELECT * FROM t2 UNION SELECT 1; +SELECT c/1 FROM t1 UNION SELECT 1; + +DROP TABLE t1, t2; # # Test truncate with temporary tables |