Description:
MySQL incorrectly validates the return_option argument of REGEXP_INSTR in CASE expression branches that are never executed. When a CASE expression contains multiple WHEN clauses and an earlier condition is always true, MySQL should not evaluate or validate arguments in subsequent branches. However, it throws an error for invalid arguments even when those branches are unreachable.
mysql> select case when (tb1.c2 between 0 and tb1.c2) then (tb1.c22) when (tb1.c21 in (tb1.c21,tb1.c21,tb1.c21)) then (regexp_instr(tb1.c14,'20200229235959.00000000000000001',-2147483648,0,tb1.c5)) when (b'0011001000110000001100100011010100101101001100000011000100101101001100000011000100100000001100000011000000111010001100000011000000111010001100000011000000101110001100000011000000110001' is true) then (tb1.c3) end from tb1 order by 1;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| case when (tb1.c2 between 0 and tb1.c2) then (tb1.c22) when (tb1.c21 in (tb1.c21,tb1.c21,tb1.c21)) then (regexp_instr(tb1.c14,'20200229235959.00000000000000001',-2147483648,0,tb1.c5)) when (b'001100100011000000110010001101010010110100110000001100010010110 |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -00:00:01 |
| -00:00:01 |
| -00:00:01 |
| -23:59:59 |
| -23:59:59 |
| -23:59:59 |
| -838:59:59 |
| -838:59:59 |
| 00:00:00 |
| 00:00:00 |
| 00:00:00 |
| 00:00:01 |
| 00:00:01 |
| 23:59:59 |
| 23:59:59 |
| 23:59:59 |
| 838:59:59 |
| 838:59:59 |
| 838:59:59 |
| 838:59:59 |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
20 rows in set (0.00 sec)
mysql> select tb1.c5 from tb1;
+-------------+
| c5 |
+-------------+
| -2147483648 |
| -2147483648 |
| -2147483648 |
| -2147483648 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 2147483647 |
| 2147483647 |
| 2147483647 |
| 2147483647 |
| 2147483647 |
| 2147483647 |
| 2147483647 |
| 2147483647 |
| 2147483647 |
+-------------+
20 rows in set (0.00 sec)
mysql> select case when (tb1.c2 between 0 and tb1.c2) then (tb1.c22) when (tb1.c21 in (tb1.c21,tb1.c21,tb1.c21)) then (regexp_instr(tb1.c14,'20200229235959.00000000000000001',-2147483648,0,tb1.c5)) when (b'0011001000110000001100100011010100101101001100000011000100101101001100000011000100100000001100000011000000111010001100000011000000111010001100000011000000101110001100000011000000110001' is true) then (tb1.c3) end from tb1 order by 1;
ERROR 1210 (HY000): Incorrect arguments to regexp_instr: return_option must be 1 or 0.
How to repeat:
create table tb1 (c1 tinyint,
c2 tinyint unsigned,
c3 smallint,
c4 smallint unsigned,
c5 integer,
c6 integer unsigned,
c7 bigint,
c8 bigint unsigned,
c9 mediumint,
c10 mediumint unsigned,
c11 float,
c12 double,
c13 decimal(60,30),
c14 boolean,
c15 binary,
c16 varbinary(64),
c17 blob,
c18 char,
c19 varchar(64),
c20 text,
c21 date,
c22 time,
c23 time(3),
c24 time(6),
c25 datetime,
c26 datetime(3),
c27 datetime(6),
c28 timestamp,
c29 timestamp(3),
c30 timestamp(6),
c31 year,
c32 bit,
c33 json,
c34 enum('1','abc','@#$',' ','111111111111111111','-111111111111111111','1970-1-1','-838:59:59.000000','1901','-9223372036854775808','0','9223372036854775807','-9223372036854775807','9e99'),
c35 set('1','abc','@#$',' ','111111111111111111','-111111111111111111','1970-1-1','-838:59:59.000000','1901','0','9e99')) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ;
insert into tb1 values (-128,0,-32768,0,-2147483648,0,-9223372036854775808,0,-8388608,0,-9999999999.9999999999,-9999999999.9999999999,-9999999999.9999999999,0,b'0',b'10111111111',' 高斯aA！@#￥%……&*()—+-=,./<>[]{;:| ','a',' 高斯aA！@#￥%……&*()—+-=,./<>[]{;:| ',' 高斯aA！@#￥%……&*()—+-=,./<>[]{;:| ','1970-1-1','-838:59:59.000000','-838:59:59','-838:59:59','1000-01-01 00:00:00','1000-01-01 00:00:00.000','1000-01-01 00:00:00.000000',timestamp('1970-01-02 00:00:01'),timestamp('1970-01-02 00:00:01.000'),timestamp('1970-01-02 00:00:01.000000'),'1901',b'0','{}','','');
insert into tb1 values (0,255,0,65535,0,4294967295,0,18446744073709551615,0,16777215,0,0,0,1,b'1',b'111111111000000000000','','#','','','2069-12-31','838:59:59.000000','838:59:59','838:59:59','1000-01-01 00:00:01','1000-01-01 00:00:00.001','1000-01-01 00:00:00.000001',timestamp('2038-01-19 03:14:07'),timestamp('2038-01-19 03:14:07.000'),timestamp('2038-01-19 03:14:07.000000'),'2155',b'1','111111111111111111',' ','111111111111111111');
insert into tb1 values (127,0,32767,65535,2147483647,0,9223372036854775807,999999999999999999,8388607,0,9999999999.9999999999,9999999999.9999999999,9999999999.9999999999,1,b'0',b'111111111000000000000',' ','0',' ',' ','2024-12-31','00:00:00','00:00:00.000','00:00:00.000000','9999-12-31 23:59:59','9999-12-31 23:59:59.999','9999-12-31 23:59:59.999999',timestamp('2024-12-31 23:59:59'),timestamp('2024-12-31 23:59:59.999'),timestamp('2024-12-31 23:59:59.999999'),'2024',b'0','-111111111111111111','111111111111111111','-111111111111111111');
insert into tb1 values (-128,0,-32768,0,2147483647,4294967295,999999999999999999,0,8388607,16777215,-99999999999999999.99999999999999999999,-99999999999999999.99999999999999999999,-99999999999999999.99999999999999999999,0,b'1',b'10111111111',repeat('{', 255),'1',repeat('9', 17),repeat('{', 255),'2025-01-01','-00:00:00','-00:00:00.000','-00:00:00.000000','2024-12-31 23:59:59','2024-12-31 23:59:59.999','2024-12-31 23:59:59.999999',timestamp('2025-01-01 00:00:00'),timestamp('2025-01-01 00:00:00.000'),timestamp('2025-01-01 00:00:00.000000'),'2025',b'1','1901','-111111111111111111','1970-1-1');
insert into tb1 values (-128,0,0,65535,2147483647,4294967295,-999999999999999999,18446744073709551615,8388607,0,99999999999999999.99999999999999999999,99999999999999999.99999999999999999999,99999999999999999.99999999999999999999,1,b'0',b'111111111000000000000',repeat('9', 255),'9',repeat('1', 17),repeat('9', 255),'2016-12-31','23:59:59','00:00:00.001','00:00:00.000001','2025-01-01 00:00:00','2025-01-01 00:00:00.000','2025-01-01 00:00:00.000000',timestamp('2025-01-01 00:00:01'),timestamp('2025-01-01 00:00:00.001'),timestamp('2025-01-01 00:00:00.000001'),'2016',b'1','0','1970-1-1','-838:59:59.000000');
insert into tb1 values (-128,0,0,0,0,4294967295,-9223372036854775807,18446744073709551615,8388607,0,99999999999999999.99999999999999999999,99999999999999999,99999999999999999,0,b'1',b'10111111111',repeat('9', 17),'0',' ',repeat('9', 17),'2020-02-29','-23:59:59','-00:00:00.001','-00:00:00.000001','2025-01-01 00:00:01','2025-01-01 00:00:00.001','2025-01-01 00:00:00.000001',timestamp('2016-12-31 23:59:59'),timestamp('2025-01-01 00:00:01.000'),timestamp('2025-01-01 00:00:01.000000'),'2020',b'0','9e99','-838:59:59.000000','1901');
insert into tb1 values (0,255,-32768,65535,-2147483648,4294967295,999999999999999999,0,-8388608,0,9999999999.9999999999,-99999999999999999,-99999999999999999,1,b'1',b'111111111000000000000',repeat('1', 255),'#',repeat('9', 17),repeat('1', 255),'2020-03-01','00:00:01','23:59:59.999','23:59:59.999999','2016-12-31 23:59:59','2025-01-01 00:00:01.000','2025-01-01 00:00:01.000000',timestamp('2020-02-29 23:59:59'),timestamp('2016-12-31 23:59:59.999'),timestamp('2016-12-31 23:59:59.999999'),'2155',b'0','0','1901','0');
insert into tb1 values (-128,0,-32768,0,2147483647,4294967295,999999999999999999,999999999999999999,0,0,99999999999999999.99999999999999999999,-9999999999.9999999999,9999999999.9999999999,1,b'1',b'10111111111',repeat('1', 17),'a',repeat('1', 17),repeat('1', 17),'2025-01-01','-00:00:01','-23:59:59.999','-23:59:59.999999','2020-02-29 23:59:59','2016-12-31 23:59:59.000','2016-12-31 23:59:59.000000',timestamp('2020-03-01 00:00:00'),timestamp('2020-02-29 23:59:59.000'),timestamp('2020-02-29 23:59:59.000000'),'2155',b'1','111111111111111111','-9223372036854775808','9e99');
insert into tb1 values (-128,0,-32768,65535,0,4294967295,-999999999999999999,0,0,16777215,0,99999999999999999,-99999999999999999,1,b'1',b'111111111000000000000',repeat('1', 17),'a',' 高斯aA！@#￥%……&*()—+-=,./<>[]{;:| ',repeat('9', 17),'2024-12-31','23:59:59','00:00:01.000','00:00:01.000000','2020-03-01 00:00:00','2020-02-29 23:59:59.999','2020-02-29 23:59:59.999999',timestamp('2020-03-01 00:00:01'),timestamp('2020-03-01 00:00:00.000'),timestamp('2020-03-01 00:00:00.000000'),'2024',b'0','9e99','0','');
insert into tb1 values (-128,0,-32768,65535,-2147483648,4294967295,999999999999999999,999999999999999999,8388607,0,0,-9999999999.9999999999,99999999999999999,1,b'0',b'10111111111',repeat('9', 255),'#','',repeat('9', 255),'1970-1-1','23:59:59','-00:00:01.000','-00:00:01.000000','2020-03-01 00:00:01','2020-03-01 00:00:00.000','2020-03-01 00:00:00.000000',timestamp('2025-01-01 00:00:01'),timestamp('2020-03-01 00:00:00.001'),timestamp('2020-03-01 00:00:00.000001'),'1901',b'1','-111111111111111111','9223372036854775807','111111111111111111');
insert into tb1 values (127,255,-32768,0,0,0,-999999999999999999,0,-8388608,16777215,-99999999999999999.99999999999999999999,-9999999999.9999999999,-9999999999.9999999999,0,b'0',b'10111111111',repeat('9', 255),'a',' ',repeat('1', 17),'1970-1-1','00:00:01','00:00:01.001','00:00:01.000001','2025-01-01 00:00:00','2020-03-01 00:00:00.001','2020-03-01 00:00:00.000001',timestamp('2025-01-01 00:00:01'),timestamp('2020-03-01 00:00:01.000'),timestamp('2020-03-01 00:00:01.000000'),'2016',b'1','0','9e99','9e99');
insert into tb1 values (127,255,0,0,2147483647,4294967295,-999999999999999999,18446744073709551615,8388607,0,-9999999999.9999999999,-99999999999999999.99999999999999999999,-99999999999999999,1,b'1',b'10111111111',repeat('9', 17),'0',repeat('1', 17),'','2020-03-01','-00:00:01','-00:00:01.001','-00:00:01.000001','2025-01-01 00:00:01','2020-03-01 00:00:01.000','2020-03-01 00:00:01.000000',timestamp('2025-01-01 00:00:00'),timestamp('2016-12-31 23:59:59.999'),timestamp('2038-01-19 03:14:07.000000'),'1901',b'0','1901','-111111111111111111','0');
insert into tb1 values (-128,0,32767,65535,2147483647,0,-9223372036854775808,0,-8388608,0,-99999999999999999.99999999999999999999,9999999999.9999999999,-99999999999999999.99999999999999999999,1,b'0',b'111111111000000000000',repeat('9', 255),'#',' 高斯aA！@#￥%……&*()—+-=,./<>[]{;:| ',repeat('9', 17),'2016-12-31','838:59:59.000000','-00:00:00.001','-00:00:00.000000','2020-02-29 23:59:59','2025-01-01 00:00:01.000','2016-12-31 23:59:59.000000',timestamp('2025-01-01 00:00:00'),timestamp('2020-02-29 23:59:59.000'),timestamp('2020-03-01 00:00:00.000001'),'2155',b'1','0','0','-111111111111111111');
insert into tb1 values (-128,255,0,65535,0,0,0,18446744073709551615,0,0,-9999999999.9999999999,99999999999999999.99999999999999999999,99999999999999999.99999999999999999999,1,b'0',b'10111111111',repeat('9', 255),'9',repeat('1', 17),' ','2025-01-01','838:59:59.000000','-00:00:00.001','00:00:00.000000','2020-03-01 00:00:01','2024-12-31 23:59:59.999','2016-12-31 23:59:59.000000',timestamp('2025-01-01 00:00:00'),timestamp('2020-03-01 00:00:00.001'),timestamp('2025-01-01 00:00:01.000000'),'2155',b'0','-111111111111111111','1901','');
insert into tb1 values (-128,0,32767,0,0,0,0,0,-8388608,16777215,9999999999.9999999999,-9999999999.9999999999,99999999999999999,0,b'1',b'111111111000000000000',' ','#',repeat('9', 17),repeat('{', 255),'2016-12-31','-23:59:59','00:00:01.001','00:00:01.000000','2020-03-01 00:00:01','2016-12-31 23:59:59.000','2020-02-29 23:59:59.999999',timestamp('2020-03-01 00:00:01'),timestamp('2016-12-31 23:59:59.999'),timestamp('2038-01-19 03:14:07.000000'),'2020',b'1','9e99','9223372036854775807','111111111111111111');
insert into tb1 values (-128,255,-32768,0,2147483647,4294967295,9223372036854775807,999999999999999999,-8388608,0,99999999999999999.99999999999999999999,99999999999999999.99999999999999999999,-99999999999999999,0,b'0',b'111111111000000000000',repeat('1', 17),'9',' 高斯aA！@#￥%……&*()—+-=,./<>[]{;:| ','','2020-03-01','838:59:59.000000','-00:00:01.000','-23:59:59.999999','2020-02-29 23:59:59','2025-01-01 00:00:00.001','2025-01-01 00:00:00.000000',timestamp('2020-02-29 23:59:59'),timestamp('2025-01-01 00:00:01.000'),timestamp('2025-01-01 00:00:00.000001'),'2155',b'1','0','-111111111111111111','1901');
insert into tb1 values (0,255,32767,65535,-2147483648,4294967295,-9223372036854775807,0,8388607,0,0,0,99999999999999999.99999999999999999999,1,b'1',b'111111111000000000000',repeat('9', 255),'0',' 高斯aA！@#￥%……&*()—+-=,./<>[]{;:| ',repeat('{', 255),'1970-1-1','-23:59:59','-00:00:01.000','00:00:01.000001','2020-03-01 00:00:01','2020-03-01 00:00:00.001','2020-03-01 00:00:00.000000',timestamp('2025-01-01 00:00:00'),timestamp('2020-03-01 00:00:00.000'),timestamp('2025-01-01 00:00:00.000000'),'2025',b'0','{}','-838:59:59.000000','111111111111111111');
insert into tb1 values (0,0,32767,65535,0,0,9223372036854775807,0,8388607,16777215,-9999999999.9999999999,-99999999999999999.99999999999999999999,-9999999999.9999999999,1,b'0',b'10111111111',' 高斯aA！@#￥%……&*()—+-=,./<>[]{;:| ','0',' 高斯aA！@#￥%……&*()—+-=,./<>[]{;:| ',repeat('1', 17),'2024-12-31','-00:00:01','-838:59:59','00:00:01.000001','2020-03-01 00:00:00','2025-01-01 00:00:01.000','2024-12-31 23:59:59.999999',timestamp('2025-01-01 00:00:01'),timestamp('2038-01-19 03:14:07.000'),timestamp('2020-03-01 00:00:01.000000'),'2020',b'0','1901','1970-1-1','1901');
insert into tb1 values (-128,255,0,65535,2147483647,0,-9223372036854775808,999999999999999999,8388607,16777215,9999999999.9999999999,99999999999999999,-99999999999999999.99999999999999999999,1,b'0',b'111111111000000000000','','a',repeat('1', 17),'','2025-01-01','-838:59:59.000000','23:59:59.999','838:59:59','2020-03-01 00:00:00','2025-01-01 00:00:00.000','2025-01-01 00:00:00.000001',timestamp('2025-01-01 00:00:01'),timestamp('2020-03-01 00:00:01.000'),timestamp('1970-01-02 00:00:01.000000'),'2020',b'0','0','','-111111111111111111');
insert into tb1 values (0,255,32767,65535,2147483647,4294967295,-999999999999999999,0,0,0,0,0,-99999999999999999.99999999999999999999,0,b'1',b'10111111111',repeat('9', 17),'a','',' 高斯aA！@#￥%……&*()—+-=,./<>[]{;:| ','2020-02-29','-00:00:00','-23:59:59.999','-23:59:59.999999','2025-01-01 00:00:01','2024-12-31 23:59:59.999','2025-01-01 00:00:01.000000',timestamp('2020-03-01 00:00:01'),timestamp('2020-03-01 00:00:00.000'),timestamp('1970-01-02 00:00:01.000000'),'2016',b'1','111111111111111111','-9223372036854775808','1970-1-1');
select case when (tb1.c2 between 0 and tb1.c2) then (tb1.c22) when (tb1.c21 in (tb1.c21,tb1.c21,tb1.c21)) then (regexp_instr(tb1.c14,'20200229235959.00000000000000001',-2147483648,0,tb1.c5)) when (b'0011001000110000001100100011010100101101001100000011000100101101001100000011000100100000001100000011000000111010001100000011000000111010001100000011000000101110001100000011000000110001' is true) then (tb1.c3) end from tb1 order by 1;
select tb1.c5 from tb1;
select case when (tb1.c2 between 0 and tb1.c2) then (tb1.c22) when (tb1.c21 in (tb1.c21,tb1.c21,tb1.c21)) then (regexp_instr(tb1.c14,'20200229235959.00000000000000001',-2147483648,0,tb1.c5)) when (b'0011001000110000001100100011010100101101001100000011000100101101001100000011000100100000001100000011000000111010001100000011000000111010001100000011000000101110001100000011000000110001' is true) then (tb1.c3) end from tb1 order by 1;
Description: MySQL incorrectly validates the return_option argument of REGEXP_INSTR in CASE expression branches that are never executed. When a CASE expression contains multiple WHEN clauses and an earlier condition is always true, MySQL should not evaluate or validate arguments in subsequent branches. However, it throws an error for invalid arguments even when those branches are unreachable. mysql> select case when (tb1.c2 between 0 and tb1.c2) then (tb1.c22) when (tb1.c21 in (tb1.c21,tb1.c21,tb1.c21)) then (regexp_instr(tb1.c14,'20200229235959.00000000000000001',-2147483648,0,tb1.c5)) when (b'0011001000110000001100100011010100101101001100000011000100101101001100000011000100100000001100000011000000111010001100000011000000111010001100000011000000101110001100000011000000110001' is true) then (tb1.c3) end from tb1 order by 1; +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | case when (tb1.c2 between 0 and tb1.c2) then (tb1.c22) when (tb1.c21 in (tb1.c21,tb1.c21,tb1.c21)) then (regexp_instr(tb1.c14,'20200229235959.00000000000000001',-2147483648,0,tb1.c5)) when (b'001100100011000000110010001101010010110100110000001100010010110 | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -00:00:01 | | -00:00:01 | | -00:00:01 | | -23:59:59 | | -23:59:59 | | -23:59:59 | | -838:59:59 | | -838:59:59 | | 00:00:00 | | 00:00:00 | | 00:00:00 | | 00:00:01 | | 00:00:01 | | 23:59:59 | | 23:59:59 | | 23:59:59 | | 838:59:59 | | 838:59:59 | | 838:59:59 | | 838:59:59 | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 20 rows in set (0.00 sec) mysql> select tb1.c5 from tb1; +-------------+ | c5 | +-------------+ | -2147483648 | | -2147483648 | | -2147483648 | | -2147483648 | | 0 | | 0 | | 0 | | 0 | | 0 | | 0 | | 0 | | 2147483647 | | 2147483647 | | 2147483647 | | 2147483647 | | 2147483647 | | 2147483647 | | 2147483647 | | 2147483647 | | 2147483647 | +-------------+ 20 rows in set (0.00 sec) mysql> select case when (tb1.c2 between 0 and tb1.c2) then (tb1.c22) when (tb1.c21 in (tb1.c21,tb1.c21,tb1.c21)) then (regexp_instr(tb1.c14,'20200229235959.00000000000000001',-2147483648,0,tb1.c5)) when (b'0011001000110000001100100011010100101101001100000011000100101101001100000011000100100000001100000011000000111010001100000011000000111010001100000011000000101110001100000011000000110001' is true) then (tb1.c3) end from tb1 order by 1; ERROR 1210 (HY000): Incorrect arguments to regexp_instr: return_option must be 1 or 0. How to repeat: create table tb1 (c1 tinyint, c2 tinyint unsigned, c3 smallint, c4 smallint unsigned, c5 integer, c6 integer unsigned, c7 bigint, c8 bigint unsigned, c9 mediumint, c10 mediumint unsigned, c11 float, c12 double, c13 decimal(60,30), c14 boolean, c15 binary, c16 varbinary(64), c17 blob, c18 char, c19 varchar(64), c20 text, c21 date, c22 time, c23 time(3), c24 time(6), c25 datetime, c26 datetime(3), c27 datetime(6), c28 timestamp, c29 timestamp(3), c30 timestamp(6), c31 year, c32 bit, c33 json, c34 enum('1','abc','@#$',' ','111111111111111111','-111111111111111111','1970-1-1','-838:59:59.000000','1901','-9223372036854775808','0','9223372036854775807','-9223372036854775807','9e99'), c35 set('1','abc','@#$',' ','111111111111111111','-111111111111111111','1970-1-1','-838:59:59.000000','1901','0','9e99')) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ; insert into tb1 values (-128,0,-32768,0,-2147483648,0,-9223372036854775808,0,-8388608,0,-9999999999.9999999999,-9999999999.9999999999,-9999999999.9999999999,0,b'0',b'10111111111',' 高斯aA！@#￥%……&*()—+-=,./<>[]{;:| ','a',' 高斯aA！@#￥%……&*()—+-=,./<>[]{;:| ',' 高斯aA！@#￥%……&*()—+-=,./<>[]{;:| ','1970-1-1','-838:59:59.000000','-838:59:59','-838:59:59','1000-01-01 00:00:00','1000-01-01 00:00:00.000','1000-01-01 00:00:00.000000',timestamp('1970-01-02 00:00:01'),timestamp('1970-01-02 00:00:01.000'),timestamp('1970-01-02 00:00:01.000000'),'1901',b'0','{}','',''); insert into tb1 values (0,255,0,65535,0,4294967295,0,18446744073709551615,0,16777215,0,0,0,1,b'1',b'111111111000000000000','','#','','','2069-12-31','838:59:59.000000','838:59:59','838:59:59','1000-01-01 00:00:01','1000-01-01 00:00:00.001','1000-01-01 00:00:00.000001',timestamp('2038-01-19 03:14:07'),timestamp('2038-01-19 03:14:07.000'),timestamp('2038-01-19 03:14:07.000000'),'2155',b'1','111111111111111111',' ','111111111111111111'); insert into tb1 values (127,0,32767,65535,2147483647,0,9223372036854775807,999999999999999999,8388607,0,9999999999.9999999999,9999999999.9999999999,9999999999.9999999999,1,b'0',b'111111111000000000000',' ','0',' ',' ','2024-12-31','00:00:00','00:00:00.000','00:00:00.000000','9999-12-31 23:59:59','9999-12-31 23:59:59.999','9999-12-31 23:59:59.999999',timestamp('2024-12-31 23:59:59'),timestamp('2024-12-31 23:59:59.999'),timestamp('2024-12-31 23:59:59.999999'),'2024',b'0','-111111111111111111','111111111111111111','-111111111111111111'); insert into tb1 values (-128,0,-32768,0,2147483647,4294967295,999999999999999999,0,8388607,16777215,-99999999999999999.99999999999999999999,-99999999999999999.99999999999999999999,-99999999999999999.99999999999999999999,0,b'1',b'10111111111',repeat('{', 255),'1',repeat('9', 17),repeat('{', 255),'2025-01-01','-00:00:00','-00:00:00.000','-00:00:00.000000','2024-12-31 23:59:59','2024-12-31 23:59:59.999','2024-12-31 23:59:59.999999',timestamp('2025-01-01 00:00:00'),timestamp('2025-01-01 00:00:00.000'),timestamp('2025-01-01 00:00:00.000000'),'2025',b'1','1901','-111111111111111111','1970-1-1'); insert into tb1 values (-128,0,0,65535,2147483647,4294967295,-999999999999999999,18446744073709551615,8388607,0,99999999999999999.99999999999999999999,99999999999999999.99999999999999999999,99999999999999999.99999999999999999999,1,b'0',b'111111111000000000000',repeat('9', 255),'9',repeat('1', 17),repeat('9', 255),'2016-12-31','23:59:59','00:00:00.001','00:00:00.000001','2025-01-01 00:00:00','2025-01-01 00:00:00.000','2025-01-01 00:00:00.000000',timestamp('2025-01-01 00:00:01'),timestamp('2025-01-01 00:00:00.001'),timestamp('2025-01-01 00:00:00.000001'),'2016',b'1','0','1970-1-1','-838:59:59.000000'); insert into tb1 values (-128,0,0,0,0,4294967295,-9223372036854775807,18446744073709551615,8388607,0,99999999999999999.99999999999999999999,99999999999999999,99999999999999999,0,b'1',b'10111111111',repeat('9', 17),'0',' ',repeat('9', 17),'2020-02-29','-23:59:59','-00:00:00.001','-00:00:00.000001','2025-01-01 00:00:01','2025-01-01 00:00:00.001','2025-01-01 00:00:00.000001',timestamp('2016-12-31 23:59:59'),timestamp('2025-01-01 00:00:01.000'),timestamp('2025-01-01 00:00:01.000000'),'2020',b'0','9e99','-838:59:59.000000','1901'); insert into tb1 values (0,255,-32768,65535,-2147483648,4294967295,999999999999999999,0,-8388608,0,9999999999.9999999999,-99999999999999999,-99999999999999999,1,b'1',b'111111111000000000000',repeat('1', 255),'#',repeat('9', 17),repeat('1', 255),'2020-03-01','00:00:01','23:59:59.999','23:59:59.999999','2016-12-31 23:59:59','2025-01-01 00:00:01.000','2025-01-01 00:00:01.000000',timestamp('2020-02-29 23:59:59'),timestamp('2016-12-31 23:59:59.999'),timestamp('2016-12-31 23:59:59.999999'),'2155',b'0','0','1901','0'); insert into tb1 values (-128,0,-32768,0,2147483647,4294967295,999999999999999999,999999999999999999,0,0,99999999999999999.99999999999999999999,-9999999999.9999999999,9999999999.9999999999,1,b'1',b'10111111111',repeat('1', 17),'a',repeat('1', 17),repeat('1', 17),'2025-01-01','-00:00:01','-23:59:59.999','-23:59:59.999999','2020-02-29 23:59:59','2016-12-31 23:59:59.000','2016-12-31 23:59:59.000000',timestamp('2020-03-01 00:00:00'),timestamp('2020-02-29 23:59:59.000'),timestamp('2020-02-29 23:59:59.000000'),'2155',b'1','111111111111111111','-9223372036854775808','9e99'); insert into tb1 values (-128,0,-32768,65535,0,4294967295,-999999999999999999,0,0,16777215,0,99999999999999999,-99999999999999999,1,b'1',b'111111111000000000000',repeat('1', 17),'a',' 高斯aA！@#￥%……&*()—+-=,./<>[]{;:| ',repeat('9', 17),'2024-12-31','23:59:59','00:00:01.000','00:00:01.000000','2020-03-01 00:00:00','2020-02-29 23:59:59.999','2020-02-29 23:59:59.999999',timestamp('2020-03-01 00:00:01'),timestamp('2020-03-01 00:00:00.000'),timestamp('2020-03-01 00:00:00.000000'),'2024',b'0','9e99','0',''); insert into tb1 values (-128,0,-32768,65535,-2147483648,4294967295,999999999999999999,999999999999999999,8388607,0,0,-9999999999.9999999999,99999999999999999,1,b'0',b'10111111111',repeat('9', 255),'#','',repeat('9', 255),'1970-1-1','23:59:59','-00:00:01.000','-00:00:01.000000','2020-03-01 00:00:01','2020-03-01 00:00:00.000','2020-03-01 00:00:00.000000',timestamp('2025-01-01 00:00:01'),timestamp('2020-03-01 00:00:00.001'),timestamp('2020-03-01 00:00:00.000001'),'1901',b'1','-111111111111111111','9223372036854775807','111111111111111111'); insert into tb1 values (127,255,-32768,0,0,0,-999999999999999999,0,-8388608,16777215,-99999999999999999.99999999999999999999,-9999999999.9999999999,-9999999999.9999999999,0,b'0',b'10111111111',repeat('9', 255),'a',' ',repeat('1', 17),'1970-1-1','00:00:01','00:00:01.001','00:00:01.000001','2025-01-01 00:00:00','2020-03-01 00:00:00.001','2020-03-01 00:00:00.000001',timestamp('2025-01-01 00:00:01'),timestamp('2020-03-01 00:00:01.000'),timestamp('2020-03-01 00:00:01.000000'),'2016',b'1','0','9e99','9e99'); insert into tb1 values (127,255,0,0,2147483647,4294967295,-999999999999999999,18446744073709551615,8388607,0,-9999999999.9999999999,-99999999999999999.99999999999999999999,-99999999999999999,1,b'1',b'10111111111',repeat('9', 17),'0',repeat('1', 17),'','2020-03-01','-00:00:01','-00:00:01.001','-00:00:01.000001','2025-01-01 00:00:01','2020-03-01 00:00:01.000','2020-03-01 00:00:01.000000',timestamp('2025-01-01 00:00:00'),timestamp('2016-12-31 23:59:59.999'),timestamp('2038-01-19 03:14:07.000000'),'1901',b'0','1901','-111111111111111111','0'); insert into tb1 values (-128,0,32767,65535,2147483647,0,-9223372036854775808,0,-8388608,0,-99999999999999999.99999999999999999999,9999999999.9999999999,-99999999999999999.99999999999999999999,1,b'0',b'111111111000000000000',repeat('9', 255),'#',' 高斯aA！@#￥%……&*()—+-=,./<>[]{;:| ',repeat('9', 17),'2016-12-31','838:59:59.000000','-00:00:00.001','-00:00:00.000000','2020-02-29 23:59:59','2025-01-01 00:00:01.000','2016-12-31 23:59:59.000000',timestamp('2025-01-01 00:00:00'),timestamp('2020-02-29 23:59:59.000'),timestamp('2020-03-01 00:00:00.000001'),'2155',b'1','0','0','-111111111111111111'); insert into tb1 values (-128,255,0,65535,0,0,0,18446744073709551615,0,0,-9999999999.9999999999,99999999999999999.99999999999999999999,99999999999999999.99999999999999999999,1,b'0',b'10111111111',repeat('9', 255),'9',repeat('1', 17),' ','2025-01-01','838:59:59.000000','-00:00:00.001','00:00:00.000000','2020-03-01 00:00:01','2024-12-31 23:59:59.999','2016-12-31 23:59:59.000000',timestamp('2025-01-01 00:00:00'),timestamp('2020-03-01 00:00:00.001'),timestamp('2025-01-01 00:00:01.000000'),'2155',b'0','-111111111111111111','1901',''); insert into tb1 values (-128,0,32767,0,0,0,0,0,-8388608,16777215,9999999999.9999999999,-9999999999.9999999999,99999999999999999,0,b'1',b'111111111000000000000',' ','#',repeat('9', 17),repeat('{', 255),'2016-12-31','-23:59:59','00:00:01.001','00:00:01.000000','2020-03-01 00:00:01','2016-12-31 23:59:59.000','2020-02-29 23:59:59.999999',timestamp('2020-03-01 00:00:01'),timestamp('2016-12-31 23:59:59.999'),timestamp('2038-01-19 03:14:07.000000'),'2020',b'1','9e99','9223372036854775807','111111111111111111'); insert into tb1 values (-128,255,-32768,0,2147483647,4294967295,9223372036854775807,999999999999999999,-8388608,0,99999999999999999.99999999999999999999,99999999999999999.99999999999999999999,-99999999999999999,0,b'0',b'111111111000000000000',repeat('1', 17),'9',' 高斯aA！@#￥%……&*()—+-=,./<>[]{;:| ','','2020-03-01','838:59:59.000000','-00:00:01.000','-23:59:59.999999','2020-02-29 23:59:59','2025-01-01 00:00:00.001','2025-01-01 00:00:00.000000',timestamp('2020-02-29 23:59:59'),timestamp('2025-01-01 00:00:01.000'),timestamp('2025-01-01 00:00:00.000001'),'2155',b'1','0','-111111111111111111','1901'); insert into tb1 values (0,255,32767,65535,-2147483648,4294967295,-9223372036854775807,0,8388607,0,0,0,99999999999999999.99999999999999999999,1,b'1',b'111111111000000000000',repeat('9', 255),'0',' 高斯aA！@#￥%……&*()—+-=,./<>[]{;:| ',repeat('{', 255),'1970-1-1','-23:59:59','-00:00:01.000','00:00:01.000001','2020-03-01 00:00:01','2020-03-01 00:00:00.001','2020-03-01 00:00:00.000000',timestamp('2025-01-01 00:00:00'),timestamp('2020-03-01 00:00:00.000'),timestamp('2025-01-01 00:00:00.000000'),'2025',b'0','{}','-838:59:59.000000','111111111111111111'); insert into tb1 values (0,0,32767,65535,0,0,9223372036854775807,0,8388607,16777215,-9999999999.9999999999,-99999999999999999.99999999999999999999,-9999999999.9999999999,1,b'0',b'10111111111',' 高斯aA！@#￥%……&*()—+-=,./<>[]{;:| ','0',' 高斯aA！@#￥%……&*()—+-=,./<>[]{;:| ',repeat('1', 17),'2024-12-31','-00:00:01','-838:59:59','00:00:01.000001','2020-03-01 00:00:00','2025-01-01 00:00:01.000','2024-12-31 23:59:59.999999',timestamp('2025-01-01 00:00:01'),timestamp('2038-01-19 03:14:07.000'),timestamp('2020-03-01 00:00:01.000000'),'2020',b'0','1901','1970-1-1','1901'); insert into tb1 values (-128,255,0,65535,2147483647,0,-9223372036854775808,999999999999999999,8388607,16777215,9999999999.9999999999,99999999999999999,-99999999999999999.99999999999999999999,1,b'0',b'111111111000000000000','','a',repeat('1', 17),'','2025-01-01','-838:59:59.000000','23:59:59.999','838:59:59','2020-03-01 00:00:00','2025-01-01 00:00:00.000','2025-01-01 00:00:00.000001',timestamp('2025-01-01 00:00:01'),timestamp('2020-03-01 00:00:01.000'),timestamp('1970-01-02 00:00:01.000000'),'2020',b'0','0','','-111111111111111111'); insert into tb1 values (0,255,32767,65535,2147483647,4294967295,-999999999999999999,0,0,0,0,0,-99999999999999999.99999999999999999999,0,b'1',b'10111111111',repeat('9', 17),'a','',' 高斯aA！@#￥%……&*()—+-=,./<>[]{;:| ','2020-02-29','-00:00:00','-23:59:59.999','-23:59:59.999999','2025-01-01 00:00:01','2024-12-31 23:59:59.999','2025-01-01 00:00:01.000000',timestamp('2020-03-01 00:00:01'),timestamp('2020-03-01 00:00:00.000'),timestamp('1970-01-02 00:00:01.000000'),'2016',b'1','111111111111111111','-9223372036854775808','1970-1-1'); select case when (tb1.c2 between 0 and tb1.c2) then (tb1.c22) when (tb1.c21 in (tb1.c21,tb1.c21,tb1.c21)) then (regexp_instr(tb1.c14,'20200229235959.00000000000000001',-2147483648,0,tb1.c5)) when (b'0011001000110000001100100011010100101101001100000011000100101101001100000011000100100000001100000011000000111010001100000011000000111010001100000011000000101110001100000011000000110001' is true) then (tb1.c3) end from tb1 order by 1; select tb1.c5 from tb1; select case when (tb1.c2 between 0 and tb1.c2) then (tb1.c22) when (tb1.c21 in (tb1.c21,tb1.c21,tb1.c21)) then (regexp_instr(tb1.c14,'20200229235959.00000000000000001',-2147483648,0,tb1.c5)) when (b'0011001000110000001100100011010100101101001100000011000100101101001100000011000100100000001100000011000000111010001100000011000000111010001100000011000000101110001100000011000000110001' is true) then (tb1.c3) end from tb1 order by 1;