mirror/mariadb
1
0
Fork 0
mirror of https://github.com/MariaDB/server.git synced 2025-07-21 10:48:15 +02:00
Code Issues Projects Releases Packages Wiki Activity
mariadb/mysql-test/main/case.test
Alexander Barkov c5d8b9963a MDEV-36716 A case expression with ROW arguments in THEN crashes 
The patch for SYS_REFCURSOR (MDEV-20034) overrode these methods:
- Item_func_case_searched::check_arguments()
- Item_func_if::check_arguments()

to validate WHEN-style arguments (e.g. args[0] in case of IF) for being
able to return a boolean result.

However, this unintentionally removed the test for the THEN-style arguments
that they are not expressions of the ROW data type.

This led to a crash inside Type_handler_hybrid_field_type::aggregate_for_result
on a DBUG_ASSERT that arguments are not of the ROW data type.

Fix:

The fix restores blocking ROW expressions in the not supported cases,
to avoid the DBUG_ASSERT and to raise an SQL error instead.

Blocking ROW_RESULT expressions is done per Item_func_case_expression
descendant individually, instead of blocking any ROW_RESULT arguments
at the Item_func_case_expression level.

The fix is done taking into account the upcoming patch for associative arrays
(MDEV-34319). It should be possible to pass associative array expressions into
some hybrid type functions, where ROW type expressions are not possible.

As a side effect, some lecagy ER_OPERAND_COLUMNS changed to
a newer ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION

Changes in the top affected class Item_func_case_expression:

- item_func.h:
  Overriding Item_func_case_expression::check_arguments() to return false,
  without checking any arguments. Descendant validate arguments
  in a various different ways. No needs to block all non-scalar data type at
  this level, to prevent disallowing associative arrays.

Changes in descendants:

- item_cmpfunc.cc:
  Adding a test in Item_func_case_simple::aggregate_switch_and_when_arguments()
  preventing passing ROW_RESULT expression in predicant and WHEN in a
  simple CASE:
    CASE predicant WHEN when1 THEN .. WHEN when2 THEN .. END;
  This is not supported yet. Should be preferrably fixed before MDEV-34319.

- item_cmpfunc.cc:
  Calling args[0]->type_handler()->Item_hybrid_func_fix_attributes()
  from Item_func_nullif::fix_length_and_dec().
  This prevents a ROW expression to be passed to args[0] of NULLIF().
  But will allow to pass associative arrays.

  args[1] is still only checked to be comparable with args[0].
  No needs to add additional tests for it.

- item_cmpfunc.h:
  Adding a call for Item_hybrid_func_fix_attributes() in
  Item_func_case_abbreviation2::cache_type_info().
  This prevents calling the descendant functions with
  a ROW expression in combination with an explicit NULL
  in the THEN-style arguments (but will allow to pass associative arrays):

    IFNULL(row_expression, NULL)
    IFNULL(NULL, row_expression)
    IF(switch, row_expression, NULL)
    IF(switch, NULL, row_expression)
    NVL2(switch, row_expression, NULL)
    NVL2(switch, NULL, row_expression)
  Adding a THD* argument into involved methods.

- item_cmpfunc.h:
  Overriding Item_func_case_abbreviation2_switch::check_arguments() to
  check that the first argument in IF() and NVL2() can return bool.
  Removing Item_func_if::check_arguments(), as it become redundant.

- sql_type.cc:
  Fixing sql_type.cc not to disallow items[0] with ROW_RESULT.
  This makes it call Item_hybrid_func_fix_attributes() at the end,
  which block ROW arguments into THEN-style arguments of hybrid functions.
  But this will allow to pass Type_handler_assoc_array expressions.

- sql_type.cc:
  Changing Type_handler_row::Item_hybrid_func_fix_attributes to raise the
  ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION error instead of the DBUG_ASSERT.
2025-05-05 19:44:04 +04:00

529 lines
18 KiB
Text
Raw Permalink Blame History

# Testing of CASE
#
--disable_warnings
drop table if exists t1, t2;
--enable_warnings
select CASE "b" when "a" then 1 when "b" then 2 END as exp;
select CASE "c" when "a" then 1 when "b" then 2 END as exp;
select CASE "c" when "a" then 1 when "b" then 2 ELSE 3 END as exp;
select CASE BINARY "b" when "a" then 1 when "B" then 2 WHEN "b" then "ok" END as exp;
select CASE "b" when "a" then 1 when binary "B" then 2 WHEN "b" then "ok" END as exp;
select CASE concat("a","b") when concat("ab","") then "a" when "b" then "b" end as exp;
select CASE when 1=0 then "true" else "false" END as exp;
select CASE 1 when 1 then "one" WHEN 2 then "two" ELSE "more" END as exp;
explain extended select CASE 1 when 1 then "one" WHEN 2 then "two" ELSE "more" END as exp;
select CASE 2.0 when 1 then "one" WHEN 2.0 then "two" ELSE "more" END as exp;
select (CASE "two" when "one" then "1" WHEN "two" then "2" END) | 0 as exp;
select (CASE "two" when "one" then 1.00 WHEN "two" then 2.00 END) +0.0 as exp;
select case 1/0 when "a" then "true" else "false" END as exp;
select case 1/0 when "a" then "true" END as exp;
select (case 1/0 when "a" then "true" END) | 0 as exp;
select (case 1/0 when "a" then "true" END) + 0.0 as exp;
select case when 1>0 then "TRUE" else "FALSE" END as exp;
select case when 1<0 then "TRUE" else "FALSE" END as exp;
#
# Test bug when using GROUP BY on CASE
#
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;
explain extended select case a when 1 then 2 when 2 then 3 else 0 end as fcase, count(*) from t1 group by fcase;
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 1267
SELECT CASE
WHEN 1
THEN _latin1'a' COLLATE latin1_danish_ci
ELSE _latin1'a' COLLATE latin1_swedish_ci
END;
--error 1270
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 as e1,
CASE _latin1'a' COLLATE latin1_bin WHEN _latin1'A' THEN '1' ELSE 2 END as e2,
CASE _latin1'a' WHEN _latin1'A' COLLATE latin1_swedish_ci THEN '1' ELSE 2 END as e3,
CASE _latin1'a' WHEN _latin1'A' COLLATE latin1_bin THEN '1' ELSE 2 END as e4
;
#
# COALESCE is a CASE abbreviation:
#
# 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 1267
CREATE TABLE t1 SELECT COALESCE(_latin1'a',_latin2'a');
--error 1267
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 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');
SHOW CREATE TABLE t1;
DROP TABLE t1;
--error 1267
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
--echo #
--echo # Bug#19875294 ASSERTION `SRC' FAILED IN MY_STRNXFRM_UNICODE
--echo # (SIG 6 -STRINGS/CTYPE-UTF8.C:5151)
--echo #
set @@sql_mode='';
CREATE TABLE t1(c1 SET('','')CHARACTER SET ucs2);
INSERT INTO t1 VALUES(990101.102);
SELECT COALESCE(c1)FROM t1 ORDER BY 1;
DROP TABLE t1;
set @@sql_mode=default;
#
# lp:1001510
# 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;
#
# lp:839387 Assertion `(Item_result)i != TIME_RESULT' failed with CASE + datetime
#
create table t1 (f1 time);
insert t1 values ('00:00:00'),('00:01:00');
select case t1.f1 when '00:00:00' then 1 end from t1;
drop table t1;
--echo #
--echo # MDEV-9745 Crash with CASE WHEN TRUE THEN COALESCE(CAST(NULL AS UNSIGNED)) ELSE 4 END
--echo #
CREATE TABLE t1 SELECT CASE WHEN TRUE THEN COALESCE(CAST(NULL AS UNSIGNED)) ELSE 4 END AS a;
DESCRIBE t1;
DROP TABLE t1;
CREATE TABLE t1 SELECT CASE WHEN TRUE THEN COALESCE(CAST(NULL AS UNSIGNED)) ELSE 40 END AS a;
DESCRIBE t1;
DROP TABLE t1;
--echo #
--echo # Start of 10.1 test
--echo #
--echo #
--echo # MDEV-8752 Wrong result for SELECT..WHERE CASE enum_field WHEN 1 THEN 1 ELSE 0 END AND a='5'
--echo #
CREATE TABLE t1 (a ENUM('5','6') CHARACTER SET BINARY);
INSERT INTO t1 VALUES ('5'),('6');
SELECT * FROM t1 WHERE a='5';
SELECT * FROM t1 WHERE a=1;
SELECT * FROM t1 WHERE CASE a WHEN 1 THEN 1 ELSE 0 END;
SELECT * FROM t1 WHERE CASE a WHEN 1 THEN 1 ELSE 0 END AND a='5';
--echo # Multiple comparison types in CASE, not Ok to propagate
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE CASE a WHEN 1 THEN 1 ELSE 0 END AND a='5';
DROP TABLE t1;
CREATE TABLE t1 (a ENUM('a','b','100')) CHARSET=latin1;
INSERT INTO t1 VALUES ('a'),('b'),('100');
SELECT * FROM t1 WHERE a='a';
SELECT * FROM t1 WHERE CASE a WHEN 'a' THEN 1 ELSE 0 END;
SELECT * FROM t1 WHERE CASE a WHEN 'a' THEN 1 ELSE 0 END AND a='a';
--echo # String comparison in CASE and in the equality, ok to propagate
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE CASE a WHEN 'a' THEN 1 ELSE 0 END AND a='a';
SELECT * FROM t1 WHERE a=3;
SELECT * FROM t1 WHERE CASE a WHEN 3 THEN 1 ELSE 0 END;
SELECT * FROM t1 WHERE CASE a WHEN 3 THEN 1 ELSE 0 END AND a=3;
--echo # Integer comparison in CASE and in the equality, not ok to propagate
--echo # ENUM does not support this type of propagation yet.
--echo # This can change in the future. See MDEV-8748.
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE CASE a WHEN 3 THEN 1 ELSE 0 END AND a=3;
SELECT * FROM t1 WHERE a=3;
SELECT * FROM t1 WHERE CASE a WHEN '100' THEN 1 ELSE 0 END;
SELECT * FROM t1 WHERE CASE a WHEN '100' THEN 1 ELSE 0 END AND a=3;
--echo # String comparison in CASE, integer comparison in the equality, not Ok to propagate
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE CASE a WHEN '100' THEN 1 ELSE 0 END AND a=3;
SELECT * FROM t1 WHERE a='100';
SELECT * FROM t1 WHERE CASE a WHEN 3 THEN 1 ELSE 0 END;
SELECT * FROM t1 WHERE CASE a WHEN 3 THEN 1 ELSE 0 END AND a='100';
--echo # Integer comparison in CASE, string comparison in the equality, not Ok to propagate
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE CASE a WHEN 3 THEN 1 ELSE 0 END AND a='100';
SELECT * FROM t1 WHERE a='100';
SELECT * FROM t1 WHERE CASE a WHEN 3 THEN 1 WHEN '100' THEN 1 ELSE 0 END;
SELECT * FROM t1 WHERE CASE a WHEN 3 THEN 1 WHEN '100' THEN 1 ELSE 0 END AND a='100';
--echo # Multiple type comparison in CASE, string comparison in the equality, not Ok to propagate
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE CASE a WHEN 3 THEN 1 WHEN '100' THEN 1 ELSE 0 END AND a='100';
SELECT * FROM t1 WHERE a=3;
SELECT * FROM t1 WHERE CASE a WHEN 3 THEN 1 WHEN '100' THEN 1 ELSE 0 END;
SELECT * FROM t1 WHERE CASE a WHEN 3 THEN 1 WHEN '100' THEN 1 ELSE 0 END AND a=3;
--echo # Multiple type comparison in CASE, integer comparison in the equality, not Ok to propagate
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE CASE a WHEN 3 THEN 1 WHEN '100' THEN 1 ELSE 0 END AND a=3;
DROP TABLE t1;
--echo #
--echo # End of MDEV-8752
--echo #
--echo #
--echo # End of 10.1 test
--echo #
#
# caching of first argument in CASE/IN for temporal types
#
#
# should not convert all values to time
select case 'foo' when time'10:00:00' then 'never' when '0' then 'bug' else 'ok' end as exp;
select 'foo' in (time'10:00:00','0');
create table t1 (a time);
insert t1 values (100000), (102030), (203040);
# only one warning, TIME('foo') should be cached
select case 'foo' when a then 'never' when '0' then 'bug' else 'ok' end from t1;
select 'foo' in (a,'0') from t1;
drop table t1;
# first comparison should be as date, second as time
select case '20:10:05' when date'2020-10-10' then 'never' when time'20:10:5' then 'ok' else 'bug' end as exp;
--echo #
--echo # End of 10.2 test
--echo #
--echo #
--echo # MDEV-11554 Wrong result for CASE on a mixture of signed and unsigned expressions
--echo #
CREATE TABLE t1 (a BIGINT, b BIGINT UNSIGNED);
INSERT INTO t1 VALUES (-9223372036854775808,18446744073709551615);
SELECT
CASE -1
WHEN -9223372036854775808 THEN 'one'
WHEN 18446744073709551615 THEN 'two'
END AS c;
PREPARE stmt FROM "SELECT
CASE -1
WHEN -9223372036854775808 THEN 'one'
WHEN 18446744073709551615 THEN 'two'
END AS c";
EXECUTE stmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
DROP TABLE t1;
--echo #
--echo # MDEV-11555 CASE with a mixture of TIME and DATETIME returns a wrong result
--echo #
SELECT
CASE TIME'10:20:30'
WHEN 102030 THEN 'one'
WHEN TIME'10:20:31' THEN 'two'
END AS good,
CASE TIME'10:20:30'
WHEN 102030 THEN 'one'
WHEN TIME'10:20:31' THEN 'two'
WHEN TIMESTAMP'2001-01-01 10:20:32' THEN 'three'
END AS was_bad_now_good;
PREPARE stmt FROM "SELECT
CASE TIME'10:20:30'
WHEN 102030 THEN 'one'
WHEN TIME'10:20:31' THEN 'two'
END AS good,
CASE TIME'10:20:30'
WHEN 102030 THEN 'one'
WHEN TIME'10:20:31' THEN 'two'
WHEN TIMESTAMP'2001-01-01 10:20:32' THEN 'three'
END AS was_bad_now_good";
EXECUTE stmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
--echo #
--echo # MDEV-13864 Change Item_func_case to store the predicant in args[0]
--echo #
SET NAMES latin1;
CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET latin1);
INSERT INTO t1 VALUES ('a'),('b'),('c');
# should propagate the predicant and the WHEN arguments (they are in comparison and use ANY_SUBST)
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='a' AND CASE a WHEN 'a' THEN 'a' ELSE 'a' END='a';
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='a' AND CASE 'a' WHEN a THEN 'a' ELSE 'a' END='a';
# should not propagate the THEN and the ELSE arguments (they are not in comparison and use IDENTITY_SUBST)
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='a' AND CASE 'a' WHEN 'a' THEN a ELSE 'a' END='a';
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='a' AND CASE 'a' WHEN 'a' THEN 'a' ELSE a END='a';
ALTER TABLE t1 MODIFY a VARBINARY(10);
# with VARBINARY it should propagate all arguments
# as IDENTITY_SUBST for VARBINARY allows substitution
# of even those arguments that are not in comparison
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='a' AND CASE a WHEN 'a' THEN 'a' ELSE 'a' END='a';
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='a' AND CASE 'a' WHEN a THEN 'a' ELSE 'a' END='a';
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='a' AND CASE 'a' WHEN 'a' THEN a ELSE 'a' END='a';
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='a' AND CASE 'a' WHEN 'a' THEN 'a' ELSE a END='a';
DROP TABLE t1;
--echo #
--echo # MDEV-17411 Wrong WHERE optimization with simple CASE and searched CASE
--echo #
CREATE TABLE t1 (a INT, b INT, KEY(a));
INSERT INTO t1 VALUES (1,1),(2,2),(3,3);
SELECT * FROM t1 WHERE CASE a WHEN b THEN 1 END=1;
SELECT * FROM t1 WHERE CASE WHEN a THEN b ELSE 1 END=3;
SELECT * FROM t1 WHERE
CASE a WHEN b THEN 1 END=1 AND
CASE WHEN a THEN b ELSE 1 END=3;
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE
CASE a WHEN b THEN 1 END=1 AND
CASE WHEN a THEN b ELSE 1 END=3;
DROP TABLE t1;
--echo #
--echo # End of 10.3 test
--echo #
--echo # Start of 12.0 tests
--echo #
--echo # MDEV-36716 A case expression with ROW arguments in THEN crashes
--echo #
# Searched CASE + an error in THEN/ELSE arguments
--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
SELECT CASE WHEN TRUE THEN ROW(1,2) ELSE ROW(2,1) END;
--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
SELECT CASE WHEN TRUE THEN ROW(1,2) ELSE NULL END;
--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
SELECT CASE WHEN TRUE THEN NULL ELSE ROW(2,1) END;
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT CASE WHEN TRUE THEN ROW(2,1) END;
# Searched CASE + an error in WHEN arguments
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT CASE WHEN ROW(1,2) THEN 0 WHEN ROW(2,1) THEN 1 END;
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT CASE WHEN ROW(1,2) THEN 0 END;
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT CASE WHEN NULL THEN 0 WHEN ROW(1,2) THEN 0 END;
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT CASE WHEN ROW(1,2) THEN 0 WHEN NULL THEN 0 END;
# Simple CASE + an error in THEN arguments
--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
SELECT CASE TRUE WHEN TRUE THEN ROW(1,2) ELSE ROW(2,1) END;
--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
SELECT CASE TRUE WHEN TRUE THEN ROW(1,2) ELSE NULL END;
--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
SELECT CASE TRUE WHEN TRUE THEN NULL ELSE ROW(2,1) END;
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT CASE TRUE WHEN TRUE THEN ROW(2,1) END;
# Simple CASE + an error in WHEN arguments - rows are not supported yet
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT CASE ROW(1,2) WHEN ROW(1,2) THEN 0 END;
--error ER_OPERAND_COLUMNS
SELECT CASE NULL WHEN ROW(1,2) THEN 0 END;
--error ER_OPERAND_COLUMNS
SELECT CASE ROW(1,2) WHEN NULL THEN 0 END;
# oracle_schema.DECODE + an error in THEN arguments
--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
SELECT oracle_schema.DECODE(TRUE, TRUE, /*then1*/ROW(1,2), /*else*/ROW(2,1));
--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
SELECT oracle_schema.DECODE(TRUE, TRUE, /*then1*/ROW(1,2), /*else*/NULL);
--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
SELECT oracle_schema.DECODE(TRUE, TRUE, /*then1*/NULL, /*else*/ROW(2,1));
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT oracle_schema.DECODE(TRUE, TRUE, /*then1*/ROW(2,1));
# oracle_schema.DECODE + an error in WHEN arguments - rows are not supported yet
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT oracle_schema.DECODE(/*predicant*/ROW(1,2), /*when1*/ROW(1,2), /*then1*/0);
--error ER_OPERAND_COLUMNS
SELECT oracle_schema.DECODE(/*predicant*/NULL, /*when1*/ROW(1,2), /*then1*/0);
--error ER_OPERAND_COLUMNS
SELECT oracle_schema.DECODE(/*predicant*/ROW(1,2), /*when1*/NULL, /*then1*/0);
--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
SELECT IF(0, ROW(1,2), ROW(1,2));
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT IF(0, NULL, ROW(1,2));
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT IF(0, ROW(1,2), NULL);
--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
SELECT NVL2('0', ROW(1,2), ROW(1,2));
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT NVL2('0', NULL, ROW(1,2));
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT NVL2('0', ROW(1,2), NULL);
--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
SELECT NVL2(NULL, ROW(1,2), ROW(1,2));
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT NVL2(NULL, NULL, ROW(1,2));
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT NVL2(NULL, ROW(1,2), NULL);
--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
SELECT COALESCE(ROW(1,2), ROW(1,2));
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT COALESCE(ROW(1,2));
--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
SELECT COALESCE(ROW(1,2),NULL);
--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
SELECT COALESCE(NULL,ROW(1,2));
--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
SELECT IFNULL(ROW(1,2), ROW(1,2));
--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
SELECT IFNULL(NULL, ROW(1,2));
--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
SELECT IFNULL(ROW(1,2), NULL);
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT NULLIF(ROW(1,2), ROW(1,2));
--error ER_OPERAND_COLUMNS
SELECT NULLIF(NULL, ROW(1,2));
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT NULLIF(ROW(1,2), NULL);
--echo # End of 12.0 tests
Reference in a new issue View git blame Copy permalink