diff options
Diffstat (limited to 'mysql-test')
| -rw-r--r-- | mysql-test/r/create.result | 24 | ||||
| -rw-r--r-- | mysql-test/r/func_weight_string.result | 2 | ||||
| -rw-r--r-- | mysql-test/r/gis.result | 2 | ||||
| -rw-r--r-- | mysql-test/r/null.result | 304 | ||||
| -rw-r--r-- | mysql-test/r/union.result | 22 | ||||
| -rw-r--r-- | mysql-test/suite/innodb_gis/r/1.result | 2 | ||||
| -rw-r--r-- | mysql-test/suite/innodb_gis/r/gis.result | 2 | ||||
| -rw-r--r-- | mysql-test/t/create.test | 16 | ||||
| -rw-r--r-- | mysql-test/t/null.test | 135 | ||||
| -rw-r--r-- | mysql-test/t/union.test | 16 |
10 files changed, 487 insertions, 38 deletions
diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result index 1f032faaa40..1a3289b2618 100644 --- a/mysql-test/r/create.result +++ b/mysql-test/r/create.result @@ -488,13 +488,35 @@ d date YES NULL e varchar(1) YES NULL f datetime YES NULL g time YES NULL -h longblob YES NULL +h blob YES NULL dd time YES NULL select * from t2; 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 ( +c_tinytext tinytext, +c_text text, +c_mediumtext mediumtext, +c_longtext longtext +); +CREATE TABLE t2 AS SELECT +ifnull(c_tinytext, CAST('yet another binary data' AS BINARY)), +ifnull(c_text, CAST('yet another binary data' AS BINARY)), +ifnull(c_mediumtext, CAST('yet another binary data' AS BINARY)), +ifnull(c_longtext, CAST('yet another binary data' AS BINARY)) +FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `ifnull(c_tinytext, CAST('yet another binary data' AS BINARY))` tinyblob DEFAULT NULL, + `ifnull(c_text, CAST('yet another binary data' AS BINARY))` blob DEFAULT NULL, + `ifnull(c_mediumtext, CAST('yet another binary data' AS BINARY))` mediumblob DEFAULT NULL, + `ifnull(c_longtext, CAST('yet another binary data' AS BINARY))` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2; +DROP TABLE t1; 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; diff --git a/mysql-test/r/func_weight_string.result b/mysql-test/r/func_weight_string.result index 04fd9962218..5fa78c861a9 100644 --- a/mysql-test/r/func_weight_string.result +++ b/mysql-test/r/func_weight_string.result @@ -57,7 +57,7 @@ create table t1 select weight_string(repeat('t',66000)) as w; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `w` longblob DEFAULT NULL + `w` mediumblob DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; select weight_string(NULL); diff --git a/mysql-test/r/gis.result b/mysql-test/r/gis.result index 77150ee3142..43de7d2e7d6 100644 --- a/mysql-test/r/gis.result +++ b/mysql-test/r/gis.result @@ -709,7 +709,7 @@ def test t1 t1 g g 255 4294967295 0 Y 144 0 63 g select asbinary(g) from t1; Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr -def asbinary(g) 252 4294967295 0 Y 128 0 63 +def asbinary(g) 251 4294967295 0 Y 128 0 63 asbinary(g) drop table t1; create table t1 (a TEXT, b GEOMETRY NOT NULL, SPATIAL KEY(b)); diff --git a/mysql-test/r/null.result b/mysql-test/r/null.result index 0ff5c3243d8..8bbdfb2f90b 100644 --- a/mysql-test/r/null.result +++ b/mysql-test/r/null.result @@ -575,8 +575,14 @@ c_float FLOAT, c_double DOUBLE, c_decimal103 DECIMAL(10,3), c_varchar10 VARCHAR(10), +c_tinytext TINYTEXT, c_text TEXT, +c_mediumtext MEDIUMTEXT, +c_longtext LONGTEXT, +c_tinyblob TINYBLOB, c_blob BLOB, +c_mediumblob MEDIUMBLOB, +c_longblob LONGBLOB, c_enum ENUM('one','two','tree'), c_datetime3 DATETIME(3), c_timestamp3 TIMESTAMP(3), @@ -899,6 +905,45 @@ t2 CREATE TABLE `t2` ( ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t2; CREATE TABLE t2 AS SELECT +NULLIF(c_tinytext, 1), +NULLIF(c_tinytext, c_smallint), +NULLIF(c_tinytext, c_tinyint), +NULLIF(c_tinytext, c_int), +NULLIF(c_tinytext, c_bigint), +NULLIF(c_tinytext, c_float), +NULLIF(c_tinytext, c_double), +NULLIF(c_tinytext, c_decimal103), +NULLIF(c_tinytext, c_varchar10), +NULLIF(c_tinytext, c_text), +NULLIF(c_tinytext, c_blob), +NULLIF(c_tinytext, c_enum), +NULLIF(c_tinytext, c_datetime3), +NULLIF(c_tinytext, c_timestamp3), +NULLIF(c_tinytext, c_date), +NULLIF(c_tinytext, c_time) +FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `NULLIF(c_tinytext, 1)` tinytext DEFAULT NULL, + `NULLIF(c_tinytext, c_smallint)` tinytext DEFAULT NULL, + `NULLIF(c_tinytext, c_tinyint)` tinytext DEFAULT NULL, + `NULLIF(c_tinytext, c_int)` tinytext DEFAULT NULL, + `NULLIF(c_tinytext, c_bigint)` tinytext DEFAULT NULL, + `NULLIF(c_tinytext, c_float)` tinytext DEFAULT NULL, + `NULLIF(c_tinytext, c_double)` tinytext DEFAULT NULL, + `NULLIF(c_tinytext, c_decimal103)` tinytext DEFAULT NULL, + `NULLIF(c_tinytext, c_varchar10)` tinytext DEFAULT NULL, + `NULLIF(c_tinytext, c_text)` tinytext DEFAULT NULL, + `NULLIF(c_tinytext, c_blob)` tinytext DEFAULT NULL, + `NULLIF(c_tinytext, c_enum)` tinytext DEFAULT NULL, + `NULLIF(c_tinytext, c_datetime3)` tinytext DEFAULT NULL, + `NULLIF(c_tinytext, c_timestamp3)` tinytext DEFAULT NULL, + `NULLIF(c_tinytext, c_date)` tinytext DEFAULT NULL, + `NULLIF(c_tinytext, c_time)` tinytext DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2; +CREATE TABLE t2 AS SELECT NULLIF(c_text, 1), NULLIF(c_text, c_smallint), NULLIF(c_text, c_tinyint), @@ -919,22 +964,139 @@ FROM t1; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( - `NULLIF(c_text, 1)` longtext DEFAULT NULL, - `NULLIF(c_text, c_smallint)` longtext DEFAULT NULL, - `NULLIF(c_text, c_tinyint)` longtext DEFAULT NULL, - `NULLIF(c_text, c_int)` longtext DEFAULT NULL, - `NULLIF(c_text, c_bigint)` longtext DEFAULT NULL, - `NULLIF(c_text, c_float)` longtext DEFAULT NULL, - `NULLIF(c_text, c_double)` longtext DEFAULT NULL, - `NULLIF(c_text, c_decimal103)` longtext DEFAULT NULL, - `NULLIF(c_text, c_varchar10)` longtext DEFAULT NULL, - `NULLIF(c_text, c_text)` longtext DEFAULT NULL, - `NULLIF(c_text, c_blob)` longtext DEFAULT NULL, - `NULLIF(c_text, c_enum)` longtext DEFAULT NULL, - `NULLIF(c_text, c_datetime3)` longtext DEFAULT NULL, - `NULLIF(c_text, c_timestamp3)` longtext DEFAULT NULL, - `NULLIF(c_text, c_date)` longtext DEFAULT NULL, - `NULLIF(c_text, c_time)` longtext DEFAULT NULL + `NULLIF(c_text, 1)` text DEFAULT NULL, + `NULLIF(c_text, c_smallint)` text DEFAULT NULL, + `NULLIF(c_text, c_tinyint)` text DEFAULT NULL, + `NULLIF(c_text, c_int)` text DEFAULT NULL, + `NULLIF(c_text, c_bigint)` text DEFAULT NULL, + `NULLIF(c_text, c_float)` text DEFAULT NULL, + `NULLIF(c_text, c_double)` text DEFAULT NULL, + `NULLIF(c_text, c_decimal103)` text DEFAULT NULL, + `NULLIF(c_text, c_varchar10)` text DEFAULT NULL, + `NULLIF(c_text, c_text)` text DEFAULT NULL, + `NULLIF(c_text, c_blob)` text DEFAULT NULL, + `NULLIF(c_text, c_enum)` text DEFAULT NULL, + `NULLIF(c_text, c_datetime3)` text DEFAULT NULL, + `NULLIF(c_text, c_timestamp3)` text DEFAULT NULL, + `NULLIF(c_text, c_date)` text DEFAULT NULL, + `NULLIF(c_text, c_time)` text DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2; +CREATE TABLE t2 AS SELECT +NULLIF(c_mediumtext, 1), +NULLIF(c_mediumtext, c_smallint), +NULLIF(c_mediumtext, c_tinyint), +NULLIF(c_mediumtext, c_int), +NULLIF(c_mediumtext, c_bigint), +NULLIF(c_mediumtext, c_float), +NULLIF(c_mediumtext, c_double), +NULLIF(c_mediumtext, c_decimal103), +NULLIF(c_mediumtext, c_varchar10), +NULLIF(c_mediumtext, c_text), +NULLIF(c_mediumtext, c_blob), +NULLIF(c_mediumtext, c_enum), +NULLIF(c_mediumtext, c_datetime3), +NULLIF(c_mediumtext, c_timestamp3), +NULLIF(c_mediumtext, c_date), +NULLIF(c_mediumtext, c_time) +FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `NULLIF(c_mediumtext, 1)` mediumtext DEFAULT NULL, + `NULLIF(c_mediumtext, c_smallint)` mediumtext DEFAULT NULL, + `NULLIF(c_mediumtext, c_tinyint)` mediumtext DEFAULT NULL, + `NULLIF(c_mediumtext, c_int)` mediumtext DEFAULT NULL, + `NULLIF(c_mediumtext, c_bigint)` mediumtext DEFAULT NULL, + `NULLIF(c_mediumtext, c_float)` mediumtext DEFAULT NULL, + `NULLIF(c_mediumtext, c_double)` mediumtext DEFAULT NULL, + `NULLIF(c_mediumtext, c_decimal103)` mediumtext DEFAULT NULL, + `NULLIF(c_mediumtext, c_varchar10)` mediumtext DEFAULT NULL, + `NULLIF(c_mediumtext, c_text)` mediumtext DEFAULT NULL, + `NULLIF(c_mediumtext, c_blob)` mediumtext DEFAULT NULL, + `NULLIF(c_mediumtext, c_enum)` mediumtext DEFAULT NULL, + `NULLIF(c_mediumtext, c_datetime3)` mediumtext DEFAULT NULL, + `NULLIF(c_mediumtext, c_timestamp3)` mediumtext DEFAULT NULL, + `NULLIF(c_mediumtext, c_date)` mediumtext DEFAULT NULL, + `NULLIF(c_mediumtext, c_time)` mediumtext DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2; +CREATE TABLE t2 AS SELECT +NULLIF(c_longtext, 1), +NULLIF(c_longtext, c_smallint), +NULLIF(c_longtext, c_tinyint), +NULLIF(c_longtext, c_int), +NULLIF(c_longtext, c_bigint), +NULLIF(c_longtext, c_float), +NULLIF(c_longtext, c_double), +NULLIF(c_longtext, c_decimal103), +NULLIF(c_longtext, c_varchar10), +NULLIF(c_longtext, c_text), +NULLIF(c_longtext, c_blob), +NULLIF(c_longtext, c_enum), +NULLIF(c_longtext, c_datetime3), +NULLIF(c_longtext, c_timestamp3), +NULLIF(c_longtext, c_date), +NULLIF(c_longtext, c_time) +FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `NULLIF(c_longtext, 1)` longtext DEFAULT NULL, + `NULLIF(c_longtext, c_smallint)` longtext DEFAULT NULL, + `NULLIF(c_longtext, c_tinyint)` longtext DEFAULT NULL, + `NULLIF(c_longtext, c_int)` longtext DEFAULT NULL, + `NULLIF(c_longtext, c_bigint)` longtext DEFAULT NULL, + `NULLIF(c_longtext, c_float)` longtext DEFAULT NULL, + `NULLIF(c_longtext, c_double)` longtext DEFAULT NULL, + `NULLIF(c_longtext, c_decimal103)` longtext DEFAULT NULL, + `NULLIF(c_longtext, c_varchar10)` longtext DEFAULT NULL, + `NULLIF(c_longtext, c_text)` longtext DEFAULT NULL, + `NULLIF(c_longtext, c_blob)` longtext DEFAULT NULL, + `NULLIF(c_longtext, c_enum)` longtext DEFAULT NULL, + `NULLIF(c_longtext, c_datetime3)` longtext DEFAULT NULL, + `NULLIF(c_longtext, c_timestamp3)` longtext DEFAULT NULL, + `NULLIF(c_longtext, c_date)` longtext DEFAULT NULL, + `NULLIF(c_longtext, c_time)` longtext DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2; +CREATE TABLE t2 AS SELECT +NULLIF(c_tinyblob, 1), +NULLIF(c_tinyblob, c_smallint), +NULLIF(c_tinyblob, c_tinyint), +NULLIF(c_tinyblob, c_int), +NULLIF(c_tinyblob, c_bigint), +NULLIF(c_tinyblob, c_float), +NULLIF(c_tinyblob, c_double), +NULLIF(c_tinyblob, c_decimal103), +NULLIF(c_tinyblob, c_varchar10), +NULLIF(c_tinyblob, c_text), +NULLIF(c_tinyblob, c_blob), +NULLIF(c_tinyblob, c_enum), +NULLIF(c_tinyblob, c_datetime3), +NULLIF(c_tinyblob, c_timestamp3), +NULLIF(c_tinyblob, c_date), +NULLIF(c_tinyblob, c_time) +FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `NULLIF(c_tinyblob, 1)` tinyblob DEFAULT NULL, + `NULLIF(c_tinyblob, c_smallint)` tinyblob DEFAULT NULL, + `NULLIF(c_tinyblob, c_tinyint)` tinyblob DEFAULT NULL, + `NULLIF(c_tinyblob, c_int)` tinyblob DEFAULT NULL, + `NULLIF(c_tinyblob, c_bigint)` tinyblob DEFAULT NULL, + `NULLIF(c_tinyblob, c_float)` tinyblob DEFAULT NULL, + `NULLIF(c_tinyblob, c_double)` tinyblob DEFAULT NULL, + `NULLIF(c_tinyblob, c_decimal103)` tinyblob DEFAULT NULL, + `NULLIF(c_tinyblob, c_varchar10)` tinyblob DEFAULT NULL, + `NULLIF(c_tinyblob, c_text)` tinyblob DEFAULT NULL, + `NULLIF(c_tinyblob, c_blob)` tinyblob DEFAULT NULL, + `NULLIF(c_tinyblob, c_enum)` tinyblob DEFAULT NULL, + `NULLIF(c_tinyblob, c_datetime3)` tinyblob DEFAULT NULL, + `NULLIF(c_tinyblob, c_timestamp3)` tinyblob DEFAULT NULL, + `NULLIF(c_tinyblob, c_date)` tinyblob DEFAULT NULL, + `NULLIF(c_tinyblob, c_time)` tinyblob DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t2; CREATE TABLE t2 AS SELECT @@ -958,22 +1120,100 @@ FROM t1; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( - `NULLIF(c_blob, 1)` longblob DEFAULT NULL, - `NULLIF(c_blob, c_smallint)` longblob DEFAULT NULL, - `NULLIF(c_blob, c_tinyint)` longblob DEFAULT NULL, - `NULLIF(c_blob, c_int)` longblob DEFAULT NULL, - `NULLIF(c_blob, c_bigint)` longblob DEFAULT NULL, - `NULLIF(c_blob, c_float)` longblob DEFAULT NULL, - `NULLIF(c_blob, c_double)` longblob DEFAULT NULL, - `NULLIF(c_blob, c_decimal103)` longblob DEFAULT NULL, - `NULLIF(c_blob, c_varchar10)` longblob DEFAULT NULL, - `NULLIF(c_blob, c_text)` longblob DEFAULT NULL, - `NULLIF(c_blob, c_blob)` longblob DEFAULT NULL, - `NULLIF(c_blob, c_enum)` longblob DEFAULT NULL, - `NULLIF(c_blob, c_datetime3)` longblob DEFAULT NULL, - `NULLIF(c_blob, c_timestamp3)` longblob DEFAULT NULL, - `NULLIF(c_blob, c_date)` longblob DEFAULT NULL, - `NULLIF(c_blob, c_time)` longblob DEFAULT NULL + `NULLIF(c_blob, 1)` blob DEFAULT NULL, + `NULLIF(c_blob, c_smallint)` blob DEFAULT NULL, + `NULLIF(c_blob, c_tinyint)` blob DEFAULT NULL, + `NULLIF(c_blob, c_int)` blob DEFAULT NULL, + `NULLIF(c_blob, c_bigint)` blob DEFAULT NULL, + `NULLIF(c_blob, c_float)` blob DEFAULT NULL, + `NULLIF(c_blob, c_double)` blob DEFAULT NULL, + `NULLIF(c_blob, c_decimal103)` blob DEFAULT NULL, + `NULLIF(c_blob, c_varchar10)` blob DEFAULT NULL, + `NULLIF(c_blob, c_text)` blob DEFAULT NULL, + `NULLIF(c_blob, c_blob)` blob DEFAULT NULL, + `NULLIF(c_blob, c_enum)` blob DEFAULT NULL, + `NULLIF(c_blob, c_datetime3)` blob DEFAULT NULL, + `NULLIF(c_blob, c_timestamp3)` blob DEFAULT NULL, + `NULLIF(c_blob, c_date)` blob DEFAULT NULL, + `NULLIF(c_blob, c_time)` blob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2; +CREATE TABLE t2 AS SELECT +NULLIF(c_mediumblob, 1), +NULLIF(c_mediumblob, c_smallint), +NULLIF(c_mediumblob, c_tinyint), +NULLIF(c_mediumblob, c_int), +NULLIF(c_mediumblob, c_bigint), +NULLIF(c_mediumblob, c_float), +NULLIF(c_mediumblob, c_double), +NULLIF(c_mediumblob, c_decimal103), +NULLIF(c_mediumblob, c_varchar10), +NULLIF(c_mediumblob, c_text), +NULLIF(c_mediumblob, c_blob), +NULLIF(c_mediumblob, c_enum), +NULLIF(c_mediumblob, c_datetime3), +NULLIF(c_mediumblob, c_timestamp3), +NULLIF(c_mediumblob, c_date), +NULLIF(c_mediumblob, c_time) +FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `NULLIF(c_mediumblob, 1)` mediumblob DEFAULT NULL, + `NULLIF(c_mediumblob, c_smallint)` mediumblob DEFAULT NULL, + `NULLIF(c_mediumblob, c_tinyint)` mediumblob DEFAULT NULL, + `NULLIF(c_mediumblob, c_int)` mediumblob DEFAULT NULL, + `NULLIF(c_mediumblob, c_bigint)` mediumblob DEFAULT NULL, + `NULLIF(c_mediumblob, c_float)` mediumblob DEFAULT NULL, + `NULLIF(c_mediumblob, c_double)` mediumblob DEFAULT NULL, + `NULLIF(c_mediumblob, c_decimal103)` mediumblob DEFAULT NULL, + `NULLIF(c_mediumblob, c_varchar10)` mediumblob DEFAULT NULL, + `NULLIF(c_mediumblob, c_text)` mediumblob DEFAULT NULL, + `NULLIF(c_mediumblob, c_blob)` mediumblob DEFAULT NULL, + `NULLIF(c_mediumblob, c_enum)` mediumblob DEFAULT NULL, + `NULLIF(c_mediumblob, c_datetime3)` mediumblob DEFAULT NULL, + `NULLIF(c_mediumblob, c_timestamp3)` mediumblob DEFAULT NULL, + `NULLIF(c_mediumblob, c_date)` mediumblob DEFAULT NULL, + `NULLIF(c_mediumblob, c_time)` mediumblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2; +CREATE TABLE t2 AS SELECT +NULLIF(c_longblob, 1), +NULLIF(c_longblob, c_smallint), +NULLIF(c_longblob, c_tinyint), +NULLIF(c_longblob, c_int), +NULLIF(c_longblob, c_bigint), +NULLIF(c_longblob, c_float), +NULLIF(c_longblob, c_double), +NULLIF(c_longblob, c_decimal103), +NULLIF(c_longblob, c_varchar10), +NULLIF(c_longblob, c_text), +NULLIF(c_longblob, c_blob), +NULLIF(c_longblob, c_enum), +NULLIF(c_longblob, c_datetime3), +NULLIF(c_longblob, c_timestamp3), +NULLIF(c_longblob, c_date), +NULLIF(c_longblob, c_time) +FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `NULLIF(c_longblob, 1)` longblob DEFAULT NULL, + `NULLIF(c_longblob, c_smallint)` longblob DEFAULT NULL, + `NULLIF(c_longblob, c_tinyint)` longblob DEFAULT NULL, + `NULLIF(c_longblob, c_int)` longblob DEFAULT NULL, + `NULLIF(c_longblob, c_bigint)` longblob DEFAULT NULL, + `NULLIF(c_longblob, c_float)` longblob DEFAULT NULL, + `NULLIF(c_longblob, c_double)` longblob DEFAULT NULL, + `NULLIF(c_longblob, c_decimal103)` longblob DEFAULT NULL, + `NULLIF(c_longblob, c_varchar10)` longblob DEFAULT NULL, + `NULLIF(c_longblob, c_text)` longblob DEFAULT NULL, + `NULLIF(c_longblob, c_blob)` longblob DEFAULT NULL, + `NULLIF(c_longblob, c_enum)` longblob DEFAULT NULL, + `NULLIF(c_longblob, c_datetime3)` longblob DEFAULT NULL, + `NULLIF(c_longblob, c_timestamp3)` longblob DEFAULT NULL, + `NULLIF(c_longblob, c_date)` longblob DEFAULT NULL, + `NULLIF(c_longblob, c_time)` longblob DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t2; CREATE TABLE t2 AS SELECT diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result index fe456e2aa80..807a194e773 100644 --- a/mysql-test/r/union.result +++ b/mysql-test/r/union.result @@ -1449,6 +1449,28 @@ t2 CREATE TABLE `t2` ( `f8` mediumtext CHARACTER SET utf8 DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1, t2; +CREATE TABLE t1 +( +c_varchar varchar(1) character set utf8 collate utf8_general_ci, +c_tinytext tinytext, +c_text text, +c_mediumtext mediumtext, +c_longtext longtext +); +CREATE TABLE t2 AS +SELECT c_tinytext, c_text, c_mediumtext, c_longtext FROM t1 +UNION +SELECT c_varchar, c_varchar, c_varchar, c_varchar FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `c_tinytext` text CHARACTER SET utf8 DEFAULT NULL, + `c_text` mediumtext CHARACTER SET utf8 DEFAULT NULL, + `c_mediumtext` longtext CHARACTER SET utf8 DEFAULT NULL, + `c_longtext` longtext CHARACTER SET utf8 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2; +DROP TABLE t1; (select avg(1)) union (select avg(1)) union (select avg(1)) union (select avg(1)) union (select avg(1)) union (select avg(1)) union (select avg(1)) union (select avg(1)) union (select avg(1)) union diff --git a/mysql-test/suite/innodb_gis/r/1.result b/mysql-test/suite/innodb_gis/r/1.result index 31579a18ea0..3ab57babc43 100644 --- a/mysql-test/suite/innodb_gis/r/1.result +++ b/mysql-test/suite/innodb_gis/r/1.result @@ -681,7 +681,7 @@ def test t1 t1 g g 255 4294967295 0 Y 144 0 63 g select ST_asbinary(g) from t1; Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr -def ST_asbinary(g) 252 4294967295 0 Y 128 0 63 +def ST_asbinary(g) 251 4294967295 0 Y 128 0 63 ST_asbinary(g) drop table t1; create table t1 (a TEXT, b GEOMETRY NOT NULL, INDEX(b(5))); diff --git a/mysql-test/suite/innodb_gis/r/gis.result b/mysql-test/suite/innodb_gis/r/gis.result index 4257413f816..beb411505f9 100644 --- a/mysql-test/suite/innodb_gis/r/gis.result +++ b/mysql-test/suite/innodb_gis/r/gis.result @@ -681,7 +681,7 @@ def test t1 t1 g g 255 4294967295 0 Y 144 0 63 g select ST_asbinary(g) from t1; Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr -def ST_asbinary(g) 252 4294967295 0 Y 128 0 63 +def ST_asbinary(g) 251 4294967295 0 Y 128 0 63 ST_asbinary(g) drop table t1; create table t1 (a TEXT, b GEOMETRY NOT NULL, SPATIAL KEY(b)); diff --git a/mysql-test/t/create.test b/mysql-test/t/create.test index 6461204f06e..bd89f220060 100644 --- a/mysql-test/t/create.test +++ b/mysql-test/t/create.test @@ -402,6 +402,22 @@ explain t2; select * from t2; drop table t1, t2; +CREATE TABLE t1 ( + c_tinytext tinytext, + c_text text, + c_mediumtext mediumtext, + c_longtext longtext +); +CREATE TABLE t2 AS SELECT + ifnull(c_tinytext, CAST('yet another binary data' AS BINARY)), + ifnull(c_text, CAST('yet another binary data' AS BINARY)), + ifnull(c_mediumtext, CAST('yet another binary data' AS BINARY)), + ifnull(c_longtext, CAST('yet another binary data' AS BINARY)) +FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; +DROP TABLE t1; + 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; diff --git a/mysql-test/t/null.test b/mysql-test/t/null.test index 3de35a74a73..403790356ce 100644 --- a/mysql-test/t/null.test +++ b/mysql-test/t/null.test @@ -414,8 +414,14 @@ CREATE TABLE t1 c_double DOUBLE, c_decimal103 DECIMAL(10,3), c_varchar10 VARCHAR(10), + c_tinytext TINYTEXT, c_text TEXT, + c_mediumtext MEDIUMTEXT, + c_longtext LONGTEXT, + c_tinyblob TINYBLOB, c_blob BLOB, + c_mediumblob MEDIUMBLOB, + c_longblob LONGBLOB, c_enum ENUM('one','two','tree'), c_datetime3 DATETIME(3), c_timestamp3 TIMESTAMP(3), @@ -597,6 +603,27 @@ SHOW CREATE TABLE t2; DROP TABLE t2; CREATE TABLE t2 AS SELECT + NULLIF(c_tinytext, 1), + NULLIF(c_tinytext, c_smallint), + NULLIF(c_tinytext, c_tinyint), + NULLIF(c_tinytext, c_int), + NULLIF(c_tinytext, c_bigint), + NULLIF(c_tinytext, c_float), + NULLIF(c_tinytext, c_double), + NULLIF(c_tinytext, c_decimal103), + NULLIF(c_tinytext, c_varchar10), + NULLIF(c_tinytext, c_text), + NULLIF(c_tinytext, c_blob), + NULLIF(c_tinytext, c_enum), + NULLIF(c_tinytext, c_datetime3), + NULLIF(c_tinytext, c_timestamp3), + NULLIF(c_tinytext, c_date), + NULLIF(c_tinytext, c_time) +FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; + +CREATE TABLE t2 AS SELECT NULLIF(c_text, 1), NULLIF(c_text, c_smallint), NULLIF(c_text, c_tinyint), @@ -617,7 +644,70 @@ FROM t1; SHOW CREATE TABLE t2; DROP TABLE t2; -# QQ: this should probably create BLOB instead of LONGBLOB +CREATE TABLE t2 AS SELECT + NULLIF(c_mediumtext, 1), + NULLIF(c_mediumtext, c_smallint), + NULLIF(c_mediumtext, c_tinyint), + NULLIF(c_mediumtext, c_int), + NULLIF(c_mediumtext, c_bigint), + NULLIF(c_mediumtext, c_float), + NULLIF(c_mediumtext, c_double), + NULLIF(c_mediumtext, c_decimal103), + NULLIF(c_mediumtext, c_varchar10), + NULLIF(c_mediumtext, c_text), + NULLIF(c_mediumtext, c_blob), + NULLIF(c_mediumtext, c_enum), + NULLIF(c_mediumtext, c_datetime3), + NULLIF(c_mediumtext, c_timestamp3), + NULLIF(c_mediumtext, c_date), + NULLIF(c_mediumtext, c_time) +FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; + +CREATE TABLE t2 AS SELECT + NULLIF(c_longtext, 1), + NULLIF(c_longtext, c_smallint), + NULLIF(c_longtext, c_tinyint), + NULLIF(c_longtext, c_int), + NULLIF(c_longtext, c_bigint), + NULLIF(c_longtext, c_float), + NULLIF(c_longtext, c_double), + NULLIF(c_longtext, c_decimal103), + NULLIF(c_longtext, c_varchar10), + NULLIF(c_longtext, c_text), + NULLIF(c_longtext, c_blob), + NULLIF(c_longtext, c_enum), + NULLIF(c_longtext, c_datetime3), + NULLIF(c_longtext, c_timestamp3), + NULLIF(c_longtext, c_date), + NULLIF(c_longtext, c_time) +FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; + + +CREATE TABLE t2 AS SELECT + NULLIF(c_tinyblob, 1), + NULLIF(c_tinyblob, c_smallint), + NULLIF(c_tinyblob, c_tinyint), + NULLIF(c_tinyblob, c_int), + NULLIF(c_tinyblob, c_bigint), + NULLIF(c_tinyblob, c_float), + NULLIF(c_tinyblob, c_double), + NULLIF(c_tinyblob, c_decimal103), + NULLIF(c_tinyblob, c_varchar10), + NULLIF(c_tinyblob, c_text), + NULLIF(c_tinyblob, c_blob), + NULLIF(c_tinyblob, c_enum), + NULLIF(c_tinyblob, c_datetime3), + NULLIF(c_tinyblob, c_timestamp3), + NULLIF(c_tinyblob, c_date), + NULLIF(c_tinyblob, c_time) +FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; + CREATE TABLE t2 AS SELECT NULLIF(c_blob, 1), NULLIF(c_blob, c_smallint), @@ -639,6 +729,49 @@ FROM t1; SHOW CREATE TABLE t2; DROP TABLE t2; +CREATE TABLE t2 AS SELECT + NULLIF(c_mediumblob, 1), + NULLIF(c_mediumblob, c_smallint), + NULLIF(c_mediumblob, c_tinyint), + NULLIF(c_mediumblob, c_int), + NULLIF(c_mediumblob, c_bigint), + NULLIF(c_mediumblob, c_float), + NULLIF(c_mediumblob, c_double), + NULLIF(c_mediumblob, c_decimal103), + NULLIF(c_mediumblob, c_varchar10), + NULLIF(c_mediumblob, c_text), + NULLIF(c_mediumblob, c_blob), + NULLIF(c_mediumblob, c_enum), + NULLIF(c_mediumblob, c_datetime3), + NULLIF(c_mediumblob, c_timestamp3), + NULLIF(c_mediumblob, c_date), + NULLIF(c_mediumblob, c_time) +FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; + +CREATE TABLE t2 AS SELECT + NULLIF(c_longblob, 1), + NULLIF(c_longblob, c_smallint), + NULLIF(c_longblob, c_tinyint), + NULLIF(c_longblob, c_int), + NULLIF(c_longblob, c_bigint), + NULLIF(c_longblob, c_float), + NULLIF(c_longblob, c_double), + NULLIF(c_longblob, c_decimal103), + NULLIF(c_longblob, c_varchar10), + NULLIF(c_longblob, c_text), + NULLIF(c_longblob, c_blob), + NULLIF(c_longblob, c_enum), + NULLIF(c_longblob, c_datetime3), + NULLIF(c_longblob, c_timestamp3), + NULLIF(c_longblob, c_date), + NULLIF(c_longblob, c_time) +FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; + + # QQ: this should probably create a ENUM column instead of VARCHAR(4) CREATE TABLE t2 AS SELECT NULLIF(c_enum, 1), diff --git a/mysql-test/t/union.test b/mysql-test/t/union.test index e0c011e3d20..fa07dc22dbb 100644 --- a/mysql-test/t/union.test +++ b/mysql-test/t/union.test @@ -889,6 +889,22 @@ create table t2 as select *, f6 as f8 from t1 union select *, f7 from t1; show create table t2; drop table t1, t2; +CREATE TABLE t1 +( + c_varchar varchar(1) character set utf8 collate utf8_general_ci, + c_tinytext tinytext, + c_text text, + c_mediumtext mediumtext, + c_longtext longtext +); +CREATE TABLE t2 AS +SELECT c_tinytext, c_text, c_mediumtext, c_longtext FROM t1 +UNION +SELECT c_varchar, c_varchar, c_varchar, c_varchar FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; +DROP TABLE t1; + # # Bug#18175: Union select over 129 tables with a sum function fails. # |