summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorAlexander Barkov <bar@mariadb.org>2015-09-30 12:37:34 +0400
committerAlexander Barkov <bar@mariadb.org>2015-09-30 12:37:34 +0400
commitcc9cfecab78bde9376e4406bf24506e92fdaaeac (patch)
tree1ca3c873ef881d5e25be4aa390253f7eeb17c4ec
parent09b87d6293b4b41321ba98366d5d7ade9ad681d3 (diff)
downloadmariadb-git-cc9cfecab78bde9376e4406bf24506e92fdaaeac.tar.gz
MDEV-8865 Wrong field type or metadata for COALESCE(signed_int_column, unsigned_int_column)
Item_func_hybrid_field_type did not return correct field_type(), cmp_type() and result_type() in some cases, because cached_result_type and cached_field_type were set in independent pieces of the code and did not properly match to each other. Fix: - Removing Item_func_hybrid_result_type - Deriving Item_func_hybrid_field_type directly from Item_func - Introducing a new class Type_handler which guarantees that field_type(), cmp_type() and result_type() are always properly synchronized and using the new class in Item_func_hybrid_field_type.
Diffstat
-rw-r--r--libmysqld/CMakeLists.txt1
-rw-r--r--mysql-test/include/func_hybrid_type.inc81
-rw-r--r--mysql-test/r/func_hybrid_type.result1396
-rw-r--r--mysql-test/t/func_hybrid_type.test63
-rw-r--r--sql/CMakeLists.txt1
-rw-r--r--sql/item.h11
-rw-r--r--sql/item_cmpfunc.cc135
-rw-r--r--sql/item_func.cc85
-rw-r--r--sql/item_func.h74
-rw-r--r--sql/item_timefunc.h2
-rw-r--r--sql/sql_type.cc106
-rw-r--r--sql/sql_type.h286
12 files changed, 2084 insertions, 157 deletions
diff --git a/libmysqld/CMakeLists.txt b/libmysqld/CMakeLists.txt
index 5e0927395c2..84e1d5bd677 100644
--- a/libmysqld/CMakeLists.txt
+++ b/libmysqld/CMakeLists.txt
@@ -104,6 +104,7 @@ SET(SQL_EMBEDDED_SOURCES emb_qcache.cc libmysqld.c lib_sql.cc
../sql/sql_explain.cc ../sql/sql_explain.h
../sql/sql_analyze_stmt.cc ../sql/sql_analyze_stmt.h
../sql/compat56.cc
+ ../sql/sql_type.cc ../sql/sql_type.h
../sql/table_cache.cc ../sql/mf_iocache_encr.cc
../sql/item_inetfunc.cc
../sql/wsrep_dummy.cc ../sql/encryption.cc
diff --git a/mysql-test/include/func_hybrid_type.inc b/mysql-test/include/func_hybrid_type.inc
new file mode 100644
index 00000000000..854c7ebd34e
--- /dev/null
+++ b/mysql-test/include/func_hybrid_type.inc
@@ -0,0 +1,81 @@
+# "mtr --ps" returns different values in "Max length"
+--disable_ps_protocol
+--enable_metadata
+--vertical_results
+SELECT
+ a AS ___________a,
+ CASE WHEN a IS NOT NULL THEN a END AS case_______a,
+ CASE WHEN a IS NOT NULL THEN a ELSE a END AS case_____a_a,
+ COALESCE(a) AS coalesce___a,
+ COALESCE(a, a) AS coalesce_a_a,
+ IF(a IS NULL, a, a) AS if_______a_a,
+ IFNULL(a, a) AS ifnull___a_a,
+ LEAST(a, a) AS least____a_a,
+ GREATEST(a, a) AS greatest_a_a,
+ b AS ___________b,
+ CASE WHEN a IS NOT NULL THEN b END AS case_______b,
+ CASE WHEN a IS NOT NULL THEN b ELSE b END AS case_____b_b,
+ COALESCE(b) AS coalesce___b,
+ COALESCE(b, b) AS coalesce_b_b,
+ IF(a IS NULL, b, b) AS if_______b_b,
+ IFNULL(b, b) AS ifnull___b_b,
+ LEAST(b, b) AS least____b_b,
+ GREATEST(b, b) AS greatest_b_b
+FROM t1;
+SELECT
+ CASE WHEN a IS NOT NULL THEN a ELSE b END AS case_____a_b,
+ CASE WHEN a IS NOT NULL THEN b ELSE a END AS case_____b_a,
+ COALESCE(a, b) AS coalesce_a_b,
+ COALESCE(b, a) AS coalesce_b_a,
+ IF(a IS NULL, a, b) AS if_______a_b,
+ IF(a IS NULL, b, a) AS if_______b_a,
+ IFNULL(a, b) AS ifnull___a_b,
+ IFNULL(b, a) AS ifnull___b_a,
+ LEAST(a, b) AS least____a_b,
+ LEAST(b, a) AS least____b_a,
+ GREATEST(a, b) AS greatest_a_b,
+ GREATEST(b, a) AS greatest_b_a
+FROM t1;
+--horizontal_results
+--disable_metadata
+--enable_ps_protocol
+CREATE TABLE t2 AS
+SELECT
+ a AS ___________a,
+ CASE WHEN a IS NOT NULL THEN a END AS case_______a,
+ CASE WHEN a IS NOT NULL THEN a ELSE a END AS case_____a_a,
+ COALESCE(a) AS coalesce___a,
+ COALESCE(a, a) AS coalesce_a_a,
+ IF(a IS NULL, a, a) AS if_______a_a,
+ IFNULL(a, a) AS ifnull___a_a,
+ LEAST(a, a) AS least____a_a,
+ GREATEST(a, a) AS greatest_a_a,
+ b AS ___________b,
+ CASE WHEN a IS NOT NULL THEN b END AS case_______b,
+ CASE WHEN a IS NOT NULL THEN b ELSE b END AS case_____b_b,
+ COALESCE(b) AS coalesce___b,
+ COALESCE(b, b) AS coalesce_b_b,
+ IF(a IS NULL, b, b) AS if_______b_b,
+ IFNULL(b, b) AS ifnull___b_b,
+ LEAST(b, b) AS least____b_b,
+ GREATEST(b, b) AS greatest_b_b
+FROM t1;
+SHOW CREATE TABLE t2;
+DROP TABLE t2;
+CREATE TABLE t2 AS
+SELECT
+ CASE WHEN a IS NOT NULL THEN a ELSE b END AS case_____a_b,
+ CASE WHEN a IS NOT NULL THEN b ELSE a END AS case_____b_a,
+ COALESCE(a, b) AS coalesce_a_b,
+ COALESCE(b, a) AS coalesce_b_a,
+ IF(a IS NULL, a, b) AS if_______a_b,
+ IF(a IS NULL, b, a) AS if_______b_a,
+ IFNULL(a, b) AS ifnull___a_b,
+ IFNULL(b, a) AS ifnull___b_a,
+ LEAST(a, b) AS least____a_b,
+ LEAST(b, a) AS least____b_a,
+ GREATEST(a, b) AS greatest_a_b,
+ GREATEST(b, a) AS greatest_b_a
+FROM t1;
+SHOW CREATE TABLE t2;
+DROP TABLE t2;
diff --git a/mysql-test/r/func_hybrid_type.result b/mysql-test/r/func_hybrid_type.result
new file mode 100644
index 00000000000..0d2ac5aab7b
--- /dev/null
+++ b/mysql-test/r/func_hybrid_type.result
@@ -0,0 +1,1396 @@
+#
+# Start of 10.1 tests
+#
+#
+# MDEV-8865 Wrong field type or metadata for COALESCE(signed_int_column, unsigned_int_column)
+#
+#
+CREATE TABLE t1 (a INT, b INT UNSIGNED);
+INSERT INTO t1 VALUES (1,1);
+INSERT INTO t1 VALUES (-1,1);
+INSERT INTO t1 VALUES (-2147483648,4294967295);
+SELECT
+a AS ___________a,
+CASE WHEN a IS NOT NULL THEN a END AS case_______a,
+CASE WHEN a IS NOT NULL THEN a ELSE a END AS case_____a_a,
+COALESCE(a) AS coalesce___a,
+COALESCE(a, a) AS coalesce_a_a,
+IF(a IS NULL, a, a) AS if_______a_a,
+IFNULL(a, a) AS ifnull___a_a,
+LEAST(a, a) AS least____a_a,
+GREATEST(a, a) AS greatest_a_a,
+b AS ___________b,
+CASE WHEN a IS NOT NULL THEN b END AS case_______b,
+CASE WHEN a IS NOT NULL THEN b ELSE b END AS case_____b_b,
+COALESCE(b) AS coalesce___b,
+COALESCE(b, b) AS coalesce_b_b,
+IF(a IS NULL, b, b) AS if_______b_b,
+IFNULL(b, b) AS ifnull___b_b,
+LEAST(b, b) AS least____b_b,
+GREATEST(b, b) AS greatest_b_b
+FROM t1;
+Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
+def test t1 t1 a ___________a 3 11 11 Y 32768 0 63
+def case_______a 3 11 11 Y 32896 0 63
+def case_____a_a 3 11 11 Y 32896 0 63
+def coalesce___a 3 11 11 Y 32896 0 63
+def coalesce_a_a 3 11 11 Y 32896 0 63
+def if_______a_a 3 11 11 Y 32896 0 63
+def ifnull___a_a 3 11 11 Y 32896 0 63
+def least____a_a 3 11 11 Y 32896 0 63
+def greatest_a_a 3 11 11 Y 32896 0 63
+def test t1 t1 b ___________b 3 10 10 Y 32800 0 63
+def case_______b 3 10 10 Y 32928 0 63
+def case_____b_b 3 10 10 Y 32928 0 63
+def coalesce___b 3 10 10 Y 32928 0 63
+def coalesce_b_b 3 10 10 Y 32928 0 63
+def if_______b_b 3 10 10 Y 32928 0 63
+def ifnull___b_b 3 10 10 Y 32928 0 63
+def least____b_b 3 10 10 Y 32928 0 63
+def greatest_b_b 3 10 10 Y 32928 0 63
+___________a 1
+case_______a 1
+case_____a_a 1
+coalesce___a 1
+coalesce_a_a 1
+if_______a_a 1
+ifnull___a_a 1
+least____a_a 1
+greatest_a_a 1
+___________b 1
+case_______b 1
+case_____b_b 1
+coalesce___b 1
+coalesce_b_b 1
+if_______b_b 1
+ifnull___b_b 1
+least____b_b 1
+greatest_b_b 1
+___________a -1
+case_______a -1
+case_____a_a -1
+coalesce___a -1
+coalesce_a_a -1
+if_______a_a -1
+ifnull___a_a -1
+least____a_a -1
+greatest_a_a -1
+___________b 1
+case_______b 1
+case_____b_b 1
+coalesce___b 1
+coalesce_b_b 1
+if_______b_b 1
+ifnull___b_b 1
+least____b_b 1
+greatest_b_b 1
+___________a -2147483648
+case_______a -2147483648
+case_____a_a -2147483648
+coalesce___a -2147483648
+coalesce_a_a -2147483648
+if_______a_a -2147483648
+ifnull___a_a -2147483648
+least____a_a -2147483648
+greatest_a_a -2147483648
+___________b 4294967295
+case_______b 4294967295
+case_____b_b 4294967295
+coalesce___b 4294967295
+coalesce_b_b 4294967295
+if_______b_b 4294967295
+ifnull___b_b 4294967295
+least____b_b 4294967295
+greatest_b_b 4294967295
+SELECT
+CASE WHEN a IS NOT NULL THEN a ELSE b END AS case_____a_b,
+CASE WHEN a IS NOT NULL THEN b ELSE a END AS case_____b_a,
+COALESCE(a, b) AS coalesce_a_b,
+COALESCE(b, a) AS coalesce_b_a,
+IF(a IS NULL, a, b) AS if_______a_b,
+IF(a IS NULL, b, a) AS if_______b_a,
+IFNULL(a, b) AS ifnull___a_b,
+IFNULL(b, a) AS ifnull___b_a,
+LEAST(a, b) AS least____a_b,
+LEAST(b, a) AS least____b_a,
+GREATEST(a, b) AS greatest_a_b,
+GREATEST(b, a) AS greatest_b_a
+FROM t1;
+Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
+def case_____a_b 246 11 11 Y 32896 0 63
+def case_____b_a 246 11 10 Y 32896 0 63
+def coalesce_a_b 246 11 11 Y 32896 0 63
+def coalesce_b_a 246 11 10 Y 32896 0 63
+def if_______a_b 246 11 10 Y 32896 0 63
+def if_______b_a 246 11 11 Y 32896 0 63
+def ifnull___a_b 246 11 11 Y 32896 0 63
+def ifnull___b_a 246 11 10 Y 32896 0 63
+def least____a_b 246 11 11 Y 32896 0 63
+def least____b_a 246 11 11 Y 32896 0 63
+def greatest_a_b 246 11 10 Y 32896 0 63
+def greatest_b_a 246 11 10 Y 32896 0 63
+case_____a_b 1
+case_____b_a 1
+coalesce_a_b 1
+coalesce_b_a 1
+if_______a_b 1
+if_______b_a 1
+ifnull___a_b 1
+ifnull___b_a 1
+least____a_b 1
+least____b_a 1
+greatest_a_b 1
+greatest_b_a 1
+case_____a_b -1
+case_____b_a 1
+coalesce_a_b -1
+coalesce_b_a 1
+if_______a_b 1
+if_______b_a -1
+ifnull___a_b -1
+ifnull___b_a 1
+least____a_b -1
+least____b_a -1
+greatest_a_b 1
+greatest_b_a 1
+case_____a_b -2147483648
+case_____b_a 4294967295
+coalesce_a_b -2147483648
+coalesce_b_a 4294967295
+if_______a_b 4294967295
+if_______b_a -2147483648
+ifnull___a_b -2147483648
+ifnull___b_a 4294967295
+least____a_b -2147483648
+least____b_a -2147483648
+greatest_a_b 4294967295
+greatest_b_a 4294967295
+CREATE TABLE t2 AS
+SELECT
+a AS ___________a,
+CASE WHEN a IS NOT NULL THEN a END AS case_______a,
+CASE WHEN a IS NOT NULL THEN a ELSE a END AS case_____a_a,
+COALESCE(a) AS coalesce___a,
+COALESCE(a, a) AS coalesce_a_a,
+IF(a IS NULL, a, a) AS if_______a_a,
+IFNULL(a, a) AS ifnull___a_a,
+LEAST(a, a) AS least____a_a,
+GREATEST(a, a) AS greatest_a_a,
+b AS ___________b,
+CASE WHEN a IS NOT NULL THEN b END AS case_______b,
+CASE WHEN a IS NOT NULL THEN b ELSE b END AS case_____b_b,
+COALESCE(b) AS coalesce___b,
+COALESCE(b, b) AS coalesce_b_b,
+IF(a IS NULL, b, b) AS if_______b_b,
+IFNULL(b, b) AS ifnull___b_b,
+LEAST(b, b) AS least____b_b,
+GREATEST(b, b) AS greatest_b_b
+FROM t1;
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `___________a` int(11) DEFAULT NULL,
+ `case_______a` int(11) DEFAULT NULL,
+ `case_____a_a` int(11) DEFAULT NULL,
+ `coalesce___a` int(11) DEFAULT NULL,
+ `coalesce_a_a` int(11) DEFAULT NULL,
+ `if_______a_a` int(11) DEFAULT NULL,
+ `ifnull___a_a` int(11) DEFAULT NULL,
+ `least____a_a` int(11) DEFAULT NULL,
+ `greatest_a_a` int(11) DEFAULT NULL,
+ `___________b` int(10) unsigned DEFAULT NULL,
+ `case_______b` int(10) unsigned DEFAULT NULL,
+ `case_____b_b` int(10) unsigned DEFAULT NULL,
+ `coalesce___b` int(10) unsigned DEFAULT NULL,
+ `coalesce_b_b` int(10) unsigned DEFAULT NULL,
+ `if_______b_b` int(10) unsigned DEFAULT NULL,
+ `ifnull___b_b` int(10) unsigned DEFAULT NULL,
+ `least____b_b` int(10) unsigned DEFAULT NULL,
+ `greatest_b_b` int(10) unsigned DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t2;
+CREATE TABLE t2 AS
+SELECT
+CASE WHEN a IS NOT NULL THEN a ELSE b END AS case_____a_b,
+CASE WHEN a IS NOT NULL THEN b ELSE a END AS case_____b_a,
+COALESCE(a, b) AS coalesce_a_b,
+COALESCE(b, a) AS coalesce_b_a,
+IF(a IS NULL, a, b) AS if_______a_b,
+IF(a IS NULL, b, a) AS if_______b_a,
+IFNULL(a, b) AS ifnull___a_b,
+IFNULL(b, a) AS ifnull___b_a,
+LEAST(a, b) AS least____a_b,
+LEAST(b, a) AS least____b_a,
+GREATEST(a, b) AS greatest_a_b,
+GREATEST(b, a) AS greatest_b_a
+FROM t1;
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `case_____a_b` decimal(10,0) DEFAULT NULL,
+ `case_____b_a` decimal(10,0) DEFAULT NULL,
+ `coalesce_a_b` decimal(10,0) DEFAULT NULL,
+ `coalesce_b_a` decimal(10,0) DEFAULT NULL,
+ `if_______a_b` decimal(10,0) DEFAULT NULL,
+ `if_______b_a` decimal(10,0) DEFAULT NULL,
+ `ifnull___a_b` decimal(10,0) DEFAULT NULL,
+ `ifnull___b_a` decimal(10,0) DEFAULT NULL,
+ `least____a_b` decimal(10,0) DEFAULT NULL,
+ `least____b_a` decimal(10,0) DEFAULT NULL,
+ `greatest_a_b` decimal(10,0) DEFAULT NULL,
+ `greatest_b_a` decimal(10,0) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t2;
+DROP TABLE t1;
+#
+CREATE TABLE t1 (a INT, b INT);
+INSERT INTO t1 VALUES (1,1);
+INSERT INTO t1 VALUES (-2147483648,2147483647);
+SELECT
+a AS ___________a,
+CASE WHEN a IS NOT NULL THEN a END AS case_______a,
+CASE WHEN a IS NOT NULL THEN a ELSE a END AS case_____a_a,
+COALESCE(a) AS coalesce___a,
+COALESCE(a, a) AS coalesce_a_a,
+IF(a IS NULL, a, a) AS if_______a_a,
+IFNULL(a, a) AS ifnull___a_a,
+LEAST(a, a) AS least____a_a,
+GREATEST(a, a) AS greatest_a_a,
+b AS ___________b,
+CASE WHEN a IS NOT NULL THEN b END AS case_______b,
+CASE WHEN a IS NOT NULL THEN b ELSE b END AS case_____b_b,
+COALESCE(b) AS coalesce___b,
+COALESCE(b, b) AS coalesce_b_b,
+IF(a IS NULL, b, b) AS if_______b_b,
+IFNULL(b, b) AS ifnull___b_b,
+LEAST(b, b) AS least____b_b,
+GREATEST(b, b) AS greatest_b_b
+FROM t1;
+Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
+def test t1 t1 a ___________a 3 11 11 Y 32768 0 63
+def case_______a 3 11 11 Y 32896 0 63
+def case_____a_a 3 11 11 Y 32896 0 63
+def coalesce___a 3 11 11 Y 32896 0 63
+def coalesce_a_a 3 11 11 Y 32896 0 63
+def if_______a_a 3 11 11 Y 32896 0 63
+def ifnull___a_a 3 11 11 Y 32896 0 63
+def least____a_a 3 11 11 Y 32896 0 63
+def greatest_a_a 3 11 11 Y 32896 0 63
+def test t1 t1 b ___________b 3 11 10 Y 32768 0 63
+def case_______b 3 11 10 Y 32896 0 63
+def case_____b_b 3 11 10 Y 32896 0 63
+def coalesce___b 3 11 10 Y 32896 0 63
+def coalesce_b_b 3 11 10 Y 32896 0 63
+def if_______b_b 3 11 10 Y 32896 0 63
+def ifnull___b_b 3 11 10 Y 32896 0 63
+def least____b_b 3 11 10 Y 32896 0 63
+def greatest_b_b 3 11 10 Y 32896 0 63
+___________a 1
+case_______a 1
+case_____a_a 1
+coalesce___a 1
+coalesce_a_a 1
+if_______a_a 1
+ifnull___a_a 1
+least____a_a 1
+greatest_a_a 1
+___________b 1
+case_______b 1
+case_____b_b 1
+coalesce___b 1
+coalesce_b_b 1
+if_______b_b 1
+ifnull___b_b 1
+least____b_b 1
+greatest_b_b 1
+___________a -2147483648
+case_______a -2147483648
+case_____a_a -2147483648
+coalesce___a -2147483648
+coalesce_a_a -2147483648
+if_______a_a -2147483648
+ifnull___a_a -2147483648
+least____a_a -2147483648
+greatest_a_a -2147483648
+___________b 2147483647
+case_______b 2147483647
+case_____b_b 2147483647
+coalesce___b 2147483647
+coalesce_b_b 2147483647
+if_______b_b 2147483647
+ifnull___b_b 2147483647
+least____b_b 2147483647
+greatest_b_b 2147483647
+SELECT
+CASE WHEN a IS NOT NULL THEN a ELSE b END AS case_____a_b,
+CASE WHEN a IS NOT NULL THEN b ELSE a END AS case_____b_a,
+COALESCE(a, b) AS coalesce_a_b,
+COALESCE(b, a) AS coalesce_b_a,
+IF(a IS NULL, a, b) AS if_______a_b,
+IF(a IS NULL, b, a) AS if_______b_a,
+IFNULL(a, b) AS ifnull___a_b,
+IFNULL(b, a) AS ifnull___b_a,
+LEAST(a, b) AS least____a_b,
+LEAST(b, a) AS least____b_a,
+GREATEST(a, b) AS greatest_a_b,
+GREATEST(b, a) AS greatest_b_a
+FROM t1;
+Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
+def case_____a_b 3 11 11 Y 32896 0 63
+def case_____b_a 3 11 10 Y 32896 0 63
+def coalesce_a_b 3 11 11 Y 32896 0 63
+def coalesce_b_a 3 11 10 Y 32896 0 63
+def if_______a_b 3 11 10 Y 32896 0 63
+def if_______b_a 3 11 11 Y 32896 0 63
+def ifnull___a_b 3 11 11 Y 32896 0 63
+def ifnull___b_a 3 11 10 Y 32896 0 63
+def least____a_b 3 11 11 Y 32896 0 63
+def least____b_a 3 11 11 Y 32896 0 63
+def greatest_a_b 3 11 10 Y 32896 0 63
+def greatest_b_a 3 11 10 Y 32896 0 63
+case_____a_b 1
+case_____b_a 1
+coalesce_a_b 1
+coalesce_b_a 1
+if_______a_b 1
+if_______b_a 1
+ifnull___a_b 1
+ifnull___b_a 1
+least____a_b 1
+least____b_a 1
+greatest_a_b 1
+greatest_b_a 1
+case_____a_b -2147483648
+case_____b_a 2147483647
+coalesce_a_b -2147483648
+coalesce_b_a 2147483647
+if_______a_b 2147483647
+if_______b_a -2147483648
+ifnull___a_b -2147483648
+ifnull___b_a 2147483647
+least____a_b -2147483648
+least____b_a -2147483648
+greatest_a_b 2147483647
+greatest_b_a 2147483647
+CREATE TABLE t2 AS
+SELECT
+a AS ___________a,
+CASE WHEN a IS NOT NULL THEN a END AS case_______a,
+CASE WHEN a IS NOT NULL THEN a ELSE a END AS case_____a_a,
+COALESCE(a) AS coalesce___a,
+COALESCE(a, a) AS coalesce_a_a,
+IF(a IS NULL, a, a) AS if_______a_a,
+IFNULL(a, a) AS ifnull___a_a,
+LEAST(a, a) AS least____a_a,
+GREATEST(a, a) AS greatest_a_a,
+b AS ___________b,
+CASE WHEN a IS NOT NULL THEN b END AS case_______b,
+CASE WHEN a IS NOT NULL THEN b ELSE b END AS case_____b_b,
+COALESCE(b) AS coalesce___b,
+COALESCE(b, b) AS coalesce_b_b,
+IF(a IS NULL, b, b) AS if_______b_b,
+IFNULL(b, b) AS ifnull___b_b,
+LEAST(b, b) AS least____b_b,
+GREATEST(b, b) AS greatest_b_b
+FROM t1;
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `___________a` int(11) DEFAULT NULL,
+ `case_______a` int(11) DEFAULT NULL,
+ `case_____a_a` int(11) DEFAULT NULL,
+ `coalesce___a` int(11) DEFAULT NULL,
+ `coalesce_a_a` int(11) DEFAULT NULL,
+ `if_______a_a` int(11) DEFAULT NULL,
+ `ifnull___a_a` int(11) DEFAULT NULL,
+ `least____a_a` int(11) DEFAULT NULL,
+ `greatest_a_a` int(11) DEFAULT NULL,
+ `___________b` int(11) DEFAULT NULL,
+ `case_______b` int(11) DEFAULT NULL,
+ `case_____b_b` int(11) DEFAULT NULL,
+ `coalesce___b` int(11) DEFAULT NULL,
+ `coalesce_b_b` int(11) DEFAULT NULL,
+ `if_______b_b` int(11) DEFAULT NULL,
+ `ifnull___b_b` int(11) DEFAULT NULL,
+ `least____b_b` int(11) DEFAULT NULL,
+ `greatest_b_b` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t2;
+CREATE TABLE t2 AS
+SELECT
+CASE WHEN a IS NOT NULL THEN a ELSE b END AS case_____a_b,
+CASE WHEN a IS NOT NULL THEN b ELSE a END AS case_____b_a,
+COALESCE(a, b) AS coalesce_a_b,
+COALESCE(b, a) AS coalesce_b_a,
+IF(a IS NULL, a, b) AS if_______a_b,
+IF(a IS NULL, b, a) AS if_______b_a,
+IFNULL(a, b) AS ifnull___a_b,
+IFNULL(b, a) AS ifnull___b_a,
+LEAST(a, b) AS least____a_b,
+LEAST(b, a) AS least____b_a,
+GREATEST(a, b) AS greatest_a_b,
+GREATEST(b, a) AS greatest_b_a
+FROM t1;
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `case_____a_b` int(11) DEFAULT NULL,
+ `case_____b_a` int(11) DEFAULT NULL,
+ `coalesce_a_b` int(11) DEFAULT NULL,
+ `coalesce_b_a` int(11) DEFAULT NULL,
+ `if_______a_b` int(11) DEFAULT NULL,
+ `if_______b_a` int(11) DEFAULT NULL,
+ `ifnull___a_b` int(11) DEFAULT NULL,
+ `ifnull___b_a` int(11) DEFAULT NULL,
+ `least____a_b` int(11) DEFAULT NULL,
+ `least____b_a` int(11) DEFAULT NULL,
+ `greatest_a_b` int(11) DEFAULT NULL,
+ `greatest_b_a` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t2;
+DROP TABLE t1;
+#
+CREATE TABLE t1 (a BIGINT, b BIGINT UNSIGNED);
+INSERT INTO t1 VALUES (1,1);
+INSERT INTO t1 VALUES (-9223372036854775808,0xFFFFFFFFFFFFFFFF);
+SELECT
+a AS ___________a,
+CASE WHEN a IS NOT NULL THEN a END AS case_______a,
+CASE WHEN a IS NOT NULL THEN a ELSE a END AS case_____a_a,
+COALESCE(a) AS coalesce___a,
+COALESCE(a, a) AS coalesce_a_a,
+IF(a IS NULL, a, a) AS if_______a_a,
+IFNULL(a, a) AS ifnull___a_a,
+LEAST(a, a) AS least____a_a,
+GREATEST(a, a) AS greatest_a_a,
+b AS ___________b,
+CASE WHEN a IS NOT NULL THEN b END AS case_______b,
+CASE WHEN a IS NOT NULL THEN b ELSE b END AS case_____b_b,
+COALESCE(b) AS coalesce___b,
+COALESCE(b, b) AS coalesce_b_b,
+IF(a IS NULL, b, b) AS if_______b_b,
+IFNULL(b, b) AS ifnull___b_b,
+LEAST(b, b) AS least____b_b,
+GREATEST(b, b) AS greatest_b_b
+FROM t1;
+Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
+def test t1 t1 a ___________a 8 20 20 Y 32768 0 63
+def case_______a 8 20 20 Y 32896 0 63
+def case_____a_a 8 20 20 Y 32896 0 63
+def coalesce___a 8 20 20 Y 32896 0 63
+def coalesce_a_a 8 20 20 Y 32896 0 63
+def if_______a_a 8 20 20 Y 32896 0 63
+def ifnull___a_a 8 20 20 Y 32896 0 63
+def least____a_a 8 20 20 Y 32896 0 63
+def greatest_a_a 8 20 20 Y 32896 0 63
+def test t1 t1 b ___________b 8 20 20 Y 32800 0 63
+def case_______b 8 20 20 Y 32928 0 63
+def case_____b_b 8 20 20 Y 32928 0 63
+def coalesce___b 8 20 20 Y 32928 0 63
+def coalesce_b_b 8 20 20 Y 32928 0 63
+def if_______b_b 8 20 20 Y 32928 0 63
+def ifnull___b_b 8 20 20 Y 32928 0 63
+def least____b_b 8 20 20 Y 32928 0 63
+def greatest_b_b 8 20 20 Y 32928 0 63
+___________a 1
+case_______a 1
+case_____a_a 1
+coalesce___a 1
+coalesce_a_a 1
+if_______a_a 1
+ifnull___a_a 1
+least____a_a 1
+greatest_a_a 1
+___________b 1
+case_______b 1
+case_____b_b 1
+coalesce___b 1
+coalesce_b_b 1
+if_______b_b 1
+ifnull___b_b 1
+least____b_b 1
+greatest_b_b 1
+___________a -9223372036854775808
+case_______a -9223372036854775808
+case_____a_a -9223372036854775808
+coalesce___a -9223372036854775808
+coalesce_a_a -9223372036854775808
+if_______a_a -9223372036854775808
+ifnull___a_a -9223372036854775808
+least____a_a -9223372036854775808
+greatest_a_a -9223372036854775808
+___________b 18446744073709551615
+case_______b 18446744073709551615
+case_____b_b 18446744073709551615
+coalesce___b 18446744073709551615
+coalesce_b_b 18446744073709551615
+if_______b_b 18446744073709551615
+ifnull___b_b 18446744073709551615
+least____b_b 18446744073709551615
+greatest_b_b 18446744073709551615
+SELECT
+CASE WHEN a IS NOT NULL THEN a ELSE b END AS case_____a_b,
+CASE WHEN a IS NOT NULL THEN b ELSE a END AS case_____b_a,
+COALESCE(a, b) AS coalesce_a_b,
+COALESCE(b, a) AS coalesce_b_a,
+IF(a IS NULL, a, b) AS if_______a_b,
+IF(a IS NULL, b, a) AS if_______b_a,
+IFNULL(a, b) AS ifnull___a_b,
+IFNULL(b, a) AS ifnull___b_a,
+LEAST(a, b) AS least____a_b,
+LEAST(b, a) AS least____b_a,
+GREATEST(a, b) AS greatest_a_b,
+GREATEST(b, a) AS greatest_b_a
+FROM t1;
+Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
+def case_____a_b 246 21 20 Y 32896 0 63
+def case_____b_a 246 21 20 Y 32896 0 63
+def coalesce_a_b 246 21 20 Y 32896 0 63
+def coalesce_b_a 246 21 20 Y 32896 0 63
+def if_______a_b 246 21 20 Y 32896 0 63
+def if_______b_a 246 21 20 Y 32896 0 63
+def ifnull___a_b 246 21 20 Y 32896 0 63
+def ifnull___b_a 246 21 20 Y 32896 0 63
+def least____a_b 246 21 20 Y 32896 0 63
+def least____b_a 246 21 20 Y 32896 0 63
+def greatest_a_b 246 21 20 Y 32896 0 63
+def greatest_b_a 246 21 20 Y 32896 0 63
+case_____a_b 1
+case_____b_a 1
+coalesce_a_b 1
+coalesce_b_a 1
+if_______a_b 1
+if_______b_a 1
+ifnull___a_b 1
+ifnull___b_a 1
+least____a_b 1
+least____b_a 1
+greatest_a_b 1
+greatest_b_a 1
+case_____a_b -9223372036854775808
+case_____b_a 18446744073709551615
+coalesce_a_b -9223372036854775808
+coalesce_b_a 18446744073709551615
+if_______a_b 18446744073709551615
+if_______b_a -9223372036854775808
+ifnull___a_b -9223372036854775808
+ifnull___b_a 18446744073709551615
+least____a_b -9223372036854775808
+least____b_a -9223372036854775808
+greatest_a_b 18446744073709551615
+greatest_b_a 18446744073709551615
+CREATE TABLE t2 AS
+SELECT
+a AS ___________a,
+CASE WHEN a IS NOT NULL THEN a END AS case_______a,
+CASE WHEN a IS NOT NULL THEN a ELSE a END AS case_____a_a,
+COALESCE(a) AS coalesce___a,
+COALESCE(a, a) AS coalesce_a_a,
+IF(a IS NULL, a, a) AS if_______a_a,
+IFNULL(a, a) AS ifnull___a_a,
+LEAST(a, a) AS least____a_a,
+GREATEST(a, a) AS greatest_a_a,
+b AS ___________b,
+CASE WHEN a IS NOT NULL THEN b END AS case_______b,
+CASE WHEN a IS NOT NULL THEN b ELSE b END AS case_____b_b,
+COALESCE(b) AS coalesce___b,
+COALESCE(b, b) AS coalesce_b_b,
+IF(a IS NULL, b, b) AS if_______b_b,
+IFNULL(b, b) AS ifnull___b_b,
+LEAST(b, b) AS least____b_b,
+GREATEST(b, b) AS greatest_b_b
+FROM t1;
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `___________a` bigint(20) DEFAULT NULL,
+ `case_______a` bigint(20) DEFAULT NULL,
+ `case_____a_a` bigint(20) DEFAULT NULL,
+ `coalesce___a` bigint(20) DEFAULT NULL,
+ `coalesce_a_a` bigint(20) DEFAULT NULL,
+ `if_______a_a` bigint(20) DEFAULT NULL,
+ `ifnull___a_a` bigint(20) DEFAULT NULL,
+ `least____a_a` bigint(20) DEFAULT NULL,
+ `greatest_a_a` bigint(20) DEFAULT NULL,
+ `___________b` bigint(20) unsigned DEFAULT NULL,
+ `case_______b` bigint(20) unsigned DEFAULT NULL,
+ `case_____b_b` bigint(20) unsigned DEFAULT NULL,
+ `coalesce___b` bigint(20) unsigned DEFAULT NULL,
+ `coalesce_b_b` bigint(20) unsigned DEFAULT NULL,
+ `if_______b_b` bigint(20) unsigned DEFAULT NULL,
+ `ifnull___b_b` bigint(20) unsigned DEFAULT NULL,
+ `least____b_b` bigint(20) unsigned DEFAULT NULL,
+ `greatest_b_b` bigint(20) unsigned DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t2;
+CREATE TABLE t2 AS
+SELECT
+CASE WHEN a IS NOT NULL THEN a ELSE b END AS case_____a_b,
+CASE WHEN a IS NOT NULL THEN b ELSE a END AS case_____b_a,
+COALESCE(a, b) AS coalesce_a_b,
+COALESCE(b, a) AS coalesce_b_a,
+IF(a IS NULL, a, b) AS if_______a_b,
+IF(a IS NULL, b, a) AS if_______b_a,
+IFNULL(a, b) AS ifnull___a_b,
+IFNULL(b, a) AS ifnull___b_a,
+LEAST(a, b) AS least____a_b,
+LEAST(b, a) AS least____b_a,
+GREATEST(a, b) AS greatest_a_b,
+GREATEST(b, a) AS greatest_b_a
+FROM t1;
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `case_____a_b` decimal(20,0) DEFAULT NULL,
+ `case_____b_a` decimal(20,0) DEFAULT NULL,
+ `coalesce_a_b` decimal(20,0) DEFAULT NULL,
+ `coalesce_b_a` decimal(20,0) DEFAULT NULL,
+ `if_______a_b` decimal(20,0) DEFAULT NULL,
+ `if_______b_a` decimal(20,0) DEFAULT NULL,
+ `ifnull___a_b` decimal(20,0) DEFAULT NULL,
+ `ifnull___b_a` decimal(20,0) DEFAULT NULL,
+ `least____a_b` decimal(20,0) DEFAULT NULL,
+ `least____b_a` decimal(20,0) DEFAULT NULL,
+ `greatest_a_b` decimal(20,0) DEFAULT NULL,
+ `greatest_b_a` decimal(20,0) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t2;
+DROP TABLE t1;
+#
+CREATE TABLE t1 (a BIGINT, b BIGINT);
+INSERT INTO t1 VALUES (1,1);
+INSERT INTO t1 VALUES (-9223372036854775808,9223372036854775807);
+SELECT
+a AS ___________a,
+CASE WHEN a IS NOT NULL THEN a END AS case_______a,
+CASE WHEN a IS NOT NULL THEN a ELSE a END AS case_____a_a,
+COALESCE(a) AS coalesce___a,
+COALESCE(a, a) AS coalesce_a_a,
+IF(a IS NULL, a, a) AS if_______a_a,
+IFNULL(a, a) AS ifnull___a_a,
+LEAST(a, a) AS least____a_a,
+GREATEST(a, a) AS greatest_a_a,
+b AS ___________b,
+CASE WHEN a IS NOT NULL THEN b END AS case_______b,
+CASE WHEN a IS NOT NULL THEN b ELSE b END AS case_____b_b,
+COALESCE(b) AS coalesce___b,
+COALESCE(b, b) AS coalesce_b_b,
+IF(a IS NULL, b, b) AS if_______b_b,
+IFNULL(b, b) AS ifnull___b_b,
+LEAST(b, b) AS least____b_b,
+GREATEST(b, b) AS greatest_b_b
+FROM t1;
+Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
+def test t1 t1 a ___________a 8 20 20 Y 32768 0 63
+def case_______a 8 20 20 Y 32896 0 63
+def case_____a_a 8 20 20 Y 32896 0 63
+def coalesce___a 8 20 20 Y 32896 0 63
+def coalesce_a_a 8 20 20 Y 32896 0 63
+def if_______a_a 8 20 20 Y 32896 0 63
+def ifnull___a_a 8 20 20 Y 32896 0 63
+def least____a_a 8 20 20 Y 32896 0 63
+def greatest_a_a 8 20 20 Y 32896 0 63
+def test t1 t1 b ___________b 8 20 19 Y 32768 0 63
+def case_______b 8 20 19 Y 32896 0 63
+def case_____b_b 8 20 19 Y 32896 0 63
+def coalesce___b 8 20 19 Y 32896 0 63
+def coalesce_b_b 8 20 19 Y 32896 0 63
+def if_______b_b 8 20 19 Y 32896 0 63
+def ifnull___b_b 8 20 19 Y 32896 0 63
+def least____b_b 8 20 19 Y 32896 0 63
+def greatest_b_b 8 20 19 Y 32896 0 63
+___________a 1
+case_______a 1
+case_____a_a 1
+coalesce___a 1
+coalesce_a_a 1
+if_______a_a 1
+ifnull___a_a 1
+least____a_a 1
+greatest_a_a 1
+___________b 1
+case_______b 1
+case_____b_b 1
+coalesce___b 1
+coalesce_b_b 1
+if_______b_b 1
+ifnull___b_b 1
+least____b_b 1
+greatest_b_b 1
+___________a -9223372036854775808
+case_______a -9223372036854775808
+case_____a_a -9223372036854775808
+coalesce___a -9223372036854775808
+coalesce_a_a -9223372036854775808
+if_______a_a -9223372036854775808
+ifnull___a_a -9223372036854775808
+least____a_a -9223372036854775808
+greatest_a_a -9223372036854775808
+___________b 9223372036854775807
+case_______b 9223372036854775807
+case_____b_b 9223372036854775807
+coalesce___b 9223372036854775807
+coalesce_b_b 9223372036854775807
+if_______b_b 9223372036854775807
+ifnull___b_b 9223372036854775807
+least____b_b 9223372036854775807
+greatest_b_b 9223372036854775807
+SELECT
+CASE WHEN a IS NOT NULL THEN a ELSE b END AS case_____a_b,
+CASE WHEN a IS NOT NULL THEN b ELSE a END AS case_____b_a,
+COALESCE(a, b) AS coalesce_a_b,
+COALESCE(b, a) AS coalesce_b_a,
+IF(a IS NULL, a, b) AS if_______a_b,
+IF(a IS NULL, b, a) AS if_______b_a,
+IFNULL(a, b) AS ifnull___a_b,
+IFNULL(b, a) AS ifnull___b_a,
+LEAST(a, b) AS least____a_b,
+LEAST(b, a) AS least____b_a,
+GREATEST(a, b) AS greatest_a_b,
+GREATEST(b, a) AS greatest_b_a
+FROM t1;
+Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
+def case_____a_b 8 20 20 Y 32896 0 63
+def case_____b_a 8 20 19 Y 32896 0 63
+def coalesce_a_b 8 20 20 Y 32896 0 63
+def coalesce_b_a 8 20 19 Y 32896 0 63
+def if_______a_b 8 20 19 Y 32896 0 63
+def if_______b_a 8 20 20 Y 32896 0 63
+def ifnull___a_b 8 20 20 Y 32896 0 63
+def ifnull___b_a 8 20 19 Y 32896 0 63
+def least____a_b 8 20 20 Y 32896 0 63
+def least____b_a 8 20 20 Y 32896 0 63
+def greatest_a_b 8 20 19 Y 32896 0 63
+def greatest_b_a 8 20 19 Y 32896 0 63
+case_____a_b 1
+case_____b_a 1
+coalesce_a_b 1
+coalesce_b_a 1
+if_______a_b 1
+if_______b_a 1
+ifnull___a_b 1
+ifnull___b_a 1
+least____a_b 1
+least____b_a 1
+greatest_a_b 1
+greatest_b_a 1
+case_____a_b -9223372036854775808
+case_____b_a 9223372036854775807
+coalesce_a_b -9223372036854775808
+coalesce_b_a 9223372036854775807
+if_______a_b 9223372036854775807
+if_______b_a -9223372036854775808
+ifnull___a_b -9223372036854775808
+ifnull___b_a 9223372036854775807
+least____a_b -9223372036854775808
+least____b_a -9223372036854775808
+greatest_a_b 9223372036854775807
+greatest_b_a 9223372036854775807
+CREATE TABLE t2 AS
+SELECT
+a AS ___________a,
+CASE WHEN a IS NOT NULL THEN a END AS case_______a,
+CASE WHEN a IS NOT NULL THEN a ELSE a END AS case_____a_a,
+COALESCE(a) AS coalesce___a,
+COALESCE(a, a) AS coalesce_a_a,
+IF(a IS NULL, a, a) AS if_______a_a,
+IFNULL(a, a) AS ifnull___a_a,
+LEAST(a, a) AS least____a_a,
+GREATEST(a, a) AS greatest_a_a,
+b AS ___________b,
+CASE WHEN a IS NOT NULL THEN b END AS case_______b,
+CASE WHEN a IS NOT NULL THEN b ELSE b END AS case_____b_b,
+COALESCE(b) AS coalesce___b,
+COALESCE(b, b) AS coalesce_b_b,
+IF(a IS NULL, b, b) AS if_______b_b,
+IFNULL(b, b) AS ifnull___b_b,
+LEAST(b, b) AS least____b_b,
+GREATEST(b, b) AS greatest_b_b
+FROM t1;
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `___________a` bigint(20) DEFAULT NULL,
+ `case_______a` bigint(20) DEFAULT NULL,
+ `case_____a_a` bigint(20) DEFAULT NULL,
+ `coalesce___a` bigint(20) DEFAULT NULL,
+ `coalesce_a_a` bigint(20) DEFAULT NULL,
+ `if_______a_a` bigint(20) DEFAULT NULL,
+ `ifnull___a_a` bigint(20) DEFAULT NULL,
+ `least____a_a` bigint(20) DEFAULT NULL,
+ `greatest_a_a` bigint(20) DEFAULT NULL,
+ `___________b` bigint(20) DEFAULT NULL,
+ `case_______b` bigint(20) DEFAULT NULL,
+ `case_____b_b` bigint(20) DEFAULT NULL,
+ `coalesce___b` bigint(20) DEFAULT NULL,
+ `coalesce_b_b` bigint(20) DEFAULT NULL,
+ `if_______b_b` bigint(20) DEFAULT NULL,
+ `ifnull___b_b` bigint(20) DEFAULT NULL,
+ `least____b_b` bigint(20) DEFAULT NULL,
+ `greatest_b_b` bigint(20) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t2;
+CREATE TABLE t2 AS
+SELECT
+CASE WHEN a IS NOT NULL THEN a ELSE b END AS case_____a_b,
+CASE WHEN a IS NOT NULL THEN b ELSE a END AS case_____b_a,
+COALESCE(a, b) AS coalesce_a_b,
+COALESCE(b, a) AS coalesce_b_a,
+IF(a IS NULL, a, b) AS if_______a_b,
+IF(a IS NULL, b, a) AS if_______b_a,
+IFNULL(a, b) AS ifnull___a_b,
+IFNULL(b, a) AS ifnull___b_a,
+LEAST(a, b) AS least____a_b,
+LEAST(b, a) AS least____b_a,
+GREATEST(a, b) AS greatest_a_b,
+GREATEST(b, a) AS greatest_b_a
+FROM t1;
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `case_____a_b` bigint(20) DEFAULT NULL,
+ `case_____b_a` bigint(20) DEFAULT NULL,
+ `coalesce_a_b` bigint(20) DEFAULT NULL,
+ `coalesce_b_a` bigint(20) DEFAULT NULL,
+ `if_______a_b` bigint(20) DEFAULT NULL,
+ `if_______b_a` bigint(20) DEFAULT NULL,
+ `ifnull___a_b` bigint(20) DEFAULT NULL,
+ `ifnull___b_a` bigint(20) DEFAULT NULL,
+ `least____a_b` bigint(20) DEFAULT NULL,
+ `least____b_a` bigint(20) DEFAULT NULL,
+ `greatest_a_b` bigint(20) DEFAULT NULL,
+ `greatest_b_a` bigint(20) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t2;
+DROP TABLE t1;
+#
+CREATE TABLE t1 (a INT, b BIT(8));
+INSERT INTO t1 VALUES (-2147483648,0x32);
+SELECT
+a AS ___________a,
+CASE WHEN a IS NOT NULL THEN a END AS case_______a,
+CASE WHEN a IS NOT NULL THEN a ELSE a END AS case_____a_a,
+COALESCE(a) AS coalesce___a,
+COALESCE(a, a) AS coalesce_a_a,
+IF(a IS NULL, a, a) AS if_______a_a,
+IFNULL(a, a) AS ifnull___a_a,
+LEAST(a, a) AS least____a_a,
+GREATEST(a, a) AS greatest_a_a,
+b AS ___________b,
+CASE WHEN a IS NOT NULL THEN b END AS case_______b,
+CASE WHEN a IS NOT NULL THEN b ELSE b END AS case_____b_b,
+COALESCE(b) AS coalesce___b,
+COALESCE(b, b) AS coalesce_b_b,
+IF(a IS NULL, b, b) AS if_______b_b,
+IFNULL(b, b) AS ifnull___b_b,
+LEAST(b, b) AS least____b_b,
+GREATEST(b, b) AS greatest_b_b
+FROM t1;
+Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
+def test t1 t1 a ___________a 3 11 11 Y 32768 0 63
+def case_______a 3 11 11 Y 32896 0 63
+def case_____a_a 3 11 11 Y 32896 0 63
+def coalesce___a 3 11 11 Y 32896 0 63
+def coalesce_a_a 3 11 11 Y 32896 0 63
+def if_______a_a 3 11 11 Y 32896 0 63
+def ifnull___a_a 3 11 11 Y 32896 0 63
+def least____a_a 3 11 11 Y 32896 0 63
+def greatest_a_a 3 11 11 Y 32896 0 63
+def test t1 t1 b ___________b 16 8 1 Y 32 0 63
+def case_______b 16 8 2 Y 160 0 63
+def case_____b_b 16 8 2 Y 160 0 63
+def coalesce___b 16 8 2 Y 160 0 63
+def coalesce_b_b 16 8 2 Y 160 0 63
+def if_______b_b 16 8 2 Y 160 0 63
+def ifnull___b_b 16 8 2 Y 160 0 63
+def least____b_b 16 8 2 Y 160 0 63
+def greatest_b_b 16 8 2 Y 160 0 63
+___________a -2147483648
+case_______a -2147483648
+case_____a_a -2147483648
+coalesce___a -2147483648
+coalesce_a_a -2147483648
+if_______a_a -2147483648
+ifnull___a_a -2147483648
+least____a_a -2147483648
+greatest_a_a -2147483648
+___________b 2
+case_______b 50
+case_____b_b 50
+coalesce___b 50
+coalesce_b_b 50
+if_______b_b 50
+ifnull___b_b 50
+least____b_b 50
+greatest_b_b 50
+SELECT
+CASE WHEN a IS NOT NULL THEN a ELSE b END AS case_____a_b,
+CASE WHEN a IS NOT NULL THEN b ELSE a END AS case_____b_a,
+COALESCE(a, b) AS coalesce_a_b,
+COALESCE(b, a) AS coalesce_b_a,
+IF(a IS NULL, a, b) AS if_______a_b,
+IF(a IS NULL, b, a) AS if_______b_a,
+IFNULL(a, b) AS ifnull___a_b,
+IFNULL(b, a) AS ifnull___b_a,
+LEAST(a, b) AS least____a_b,
+LEAST(b, a) AS least____b_a,
+GREATEST(a, b) AS greatest_a_b,
+GREATEST(b, a) AS greatest_b_a
+FROM t1;
+Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
+def case_____a_b 246 11 11 Y 32896 0 63
+def case_____b_a 246 11 2 Y 32896 0 63
+def coalesce_a_b 246 11 11 Y 32896 0 63
+def coalesce_b_a 246 11 2 Y 32896 0 63
+def if_______a_b 246 11 2 Y 32896 0 63
+def if_______b_a 246 11 11 Y 32896 0 63
+def ifnull___a_b 246 11 11 Y 32896 0 63
+def ifnull___b_a 246 11 2 Y 32896 0 63
+def least____a_b 246 11 11 Y 32896 0 63
+def least____b_a 246 11 11 Y 32896 0 63
+def greatest_a_b 246 11 2 Y 32896 0 63
+def greatest_b_a 246 11 2 Y 32896 0 63
+case_____a_b -2147483648
+case_____b_a 50
+coalesce_a_b -2147483648
+coalesce_b_a 50
+if_______a_b 50
+if_______b_a -2147483648
+ifnull___a_b -2147483648
+ifnull___b_a 50
+least____a_b -2147483648
+least____b_a -2147483648
+greatest_a_b 50
+greatest_b_a 50
+CREATE TABLE t2 AS
+SELECT
+a AS ___________a,
+CASE WHEN a IS NOT NULL THEN a END AS case_______a,
+CASE WHEN a IS NOT NULL THEN a ELSE a END AS case_____a_a,
+COALESCE(a) AS coalesce___a,
+COALESCE(a, a) AS coalesce_a_a,
+IF(a IS NULL, a, a) AS if_______a_a,
+IFNULL(a, a) AS ifnull___a_a,
+LEAST(a, a) AS least____a_a,
+GREATEST(a, a) AS greatest_a_a,
+b AS ___________b,
+CASE WHEN a IS NOT NULL THEN b END AS case_______b,
+CASE WHEN a IS NOT NULL THEN b ELSE b END AS case_____b_b,
+COALESCE(b) AS coalesce___b,
+COALESCE(b, b) AS coalesce_b_b,
+IF(a IS NULL, b, b) AS if_______b_b,
+IFNULL(b, b) AS ifnull___b_b,
+LEAST(b, b) AS least____b_b,
+GREATEST(b, b) AS greatest_b_b
+FROM t1;
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `___________a` int(11) DEFAULT NULL,
+ `case_______a` int(11) DEFAULT NULL,
+ `case_____a_a` int(11) DEFAULT NULL,
+ `coalesce___a` int(11) DEFAULT NULL,
+ `coalesce_a_a` int(11) DEFAULT NULL,
+ `if_______a_a` int(11) DEFAULT NULL,
+ `ifnull___a_a` int(11) DEFAULT NULL,
+ `least____a_a` int(11) DEFAULT NULL,
+ `greatest_a_a` int(11) DEFAULT NULL,
+ `___________b` bit(8) DEFAULT NULL,
+ `case_______b` int(8) unsigned DEFAULT NULL,
+ `case_____b_b` int(8) unsigned DEFAULT NULL,
+ `coalesce___b` int(8) unsigned DEFAULT NULL,
+ `coalesce_b_b` int(8) unsigned DEFAULT NULL,
+ `if_______b_b` int(8) unsigned DEFAULT NULL,
+ `ifnull___b_b` bit(8) DEFAULT NULL,
+ `least____b_b` int(8) unsigned DEFAULT NULL,
+ `greatest_b_b` int(8) unsigned DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t2;
+CREATE TABLE t2 AS
+SELECT
+CASE WHEN a IS NOT NULL THEN a ELSE b END AS case_____a_b,
+CASE WHEN a IS NOT NULL THEN b ELSE a END AS case_____b_a,
+COALESCE(a, b) AS coalesce_a_b,
+COALESCE(b, a) AS coalesce_b_a,
+IF(a IS NULL, a, b) AS if_______a_b,
+IF(a IS NULL, b, a) AS if_______b_a,
+IFNULL(a, b) AS ifnull___a_b,
+IFNULL(b, a) AS ifnull___b_a,
+LEAST(a, b) AS least____a_b,
+LEAST(b, a) AS least____b_a,
+GREATEST(a, b) AS greatest_a_b,
+GREATEST(b, a) AS greatest_b_a
+FROM t1;
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `case_____a_b` decimal(10,0) DEFAULT NULL,
+ `case_____b_a` decimal(10,0) DEFAULT NULL,
+ `coalesce_a_b` decimal(10,0) DEFAULT NULL,
+ `coalesce_b_a` decimal(10,0) DEFAULT NULL,
+ `if_______a_b` decimal(10,0) DEFAULT NULL,
+ `if_______b_a` decimal(10,0) DEFAULT NULL,
+ `ifnull___a_b` decimal(10,0) DEFAULT NULL,
+ `ifnull___b_a` decimal(10,0) DEFAULT NULL,
+ `least____a_b` decimal(10,0) DEFAULT NULL,
+ `least____b_a` decimal(10,0) DEFAULT NULL,
+ `greatest_a_b` decimal(10,0) DEFAULT NULL,
+ `greatest_b_a` decimal(10,0) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t2;
+DROP TABLE t1;
+#
+CREATE TABLE t1 (a INT UNSIGNED, b BIT(8));
+INSERT INTO t1 VALUES (4294967295,0x32);
+SELECT
+a AS ___________a,
+CASE WHEN a IS NOT NULL THEN a END AS case_______a,
+CASE WHEN a IS NOT NULL THEN a ELSE a END AS case_____a_a,
+COALESCE(a) AS coalesce___a,
+COALESCE(a, a) AS coalesce_a_a,
+IF(a IS NULL, a, a) AS if_______a_a,
+IFNULL(a, a) AS ifnull___a_a,
+LEAST(a, a) AS least____a_a,
+GREATEST(a, a) AS greatest_a_a,
+b AS ___________b,
+CASE WHEN a IS NOT NULL THEN b END AS case_______b,
+CASE WHEN a IS NOT NULL THEN b ELSE b END AS case_____b_b,
+COALESCE(b) AS coalesce___b,
+COALESCE(b, b) AS coalesce_b_b,
+IF(a IS NULL, b, b) AS if_______b_b,
+IFNULL(b, b) AS ifnull___b_b,
+LEAST(b, b) AS least____b_b,
+GREATEST(b, b) AS greatest_b_b
+FROM t1;
+Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
+def test t1 t1 a ___________a 3 10 10 Y 32800 0 63
+def case_______a 3 10 10 Y 32928 0 63
+def case_____a_a 3 10 10 Y 32928 0 63
+def coalesce___a 3 10 10 Y 32928 0 63
+def coalesce_a_a 3 10 10 Y 32928 0 63
+def if_______a_a 3 10 10 Y 32928 0 63
+def ifnull___a_a 3 10 10 Y 32928 0 63
+def least____a_a 3 10 10 Y 32928 0 63
+def greatest_a_a 3 10 10 Y 32928 0 63
+def test t1 t1 b ___________b 16 8 1 Y 32 0 63
+def case_______b 16 8 2 Y 160 0 63
+def case_____b_b 16 8 2 Y 160 0 63
+def coalesce___b 16 8 2 Y 160 0 63
+def coalesce_b_b 16 8 2 Y 160 0 63
+def if_______b_b 16 8 2 Y 160 0 63
+def ifnull___b_b 16 8 2 Y 160 0 63
+def least____b_b 16 8 2 Y 160 0 63
+def greatest_b_b 16 8 2 Y 160 0 63
+___________a 4294967295
+case_______a 4294967295
+case_____a_a 4294967295
+coalesce___a 4294967295
+coalesce_a_a 4294967295
+if_______a_a 4294967295
+ifnull___a_a 4294967295
+least____a_a 4294967295
+greatest_a_a 4294967295
+___________b 2
+case_______b 50
+case_____b_b 50
+coalesce___b 50
+coalesce_b_b 50
+if_______b_b 50
+ifnull___b_b 50
+least____b_b 50
+greatest_b_b 50
+SELECT
+CASE WHEN a IS NOT NULL THEN a ELSE b END AS case_____a_b,
+CASE WHEN a IS NOT NULL THEN b ELSE a END AS case_____b_a,
+COALESCE(a, b) AS coalesce_a_b,
+COALESCE(b, a) AS coalesce_b_a,
+IF(a IS NULL, a, b) AS if_______a_b,
+IF(a IS NULL, b, a) AS if_______b_a,
+IFNULL(a, b) AS ifnull___a_b,
+IFNULL(b, a) AS ifnull___b_a,
+LEAST(a, b) AS least____a_b,
+LEAST(b, a) AS least____b_a,
+GREATEST(a, b) AS greatest_a_b,
+GREATEST(b, a) AS greatest_b_a
+FROM t1;
+Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
+def case_____a_b 8 10 10 Y 32928 0 63
+def case_____b_a 8 10 2 Y 32928 0 63
+def coalesce_a_b 8 10 10 Y 32928 0 63
+def coalesce_b_a 8 10 2 Y 32928 0 63
+def if_______a_b 8 10 2 Y 32928 0 63
+def if_______b_a 8 10 10 Y 32928 0 63
+def ifnull___a_b 8 10 10 Y 32928 0 63
+def ifnull___b_a 8 10 2 Y 32928 0 63
+def least____a_b 253 10 2 Y 32 0 8
+def least____b_a 253 10 2 Y 32 0 8
+def greatest_a_b 253 10 10 Y 32 0 8
+def greatest_b_a 253 10 10 Y 32 0 8
+case_____a_b 4294967295
+case_____b_a 50
+coalesce_a_b 4294967295
+coalesce_b_a 50
+if_______a_b 50
+if_______b_a 4294967295
+ifnull___a_b 4294967295
+ifnull___b_a 50
+least____a_b 50
+least____b_a 50
+greatest_a_b 4294967295
+greatest_b_a 4294967295
+CREATE TABLE t2 AS
+SELECT
+a AS ___________a,
+CASE WHEN a IS NOT NULL THEN a END AS case_______a,
+CASE WHEN a IS NOT NULL THEN a ELSE a END AS case_____a_a,
+COALESCE(a) AS coalesce___a,
+COALESCE(a, a) AS coalesce_a_a,
+IF(a IS NULL, a, a) AS if_______a_a,
+IFNULL(a, a) AS ifnull___a_a,
+LEAST(a, a) AS least____a_a,
+GREATEST(a, a) AS greatest_a_a,
+b AS ___________b,
+CASE WHEN a IS NOT NULL THEN b END AS case_______b,
+CASE WHEN a IS NOT NULL THEN b ELSE b END AS case_____b_b,
+COALESCE(b) AS coalesce___b,
+COALESCE(b, b) AS coalesce_b_b,
+IF(a IS NULL, b, b) AS if_______b_b,
+IFNULL(b, b) AS ifnull___b_b,
+LEAST(b, b) AS least____b_b,
+GREATEST(b, b) AS greatest_b_b
+FROM t1;
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `___________a` int(10) unsigned DEFAULT NULL,
+ `case_______a` int(10) unsigned DEFAULT NULL,
+ `case_____a_a` int(10) unsigned DEFAULT NULL,
+ `coalesce___a` int(10) unsigned DEFAULT NULL,
+ `coalesce_a_a` int(10) unsigned DEFAULT NULL,
+ `if_______a_a` int(10) unsigned DEFAULT NULL,
+ `ifnull___a_a` int(10) unsigned DEFAULT NULL,
+ `least____a_a` int(10) unsigned DEFAULT NULL,
+ `greatest_a_a` int(10) unsigned DEFAULT NULL,
+ `___________b` bit(8) DEFAULT NULL,
+ `case_______b` int(8) unsigned DEFAULT NULL,
+ `case_____b_b` int(8) unsigned DEFAULT NULL,
+ `coalesce___b` int(8) unsigned DEFAULT NULL,
+ `coalesce_b_b` int(8) unsigned DEFAULT NULL,
+ `if_______b_b` int(8) unsigned DEFAULT NULL,
+ `ifnull___b_b` bit(8) DEFAULT NULL,
+ `least____b_b` int(8) unsigned DEFAULT NULL,
+ `greatest_b_b` int(8) unsigned DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t2;
+CREATE TABLE t2 AS
+SELECT
+CASE WHEN a IS NOT NULL THEN a ELSE b END AS case_____a_b,
+CASE WHEN a IS NOT NULL THEN b ELSE a END AS case_____b_a,
+COALESCE(a, b) AS coalesce_a_b,
+COALESCE(b, a) AS coalesce_b_a,
+IF(a IS NULL, a, b) AS if_______a_b,
+IF(a IS NULL, b, a) AS if_______b_a,
+IFNULL(a, b) AS ifnull___a_b,
+IFNULL(b, a) AS ifnull___b_a,
+LEAST(a, b) AS least____a_b,
+LEAST(b, a) AS least____b_a,
+GREATEST(a, b) AS greatest_a_b,
+GREATEST(b, a) AS greatest_b_a
+FROM t1;
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `case_____a_b` int(10) unsigned DEFAULT NULL,
+ `case_____b_a` int(10) unsigned DEFAULT NULL,
+ `coalesce_a_b` int(10) unsigned DEFAULT NULL,
+ `coalesce_b_a` int(10) unsigned DEFAULT NULL,
+ `if_______a_b` int(10) unsigned DEFAULT NULL,
+ `if_______b_a` int(10) unsigned DEFAULT NULL,
+ `ifnull___a_b` bigint(10) unsigned DEFAULT NULL,
+ `ifnull___b_a` bigint(10) unsigned DEFAULT NULL,
+ `least____a_b` int(10) unsigned DEFAULT NULL,
+ `least____b_a` int(10) unsigned DEFAULT NULL,
+ `greatest_a_b` int(10) unsigned DEFAULT NULL,
+ `greatest_b_a` int(10) unsigned DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t2;
+DROP TABLE t1;
+#
+CREATE TABLE t1 (a BIT(7), b BIT(8));
+INSERT INTO t1 VALUES (0x32,0x32);
+SELECT
+a AS ___________a,
+CASE WHEN a IS NOT NULL THEN a END AS case_______a,
+CASE WHEN a IS NOT NULL THEN a ELSE a END AS case_____a_a,
+COALESCE(a) AS coalesce___a,
+COALESCE(a, a) AS coalesce_a_a,
+IF(a IS NULL, a, a) AS if_______a_a,
+IFNULL(a, a) AS ifnull___a_a,
+LEAST(a, a) AS least____a_a,
+GREATEST(a, a) AS greatest_a_a,
+b AS ___________b,
+CASE WHEN a IS NOT NULL THEN b END AS case_______b,
+CASE WHEN a IS NOT NULL THEN b ELSE b END AS case_____b_b,
+COALESCE(b) AS coalesce___b,
+COALESCE(b, b) AS coalesce_b_b,
+IF(a IS NULL, b, b) AS if_______b_b,
+IFNULL(b, b) AS ifnull___b_b,
+LEAST(b, b) AS least____b_b,
+GREATEST(b, b) AS greatest_b_b
+FROM t1;
+Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
+def test t1 t1 a ___________a 16 7 1 Y 32 0 63
+def case_______a 16 7 2 Y 160 0 63
+def case_____a_a 16 7 2 Y 160 0 63
+def coalesce___a 16 7 2 Y 160 0 63
+def coalesce_a_a 16 7 2 Y 160 0 63
+def if_______a_a 16 7 2 Y 160 0 63
+def ifnull___a_a 16 7 2 Y 160 0 63
+def least____a_a 16 7 2 Y 160 0 63
+def greatest_a_a 16 7 2 Y 160 0 63
+def test t1 t1 b ___________b 16 8 1 Y 32 0 63
+def case_______b 16 8 2 Y 160 0 63
+def case_____b_b 16 8 2 Y 160 0 63
+def coalesce___b 16 8 2 Y 160 0 63
+def coalesce_b_b 16 8 2 Y 160 0 63
+def if_______b_b 16 8 2 Y 160 0 63
+def ifnull___b_b 16 8 2 Y 160 0 63
+def least____b_b 16 8 2 Y 160 0 63
+def greatest_b_b 16 8 2 Y 160 0 63
+___________a 2
+case_______a 50
+case_____a_a 50
+coalesce___a 50
+coalesce_a_a 50
+if_______a_a 50
+ifnull___a_a 50
+least____a_a 50
+greatest_a_a 50
+___________b 2
+case_______b 50
+case_____b_b 50
+coalesce___b 50
+coalesce_b_b 50
+if_______b_b 50
+ifnull___b_b 50
+least____b_b 50
+greatest_b_b 50
+SELECT
+CASE WHEN a IS NOT NULL THEN a ELSE b END AS case_____a_b,
+CASE WHEN a IS NOT NULL THEN b ELSE a END AS case_____b_a,
+COALESCE(a, b) AS coalesce_a_b,
+COALESCE(b, a) AS coalesce_b_a,
+IF(a IS NULL, a, b) AS if_______a_b,
+IF(a IS NULL, b, a) AS if_______b_a,
+IFNULL(a, b) AS ifnull___a_b,
+IFNULL(b, a) AS ifnull___b_a,
+LEAST(a, b) AS least____a_b,
+LEAST(b, a) AS least____b_a,
+GREATEST(a, b) AS greatest_a_b,
+GREATEST(b, a) AS greatest_b_a
+FROM t1;
+Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
+def case_____a_b 16 8 2 Y 160 0 63
+def case_____b_a 16 8 2 Y 160 0 63
+def coalesce_a_b 16 8 2 Y 160 0 63
+def coalesce_b_a 16 8 2 Y 160 0 63
+def if_______a_b 16 8 2 Y 160 0 63
+def if_______b_a 16 8 2 Y 160 0 63
+def ifnull___a_b 16 8 2 Y 160 0 63
+def ifnull___b_a 16 8 2 Y 160 0 63
+def least____a_b 16 8 2 Y 160 0 63
+def least____b_a 16 8 2 Y 160 0 63
+def greatest_a_b 16 8 2 Y 160 0 63
+def greatest_b_a 16 8 2 Y 160 0 63
+case_____a_b 50
+case_____b_a 50
+coalesce_a_b 50
+coalesce_b_a 50
+if_______a_b 50
+if_______b_a 50
+ifnull___a_b 50
+ifnull___b_a 50
+least____a_b 50
+least____b_a 50
+greatest_a_b 50
+greatest_b_a 50
+CREATE TABLE t2 AS
+SELECT
+a AS ___________a,
+CASE WHEN a IS NOT NULL THEN a END AS case_______a,
+CASE WHEN a IS NOT NULL THEN a ELSE a END AS case_____a_a,
+COALESCE(a) AS coalesce___a,
+COALESCE(a, a) AS coalesce_a_a,
+IF(a IS NULL, a, a) AS if_______a_a,
+IFNULL(a, a) AS ifnull___a_a,
+LEAST(a, a) AS least____a_a,
+GREATEST(a, a) AS greatest_a_a,
+b AS ___________b,
+CASE WHEN a IS NOT NULL THEN b END AS case_______b,
+CASE WHEN a IS NOT NULL THEN b ELSE b END AS case_____b_b,
+COALESCE(b) AS coalesce___b,
+COALESCE(b, b) AS coalesce_b_b,
+IF(a IS NULL, b, b) AS if_______b_b,
+IFNULL(b, b) AS ifnull___b_b,
+LEAST(b, b) AS least____b_b,
+GREATEST(b, b) AS greatest_b_b
+FROM t1;
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `___________a` bit(7) DEFAULT NULL,
+ `case_______a` int(7) unsigned DEFAULT NULL,
+ `case_____a_a` int(7) unsigned DEFAULT NULL,
+ `coalesce___a` int(7) unsigned DEFAULT NULL,
+ `coalesce_a_a` int(7) unsigned DEFAULT NULL,
+ `if_______a_a` int(7) unsigned DEFAULT NULL,
+ `ifnull___a_a` bit(7) DEFAULT NULL,
+ `least____a_a` int(7) unsigned DEFAULT NULL,
+ `greatest_a_a` int(7) unsigned DEFAULT NULL,
+ `___________b` bit(8) DEFAULT NULL,
+ `case_______b` int(8) unsigned DEFAULT NULL,
+ `case_____b_b` int(8) unsigned DEFAULT NULL,
+ `coalesce___b` int(8) unsigned DEFAULT NULL,
+ `coalesce_b_b` int(8) unsigned DEFAULT NULL,
+ `if_______b_b` int(8) unsigned DEFAULT NULL,
+ `ifnull___b_b` bit(8) DEFAULT NULL,
+ `least____b_b` int(8) unsigned DEFAULT NULL,
+ `greatest_b_b` int(8) unsigned DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t2;
+CREATE TABLE t2 AS
+SELECT
+CASE WHEN a IS NOT NULL THEN a ELSE b END AS case_____a_b,
+CASE WHEN a IS NOT NULL THEN b ELSE a END AS case_____b_a,
+COALESCE(a, b) AS coalesce_a_b,
+COALESCE(b, a) AS coalesce_b_a,
+IF(a IS NULL, a, b) AS if_______a_b,
+IF(a IS NULL, b, a) AS if_______b_a,
+IFNULL(a, b) AS ifnull___a_b,
+IFNULL(b, a) AS ifnull___b_a,
+LEAST(a, b) AS least____a_b,
+LEAST(b, a) AS least____b_a,
+GREATEST(a, b) AS greatest_a_b,
+GREATEST(b, a) AS greatest_b_a
+FROM t1;
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `case_____a_b` int(8) unsigned DEFAULT NULL,
+ `case_____b_a` int(8) unsigned DEFAULT NULL,
+ `coalesce_a_b` int(8) unsigned DEFAULT NULL,
+ `coalesce_b_a` int(8) unsigned DEFAULT NULL,
+ `if_______a_b` int(8) unsigned DEFAULT NULL,
+ `if_______b_a` int(8) unsigned DEFAULT NULL,
+ `ifnull___a_b` bit(8) DEFAULT NULL,
+ `ifnull___b_a` bit(8) DEFAULT NULL,
+ `least____a_b` int(8) unsigned DEFAULT NULL,
+ `least____b_a` int(8) unsigned DEFAULT NULL,
+ `greatest_a_b` int(8) unsigned DEFAULT NULL,
+ `greatest_b_a` int(8) unsigned DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t2;
+DROP TABLE t1;
+#
+# End of 10.1 tests
+#
diff --git a/mysql-test/t/func_hybrid_type.test b/mysql-test/t/func_hybrid_type.test
new file mode 100644
index 00000000000..55bfead668c
--- /dev/null
+++ b/mysql-test/t/func_hybrid_type.test
@@ -0,0 +1,63 @@
+--echo #
+--echo # Start of 10.1 tests
+--echo #
+
+--echo #
+--echo # MDEV-8865 Wrong field type or metadata for COALESCE(signed_int_column, unsigned_int_column)
+--echo #
+
+--echo #
+CREATE TABLE t1 (a INT, b INT UNSIGNED);
+INSERT INTO t1 VALUES (1,1);
+INSERT INTO t1 VALUES (-1,1);
+INSERT INTO t1 VALUES (-2147483648,4294967295);
+--source include/func_hybrid_type.inc
+DROP TABLE t1;
+
+--echo #
+CREATE TABLE t1 (a INT, b INT);
+INSERT INTO t1 VALUES (1,1);
+INSERT INTO t1 VALUES (-2147483648,2147483647);
+--source include/func_hybrid_type.inc
+DROP TABLE t1;
+
+--echo #
+CREATE TABLE t1 (a BIGINT, b BIGINT UNSIGNED);
+INSERT INTO t1 VALUES (1,1);
+INSERT INTO t1 VALUES (-9223372036854775808,0xFFFFFFFFFFFFFFFF);
+--source include/func_hybrid_type.inc
+DROP TABLE t1;
+
+--echo #
+CREATE TABLE t1 (a BIGINT, b BIGINT);
+INSERT INTO t1 VALUES (1,1);
+INSERT INTO t1 VALUES (-9223372036854775808,9223372036854775807);
+--source include/func_hybrid_type.inc
+DROP TABLE t1;
+
+# Testing BIT(N) types.
+# Using safe BIT(N) type and value to make sure
+# that "file func_hybrid_type.test" tells "ASCII text".
+
+--echo #
+CREATE TABLE t1 (a INT, b BIT(8));
+INSERT INTO t1 VALUES (-2147483648,0x32);
+--source include/func_hybrid_type.inc
+DROP TABLE t1;
+
+--echo #
+CREATE TABLE t1 (a INT UNSIGNED, b BIT(8));
+INSERT INTO t1 VALUES (4294967295,0x32);
+--source include/func_hybrid_type.inc
+DROP TABLE t1;
+
+--echo #
+CREATE TABLE t1 (a BIT(7), b BIT(8));
+INSERT INTO t1 VALUES (0x32,0x32);
+--source include/func_hybrid_type.inc
+DROP TABLE t1;
+
+
+--echo #
+--echo # End of 10.1 tests
+--echo #
diff --git a/sql/CMakeLists.txt b/sql/CMakeLists.txt
index 033a0e04ccf..0d9016c9d6d 100644
--- a/sql/CMakeLists.txt
+++ b/sql/CMakeLists.txt
@@ -135,6 +135,7 @@ SET (SQL_SOURCE
my_apc.cc my_apc.h mf_iocache_encr.cc
my_json_writer.cc my_json_writer.h
rpl_gtid.cc rpl_parallel.cc
+ sql_type.cc sql_type.h
${WSREP_SOURCES}
table_cache.cc encryption.cc
${CMAKE_CURRENT_BINARY_DIR}/sql_builtin.cc
diff --git a/sql/item.h b/sql/item.h
index 4654c4ac1cc..27847274d39 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -28,6 +28,7 @@
#include "unireg.h" // REQUIRED: for other includes
#include "thr_malloc.h" /* sql_calloc */
#include "field.h" /* Derivation */
+#include "sql_type.h"
C_MODE_START
#include <ma_dyncol.h>
@@ -602,7 +603,9 @@ public:
};
-class Item: public Value_source, public Type_std_attributes
+class Item: public Value_source,
+ public Type_std_attributes,
+ public Type_handler
{
Item(const Item &); /* Prevent use of these */
void operator=(Item &);
@@ -752,12 +755,12 @@ public:
virtual bool send(Protocol *protocol, String *str);
virtual bool eq(const Item *, bool binary_cmp) const;
/* result_type() of an item specifies how the value should be returned */
- virtual Item_result result_type() const { return REAL_RESULT; }
+ Item_result result_type() const { return REAL_RESULT; }
/* ... while cmp_type() specifies how it should be compared */
- virtual Item_result cmp_type() const;
+ Item_result cmp_type() const;
virtual Item_result cast_to_int_type() const { return cmp_type(); }
virtual enum_field_types string_field_type() const;
- virtual enum_field_types field_type() const;
+ enum_field_types field_type() const;
virtual enum Type type() const =0;
/*
real_type() is the type of base item. This is same as type() for
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index 0e2802fe794..bad24dc1ec2 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -34,47 +34,6 @@
#include "sql_time.h" // make_truncated_value_warning
#include "sql_base.h" // dynamic_column_error_message
-static Item_result item_store_type(Item_result a, Item *item,
- my_bool unsigned_flag)
-{
- Item_result b= item->result_type();
-
- if (a == STRING_RESULT || b == STRING_RESULT)
- return STRING_RESULT;
- else if (a == REAL_RESULT || b == REAL_RESULT)
- return REAL_RESULT;
- else if (a == DECIMAL_RESULT || b == DECIMAL_RESULT ||
- unsigned_flag != item->unsigned_flag)
- return DECIMAL_RESULT;
- else
- return INT_RESULT;
-}
-
-static void agg_result_type(Item_result *type, Item **items, uint nitems)
-{
- Item **item, **item_end;
- my_bool unsigned_flag= 0;
-
- *type= STRING_RESULT;
- /* Skip beginning NULL items */
- for (item= items, item_end= item + nitems; item < item_end; item++)
- {
- if ((*item)->type() != Item::NULL_ITEM)
- {
- *type= (*item)->result_type();
- unsigned_flag= (*item)->unsigned_flag;
- item++;
- break;
- }
- }
- /* Combine result types. Note: NULL items don't affect the result */
- for (; item < item_end; item++)
- {
- if ((*item)->type() != Item::NULL_ITEM)
- *type= item_store_type(*type, *item, unsigned_flag);
- }
-}
-
/**
find an temporal type (item) that others will be converted to
@@ -185,6 +144,22 @@ static int agg_cmp_type(Item_result *type, Item **items, uint nitems)
@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
@@ -198,14 +173,50 @@ static int agg_cmp_type(Item_result *type, Item **items, uint nitems)
@return aggregated field type.
*/
-enum_field_types agg_field_type(Item **items, uint nitems)
+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 )
- return (enum_field_types)-1;
+ 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++)
- res= Field::field_type_merge(res, items[i]->field_type());
+ {
+ 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;
}
@@ -2261,13 +2272,13 @@ void
Item_func_case_abbreviation2::fix_length_and_dec2(Item **args)
{
uint32 char_length;
- agg_result_type(&cached_result_type, args, 2);
- cached_field_type= agg_field_type(args, 2);
+ set_handler_by_field_type(agg_field_type(args, 2, true));
maybe_null=args[0]->maybe_null || args[1]->maybe_null;
decimals= MY_MAX(args[0]->decimals, args[1]->decimals);
unsigned_flag= args[0]->unsigned_flag && args[1]->unsigned_flag;
- if (cached_result_type == DECIMAL_RESULT || cached_result_type == INT_RESULT)
+ if (Item_func_case_abbreviation2::result_type() == DECIMAL_RESULT ||
+ Item_func_case_abbreviation2::result_type() == INT_RESULT)
{
int len0= args[0]->max_char_length() - args[0]->decimals
- (args[0]->unsigned_flag ? 0 : 1);
@@ -2280,9 +2291,10 @@ Item_func_case_abbreviation2::fix_length_and_dec2(Item **args)
else
char_length= MY_MAX(args[0]->max_char_length(), args[1]->max_char_length());
- switch (cached_result_type) {
+ switch (Item_func_case_abbreviation2::result_type()) {
case STRING_RESULT:
- if (count_string_result_length(cached_field_type, args, 2))
+ if (count_string_result_length(Item_func_case_abbreviation2::field_type(),
+ args, 2))
return;
break;
case DECIMAL_RESULT:
@@ -2459,8 +2471,7 @@ void Item_func_if::fix_after_pullout(st_select_lex *new_parent, Item **ref)
void Item_func_if::cache_type_info(Item *source)
{
Type_std_attributes::set(source);
- cached_field_type= source->field_type();
- cached_result_type= source->result_type();
+ set_handler_by_field_type(source->field_type());
maybe_null= source->maybe_null;
}
@@ -2475,7 +2486,7 @@ Item_func_if::fix_length_and_dec()
maybe_null= true;
// If both arguments are NULL, make resulting type BINARY(0).
if (args[2]->type() == NULL_ITEM)
- cached_field_type= MYSQL_TYPE_STRING;
+ set_handler_by_field_type(MYSQL_TYPE_STRING);
return;
}
if (args[2]->type() == NULL_ITEM)
@@ -2546,8 +2557,7 @@ Item_func_nullif::fix_length_and_dec()
if (!args[2]) // Only false if EOM
return;
- cached_result_type= args[2]->result_type();
- cached_field_type= args[2]->field_type();
+ set_handler_by_field_type(args[2]->field_type());
collation.set(args[2]->collation);
decimals= args[2]->decimals;
unsigned_flag= args[2]->unsigned_flag;
@@ -2961,12 +2971,11 @@ void Item_func_case::fix_length_and_dec()
if (else_expr_num != -1)
agg[nagg++]= args[else_expr_num];
- agg_result_type(&cached_result_type, agg, nagg);
- cached_field_type= agg_field_type(agg, nagg);
+ set_handler_by_field_type(agg_field_type(agg, nagg, true));
- if (cached_result_type == STRING_RESULT)
+ if (Item_func_case::result_type() == STRING_RESULT)
{
- if (count_string_result_length(cached_field_type, agg, nagg))
+ if (count_string_result_length(Item_func_case::field_type(), agg, nagg))
return;
/*
Copy all THEN and ELSE items back to args[] array.
@@ -3295,11 +3304,11 @@ my_decimal *Item_func_coalesce::decimal_op(my_decimal *decimal_value)
void Item_func_coalesce::fix_length_and_dec()
{
- cached_field_type= agg_field_type(args, arg_count);
- agg_result_type(&cached_result_type, args, arg_count);
- switch (cached_result_type) {
+ set_handler_by_field_type(agg_field_type(args, arg_count, true));
+ switch (Item_func_coalesce::result_type()) {
case STRING_RESULT:
- if (count_string_result_length(cached_field_type, args, arg_count))
+ if (count_string_result_length(Item_func_coalesce::field_type(),
+ args, arg_count))
return;
break;
case DECIMAL_RESULT:
diff --git a/sql/item_func.cc b/sql/item_func.cc
index 711d23d9b15..7155f1d4ade 100644
--- a/sql/item_func.cc
+++ b/sql/item_func.cc
@@ -800,28 +800,28 @@ void Item_num_op::fix_length_and_dec(void)
{
count_real_length();
max_length= float_length(decimals);
- cached_result_type= REAL_RESULT;
+ set_handler_by_result_type(REAL_RESULT);
}
else if (r0 == DECIMAL_RESULT || r1 == DECIMAL_RESULT ||
r0 == TIME_RESULT || r1 == TIME_RESULT)
{
- cached_result_type= DECIMAL_RESULT;
+ set_handler_by_result_type(DECIMAL_RESULT);
result_precision();
fix_decimals();
if ((r0 == TIME_RESULT || r1 == TIME_RESULT) && decimals == 0)
- cached_result_type= INT_RESULT;
+ set_handler_by_result_type(INT_RESULT);
}
else
{
DBUG_ASSERT(r0 == INT_RESULT && r1 == INT_RESULT);
- cached_result_type=INT_RESULT;
+ set_handler_by_result_type(INT_RESULT);
result_precision();
decimals= 0;
}
DBUG_PRINT("info", ("Type: %s",
- (cached_result_type == REAL_RESULT ? "REAL_RESULT" :
- cached_result_type == DECIMAL_RESULT ? "DECIMAL_RESULT" :
- cached_result_type == INT_RESULT ? "INT_RESULT" :
+ (result_type() == REAL_RESULT ? "REAL_RESULT" :
+ result_type() == DECIMAL_RESULT ? "DECIMAL_RESULT" :
+ result_type() == INT_RESULT ? "INT_RESULT" :
"--ILLEGAL!!!--")));
DBUG_VOID_RETURN;
}
@@ -837,20 +837,22 @@ void Item_func_num1::fix_length_and_dec()
{
DBUG_ENTER("Item_func_num1::fix_length_and_dec");
DBUG_PRINT("info", ("name %s", func_name()));
- switch (cached_result_type= args[0]->cast_to_int_type()) {
+ // Note, cast_to_int_type() can return TIME_RESULT
+ switch (args[0]->cast_to_int_type()) {
case INT_RESULT:
+ set_handler_by_result_type(INT_RESULT);
max_length= args[0]->max_length;
unsigned_flag= args[0]->unsigned_flag;
break;
case STRING_RESULT:
case REAL_RESULT:
- cached_result_type= REAL_RESULT;
+ set_handler_by_result_type(REAL_RESULT);
decimals= args[0]->decimals; // Preserve NOT_FIXED_DEC
max_length= float_length(decimals);
break;
case TIME_RESULT:
- cached_result_type= DECIMAL_RESULT;
case DECIMAL_RESULT:
+ set_handler_by_result_type(DECIMAL_RESULT);
decimals= args[0]->decimal_scale(); // Do not preserve NOT_FIXED_DEC
max_length= args[0]->max_length;
break;
@@ -858,18 +860,18 @@ void Item_func_num1::fix_length_and_dec()
DBUG_ASSERT(0);
}
DBUG_PRINT("info", ("Type: %s",
- (cached_result_type == REAL_RESULT ? "REAL_RESULT" :
- cached_result_type == DECIMAL_RESULT ? "DECIMAL_RESULT" :
- cached_result_type == INT_RESULT ? "INT_RESULT" :
+ (result_type() == REAL_RESULT ? "REAL_RESULT" :
+ result_type() == DECIMAL_RESULT ? "DECIMAL_RESULT" :
+ result_type() == INT_RESULT ? "INT_RESULT" :
"--ILLEGAL!!!--")));
DBUG_VOID_RETURN;
}
-String *Item_func_hybrid_result_type::val_str(String *str)
+String *Item_func_hybrid_field_type::val_str(String *str)
{
DBUG_ASSERT(fixed == 1);
- switch (cached_result_type) {
+ switch (Item_func_hybrid_field_type::result_type()) {
case DECIMAL_RESULT:
{
my_decimal decimal_value, *val;
@@ -921,10 +923,10 @@ String *Item_func_hybrid_result_type::val_str(String *str)
}
-double Item_func_hybrid_result_type::val_real()
+double Item_func_hybrid_field_type::val_real()
{
DBUG_ASSERT(fixed == 1);
- switch (cached_result_type) {
+ switch (Item_func_hybrid_field_type::result_type()) {
case DECIMAL_RESULT:
{
my_decimal decimal_value, *val;
@@ -966,10 +968,10 @@ double Item_func_hybrid_result_type::val_real()
}
-longlong Item_func_hybrid_result_type::val_int()
+longlong Item_func_hybrid_field_type::val_int()
{
DBUG_ASSERT(fixed == 1);
- switch (cached_result_type) {
+ switch (Item_func_hybrid_field_type::result_type()) {
case DECIMAL_RESULT:
{
my_decimal decimal_value, *val;
@@ -1008,11 +1010,11 @@ longlong Item_func_hybrid_result_type::val_int()
}
-my_decimal *Item_func_hybrid_result_type::val_decimal(my_decimal *decimal_value)
+my_decimal *Item_func_hybrid_field_type::val_decimal(my_decimal *decimal_value)
{
my_decimal *val= decimal_value;
DBUG_ASSERT(fixed == 1);
- switch (cached_result_type) {
+ switch (Item_func_hybrid_field_type::result_type()) {
case DECIMAL_RESULT:
val= decimal_op(decimal_value);
break;
@@ -1054,11 +1056,11 @@ my_decimal *Item_func_hybrid_result_type::val_decimal(my_decimal *decimal_value)
}
-bool Item_func_hybrid_result_type::get_date(MYSQL_TIME *ltime,
+bool Item_func_hybrid_field_type::get_date(MYSQL_TIME *ltime,
ulonglong fuzzydate)
{
DBUG_ASSERT(fixed == 1);
- switch (cached_result_type) {
+ switch (Item_func_hybrid_field_type::result_type()) {
case DECIMAL_RESULT:
{
my_decimal value, *res;
@@ -1812,7 +1814,7 @@ void Item_func_div::fix_length_and_dec()
DBUG_ENTER("Item_func_div::fix_length_and_dec");
prec_increment= current_thd->variables.div_precincrement;
Item_num_op::fix_length_and_dec();
- switch (cached_result_type) {
+ switch (Item_func_div::result_type()) {
case REAL_RESULT:
{
decimals=MY_MAX(args[0]->decimals,args[1]->decimals)+prec_increment;
@@ -1828,7 +1830,7 @@ void Item_func_div::fix_length_and_dec()
break;
}
case INT_RESULT:
- cached_result_type= DECIMAL_RESULT;
+ set_handler_by_result_type(DECIMAL_RESULT);
DBUG_PRINT("info", ("Type changed: DECIMAL_RESULT"));
result_precision();
break;
@@ -2075,7 +2077,7 @@ void Item_func_neg::fix_length_and_dec()
Use val() to get value as arg_type doesn't mean that item is
Item_int or Item_float due to existence of Item_param.
*/
- if (cached_result_type == INT_RESULT && args[0]->const_item())
+ if (Item_func_neg::result_type() == INT_RESULT && args[0]->const_item())
{
longlong val= args[0]->val_int();
if ((ulonglong) val >= (ulonglong) LONGLONG_MIN &&
@@ -2086,7 +2088,7 @@ void Item_func_neg::fix_length_and_dec()
Ensure that result is converted to DECIMAL, as longlong can't hold
the negated number
*/
- cached_result_type= DECIMAL_RESULT;
+ set_handler_by_result_type(DECIMAL_RESULT);
DBUG_PRINT("info", ("Type changed: DECIMAL_RESULT"));
}
}
@@ -2388,11 +2390,12 @@ void Item_func_int_val::fix_length_and_dec()
set_if_smaller(max_length,tmp);
decimals= 0;
- switch (cached_result_type= args[0]->cast_to_int_type())
+ // Note, cast_to_int_type() can return TIME_RESULT
+ switch (args[0]->cast_to_int_type())
{
case STRING_RESULT:
case REAL_RESULT:
- cached_result_type= REAL_RESULT;
+ set_handler_by_result_type(REAL_RESULT);
max_length= float_length(decimals);
break;
case INT_RESULT:
@@ -2405,21 +2408,21 @@ void Item_func_int_val::fix_length_and_dec()
if ((args[0]->max_length - args[0]->decimals) >=
(DECIMAL_LONGLONG_DIGITS - 2))
{
- cached_result_type= DECIMAL_RESULT;
+ set_handler_by_result_type(DECIMAL_RESULT);
}
else
{
unsigned_flag= args[0]->unsigned_flag;
- cached_result_type= INT_RESULT;
+ set_handler_by_result_type(INT_RESULT);
}
break;
case ROW_RESULT:
DBUG_ASSERT(0);
}
DBUG_PRINT("info", ("Type: %s",
- (cached_result_type == REAL_RESULT ? "REAL_RESULT" :
- cached_result_type == DECIMAL_RESULT ? "DECIMAL_RESULT" :
- cached_result_type == INT_RESULT ? "INT_RESULT" :
+ (result_type() == REAL_RESULT ? "REAL_RESULT" :
+ result_type() == DECIMAL_RESULT ? "DECIMAL_RESULT" :
+ result_type() == INT_RESULT ? "INT_RESULT" :
"--ILLEGAL!!!--")));
DBUG_VOID_RETURN;
@@ -2534,10 +2537,10 @@ void Item_func_round::fix_length_and_dec()
if (args[0]->result_type() == DECIMAL_RESULT)
{
max_length++;
- cached_result_type= DECIMAL_RESULT;
+ set_handler_by_result_type(DECIMAL_RESULT);
}
else
- cached_result_type= REAL_RESULT;
+ set_handler_by_result_type(REAL_RESULT);
return;
}
@@ -2555,14 +2558,14 @@ void Item_func_round::fix_length_and_dec()
{
decimals= MY_MIN(decimals_to_set, NOT_FIXED_DEC);
max_length= float_length(decimals);
- cached_result_type= REAL_RESULT;
+ set_handler_by_result_type(REAL_RESULT);
return;
}
switch (args[0]->result_type()) {
case REAL_RESULT:
case STRING_RESULT:
- cached_result_type= REAL_RESULT;
+ set_handler_by_result_type(REAL_RESULT);
decimals= MY_MIN(decimals_to_set, NOT_FIXED_DEC);
max_length= float_length(decimals);
break;
@@ -2573,14 +2576,14 @@ void Item_func_round::fix_length_and_dec()
!val1_unsigned);
max_length= args[0]->max_length + length_can_increase;
/* Here we can keep INT_RESULT */
- cached_result_type= INT_RESULT;
+ set_handler_by_result_type(INT_RESULT);
decimals= 0;
break;
}
/* fall through */
case DECIMAL_RESULT:
{
- cached_result_type= DECIMAL_RESULT;
+ set_handler_by_result_type(DECIMAL_RESULT);
decimals_to_set= MY_MIN(DECIMAL_MAX_SCALE, decimals_to_set);
int decimals_delta= args[0]->decimals - decimals_to_set;
int precision= args[0]->decimal_precision();
@@ -2884,7 +2887,7 @@ void Item_func_min_max::fix_length_and_dec()
set_if_smaller(decimals, TIME_SECOND_PART_DIGITS);
}
else
- cached_field_type= agg_field_type(args, arg_count);
+ cached_field_type= agg_field_type(args, arg_count, false);
}
diff --git a/sql/item_func.h b/sql/item_func.h
index ea8a5a88b35..74a18c1edfe 100644
--- a/sql/item_func.h
+++ b/sql/item_func.h
@@ -373,29 +373,32 @@ public:
};
-class Item_func_hybrid_result_type: public Item_func
+class Item_func_hybrid_field_type: public Item_func,
+ public Type_handler_hybrid_field_type
{
-protected:
- Item_result cached_result_type;
-
public:
- Item_func_hybrid_result_type(THD *thd):
- Item_func(thd), cached_result_type(REAL_RESULT)
+ Item_func_hybrid_field_type(THD *thd):
+ Item_func(thd)
{ collation.set_numeric(); }
- Item_func_hybrid_result_type(THD *thd, Item *a):
- Item_func(thd, a), cached_result_type(REAL_RESULT)
+ Item_func_hybrid_field_type(THD *thd, Item *a):
+ Item_func(thd, a)
{ collation.set_numeric(); }
- Item_func_hybrid_result_type(THD *thd, Item *a, Item *b):
- Item_func(thd, a, b), cached_result_type(REAL_RESULT)
+ Item_func_hybrid_field_type(THD *thd, Item *a, Item *b):
+ Item_func(thd, a, b)
{ collation.set_numeric(); }
- Item_func_hybrid_result_type(THD *thd, Item *a, Item *b, Item *c):
- Item_func(thd, a, b, c), cached_result_type(REAL_RESULT)
+ Item_func_hybrid_field_type(THD *thd, Item *a, Item *b, Item *c):
+ Item_func(thd, a, b, c)
{ collation.set_numeric(); }
- Item_func_hybrid_result_type(THD *thd, List<Item> &list):
- Item_func(thd, list), cached_result_type(REAL_RESULT)
+ Item_func_hybrid_field_type(THD *thd, List<Item> &list):
+ Item_func(thd, list)
{ collation.set_numeric(); }
- enum Item_result result_type () const { return cached_result_type; }
+ enum_field_types field_type() const
+ { return Type_handler_hybrid_field_type::field_type(); }
+ enum Item_result result_type () const
+ { return Type_handler_hybrid_field_type::result_type(); }
+ enum Item_result cmp_type () const
+ { return Type_handler_hybrid_field_type::cmp_type(); }
double val_real();
longlong val_int();
@@ -449,33 +452,7 @@ public:
};
-
-class Item_func_hybrid_field_type :public Item_func_hybrid_result_type
-{
-protected:
- enum_field_types cached_field_type;
-public:
- Item_func_hybrid_field_type(THD *thd):
- Item_func_hybrid_result_type(thd), cached_field_type(MYSQL_TYPE_DOUBLE)
- {}
- Item_func_hybrid_field_type(THD *thd, Item *a, Item *b):
- Item_func_hybrid_result_type(thd, a, b),
- cached_field_type(MYSQL_TYPE_DOUBLE)
- {}
- Item_func_hybrid_field_type(THD *thd, Item *a, Item *b, Item *c):
- Item_func_hybrid_result_type(thd, a, b, c),
- cached_field_type(MYSQL_TYPE_DOUBLE)
- {}
- Item_func_hybrid_field_type(THD *thd, List<Item> &list):
- Item_func_hybrid_result_type(thd, list),
- cached_field_type(MYSQL_TYPE_DOUBLE)
- {}
- enum_field_types field_type() const { return cached_field_type; }
-};
-
-
-
-class Item_func_numhybrid: public Item_func_hybrid_result_type
+class Item_func_numhybrid: public Item_func_hybrid_field_type
{
protected:
@@ -487,18 +464,18 @@ protected:
}
public:
- Item_func_numhybrid(THD *thd): Item_func_hybrid_result_type(thd)
+ Item_func_numhybrid(THD *thd): Item_func_hybrid_field_type(thd)
{ }
- Item_func_numhybrid(THD *thd, Item *a): Item_func_hybrid_result_type(thd, a)
+ Item_func_numhybrid(THD *thd, Item *a): Item_func_hybrid_field_type(thd, a)
{ }
Item_func_numhybrid(THD *thd, Item *a, Item *b):
- Item_func_hybrid_result_type(thd, a, b)
+ Item_func_hybrid_field_type(thd, a, b)
{ }
Item_func_numhybrid(THD *thd, Item *a, Item *b, Item *c):
- Item_func_hybrid_result_type(thd, a, b, c)
+ Item_func_hybrid_field_type(thd, a, b, c)
{ }
Item_func_numhybrid(THD *thd, List<Item> &list):
- Item_func_hybrid_result_type(thd, list)
+ Item_func_hybrid_field_type(thd, list)
{ }
String *str_op(String *str) { DBUG_ASSERT(0); return 0; }
bool date_op(MYSQL_TIME *ltime, uint fuzzydate) { DBUG_ASSERT(0); return true; }
@@ -2169,7 +2146,8 @@ 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);
+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/item_timefunc.h b/sql/item_timefunc.h
index edc77038f89..2d02a55b16a 100644
--- a/sql/item_timefunc.h
+++ b/sql/item_timefunc.h
@@ -421,7 +421,7 @@ public:
set_if_smaller(decimals, TIME_SECOND_PART_DIGITS);
max_length=17 + (decimals ? decimals + 1 : 0);
maybe_null= true;
- cached_result_type= decimals ? DECIMAL_RESULT : INT_RESULT;
+ set_handler_by_result_type(decimals ? DECIMAL_RESULT : INT_RESULT);
}
double real_op() { DBUG_ASSERT(0); return 0; }
String *str_op(String *str) { DBUG_ASSERT(0); return 0; }
diff --git a/sql/sql_type.cc b/sql/sql_type.cc
new file mode 100644
index 00000000000..ce0d9852119
--- /dev/null
+++ b/sql/sql_type.cc
@@ -0,0 +1,106 @@
+/*
+ Copyright (c) 2015 MariaDB Foundation.
+
+ This program is free software; you can redistribute it and/or modify
+ it under the terms of the GNU General Public License as published by
+ the Free Software Foundation; version 2 of the License.
+
+ This program is distributed in the hope that it will be useful,
+ but WITHOUT ANY WARRANTY; without even the implied warranty of
+ MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+ GNU General Public License for more details.
+
+ You should have received a copy of the GNU General Public License
+ along with this program; if not, write to the Free Software
+ Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA */
+
+#include "sql_type.h"
+
+static Type_handler_tiny type_handler_tiny;
+static Type_handler_short type_handler_short;
+static Type_handler_long type_handler_long;
+static Type_handler_longlong type_handler_longlong;
+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;
+static Type_handler_date type_handler_date;
+static Type_handler_datetime type_handler_datetime;
+static Type_handler_timestamp type_handler_timestamp;
+static Type_handler_olddecimal type_handler_olddecimal;
+static Type_handler_newdecimal type_handler_newdecimal;
+static Type_handler_null type_handler_null;
+static Type_handler_string type_handler_string;
+static Type_handler_varchar type_handler_varchar;
+static Type_handler_tiny_blob type_handler_tiny_blob;
+static Type_handler_medium_blob type_handler_medium_blob;
+static Type_handler_long_blob type_handler_long_blob;
+static Type_handler_blob type_handler_blob;
+static Type_handler_geometry type_handler_geometry;
+
+
+Type_handler_hybrid_field_type::Type_handler_hybrid_field_type()
+ :m_type_handler(&type_handler_double)
+{
+}
+
+
+const Type_handler *
+Type_handler_hybrid_field_type::get_handler_by_result_type(Item_result type)
+ const
+{
+ switch (type) {
+ case REAL_RESULT: return &type_handler_double;
+ case INT_RESULT: return &type_handler_longlong;
+ case DECIMAL_RESULT: return &type_handler_newdecimal;
+ case STRING_RESULT: return &type_handler_string;
+ case TIME_RESULT:
+ case ROW_RESULT:
+ DBUG_ASSERT(0);
+ }
+ return &type_handler_string;
+}
+
+
+const Type_handler *
+Type_handler_hybrid_field_type::get_handler_by_field_type(enum_field_types type)
+ const
+{
+ switch (type) {
+ case MYSQL_TYPE_DECIMAL: return &type_handler_olddecimal;
+ case MYSQL_TYPE_NEWDECIMAL: return &type_handler_newdecimal;
+ case MYSQL_TYPE_TINY: return &type_handler_tiny;
+ case MYSQL_TYPE_SHORT: return &type_handler_short;
+ case MYSQL_TYPE_LONG: return &type_handler_long;
+ case MYSQL_TYPE_LONGLONG: return &type_handler_longlong;
+ case MYSQL_TYPE_INT24: return &type_handler_int24;
+ case MYSQL_TYPE_YEAR: return &type_handler_year;
+ case MYSQL_TYPE_BIT: return &type_handler_bit;
+ case MYSQL_TYPE_FLOAT: return &type_handler_float;
+ case MYSQL_TYPE_DOUBLE: return &type_handler_double;
+ case MYSQL_TYPE_NULL: return &type_handler_null;
+ case MYSQL_TYPE_VARCHAR: return &type_handler_varchar;
+ case MYSQL_TYPE_TINY_BLOB: return &type_handler_tiny_blob;
+ case MYSQL_TYPE_MEDIUM_BLOB: return &type_handler_medium_blob;
+ case MYSQL_TYPE_LONG_BLOB: return &type_handler_long_blob;
+ case MYSQL_TYPE_BLOB: return &type_handler_blob;
+ case MYSQL_TYPE_VAR_STRING: return &type_handler_varchar; // Map to VARCHAR
+ case MYSQL_TYPE_STRING: return &type_handler_string;
+ case MYSQL_TYPE_ENUM: return &type_handler_varchar; // Map to VARCHAR
+ case MYSQL_TYPE_SET: return &type_handler_varchar; // Map to VARCHAR
+ case MYSQL_TYPE_GEOMETRY: return &type_handler_geometry;
+ case MYSQL_TYPE_TIMESTAMP: return &type_handler_timestamp;
+ case MYSQL_TYPE_TIMESTAMP2: return &type_handler_timestamp;
+ case MYSQL_TYPE_DATE: return &type_handler_date;
+ case MYSQL_TYPE_TIME: return &type_handler_time;
+ case MYSQL_TYPE_TIME2: return &type_handler_time;
+ case MYSQL_TYPE_DATETIME: return &type_handler_datetime;
+ case MYSQL_TYPE_DATETIME2: return &type_handler_datetime;
+ case MYSQL_TYPE_NEWDATE: return &type_handler_date;
+ };
+ DBUG_ASSERT(0);
+ return &type_handler_string;
+}
+
diff --git a/sql/sql_type.h b/sql/sql_type.h
new file mode 100644
index 00000000000..13ddf4a83f8
--- /dev/null
+++ b/sql/sql_type.h
@@ -0,0 +1,286 @@
+#ifndef SQL_TYPE_H_INCLUDED
+#define SQL_TYPE_H_INCLUDED
+/*
+ Copyright (c) 2015 MariaDB Foundation.
+
+ This program is free software; you can redistribute it and/or modify
+ it under the terms of the GNU General Public License as published by
+ the Free Software Foundation; version 2 of the License.
+
+ This program is distributed in the hope that it will be useful,
+ but WITHOUT ANY WARRANTY; without even the implied warranty of
+ MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+ GNU General Public License for more details.
+
+ You should have received a copy of the GNU General Public License
+ along with this program; if not, write to the Free Software
+ Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA */
+
+#ifdef USE_PRAGMA_INTERFACE
+#pragma interface /* gcc class implementation */
+#endif
+
+
+#include "mysqld.h"
+
+class Type_handler
+{
+public:
+ virtual enum_field_types field_type() const= 0;
+ virtual Item_result result_type() const= 0;
+ virtual Item_result cmp_type() const= 0;
+};
+
+/*** Abstract classes for every XXX_RESULT */
+
+class Type_handler_real_result: public Type_handler
+{
+public:
+ Item_result result_type() const { return REAL_RESULT; }
+ Item_result cmp_type() const { return REAL_RESULT; }
+};
+
+
+class Type_handler_decimal_result: public Type_handler
+{
+public:
+ Item_result result_type() const { return DECIMAL_RESULT; }
+ Item_result cmp_type() const { return DECIMAL_RESULT; }
+};
+
+
+class Type_handler_int_result: public Type_handler
+{
+public:
+ Item_result result_type() const { return INT_RESULT; }
+ Item_result cmp_type() const { return INT_RESULT; }
+};
+
+
+class Type_handler_temporal_result: public Type_handler
+{
+public:
+ Item_result result_type() const { return STRING_RESULT; }
+ Item_result cmp_type() const { return TIME_RESULT; }
+};
+
+
+class Type_handler_string_result: public Type_handler
+{
+public:
+ Item_result result_type() const { return STRING_RESULT; }
+ Item_result cmp_type() const { return STRING_RESULT; }
+};
+
+
+/***
+ Instantiable classes for every MYSQL_TYPE_XXX
+
+ There are no Type_handler_xxx for the following types:
+ - MYSQL_TYPE_VAR_STRING (old VARCHAR) - mapped to MYSQL_TYPE_VARSTRING
+ - MYSQL_TYPE_ENUM - mapped to MYSQL_TYPE_VARSTRING
+ - MYSQL_TYPE_SET: - mapped to MYSQL_TYPE_VARSTRING
+
+ because the functionality that currently uses Type_handler
+ (e.g. hybrid type functions) does not need to distinguish between
+ these types and VARCHAR.
+ For example:
+ CREATE TABLE t2 AS SELECT COALESCE(enum_column) FROM t1;
+ creates a VARCHAR column.
+
+ There most likely be Type_handler_enum and Type_handler_set later,
+ when the Type_handler infrastructure gets used in more pieces of the code.
+*/
+
+
+class Type_handler_tiny: public Type_handler_int_result
+{
+public:
+ enum_field_types field_type() const { return MYSQL_TYPE_TINY; }
+};
+
+
+class Type_handler_short: public Type_handler_int_result
+{
+public:
+ enum_field_types field_type() const { return MYSQL_TYPE_SHORT; }
+};
+
+
+class Type_handler_long: public Type_handler_int_result
+{
+public:
+ enum_field_types field_type() const { return MYSQL_TYPE_LONG; }
+};
+
+
+class Type_handler_longlong: public Type_handler_int_result
+{
+public:
+ enum_field_types field_type() const { return MYSQL_TYPE_LONGLONG; }
+};
+
+
+class Type_handler_int24: public Type_handler_int_result
+{
+public:
+ enum_field_types field_type() const { return MYSQL_TYPE_INT24; }
+};
+
+
+class Type_handler_year: public Type_handler_int_result
+{
+public:
+ enum_field_types field_type() const { return MYSQL_TYPE_YEAR; }
+};
+
+
+class Type_handler_bit: public Type_handler_int_result
+{
+public:
+ enum_field_types field_type() const { return MYSQL_TYPE_BIT; }
+};
+
+
+class Type_handler_float: public Type_handler_real_result
+{
+public:
+ enum_field_types field_type() const { return MYSQL_TYPE_FLOAT; }
+};
+
+
+class Type_handler_double: public Type_handler_real_result
+{
+public:
+ enum_field_types field_type() const { return MYSQL_TYPE_DOUBLE; }
+};
+
+
+class Type_handler_time: public Type_handler_temporal_result
+{
+public:
+ enum_field_types field_type() const { return MYSQL_TYPE_TIME; }
+};
+
+
+class Type_handler_date: public Type_handler_temporal_result
+{
+public:
+ enum_field_types field_type() const { return MYSQL_TYPE_DATE; }
+};
+
+
+class Type_handler_datetime: public Type_handler_temporal_result
+{
+public:
+ enum_field_types field_type() const { return MYSQL_TYPE_DATETIME; }
+};
+
+
+class Type_handler_timestamp: public Type_handler_temporal_result
+{
+public:
+ enum_field_types field_type() const { return MYSQL_TYPE_TIMESTAMP; }
+};
+
+
+class Type_handler_olddecimal: public Type_handler_decimal_result
+{
+public:
+ enum_field_types field_type() const { return MYSQL_TYPE_DECIMAL; }
+};
+
+
+class Type_handler_newdecimal: public Type_handler_decimal_result
+{
+public:
+ enum_field_types field_type() const { return MYSQL_TYPE_NEWDECIMAL; }
+};
+
+
+class Type_handler_null: public Type_handler_string_result
+{
+public:
+ enum_field_types field_type() const { return MYSQL_TYPE_NULL; }
+};
+
+
+class Type_handler_string: public Type_handler_string_result
+{
+public:
+ enum_field_types field_type() const { return MYSQL_TYPE_STRING; }
+};
+
+
+class Type_handler_varchar: public Type_handler_string_result
+{
+public:
+ enum_field_types field_type() const { return MYSQL_TYPE_VARCHAR; }
+};
+
+
+class Type_handler_tiny_blob: public Type_handler_string_result
+{
+public:
+ enum_field_types field_type() const { return MYSQL_TYPE_TINY_BLOB; }
+};
+
+
+class Type_handler_medium_blob: public Type_handler_string_result
+{
+public:
+ enum_field_types field_type() const { return MYSQL_TYPE_MEDIUM_BLOB; }
+};
+
+
+class Type_handler_long_blob: public Type_handler_string_result
+{
+public:
+ enum_field_types field_type() const { return MYSQL_TYPE_LONG_BLOB; }
+};
+
+
+class Type_handler_blob: public Type_handler_string_result
+{
+public:
+ enum_field_types field_type() const { return MYSQL_TYPE_BLOB; }
+};
+
+
+class Type_handler_geometry: public Type_handler_string_result
+{
+public:
+ enum_field_types field_type() const { return MYSQL_TYPE_GEOMETRY; }
+};
+
+
+/**
+ A handler for hybrid type functions, e.g.
+ COALESCE(), IF(), IFNULL(), NULLIF(), CASE,
+ numeric operators,
+ UNIX_TIMESTAMP(), TIME_TO_SEC().
+
+ Makes sure that field_type(), cmp_type() and result_type()
+ are always in sync to each other for hybrid functions.
+*/
+class Type_handler_hybrid_field_type: public Type_handler
+{
+ const Type_handler *m_type_handler;
+ const Type_handler *get_handler_by_result_type(Item_result type) const;
+ const Type_handler *get_handler_by_field_type(enum_field_types type) const;
+public:
+ Type_handler_hybrid_field_type();
+ enum_field_types field_type() const { return m_type_handler->field_type(); }
+ Item_result result_type() const { return m_type_handler->result_type(); }
+ Item_result cmp_type() const { return m_type_handler->cmp_type(); }
+ const Type_handler *set_handler_by_result_type(Item_result type)
+ {
+ return (m_type_handler= get_handler_by_result_type(type));
+ }
+ const Type_handler *set_handler_by_field_type(enum_field_types type)
+ {
+ return (m_type_handler= get_handler_by_field_type(type));
+ }
+};
+
+#endif /* SQL_TYPE_H_INCLUDED */
View Lorry Controller Status