diff options
| -rw-r--r-- | mysql-test/r/ctype_binary.result | 4 | ||||
| -rw-r--r-- | mysql-test/r/ctype_cp1251.result | 4 | ||||
| -rw-r--r-- | mysql-test/r/ctype_latin1.result | 4 | ||||
| -rw-r--r-- | mysql-test/r/ctype_ucs.result | 4 | ||||
| -rw-r--r-- | mysql-test/r/ctype_utf8.result | 4 | ||||
| -rw-r--r-- | mysql-test/r/func_hybrid_type.result | 77 | ||||
| -rw-r--r-- | mysql-test/r/gis.result | 191 | ||||
| -rw-r--r-- | mysql-test/t/func_hybrid_type.test | 46 | ||||
| -rw-r--r-- | mysql-test/t/gis.test | 73 | ||||
| -rw-r--r-- | sql/item.cc | 3 | ||||
| -rw-r--r-- | sql/item_func.cc | 117 | ||||
| -rw-r--r-- | sql/item_func.h | 29 | ||||
| -rw-r--r-- | sql/sql_type.cc | 167 | ||||
| -rw-r--r-- | sql/sql_type.h | 9 |
14 files changed, 575 insertions, 157 deletions
diff --git a/mysql-test/r/ctype_binary.result b/mysql-test/r/ctype_binary.result index 976c3df6e0e..6c16b10f1ee 100644 --- a/mysql-test/r/ctype_binary.result +++ b/mysql-test/r/ctype_binary.result @@ -608,7 +608,7 @@ create table t1 as select concat(least(1,2)) as c1; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `c1` varbinary(2) DEFAULT NULL + `c1` varbinary(1) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; select hex(concat(greatest(1,2))); @@ -618,7 +618,7 @@ create table t1 as select concat(greatest(1,2)) as c1; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `c1` varbinary(2) DEFAULT NULL + `c1` varbinary(1) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; select hex(concat(case when 11 then 22 else 33 end)); diff --git a/mysql-test/r/ctype_cp1251.result b/mysql-test/r/ctype_cp1251.result index 037f3cb07e3..f4f54d84e64 100644 --- a/mysql-test/r/ctype_cp1251.result +++ b/mysql-test/r/ctype_cp1251.result @@ -1017,7 +1017,7 @@ create table t1 as select concat(least(1,2)) as c1; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `c1` varchar(2) CHARACTER SET cp1251 DEFAULT NULL + `c1` varchar(1) CHARACTER SET cp1251 DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; select hex(concat(greatest(1,2))); @@ -1027,7 +1027,7 @@ create table t1 as select concat(greatest(1,2)) as c1; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `c1` varchar(2) CHARACTER SET cp1251 DEFAULT NULL + `c1` varchar(1) CHARACTER SET cp1251 DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; select hex(concat(case when 11 then 22 else 33 end)); diff --git a/mysql-test/r/ctype_latin1.result b/mysql-test/r/ctype_latin1.result index fc37dcfda8b..4b92c8100ce 100644 --- a/mysql-test/r/ctype_latin1.result +++ b/mysql-test/r/ctype_latin1.result @@ -1314,7 +1314,7 @@ create table t1 as select concat(least(1,2)) as c1; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `c1` varchar(2) DEFAULT NULL + `c1` varchar(1) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; select hex(concat(greatest(1,2))); @@ -1324,7 +1324,7 @@ create table t1 as select concat(greatest(1,2)) as c1; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `c1` varchar(2) DEFAULT NULL + `c1` varchar(1) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; select hex(concat(case when 11 then 22 else 33 end)); diff --git a/mysql-test/r/ctype_ucs.result b/mysql-test/r/ctype_ucs.result index 389a5d3850a..5b2fadb8168 100644 --- a/mysql-test/r/ctype_ucs.result +++ b/mysql-test/r/ctype_ucs.result @@ -2193,7 +2193,7 @@ create table t1 as select concat(least(1,2)) as c1; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `c1` varchar(2) CHARACTER SET ucs2 DEFAULT NULL + `c1` varchar(1) CHARACTER SET ucs2 DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; select hex(concat(greatest(1,2))); @@ -2203,7 +2203,7 @@ create table t1 as select concat(greatest(1,2)) as c1; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `c1` varchar(2) CHARACTER SET ucs2 DEFAULT NULL + `c1` varchar(1) CHARACTER SET ucs2 DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; select hex(concat(case when 11 then 22 else 33 end)); diff --git a/mysql-test/r/ctype_utf8.result b/mysql-test/r/ctype_utf8.result index eb18bb5cbf9..573799ff6e5 100644 --- a/mysql-test/r/ctype_utf8.result +++ b/mysql-test/r/ctype_utf8.result @@ -3065,7 +3065,7 @@ create table t1 as select concat(least(1,2)) as c1; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `c1` varchar(2) CHARACTER SET utf8 DEFAULT NULL + `c1` varchar(1) CHARACTER SET utf8 DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; select hex(concat(greatest(1,2))); @@ -3075,7 +3075,7 @@ create table t1 as select concat(greatest(1,2)) as c1; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `c1` varchar(2) CHARACTER SET utf8 DEFAULT NULL + `c1` varchar(1) CHARACTER SET utf8 DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; select hex(concat(case when 11 then 22 else 33 end)); diff --git a/mysql-test/r/func_hybrid_type.result b/mysql-test/r/func_hybrid_type.result index 324db452f9b..edb3a553492 100644 --- a/mysql-test/r/func_hybrid_type.result +++ b/mysql-test/r/func_hybrid_type.result @@ -1615,8 +1615,8 @@ def coalesce___b 254 1 1 Y 0 39 8 def coalesce_b_b 254 1 1 Y 0 39 8 def if_______b_b 254 1 1 Y 0 39 8 def ifnull___b_b 254 1 1 Y 0 39 8 -def least____b_b 254 1 1 Y 0 0 8 -def greatest_b_b 254 1 1 Y 0 0 8 +def least____b_b 254 1 1 Y 0 39 8 +def greatest_b_b 254 1 1 Y 0 39 8 ___________a a case_______a a case_____a_a a @@ -2190,10 +2190,10 @@ def if_______a_b 12 19 19 Y 128 0 63 def if_______b_a 12 19 19 Y 128 0 63 def ifnull___a_b 12 19 19 Y 128 0 63 def ifnull___b_a 12 19 19 Y 128 0 63 -def least____a_b 12 10 19 Y 128 0 63 -def least____b_a 12 10 19 Y 128 0 63 -def greatest_a_b 12 10 19 Y 128 0 63 -def greatest_b_a 12 10 19 Y 128 0 63 +def least____a_b 12 19 19 Y 128 0 63 +def least____b_a 12 19 19 Y 128 0 63 +def greatest_a_b 12 19 19 Y 128 0 63 +def greatest_b_a 12 19 19 Y 128 0 63 case_____a_b 2010-01-01 00:00:00 case_____b_a 2001-01-01 10:20:30 coalesce_a_b 2010-01-01 00:00:00 @@ -2317,8 +2317,8 @@ def coalesce___a 7 19 19 N 129 0 63 def coalesce_a_a 7 19 19 N 129 0 63 def if_______a_a 7 19 19 N 129 0 63 def ifnull___a_a 7 19 19 N 129 0 63 -def least____a_a 7 19 19 N 161 0 63 -def greatest_a_a 7 19 19 N 161 0 63 +def least____a_a 7 19 19 N 129 0 63 +def greatest_a_a 7 19 19 N 129 0 63 def test t1 t1 b ___________b 11 10 8 Y 128 0 63 def case_______b 11 10 8 Y 128 0 63 def case_____b_b 11 10 8 Y 128 0 63 @@ -3050,7 +3050,7 @@ DROP TABLE t1; SET timestamp=UNIX_TIMESTAMP('2010-01-01 01:02:03'); SELECT GREATEST(CURRENT_TIME, CURRENT_DATE), COALESCE(CURRENT_TIME, CURRENT_DATE); Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr -def GREATEST(CURRENT_TIME, CURRENT_DATE) 12 10 19 N 129 0 63 +def GREATEST(CURRENT_TIME, CURRENT_DATE) 12 19 19 N 129 0 63 def COALESCE(CURRENT_TIME, CURRENT_DATE) 12 19 19 N 129 0 63 GREATEST(CURRENT_TIME, CURRENT_DATE) COALESCE(CURRENT_TIME, CURRENT_DATE) 2010-01-01 01:02:03 2010-01-01 01:02:03 @@ -3058,9 +3058,14 @@ CREATE TABLE t1 (a TIMESTAMP); INSERT INTO t1 VALUES ('2010-01-01 10:20:30'); SELECT GREATEST(a,a) FROM t1; Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr -def GREATEST(a,a) 7 19 19 N 161 0 63 +def GREATEST(a,a) 7 19 19 N 129 0 63 GREATEST(a,a) 2010-01-01 10:20:30 +SELECT COALESCE(a,a) FROM t1; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def COALESCE(a,a) 7 19 19 N 129 0 63 +COALESCE(a,a) +2010-01-01 10:20:30 DROP TABLE t1; CREATE TABLE t1 (a TIMESTAMP, b DATETIME); CREATE TABLE t2 AS SELECT LEAST(a,a),LEAST(b,b),LEAST(a,b) FROM t1; @@ -3445,3 +3450,55 @@ DROP TABLE t1; # # End of 10.1 tests # +# +# Start of 10.3 tests +# +# +# MDEV-12497 Wrong data type for LEAST(latin1_expr, utf8_expr) +# +CREATE TABLE t1 AS SELECT +LEAST(_latin1'aaa',_utf8 0xC39F) AS c1, +COALESCE(_latin1'aaa',_utf8 0xC39F) AS c2; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(3) CHARACTER SET utf8 NOT NULL, + `c2` varchar(3) CHARACTER SET utf8 NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +SELECT * FROM t1; +c1 c2 +aaa aaa +DROP TABLE t1; +# +# MDEV-12504 Wrong data type for LEAST(date_expr,time_expr) +# +CREATE TABLE t1 AS SELECT +LEAST(DATE'2001-01-01', TIME'10:20:30') AS c1, +CONCAT(LEAST(DATE'2001-01-01', TIME'10:20:30')) AS c2; +SELECT * FROM t1; +c1 c2 +2001-01-01 00:00:00 2001-01-01 00:00:00 +DROP TABLE t1; +# +# MDEV-12505 Wrong data type for GREATEST(bit_column, int_column) +# +CREATE TABLE t1 (a BIT(64),b INT); +INSERT INTO t1 VALUES (0xFFFFFFFFFFFFFFFF,-1); +SELECT a>b, COALESCE(a,b), GREATEST(a,b) FROM t1; +a>b COALESCE(a,b) GREATEST(a,b) +1 18446744073709551615 18446744073709551615 +CREATE TABLE t2 AS SELECT COALESCE(a,b),GREATEST(a,b) FROM t1; +SELECT * FROM t2; +COALESCE(a,b) GREATEST(a,b) +18446744073709551615 18446744073709551615 +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `COALESCE(a,b)` decimal(64,0) DEFAULT NULL, + `GREATEST(a,b)` decimal(64,0) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2; +DROP TABLE t1; +# +# End of 10.3 tests +# diff --git a/mysql-test/r/gis.result b/mysql-test/r/gis.result index 6a7b7e7b8bf..77150ee3142 100644 --- a/mysql-test/r/gis.result +++ b/mysql-test/r/gis.result @@ -2792,7 +2792,7 @@ t2 CREATE TABLE `t2` ( CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1 ERROR: Illegal parameter data types set and geometry for operation 'UNION' -# This does not preserve geometry type (MDEV-9405) +# This does not preserve geometry type (MDEV-12560) CREATE TABLE t1 AS SELECT COALESCE(NULL, Point(1,1)); SHOW CREATE TABLE t1; Table Create Table @@ -3711,6 +3711,195 @@ CASE a WHEN POINT(1,1) THEN "a" WHEN POINT(1,2) THEN "b" END DROP PROCEDURE p1; DROP PROCEDURE p2; # +# MDEV-12506 Split Item_func_min_max::fix_length_and_dec() into methods in Type_handler +# +CREATE PROCEDURE p2(query TEXT) +BEGIN +DECLARE errcount INT DEFAULT 0; +DECLARE CONTINUE HANDLER FOR SQLEXCEPTION +BEGIN +SET errcount = errcount+1; +#SHOW WARNINGS; +GET DIAGNOSTICS CONDITION 1 @p= MESSAGE_TEXT; +SELECT @p AS `ERROR: `; +END; +SELECT query AS ``; +EXECUTE IMMEDIATE query; +IF errcount = 0 +THEN +SHOW CREATE TABLE t2; +DROP TABLE t2; +END IF; +END; +$$ +CREATE PROCEDURE p1(query TEXT) +BEGIN +SELECT query AS `-------------------------------------`; +EXECUTE IMMEDIATE query; +CALL p2('CREATE TABLE t2 AS SELECT LEAST(a,b) FROM t1'); +DROP TABLE t1; +END; +$$ +------------------------------------- +CREATE TABLE t1 (a CHAR(10), b Point) + +CREATE TABLE t2 AS SELECT LEAST(a,b) FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `LEAST(a,b)` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +------------------------------------- +CREATE TABLE t1 (a VARCHAR(10), b Point) + +CREATE TABLE t2 AS SELECT LEAST(a,b) FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `LEAST(a,b)` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +------------------------------------- +CREATE TABLE t1 (a TINYTEXT, b Point) + +CREATE TABLE t2 AS SELECT LEAST(a,b) FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `LEAST(a,b)` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +------------------------------------- +CREATE TABLE t1 (a TEXT, b Point) + +CREATE TABLE t2 AS SELECT LEAST(a,b) FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `LEAST(a,b)` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +------------------------------------- +CREATE TABLE t1 (a MEDIUMTEXT, b Point) + +CREATE TABLE t2 AS SELECT LEAST(a,b) FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `LEAST(a,b)` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +------------------------------------- +CREATE TABLE t1 (a LONGTEXT, b Point) + +CREATE TABLE t2 AS SELECT LEAST(a,b) FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `LEAST(a,b)` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +------------------------------------- +CREATE TABLE t1 (a TINYINT, b Point) + +CREATE TABLE t2 AS SELECT LEAST(a,b) FROM t1 +ERROR: +Illegal parameter data types tinyint and geometry for operation 'least' +------------------------------------- +CREATE TABLE t1 (a SMALLINT, b Point) + +CREATE TABLE t2 AS SELECT LEAST(a,b) FROM t1 +ERROR: +Illegal parameter data types smallint and geometry for operation 'least' +------------------------------------- +CREATE TABLE t1 (a MEDIUMINT, b Point) + +CREATE TABLE t2 AS SELECT LEAST(a,b) FROM t1 +ERROR: +Illegal parameter data types mediumint and geometry for operation 'least' +------------------------------------- +CREATE TABLE t1 (a INT, b Point) + +CREATE TABLE t2 AS SELECT LEAST(a,b) FROM t1 +ERROR: +Illegal parameter data types int and geometry for operation 'least' +------------------------------------- +CREATE TABLE t1 (a BIGINT, b Point) + +CREATE TABLE t2 AS SELECT LEAST(a,b) FROM t1 +ERROR: +Illegal parameter data types bigint and geometry for operation 'least' +------------------------------------- +CREATE TABLE t1 (a FLOAT, b Point) + +CREATE TABLE t2 AS SELECT LEAST(a,b) FROM t1 +ERROR: +Illegal parameter data types float and geometry for operation 'least' +------------------------------------- +CREATE TABLE t1 (a DOUBLE, b Point) + +CREATE TABLE t2 AS SELECT LEAST(a,b) FROM t1 +ERROR: +Illegal parameter data types double and geometry for operation 'least' +------------------------------------- +CREATE TABLE t1 (a DECIMAL(10,2), b Point) + +CREATE TABLE t2 AS SELECT LEAST(a,b) FROM t1 +ERROR: +Illegal parameter data types decimal and geometry for operation 'least' +------------------------------------- +CREATE TABLE t1 (a BIT(8), b Point) + +CREATE TABLE t2 AS SELECT LEAST(a,b) FROM t1 +ERROR: +Illegal parameter data types bit and geometry for operation 'least' +------------------------------------- +CREATE TABLE t1 (a TIME, b Point) + +CREATE TABLE t2 AS SELECT LEAST(a,b) FROM t1 +ERROR: +Illegal parameter data types time and geometry for operation 'least' +------------------------------------- +CREATE TABLE t1 (a DATE, b Point) + +CREATE TABLE t2 AS SELECT LEAST(a,b) FROM t1 +ERROR: +Illegal parameter data types date and geometry for operation 'least' +------------------------------------- +CREATE TABLE t1 (a DATETIME, b Point) + +CREATE TABLE t2 AS SELECT LEAST(a,b) FROM t1 +ERROR: +Illegal parameter data types datetime and geometry for operation 'least' +------------------------------------- +CREATE TABLE t1 (a TIMESTAMP, b Point) + +CREATE TABLE t2 AS SELECT LEAST(a,b) FROM t1 +ERROR: +Illegal parameter data types timestamp and geometry for operation 'least' +------------------------------------- +CREATE TABLE t1 (a YEAR, b Point) + +CREATE TABLE t2 AS SELECT LEAST(a,b) FROM t1 +ERROR: +Illegal parameter data types year and geometry for operation 'least' +# This LEAST(ENUM,GEOMETRY) creates BLOB, but fails on error with UNION (see MDEV-12503) +------------------------------------- +CREATE TABLE t1 (a ENUM(0x61), b Point) + +CREATE TABLE t2 AS SELECT LEAST(a,b) FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `LEAST(a,b)` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +------------------------------------- +CREATE TABLE t1 (a SET(0x61), b Point) + +CREATE TABLE t2 AS SELECT LEAST(a,b) FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `LEAST(a,b)` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +# This does not preserve geometry type (MDEV-9405) +CREATE TABLE t1 AS SELECT LEAST(NULL, Point(1,1)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `LEAST(NULL, Point(1,1))` geometry DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +DROP PROCEDURE p1; +DROP PROCEDURE p2; +# # MDEV-12001 Split Item_func_round::fix_length_and_dec to virtual methods in Type_handler # CREATE TABLE t1 (a GEOMETRY); diff --git a/mysql-test/t/func_hybrid_type.test b/mysql-test/t/func_hybrid_type.test index dd8a399025b..2efbdbe0451 100644 --- a/mysql-test/t/func_hybrid_type.test +++ b/mysql-test/t/func_hybrid_type.test @@ -155,6 +155,8 @@ CREATE TABLE t1 (a TIMESTAMP); INSERT INTO t1 VALUES ('2010-01-01 10:20:30'); # Expect TIMESTAMP type (7) in metadata SELECT GREATEST(a,a) FROM t1; +# Similar to this one +SELECT COALESCE(a,a) FROM t1; DROP TABLE t1; --disable_metadata --enable_ps_protocol @@ -459,3 +461,47 @@ DROP TABLE t1; --echo # --echo # End of 10.1 tests --echo # + +--echo # +--echo # Start of 10.3 tests +--echo # + +--echo # +--echo # MDEV-12497 Wrong data type for LEAST(latin1_expr, utf8_expr) +--echo # + +CREATE TABLE t1 AS SELECT + LEAST(_latin1'aaa',_utf8 0xC39F) AS c1, + COALESCE(_latin1'aaa',_utf8 0xC39F) AS c2; +SHOW CREATE TABLE t1; +SELECT * FROM t1; +DROP TABLE t1; + +--echo # +--echo # MDEV-12504 Wrong data type for LEAST(date_expr,time_expr) +--echo # + +CREATE TABLE t1 AS SELECT + LEAST(DATE'2001-01-01', TIME'10:20:30') AS c1, + CONCAT(LEAST(DATE'2001-01-01', TIME'10:20:30')) AS c2; +SELECT * FROM t1; +DROP TABLE t1; + +--echo # +--echo # MDEV-12505 Wrong data type for GREATEST(bit_column, int_column) +--echo # + +CREATE TABLE t1 (a BIT(64),b INT); +INSERT INTO t1 VALUES (0xFFFFFFFFFFFFFFFF,-1); +SELECT a>b, COALESCE(a,b), GREATEST(a,b) FROM t1; +CREATE TABLE t2 AS SELECT COALESCE(a,b),GREATEST(a,b) FROM t1; +SELECT * FROM t2; +SHOW CREATE TABLE t2; +DROP TABLE t2; +DROP TABLE t1; + + +--echo # +--echo # End of 10.3 tests +--echo # + diff --git a/mysql-test/t/gis.test b/mysql-test/t/gis.test index 1b355b70bc6..9b3dc69a7d1 100644 --- a/mysql-test/t/gis.test +++ b/mysql-test/t/gis.test @@ -1789,7 +1789,7 @@ CALL p1('CREATE TABLE t1 (a ENUM(0x61), b Point)'); CALL p1('CREATE TABLE t1 (a SET(0x61), b Point)'); --enable_query_log ---echo # This does not preserve geometry type (MDEV-9405) +--echo # This does not preserve geometry type (MDEV-12560) CREATE TABLE t1 AS SELECT COALESCE(NULL, Point(1,1)); SHOW CREATE TABLE t1; DROP TABLE t1; @@ -1870,6 +1870,77 @@ DROP PROCEDURE p2; --echo # +--echo # MDEV-12506 Split Item_func_min_max::fix_length_and_dec() into methods in Type_handler +--echo # + +DELIMITER $$; +CREATE PROCEDURE p2(query TEXT) +BEGIN + DECLARE errcount INT DEFAULT 0; + DECLARE CONTINUE HANDLER FOR SQLEXCEPTION + BEGIN + SET errcount = errcount+1; + #SHOW WARNINGS; + GET DIAGNOSTICS CONDITION 1 @p= MESSAGE_TEXT; + SELECT @p AS `ERROR: `; + END; + SELECT query AS ``; + EXECUTE IMMEDIATE query; + IF errcount = 0 + THEN + SHOW CREATE TABLE t2; + DROP TABLE t2; + END IF; +END; +$$ +CREATE PROCEDURE p1(query TEXT) +BEGIN + SELECT query AS `-------------------------------------`; + EXECUTE IMMEDIATE query; + CALL p2('CREATE TABLE t2 AS SELECT LEAST(a,b) FROM t1'); + DROP TABLE t1; +END; +$$ +DELIMITER ;$$ + +--disable_query_log +CALL p1('CREATE TABLE t1 (a CHAR(10), b Point)'); +CALL p1('CREATE TABLE t1 (a VARCHAR(10), b Point)'); +CALL p1('CREATE TABLE t1 (a TINYTEXT, b Point)'); +CALL p1('CREATE TABLE t1 (a TEXT, b Point)'); +CALL p1('CREATE TABLE t1 (a MEDIUMTEXT, b Point)'); +CALL p1('CREATE TABLE t1 (a LONGTEXT, b Point)'); + +CALL p1('CREATE TABLE t1 (a TINYINT, b Point)'); +CALL p1('CREATE TABLE t1 (a SMALLINT, b Point)'); +CALL p1('CREATE TABLE t1 (a MEDIUMINT, b Point)'); +CALL p1('CREATE TABLE t1 (a INT, b Point)'); +CALL p1('CREATE TABLE t1 (a BIGINT, b Point)'); +CALL p1('CREATE TABLE t1 (a FLOAT, b Point)'); +CALL p1('CREATE TABLE t1 (a DOUBLE, b Point)'); +CALL p1('CREATE TABLE t1 (a DECIMAL(10,2), b Point)'); +CALL p1('CREATE TABLE t1 (a BIT(8), b Point)'); + +CALL p1('CREATE TABLE t1 (a TIME, b Point)'); +CALL p1('CREATE TABLE t1 (a DATE, b Point)'); +CALL p1('CREATE TABLE t1 (a DATETIME, b Point)'); +CALL p1('CREATE TABLE t1 (a TIMESTAMP, b Point)'); +CALL p1('CREATE TABLE t1 (a YEAR, b Point)'); + +--echo # This LEAST(ENUM,GEOMETRY) creates BLOB, but fails on error with UNION (see MDEV-12503) +CALL p1('CREATE TABLE t1 (a ENUM(0x61), b Point)'); +CALL p1('CREATE TABLE t1 (a SET(0x61), b Point)'); +--enable_query_log + +--echo # This does not preserve geometry type (MDEV-9405) +CREATE TABLE t1 AS SELECT LEAST(NULL, Point(1,1)); +SHOW CREATE TABLE t1; +DROP TABLE t1; + +DROP PROCEDURE p1; +DROP PROCEDURE p2; + +--echo # --echo # MDEV-12001 Split Item_func_round::fix_length_and_dec to virtual methods in Type_handler --echo # diff --git a/sql/item.cc b/sql/item.cc index 0456adc3fb1..f5ef8f30dff 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -10187,7 +10187,8 @@ void Item_cache_row::set_null() Item_type_holder::Item_type_holder(THD *thd, Item *item) :Item(thd, item), Type_handler_hybrid_field_type(item->real_type_handler()), - enum_set_typelib(0) + enum_set_typelib(0), + geometry_type(Field::GEOM_GEOMETRY) { DBUG_ASSERT(item->fixed); maybe_null= item->maybe_null; diff --git a/sql/item_func.cc b/sql/item_func.cc index a52c2609412..96057ec31f5 100644 --- a/sql/item_func.cc +++ b/sql/item_func.cc @@ -2697,119 +2697,12 @@ double Item_func_units::val_real() } -void Item_func_min_max::fix_length_and_dec() +bool Item_func_min_max::fix_attributes(Item **items, uint nitems) { - uint unsigned_count= 0; - int max_int_part=0; - decimals=0; - max_length=0; - maybe_null=0; - Item_result tmp_cmp_type= args[0]->cmp_type(); - uint string_type_count= 0; - uint temporal_type_count= 0; - enum_field_types temporal_field_type= MYSQL_TYPE_DATETIME; - - for (uint i=0 ; i < arg_count ; i++) - { - set_if_bigger(max_length, args[i]->max_length); - set_if_bigger(decimals, args[i]->decimals); - set_if_bigger(max_int_part, args[i]->decimal_int_part()); - unsigned_count+= args[i]->unsigned_flag; - if (args[i]->maybe_null) - maybe_null= 1; - tmp_cmp_type= item_cmp_type(tmp_cmp_type, args[i]->cmp_type()); - string_type_count+= args[i]->cmp_type() == STRING_RESULT; - if (args[i]->cmp_type() == TIME_RESULT) - { - if (!temporal_type_count) - temporal_field_type= args[i]->field_type(); - else - temporal_field_type= Field::field_type_merge(temporal_field_type, - args[i]->field_type()); - temporal_type_count++; - } - } - unsigned_flag= unsigned_count == arg_count; // if all args are unsigned - - switch (tmp_cmp_type) { - case TIME_RESULT: - // At least one temporal argument was found. - collation.set_numeric(); - set_handler_by_field_type(temporal_field_type); - if (is_temporal_type_with_time(temporal_field_type)) - set_if_smaller(decimals, TIME_SECOND_PART_DIGITS); - else - decimals= 0; - break; - - case STRING_RESULT: - if (aggregate_for_result(func_name(), args, arg_count, false)) - return; - /* - All arguments are of string-alike types: - CHAR, VARCHAR, TEXT, BINARY, VARBINARY, BLOB, SET, ENUM - No numeric and no temporal types were found. - */ - agg_arg_charsets_for_string_result_with_comparison(collation, - args, arg_count); - break; - - case INT_RESULT: - /* - All arguments have INT-alike types: - TINY, SHORT, LONG, LONGLONG, INT24, YEAR, BIT. - */ - collation.set_numeric(); - fix_char_length(my_decimal_precision_to_length_no_truncation(max_int_part + - decimals, - decimals, - unsigned_flag)); - if (unsigned_count != 0 && unsigned_count != arg_count) - { - /* - If all args are of INT-alike type, but have different unsigned_flag, - then change type to DECIMAL. - */ - set_handler_by_field_type(MYSQL_TYPE_NEWDECIMAL); - } - else - { - /* - There are only INT-alike arguments with equal unsigned_flag. - Aggregate types to get the best covering type. - Treat BIT as LONGLONG when aggregating to non-BIT types. - Possible final type: TINY, SHORT, LONG, LONGLONG, INT24, YEAR, BIT. - */ - if (aggregate_for_result(func_name(), args, arg_count, true)) - return; - } - break; - - case DECIMAL_RESULT: - // All arguments are of DECIMAL type - collation.set_numeric(); - fix_char_length(my_decimal_precision_to_length_no_truncation(max_int_part + - decimals, - decimals, - unsigned_flag)); - set_handler_by_field_type(MYSQL_TYPE_NEWDECIMAL); - break; - - case ROW_RESULT: - DBUG_ASSERT(0); - // Pass through - case REAL_RESULT: - collation.set_numeric(); - fix_char_length(float_length(decimals)); - /* - Set type to DOUBLE, as Item_func::create_tmp_field() does not - distinguish between DOUBLE and FLOAT and always creates Field_double. - Perhaps we should eventually change this to use aggregate_for_result() - and fix Item_func::create_tmp_field() to create Field_float when possible. - */ - set_handler_by_field_type(MYSQL_TYPE_DOUBLE); - break; - } + bool rc= Item_func_min_max::type_handler()-> + Item_func_min_max_fix_attributes(current_thd, this, items, nitems); + DBUG_ASSERT(!rc || current_thd->is_error()); + return rc; } diff --git a/sql/item_func.h b/sql/item_func.h index a367a0d74c4..3fc2586729d 100644 --- a/sql/item_func.h +++ b/sql/item_func.h @@ -1434,6 +1434,8 @@ class Item_func_min_max :public Item_hybrid_func { String tmp_value; int cmp_sign; +protected: + bool fix_attributes(Item **item, uint nitems); public: Item_func_min_max(THD *thd, List<Item> &list, int cmp_sign_arg): Item_hybrid_func(thd, list), cmp_sign(cmp_sign_arg) @@ -1474,7 +1476,32 @@ public: return Item_func_min_max::type_handler()-> Item_func_min_max_get_date(this, res, fuzzy_date); } - void fix_length_and_dec(); + void aggregate_attributes_real(Item **items, uint nitems) + { + /* + Aggregating attributes for the double data type for LEAST/GREATEST + is almost the same with aggregating for CASE-alike hybrid functions, + (CASE..THEN, COALESCE, IF, etc). + There is one notable difference though, when a numeric argument is mixed + with a string argument: + - CASE-alike functions return a string data type in such cases + COALESCE(10,'x') -> VARCHAR(2) = '10' + - LEAST/GREATEST returns double: + GREATEST(10,'10e4') -> DOUBLE = 100000 + As the string argument can represent a number in the scientific notation, + like in the example above, max_length of the result can be longer than + max_length of the arguments. To handle this properly, max_length is + additionally assigned to the result of float_length(decimals). + */ + Item_func::aggregate_attributes_real(items, nitems); + max_length= float_length(decimals); + } + void fix_length_and_dec() + { + if (aggregate_for_min_max(func_name(), args, arg_count)) + return; + fix_attributes(args, arg_count); + } }; class Item_func_min :public Item_func_min_max diff --git a/sql/sql_type.cc b/sql/sql_type.cc index a2994c74250..6a150703831 100644 --- a/sql/sql_type.cc +++ b/sql/sql_type.cc @@ -370,7 +370,6 @@ Type_handler_hybrid_field_type::aggregate_for_result(const char *funcname, return true; } set_handler(items[0]->type_handler()); - uint unsigned_count= items[0]->unsigned_flag; for (uint i= 1 ; i < nitems ; i++) { const Type_handler *cur= items[i]->type_handler(); @@ -388,26 +387,6 @@ Type_handler_hybrid_field_type::aggregate_for_result(const char *funcname, type_handler()->name().ptr(), cur->name().ptr(), funcname); return true; } - unsigned_count+= items[i]->unsigned_flag; - } - switch (field_type()) { - case MYSQL_TYPE_TINY: - case MYSQL_TYPE_SHORT: - case MYSQL_TYPE_LONG: - case MYSQL_TYPE_LONGLONG: - case MYSQL_TYPE_INT24: - case MYSQL_TYPE_YEAR: - case MYSQL_TYPE_BIT: - if (unsigned_count != 0 && unsigned_count != nitems) - { - /* - If all arguments are of INT-alike type but have different - unsigned_flag, then convert to DECIMAL. - */ - set_handler(&type_handler_newdecimal); - } - default: - break; } return false; } @@ -479,6 +458,114 @@ Type_handler_hybrid_field_type::aggregate_for_comparison(const Type_handler *h) } +/** + Aggregate data type handler for LEAST/GRATEST. + aggregate_for_min_max() is close to aggregate_for_comparison(), + but tries to preserve the exact type handler for string, int and temporal + data types (instead of converting to super-types). + FLOAT is not preserved and is converted to its super-type (DOUBLE). + This should probably fixed eventually, for symmetry. +*/ + +bool +Type_handler_hybrid_field_type::aggregate_for_min_max(const Type_handler *h) +{ + if (!m_type_handler->is_traditional_type() || + !h->is_traditional_type()) + { + /* + If at least one data type is non-traditional, + do aggregation for result immediately. + For now we suppose that these two expressions: + - LEAST(type1, type2) + - COALESCE(type1, type2) + return the same data type (or both expressions return error) + if type1 and/or type2 are non-traditional. + This may change in the future. + */ + h= type_handler_data-> + m_type_aggregator_for_result.find_handler(m_type_handler, h); + if (!h) + return true; + m_type_handler= h; + return false; + } + + Item_result a= cmp_type(); + Item_result b= h->cmp_type(); + DBUG_ASSERT(a != ROW_RESULT); // Disallowed by check_cols() in fix_fields() + DBUG_ASSERT(b != ROW_RESULT); // Disallowed by check_cols() in fix_fields() + + if (a == STRING_RESULT && b == STRING_RESULT) + m_type_handler= + Type_handler::aggregate_for_result_traditional(m_type_handler, h); + else if (a == INT_RESULT && b == INT_RESULT) + { + // BIT aggregates with non-BIT as BIGINT + if (m_type_handler != h) + { + if (m_type_handler == &type_handler_bit) + m_type_handler= &type_handler_longlong; + else if (h == &type_handler_bit) + h= &type_handler_longlong; + } + m_type_handler= + Type_handler::aggregate_for_result_traditional(m_type_handler, h); + } + else if (a == TIME_RESULT || b == TIME_RESULT) + { + if ((a == TIME_RESULT) + (b == TIME_RESULT) == 1) + { + /* + We're here if there's only one temporal data type: + either m_type_handler or h. + */ + if (b == TIME_RESULT) + m_type_handler= h; // Temporal types bit non-temporal types + } + else + { + /* + We're here if both m_type_handler and h are temporal data types. + */ + m_type_handler= + Type_handler::aggregate_for_result_traditional(m_type_handler, h); + } + } + else if ((a == INT_RESULT || a == DECIMAL_RESULT) && + (b == INT_RESULT || b == DECIMAL_RESULT)) + { + m_type_handler= &type_handler_newdecimal; + } + else + { + m_type_handler= &type_handler_double; + } + return false; +} + + +bool +Type_handler_hybrid_field_type::aggregate_for_min_max(const char *funcname, + Item **items, uint nitems) +{ + // LEAST/GREATEST require at least two arguments + DBUG_ASSERT(nitems > 1); + set_handler(items[0]->type_handler()); + for (uint i= 1; i < nitems; i++) + { + const Type_handler *cur= items[i]->type_handler(); + if (aggregate_for_min_max(cur)) + { + my_error(ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION, MYF(0), + type_handler()->name().ptr(), cur->name().ptr(), funcname); + return true; + } + } + return false; +} + + const Type_handler * Type_handler::aggregate_for_num_op_traditional(const Type_handler *h0, const Type_handler *h1) @@ -1313,6 +1400,17 @@ bool Type_handler_int_result:: Item_hybrid_func_fix_attributes(THD *thd, Item_hybrid_func *func, Item **items, uint nitems) const { + uint unsigned_flag= items[0]->unsigned_flag; + for (uint i= 1; i < nitems; i++) + { + if (unsigned_flag != items[i]->unsigned_flag) + { + // Convert a mixture of signed and unsigned int to decimal + func->set_handler(&type_handler_newdecimal); + func->aggregate_attributes_decimal(items, nitems); + return false; + } + } func->aggregate_attributes_int(items, nitems); return false; } @@ -1379,6 +1477,33 @@ bool Type_handler_timestamp_common:: return false; } +/*************************************************************************/ + +bool Type_handler:: + Item_func_min_max_fix_attributes(THD *thd, Item_func_min_max *func, + Item **items, uint nitems) const +{ + /* + Aggregating attributes for LEAST/GREATES is exactly the same + with aggregating for CASE-alike functions (e.g. COALESCE) + for the majority of data type handlers. + */ + return Item_hybrid_func_fix_attributes(thd, func, items, nitems); +} + + +bool Type_handler_real_result:: + Item_func_min_max_fix_attributes(THD *thd, Item_func_min_max *func, + Item **items, uint nitems) const +{ + /* + DOUBLE is an exception and aggregates attributes differently + for LEAST/GREATEST vs CASE-alike functions. See the comment in + Item_func_min_max::aggregate_attributes_real(). + */ + func->aggregate_attributes_real(items, nitems); + return false; +} /*************************************************************************/ diff --git a/sql/sql_type.h b/sql/sql_type.h index 2c8d5df050f..21b8e75db11 100644 --- a/sql/sql_type.h +++ b/sql/sql_type.h @@ -485,6 +485,10 @@ public: virtual bool Item_hybrid_func_fix_attributes(THD *thd, Item_hybrid_func *func, Item **items, uint nitems) const= 0; + virtual bool Item_func_min_max_fix_attributes(THD *thd, + Item_func_min_max *func, + Item **items, + uint nitems) const; virtual bool Item_sum_hybrid_fix_length_and_dec(Item_sum_hybrid *) const= 0; virtual bool Item_sum_sum_fix_length_and_dec(Item_sum_sum *) const= 0; virtual bool Item_sum_avg_fix_length_and_dec(Item_sum_avg *) const= 0; @@ -840,6 +844,8 @@ public: bool set_comparator_func(Arg_comparator *cmp) const; bool Item_hybrid_func_fix_attributes(THD *thd, Item_hybrid_func *func, Item **items, uint nitems) const; + bool Item_func_min_max_fix_attributes(THD *thd, Item_func_min_max *func, + Item **items, uint nitems) const; bool Item_sum_hybrid_fix_length_and_dec(Item_sum_hybrid *func) const; bool Item_sum_sum_fix_length_and_dec(Item_sum_sum *) const; bool Item_sum_avg_fix_length_and_dec(Item_sum_avg *) const; @@ -1602,6 +1608,7 @@ public: class Type_handler_hybrid_field_type { const Type_handler *m_type_handler; + bool aggregate_for_min_max(const Type_handler *other); public: Type_handler_hybrid_field_type(); Type_handler_hybrid_field_type(const Type_handler *handler) @@ -1657,6 +1664,8 @@ public: bool aggregate_for_result(const Type_handler *other); bool aggregate_for_result(const char *funcname, Item **item, uint nitems, bool treat_bit_as_number); + bool aggregate_for_min_max(const char *funcname, Item **item, uint nitems); + bool aggregate_for_num_op(const class Type_aggregator *aggregator, const Type_handler *h0, const Type_handler *h1); }; |