case.test

# Testing of CASE
#

--disable_warnings
drop table if exists t1, t2;
--enable_warnings

SET sql_mode = 'NO_ENGINE_SUBSTITUTION';

select CASE "b" when "a" then 1 when "b" then 2 END;
select CASE "c" when "a" then 1 when "b" then 2 END;
select CASE "c" when "a" then 1 when "b" then 2 ELSE 3 END;
select CASE BINARY "b" when "a" then 1 when "B" then 2 WHEN "b" then "ok" END;
select CASE "b" when "a" then 1 when binary "B" then 2 WHEN "b" then "ok" END;
select CASE concat("a","b") when concat("ab","") then "a" when "b" then "b" end;
select CASE when 1=0 then "true" else "false" END;
select CASE 1 when 1 then "one" WHEN 2 then "two" ELSE "more" END;
explain select 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;
select (CASE "two" when "one" then "1" WHEN "two" then "2" END) | 0;
select (CASE "two" when "one" then 1.00 WHEN "two" then 2.00 END) +0.0;
select case 1/0 when "a" then "true" else "false" END;
select case 1/0 when "a" then "true" END;
select (case 1/0 when "a" then "true" END) | 0;
select (case 1/0 when "a" then "true" END) + 0.0;
select case when 1>0 then "TRUE" else "FALSE" END;
select case when 1<0 then "TRUE" else "FALSE" END;

#
# Test bug when using GROUP BY on CASE
#
create table t1 (a int);
insert into t1 values(1),(2),(3),(4);
--sorted_result
select case a when 1 then 2 when 2 then 3 else 0 end as fcase, count(*) from t1 group by fcase;
analyze table t1;
explain select case a when 1 then 2 when 2 then 3 else 0 end as fcase, count(*) from t1 group by fcase;
--sorted_result
select case a when 1 then "one" when 2 then "two" else "nothing" end as fcase, count(*) from t1 group by fcase;
drop table t1;

#
# Test MAX(CASE ... ) that can return null
#

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`;
drop table t1;

SET NAMES latin1;

#
# CASE and argument types/collations aggregation into result 
#
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;
DROP TABLE t1;

--error ER_CANT_AGGREGATE_2COLLATIONS
SELECT CASE 
  WHEN 1 
  THEN _latin1'a' COLLATE latin1_danish_ci 
  ELSE _latin1'a' COLLATE latin1_swedish_ci
  END;

--error ER_CANT_AGGREGATE_3COLLATIONS
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;

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
;

#
# COALESCE is a CASE abbrevation:
#
# COALESCE(v1,v2) == CASE WHEN v1 IS NOT NULL THEN v1 ELSE v2 END
#
# COALESCE(V1, V2, . . . ,Vn ) =  
#     CASE WHEN V1 IS NOT NULL THEN V1 ELSE COALESCE (V2, . . . ,Vn) END
#
# Check COALESCE argument types aggregation

--error ER_CANT_AGGREGATE_2COLLATIONS
CREATE TABLE t1 SELECT COALESCE(_latin1'a',_latin2'a');
--error ER_CANT_AGGREGATE_2COLLATIONS
CREATE TABLE t1 SELECT COALESCE('a' COLLATE latin1_swedish_ci,'b' COLLATE latin1_bin);
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 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');
SHOW CREATE TABLE t1;
DROP TABLE t1;

--error ER_CANT_AGGREGATE_2COLLATIONS
CREATE TABLE t1 SELECT IFNULL('a' COLLATE latin1_swedish_ci, 'b' COLLATE latin1_bin);

# Test for BUG#10151
SELECT 'case+union+test'
UNION 
SELECT CASE LOWER('1') WHEN LOWER('2') THEN 'BUG' ELSE 'nobug' END;

SELECT CASE LOWER('1') WHEN LOWER('2') THEN 'BUG' ELSE 'nobug' END;

SELECT 'case+union+test'
UNION 
SELECT CASE '1' WHEN '2' THEN 'BUG' ELSE 'nobug' END;

#
# Bug #17896: problem with MIN(CASE...)
#

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;
drop table t1;


#
# Tests for bug #9939: conversion of the arguments for COALESCE and IFNULL
#

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;

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;

DROP TABLE t1,t2;

--echo End of 4.1 tests

#
# #30782: Truncated UNSIGNED BIGINT columns 
#
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;
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;
drop table t1, t2;

--echo End of 5.0 tests

#
# Bug #11764313 57135: CRASH IN ITEM_FUNC_CASE::FIND_ITEM WITH CASE WHEN
# ELSE CLAUSE
#

CREATE TABLE t1(a YEAR);
SELECT 1 FROM t1 WHERE a=1 AND CASE 1 WHEN a THEN 1 ELSE 1 END;
DROP TABLE t1;

SET sql_mode = default;

--echo #
--echo # Bug#19875294 ASSERTION `SRC' FAILED IN MY_STRNXFRM_UNICODE
--echo #              (SIG 6 -STRINGS/CTYPE-utf8mb3.C:5151)
--echo #

set @@sql_mode='';
CREATE TABLE t1(c1 SET('','')CHARACTER SET ucs2) engine=innodb;
INSERT INTO t1 VALUES(990101.102);
SELECT COALESCE(c1)FROM t1 ORDER BY 1;
DROP TABLE t1;
set @@sql_mode=default;

--echo # Bug#24733658: IF and friends give wrong type for signed and unsigned

CREATE TABLE source(bt INTEGER, bf INTEGER, i8u BIGINT UNSIGNED, i8s BIGINT);
INSERT INTO source VALUES
(1,0,0,-9223372036854775808), (1,0,18446744073709551615,9223372036854775807);

CREATE TABLE target
SELECT IF(bt,i8u,i8s) AS u, IF(bf,i8u,i8s) AS s
FROM source;

SHOW CREATE TABLE target;

SELECT IF(bt,i8u,i8s) AS u, IF(bf,i8u,i8s) AS s
FROM source;

SELECT * FROM target;

DROP TABLE target;

CREATE TABLE target
SELECT CASE WHEN bt THEN i8u ELSE i8s END AS u,
       CASE WHEN bf THEN i8u ELSE i8s END AS s
FROM source;

SHOW CREATE TABLE target;

SELECT CASE WHEN bt THEN i8u ELSE i8s END AS u,
       CASE WHEN bf THEN i8u ELSE i8s END AS s
FROM source;

SELECT * FROM target;

DROP TABLE target;

CREATE TABLE target
SELECT CASE bt WHEN TRUE THEN i8u WHEN FALSE THEN i8s END AS u,
       CASE bf WHEN TRUE THEN i8u WHEN FALSE THEN i8s END AS s
FROM source;

SHOW CREATE TABLE target;

SELECT CASE bt WHEN TRUE THEN i8u WHEN FALSE THEN i8s END AS u,
       CASE bf WHEN TRUE THEN i8u WHEN FALSE THEN i8s END AS s
FROM source;

SELECT * FROM target;

DROP TABLE target;

CREATE TABLE target
SELECT COALESCE(i8u, i8s) AS u, COALESCE(i8s, i8u) AS s
FROM source;

SHOW CREATE TABLE target;

SELECT COALESCE(i8u, i8s) AS u, COALESCE(i8s, i8u) AS s
FROM source;

SELECT * FROM target;

DROP TABLE source, target;

--echo # Bug#25139420: BIT(n) data type confused when used with IF, IFNULL

CREATE TABLE t (a bit(5));
INSERT INTO t VALUES
 (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),
 (10),(11),(12),(19),(20),(21),(29),(30),(31);

let $query=
SELECT HEX(a),
       IFNULL(a,a) AS b,
       IFNULL(a,a)+0 AS c,
       IFNULL(a+0,a+0) AS d,
       IFNULL(a+0,a) AS e,
       IFNULL(a,a+0) AS f
FROM t;

eval $query;
eval CREATE TABLE u AS $query;
SHOW CREATE TABLE u;
DROP TABLE t, u;

--echo # Bug#26389402: Outer join optimized away with user-defined functions

CREATE TABLE t00(a INTEGER);
INSERT INTO t00 VALUES (1),(2);
CREATE TABLE t01(a INTEGER);
INSERT INTO t01 VALUES (1);
CREATE VIEW v0 AS
SELECT t00.a, t01.a AS b, IFNULL(t01.a, 666) AS c
FROM t00 LEFT JOIN t01 USING(a);
SELECT * FROM v0
WHERE c >= 0;

CREATE FUNCTION f(a INTEGER) RETURNS INTEGER DETERMINISTIC
RETURN IFNULL(a, 666);

CREATE VIEW v1 AS
SELECT t00.a, t01.a AS b, f(t01.a) AS c
FROM t00 LEFT JOIN t01 USING(a);
SELECT * FROM v1
WHERE c >= 0;

CREATE VIEW v2 AS
SELECT t00.a, t01.a AS b, f(IFNULL(t01.a, NULL)) AS c
FROM t00 LEFT JOIN t01 USING(a);
SELECT * FROM v2
WHERE c >= 0;

SELECT t00.a, t01.a AS b, f(t01.a) AS c
FROM t00 LEFT JOIN t01 USING(a)
WHERE f(t01.a) >= 0;

DROP FUNCTION f;
DROP VIEW v0, v1, v2;
DROP TABLE t00, t01;

--echo #
--echo # Bug #25051195 Wrong calculation of decimals after point for IFNULL w/ GROUP BY using
--echo #               temporary table.
CREATE TABLE t (i1 INT,
                d1 DOUBLE,
                e2 DECIMAL(5,2));

INSERT INTO t VALUES ( 6,    6.0,  10.0/3),
                     ( null, 9.0,  10.0/3),
                     ( 1,    null, 10.0/3),
                     ( 2,    2.0,  null  );
--echo IFNULL and IF should yield same number for decimals after point here
SELECT IFNULL(e2,i1) nullif_c,
       IF(e2 IS NULL,i1,e2) if_c,
       SUM(d1) FROM t
       GROUP BY e2,i1 ORDER BY nullif_c, SUM(d1);

DROP TABLE t;

--echo # Bug#29463760 In cases like ifnull(datetime(2), time(3), wrong answer

# Establish a specific date for use when extending TIME values:
SET TIMESTAMP=UNIX_TIMESTAMP('2019-03-11 12:00:00');
CREATE TABLE t1 (dt2 DATETIME(2), t3 TIME(3), d DATE);
INSERT INTO t1 VALUES ('2001-01-01 00:00:00.12', '00:00:00.567', '2002-01-01');
SELECT CONCAT(IFNULL(t3, d)) AS col1 FROM t1;
SELECT CONCAT(IFNULL(t3, d)) AS col1 FROM t1 GROUP BY col1;
SET TIMESTAMP=DEFAULT;

DROP TABLE t1;

--echo #
--echo # Test for a memory leak in WL#6570
--echo #

CREATE TABLE t1 (a VARCHAR(110));
INSERT INTO t1 VALUES (REPEAT("a",100));
SELECT (t1.a,t1.a) IN (('a','c'),('a','b')) END FROM t1;
SELECT CASE t1.a WHEN 'a' THEN 'c' ELSE 'd' END FROM t1;
DROP TABLE t1;

--echo #
--echo # Bug#32591239: VAL_JSON(JSON_WRAPPER*):ASSERTION
--echo #               `!CURRENT_THD->IS_ERROR() && HAS_VALUE' FAILED
--echo #
CREATE TABLE t1 (col_int int, col_double double, j json);
INSERT INTO t1 VALUES (382218415, -36452.389, '{"key1": 220655528}');
--error ER_DATA_OUT_OF_RANGE
SELECT col_int FROM t1
WHERE CASE WHEN POWER(col_double, col_int) THEN j ELSE j END;
DROP TABLE t1;
--echo # Bug#32865008: GROUP_CONCAT: setup_fields: assertion `!thd->is_error()' failed

--error ER_WRONG_ARGUMENTS
DO GROUP_CONCAT(DISTINCT NULLIF(1, PERIOD_ADD(0x6f09c5f8 ,'7451-01-27')));

--echo # Bug#35513196: Assertion failed: this_type != enum_json_type::J_ERROR

--error ER_WRONG_ARGUMENTS
DO IFNULL(multipolygon(
              multilinestring(
                  linestring(point(8117,-31186), point(31282,20992)),
                  linestring(point(-10280,-15814), point(13662,-12122),
                             point(12677,16556)))),
          benchmark(2, version() >> (NOT json_objectagg('{"ab":2}',
                          '[{"a":"3"},{"a":2},{"b":1},{"a":0},{"a":[1,2]}]'))));

--error ER_WRONG_ARGUMENTS
DO COALESCE(multipolygon(
                multilinestring(
                    linestring(point(8117,-31186), point(31282,20992)),
                    linestring(point(-10280,-15814), point(13662,-12122),
                               point(12677,16556)))),
            benchmark(2, version() >> (NOT json_objectagg('{"ab":2}',
                          '[{"a":"3"},{"a":2},{"b":1},{"a":0},{"a":[1,2]}]'))));

--echo #
--echo # Bug#36421511 Assertion `digits >= count_digits(number)' failed.
--echo #

CREATE TABLE t (
  c3 DATETIME(6) NOT NULL,
  c4 DATE NOT NULL
);

INSERT INTO t VALUES ('2024-08-17 22:27:27.000000','2024-11-27');

--error ER_INVALID_TYPE_FOR_JSON
SELECT
  CASE(c3->'')
  WHEN(FROM_UNIXTIME(36))
  THEN(c4)
  ELSE(c3)
  END
FROM t;

DROP TABLE t;

--echo # Bug#36570439: Assertion failed: other_type != enum_json_type::J_ERROR
--echo #               Json_wrapper::compare()

CREATE TABLE t (a INT, b JSON, c VARCHAR(1));

INSERT INTO t VALUES (0, '{}', NULL);

--error ER_INVALID_JSON_PATH
SELECT b
FROM t
WHERE (CASE
       WHEN a THEN NOW(2)
       WHEN b->'w' THEN a < b
       ELSE b <= 1
       END
      ) <=> c;

DROP TABLE t;

--echo #
--echo # Bug#36844420: Assertion failed: tmp >= tmp_value.ptr() in
--echo #               Item_func_reverse::val_str()
--echo #

SELECT REVERSE(IFNULL(DENSE_RANK() OVER(),
                      CONVERT('abc' USING utf32))) AS c;