maria-developers team mailing list archive
-
maria-developers team
-
Mailing list archive
-
Message #10637
MDEV-12506 Split Item_func_min_max::fix_length_and_dec() into methods in Type_handler
Hello Alexey,
Can you please review a patch for MDEV-12506?
Thanks.
commit bb6cc2b98402c0bc83150b8ad5c09622813a887c
Author: Alexander Barkov <bar@xxxxxxxxxxx>
Date: Sat Apr 15 07:21:55 2017 +0400
MDEV-12506 Split Item_func_min_max::fix_length_and_dec() into methods in Type_handler
This patch does the following:
1. Adds a new method Type_handler_hybrid_field_type::aggregate_for_min_max()
- For non-traditional data types it uses
type_handler_data->m_type_aggregator_for_result.find_handler()
This allows pluggable data types to define in the future their
own behavior of the result data type detection for LEAST/GREATEST.
Also, this disallows expressions of the GEOMETRY data type
(and its variants such as POINT) to be mixed in with
numeric and temporal data types in LEAST/GREATEST.
- For traditional data types it reproduces the old behavior of
the result data type detection (but not attributes, see below).
2. Adds a new virtual method Type_handler::Item_func_min_max_fix_attributes()
and reuses as much as possible the code that calculates data type attributes
for CASE-alike functions (e.g. CASE..THEN, COALESCE, IF).
As the old code responsible for attributes calculation in the old
implementation of Item_func_min_max::fix_length_and_dec()
was not fully correct, this automatically fixes the following bugs:
- MDEV-12497 Wrong data type for LEAST(latin1_expr, utf8_expr)
The old fix_length_and_dec() calculated max_length before
character set aggregation. Now max_length is calculated after, in
Item_func::count_string_length() called from
Item_func::aggregate_attributes_string() called from
Type_handler_string_result::Item_hybrid_func_fix_attributes() called from
Type_handler::Item_func_min_max_fix_attributes() called from
Item_func_min_max::fix_length_and_dec().
- MDEV-12504 Wrong data type for LEAST(date_expr,time_expr)
The old fix_length_and_dec() simply used the maximum of max_length
among all arguments to set its own max_length and did not take
into account that a mixture of DATE and TIME becomes DATETIME.
Now this is correctly handled by:
Type_handler_datetime_common::Item_hybrid_func_fix_attributes() called from
Type_handler::Item_func_min_max_fix_attributes() called from
Item_func_min_max::fix_length_and_dec().
3. Removes the old implementation of Item_func_min_max::fix_length_and_dec()
and replaces it to calls of the new methods.
4. Cleanup: moves the code related to unsigned_flag processing
from Type_handler_hybrid_field_type::aggregate_for_result()
to Type_handler_int_result::Item_hybrid_func_fix_attributes().
This is done:
- to avoid code duplication in
Type_handler_hybrid_field_type::aggregate_for_min_max()
- to get rid of one more call for field_type(), which is unfriendly
to the conceipt of pluggable data types.
diff --git a/mysql-test/r/ctype_binary.result b/mysql-test/r/ctype_binary.result
index 976c3df..6c16b10 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 037f3cb..f4f54d8 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 fc37dcf..4b92c81 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 389a5d3..5b2fadb 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 eb18bb5..573799f 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 324db45..edb3a55 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 e713233..86bdc5e 100644
--- a/mysql-test/r/gis.result
+++ b/mysql-test/r/gis.result
@@ -2804,7 +2804,7 @@ 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
+ `NULL` point DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
@@ -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 dd8a399..2efbdbe 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 a19de83..d64be95 100644
--- a/mysql-test/t/gis.test
+++ b/mysql-test/t/gis.test
@@ -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_func.cc b/sql/item_func.cc
index a52c260..96057ec 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 7f9321f..e5fa4d1 100644
--- a/sql/item_func.h
+++ b/sql/item_func.h
@@ -1442,6 +1442,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)
@@ -1482,7 +1484,32 @@ class Item_func_min_max :public Item_hybrid_func
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 337111f..0e7e41f 100644
--- a/sql/sql_type.cc
+++ b/sql/sql_type.cc
@@ -368,7 +368,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();
@@ -386,26 +385,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;
}
@@ -477,6 +456,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)
@@ -1311,6 +1398,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;
}
@@ -1377,6 +1475,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 891c785..5ac6d22 100644
--- a/sql/sql_type.h
+++ b/sql/sql_type.h
@@ -426,6 +426,10 @@ class Type_handler
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;
@@ -781,6 +785,8 @@ class Type_handler_real_result: public Type_handler_numeric
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;
@@ -1523,6 +1529,7 @@ class Type_handler_set: public Type_handler_string_result
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)
@@ -1578,6 +1585,8 @@ class Type_handler_hybrid_field_type
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);
};
