diff options
| -rw-r--r-- | mysql-test/r/func_str.result | 8 | ||||
| -rw-r--r-- | mysql-test/r/gis.result | 612 | ||||
| -rw-r--r-- | mysql-test/t/func_str.test | 2 | ||||
| -rw-r--r-- | mysql-test/t/gis.test | 79 | ||||
| -rw-r--r-- | sql/field.cc | 222 | ||||
| -rw-r--r-- | sql/field.h | 1 | ||||
| -rw-r--r-- | sql/item.cc | 53 | ||||
| -rw-r--r-- | sql/item_cmpfunc.cc | 86 | ||||
| -rw-r--r-- | sql/item_cmpfunc.h | 8 | ||||
| -rw-r--r-- | sql/item_func.cc | 8 | ||||
| -rw-r--r-- | sql/item_func.h | 2 | ||||
| -rw-r--r-- | sql/share/errmsg-utf8.txt | 2 | ||||
| -rw-r--r-- | sql/sql_type.cc | 183 | ||||
| -rw-r--r-- | sql/sql_type.h | 138 |
14 files changed, 1138 insertions, 266 deletions
diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result index b095cf4574e..de15d18d542 100644 --- a/mysql-test/r/func_str.result +++ b/mysql-test/r/func_str.result @@ -2938,13 +2938,7 @@ sha1('P'), ) ) ) AS r; -r -0 -Warnings: -Warning 1292 Truncated incorrect INTEGER value: '511993d3c99719e38a6779073019dacd7178ddb9' -Warning 1292 Truncated incorrect DECIMAL value: '[.DC2.]' -Warning 1292 Truncated incorrect INTEGER value: '511993d3c99719e38a6779073019dacd7178ddb9' -Warning 1292 Truncated incorrect DOUBLE value: '0.000000000000000000000000000000111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111' +ERROR HY000: Illegal parameter data types geometry and decimal for operation 'coalesce' connection conn1; SET @@global.max_allowed_packet:= @tmp_max; disconnect newconn; diff --git a/mysql-test/r/gis.result b/mysql-test/r/gis.result index ed0d8f540a0..7f620e67b0e 100644 --- a/mysql-test/r/gis.result +++ b/mysql-test/r/gis.result @@ -2199,5 +2199,617 @@ c 1 DROP TABLE t1; # +# +# +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 CASE WHEN TRUE THEN a ELSE b END FROM t1'); +CALL p2('CREATE TABLE t2 AS SELECT COALESCE(a,b) FROM t1'); +CALL p2('CREATE TABLE t2 AS SELECT IF(TRUE,a,b) FROM t1'); +CALL p2('CREATE TABLE t2 AS SELECT IFNULL(a,b) FROM t1'); +CALL p2('CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1'); +DROP TABLE t1; +END; +$$ +------------------------------------- +CREATE TABLE t1 (a CHAR(10), b Point) + +CREATE TABLE t2 AS SELECT CASE WHEN TRUE THEN a ELSE b END FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `CASE WHEN TRUE THEN a ELSE b END` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + +CREATE TABLE t2 AS SELECT COALESCE(a,b) FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `COALESCE(a,b)` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + +CREATE TABLE t2 AS SELECT IF(TRUE,a,b) FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `IF(TRUE,a,b)` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + +CREATE TABLE t2 AS SELECT IFNULL(a,b) FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `IFNULL(a,b)` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + +CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `a` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +------------------------------------- +CREATE TABLE t1 (a VARCHAR(10), b Point) + +CREATE TABLE t2 AS SELECT CASE WHEN TRUE THEN a ELSE b END FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `CASE WHEN TRUE THEN a ELSE b END` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + +CREATE TABLE t2 AS SELECT COALESCE(a,b) FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `COALESCE(a,b)` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + +CREATE TABLE t2 AS SELECT IF(TRUE,a,b) FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `IF(TRUE,a,b)` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + +CREATE TABLE t2 AS SELECT IFNULL(a,b) FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `IFNULL(a,b)` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + +CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `a` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +------------------------------------- +CREATE TABLE t1 (a TINYTEXT, b Point) + +CREATE TABLE t2 AS SELECT CASE WHEN TRUE THEN a ELSE b END FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `CASE WHEN TRUE THEN a ELSE b END` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + +CREATE TABLE t2 AS SELECT COALESCE(a,b) FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `COALESCE(a,b)` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + +CREATE TABLE t2 AS SELECT IF(TRUE,a,b) FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `IF(TRUE,a,b)` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + +CREATE TABLE t2 AS SELECT IFNULL(a,b) FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `IFNULL(a,b)` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + +CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `a` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +------------------------------------- +CREATE TABLE t1 (a TEXT, b Point) + +CREATE TABLE t2 AS SELECT CASE WHEN TRUE THEN a ELSE b END FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `CASE WHEN TRUE THEN a ELSE b END` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + +CREATE TABLE t2 AS SELECT COALESCE(a,b) FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `COALESCE(a,b)` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + +CREATE TABLE t2 AS SELECT IF(TRUE,a,b) FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `IF(TRUE,a,b)` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + +CREATE TABLE t2 AS SELECT IFNULL(a,b) FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `IFNULL(a,b)` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + +CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `a` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +------------------------------------- +CREATE TABLE t1 (a MEDIUMTEXT, b Point) + +CREATE TABLE t2 AS SELECT CASE WHEN TRUE THEN a ELSE b END FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `CASE WHEN TRUE THEN a ELSE b END` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + +CREATE TABLE t2 AS SELECT COALESCE(a,b) FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `COALESCE(a,b)` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + +CREATE TABLE t2 AS SELECT IF(TRUE,a,b) FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `IF(TRUE,a,b)` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + +CREATE TABLE t2 AS SELECT IFNULL(a,b) FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `IFNULL(a,b)` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + +CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `a` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +------------------------------------- +CREATE TABLE t1 (a LONGTEXT, b Point) + +CREATE TABLE t2 AS SELECT CASE WHEN TRUE THEN a ELSE b END FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `CASE WHEN TRUE THEN a ELSE b END` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + +CREATE TABLE t2 AS SELECT COALESCE(a,b) FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `COALESCE(a,b)` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + +CREATE TABLE t2 AS SELECT IF(TRUE,a,b) FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `IF(TRUE,a,b)` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + +CREATE TABLE t2 AS SELECT IFNULL(a,b) FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `IFNULL(a,b)` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + +CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `a` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +------------------------------------- +CREATE TABLE t1 (a TINYINT, b Point) + +CREATE TABLE t2 AS SELECT CASE WHEN TRUE THEN a ELSE b END FROM t1 +ERROR: +Illegal parameter data types tinyint and geometry for operation 'case' + +CREATE TABLE t2 AS SELECT COALESCE(a,b) FROM t1 +ERROR: +Illegal parameter data types tinyint and geometry for operation 'coalesce' + +CREATE TABLE t2 AS SELECT IF(TRUE,a,b) FROM t1 +ERROR: +Illegal parameter data types tinyint and geometry for operation 'if' + +CREATE TABLE t2 AS SELECT IFNULL(a,b) FROM t1 +ERROR: +Illegal parameter data types tinyint and geometry for operation 'ifnull' + +CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1 +ERROR: +Illegal parameter data types tinyint and geometry for operation 'UNION' +------------------------------------- +CREATE TABLE t1 (a SMALLINT, b Point) + +CREATE TABLE t2 AS SELECT CASE WHEN TRUE THEN a ELSE b END FROM t1 +ERROR: +Illegal parameter data types shortint and geometry for operation 'case' + +CREATE TABLE t2 AS SELECT COALESCE(a,b) FROM t1 +ERROR: +Illegal parameter data types shortint and geometry for operation 'coalesce' + +CREATE TABLE t2 AS SELECT IF(TRUE,a,b) FROM t1 +ERROR: +Illegal parameter data types shortint and geometry for operation 'if' + +CREATE TABLE t2 AS SELECT IFNULL(a,b) FROM t1 +ERROR: +Illegal parameter data types shortint and geometry for operation 'ifnull' + +CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1 +ERROR: +Illegal parameter data types shortint and geometry for operation 'UNION' +------------------------------------- +CREATE TABLE t1 (a MEDIUMINT, b Point) + +CREATE TABLE t2 AS SELECT CASE WHEN TRUE THEN a ELSE b END FROM t1 +ERROR: +Illegal parameter data types mediumint and geometry for operation 'case' + +CREATE TABLE t2 AS SELECT COALESCE(a,b) FROM t1 +ERROR: +Illegal parameter data types mediumint and geometry for operation 'coalesce' + +CREATE TABLE t2 AS SELECT IF(TRUE,a,b) FROM t1 +ERROR: +Illegal parameter data types mediumint and geometry for operation 'if' + +CREATE TABLE t2 AS SELECT IFNULL(a,b) FROM t1 +ERROR: +Illegal parameter data types mediumint and geometry for operation 'ifnull' + +CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1 +ERROR: +Illegal parameter data types mediumint and geometry for operation 'UNION' +------------------------------------- +CREATE TABLE t1 (a INT, b Point) + +CREATE TABLE t2 AS SELECT CASE WHEN TRUE THEN a ELSE b END FROM t1 +ERROR: +Illegal parameter data types int and geometry for operation 'case' + +CREATE TABLE t2 AS SELECT COALESCE(a,b) FROM t1 +ERROR: +Illegal parameter data types int and geometry for operation 'coalesce' + +CREATE TABLE t2 AS SELECT IF(TRUE,a,b) FROM t1 +ERROR: +Illegal parameter data types int and geometry for operation 'if' + +CREATE TABLE t2 AS SELECT IFNULL(a,b) FROM t1 +ERROR: +Illegal parameter data types int and geometry for operation 'ifnull' + +CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1 +ERROR: +Illegal parameter data types int and geometry for operation 'UNION' +------------------------------------- +CREATE TABLE t1 (a BIGINT, b Point) + +CREATE TABLE t2 AS SELECT CASE WHEN TRUE THEN a ELSE b END FROM t1 +ERROR: +Illegal parameter data types bigint and geometry for operation 'case' + +CREATE TABLE t2 AS SELECT COALESCE(a,b) FROM t1 +ERROR: +Illegal parameter data types bigint and geometry for operation 'coalesce' + +CREATE TABLE t2 AS SELECT IF(TRUE,a,b) FROM t1 +ERROR: +Illegal parameter data types bigint and geometry for operation 'if' + +CREATE TABLE t2 AS SELECT IFNULL(a,b) FROM t1 +ERROR: +Illegal parameter data types bigint and geometry for operation 'ifnull' + +CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1 +ERROR: +Illegal parameter data types bigint and geometry for operation 'UNION' +------------------------------------- +CREATE TABLE t1 (a FLOAT, b Point) + +CREATE TABLE t2 AS SELECT CASE WHEN TRUE THEN a ELSE b END FROM t1 +ERROR: +Illegal parameter data types float and geometry for operation 'case' + +CREATE TABLE t2 AS SELECT COALESCE(a,b) FROM t1 +ERROR: +Illegal parameter data types float and geometry for operation 'coalesce' + +CREATE TABLE t2 AS SELECT IF(TRUE,a,b) FROM t1 +ERROR: +Illegal parameter data types float and geometry for operation 'if' + +CREATE TABLE t2 AS SELECT IFNULL(a,b) FROM t1 +ERROR: +Illegal parameter data types float and geometry for operation 'ifnull' + +CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1 +ERROR: +Illegal parameter data types float and geometry for operation 'UNION' +------------------------------------- +CREATE TABLE t1 (a DOUBLE, b Point) + +CREATE TABLE t2 AS SELECT CASE WHEN TRUE THEN a ELSE b END FROM t1 +ERROR: +Illegal parameter data types double and geometry for operation 'case' + +CREATE TABLE t2 AS SELECT COALESCE(a,b) FROM t1 +ERROR: +Illegal parameter data types double and geometry for operation 'coalesce' + +CREATE TABLE t2 AS SELECT IF(TRUE,a,b) FROM t1 +ERROR: +Illegal parameter data types double and geometry for operation 'if' + +CREATE TABLE t2 AS SELECT IFNULL(a,b) FROM t1 +ERROR: +Illegal parameter data types double and geometry for operation 'ifnull' + +CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1 +ERROR: +Illegal parameter data types double and geometry for operation 'UNION' +------------------------------------- +CREATE TABLE t1 (a DECIMAL(10,2), b Point) + +CREATE TABLE t2 AS SELECT CASE WHEN TRUE THEN a ELSE b END FROM t1 +ERROR: +Illegal parameter data types decimal and geometry for operation 'case' + +CREATE TABLE t2 AS SELECT COALESCE(a,b) FROM t1 +ERROR: +Illegal parameter data types decimal and geometry for operation 'coalesce' + +CREATE TABLE t2 AS SELECT IF(TRUE,a,b) FROM t1 +ERROR: +Illegal parameter data types decimal and geometry for operation 'if' + +CREATE TABLE t2 AS SELECT IFNULL(a,b) FROM t1 +ERROR: +Illegal parameter data types decimal and geometry for operation 'ifnull' + +CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1 +ERROR: +Illegal parameter data types decimal and geometry for operation 'UNION' +------------------------------------- +CREATE TABLE t1 (a BIT(8), b Point) + +CREATE TABLE t2 AS SELECT CASE WHEN TRUE THEN a ELSE b END FROM t1 +ERROR: +Illegal parameter data types bigint and geometry for operation 'case' + +CREATE TABLE t2 AS SELECT COALESCE(a,b) FROM t1 +ERROR: +Illegal parameter data types bigint and geometry for operation 'coalesce' + +CREATE TABLE t2 AS SELECT IF(TRUE,a,b) FROM t1 +ERROR: +Illegal parameter data types bigint and geometry for operation 'if' + +CREATE TABLE t2 AS SELECT IFNULL(a,b) FROM t1 +ERROR: +Illegal parameter data types bigint and geometry for operation 'ifnull' + +CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1 +ERROR: +Illegal parameter data types bit and geometry for operation 'UNION' +------------------------------------- +CREATE TABLE t1 (a TIME, b Point) + +CREATE TABLE t2 AS SELECT CASE WHEN TRUE THEN a ELSE b END FROM t1 +ERROR: +Illegal parameter data types time and geometry for operation 'case' + +CREATE TABLE t2 AS SELECT COALESCE(a,b) FROM t1 +ERROR: +Illegal parameter data types time and geometry for operation 'coalesce' + +CREATE TABLE t2 AS SELECT IF(TRUE,a,b) FROM t1 +ERROR: +Illegal parameter data types time and geometry for operation 'if' + +CREATE TABLE t2 AS SELECT IFNULL(a,b) FROM t1 +ERROR: +Illegal parameter data types time and geometry for operation 'ifnull' + +CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1 +ERROR: +Illegal parameter data types time and geometry for operation 'UNION' +------------------------------------- +CREATE TABLE t1 (a DATE, b Point) + +CREATE TABLE t2 AS SELECT CASE WHEN TRUE THEN a ELSE b END FROM t1 +ERROR: +Illegal parameter data types date and geometry for operation 'case' + +CREATE TABLE t2 AS SELECT COALESCE(a,b) FROM t1 +ERROR: +Illegal parameter data types date and geometry for operation 'coalesce' + +CREATE TABLE t2 AS SELECT IF(TRUE,a,b) FROM t1 +ERROR: +Illegal parameter data types date and geometry for operation 'if' + +CREATE TABLE t2 AS SELECT IFNULL(a,b) FROM t1 +ERROR: +Illegal parameter data types date and geometry for operation 'ifnull' + +CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1 +ERROR: +Illegal parameter data types date and geometry for operation 'UNION' +------------------------------------- +CREATE TABLE t1 (a DATETIME, b Point) + +CREATE TABLE t2 AS SELECT CASE WHEN TRUE THEN a ELSE b END FROM t1 +ERROR: +Illegal parameter data types datetime and geometry for operation 'case' + +CREATE TABLE t2 AS SELECT COALESCE(a,b) FROM t1 +ERROR: +Illegal parameter data types datetime and geometry for operation 'coalesce' + +CREATE TABLE t2 AS SELECT IF(TRUE,a,b) FROM t1 +ERROR: +Illegal parameter data types datetime and geometry for operation 'if' + +CREATE TABLE t2 AS SELECT IFNULL(a,b) FROM t1 +ERROR: +Illegal parameter data types datetime and geometry for operation 'ifnull' + +CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1 +ERROR: +Illegal parameter data types datetime and geometry for operation 'UNION' +------------------------------------- +CREATE TABLE t1 (a TIMESTAMP, b Point) + +CREATE TABLE t2 AS SELECT CASE WHEN TRUE THEN a ELSE b END FROM t1 +ERROR: +Illegal parameter data types timestamp and geometry for operation 'case' + +CREATE TABLE t2 AS SELECT COALESCE(a,b) FROM t1 +ERROR: +Illegal parameter data types timestamp and geometry for operation 'coalesce' + +CREATE TABLE t2 AS SELECT IF(TRUE,a,b) FROM t1 +ERROR: +Illegal parameter data types timestamp and geometry for operation 'if' + +CREATE TABLE t2 AS SELECT IFNULL(a,b) FROM t1 +ERROR: +Illegal parameter data types timestamp and geometry for operation 'ifnull' + +CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1 +ERROR: +Illegal parameter data types timestamp and geometry for operation 'UNION' +------------------------------------- +CREATE TABLE t1 (a YEAR, b Point) + +CREATE TABLE t2 AS SELECT CASE WHEN TRUE THEN a ELSE b END FROM t1 +ERROR: +Illegal parameter data types year and geometry for operation 'case' + +CREATE TABLE t2 AS SELECT COALESCE(a,b) FROM t1 +ERROR: +Illegal parameter data types year and geometry for operation 'coalesce' + +CREATE TABLE t2 AS SELECT IF(TRUE,a,b) FROM t1 +ERROR: +Illegal parameter data types year and geometry for operation 'if' + +CREATE TABLE t2 AS SELECT IFNULL(a,b) FROM t1 +ERROR: +Illegal parameter data types year and geometry for operation 'ifnull' + +CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1 +ERROR: +Illegal parameter data types year and geometry for operation 'UNION' +# This creates BLOB with hybrid functions, but fails on error with UNION (MDEV-11458) +------------------------------------- +CREATE TABLE t1 (a ENUM(0x61), b Point) + +CREATE TABLE t2 AS SELECT CASE WHEN TRUE THEN a ELSE b END FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `CASE WHEN TRUE THEN a ELSE b END` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + +CREATE TABLE t2 AS SELECT COALESCE(a,b) FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `COALESCE(a,b)` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + +CREATE TABLE t2 AS SELECT IF(TRUE,a,b) FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `IF(TRUE,a,b)` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + +CREATE TABLE t2 AS SELECT IFNULL(a,b) FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `IFNULL(a,b)` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + +CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1 +ERROR: +Illegal parameter data types enum and geometry for operation 'UNION' +------------------------------------- +CREATE TABLE t1 (a SET(0x61), b Point) + +CREATE TABLE t2 AS SELECT CASE WHEN TRUE THEN a ELSE b END FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `CASE WHEN TRUE THEN a ELSE b END` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + +CREATE TABLE t2 AS SELECT COALESCE(a,b) FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `COALESCE(a,b)` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + +CREATE TABLE t2 AS SELECT IF(TRUE,a,b) FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `IF(TRUE,a,b)` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + +CREATE TABLE t2 AS SELECT IFNULL(a,b) FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `IFNULL(a,b)` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + +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) +CREATE TABLE t1 AS SELECT COALESCE(NULL, Point(1,1)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `COALESCE(NULL, Point(1,1))` geometry DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 AS SELECT NULL UNION SELECT Point(1,1); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `NULL` geometry DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +DROP PROCEDURE p1; +DROP PROCEDURE p2; +# # End of 10.2 tests # diff --git a/mysql-test/t/func_str.test b/mysql-test/t/func_str.test index 2645417f3e5..cfaae1a46e0 100644 --- a/mysql-test/t/func_str.test +++ b/mysql-test/t/func_str.test @@ -1554,6 +1554,8 @@ format(rpad('111111111.1', # But in mysqltest --disable_prepare_warnings affects SELECT queries only # and can't suppress prepare time warnings for DO. # + +--error ER_CANT_AGGREGATE_2TYPES SELECT round( concat( ( diff --git a/mysql-test/t/gis.test b/mysql-test/t/gis.test index 09d7a29744f..b11fe18ec6d 100644 --- a/mysql-test/t/gis.test +++ b/mysql-test/t/gis.test @@ -1717,5 +1717,84 @@ SELECT c FROM t1; DROP TABLE t1; --echo # +--echo # +--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 CASE WHEN TRUE THEN a ELSE b END FROM t1'); + CALL p2('CREATE TABLE t2 AS SELECT COALESCE(a,b) FROM t1'); + CALL p2('CREATE TABLE t2 AS SELECT IF(TRUE,a,b) FROM t1'); + CALL p2('CREATE TABLE t2 AS SELECT IFNULL(a,b) FROM t1'); + CALL p2('CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT 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 creates BLOB with hybrid functions, but fails on error with UNION (MDEV-11458) +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 COALESCE(NULL, Point(1,1)); +SHOW CREATE TABLE t1; +DROP TABLE t1; +CREATE TABLE t1 AS SELECT NULL UNION SELECT Point(1,1); +SHOW CREATE TABLE t1; +DROP TABLE t1; + +DROP PROCEDURE p1; +DROP PROCEDURE p2; + + +--echo # --echo # End of 10.2 tests --echo # diff --git a/sql/field.cc b/sql/field.cc index 48ae682a893..675c3ab453e 100644 --- a/sql/field.cc +++ b/sql/field.cc @@ -82,18 +82,35 @@ const char field_separator=','; following #defines describe that gap and how to canculate number of fields and index of field in this array. */ -#define FIELDTYPE_TEAR_FROM (MYSQL_TYPE_BIT + 1) -#define FIELDTYPE_TEAR_TO (MYSQL_TYPE_NEWDECIMAL - 1) -#define FIELDTYPE_NUM (FIELDTYPE_TEAR_FROM + (255 - FIELDTYPE_TEAR_TO)) +const int FIELDTYPE_TEAR_FROM= (MYSQL_TYPE_BIT + 1); +const int FIELDTYPE_TEAR_TO= (MYSQL_TYPE_NEWDECIMAL - 1); +const int FIELDTYPE_LAST= 254; +const int FIELDTYPE_NUM= FIELDTYPE_TEAR_FROM + (FIELDTYPE_LAST - + FIELDTYPE_TEAR_TO); + static inline int field_type2index (enum_field_types field_type) { + DBUG_ASSERT(real_type_to_type(field_type) < FIELDTYPE_TEAR_FROM || + real_type_to_type(field_type) > FIELDTYPE_TEAR_TO); + DBUG_ASSERT(field_type <= FIELDTYPE_LAST); field_type= real_type_to_type(field_type); - return (field_type < FIELDTYPE_TEAR_FROM ? - field_type : - ((int)FIELDTYPE_TEAR_FROM) + (field_type - FIELDTYPE_TEAR_TO) - 1); + if (field_type < FIELDTYPE_TEAR_FROM) + return field_type; + return FIELDTYPE_TEAR_FROM + (field_type - FIELDTYPE_TEAR_TO) - 1; } +/** + Implements data type merge rules for the built-in traditional data types. + Used for operations such as: + - UNION + - CASE and its abbreviations COALESCE, IF, IFNULL + - LEAST/GREATEST + + Given Fields A and B of real_types a and b, we find the result type of + COALESCE(A, B) by querying: + field_types_merge_rules[field_type_to_index(a)][field_type_to_index(b)]. +*/ static enum_field_types field_types_merge_rules [FIELDTYPE_NUM][FIELDTYPE_NUM]= { /* MYSQL_TYPE_DECIMAL -> */ @@ -124,8 +141,8 @@ static enum_field_types field_types_merge_rules [FIELDTYPE_NUM][FIELDTYPE_NUM]= MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_LONG_BLOB, //MYSQL_TYPE_BLOB MYSQL_TYPE_VAR_STRING MYSQL_TYPE_BLOB, MYSQL_TYPE_VARCHAR, - //MYSQL_TYPE_STRING MYSQL_TYPE_GEOMETRY - MYSQL_TYPE_STRING, MYSQL_TYPE_VARCHAR + //MYSQL_TYPE_STRING + MYSQL_TYPE_STRING }, /* MYSQL_TYPE_TINY -> */ { @@ -155,8 +172,8 @@ static enum_field_types field_types_merge_rules [FIELDTYPE_NUM][FIELDTYPE_NUM]= MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_LONG_BLOB, //MYSQL_TYPE_BLOB MYSQL_TYPE_VAR_STRING MYSQL_TYPE_BLOB, MYSQL_TYPE_VARCHAR, - //MYSQL_TYPE_STRING MYSQL_TYPE_GEOMETRY - MYSQL_TYPE_STRING, MYSQL_TYPE_VARCHAR + //MYSQL_TYPE_STRING + MYSQL_TYPE_STRING }, /* MYSQL_TYPE_SHORT -> */ { @@ -186,8 +203,8 @@ static enum_field_types field_types_merge_rules [FIELDTYPE_NUM][FIELDTYPE_NUM]= MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_LONG_BLOB, //MYSQL_TYPE_BLOB MYSQL_TYPE_VAR_STRING MYSQL_TYPE_BLOB, MYSQL_TYPE_VARCHAR, - //MYSQL_TYPE_STRING MYSQL_TYPE_GEOMETRY - MYSQL_TYPE_STRING, MYSQL_TYPE_VARCHAR + //MYSQL_TYPE_STRING + MYSQL_TYPE_STRING }, /* MYSQL_TYPE_LONG -> */ { @@ -217,8 +234,8 @@ static enum_field_types field_types_merge_rules [FIELDTYPE_NUM][FIELDTYPE_NUM]= MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_LONG_BLOB, //MYSQL_TYPE_BLOB MYSQL_TYPE_VAR_STRING MYSQL_TYPE_BLOB, MYSQL_TYPE_VARCHAR, - //MYSQL_TYPE_STRING MYSQL_TYPE_GEOMETRY - MYSQL_TYPE_STRING, MYSQL_TYPE_VARCHAR + //MYSQL_TYPE_STRING + MYSQL_TYPE_STRING }, /* MYSQL_TYPE_FLOAT -> */ { @@ -248,8 +265,8 @@ static enum_field_types field_types_merge_rules [FIELDTYPE_NUM][FIELDTYPE_NUM]= MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_LONG_BLOB, //MYSQL_TYPE_BLOB MYSQL_TYPE_VAR_STRING MYSQL_TYPE_BLOB, MYSQL_TYPE_VARCHAR, - //MYSQL_TYPE_STRING MYSQL_TYPE_GEOMETRY - MYSQL_TYPE_STRING, MYSQL_TYPE_VARCHAR + //MYSQL_TYPE_STRING + MYSQL_TYPE_STRING }, /* MYSQL_TYPE_DOUBLE -> */ { @@ -279,8 +296,8 @@ static enum_field_types field_types_merge_rules [FIELDTYPE_NUM][FIELDTYPE_NUM]= MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_LONG_BLOB, //MYSQL_TYPE_BLOB MYSQL_TYPE_VAR_STRING MYSQL_TYPE_BLOB, MYSQL_TYPE_VARCHAR, - //MYSQL_TYPE_STRING MYSQL_TYPE_GEOMETRY - MYSQL_TYPE_STRING, MYSQL_TYPE_VARCHAR + //MYSQL_TYPE_STRING + MYSQL_TYPE_STRING }, /* MYSQL_TYPE_NULL -> */ { @@ -310,8 +327,8 @@ static enum_field_types field_types_merge_rules [FIELDTYPE_NUM][FIELDTYPE_NUM]= MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_LONG_BLOB, //MYSQL_TYPE_BLOB MYSQL_TYPE_VAR_STRING MYSQL_TYPE_BLOB, MYSQL_TYPE_VARCHAR, - //MYSQL_TYPE_STRING MYSQL_TYPE_GEOMETRY - MYSQL_TYPE_STRING, MYSQL_TYPE_GEOMETRY + //MYSQL_TYPE_STRING + MYSQL_TYPE_STRING }, /* MYSQL_TYPE_TIMESTAMP -> */ { @@ -341,8 +358,8 @@ static enum_field_types field_types_merge_rules [FIELDTYPE_NUM][FIELDTYPE_NUM]= MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_LONG_BLOB, //MYSQL_TYPE_BLOB MYSQL_TYPE_VAR_STRING MYSQL_TYPE_BLOB, MYSQL_TYPE_VARCHAR, - //MYSQL_TYPE_STRING MYSQL_TYPE_GEOMETRY - MYSQL_TYPE_STRING, MYSQL_TYPE_VARCHAR + //MYSQL_TYPE_STRING + MYSQL_TYPE_STRING }, /* MYSQL_TYPE_LONGLONG -> */ { @@ -372,8 +389,8 @@ static enum_field_types field_types_merge_rules [FIELDTYPE_NUM][FIELDTYPE_NUM]= MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_LONG_BLOB, //MYSQL_TYPE_BLOB MYSQL_TYPE_VAR_STRING MYSQL_TYPE_BLOB, MYSQL_TYPE_VARCHAR, - //MYSQL_TYPE_STRING MYSQL_TYPE_GEOMETRY - MYSQL_TYPE_STRING, MYSQL_TYPE_VARCHAR + //MYSQL_TYPE_STRING + MYSQL_TYPE_STRING }, /* MYSQL_TYPE_INT24 -> */ { @@ -403,8 +420,8 @@ static enum_field_types field_types_merge_rules [FIELDTYPE_NUM][FIELDTYPE_NUM]= MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_LONG_BLOB, //MYSQL_TYPE_BLOB MYSQL_TYPE_VAR_STRING MYSQL_TYPE_BLOB, MYSQL_TYPE_VARCHAR, - //MYSQL_TYPE_STRING MYSQL_TYPE_GEOMETRY - MYSQL_TYPE_STRING, MYSQL_TYPE_VARCHAR + //MYSQL_TYPE_STRING + MYSQL_TYPE_STRING }, /* MYSQL_TYPE_DATE -> */ { @@ -434,8 +451,8 @@ static enum_field_types field_types_merge_rules [FIELDTYPE_NUM][FIELDTYPE_NUM]= MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_LONG_BLOB, //MYSQL_TYPE_BLOB MYSQL_TYPE_VAR_STRING MYSQL_TYPE_BLOB, MYSQL_TYPE_VARCHAR, - //MYSQL_TYPE_STRING MYSQL_TYPE_GEOMETRY - MYSQL_TYPE_STRING, MYSQL_TYPE_VARCHAR + //MYSQL_TYPE_STRING + MYSQL_TYPE_STRING }, /* MYSQL_TYPE_TIME -> */ { @@ -465,8 +482,8 @@ static enum_field_types field_types_merge_rules [FIELDTYPE_NUM][FIELDTYPE_NUM]= MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_LONG_BLOB, //MYSQL_TYPE_BLOB MYSQL_TYPE_VAR_STRING MYSQL_TYPE_BLOB, MYSQL_TYPE_VARCHAR, - //MYSQL_TYPE_STRING MYSQL_TYPE_GEOMETRY - MYSQL_TYPE_STRING, MYSQL_TYPE_VARCHAR + //MYSQL_TYPE_STRING + MYSQL_TYPE_STRING }, /* MYSQL_TYPE_DATETIME -> */ { @@ -496,8 +513,8 @@ static enum_field_types field_types_merge_rules [FIELDTYPE_NUM][FIELDTYPE_NUM]= MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_LONG_BLOB, //MYSQL_TYPE_BLOB MYSQL_TYPE_VAR_STRING MYSQL_TYPE_BLOB, MYSQL_TYPE_VARCHAR, - //MYSQL_TYPE_STRING MYSQL_TYPE_GEOMETRY - MYSQL_TYPE_STRING, MYSQL_TYPE_VARCHAR + //MYSQL_TYPE_STRING + MYSQL_TYPE_STRING }, /* MYSQL_TYPE_YEAR -> */ { @@ -527,8 +544,8 @@ static enum_field_types field_types_merge_rules [FIELDTYPE_NUM][FIELDTYPE_NUM]= MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_LONG_BLOB, //MYSQL_TYPE_BLOB MYSQL_TYPE_VAR_STRING MYSQL_TYPE_BLOB, MYSQL_TYPE_VARCHAR, - //MYSQL_TYPE_STRING MYSQL_TYPE_GEOMETRY - MYSQL_TYPE_STRING, MYSQL_TYPE_VARCHAR + //MYSQL_TYPE_STRING + MYSQL_TYPE_STRING }, /* MYSQL_TYPE_NEWDATE -> */ { @@ -558,8 +575,8 @@ static enum_field_types field_types_merge_rules [FIELDTYPE_NUM][FIELDTYPE_NUM]= MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_LONG_BLOB, //MYSQL_TYPE_BLOB MYSQL_TYPE_VAR_STRING MYSQL_TYPE_BLOB, MYSQL_TYPE_VARCHAR, - //MYSQL_TYPE_STRING MYSQL_TYPE_GEOMETRY - MYSQL_TYPE_STRING, MYSQL_TYPE_VARCHAR + //MYSQL_TYPE_STRING + MYSQL_TYPE_STRING }, /* MYSQL_TYPE_VARCHAR -> */ { @@ -589,8 +606,8 @@ static enum_field_types field_types_merge_rules [FIELDTYPE_NUM][FIELDTYPE_NUM]= MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_LONG_BLOB, //MYSQL_TYPE_BLOB MYSQL_TYPE_VAR_STRING MYSQL_TYPE_BLOB, MYSQL_TYPE_VARCHAR, - //MYSQL_TYPE_STRING MYSQL_TYPE_GEOMETRY - MYSQL_TYPE_VARCHAR, MYSQL_TYPE_VARCHAR + //MYSQL_TYPE_STRING + MYSQL_TYPE_VARCHAR }, /* MYSQL_TYPE_BIT -> */ { @@ -620,8 +637,8 @@ static enum_field_types field_types_merge_rules [FIELDTYPE_NUM][FIELDTYPE_NUM]= MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_LONG_BLOB, //MYSQL_TYPE_BLOB MYSQL_TYPE_VAR_STRING MYSQL_TYPE_BLOB, MYSQL_TYPE_VARCHAR, - //MYSQL_TYPE_STRING MYSQL_TYPE_GEOMETRY - MYSQL_TYPE_STRING, MYSQL_TYPE_VARCHAR + //MYSQL_TYPE_STRING + MYSQL_TYPE_STRING }, /* MYSQL_TYPE_NEWDECIMAL -> */ { @@ -651,8 +668,8 @@ static enum_field_types field_types_merge_rules [FIELDTYPE_NUM][FIELDTYPE_NUM]= MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_LONG_BLOB, //MYSQL_TYPE_BLOB MYSQL_TYPE_VAR_STRING MYSQL_TYPE_BLOB, MYSQL_TYPE_VARCHAR, - //MYSQL_TYPE_STRING MYSQL_TYPE_GEOMETRY - MYSQL_TYPE_STRING, MYSQL_TYPE_VARCHAR + //MYSQL_TYPE_STRING + MYSQL_TYPE_STRING }, /* MYSQL_TYPE_ENUM -> */ { @@ -682,8 +699,8 @@ static enum_field_types field_types_merge_rules [FIELDTYPE_NUM][FIELDTYPE_NUM]= MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_LONG_BLOB, //MYSQL_TYPE_BLOB MYSQL_TYPE_VAR_STRING MYSQL_TYPE_BLOB, MYSQL_TYPE_VARCHAR, - //MYSQL_TYPE_STRING MYSQL_TYPE_GEOMETRY - MYSQL_TYPE_STRING, MYSQL_TYPE_VARCHAR + //MYSQL_TYPE_STRING + MYSQL_TYPE_STRING }, /* MYSQL_TYPE_SET -> */ { @@ -713,8 +730,8 @@ static enum_field_types field_types_merge_rules [FIELDTYPE_NUM][FIELDTYPE_NUM]= MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_LONG_BLOB, //MYSQL_TYPE_BLOB MYSQL_TYPE_VAR_STRING MYSQL_TYPE_BLOB, MYSQL_TYPE_VARCHAR, - //MYSQL_TYPE_STRING MYSQL_TYPE_GEOMETRY - MYSQL_TYPE_STRING, MYSQL_TYPE_VARCHAR + //MYSQL_TYPE_STRING + MYSQL_TYPE_STRING }, /* MYSQL_TYPE_TINY_BLOB -> */ { @@ -744,8 +761,8 @@ static enum_field_types field_types_merge_rules [FIELDTYPE_NUM][FIELDTYPE_NUM]= MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_LONG_BLOB, //MYSQL_TYPE_BLOB MYSQL_TYPE_VAR_STRING MYSQL_TYPE_BLOB, MYSQL_TYPE_TINY_BLOB, - //MYSQL_TYPE_STRING MYSQL_TYPE_GEOMETRY - MYSQL_TYPE_TINY_BLOB, MYSQL_TYPE_TINY_BLOB + //MYSQL_TYPE_STRING + MYSQL_TYPE_TINY_BLOB }, /* MYSQL_TYPE_MEDIUM_BLOB -> */ { @@ -775,8 +792,8 @@ static enum_field_types field_types_merge_rules [FIELDTYPE_NUM][FIELDTYPE_NUM]= MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_LONG_BLOB, //MYSQL_TYPE_BLOB MYSQL_TYPE_VAR_STRING MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_MEDIUM_BLOB, - //MYSQL_TYPE_STRING MYSQL_TYPE_GEOMETRY - MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_MEDIUM_BLOB + //MYSQL_TYPE_STRING + MYSQL_TYPE_MEDIUM_BLOB }, /* MYSQL_TYPE_LONG_BLOB -> */ { @@ -806,8 +823,8 @@ static enum_field_types field_types_merge_rules [FIELDTYPE_NUM][FIELDTYPE_NUM]= MYSQL_TYPE_LONG_BLOB, MYSQL_TYPE_LONG_BLOB, //MYSQL_TYPE_BLOB MYSQL_TYPE_VAR_STRING MYSQL_TYPE_LONG_BLOB, MYSQL_TYPE_LONG_BLOB, - //MYSQL_TYPE_STRING MYSQL_TYPE_GEOMETRY - MYSQL_TYPE_LONG_BLOB, MYSQL_TYPE_LONG_BLOB + //MYSQL_TYPE_STRING + MYSQL_TYPE_LONG_BLOB }, /* MYSQL_TYPE_BLOB -> */ { @@ -837,8 +854,8 @@ static enum_field_types field_types_merge_rules [FIELDTYPE_NUM][FIELDTYPE_NUM]= MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_LONG_BLOB, //MYSQL_TYPE_BLOB MYSQL_TYPE_VAR_STRING MYSQL_TYPE_BLOB, MYSQL_TYPE_BLOB, - //MYSQL_TYPE_STRING MYSQL_TYPE_GEOMETRY - MYSQL_TYPE_BLOB, MYSQL_TYPE_BLOB + //MYSQL_TYPE_STRING + MYSQL_TYPE_BLOB }, /* MYSQL_TYPE_VAR_STRING -> */ { @@ -868,8 +885,8 @@ static enum_field_types field_types_merge_rules [FIELDTYPE_NUM][FIELDTYPE_NUM]= MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_LONG_BLOB, //MYSQL_TYPE_BLOB MYSQL_TYPE_VAR_STRING MYSQL_TYPE_BLOB, MYSQL_TYPE_VARCHAR, - //MYSQL_TYPE_STRING MYSQL_TYPE_GEOMETRY - MYSQL_TYPE_VARCHAR, MYSQL_TYPE_VARCHAR + //MYSQL_TYPE_STRING + MYSQL_TYPE_VARCHAR }, /* MYSQL_TYPE_STRING -> */ { @@ -899,39 +916,8 @@ static enum_field_types field_types_merge_rules [FIELDTYPE_NUM][FIELDTYPE_NUM]= MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_LONG_BLOB, //MYSQL_TYPE_BLOB MYSQL_TYPE_VAR_STRING MYSQL_TYPE_BLOB, MYSQL_TYPE_VARCHAR, - //MYSQL_TYPE_STRING MYSQL_TYPE_GEOMETRY - MYSQL_TYPE_STRING, MYSQL_TYPE_STRING - }, - /* MYSQL_TYPE_GEOMETRY -> */ - { - //MYSQL_TYPE_DECIMAL MYSQL_TYPE_TINY - MYSQL_TYPE_VARCHAR, MYSQL_TYPE_VARCHAR, - //MYSQL_TYPE_SHORT MYSQL_TYPE_LONG - MYSQL_TYPE_VARCHAR, MYSQL_TYPE_VARCHAR, - //MYSQL_TYPE_FLOAT MYSQL_TYPE_DOUBLE - MYSQL_TYPE_VARCHAR, MYSQL_TYPE_VARCHAR, - //MYSQL_TYPE_NULL MYSQL_TYPE_TIMESTAMP - MYSQL_TYPE_GEOMETRY, MYSQL_TYPE_VARCHAR, - //MYSQL_TYPE_LONGLONG MYSQL_TYPE_INT24 - MYSQL_TYPE_VARCHAR, MYSQL_TYPE_VARCHAR, - //MYSQL_TYPE_DATE MYSQL_TYPE_TIME - MYSQL_TYPE_VARCHAR, MYSQL_TYPE_VARCHAR, - //MYSQL_TYPE_DATETIME MYSQL_TYPE_YEAR - MYSQL_TYPE_VARCHAR, MYSQL_TYPE_VARCHAR, - //MYSQL_TYPE_NEWDATE MYSQL_TYPE_VARCHAR - MYSQL_TYPE_VARCHAR, MYSQL_TYPE_VARCHAR, - //MYSQL_TYPE_BIT <16>-<245> - MYSQL_TYPE_VARCHAR, - //MYSQL_TYPE_NEWDECIMAL MYSQL_TYPE_ENUM - MYSQL_TYPE_VARCHAR, MYSQL_TYPE_VARCHAR, - //MYSQL_TYPE_SET MYSQL_TYPE_TINY_BLOB - MYSQL_TYPE_VARCHAR, MYSQL_TYPE_TINY_BLOB, - //MYSQL_TYPE_MEDIUM_BLOB MYSQL_TYPE_LONG_BLOB - MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_LONG_BLOB, - //MYSQL_TYPE_BLOB MYSQL_TYPE_VAR_STRING - MYSQL_TYPE_BLOB, MYSQL_TYPE_VARCHAR, - //MYSQL_TYPE_STRING MYSQL_TYPE_GEOMETRY - MYSQL_TYPE_STRING, MYSQL_TYPE_GEOMETRY + //MYSQL_TYPE_STRING + MYSQL_TYPE_STRING } }; @@ -948,46 +934,19 @@ static enum_field_types field_types_merge_rules [FIELDTYPE_NUM][FIELDTYPE_NUM]= enum_field_types Field::field_type_merge(enum_field_types a, enum_field_types b) { - DBUG_ASSERT(real_type_to_type(a) < FIELDTYPE_TEAR_FROM || - real_type_to_type(a) > FIELDTYPE_TEAR_TO); - DBUG_ASSERT(real_type_to_type(b) < FIELDTYPE_TEAR_FROM || - real_type_to_type(b) > FIELDTYPE_TEAR_TO); return field_types_merge_rules[field_type2index(a)] [field_type2index(b)]; } - -static Item_result field_types_result_type [FIELDTYPE_NUM]= +const Type_handler * +Type_handler::aggregate_for_result_traditional(const Type_handler *a, + const Type_handler *b) { - //MYSQL_TYPE_DECIMAL MYSQL_TYPE_TINY - DECIMAL_RESULT, INT_RESULT, - //MYSQL_TYPE_SHORT MYSQL_TYPE_LONG - INT_RESULT, INT_RESULT, - //MYSQL_TYPE_FLOAT MYSQL_TYPE_DOUBLE - REAL_RESULT, REAL_RESULT, - //MYSQL_TYPE_NULL MYSQL_TYPE_TIMESTAMP - STRING_RESULT, STRING_RESULT, - //MYSQL_TYPE_LONGLONG MYSQL_TYPE_INT24 - INT_RESULT, INT_RESULT, - //MYSQL_TYPE_DATE MYSQL_TYPE_TIME - STRING_RESULT, STRING_RESULT, - //MYSQL_TYPE_DATETIME MYSQL_TYPE_YEAR - STRING_RESULT, INT_RESULT, - //MYSQL_TYPE_NEWDATE MYSQL_TYPE_VARCHAR - STRING_RESULT, STRING_RESULT, - //MYSQL_TYPE_BIT <16>-<245> - STRING_RESULT, - //MYSQL_TYPE_NEWDECIMAL MYSQL_TYPE_ENUM - DECIMAL_RESULT, STRING_RESULT, - //MYSQL_TYPE_SET MYSQL_TYPE_TINY_BLOB - STRING_RESULT, STRING_RESULT, - //MYSQL_TYPE_MEDIUM_BLOB MYSQL_TYPE_LONG_BLOB - STRING_RESULT, STRING_RESULT, - //MYSQL_TYPE_BLOB MYSQL_TYPE_VAR_STRING - STRING_RESULT, STRING_RESULT, - //MYSQL_TYPE_STRING MYSQL_TYPE_GEOMETRY - STRING_RESULT, STRING_RESULT -}; + enum_field_types ta= a->real_field_type(); + enum_field_types tb= b->real_field_type(); + return + Type_handler::get_handler_by_real_type(Field::field_type_merge(ta, tb)); +} /* @@ -1034,21 +993,6 @@ int compare(unsigned int a, unsigned int b) CPP_UNNAMED_NS_END -/** - Detect Item_result by given field type of UNION merge result. - - @param field_type given field type - - @return - Item_result (type of internal MySQL expression result) -*/ - -Item_result Field::result_merge_type(enum_field_types field_type) -{ - DBUG_ASSERT(real_type_to_type(field_type) < FIELDTYPE_TEAR_FROM || - real_type_to_type(field_type) > FIELDTYPE_TEAR_TO); - return field_types_result_type[field_type2index(field_type)]; -} /***************************************************************************** Static help functions diff --git a/sql/field.h b/sql/field.h index c37c8586620..3a5ffc3330b 100644 --- a/sql/field.h +++ b/sql/field.h @@ -853,7 +853,6 @@ public: virtual Item_result cmp_type () const { return result_type(); } static bool type_can_have_key_part(enum_field_types); static enum_field_types field_type_merge(enum_field_types, enum_field_types); - static Item_result result_merge_type(enum_field_types); virtual bool eq(Field *field) { return (ptr == field->ptr && null_ptr == field->null_ptr && diff --git a/sql/item.cc b/sql/item.cc index c37613e01fa..88ff0c13bcf 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -9900,23 +9900,8 @@ Item_type_holder::Item_type_holder(THD *thd, Item *item) { DBUG_ASSERT(item->fixed); maybe_null= item->maybe_null; - collation.set(item->collation); get_full_info(item); - /** - Field::result_merge_type(real_field_type()) should be equal to - result_type(), with one exception when "this" is a Item_field for - a BIT field: - - Field_bit::result_type() returns INT_RESULT, so does its Item_field. - - Field::result_merge_type(MYSQL_TYPE_BIT) returns STRING_RESULT. - Perhaps we need a new method in Type_handler to cover these type - merging rules for UNION. - */ - DBUG_ASSERT(real_field_type() == MYSQL_TYPE_BIT || - Item_type_holder::result_type() == - Field::result_merge_type(Item_type_holder::real_field_type())); - /* fix variable decimals which always is NOT_FIXED_DEC */ - if (Field::result_merge_type(real_field_type()) == INT_RESULT) - decimals= 0; + DBUG_ASSERT(!decimals || Item_type_holder::result_type() != INT_RESULT); prev_decimal_int_part= item->decimal_int_part(); #ifdef HAVE_SPATIAL if (item->field_type() == MYSQL_TYPE_GEOMETRY) @@ -10030,21 +10015,37 @@ bool Item_type_holder::join_types(THD *thd, Item *item) DBUG_PRINT("info:", ("in type %d len %d, dec %d", get_real_type(item), item->max_length, item->decimals)); - set_handler_by_real_type(Field::field_type_merge(real_field_type(), - get_real_type(item))); - { - uint item_decimals= item->decimals; - /* fix variable decimals which always is NOT_FIXED_DEC */ - if (Field::result_merge_type(real_field_type()) == INT_RESULT) - item_decimals= 0; - decimals= MY_MAX(decimals, item_decimals); + const Type_handler *item_type_handler= + Type_handler::get_handler_by_real_type(get_real_type(item)); + if (aggregate_for_result(item_type_handler)) + { + my_error(ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION, MYF(0), + Item_type_holder::type_handler()->name().ptr(), + item_type_handler->name().ptr(), + "UNION"); + DBUG_RETURN(true); } + /* + At this point non-zero decimals in combination with integer data types + is possible in some cases: + SELECT * FROM (SELECT NULL) a UNION SELECT 1; + In the constructor Item_type_holder::Item_type_holder() the data type + handler was set to type_handler_null with decimals==NOT_FIXED_DEC. + After the above call for aggregate_for_result() for the literal 1 + which is on the right side of the UNION, the data type handler + changes to type_handler_longlong, while decimals is still NOT_FIXED_DEC. + */ + if (Item_type_holder::result_type() == INT_RESULT) + decimals= 0; + else + decimals= MY_MAX(decimals, item->decimals); + if (Item_type_holder::field_type() == FIELD_TYPE_GEOMETRY) geometry_type= Field_geom::geometry_type_merge(geometry_type, item->get_geometry_type()); - if (Field::result_merge_type(real_field_type()) == DECIMAL_RESULT) + if (Item_type_holder::result_type() == DECIMAL_RESULT) { decimals= MY_MIN(MY_MAX(decimals, item->decimals), DECIMAL_MAX_SCALE); int item_int_part= item->decimal_int_part(); @@ -10056,7 +10057,7 @@ bool Item_type_holder::join_types(THD *thd, Item *item) unsigned_flag); } - switch (Field::result_merge_type(real_field_type())) + switch (Item_type_holder::result_type()) { case STRING_RESULT: { diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index f4bd211c714..38af8c484f5 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -155,87 +155,6 @@ bool Type_handler_hybrid_field_type::aggregate_for_comparison(Item **items, } -/** - @brief Aggregates field types from the array of items. - - @param[in] items array of items to aggregate the type from - @paran[in] nitems number of items in the array - @param[in] treat_bit_as_number - if BIT should be aggregated to a non-BIT - counterpart as a LONGLONG number or as a VARBINARY string. - - Currently behaviour depends on the function: - - LEAST/GREATEST treat BIT as VARBINARY when - aggregating with a non-BIT counterpart. - Note, UNION also works this way. - - - CASE, COALESCE, IF, IFNULL treat BIT as LONGLONG when - aggregating with a non-BIT counterpart; - - This inconsistency may be changed in the future. See MDEV-8867. - - Note, independently from "treat_bit_as_number": - - a single BIT argument gives BIT as a result - - two BIT couterparts give BIT as a result - - @details This function aggregates field types from the array of items. - Found type is supposed to be used later as the result field type - of a multi-argument function. - Aggregation itself is performed by the Field::field_type_merge() - function. - - @note The term "aggregation" is used here in the sense of inferring the - result type of a function from its argument types. - - @return aggregated field type. -*/ - -enum_field_types agg_field_type(Item **items, uint nitems, - bool treat_bit_as_number) -{ - uint i; - if (!nitems || items[0]->result_type() == ROW_RESULT) - { - DBUG_ASSERT(0); - return MYSQL_TYPE_NULL; - } - enum_field_types res= items[0]->field_type(); - uint unsigned_count= items[0]->unsigned_flag; - for (i= 1 ; i < nitems ; i++) - { - enum_field_types cur= items[i]->field_type(); - if (treat_bit_as_number && - ((res == MYSQL_TYPE_BIT) ^ (cur == MYSQL_TYPE_BIT))) - { - if (res == MYSQL_TYPE_BIT) - res= MYSQL_TYPE_LONGLONG; // BIT + non-BIT - else - cur= MYSQL_TYPE_LONGLONG; // non-BIT + BIT - } - res= Field::field_type_merge(res, cur); - unsigned_count+= items[i]->unsigned_flag; - } - switch (res) { - 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. - */ - return MYSQL_TYPE_NEWDECIMAL; - } - default: - break; - } - return res; -} - /* Collects different types for comparison of first item with each other items @@ -3105,8 +3024,9 @@ void Item_func_case::fix_length_and_dec() if (else_expr_num != -1) agg[nagg++]= args[else_expr_num]; - - set_handler_by_field_type(agg_field_type(agg, nagg, true)); + + if (aggregate_for_result(func_name(), agg, nagg, true)) + return; if (fix_attributes(agg, nagg)) return; diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h index 4d99df69aa0..17b2c1a656f 100644 --- a/sql/item_cmpfunc.h +++ b/sql/item_cmpfunc.h @@ -977,8 +977,8 @@ public: bool date_op(MYSQL_TIME *ltime,uint fuzzydate); void fix_length_and_dec() { - set_handler_by_field_type(agg_field_type(args, arg_count, true)); - fix_attributes(args, arg_count); + if (!aggregate_for_result(func_name(), args, arg_count, true)) + fix_attributes(args, arg_count); } const char *func_name() const { return "coalesce"; } table_map not_null_tables() const { return 0; } @@ -997,8 +997,8 @@ class Item_func_case_abbreviation2 :public Item_func_hybrid_field_type protected: void fix_length_and_dec2(Item **items) { - set_handler_by_field_type(agg_field_type(items, 2, true)); - fix_attributes(items, 2); + if (!aggregate_for_result(func_name(), items, 2, true)) + fix_attributes(items, 2); } uint decimal_precision2(Item **args) const; public: diff --git a/sql/item_func.cc b/sql/item_func.cc index d1c373722e4..156bd228c5a 100644 --- a/sql/item_func.cc +++ b/sql/item_func.cc @@ -2695,6 +2695,8 @@ void Item_func_min_max::fix_length_and_dec() 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 @@ -2702,7 +2704,6 @@ void Item_func_min_max::fix_length_and_dec() */ agg_arg_charsets_for_string_result_with_comparison(collation, args, arg_count); - set_handler_by_field_type(agg_field_type(args, arg_count, false)); break; case INT_RESULT: @@ -2731,7 +2732,8 @@ void Item_func_min_max::fix_length_and_dec() Treat BIT as LONGLONG when aggregating to non-BIT types. Possible final type: TINY, SHORT, LONG, LONGLONG, INT24, YEAR, BIT. */ - set_handler_by_field_type(agg_field_type(args, arg_count, true)); + if (aggregate_for_result(func_name(), args, arg_count, true)) + return; } break; @@ -2754,7 +2756,7 @@ void Item_func_min_max::fix_length_and_dec() /* 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 agg_field_type() here, + 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); diff --git a/sql/item_func.h b/sql/item_func.h index 458be997906..fcb03e914ac 100644 --- a/sql/item_func.h +++ b/sql/item_func.h @@ -2587,8 +2587,6 @@ public: Item *get_system_var(THD *thd, enum_var_type var_type, LEX_STRING name, LEX_STRING component); extern bool check_reserved_words(LEX_STRING *name); -extern enum_field_types agg_field_type(Item **items, uint nitems, - bool treat_bit_as_number); Item *find_date_time_item(Item **args, uint nargs, uint col); double my_double_round(double value, longlong dec, bool dec_unsigned, bool truncate); diff --git a/sql/share/errmsg-utf8.txt b/sql/share/errmsg-utf8.txt index aa11b55da0d..761fa214471 100644 --- a/sql/share/errmsg-utf8.txt +++ b/sql/share/errmsg-utf8.txt @@ -7438,3 +7438,5 @@ ER_JSON_PATH_ARRAY eng "JSON path should end with an array identifier in argument %d to function '%s'" ER_JSON_ONE_OR_ALL eng "Argument 2 to function '%s' must be "one" or "all"." +ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION + eng "Illegal parameter data types %s and %s for operation '%s'" diff --git a/sql/sql_type.cc b/sql/sql_type.cc index 4dc803190f4..afc2a96c97f 100644 --- a/sql/sql_type.cc +++ b/sql/sql_type.cc @@ -25,7 +25,6 @@ static Type_handler_short type_handler_short; static Type_handler_long type_handler_long; static Type_handler_int24 type_handler_int24; static Type_handler_year type_handler_year; -static Type_handler_bit type_handler_bit; static Type_handler_float type_handler_float; static Type_handler_double type_handler_double; static Type_handler_time type_handler_time; @@ -54,6 +53,39 @@ Type_handler_varchar type_handler_varchar; Type_handler_longlong type_handler_longlong; Type_handler_newdecimal type_handler_newdecimal; Type_handler_datetime type_handler_datetime; +Type_handler_bit type_handler_bit; + + +Type_aggregator type_aggregator_for_result; + + +class Static_data_initializer +{ +public: + static Static_data_initializer m_singleton; + Static_data_initializer() + { +#ifdef HAVE_SPATIAL + type_aggregator_for_result.add(&type_handler_geometry, + &type_handler_null, + &type_handler_geometry); + type_aggregator_for_result.add(&type_handler_geometry, + &type_handler_geometry, + &type_handler_geometry); + type_aggregator_for_result.add(&type_handler_geometry, + &type_handler_blob, + &type_handler_long_blob); + type_aggregator_for_result.add(&type_handler_geometry, + &type_handler_varchar, + &type_handler_long_blob); + type_aggregator_for_result.add(&type_handler_geometry, + &type_handler_string, + &type_handler_long_blob); +#endif + } +}; + +Static_data_initializer Static_data_initializer::m_singleton; void Type_std_attributes::set(const Field *field) @@ -150,6 +182,46 @@ Type_handler_hybrid_field_type::Type_handler_hybrid_field_type() /***************************************************************************/ +const Name Type_handler_row::m_name_row(C_STRING_WITH_LEN("row")); + +const Name Type_handler_null::m_name_null(C_STRING_WITH_LEN("null")); + +const Name + Type_handler_string::m_name_char(C_STRING_WITH_LEN("char")), + Type_handler_varchar::m_name_varchar(C_STRING_WITH_LEN("varchar")), + Type_handler_tiny_blob::m_name_tinyblob(C_STRING_WITH_LEN("tinyblob")), + Type_handler_medium_blob::m_name_mediumblob(C_STRING_WITH_LEN("mediumblob")), + Type_handler_long_blob::m_name_longblob(C_STRING_WITH_LEN("longblob")), + Type_handler_blob::m_name_blob(C_STRING_WITH_LEN("blob")); + +const Name + Type_handler_enum::m_name_enum(C_STRING_WITH_LEN("enum")), + Type_handler_set::m_name_set(C_STRING_WITH_LEN("set")); + +const Name + Type_handler_tiny::m_name_tiny(C_STRING_WITH_LEN("tinyint")), + Type_handler_short::m_name_short(C_STRING_WITH_LEN("shortint")), + Type_handler_long::m_name_int(C_STRING_WITH_LEN("int")), + Type_handler_longlong::m_name_longlong(C_STRING_WITH_LEN("bigint")), + Type_handler_int24::m_name_mediumint(C_STRING_WITH_LEN("mediumint")), + Type_handler_year::m_name_year(C_STRING_WITH_LEN("year")), + Type_handler_bit::m_name_bit(C_STRING_WITH_LEN("bit")); + +const Name + Type_handler_float::m_name_float(C_STRING_WITH_LEN("float")), + Type_handler_double::m_name_double(C_STRING_WITH_LEN("double")); + +const Name + Type_handler_olddecimal::m_name_decimal(C_STRING_WITH_LEN("decimal")), + Type_handler_newdecimal::m_name_decimal(C_STRING_WITH_LEN("decimal")); + +const Name + Type_handler_time_common::m_name_time(C_STRING_WITH_LEN("time")), + Type_handler_date_common::m_name_date(C_STRING_WITH_LEN("date")), + Type_handler_datetime_common::m_name_datetime(C_STRING_WITH_LEN("datetime")), + Type_handler_timestamp_common::m_name_timestamp(C_STRING_WITH_LEN("timestamp")); + +/***************************************************************************/ const Type_handler *Type_handler_int_result::type_handler_for_comparison() const { @@ -192,6 +264,113 @@ const Type_handler *Type_handler_row::type_handler_for_comparison() const } +/***************************************************************************/ + +bool +Type_handler_hybrid_field_type::aggregate_for_result(const Type_handler *other) +{ + if (m_type_handler->is_traditional_type() && other->is_traditional_type()) + { + m_type_handler= + Type_handler::aggregate_for_result_traditional(m_type_handler, other); + return false; + } + other= type_aggregator_for_result.find_handler(m_type_handler, other); + if (!other) + return true; + m_type_handler= other; + return false; +} + + +/** + @brief Aggregates field types from the array of items. + + @param[in] items array of items to aggregate the type from + @param[in] nitems number of items in the array + @param[in] treat_bit_as_number - if BIT should be aggregated to a non-BIT + counterpart as a LONGLONG number or as a VARBINARY string. + + Currently behaviour depends on the function: + - LEAST/GREATEST treat BIT as VARBINARY when + aggregating with a non-BIT counterpart. + Note, UNION also works this way. + + - CASE, COALESCE, IF, IFNULL treat BIT as LONGLONG when + aggregating with a non-BIT counterpart; + + This inconsistency may be changed in the future. See MDEV-8867. + + Note, independently from "treat_bit_as_number": + - a single BIT argument gives BIT as a result + - two BIT couterparts give BIT as a result + + @details This function aggregates field types from the array of items. + Found type is supposed to be used later as the result field type + of a multi-argument function. + Aggregation itself is performed by Type_handler::aggregate_for_result(). + + @note The term "aggregation" is used here in the sense of inferring the + result type of a function from its argument types. + + @retval false - on success + @retval true - on error +*/ + +bool +Type_handler_hybrid_field_type::aggregate_for_result(const char *funcname, + Item **items, uint nitems, + bool treat_bit_as_number) +{ + if (!nitems || items[0]->result_type() == ROW_RESULT) + { + DBUG_ASSERT(0); + set_handler(&type_handler_null); + 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(); + if (treat_bit_as_number && + ((type_handler() == &type_handler_bit) ^ (cur == &type_handler_bit))) + { + if (type_handler() == &type_handler_bit) + set_handler(&type_handler_longlong); // BIT + non-BIT + else + cur= &type_handler_longlong; // non-BIT + BIT + } + if (aggregate_for_result(cur)) + { + my_error(ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION, MYF(0), + 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; +} + /** Collect built-in data type handlers for comparison. This method is very similar to item_cmp_type() defined in item.cc. @@ -719,6 +898,8 @@ Field *Type_handler_long_blob::make_conversion_table_field(TABLE *table, #ifdef HAVE_SPATIAL +const Name Type_handler_geometry::m_name_geometry(C_STRING_WITH_LEN("geometry")); + Field *Type_handler_geometry::make_conversion_table_field(TABLE *table, uint metadata, const Field *target) diff --git a/sql/sql_type.h b/sql/sql_type.h index 12f846e8e94..c778c0feba2 100644 --- a/sql/sql_type.h +++ b/sql/sql_type.h @@ -22,6 +22,7 @@ #include "mysqld.h" +#include "sql_array.h" class Field; class Item; @@ -240,6 +241,19 @@ public: }; +class Name: private LEX_CSTRING +{ +public: + Name(const char *str_arg, uint length_arg) + { + LEX_CSTRING::str= str_arg; + LEX_CSTRING::length= length_arg; + } + const char *ptr() const { return LEX_CSTRING::str; } + uint length() const { return LEX_CSTRING::length; } +}; + + class Type_handler { protected: @@ -261,6 +275,11 @@ public: DBUG_ASSERT(type != TIME_RESULT); return get_handler_by_cmp_type(type); } + static const + Type_handler *aggregate_for_result_traditional(const Type_handler *h1, + const Type_handler *h2); + + virtual const Name name() const= 0; virtual enum_field_types field_type() const= 0; virtual enum_field_types real_field_type() const { return field_type(); } virtual Item_result result_type() const= 0; @@ -273,6 +292,14 @@ public: { return this; } virtual ~Type_handler() {} /** + Determines MariaDB traditional data types that always present + in the server. + */ + virtual bool is_traditional_type() const + { + return true; + } + /** Makes a temporary table Field to handle numeric aggregate functions, e.g. SUM(DISTINCT expr), AVG(DISTINCT expr), etc. */ @@ -365,8 +392,10 @@ public: */ class Type_handler_row: public Type_handler { + static const Name m_name_row; public: virtual ~Type_handler_row() {} + const Name name() const { return m_name_row; } enum_field_types field_type() const { DBUG_ASSERT(0); @@ -710,8 +739,10 @@ public: class Type_handler_tiny: public Type_handler_int_result { + static const Name m_name_tiny; public: virtual ~Type_handler_tiny() {} + const Name name() const { return m_name_tiny; } enum_field_types field_type() const { return MYSQL_TYPE_TINY; } uint32 max_display_length(const Item *item) const { return 4; } Field *make_conversion_table_field(TABLE *TABLE, uint metadata, @@ -721,8 +752,10 @@ public: class Type_handler_short: public Type_handler_int_result { + static const Name m_name_short; public: virtual ~Type_handler_short() {} + const Name name() const { return m_name_short; } enum_field_types field_type() const { return MYSQL_TYPE_SHORT; } uint32 max_display_length(const Item *item) const { return 6; } Field *make_conversion_table_field(TABLE *TABLE, uint metadata, @@ -732,8 +765,10 @@ public: class Type_handler_long: public Type_handler_int_result { + static const Name m_name_int; public: virtual ~Type_handler_long() {} + const Name name() const { return m_name_int; } enum_field_types field_type() const { return MYSQL_TYPE_LONG; } uint32 max_display_length(const Item *item) const { @@ -746,8 +781,10 @@ public: class Type_handler_longlong: public Type_handler_int_result { + static const Name m_name_longlong; public: virtual ~Type_handler_longlong() {} + const Name name() const { return m_name_longlong; } enum_field_types field_type() const { return MYSQL_TYPE_LONGLONG; } uint32 max_display_length(const Item *item) const { return 20; } Field *make_conversion_table_field(TABLE *TABLE, uint metadata, @@ -757,8 +794,10 @@ public: class Type_handler_int24: public Type_handler_int_result { + static const Name m_name_mediumint; public: virtual ~Type_handler_int24() {} + const Name name() const { return m_name_mediumint; } enum_field_types field_type() const { return MYSQL_TYPE_INT24; } uint32 max_display_length(const Item *item) const { return 8; } Field *make_conversion_table_field(TABLE *, uint metadata, @@ -768,8 +807,10 @@ public: class Type_handler_year: public Type_handler_int_result { + static const Name m_name_year; public: virtual ~Type_handler_year() {} + const Name name() const { return m_name_year; } enum_field_types field_type() const { return MYSQL_TYPE_YEAR; } uint32 max_display_length(const Item *item) const; Field *make_conversion_table_field(TABLE *, uint metadata, @@ -779,8 +820,10 @@ public: class Type_handler_bit: public Type_handler_int_result { + static const Name m_name_bit; public: virtual ~Type_handler_bit() {} + const Name name() const { return m_name_bit; } enum_field_types field_type() const { return MYSQL_TYPE_BIT; } uint32 max_display_length(const Item *item) const; Field *make_conversion_table_field(TABLE *, uint metadata, @@ -790,8 +833,10 @@ public: class Type_handler_float: public Type_handler_real_result { + static const Name m_name_float; public: virtual ~Type_handler_float() {} + const Name name() const { return m_name_float; } enum_field_types field_type() const { return MYSQL_TYPE_FLOAT; } uint32 max_display_length(const Item *item) const { return 25; } Field *make_num_distinct_aggregator_field(MEM_ROOT *, const Item *) const; @@ -802,8 +847,10 @@ public: class Type_handler_double: public Type_handler_real_result { + static const Name m_name_double; public: virtual ~Type_handler_double() {} + const Name name() const { return m_name_double; } enum_field_types field_type() const { return MYSQL_TYPE_DOUBLE; } uint32 max_display_length(const Item *item) const { return 53; } Field *make_conversion_table_field(TABLE *, uint metadata, @@ -813,8 +860,10 @@ public: class Type_handler_time_common: public Type_handler_temporal_result { + static const Name m_name_time; public: virtual ~Type_handler_time_common() { } + const Name name() const { return m_name_time; } enum_field_types field_type() const { return MYSQL_TYPE_TIME; } const Type_handler *type_handler_for_comparison() const; int Item_save_in_field(Item *item, Field *field, bool no_conversions) const; @@ -857,8 +906,10 @@ public: class Type_handler_date_common: public Type_handler_temporal_with_date { + static const Name m_name_date; public: virtual ~Type_handler_date_common() {} + const Name name() const { return m_name_date; } enum_field_types field_type() const { return MYSQL_TYPE_DATE; } bool Item_hybrid_func_fix_attributes(THD *thd, Item_hybrid_func *func, Item **items, uint nitems) const; @@ -884,8 +935,10 @@ public: class Type_handler_datetime_common: public Type_handler_temporal_with_date { + static const Name m_name_datetime; public: virtual ~Type_handler_datetime_common() {} + const Name name() const { return m_name_datetime; } enum_field_types field_type() const { return MYSQL_TYPE_DATETIME; } bool Item_hybrid_func_fix_attributes(THD *thd, Item_hybrid_func *func, Item **items, uint nitems) const; @@ -913,8 +966,10 @@ public: class Type_handler_timestamp_common: public Type_handler_temporal_with_date { + static const Name m_name_timestamp; public: virtual ~Type_handler_timestamp_common() {} + const Name name() const { return m_name_timestamp; } enum_field_types field_type() const { return MYSQL_TYPE_TIMESTAMP; } bool Item_hybrid_func_fix_attributes(THD *thd, Item_hybrid_func *func, Item **items, uint nitems) const; @@ -942,8 +997,10 @@ public: class Type_handler_olddecimal: public Type_handler_decimal_result { + static const Name m_name_decimal; public: virtual ~Type_handler_olddecimal() {} + const Name name() const { return m_name_decimal; } enum_field_types field_type() const { return MYSQL_TYPE_DECIMAL; } Field *make_conversion_table_field(TABLE *, uint metadata, const Field *target) const; @@ -952,8 +1009,10 @@ public: class Type_handler_newdecimal: public Type_handler_decimal_result { + static const Name m_name_decimal; public: virtual ~Type_handler_newdecimal() {} + const Name name() const { return m_name_decimal; } enum_field_types field_type() const { return MYSQL_TYPE_NEWDECIMAL; } Field *make_conversion_table_field(TABLE *, uint metadata, const Field *target) const; @@ -962,8 +1021,10 @@ public: class Type_handler_null: public Type_handler_string_result { + static const Name m_name_null; public: virtual ~Type_handler_null() {} + const Name name() const { return m_name_null; } enum_field_types field_type() const { return MYSQL_TYPE_NULL; } uint32 max_display_length(const Item *item) const { return 0; } Field *make_conversion_table_field(TABLE *, uint metadata, @@ -973,8 +1034,10 @@ public: class Type_handler_string: public Type_handler_string_result { + static const Name m_name_char; public: virtual ~Type_handler_string() {} + const Name name() const { return m_name_char; } enum_field_types field_type() const { return MYSQL_TYPE_STRING; } Field *make_conversion_table_field(TABLE *, uint metadata, const Field *target) const; @@ -983,8 +1046,10 @@ public: class Type_handler_varchar: public Type_handler_string_result { + static const Name m_name_varchar; public: virtual ~Type_handler_varchar() {} + const Name name() const { return m_name_varchar; } enum_field_types field_type() const { return MYSQL_TYPE_VARCHAR; } Field *make_conversion_table_field(TABLE *, uint metadata, const Field *target) const; @@ -993,8 +1058,10 @@ public: class Type_handler_tiny_blob: public Type_handler_string_result { + static const Name m_name_tinyblob; public: virtual ~Type_handler_tiny_blob() {} + const Name name() const { return m_name_tinyblob; } enum_field_types field_type() const { return MYSQL_TYPE_TINY_BLOB; } Field *make_conversion_table_field(TABLE *, uint metadata, const Field *target) const; @@ -1003,8 +1070,10 @@ public: class Type_handler_medium_blob: public Type_handler_string_result { + static const Name m_name_mediumblob; public: virtual ~Type_handler_medium_blob() {} + const Name name() const { return m_name_mediumblob; } enum_field_types field_type() const { return MYSQL_TYPE_MEDIUM_BLOB; } Field *make_conversion_table_field(TABLE *, uint metadata, const Field *target) const; @@ -1013,8 +1082,10 @@ public: class Type_handler_long_blob: public Type_handler_string_result { + static const Name m_name_longblob; public: virtual ~Type_handler_long_blob() {} + const Name name() const { return m_name_longblob; } enum_field_types field_type() const { return MYSQL_TYPE_LONG_BLOB; } Field *make_conversion_table_field(TABLE *, uint metadata, const Field *target) const; @@ -1023,8 +1094,10 @@ public: class Type_handler_blob: public Type_handler_string_result { + static const Name m_name_blob; public: virtual ~Type_handler_blob() {} + const Name name() const { return m_name_blob; } enum_field_types field_type() const { return MYSQL_TYPE_BLOB; } Field *make_conversion_table_field(TABLE *, uint metadata, const Field *target) const; @@ -1034,19 +1107,27 @@ public: #ifdef HAVE_SPATIAL class Type_handler_geometry: public Type_handler_string_result { + static const Name m_name_geometry; public: virtual ~Type_handler_geometry() {} + const Name name() const { return m_name_geometry; } enum_field_types field_type() const { return MYSQL_TYPE_GEOMETRY; } Field *make_conversion_table_field(TABLE *, uint metadata, const Field *target) const; + bool is_traditional_type() const + { + return false; + } }; #endif class Type_handler_enum: public Type_handler_string_result { + static const Name m_name_enum; public: virtual ~Type_handler_enum() {} + const Name name() const { return m_name_enum; } enum_field_types field_type() const { return MYSQL_TYPE_VARCHAR; } virtual enum_field_types real_field_type() const { return MYSQL_TYPE_ENUM; } Field *make_conversion_table_field(TABLE *, uint metadata, @@ -1056,8 +1137,10 @@ public: class Type_handler_set: public Type_handler_string_result { + static const Name m_name_set; public: virtual ~Type_handler_set() {} + const Name name() const { return m_name_set; } enum_field_types field_type() const { return MYSQL_TYPE_VARCHAR; } virtual enum_field_types real_field_type() const { return MYSQL_TYPE_SET; } Field *make_conversion_table_field(TABLE *, uint metadata, @@ -1127,6 +1210,9 @@ public: } void aggregate_for_comparison(const Type_handler *other); bool aggregate_for_comparison(Item **items, uint nitems); + bool aggregate_for_result(const Type_handler *other); + bool aggregate_for_result(const char *funcname, + Item **item, uint nitems, bool treat_bit_as_number); }; @@ -1150,5 +1236,57 @@ extern Type_handler_varchar type_handler_varchar; extern Type_handler_longlong type_handler_longlong; extern Type_handler_newdecimal type_handler_newdecimal; extern Type_handler_datetime type_handler_datetime; +extern Type_handler_longlong type_handler_longlong; +extern Type_handler_bit type_handler_bit; + +class Type_aggregator +{ + class Pair + { + public: + const Type_handler *m_handler1; + const Type_handler *m_handler2; + const Type_handler *m_result; + Pair() { } + Pair(const Type_handler *handler1, + const Type_handler *handler2, + const Type_handler *result) + :m_handler1(handler1), m_handler2(handler2), m_result(result) + { } + bool eq(const Type_handler *handler1, const Type_handler *handler2) const + { + return m_handler1 == handler1 && m_handler2 == handler2; + } + }; + Dynamic_array<Pair> m_array; + const Pair* find_pair(const Type_handler *handler1, + const Type_handler *handler2) const + { + for (uint i= 0; i < m_array.elements(); i++) + { + const Pair& el= m_array.at(i); + if (el.eq(handler1, handler2) || el.eq(handler2, handler1)) + return ⪙ + } + return NULL; + } +public: + Type_aggregator() + { } + bool add(const Type_handler *handler1, + const Type_handler *handler2, + const Type_handler *result) + { + return m_array.append(Pair(handler1, handler2, result)); + } + const Type_handler *find_handler(const Type_handler *handler1, + const Type_handler *handler2) const + { + const Pair* el= find_pair(handler1, handler2); + return el ? el->m_result : NULL; + } +}; + +extern Type_aggregator type_aggregator_for_result; #endif /* SQL_TYPE_H_INCLUDED */ |