• Alexander Barkov's avatar
    WL#2649 Number-to-string conversions · 702166bc
    Alexander Barkov authored
    added:
      include/ctype_numconv.inc
      mysql-test/include/ctype_numconv.inc
      mysql-test/r/ctype_binary.result
      mysql-test/t/ctype_binary.test
      Adding tests
    
    modified:
    
      mysql-test/r/bigint.result
      mysql-test/r/case.result
      mysql-test/r/create.result
      mysql-test/r/ctype_cp1251.result
      mysql-test/r/ctype_latin1.result
      mysql-test/r/ctype_ucs.result
      mysql-test/r/func_gconcat.result
      mysql-test/r/func_str.result
      mysql-test/r/metadata.result
      mysql-test/r/ps_1general.result
      mysql-test/r/ps_2myisam.result
      mysql-test/r/ps_3innodb.result
      mysql-test/r/ps_4heap.result
      mysql-test/r/ps_5merge.result
      mysql-test/r/show_check.result
      mysql-test/r/type_datetime.result
      mysql-test/r/type_ranges.result
      mysql-test/r/union.result
      mysql-test/suite/ndb/r/ps_7ndb.result
      mysql-test/t/ctype_cp1251.test
      mysql-test/t/ctype_latin1.test
      mysql-test/t/ctype_ucs.test
      mysql-test/t/func_str.test
        Fixing tests
    
    
      @ sql/field.cc
         - Return str result using my_charset_numeric.
         - Using real multi-byte aware str_to_XXX functions
           to handle tricky charset values propely (e.g. UCS2)
      @ sql/field.h
         - Changing derivation of non-string field types to DERIVATION_NUMERIC.
         - Changing binary() for numeric/datetime fields to always
         return TRUE even if charset is not my_charset_bin. We need
         this to keep ha_base_keytype() return HA_KEYTYPE_BINARY.
         - Adding BINARY_FLAG into some fields, because it's not
         being set automatically anymore with
         "my_charset_bin to my_charset_numeric" change.
        - Changing derivation for numeric/datetime datatypes to a weaker
          value, to make "SELECT concat('string', field)" use character
          set of the string literal for the result of the function.
      @ sql/item.cc
         - Implementing generic val_str_ascii().
         - Using max_char_length() instead of direct read of max_length
           to make "tricky" charsets like UCS2 work.
           NOTE: in the future we'll possibly remove all direct reads of max_length
         - Fixing Item_num::safe_charset_converter().
           Previously it alligned binary string to
           character string (for example by adding leading 0x00
           when doing binary->UCS2 conversion). Now it just
           converts from my_charset_numbner to "tocs".
         - Using val_str_ascii() in Item::get_time() to make UCS2 arguments work.
         - Other misc changes
      @ sql/item.h
         - Changing MY_COLL_CMP_CONV and MY_COLL_ALLOW_CONV to
           bit operations instead of hard-coded bit masks.
         - Addding new method DTCollation.set_numeric().
         - Adding new methods to Item.
         - Adding helper functions to make code look nicer:
           agg_item_charsets_for_string_result()
           agg_item_charsets_for_comparison()
         - Changing charset for Item_num-derived items
           from my_charset_bin to my_charset_numeric
           (which is an alias for latin1).
      @ sql/item_cmpfunc.cc
         - Using new helper functions
         - Other misc changes
      @ sql/item_cmpfunc.h
         - Fixing strcmp() to return max_length=2.
           Previously it returned 1, which was wrong,
           because it did not fit '-1'.
      @ sql/item_func.cc
         - Using new helper functions
         - Other minor changes
      @ sql/item_func.h
         - Removing unused functions
         - Adding helper functions
           agg_arg_charsets_for_string_result()
           agg_arg_charsets_for_comparison()
         - Adding set_numeric() into constructors of numeric items.
         - Using fix_length_and_charset() and fix_char_length()
           instead of direct write to max_length.
      @ sql/item_geofunc.cc
         - Changing class for Item_func_geometry_type and
           Item_func_as_wkt from Item_str_func to
           Item_str_ascii_func, to make them return UCS2 result
           properly (when character_set_connection=ucs2).
      @ sql/item_geofunc.h
         - Changing class for Item_func_geometry_type and
           Item_func_as_wkt from Item_str_func to
           Item_str_ascii_func, to make them return UCS2 result
           properly (when @@character_set_connection=ucs2).
      @ sql/item_strfunc.cc
         - Implementing Item_str_func::val_str().
         - Renaming val_str to val_str_ascii for some items,
           to make them work with UCS2 properly.
         - Using new helper functions
         - All single-argument functions that expect string
           result now call this method:
           agg_arg_charsets_for_string_result(collation, args, 1);
           This enables character set conversion to @@character_set_connection
           in case of pure numeric input.
      @ sql/item_strfunc.h
         - Introducing Item_str_ascii_func - for functions
           which return pure ASCII data, for performance purposes,
           as well as for the cases when the old implementation
           of val_str() was heavily 8-bit oriented and implementing
           a UCS2-aware version is tricky.
      @ sql/item_sum.cc
         - Using new helper functions.
      @ sql/item_timefunc.cc
         - Using my_charset_numeric instead of my_charset_bin.
         - Using fix_char_length(), fix_length_and_charset()
           and fix_length_and_charset_datetime()
           instead of direct write to max_length.
         - Using tricky-charset aware function str_to_time_with_warn()
      @ sql/item_timefunc.h
         - Using new helper functions for charset and length initialization.
         - Changing base class for Item_func_get_format() to make
           it return UCS2 properly (when character_set_connection=ucs2).
      @ sql/item_xmlfunc.cc
         - Using new helper function
      @ sql/my_decimal.cc
         - Adding a new DECIMAL to CHAR converter
           with real multibyte support (e.g. UCS2)
    
      @ sql/mysql_priv.h
         - Introducing a new derivation level for numeric/datetime data types.
         - Adding macros for my_charset_numeric and MY_REPERTOIRE_NUMERIC.
         - Adding prototypes for str_set_decimal()
         - Adding prototypes for character-set aware str_to_xxx() functions.
      @ sql/protocol.cc
         - Changing charsetnr to "binary" client-side metadata for
           numeric/datetime data types.
      @ sql/time.cc
         - Adding to_ascii() helper function, to convert a string
           in any character set to ascii representation. In the
           future can be extended to understand digits written
           in various non-Latin word scripts.
         - Adding real multy-byte character set aware versions for str_to_XXXX,
           to make these these type of queries work correct:
             INSERT INTO t1 SET datetime_column=ucs2_expression;
       @  strings/ctype-ucs2.c
         - endptr was not calculated correctly. INSERTing of UCS2
           values into numeric columns returned warnings about
           truncated wrong data.
    702166bc
case.result 9.33 KB
drop table if exists t1, t2;
select CASE "b" when "a" then 1 when "b" then 2 END;
CASE "b" when "a" then 1 when "b" then 2 END
2
select CASE "c" when "a" then 1 when "b" then 2 END;
CASE "c" when "a" then 1 when "b" then 2 END
NULL
select CASE "c" when "a" then 1 when "b" then 2 ELSE 3 END;
CASE "c" when "a" then 1 when "b" then 2 ELSE 3 END
3
select CASE BINARY "b" when "a" then 1 when "B" then 2 WHEN "b" then "ok" END;
CASE BINARY "b" when "a" then 1 when "B" then 2 WHEN "b" then "ok" END
ok
select CASE "b" when "a" then 1 when binary "B" then 2 WHEN "b" then "ok" END;
CASE "b" when "a" then 1 when binary "B" then 2 WHEN "b" then "ok" END
ok
select CASE concat("a","b") when concat("ab","") then "a" when "b" then "b" end;
CASE concat("a","b") when concat("ab","") then "a" when "b" then "b" end
a
select CASE when 1=0 then "true" else "false" END;
CASE when 1=0 then "true" else "false" END
false
select CASE 1 when 1 then "one" WHEN 2 then "two" ELSE "more" END;
CASE 1 when 1 then "one" WHEN 2 then "two" ELSE "more" END
one
explain extended select CASE 1 when 1 then "one" WHEN 2 then "two" ELSE "more" END;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	select (case 1 when 1 then 'one' when 2 then 'two' else 'more' end) AS `CASE 1 when 1 then "one" WHEN 2 then "two" ELSE "more" END`
select CASE 2.0 when 1 then "one" WHEN 2.0 then "two" ELSE "more" END;
CASE 2.0 when 1 then "one" WHEN 2.0 then "two" ELSE "more" END
two
select (CASE "two" when "one" then "1" WHEN "two" then "2" END) | 0;
(CASE "two" when "one" then "1" WHEN "two" then "2" END) | 0
2
select (CASE "two" when "one" then 1.00 WHEN "two" then 2.00 END) +0.0;
(CASE "two" when "one" then 1.00 WHEN "two" then 2.00 END) +0.0
2.00
select case 1/0 when "a" then "true" else "false" END;
case 1/0 when "a" then "true" else "false" END
false
select case 1/0 when "a" then "true" END;
case 1/0 when "a" then "true" END
NULL
select (case 1/0 when "a" then "true" END) | 0;
(case 1/0 when "a" then "true" END) | 0
NULL
select (case 1/0 when "a" then "true" END) + 0.0;
(case 1/0 when "a" then "true" END) + 0.0
NULL
select case when 1>0 then "TRUE" else "FALSE" END;
case when 1>0 then "TRUE" else "FALSE" END
TRUE
select case when 1<0 then "TRUE" else "FALSE" END;
case when 1<0 then "TRUE" else "FALSE" END
FALSE
create table t1 (a int);
insert into t1 values(1),(2),(3),(4);
select case a when 1 then 2 when 2 then 3 else 0 end as fcase, count(*) from t1 group by fcase;
fcase	count(*)
0	2
2	1
3	1
explain extended select case a when 1 then 2 when 2 then 3 else 0 end as fcase, count(*) from t1 group by fcase;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	100.00	Using temporary; Using filesort
Warnings:
Note	1003	select (case `test`.`t1`.`a` when 1 then 2 when 2 then 3 else 0 end) AS `fcase`,count(0) AS `count(*)` from `test`.`t1` group by (case `test`.`t1`.`a` when 1 then 2 when 2 then 3 else 0 end)
select case a when 1 then "one" when 2 then "two" else "nothing" end as fcase, count(*) from t1 group by fcase;
fcase	count(*)
nothing	2
one	1
two	1
drop table t1;
create table t1 (row int not null, col int not null, val varchar(255) not null);
insert into t1 values (1,1,'orange'),(1,2,'large'),(2,1,'yellow'),(2,2,'medium'),(3,1,'green'),(3,2,'small');
select max(case col when 1 then val else null end) as color from t1 group by row;
color
orange
yellow
green
drop table t1;
SET NAMES latin1;
CREATE TABLE t1 SELECT 
CASE WHEN 1 THEN _latin1'a' COLLATE latin1_danish_ci ELSE _latin1'a' END AS c1,
CASE WHEN 1 THEN _latin1'a' ELSE _latin1'a' COLLATE latin1_danish_ci END AS c2,
CASE WHEN 1 THEN 'a' ELSE  1  END AS c3,
CASE WHEN 1 THEN  1  ELSE 'a' END AS c4,
CASE WHEN 1 THEN 'a' ELSE 1.0 END AS c5,
CASE WHEN 1 THEN 1.0 ELSE 'a' END AS c6,
CASE WHEN 1 THEN  1  ELSE 1.0 END AS c7,
CASE WHEN 1 THEN 1.0 ELSE  1  END AS c8,
CASE WHEN 1 THEN 1.0 END AS c9,
CASE WHEN 1 THEN 0.1e1 else 0.1 END AS c10,
CASE WHEN 1 THEN 0.1e1 else 1 END AS c11,
CASE WHEN 1 THEN 0.1e1 else '1' END AS c12
;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(1) CHARACTER SET latin1 COLLATE latin1_danish_ci NOT NULL DEFAULT '',
  `c2` varchar(1) CHARACTER SET latin1 COLLATE latin1_danish_ci NOT NULL DEFAULT '',
  `c3` varchar(1) NOT NULL DEFAULT '',
  `c4` varchar(1) NOT NULL DEFAULT '',
  `c5` varchar(4) NOT NULL DEFAULT '',
  `c6` varchar(4) NOT NULL DEFAULT '',
  `c7` decimal(2,1) NOT NULL DEFAULT '0.0',
  `c8` decimal(2,1) NOT NULL DEFAULT '0.0',
  `c9` decimal(2,1) DEFAULT NULL,
  `c10` double NOT NULL DEFAULT '0',
  `c11` double NOT NULL DEFAULT '0',
  `c12` varchar(5) NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
SELECT CASE 
WHEN 1 
THEN _latin1'a' COLLATE latin1_danish_ci 
ELSE _latin1'a' COLLATE latin1_swedish_ci
END;
ERROR HY000: Illegal mix of collations (latin1_danish_ci,EXPLICIT) and (latin1_swedish_ci,EXPLICIT) for operation 'case'
SELECT CASE _latin1'a' COLLATE latin1_general_ci
WHEN _latin1'a' COLLATE latin1_danish_ci  THEN 1
WHEN _latin1'a' COLLATE latin1_swedish_ci THEN 2
END;
ERROR HY000: Illegal mix of collations (latin1_general_ci,EXPLICIT), (latin1_danish_ci,EXPLICIT), (latin1_swedish_ci,EXPLICIT) for operation 'case'
SELECT 
CASE _latin1'a' COLLATE latin1_general_ci  WHEN _latin1'A' THEN '1' ELSE 2 END,
CASE _latin1'a' COLLATE latin1_bin         WHEN _latin1'A' THEN '1' ELSE 2 END,
CASE _latin1'a' WHEN _latin1'A' COLLATE latin1_swedish_ci THEN '1' ELSE 2 END,
CASE _latin1'a' WHEN _latin1'A' COLLATE latin1_bin        THEN '1' ELSE 2 END
;
CASE _latin1'a' COLLATE latin1_general_ci  WHEN _latin1'A' THEN '1' ELSE 2 END	CASE _latin1'a' COLLATE latin1_bin         WHEN _latin1'A' THEN '1' ELSE 2 END	CASE _latin1'a' WHEN _latin1'A' COLLATE latin1_swedish_ci THEN '1' ELSE 2 END	CASE _latin1'a' WHEN _latin1'A' COLLATE latin1_bin        THEN '1' ELSE 2 END
1	2	1	2
CREATE TABLE t1 SELECT COALESCE(_latin1'a',_latin2'a');
ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin2_general_ci,COERCIBLE) for operation 'coalesce'
CREATE TABLE t1 SELECT COALESCE('a' COLLATE latin1_swedish_ci,'b' COLLATE latin1_bin);
ERROR HY000: Illegal mix of collations (latin1_swedish_ci,EXPLICIT) and (latin1_bin,EXPLICIT) for operation 'coalesce'
CREATE TABLE t1 SELECT 
COALESCE(1), COALESCE(1.0),COALESCE('a'),
COALESCE(1,1.0), COALESCE(1,'1'),COALESCE(1.1,'1'),
COALESCE('a' COLLATE latin1_bin,'b');
explain extended SELECT 
COALESCE(1), COALESCE(1.0),COALESCE('a'),
COALESCE(1,1.0), COALESCE(1,'1'),COALESCE(1.1,'1'),
COALESCE('a' COLLATE latin1_bin,'b');
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	select coalesce(1) AS `COALESCE(1)`,coalesce(1.0) AS `COALESCE(1.0)`,coalesce('a') AS `COALESCE('a')`,coalesce(1,1.0) AS `COALESCE(1,1.0)`,coalesce(1,'1') AS `COALESCE(1,'1')`,coalesce(1.1,'1') AS `COALESCE(1.1,'1')`,coalesce(('a' collate latin1_bin),'b') AS `COALESCE('a' COLLATE latin1_bin,'b')`
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `COALESCE(1)` int(1) NOT NULL DEFAULT '0',
  `COALESCE(1.0)` decimal(2,1) NOT NULL DEFAULT '0.0',
  `COALESCE('a')` varchar(1) NOT NULL DEFAULT '',
  `COALESCE(1,1.0)` decimal(2,1) NOT NULL DEFAULT '0.0',
  `COALESCE(1,'1')` varchar(1) NOT NULL DEFAULT '',
  `COALESCE(1.1,'1')` varchar(4) NOT NULL DEFAULT '',
  `COALESCE('a' COLLATE latin1_bin,'b')` varchar(1) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
SELECT 'case+union+test'
UNION 
SELECT CASE LOWER('1') WHEN LOWER('2') THEN 'BUG' ELSE 'nobug' END;
case+union+test
case+union+test
nobug
SELECT CASE LOWER('1') WHEN LOWER('2') THEN 'BUG' ELSE 'nobug' END;
CASE LOWER('1') WHEN LOWER('2') THEN 'BUG' ELSE 'nobug' END
nobug
SELECT 'case+union+test'
UNION 
SELECT CASE '1' WHEN '2' THEN 'BUG' ELSE 'nobug' END;
case+union+test
case+union+test
nobug
create table t1(a float, b int default 3);
insert into t1 (a) values (2), (11), (8);
select min(a), min(case when 1=1 then a else NULL end),
min(case when 1!=1 then NULL else a end) 
from t1 where b=3 group by b;
min(a)	min(case when 1=1 then a else NULL end)	min(case when 1!=1 then NULL else a end)
2	2	2
drop table t1;
CREATE TABLE t1 (EMPNUM INT);
INSERT INTO t1 VALUES (0), (2);
CREATE TABLE t2 (EMPNUM DECIMAL (4, 2));
INSERT INTO t2 VALUES (0.0), (9.0);
SELECT COALESCE(t2.EMPNUM,t1.EMPNUM) AS CEMPNUM,
t1.EMPNUM AS EMPMUM1, t2.EMPNUM AS EMPNUM2
FROM t1 LEFT JOIN t2 ON t1.EMPNUM=t2.EMPNUM;
CEMPNUM	EMPMUM1	EMPNUM2
0.00	0	0.00
2.00	2	NULL
SELECT IFNULL(t2.EMPNUM,t1.EMPNUM) AS CEMPNUM,
t1.EMPNUM AS EMPMUM1, t2.EMPNUM AS EMPNUM2
FROM t1 LEFT JOIN t2 ON t1.EMPNUM=t2.EMPNUM;
CEMPNUM	EMPMUM1	EMPNUM2
0.00	0	0.00
2.00	2	NULL
DROP TABLE t1,t2;
End of 4.1 tests
create table t1 (a int, b bigint unsigned);
create table t2 (c int);
insert into t1 (a, b) values (1,4572794622775114594), (2,18196094287899841997),
(3,11120436154190595086);
insert into t2 (c) values (1), (2), (3);
select t1.a, (case t1.a when 0 then 0 else t1.b end) d from t1 
join t2 on t1.a=t2.c order by d;
a	d
1	4572794622775114594
3	11120436154190595086
2	18196094287899841997
select t1.a, (case t1.a when 0 then 0 else t1.b end) d from t1 
join t2 on t1.a=t2.c where b=11120436154190595086 order by d;
a	d
3	11120436154190595086
drop table t1, t2;
End of 5.0 tests