3338 lines
118 KiB
Plaintext
3338 lines
118 KiB
Plaintext
drop table if exists t1, t2;
|
|
create table t1 (b enum('a', 'b'));
|
|
insert into t1 values ('a');
|
|
create table t2 (b enum('b','a') not null, unique(b));
|
|
insert into t2 values ('a');
|
|
select /*+ inl_join(t2)*/ * from t1, t2 where t1.b=t2.b;
|
|
b b
|
|
a a
|
|
drop table if exists t1, t2;
|
|
create table t1 (b set('a', 'b'));
|
|
insert into t1 values ('a');
|
|
create table t2 (b set('b','a') not null, unique(b));
|
|
insert into t2 values ('a');
|
|
select /*+ inl_join(t2)*/ * from t1, t2 where t1.b=t2.b;
|
|
b b
|
|
a a
|
|
drop table if exists t1, t2;
|
|
create table t1 (b enum('a', 'b'));
|
|
insert into t1 values ('a');
|
|
create table t2 (b set('b','a') not null, unique(b));
|
|
insert into t2 values ('a');
|
|
select /*+ inl_join(t2)*/ * from t1, t2 where t1.b=t2.b;
|
|
b b
|
|
a a
|
|
drop table if exists t1, t2;
|
|
create table t1 (b char(10));
|
|
insert into t1 values ('a');
|
|
create table t2 (b enum('b','a') not null, unique(b));
|
|
insert into t2 values ('a');
|
|
select /*+ inl_join(t2)*/ * from t1, t2 where t1.b=t2.b;
|
|
b b
|
|
a a
|
|
drop table if exists t1, t2;
|
|
create table t1 (b char(10));
|
|
insert into t1 values ('a');
|
|
create table t2 (b set('b','a') not null, unique(b));
|
|
insert into t2 values ('a');
|
|
select /*+ inl_join(t2)*/ * from t1, t2 where t1.b=t2.b;
|
|
b b
|
|
a a
|
|
drop table if exists t;
|
|
create table t(a decimal(16, 2));
|
|
select sum(case when 1 then a end) from t group by a;
|
|
sum(case when 1 then a end)
|
|
show create table t;
|
|
Table Create Table
|
|
t CREATE TABLE `t` (
|
|
`a` decimal(16,2) DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
|
|
drop table if exists t;
|
|
create table t(a tinyint, b bit(63));
|
|
insert ignore into t values(599999999, -1);
|
|
Level Code Message
|
|
Warning 1264 Out of range value for column 'a' at row 1
|
|
Warning 1406 Data too long for column 'b' at row 1
|
|
select hex(a), hex(b) from t;
|
|
hex(a) hex(b)
|
|
7F 7FFFFFFFFFFFFFFF
|
|
drop table if exists t;
|
|
create table t(b bit(16));
|
|
insert ignore into t values(0x3635313836),(0x333830);
|
|
Level Code Message
|
|
Warning 1406 Data too long for column 'b' at row 1
|
|
Warning 1406 Data too long for column 'b' at row 2
|
|
select hex(b) from t;
|
|
hex(b)
|
|
FFFF
|
|
FFFF
|
|
drop table if exists t;
|
|
create table t(a char(32) not null, b float default '0') engine=innodb default charset=utf8mb4;
|
|
insert into t value('0a6f9d012f98467f8e671e9870044528', 208.867);
|
|
select concat_ws( ',', b) from t where a = '0a6f9d012f98467f8e671e9870044528';
|
|
concat_ws( ',', b)
|
|
208.867
|
|
drop table if exists t;
|
|
CREATE TABLE t (b BIT(8));
|
|
INSERT INTO t SET b = b'11111111';
|
|
INSERT INTO t SET b = b'1010';
|
|
INSERT INTO t SET b = b'0101';
|
|
SELECT b+0, BIN(b), OCT(b), HEX(b) FROM t;
|
|
b+0 BIN(b) OCT(b) HEX(b)
|
|
255 11111111 377 FF
|
|
10 1010 12 A
|
|
5 101 5 5
|
|
drop table if exists t;
|
|
create table t(a char(20));
|
|
select convert(a using a) from t;
|
|
Error 1115 (42000): Unknown character set: 'a'
|
|
drop table if exists t1;
|
|
create table t1(c1 varbinary(100));
|
|
insert into t1 values('abc');
|
|
select 1 from t1 where char_length(c1) = 10;
|
|
1
|
|
drop table if exists t;
|
|
create table t (id int NOT NULL DEFAULT 8);
|
|
SET sql_mode = '';
|
|
insert into t values (1), (NULL), (2);
|
|
Level Code Message
|
|
Warning 1048 Column 'id' cannot be null
|
|
select * from t;
|
|
id
|
|
1
|
|
0
|
|
2
|
|
set @@sql_mode=default;
|
|
drop table if exists t;
|
|
create table t(a json);
|
|
insert into t values ('"a"'), ('"B"'), ('"c"'), ('"D"'), ('{"a": 1}'), ('1'), ('{"b": 2}'), ('[1, 2]'), ('[3, 4]');
|
|
select min(a) from t;
|
|
min(a)
|
|
1
|
|
select max(a) from t;
|
|
max(a)
|
|
[3, 4]
|
|
select min(a collate utf8mb4_bin) from t;
|
|
min(a collate utf8mb4_bin)
|
|
"B"
|
|
select max(a collate utf8mb4_bin) from t;
|
|
max(a collate utf8mb4_bin)
|
|
{"b": 2}
|
|
select min(a collate utf8mb4_unicode_ci) from t;
|
|
min(a collate utf8mb4_unicode_ci)
|
|
"a"
|
|
select max(a collate utf8mb4_unicode_ci) from t;
|
|
max(a collate utf8mb4_unicode_ci)
|
|
1
|
|
SET timestamp=UNIX_TIMESTAMP('2011-11-01 17:48:00');
|
|
SELECT cast(cast('12:12:12' as time) as datetime(6));
|
|
cast(cast('12:12:12' as time) as datetime(6))
|
|
2011-11-01 12:12:12.000000
|
|
drop table if exists t;
|
|
create table t (tm time(6));
|
|
insert into t values('23:59:59');
|
|
SELECT cast(tm as datetime(6)) from t;
|
|
cast(tm as datetime(6))
|
|
2011-11-01 23:59:59.000000
|
|
drop table if exists `PK_S_MULTI_43`;
|
|
CREATE TABLE `PK_S_MULTI_43`(`COL1` time(2) NOT NULL, `COL2` time(2) NOT NULL, `COL3` time(2) DEFAULT NULL, PRIMARY KEY(`COL1`,`COL2`));
|
|
insert into PK_S_MULTI_43(col1, col2) values('-512:37:22.00', '-512:37:22.00');
|
|
select extract(day_microsecond from '-512:37:22.00');
|
|
extract(day_microsecond from '-512:37:22.00')
|
|
-5123722000000
|
|
select extract(day_microsecond from col1) from PK_S_MULTI_43;
|
|
extract(day_microsecond from col1)
|
|
-5123722000000
|
|
drop table if exists t;
|
|
create table t(c datetime(6));
|
|
insert into t values('2001-01-01 02:03:04.050607');
|
|
select extract(day_microsecond from cast('2001-01-01 02:03:04.050607' as datetime(6))) from t;
|
|
extract(day_microsecond from cast('2001-01-01 02:03:04.050607' as datetime(6)))
|
|
1020304050607
|
|
select extract(day_microsecond from c) from t;
|
|
extract(day_microsecond from c)
|
|
1020304050607
|
|
drop table if exists ft;
|
|
create table ft (tint int, tdou double, tdec decimal(22,9),tchar char(44));
|
|
insert into ft values(1234567890,123467890.1234,123467890.1234,'123467890.1234');
|
|
insert into ft values(1234567890,123467890.123456789,123467890.123456789,'123467890.123456789');
|
|
SELECT FROM_UNIXTIME(tchar) from ft;
|
|
FROM_UNIXTIME(tchar)
|
|
1973-11-30 08:38:10.123400
|
|
1973-11-30 08:38:10.123457
|
|
drop table if exists ft;
|
|
create table ft (tint int, tdou double, tdec decimal(22,9),tchar varchar(44));
|
|
insert into ft values(1234567890,123467890.1234,123467890.1234,'123467890.1234');
|
|
insert into ft values(1234567890,123467890.123456789,123467890.123456789,'123467890.123456789');
|
|
SELECT FROM_UNIXTIME(tchar) from ft;
|
|
FROM_UNIXTIME(tchar)
|
|
1973-11-30 08:38:10.123400
|
|
1973-11-30 08:38:10.123457
|
|
drop table if exists ft;
|
|
create table ft (tint int, tdou double, tdec decimal(22,9),tchar blob);
|
|
insert into ft values(1234567890,123467890.1234,123467890.1234,'123467890.1234');
|
|
insert into ft values(1234567890,123467890.123456789,123467890.123456789,'123467890.123456789');
|
|
SELECT FROM_UNIXTIME(tchar) from ft;
|
|
FROM_UNIXTIME(tchar)
|
|
1973-11-30 08:38:10.123400
|
|
1973-11-30 08:38:10.123457
|
|
drop table if exists ft;
|
|
create table ft (tint int, tdou double, tdec decimal(22,9),tchar tinyblob);
|
|
insert into ft values(1234567890,123467890.1234,123467890.1234,'123467890.1234');
|
|
insert into ft values(1234567890,123467890.123456789,123467890.123456789,'123467890.123456789');
|
|
SELECT FROM_UNIXTIME(tchar) from ft;
|
|
FROM_UNIXTIME(tchar)
|
|
1973-11-30 08:38:10.123400
|
|
1973-11-30 08:38:10.123457
|
|
drop table if exists ft;
|
|
create table ft (tint int, tdou double, tdec decimal(22,9),tchar mediumblob);
|
|
insert into ft values(1234567890,123467890.1234,123467890.1234,'123467890.1234');
|
|
insert into ft values(1234567890,123467890.123456789,123467890.123456789,'123467890.123456789');
|
|
SELECT FROM_UNIXTIME(tchar) from ft;
|
|
FROM_UNIXTIME(tchar)
|
|
1973-11-30 08:38:10.123400
|
|
1973-11-30 08:38:10.123457
|
|
drop table if exists ft;
|
|
create table ft (tint int, tdou double, tdec decimal(22,9),tchar longblob);
|
|
insert into ft values(1234567890,123467890.1234,123467890.1234,'123467890.1234');
|
|
insert into ft values(1234567890,123467890.123456789,123467890.123456789,'123467890.123456789');
|
|
SELECT FROM_UNIXTIME(tchar) from ft;
|
|
FROM_UNIXTIME(tchar)
|
|
1973-11-30 08:38:10.123400
|
|
1973-11-30 08:38:10.123457
|
|
truncate table ft;
|
|
insert into ft values(1234567890,123467890.1234,123467890.1234,'123467890.1234000000000000000000100111111111');
|
|
SELECT FROM_UNIXTIME(tchar) from ft;
|
|
FROM_UNIXTIME(tchar)
|
|
1973-11-30 08:38:10.123400
|
|
Level Code Message
|
|
Warning 1292 Truncated incorrect DECIMAL value: '123467890.1234000000000000000000100111111111'
|
|
truncate table ft;
|
|
insert into ft values(1234567890,123467890.1234,123467890.1234,'11111123467890.1234');
|
|
SELECT FROM_UNIXTIME(tchar) from ft;
|
|
FROM_UNIXTIME(tchar)
|
|
NULL
|
|
drop table if exists ft;
|
|
create table ft (tint int, tdou double, tdec decimal(22,9),tchar char(44));
|
|
insert into ft values(1234567890,123467890.1234,123467890.1234,'123467890.1234');
|
|
SELECT FROM_UNIXTIME(tchar) from ft where FROM_UNIXTIME(tchar)= '1973-11-30 08:38:10.123400' ;
|
|
FROM_UNIXTIME(tchar)
|
|
1973-11-30 08:38:10.123400
|
|
SELECT FROM_UNIXTIME(cast(tchar as decimal(44,1))) from ft where FROM_UNIXTIME(tchar)= '1973-11-30 08:38:10.123400' ;
|
|
FROM_UNIXTIME(cast(tchar as decimal(44,1)))
|
|
1973-11-30 08:38:10.1
|
|
SELECT FROM_UNIXTIME(tchar,'%Y%m%d') from ft where FROM_UNIXTIME(tchar)= '1973-11-30 08:38:10.123400' ;
|
|
FROM_UNIXTIME(tchar,'%Y%m%d')
|
|
19731130
|
|
drop table if exists t;
|
|
create table t(dou double, varc varchar(100));
|
|
insert into t values (1.23e23, '111111111111111111111111111111111111111111111111111111111111111111111111111');
|
|
select from_unixtime(dou) from t;
|
|
from_unixtime(dou)
|
|
NULL
|
|
select from_unixtime(varc) from t;
|
|
from_unixtime(varc)
|
|
NULL
|
|
select from_unixtime(dou, '%Y-%m-%d') from t;
|
|
from_unixtime(dou, '%Y-%m-%d')
|
|
NULL
|
|
select from_unixtime(varc, '%Y-%m-%d') from t;
|
|
from_unixtime(varc, '%Y-%m-%d')
|
|
NULL
|
|
drop table if exists `sun`;
|
|
CREATE TABLE `sun` ( `dest` varchar(10) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
|
|
insert into sun values('20231020');
|
|
set @@sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
|
|
set @@tidb_enable_vectorized_expression = on;
|
|
select str_to_date(substr(dest,1,6),'%H%i%s') from sun;
|
|
str_to_date(substr(dest,1,6),'%H%i%s')
|
|
20:23:10
|
|
set @@tidb_enable_vectorized_expression = off;
|
|
select str_to_date(substr(dest,1,6),'%H%i%s') from sun;
|
|
str_to_date(substr(dest,1,6),'%H%i%s')
|
|
20:23:10
|
|
set @@sql_mode=default;
|
|
drop table if exists `6bf9e76d-ab44-4031-8a07-418b10741580`, `8919f3f4-25be-4a1a-904a-bb5e863d8fc8`;
|
|
CREATE TABLE `6bf9e76d-ab44-4031-8a07-418b10741580` (
|
|
`e0b5f703-6cfe-49b4-bc21-16a6455e43a7` set('7','va','ung60','ow','1g','gxwz5','uhnh','k','5la1','q8d9c','1f') NOT NULL DEFAULT '7,1g,uhnh,5la1,q8d9c',
|
|
`fbc3527f-9617-4b9d-a5dc-4be31c00d8a5` datetime DEFAULT '6449-09-28 14:39:04',
|
|
PRIMARY KEY (`e0b5f703-6cfe-49b4-bc21-16a6455e43a7`) /*T![clustered_index] CLUSTERED */
|
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
|
|
CREATE TABLE `8919f3f4-25be-4a1a-904a-bb5e863d8fc8` (
|
|
`9804d5f2-cbc7-43b7-b241-ea2656dc941a` enum('s951','36d','ua65','49yru','6l2em','4ea','jf2d2','vprsc','3yl7n','hz','ov') DEFAULT '4ea',
|
|
`323cdbcb-0c14-4362-90ab-ea42caaed6a5` year(4) NOT NULL DEFAULT '1983',
|
|
`b9b70f39-1a02-4114-9d7d-fa6259c1b691` time DEFAULT '20:18:04',
|
|
PRIMARY KEY (`323cdbcb-0c14-4362-90ab-ea42caaed6a5`) /*T![clustered_index] CLUSTERED */,
|
|
KEY `a704d6bb-772b-44ea-8cb0-6f7491c1aaa6` (`323cdbcb-0c14-4362-90ab-ea42caaed6a5`,`9804d5f2-cbc7-43b7-b241-ea2656dc941a`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=ascii COLLATE=ascii_bin;
|
|
delete from `6bf9e76d-ab44-4031-8a07-418b10741580` where not( `6bf9e76d-ab44-4031-8a07-418b10741580`.`e0b5f703-6cfe-49b4-bc21-16a6455e43a7` in ( select `9804d5f2-cbc7-43b7-b241-ea2656dc941a` from `8919f3f4-25be-4a1a-904a-bb5e863d8fc8` where `6bf9e76d-ab44-4031-8a07-418b10741580`.`e0b5f703-6cfe-49b4-bc21-16a6455e43a7` in ( '1f' ) and `6bf9e76d-ab44-4031-8a07-418b10741580`.`e0b5f703-6cfe-49b4-bc21-16a6455e43a7` in ( '1g' ,'va' ,'uhnh' ) ) ) and not( IsNull( `6bf9e76d-ab44-4031-8a07-418b10741580`.`e0b5f703-6cfe-49b4-bc21-16a6455e43a7` ) );
|
|
drop table if exists test;
|
|
create table test (name1 blob,name2 blob);
|
|
insert into test values(aes_encrypt('a', 'x'), aes_encrypt('b', 'x'));
|
|
SELECT * FROM test WHERE CAST(AES_DECRYPT(name1, 'x') AS CHAR) = '00' AND CAST(AES_DECRYPT(name2, 'x') AS CHAR) = '1';
|
|
name1 name2
|
|
drop table if exists ordres;
|
|
CREATE TABLE orders (id bigint(20) unsigned NOT NULL ,account_id bigint(20) unsigned NOT NULL DEFAULT '0' ,loan bigint(20) unsigned NOT NULL DEFAULT '0' ,stage_num int(20) unsigned NOT NULL DEFAULT '0' ,apply_time bigint(20) unsigned NOT NULL DEFAULT '0' ,PRIMARY KEY (id) /*T![clustered_index] CLUSTERED */,KEY idx_orders_account_id (account_id),KEY idx_orders_apply_time (apply_time));
|
|
insert into orders values (20, 210802010000721168, 20000 , 2 , 1682484268727), (22, 210802010000721168, 35100 , 4 , 1650885615002);
|
|
select min(if(apply_to_now_days <= 30,loan,null)) as min, max(if(apply_to_now_days <= 720,loan,null)) as max from (select loan, datediff(from_unixtime(unix_timestamp('2023-05-18 18:43:43') + 18000), from_unixtime(apply_time/1000 + 18000)) as apply_to_now_days from orders) t1;
|
|
min max
|
|
20000 35100
|
|
drop table if exists t_tiny, t_small, t_medium, t_int, t_big;
|
|
create table t_tiny (c0 TINYINT UNSIGNED);
|
|
INSERT IGNORE INTO t_tiny(c0) VALUES (1E9);
|
|
Level Code Message
|
|
Warning 1264 Out of range value for column 'c0' at row 1
|
|
select * from t_tiny;
|
|
c0
|
|
255
|
|
create table t_small (c0 SMALLINT UNSIGNED);
|
|
INSERT IGNORE INTO t_small(c0) VALUES (1E9);
|
|
Level Code Message
|
|
Warning 1264 Out of range value for column 'c0' at row 1
|
|
select * from t_small;
|
|
c0
|
|
65535
|
|
create table t_medium (c0 MEDIUMINT UNSIGNED);
|
|
INSERT IGNORE INTO t_medium(c0) VALUES (1E9);
|
|
Level Code Message
|
|
Warning 1264 Out of range value for column 'c0' at row 1
|
|
select * from t_medium;
|
|
c0
|
|
16777215
|
|
create table t_int (c0 INT UNSIGNED);
|
|
INSERT IGNORE INTO t_int(c0) VALUES (1E20);
|
|
Level Code Message
|
|
Warning 1264 Out of range value for column 'c0' at row 1
|
|
select * from t_int;
|
|
c0
|
|
4294967295
|
|
create table t_big (c0 BIGINT UNSIGNED);
|
|
INSERT IGNORE INTO t_big(c0) VALUES (1E20);
|
|
Level Code Message
|
|
Warning 1264 Out of range value for column 'c0' at row 1
|
|
select * from t_big;
|
|
c0
|
|
18446744073709551615
|
|
drop table if exists t;
|
|
CREATE TABLE t (a bigint unsigned,key (a));
|
|
INSERT INTO t VALUES (0), (4), (5), (6), (7), (8), (9223372036854775810), (18446744073709551614), (18446744073709551615);
|
|
SELECT a FROM t WHERE a NOT IN (-1, -2, 18446744073709551615);
|
|
a
|
|
0
|
|
4
|
|
5
|
|
6
|
|
7
|
|
8
|
|
9223372036854775810
|
|
18446744073709551614
|
|
18446744073709551615
|
|
SELECT a FROM t WHERE a NOT IN (-1, -2, 4, 9223372036854775810);
|
|
a
|
|
0
|
|
5
|
|
6
|
|
7
|
|
8
|
|
18446744073709551614
|
|
18446744073709551615
|
|
SELECT a FROM t WHERE a NOT IN (-1, -2, 0, 4, 18446744073709551614);
|
|
a
|
|
5
|
|
6
|
|
7
|
|
8
|
|
9223372036854775810
|
|
18446744073709551614
|
|
18446744073709551615
|
|
drop table if exists t1;
|
|
create table t1 (some_id smallint(5) unsigned,key (some_id) );
|
|
insert into t1 values (1),(2);
|
|
select some_id from t1 where some_id not in(2,-1);
|
|
some_id
|
|
1
|
|
select monthname(str_to_date(null, '%m')), monthname(str_to_date(null, '%m')), monthname(str_to_date(1, '%m')), monthname(str_to_date(0, '%m'));
|
|
monthname(str_to_date(null, '%m')) monthname(str_to_date(null, '%m')) monthname(str_to_date(1, '%m')) monthname(str_to_date(0, '%m'))
|
|
NULL NULL NULL NULL
|
|
select str_to_date(1, '%m');
|
|
str_to_date(1, '%m')
|
|
NULL
|
|
select str_to_date(01, '%d');
|
|
str_to_date(01, '%d')
|
|
NULL
|
|
select str_to_date(2019, '%Y');
|
|
str_to_date(2019, '%Y')
|
|
NULL
|
|
select str_to_date('5,2019','%m,%Y');
|
|
str_to_date('5,2019','%m,%Y')
|
|
NULL
|
|
select str_to_date('01,2019','%d,%Y');
|
|
str_to_date('01,2019','%d,%Y')
|
|
NULL
|
|
select str_to_date('01,5','%d,%m');
|
|
str_to_date('01,5','%d,%m')
|
|
NULL
|
|
set sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
|
|
select str_to_date(1, '%m');
|
|
str_to_date(1, '%m')
|
|
0000-01-00
|
|
select str_to_date(01, '%d');
|
|
str_to_date(01, '%d')
|
|
0000-00-01
|
|
select str_to_date(2019, '%Y');
|
|
str_to_date(2019, '%Y')
|
|
2019-00-00
|
|
select str_to_date('5,2019','%m,%Y');
|
|
str_to_date('5,2019','%m,%Y')
|
|
2019-05-00
|
|
select str_to_date('01,2019','%d,%Y');
|
|
str_to_date('01,2019','%d,%Y')
|
|
2019-00-01
|
|
select str_to_date('01,5','%d,%m');
|
|
str_to_date('01,5','%d,%m')
|
|
0000-05-01
|
|
set @@sql_mode=default;
|
|
drop table if exists t1, t2;
|
|
CREATE TABLE `t1` (`period_name` varchar(24) DEFAULT NULL ,`period_id` bigint(20) DEFAULT NULL ,`starttime` bigint(20) DEFAULT NULL);
|
|
CREATE TABLE `t2` (`bussid` bigint(20) DEFAULT NULL,`ct` bigint(20) DEFAULT NULL);
|
|
select
|
|
a.period_name,
|
|
b.date8
|
|
from
|
|
(select * from t1) a
|
|
left join
|
|
(select bussid,date(from_unixtime(ct)) date8 from t2) b
|
|
on
|
|
a.period_id = b.bussid
|
|
where
|
|
datediff(b.date8, date(from_unixtime(a.starttime))) >= 0;
|
|
period_name date8
|
|
SELECT "1900-01-01 00:00:00" + INTERVAL "100000000:214748364700" MINUTE_SECOND;
|
|
"1900-01-01 00:00:00" + INTERVAL "100000000:214748364700" MINUTE_SECOND
|
|
8895-03-27 22:11:40
|
|
SELECT "1900-01-01 00:00:00" + INTERVAL 1 << 37 SECOND;
|
|
"1900-01-01 00:00:00" + INTERVAL 1 << 37 SECOND
|
|
6255-04-08 15:04:32
|
|
SELECT "1900-01-01 00:00:00" + INTERVAL 1 << 31 MINUTE;
|
|
"1900-01-01 00:00:00" + INTERVAL 1 << 31 MINUTE
|
|
5983-01-24 02:08:00
|
|
SELECT "1900-01-01 00:00:00" + INTERVAL 1 << 38 SECOND;
|
|
"1900-01-01 00:00:00" + INTERVAL 1 << 38 SECOND
|
|
NULL
|
|
SELECT "1900-01-01 00:00:00" + INTERVAL 1 << 33 MINUTE;
|
|
"1900-01-01 00:00:00" + INTERVAL 1 << 33 MINUTE
|
|
NULL
|
|
SELECT "1900-01-01 00:00:00" + INTERVAL 1 << 30 HOUR;
|
|
"1900-01-01 00:00:00" + INTERVAL 1 << 30 HOUR
|
|
NULL
|
|
SELECT "1900-01-01 00:00:00" + INTERVAL "1000000000:214748364700" MINUTE_SECOND;
|
|
"1900-01-01 00:00:00" + INTERVAL "1000000000:214748364700" MINUTE_SECOND
|
|
NULL
|
|
SELECT 19000101000000 + INTERVAL "100000000:214748364700" MINUTE_SECOND;
|
|
19000101000000 + INTERVAL "100000000:214748364700" MINUTE_SECOND
|
|
8895-03-27 22:11:40
|
|
SELECT 19000101000000 + INTERVAL 1 << 37 SECOND;
|
|
19000101000000 + INTERVAL 1 << 37 SECOND
|
|
6255-04-08 15:04:32
|
|
SELECT 19000101000000 + INTERVAL 1 << 31 MINUTE;
|
|
19000101000000 + INTERVAL 1 << 31 MINUTE
|
|
5983-01-24 02:08:00
|
|
SELECT "8895-03-27 22:11:40" - INTERVAL "100000000:214748364700" MINUTE_SECOND;
|
|
"8895-03-27 22:11:40" - INTERVAL "100000000:214748364700" MINUTE_SECOND
|
|
1900-01-01 00:00:00
|
|
SELECT "6255-04-08 15:04:32" - INTERVAL 1 << 37 SECOND;
|
|
"6255-04-08 15:04:32" - INTERVAL 1 << 37 SECOND
|
|
1900-01-01 00:00:00
|
|
SELECT "5983-01-24 02:08:00" - INTERVAL 1 << 31 MINUTE;
|
|
"5983-01-24 02:08:00" - INTERVAL 1 << 31 MINUTE
|
|
1900-01-01 00:00:00
|
|
SELECT "9999-01-01 00:00:00" - INTERVAL 1 << 39 SECOND;
|
|
"9999-01-01 00:00:00" - INTERVAL 1 << 39 SECOND
|
|
NULL
|
|
SELECT "9999-01-01 00:00:00" - INTERVAL 1 << 33 MINUTE;
|
|
"9999-01-01 00:00:00" - INTERVAL 1 << 33 MINUTE
|
|
NULL
|
|
SELECT "9999-01-01 00:00:00" - INTERVAL 1 << 30 HOUR;
|
|
"9999-01-01 00:00:00" - INTERVAL 1 << 30 HOUR
|
|
NULL
|
|
SELECT "9999-01-01 00:00:00" - INTERVAL "10000000000:214748364700" MINUTE_SECOND;
|
|
"9999-01-01 00:00:00" - INTERVAL "10000000000:214748364700" MINUTE_SECOND
|
|
NULL
|
|
SELECT 88950327221140 - INTERVAL "100000000:214748364700" MINUTE_SECOND ;
|
|
88950327221140 - INTERVAL "100000000:214748364700" MINUTE_SECOND
|
|
1900-01-01 00:00:00
|
|
SELECT 62550408150432 - INTERVAL 1 << 37 SECOND;
|
|
62550408150432 - INTERVAL 1 << 37 SECOND
|
|
1900-01-01 00:00:00
|
|
SELECT 59830124020800 - INTERVAL 1 << 31 MINUTE;
|
|
59830124020800 - INTERVAL 1 << 31 MINUTE
|
|
1900-01-01 00:00:00
|
|
SELECT 10000101000000 + INTERVAL "111111111111111111" MICROSECOND;
|
|
10000101000000 + INTERVAL "111111111111111111" MICROSECOND
|
|
4520-12-21 05:31:51.111111
|
|
SELECT 10000101000000 + INTERVAL "111111111111.111111" SECOND;
|
|
10000101000000 + INTERVAL "111111111111.111111" SECOND
|
|
4520-12-21 05:31:51.111111
|
|
SELECT 10000101000000 + INTERVAL "111111111111.111111111" SECOND;
|
|
10000101000000 + INTERVAL "111111111111.111111111" SECOND
|
|
4520-12-21 05:31:51.111111
|
|
SELECT 10000101000000 + INTERVAL "111111111111.111" SECOND;
|
|
10000101000000 + INTERVAL "111111111111.111" SECOND
|
|
4520-12-21 05:31:51.111000
|
|
SELECT 10000101000000 + INTERVAL "111111111111." SECOND;
|
|
10000101000000 + INTERVAL "111111111111." SECOND
|
|
4520-12-21 05:31:51
|
|
SELECT 10000101000000 + INTERVAL "111111111111111111.5" MICROSECOND;
|
|
10000101000000 + INTERVAL "111111111111111111.5" MICROSECOND
|
|
4520-12-21 05:31:51.111111
|
|
SELECT 10000101000000 + INTERVAL "111111111111111112.5" MICROSECOND;
|
|
10000101000000 + INTERVAL "111111111111111112.5" MICROSECOND
|
|
4520-12-21 05:31:51.111112
|
|
SELECT 10000101000000 + INTERVAL "111111111111111111.500000" MICROSECOND;
|
|
10000101000000 + INTERVAL "111111111111111111.500000" MICROSECOND
|
|
4520-12-21 05:31:51.111111
|
|
SELECT 10000101000000 + INTERVAL "111111111111111111.50000000" MICROSECOND;
|
|
10000101000000 + INTERVAL "111111111111111111.50000000" MICROSECOND
|
|
4520-12-21 05:31:51.111111
|
|
SELECT 10000101000000 + INTERVAL "111111111111111111.6" MICROSECOND;
|
|
10000101000000 + INTERVAL "111111111111111111.6" MICROSECOND
|
|
4520-12-21 05:31:51.111111
|
|
SELECT 10000101000000 + INTERVAL "111111111111111111.499999" MICROSECOND;
|
|
10000101000000 + INTERVAL "111111111111111111.499999" MICROSECOND
|
|
4520-12-21 05:31:51.111111
|
|
SELECT 10000101000000 + INTERVAL "111111111111111111.499999999999" MICROSECOND;
|
|
10000101000000 + INTERVAL "111111111111111111.499999999999" MICROSECOND
|
|
4520-12-21 05:31:51.111111
|
|
SELECT INTERVAL 1.123456789e3 SECOND + "1900-01-01 00:00:00";
|
|
INTERVAL 1.123456789e3 SECOND + "1900-01-01 00:00:00"
|
|
1900-01-01 00:18:43.456789
|
|
SELECT INTERVAL 1 Year + 19000101000000;
|
|
INTERVAL 1 Year + 19000101000000
|
|
1901-01-01 00:00:00
|
|
select interval 6 month + date("1900-01-01");
|
|
interval 6 month + date("1900-01-01")
|
|
1900-07-01
|
|
select interval "5:2" MINUTE_SECOND + "1900-01-01";
|
|
interval "5:2" MINUTE_SECOND + "1900-01-01"
|
|
1900-01-01 00:05:02
|
|
drop table if exists t;
|
|
create table t(a bigint unsigned primary key);
|
|
insert into t values(9223372036854775807), (18446744073709551615);
|
|
select * from t where a > 9223372036854775807-0.5 order by a;
|
|
a
|
|
9223372036854775807
|
|
18446744073709551615
|
|
drop table if exists t1, t2;
|
|
create table t1(id varchar(36) not null primary key, org_id varchar(36) not null, status tinyint default 1 not null, ns varchar(36) default '' not null);
|
|
create table t2(id varchar(36) not null primary key, order_id varchar(36) not null, begin_time timestamp(3) default CURRENT_TIMESTAMP(3) not null);
|
|
create index idx_oid on t2(order_id);
|
|
insert into t1 value (1,1,1,'a');
|
|
insert into t1 value (2,1,2,'a');
|
|
insert into t1 value (3,1,3,'a');
|
|
insert into t2 value (1,2,date'2020-05-08');
|
|
explain format = 'plan_tree' SELECT /*+ INL_MERGE_JOIN(t1,t2) */ COUNT(*) FROM t1 LEFT JOIN t2 ON t1.id = t2.order_id WHERE t1.ns = 'a' AND t1.org_id IN (1) AND t1.status IN (2,6,10) AND timestampdiff(month, t2.begin_time, date'2020-05-06') = 0;
|
|
id task access object operator info
|
|
StreamAgg root funcs:count(1)->Column
|
|
└─IndexJoin root inner join, inner:IndexLookUp, outer key:expression__issues.t1.id, inner key:expression__issues.t2.order_id, equal cond:eq(expression__issues.t1.id, expression__issues.t2.order_id)
|
|
├─TableReader(Build) root data:Selection
|
|
│ └─Selection cop[tikv] eq(cast(expression__issues.t1.org_id, double BINARY), 1), eq(expression__issues.t1.ns, "a"), in(expression__issues.t1.status, 2, 6, 10)
|
|
│ └─TableFullScan cop[tikv] table:t1 keep order:false, stats:pseudo
|
|
└─IndexLookUp(Probe) root
|
|
├─IndexRangeScan(Build) cop[tikv] table:t2, index:idx_oid(order_id) range: decided by [eq(expression__issues.t2.order_id, expression__issues.t1.id)], keep order:false, stats:pseudo
|
|
└─Selection(Probe) cop[tikv] eq(timestampdiff("MONTH", expression__issues.t2.begin_time, 2020-05-06), 0)
|
|
└─TableRowIDScan cop[tikv] table:t2 keep order:false, stats:pseudo
|
|
SELECT /*+ INL_MERGE_JOIN(t1,t2) */ COUNT(*) FROM t1 LEFT JOIN t2 ON t1.id = t2.order_id WHERE t1.ns = 'a' AND t1.org_id IN (1) AND t1.status IN (2,6,10) AND timestampdiff(month, t2.begin_time, date'2020-05-06') = 0;
|
|
COUNT(*)
|
|
1
|
|
drop table if exists t;
|
|
create table t(a int);
|
|
insert into t values(1);
|
|
select * from t where a < -184467440737095516167.1;
|
|
a
|
|
select * from t where a > -184467440737095516167.1;
|
|
a
|
|
1
|
|
select * from t where a < 184467440737095516167.1;
|
|
a
|
|
1
|
|
select * from t where a > 184467440737095516167.1;
|
|
a
|
|
drop table if exists t;
|
|
create table t(b bit(1));
|
|
insert into t values(b'1');
|
|
select count(*) from t where b = 1;
|
|
count(*)
|
|
1
|
|
select count(*) from t where b = '1';
|
|
count(*)
|
|
1
|
|
select count(*) from t where b = b'1';
|
|
count(*)
|
|
1
|
|
drop table if exists t;
|
|
create table t(b bit(63));
|
|
insert into t values(b'111111111111111111111111111111111111111111111111111111111111111');
|
|
select count(*) from t where b = 9223372036854775807;
|
|
count(*)
|
|
1
|
|
select count(*) from t where b = '9223372036854775807';
|
|
count(*)
|
|
1
|
|
select count(*) from t where b = b'111111111111111111111111111111111111111111111111111111111111111';
|
|
count(*)
|
|
1
|
|
drop table if exists t1;
|
|
CREATE TABLE t1 (v bigint(20) UNSIGNED NOT NULL);
|
|
INSERT INTO t1 VALUES (1), (2);
|
|
SELECT SUM(IF(v > 1, v, -v)) FROM t1;
|
|
SUM(IF(v > 1, v, -v))
|
|
1
|
|
SELECT sum(IFNULL(cast(null+rand() as unsigned), -v)) FROM t1;
|
|
sum(IFNULL(cast(null+rand() as unsigned), -v))
|
|
-3
|
|
SELECT sum(COALESCE(cast(null+rand() as unsigned), -v)) FROM t1;
|
|
sum(COALESCE(cast(null+rand() as unsigned), -v))
|
|
-3
|
|
SELECT sum(COALESCE(cast(null+rand() as unsigned), v)) FROM t1;
|
|
sum(COALESCE(cast(null+rand() as unsigned), v))
|
|
3
|
|
drop table if exists t;
|
|
CREATE TABLE t (a decimal(6,3),b double(6,3),c float(6,3));
|
|
INSERT INTO t VALUES (1.100,1.100,1.100);
|
|
SELECT DATE_ADD('2003-11-18 07:25:13',INTERVAL a MINUTE_SECOND) FROM t;
|
|
DATE_ADD('2003-11-18 07:25:13',INTERVAL a MINUTE_SECOND)
|
|
2003-11-18 07:27:53
|
|
SELECT DATE_ADD('2003-11-18 07:25:13',INTERVAL b MINUTE_SECOND) FROM t;
|
|
DATE_ADD('2003-11-18 07:25:13',INTERVAL b MINUTE_SECOND)
|
|
2003-11-18 07:27:53
|
|
SELECT DATE_ADD('2003-11-18 07:25:13',INTERVAL c MINUTE_SECOND) FROM t;
|
|
DATE_ADD('2003-11-18 07:25:13',INTERVAL c MINUTE_SECOND)
|
|
2003-11-18 07:27:53
|
|
drop table if exists t;
|
|
CREATE TABLE t (a decimal(11,7),b double(11,7),c float(11,7));
|
|
INSERT INTO t VALUES (123.9999999,123.9999999,123.9999999),(-123.9999999,-123.9999999,-123.9999999);
|
|
SELECT DATE_ADD('2003-11-18 07:25:13',INTERVAL a MINUTE_SECOND) FROM t;
|
|
DATE_ADD('2003-11-18 07:25:13',INTERVAL a MINUTE_SECOND)
|
|
2004-03-13 03:14:52
|
|
2003-07-25 11:35:34
|
|
SELECT DATE_ADD('2003-11-18 07:25:13',INTERVAL b MINUTE_SECOND) FROM t;
|
|
DATE_ADD('2003-11-18 07:25:13',INTERVAL b MINUTE_SECOND)
|
|
2004-03-13 03:14:52
|
|
2003-07-25 11:35:34
|
|
SELECT DATE_ADD('2003-11-18 07:25:13',INTERVAL c MINUTE_SECOND) FROM t;
|
|
DATE_ADD('2003-11-18 07:25:13',INTERVAL c MINUTE_SECOND)
|
|
2003-11-18 09:29:13
|
|
2003-11-18 05:21:13
|
|
SELECT DATE_ADD('2007-03-28 22:08:28',INTERVAL -2.2 MINUTE_MICROSECOND);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL -2.2 MINUTE_MICROSECOND)
|
|
2007-03-28 22:08:25.800000
|
|
SELECT DATE_ADD('2007-03-28 22:08:28',INTERVAL -2.2 SECOND_MICROSECOND);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL -2.2 SECOND_MICROSECOND)
|
|
2007-03-28 22:08:25.800000
|
|
SELECT DATE_ADD('2007-03-28 22:08:28',INTERVAL -2.2 HOUR_MICROSECOND);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL -2.2 HOUR_MICROSECOND)
|
|
2007-03-28 22:08:25.800000
|
|
SELECT DATE_ADD('2007-03-28 22:08:28',INTERVAL -2.2 DAY_MICROSECOND);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL -2.2 DAY_MICROSECOND)
|
|
2007-03-28 22:08:25.800000
|
|
SELECT DATE_ADD('2007-03-28 22:08:28',INTERVAL -2.2 SECOND);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL -2.2 SECOND)
|
|
2007-03-28 22:08:25.800000
|
|
SELECT DATE_ADD('2007-03-28 22:08:28',INTERVAL -2.2 HOUR_SECOND);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL -2.2 HOUR_SECOND)
|
|
2007-03-28 22:06:26
|
|
SELECT DATE_ADD('2007-03-28 22:08:28',INTERVAL -2.2 DAY_SECOND);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL -2.2 DAY_SECOND)
|
|
2007-03-28 22:06:26
|
|
SELECT DATE_ADD('2007-03-28 22:08:28',INTERVAL -2.2 MINUTE_SECOND);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL -2.2 MINUTE_SECOND)
|
|
2007-03-28 22:06:26
|
|
SELECT DATE_ADD('2007-03-28 22:08:28',INTERVAL -2.2 MINUTE);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL -2.2 MINUTE)
|
|
2007-03-28 22:06:28
|
|
SELECT DATE_ADD('2007-03-28 22:08:28',INTERVAL -2.2 DAY_MINUTE);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL -2.2 DAY_MINUTE)
|
|
2007-03-28 20:06:28
|
|
SELECT DATE_ADD('2007-03-28 22:08:28',INTERVAL -2.2 HOUR_MINUTE);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL -2.2 HOUR_MINUTE)
|
|
2007-03-28 20:06:28
|
|
SELECT DATE_ADD('2007-03-28 22:08:28',INTERVAL -2.2 DAY_HOUR);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL -2.2 DAY_HOUR)
|
|
2007-03-26 20:08:28
|
|
SELECT DATE_ADD('2007-03-28 22:08:28',INTERVAL -2.2 YEAR_MONTH);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL -2.2 YEAR_MONTH)
|
|
2005-01-28 22:08:28
|
|
SELECT DATE_ADD('2007-03-28 22:08:28',INTERVAL 2.2 MINUTE_MICROSECOND);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL 2.2 MINUTE_MICROSECOND)
|
|
2007-03-28 22:08:30.200000
|
|
SELECT DATE_ADD('2007-03-28 22:08:28',INTERVAL 2.2 SECOND_MICROSECOND);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL 2.2 SECOND_MICROSECOND)
|
|
2007-03-28 22:08:30.200000
|
|
SELECT DATE_ADD('2007-03-28 22:08:28',INTERVAL 2.2 HOUR_MICROSECOND);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL 2.2 HOUR_MICROSECOND)
|
|
2007-03-28 22:08:30.200000
|
|
SELECT DATE_ADD('2007-03-28 22:08:28',INTERVAL 2.2 DAY_MICROSECOND);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL 2.2 DAY_MICROSECOND)
|
|
2007-03-28 22:08:30.200000
|
|
SELECT DATE_ADD('2007-03-28 22:08:28',INTERVAL 2.2 SECOND);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL 2.2 SECOND)
|
|
2007-03-28 22:08:30.200000
|
|
SELECT DATE_ADD('2007-03-28 22:08:28',INTERVAL 2.2 HOUR_SECOND);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL 2.2 HOUR_SECOND)
|
|
2007-03-28 22:10:30
|
|
SELECT DATE_ADD('2007-03-28 22:08:28',INTERVAL 2.2 DAY_SECOND);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL 2.2 DAY_SECOND)
|
|
2007-03-28 22:10:30
|
|
SELECT DATE_ADD('2007-03-28 22:08:28',INTERVAL 2.2 MINUTE_SECOND);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL 2.2 MINUTE_SECOND)
|
|
2007-03-28 22:10:30
|
|
SELECT DATE_ADD('2007-03-28 22:08:28',INTERVAL 2.2 MINUTE);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL 2.2 MINUTE)
|
|
2007-03-28 22:10:28
|
|
SELECT DATE_ADD('2007-03-28 22:08:28',INTERVAL 2.2 DAY_MINUTE);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL 2.2 DAY_MINUTE)
|
|
2007-03-29 00:10:28
|
|
SELECT DATE_ADD('2007-03-28 22:08:28',INTERVAL 2.2 HOUR_MINUTE);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL 2.2 HOUR_MINUTE)
|
|
2007-03-29 00:10:28
|
|
SELECT DATE_ADD('2007-03-28 22:08:28',INTERVAL 2.2 DAY_HOUR);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL 2.2 DAY_HOUR)
|
|
2007-03-31 00:08:28
|
|
SELECT DATE_ADD('2007-03-28 22:08:28',INTERVAL 2.2 YEAR_MONTH);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL 2.2 YEAR_MONTH)
|
|
2009-05-28 22:08:28
|
|
drop table if exists t;
|
|
create table t (d decimal(19, 0), i bigint(11));
|
|
insert into t values (123456789012, 123456789012);
|
|
select * from t where d = i;
|
|
d i
|
|
123456789012 123456789012
|
|
select '0-3261554956'+0.0;
|
|
'0-3261554956'+0.0
|
|
0
|
|
select cast('0-1234' as real);
|
|
cast('0-1234' as real)
|
|
0
|
|
drop table if exists testValuesBinary;
|
|
create table testValuesBinary(id int primary key auto_increment, a bit(1));
|
|
insert into testValuesBinary values(1,1);
|
|
insert into testValuesBinary values(1,1) on duplicate key update id = values(id),a = values(a);
|
|
select a=0 from testValuesBinary;
|
|
a=0
|
|
0
|
|
insert into testValuesBinary values(1,0) on duplicate key update id = values(id),a = values(a);
|
|
select a=0 from testValuesBinary;
|
|
a=0
|
|
1
|
|
drop table testValuesBinary;
|
|
DROP TABLE IF EXISTS t;
|
|
CREATE TABLE t (v VARCHAR(100));
|
|
INSERT INTO t VALUES ('3289742893213123732904809');
|
|
SELECT * FROM t WHERE v;
|
|
v
|
|
3289742893213123732904809
|
|
drop table if exists tt;
|
|
create table tt(a varchar(10));
|
|
insert into tt values(NULL);
|
|
analyze table tt;
|
|
select * from tt;
|
|
a
|
|
NULL
|
|
select collation(format_bytes(1024)) != 'binary';
|
|
collation(format_bytes(1024)) != 'binary'
|
|
1
|
|
select collation(format_nano_time(234)) != 'binary';
|
|
collation(format_nano_time(234)) != 'binary'
|
|
1
|
|
drop table if exists t;
|
|
create table t (a char(10) charset gbk collate gbk_chinese_ci, b time);
|
|
insert into t values ('08:00:00', '08:00:00');
|
|
select t1.a, t2.b from t as t1 right join t as t2 on t1.a = t2.b;
|
|
a b
|
|
08:00:00 08:00:00
|
|
drop table if exists table_30_utf8_4, t;
|
|
create table t(a int);
|
|
insert into t values(1);
|
|
create table table_30_utf8_4 ( `pk` int primary key, `col_int_key_unsigned` int unsigned , `col_int_key_signed` int, `col_float_key_signed` float , `col_float_key_unsigned` float unsigned) character set utf8 partition by hash(pk) partitions 4;
|
|
insert ignore into table_30_utf8_4 values (0,91, 10, 14,19.0495);
|
|
alter table table_30_utf8_4 add column a int as (col_int_key_signed * 2);
|
|
SELECT count(1) AS val FROM table_30_utf8_4 WHERE table_30_utf8_4.col_int_key_unsigned!=table_30_utf8_4.a OR (SELECT count(1) AS val FROM t WHERE table_30_utf8_4.col_float_key_signed!=table_30_utf8_4.col_float_key_unsigned )!=7984764426240273913;
|
|
val
|
|
1
|
|
select a from table_30_utf8_4 order by a;
|
|
a
|
|
20
|
|
drop table if exists t1;
|
|
CREATE TABLE t1 ( id INT NOT NULL PRIMARY KEY auto_increment, pad VARCHAR(10) NOT NULL, expr varchar(100) AS (NOT 1 BETWEEN -5 AND 5));
|
|
INSERT INTO t1 (pad) VALUES ('a'), ('b');
|
|
SELECT id, pad, expr, NOT 1 BETWEEN -5 AND 5 as expr_in_select FROM t1;
|
|
id pad expr expr_in_select
|
|
1 a 0 0
|
|
2 b 0 0
|
|
drop table if exists t0;
|
|
CREATE TABLE t0(c0 int);
|
|
INSERT INTO t0 VALUES (0);
|
|
SELECT t0.c0 FROM t0 WHERE CHAR(204355900);
|
|
c0
|
|
0
|
|
SELECT t0.c0 FROM t0 WHERE not CHAR(204355900);
|
|
c0
|
|
SELECT t0.c0 FROM t0 WHERE '.0';
|
|
c0
|
|
SELECT t0.c0 FROM t0 WHERE not '.0';
|
|
c0
|
|
0
|
|
select * from t0 where '.00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000009';
|
|
c0
|
|
0
|
|
select * from t0 where not '.00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000009';
|
|
c0
|
|
select * from t0 where '.000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000009';
|
|
c0
|
|
select * from t0 where not '.000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000009';
|
|
c0
|
|
0
|
|
drop table if exists testjson;
|
|
CREATE TABLE testjson (j json DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;
|
|
INSERT INTO testjson SET j='{"test":3}';
|
|
INSERT INTO testjson SET j='{"test":0}';
|
|
insert into testjson set j='{"test":"0"}';
|
|
insert into testjson set j='{"test":0.0}';
|
|
INSERT INTO testjson SET j='{"test":"aaabbb"}';
|
|
INSERT INTO testjson SET j='{"test":3.1415}';
|
|
INSERT INTO testjson SET j='{"test":[]}';
|
|
INSERT INTO testjson SET j='{"test":[1,2]}';
|
|
INSERT INTO testjson SET j='{"test":["b","c"]}';
|
|
INSERT INTO testjson SET j='{"test":{"ke":"val"}}';
|
|
insert into testjson set j='{"test":"2015-07-27 09:43:47"}';
|
|
insert into testjson set j='{"test":"0000-00-00 00:00:00"}';
|
|
insert into testjson set j='{"test":"0778"}';
|
|
insert into testjson set j='{"test":"0000"}';
|
|
insert into testjson set j='{"test":null}';
|
|
insert into testjson set j=null;
|
|
insert into testjson set j='{"test":[null]}';
|
|
insert into testjson set j='{"test":true}';
|
|
insert into testjson set j='{"test":false}';
|
|
insert into testjson set j='""';
|
|
insert into testjson set j='null';
|
|
insert into testjson set j='0';
|
|
insert into testjson set j='"0"';
|
|
SELECT * FROM testjson WHERE JSON_EXTRACT(j,'$.test');
|
|
j
|
|
{"test": 3}
|
|
{"test": "0"}
|
|
{"test": "aaabbb"}
|
|
{"test": 3.1415}
|
|
{"test": []}
|
|
{"test": [1, 2]}
|
|
{"test": ["b", "c"]}
|
|
{"test": {"ke": "val"}}
|
|
{"test": "2015-07-27 09:43:47"}
|
|
{"test": "0000-00-00 00:00:00"}
|
|
{"test": "0778"}
|
|
{"test": "0000"}
|
|
{"test": null}
|
|
{"test": [null]}
|
|
{"test": true}
|
|
{"test": false}
|
|
select * from testjson where j;
|
|
j
|
|
{"test": 3}
|
|
{"test": 0}
|
|
{"test": "0"}
|
|
{"test": 0.0}
|
|
{"test": "aaabbb"}
|
|
{"test": 3.1415}
|
|
{"test": []}
|
|
{"test": [1, 2]}
|
|
{"test": ["b", "c"]}
|
|
{"test": {"ke": "val"}}
|
|
{"test": "2015-07-27 09:43:47"}
|
|
{"test": "0000-00-00 00:00:00"}
|
|
{"test": "0778"}
|
|
{"test": "0000"}
|
|
{"test": null}
|
|
{"test": [null]}
|
|
{"test": true}
|
|
{"test": false}
|
|
""
|
|
null
|
|
"0"
|
|
insert into mysql.expr_pushdown_blacklist values('json_extract','tikv','');
|
|
admin reload expr_pushdown_blacklist;
|
|
SELECT * FROM testjson WHERE JSON_EXTRACT(j,'$.test');
|
|
j
|
|
{"test": 3}
|
|
{"test": "0"}
|
|
{"test": "aaabbb"}
|
|
{"test": 3.1415}
|
|
{"test": []}
|
|
{"test": [1, 2]}
|
|
{"test": ["b", "c"]}
|
|
{"test": {"ke": "val"}}
|
|
{"test": "2015-07-27 09:43:47"}
|
|
{"test": "0000-00-00 00:00:00"}
|
|
{"test": "0778"}
|
|
{"test": "0000"}
|
|
{"test": null}
|
|
{"test": [null]}
|
|
{"test": true}
|
|
{"test": false}
|
|
select * from testjson where j;
|
|
j
|
|
{"test": 3}
|
|
{"test": 0}
|
|
{"test": "0"}
|
|
{"test": 0.0}
|
|
{"test": "aaabbb"}
|
|
{"test": 3.1415}
|
|
{"test": []}
|
|
{"test": [1, 2]}
|
|
{"test": ["b", "c"]}
|
|
{"test": {"ke": "val"}}
|
|
{"test": "2015-07-27 09:43:47"}
|
|
{"test": "0000-00-00 00:00:00"}
|
|
{"test": "0778"}
|
|
{"test": "0000"}
|
|
{"test": null}
|
|
{"test": [null]}
|
|
{"test": true}
|
|
{"test": false}
|
|
""
|
|
null
|
|
"0"
|
|
delete from mysql.expr_pushdown_blacklist;
|
|
admin reload expr_pushdown_blacklist;
|
|
drop table if exists t0;
|
|
CREATE TABLE t0(c0 int);
|
|
INSERT INTO t0 VALUES (1);
|
|
SELECT * FROM t0 WHERE 1 AND 0.4;
|
|
c0
|
|
1
|
|
drop table if exists t;
|
|
create table t(a int);
|
|
insert into t values(2);
|
|
select * from t where (not not a) = a;
|
|
a
|
|
select * from t where (not not not not a) = a;
|
|
a
|
|
drop table if exists t0;
|
|
CREATE TABLE t0(c0 INT);
|
|
INSERT INTO t0(c0) VALUES (0);
|
|
SELECT * FROM t0 WHERE -10000000000000000000 | t0.c0 UNION SELECT * FROM t0;
|
|
c0
|
|
0
|
|
SELECT * FROM t0 WHERE -10000000000000000000 | t0.c0 UNION all SELECT * FROM t0;
|
|
c0
|
|
0
|
|
0
|
|
drop table if exists t0;
|
|
CREATE TABLE t0(c0 TEXT(10));
|
|
INSERT INTO t0(c0) VALUES (1);
|
|
SELECT * FROM t0 WHERE ('a' != t0.c0) AND t0.c0;
|
|
c0
|
|
1
|
|
CREATE INDEX i0 ON t0(c0(10));
|
|
SELECT * FROM t0 WHERE ('a' != t0.c0) AND t0.c0;
|
|
c0
|
|
1
|
|
drop table if exists t0;
|
|
CREATE TABLE t0(c0 INT, c1 INT AS (c0));
|
|
CREATE INDEX i0 ON t0(c1);
|
|
SELECT t0.c0 FROM t0 UNION ALL SELECT 0 FROM t0;
|
|
c0
|
|
drop table if exists t0, t1;
|
|
CREATE TABLE t0(c0 INT);
|
|
CREATE TABLE t1(c0 INT);
|
|
SELECT * FROM t1 NATURAL LEFT JOIN t0 WHERE NOT t1.c0;
|
|
c0
|
|
drop table if exists t0,t1;
|
|
CREATE TABLE t0(c0 INT);
|
|
CREATE TABLE t1(c0 INT);
|
|
INSERT INTO t0 VALUES (NULL), (1);
|
|
INSERT INTO t1 VALUES (0);
|
|
SELECT t0.c0 FROM t0 JOIN t1 ON (t0.c0 REGEXP 1) | t1.c0 WHERE BINARY STRCMP(t1.c0, t0.c0);
|
|
c0
|
|
1
|
|
drop table if exists t;
|
|
create table t (a int);
|
|
insert into t values (42);
|
|
select a from t where a/10000;
|
|
a
|
|
42
|
|
select a from t where a/100000;
|
|
a
|
|
42
|
|
select a from t where a/1000000;
|
|
a
|
|
42
|
|
select a from t where a/10000000;
|
|
a
|
|
42
|
|
drop table if exists t;
|
|
create table t(a datetime, b year);
|
|
insert into t values('2000-05-03 16:44:44', 2018);
|
|
insert into t values('2020-10-01 11:11:11', 2000);
|
|
insert into t values('2020-10-01 11:11:11', 2070);
|
|
insert into t values('2020-10-01 11:11:11', 1999);
|
|
select * from t where t.a < t.b;
|
|
a b
|
|
2000-05-03 16:44:44 2018
|
|
2020-10-01 11:11:11 2070
|
|
select * from t where t.a > t.b;
|
|
a b
|
|
2020-10-01 11:11:11 2000
|
|
2020-10-01 11:11:11 1999
|
|
drop table if exists tt;
|
|
create table tt(a date, b year);
|
|
insert into tt values('2019-11-11', 2000);
|
|
insert into tt values('2019-11-11', 2020);
|
|
insert into tt values('2019-11-11', 2022);
|
|
select * from tt where tt.a > tt.b;
|
|
a b
|
|
2019-11-11 2000
|
|
select * from tt where tt.a < tt.b;
|
|
a b
|
|
2019-11-11 2020
|
|
2019-11-11 2022
|
|
drop table if exists ttt;
|
|
create table ttt(a timestamp, b year);
|
|
insert into ttt values('2019-11-11 11:11:11', 2019);
|
|
insert into ttt values('2019-11-11 11:11:11', 2000);
|
|
insert into ttt values('2019-11-11 11:11:11', 2022);
|
|
select * from ttt where ttt.a > ttt.b;
|
|
a b
|
|
2019-11-11 11:11:11 2019
|
|
2019-11-11 11:11:11 2000
|
|
select * from ttt where ttt.a < ttt.b;
|
|
a b
|
|
2019-11-11 11:11:11 2022
|
|
drop table if exists t0, t1;
|
|
create table t0 (c0 int);
|
|
create table t1 (c0 int);
|
|
SELECT * FROM t1 LEFT JOIN t0 ON TRUE WHERE BINARY EXPORT_SET(0, 0, 0 COLLATE 'binary', t0.c0, 0 COLLATE 'binary');
|
|
c0 c0
|
|
drop table if exists t;
|
|
create table t(a int,b varchar(20),c datetime,d double,e int,f int as(a+b),key(a),key(b),key(c),key(d),key(e),key(f));
|
|
insert into t(a,b,e) values(null,"5",null);
|
|
insert into t(a,b,e) values("5",null,null);
|
|
select /*+ use_index_merge(t)*/ * from t where t.e=5 or t.a=5;
|
|
a b c d e f
|
|
5 NULL NULL NULL NULL NULL
|
|
drop table if exists t1, t2;
|
|
create table t1(a char) collate utf8mb4_bin;
|
|
create table t2(a char) collate utf8mb4_bin;
|
|
insert into t1 values('a');
|
|
insert into t2 values('a');
|
|
select collation(t1.a) from t1 union select collation(t2.a) from t2;
|
|
collation(t1.a)
|
|
utf8mb4_bin
|
|
select collation(user());
|
|
collation(user())
|
|
utf8mb4_bin
|
|
select collation(compress('abc'));
|
|
collation(compress('abc'))
|
|
binary
|
|
set tidb_enable_prepared_plan_cache=1;
|
|
drop table if exists t;
|
|
set @@tidb_enable_vectorized_expression = false;
|
|
create table t(a datetime);
|
|
insert into t values(from_unixtime(1589873945)), (from_unixtime(1589873946));
|
|
prepare stmt7 from 'SELECT unix_timestamp(a) FROM t WHERE a = from_unixtime(?);';
|
|
set @val1 = 1589873945;
|
|
set @val2 = 1589873946;
|
|
execute stmt7 using @val1;
|
|
unix_timestamp(a)
|
|
1589873945
|
|
execute stmt7 using @val2;
|
|
unix_timestamp(a)
|
|
1589873946
|
|
drop table if exists t0;
|
|
create table t0(a char(10), b int as ((a)));
|
|
insert into t0(a) values("0.5");
|
|
select * from t0;
|
|
a b
|
|
0.5 1
|
|
drop table if exists t;
|
|
create table t(a int, b json, c int AS (JSON_EXTRACT(b, '$.population')), key(c));
|
|
select /*+ TIDB_INLJ(t2) */ t1.a, t1.c, t2.a from t t1, t t2 where t1.c=t2.c;
|
|
a c a
|
|
drop table if exists t;
|
|
CREATE TABLE t (id int(10) unsigned NOT NULL AUTO_INCREMENT,type tinyint(4) NOT NULL,create_time int(11) NOT NULL,PRIMARY KEY (id));
|
|
insert into t values (4, 2, 1598584933);
|
|
select from_unixtime(create_time,'%Y-%m-%d') as t_day,count(*) as cnt from t where `type` = 1 group by t_day union all select from_unixtime(create_time,'%Y-%m-%d') as t_day,count(*) as cnt from t where `type` = 2 group by t_day;
|
|
t_day cnt
|
|
2020-08-28 1
|
|
drop table if exists t1;
|
|
create table t1 (col0 BLOB, col1 CHAR(74), col2 DATE UNIQUE);
|
|
insert into t1 values ('l', '7a34bc7d-6786-461b-92d3-fd0a6cd88f39', '1000-01-03');
|
|
insert into t1 values ('l', NULL, '1000-01-04');
|
|
insert into t1 values ('b', NULL, '1000-01-02');
|
|
select INTERVAL( ( CONVERT( -11752 USING utf8 ) ), 6558853612195285496, `col1`) from t1;
|
|
INTERVAL( ( CONVERT( -11752 USING utf8 ) ), 6558853612195285496, `col1`)
|
|
0
|
|
0
|
|
0
|
|
drop table if exists t, t1;
|
|
create table t(a int, b enum('A', 'B'));
|
|
create table t1(a1 int, b1 enum('B', 'A'));
|
|
insert into t values (1, 'A');
|
|
insert into t1 values (1, 'A');
|
|
select /*+ HASH_JOIN(t, t1) */ * from t join t1 on t.b = t1.b1;
|
|
a b a1 b1
|
|
1 A 1 A
|
|
drop table t, t1;
|
|
create table t(a int, b set('A', 'B'));
|
|
create table t1(a1 int, b1 set('B', 'A'));
|
|
insert into t values (1, 'A');
|
|
insert into t1 values (1, 'A');
|
|
select /*+ HASH_JOIN(t, t1) */ * from t join t1 on t.b = t1.b1;
|
|
a b a1 b1
|
|
1 A 1 A
|
|
drop table if exists t1;
|
|
create table t1 (c_int int, primary key (c_int));
|
|
insert into t1 values (1), (2), (3);
|
|
drop table if exists t2;
|
|
create table t2 (c_int int, primary key (c_int));
|
|
insert into t2 values (1);
|
|
select (select count(c_int) from t2 where c_int = t1.c_int) c1, (select count(1) from t2 where c_int = t1.c_int) c2 from t1;
|
|
c1 c2
|
|
1 1
|
|
0 0
|
|
0 0
|
|
select (select count(c_int*c_int) from t2 where c_int = t1.c_int) c1, (select count(1) from t2 where c_int = t1.c_int) c2 from t1;
|
|
c1 c2
|
|
1 1
|
|
0 0
|
|
0 0
|
|
drop table if exists t0;
|
|
CREATE TABLE t0(c0 INTEGER AS (NULL) NOT NULL, c1 INT);
|
|
CREATE INDEX i0 ON t0(c0, c1);
|
|
INSERT IGNORE INTO t0(c1) VALUES (0);
|
|
SELECT * FROM t0;
|
|
c0 c1
|
|
0 0
|
|
begin;
|
|
INSERT IGNORE INTO t0(c1) VALUES (0);
|
|
SELECT * FROM t0;
|
|
c0 c1
|
|
0 0
|
|
0 0
|
|
rollback;
|
|
drop table if exists t;
|
|
create table t (a int) partition by range(a) (PARTITION p0 VALUES LESS THAN (10));
|
|
alter table t add partition (partition p1 values less than (a));
|
|
Error 1054 (42S22): Unknown column 'a' in 'expression'
|
|
select * from t;
|
|
a
|
|
drop table if exists t;
|
|
create table t (a int) partition by range(a) (PARTITION p0 VALUES LESS THAN (a));
|
|
Error 1054 (42S22): Unknown column 'a' in 'expression'
|
|
DROP TABLE IF EXISTS `table_float`;
|
|
DROP TABLE IF EXISTS `table_int_float_varchar`;
|
|
CREATE TABLE `table_float` (`id_1` int(16) NOT NULL AUTO_INCREMENT,`col_float_1` float DEFAULT NULL,PRIMARY KEY (`id_1`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=97635;
|
|
CREATE TABLE `table_int_float_varchar` (`id_6` int(16) NOT NULL AUTO_INCREMENT,`col_int_6` int(16) DEFAULT NULL,`col_float_6` float DEFAULT NULL,`col_varchar_6` varchar(511) DEFAULT NULL,PRIMARY KEY (`id_6`),KEY `vhyen` (`id_6`,`col_int_6`,`col_float_6`,`col_varchar_6`(1)),KEY `zzylq` (`id_6`,`col_int_6`,`col_float_6`,`col_varchar_6`(1))) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=90818;
|
|
INSERT INTO `table_float` VALUES (1,NULL),(2,0.1),(3,0),(4,-0.1),(5,-0.1),(6,NULL),(7,0.5),(8,0),(9,0),(10,NULL),(11,1),(12,1.5),(13,NULL),(14,NULL);
|
|
INSERT INTO `table_int_float_varchar` VALUES (1,0,0.1,'true'),(2,-1,1.5,'2020-02-02 02:02:00'),(3,NULL,1.5,NULL),(4,65535,0.1,'true'),(5,NULL,0.1,'1'),(6,-1,1.5,'2020-02-02 02:02:00'),(7,-1,NULL,''),(8,NULL,-0.1,NULL),(9,NULL,-0.1,'1'),(10,-1,NULL,''),(11,NULL,1.5,'false'),(12,-1,0,NULL),(13,0,-0.1,NULL),(14,-1,NULL,'-0'),(15,65535,-1,'1'),(16,NULL,0.5,NULL),(17,-1,NULL,NULL);
|
|
select count(*) from table_float
|
|
JOIN table_int_float_varchar AS tmp3 ON (tmp3.col_varchar_6 AND NULL)
|
|
IS NULL WHERE col_int_6=0;
|
|
count(*)
|
|
14
|
|
SELECT count(*) FROM (table_float JOIN table_int_float_varchar AS tmp3 ON (tmp3.col_varchar_6 AND NULL) IS NULL);
|
|
count(*)
|
|
154
|
|
SELECT * FROM (table_int_float_varchar AS tmp3) WHERE (col_varchar_6 AND NULL) IS NULL AND col_int_6=0;
|
|
id_6 col_int_6 col_float_6 col_varchar_6
|
|
13 0 -0.1 NULL
|
|
drop table if exists papers;
|
|
create table papers(title text, content longtext);
|
|
insert into papers values('title', 'content');
|
|
select to_base64(title), to_base64(content) from papers;
|
|
to_base64(title) to_base64(content)
|
|
dGl0bGU= Y29udGVudA==
|
|
set tidb_enable_vectorized_expression = 0;
|
|
select to_base64(title), to_base64(content) from papers;
|
|
to_base64(title) to_base64(content)
|
|
dGl0bGU= Y29udGVudA==
|
|
set tidb_enable_vectorized_expression = 1;
|
|
drop table if exists t, t1;
|
|
create table t(a enum('a', 'b'), b tinyint);
|
|
create table t1(c varchar(20));
|
|
insert into t values('b', 0);
|
|
insert into t1 values('b');
|
|
select * from t, t1 where t.a= t1.c;
|
|
a b c
|
|
b 0 b
|
|
select * from t, t1 where t.b= t1.c;
|
|
a b c
|
|
b 0 b
|
|
select * from t, t1 where t.a = t1.c and t.b= t1.c;
|
|
a b c
|
|
b 0 b
|
|
drop table if exists t;
|
|
create table t(a enum('a','b'));
|
|
insert into t values('b');
|
|
select * from t where a > 1 and a = "b";
|
|
a
|
|
b
|
|
drop table if exists lt;
|
|
create table lt (d decimal(10, 4));
|
|
insert into lt values(0.2),(0.2);
|
|
select LEAD(d,1,1) OVER(), LAG(d,1,1) OVER() from lt;
|
|
LEAD(d,1,1) OVER() LAG(d,1,1) OVER()
|
|
0.2000 1.0000
|
|
1.0000 0.2000
|
|
drop table if exists t;
|
|
create table t(a int);
|
|
insert into t values (1);
|
|
insert into t select values(a) from t;
|
|
select * from t;
|
|
a
|
|
1
|
|
NULL
|
|
DROP TABLE IF EXISTS tmp;
|
|
CREATE TABLE tmp (id int(11) NOT NULL,value int(1) NOT NULL,PRIMARY KEY (id));
|
|
INSERT INTO tmp VALUES (1, 1),(2,2),(3,3),(4,4),(5,5);
|
|
SET @sum := 10;
|
|
SELECT @sum := IF(@sum=20,4,@sum + tmp.value) sum FROM tmp ORDER BY tmp.id;
|
|
sum
|
|
11
|
|
13
|
|
16
|
|
20
|
|
4
|
|
drop table if exists t;
|
|
create table t(id int primary key, c int, d timestamp null default null);
|
|
insert into t values(1, 2, '2038-01-18 20:20:30');
|
|
update t set d = adddate(d, interval 1 day) where id < 10;
|
|
Error 1292 (22007): Incorrect timestamp value: '2038-01-19 20:20:30'
|
|
drop view if exists t15847;
|
|
CREATE VIEW t15847(c0) AS SELECT NULL;
|
|
SELECT * FROM t15847 WHERE (NOT (IF(t15847.c0, NULL, NULL)));
|
|
c0
|
|
drop view if exists t15847;
|
|
select json_array(true);
|
|
json_array(true)
|
|
[true]
|
|
select json_array(1=2);
|
|
json_array(1=2)
|
|
[false]
|
|
select json_array(1!=2);
|
|
json_array(1!=2)
|
|
[true]
|
|
select json_array(1<2);
|
|
json_array(1<2)
|
|
[true]
|
|
select json_array(1<=2);
|
|
json_array(1<=2)
|
|
[true]
|
|
select json_array(1>2);
|
|
json_array(1>2)
|
|
[false]
|
|
select json_array(1>=2);
|
|
json_array(1>=2)
|
|
[false]
|
|
select json_object(true, null <=> null);
|
|
json_object(true, null <=> null)
|
|
{"1": true}
|
|
select json_object(false, 1 and 2);
|
|
json_object(false, 1 and 2)
|
|
{"0": true}
|
|
select json_object(false, 1 and 0);
|
|
json_object(false, 1 and 0)
|
|
{"0": false}
|
|
select json_object(false, 1 or 0);
|
|
json_object(false, 1 or 0)
|
|
{"0": true}
|
|
select json_object(false, 1 xor 0);
|
|
json_object(false, 1 xor 0)
|
|
{"0": true}
|
|
select json_object(false, 1 xor 1);
|
|
json_object(false, 1 xor 1)
|
|
{"0": false}
|
|
select json_object(false, not 1);
|
|
json_object(false, not 1)
|
|
{"0": false}
|
|
select json_array(null and 1);
|
|
json_array(null and 1)
|
|
[null]
|
|
select json_array(null and 0);
|
|
json_array(null and 0)
|
|
[false]
|
|
select json_array(null or 1);
|
|
json_array(null or 1)
|
|
[true]
|
|
select json_array(null or 0);
|
|
json_array(null or 0)
|
|
[null]
|
|
select json_array(1.15 or 0);
|
|
json_array(1.15 or 0)
|
|
[true]
|
|
select json_array('abc' or 0);
|
|
json_array('abc' or 0)
|
|
[false]
|
|
select json_array('1abc' or 0);
|
|
json_array('1abc' or 0)
|
|
[true]
|
|
select json_array(null is true);
|
|
json_array(null is true)
|
|
[false]
|
|
select json_array(null is null);
|
|
json_array(null is null)
|
|
[true]
|
|
select json_array(1 in (1, 2));
|
|
json_array(1 in (1, 2))
|
|
[true]
|
|
select json_array(0 in (1, 2));
|
|
json_array(0 in (1, 2))
|
|
[false]
|
|
select json_array(0 not in (1, 2));
|
|
json_array(0 not in (1, 2))
|
|
[true]
|
|
select json_array(1 between 0 and 2);
|
|
json_array(1 between 0 and 2)
|
|
[true]
|
|
select json_array(1 not between 0 and 2);
|
|
json_array(1 not between 0 and 2)
|
|
[false]
|
|
select json_array('123' like '123');
|
|
json_array('123' like '123')
|
|
[true]
|
|
select json_array('abcdef' rlike 'a.*c.*');
|
|
json_array('abcdef' rlike 'a.*c.*')
|
|
[true]
|
|
select json_array(is_ipv4('127.0.0.1'));
|
|
json_array(is_ipv4('127.0.0.1'))
|
|
[true]
|
|
select json_array(is_ipv6('1a6b:8888:ff66:77ee:0000:1234:5678:bcde'));
|
|
json_array(is_ipv6('1a6b:8888:ff66:77ee:0000:1234:5678:bcde'))
|
|
[true]
|
|
drop table if exists t7;
|
|
create table t7 (col0 SMALLINT, col1 VARBINARY(1), col2 DATE, col3 BIGINT, col4 BINARY(166));
|
|
insert into t7 values ('32767', '', '1000-01-03', '-0', '11101011');
|
|
select col2 = 1 from t7;
|
|
col2 = 1
|
|
0
|
|
select col2 != 1 from t7;
|
|
col2 != 1
|
|
1
|
|
select CAST("9223372036854775808" as json);
|
|
CAST("9223372036854775808" as json)
|
|
9223372036854775808
|
|
select json_type(CAST("9223372036854775808" as json));
|
|
json_type(CAST("9223372036854775808" as json))
|
|
UNSIGNED INTEGER
|
|
select CAST(9223372036854775808 as json);
|
|
CAST(9223372036854775808 as json)
|
|
9223372036854775808
|
|
select json_type(CAST(9223372036854775808 as json));
|
|
json_type(CAST(9223372036854775808 as json))
|
|
UNSIGNED INTEGER
|
|
select CAST(-9223372036854775808 as json);
|
|
CAST(-9223372036854775808 as json)
|
|
-9223372036854775808
|
|
select json_type(CAST(-9223372036854775808 as json));
|
|
json_type(CAST(-9223372036854775808 as json))
|
|
INTEGER
|
|
drop table if exists tx2;
|
|
create table tx2 (col json);
|
|
insert into tx2 values (json_array("3")),(json_array("3")),(json_array("3")),(json_array("3"));
|
|
insert into tx2 values (json_array(3.0));
|
|
insert into tx2 values (json_array(3));
|
|
insert into tx2 values (json_array(3.0));
|
|
insert into tx2 values (json_array(-3));
|
|
insert into tx2 values (json_array(-3.0));
|
|
insert into tx2 values (json_array(922337203685477580));
|
|
insert into tx2 values (json_array(922337203685477581)),(json_array(922337203685477581)),(json_array(922337203685477581)),(json_array(922337203685477581)),(json_array(922337203685477581));
|
|
select col, count(1) c from tx2 group by col order by c desc;
|
|
col c
|
|
[922337203685477581] 5
|
|
["3"] 4
|
|
[3] 3
|
|
[-3] 2
|
|
[922337203685477580] 1
|
|
drop table if exists t12205;
|
|
create table t12205(
|
|
`col_varchar_64` varchar(64) DEFAULT NULL,
|
|
`col_varchar_64_key` varchar(64) DEFAULT NULL
|
|
);
|
|
insert into t12205 values('-1038024704','-527892480');
|
|
select SEC_TO_TIME( ( `col_varchar_64` & `col_varchar_64_key` ) ),`col_varchar_64` & `col_varchar_64_key` from t12205;
|
|
SEC_TO_TIME( ( `col_varchar_64` & `col_varchar_64_key` ) ) `col_varchar_64` & `col_varchar_64_key`
|
|
838:59:59 18446744072635875328
|
|
Level Code Message
|
|
Warning 1292 Truncated incorrect time value: '18446744072635875000'
|
|
drop table if exists t;
|
|
create table t(1e int);
|
|
insert into t values (1);
|
|
select t.1e from expression__issues.t;
|
|
1e
|
|
1
|
|
drop table if exists t;
|
|
create table t(99e int, r10 int);
|
|
insert into t values (1, 10), (2, 2);
|
|
select 99e+r10 from t;
|
|
99e+r10
|
|
11
|
|
4
|
|
select .78$123;
|
|
$123
|
|
0.78
|
|
select .78$421+1;
|
|
Error 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your TiDB version for the right syntax to use line 1 column 15 near "+1"
|
|
select t. `r10` > 3 from t;
|
|
t. `r10` > 3
|
|
1
|
|
0
|
|
select * from t where t. `r10` > 3;
|
|
99e r10
|
|
1 10
|
|
drop table if exists t1;
|
|
create table t1 (f1 decimal(5,5));
|
|
insert into t1 values (-0.12345);
|
|
select concat(f1) from t1;
|
|
concat(f1)
|
|
-0.12345
|
|
drop table if exists t12206;
|
|
create table t12206(
|
|
`col_tinyint_unsigned` tinyint(3) unsigned DEFAULT NULL,
|
|
`col_double_unsigned` double unsigned DEFAULT NULL,
|
|
`col_year_key` year(4) DEFAULT NULL
|
|
);
|
|
insert into t12206 values(73,0,0000);
|
|
SELECT TIME_FORMAT( `col_tinyint_unsigned`, ( IFNULL( `col_double_unsigned`, `col_year_key` ) ) ) AS field1 FROM `t12206`;
|
|
field1
|
|
NULL
|
|
Level Code Message
|
|
Warning 1292 Truncated incorrect time value: '73'
|
|
drop table if exists t12209;
|
|
create table t12209(a bigint(20));
|
|
insert into t12209 values(1);
|
|
select `a` DIV ( ROUND( ( SCHEMA() ), '1978-05-18 03:35:52.043591' ) ) from `t12209`;
|
|
`a` DIV ( ROUND( ( SCHEMA() ), '1978-05-18 03:35:52.043591' ) )
|
|
NULL
|
|
drop table if exists ta, tb;
|
|
CREATE TABLE `ta` ( `k` varchar(32) NOT NULL DEFAULT ' ', `c0` varchar(32) NOT NULL DEFAULT ' ', `c` varchar(18) NOT NULL DEFAULT ' ', `e0` varchar(1) NOT NULL DEFAULT ' ', PRIMARY KEY (`k`,`c0`,`c`), KEY `idx` (`c`,`e0`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
|
|
CREATE TABLE `tb` ( `k` varchar(32) NOT NULL DEFAULT ' ', `e` int(11) NOT NULL DEFAULT '0', `i` int(11) NOT NULL DEFAULT '0', `s` varchar(1) NOT NULL DEFAULT ' ', `c` varchar(50) NOT NULL DEFAULT ' ', PRIMARY KEY (`k`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
|
|
prepare stmt from "select a.* from ta a left join tb b on a.k = b.k where (a.k <> '000000' and ((b.s = ? and i = ? ) or (b.s = ? and e = ?) or (b.s not in(?, ?))) and b.c like '%1%') or (a.c <> '000000' and a.k = '000000')";
|
|
set @a=3;set @b=20200414;set @c='a';set @d=20200414;set @e=3;set @f='a';
|
|
execute stmt using @a,@b,@c,@d,@e,@f;
|
|
k c0 c e0
|
|
drop table if exists t;
|
|
create table t(
|
|
a enum('a','','c'),
|
|
b enum('0','1','2'),
|
|
c set('a','','c'),
|
|
d set('0','1','2')
|
|
);
|
|
insert into t values(1,1,1,1),(2,2,2,2),(3,3,3,3);
|
|
set @@sql_mode = '';
|
|
insert into t values('','','','');
|
|
select * from t;
|
|
a b c d
|
|
a 0 a 0
|
|
1 1
|
|
c 2 a, 0,1
|
|
|
|
select a from t where a;
|
|
a
|
|
a
|
|
|
|
c
|
|
|
|
select b from t where b;
|
|
b
|
|
0
|
|
1
|
|
2
|
|
select c from t where c;
|
|
c
|
|
a
|
|
|
|
a,
|
|
|
|
select d from t where d;
|
|
d
|
|
0
|
|
1
|
|
0,1
|
|
set @@sql_mode=default;
|
|
drop table if exists t;
|
|
create table t(a year);
|
|
insert into t values(2002);
|
|
select * from t where a=2;
|
|
a
|
|
2002
|
|
select * from t where a='2';
|
|
a
|
|
2002
|
|
drop table if exists t1, t2;
|
|
create table t1 (c_int int, c_str varchar(40), primary key (c_int, c_str) clustered, key(c_int) );
|
|
create table t2 like t1 ;
|
|
insert into t1 values (1, 'crazy lumiere'), (10, 'goofy mestorf');
|
|
insert into t2 select * from t1 ;
|
|
select (select t2.c_str from t2 where t2.c_str = t1.c_str and t2.c_int = 10 order by t2.c_str limit 1) x from t1;
|
|
x
|
|
NULL
|
|
goofy mestorf
|
|
drop table if exists t1;
|
|
create table t1(c1 int);
|
|
insert into t1 values(-2147483648), (-2147483648), (null);
|
|
select count(*) from t1 where c1 > (select sum(c1) from t1);
|
|
count(*)
|
|
2
|
|
drop table if exists t;
|
|
create table t(a int primary key, b set('Alice','Bob') DEFAULT NULL);
|
|
insert into t value(1,'Bob');
|
|
select max(b) + 0 from t group by a;
|
|
max(b) + 0
|
|
2
|
|
drop table if exists t;
|
|
create table t(a int, b set('Alice','Bob') DEFAULT NULL);
|
|
insert into t value(1,'Bob');
|
|
select max(b) + 0 from t group by a;
|
|
max(b) + 0
|
|
2
|
|
drop table if exists test_decimal,test_t;
|
|
create table test_decimal(col_decimal decimal(10,0));
|
|
insert into test_decimal values(null),(8);
|
|
create table test_t(a int(11), b decimal(32,0));
|
|
insert into test_t values(1,4),(2,4),(5,4),(7,4),(9,4);
|
|
SELECT ( test_decimal . `col_decimal` , test_decimal . `col_decimal` ) IN ( select * from test_t ) as field1 FROM test_decimal;
|
|
field1
|
|
NULL
|
|
0
|
|
drop table if exists foo, bar;
|
|
create table foo(a decimal(65,0));
|
|
create table bar(a decimal(65,0), b decimal(65,0));
|
|
insert into bar values(0,0),(1,1),(2,2);
|
|
insert into foo select if(b>0, if(a/b>1, 1, 2), null) from bar;
|
|
drop table if exists t1_1, t2_1;
|
|
CREATE TABLE `t1_1` (`col1` double DEFAULT NULL, `col2` double DEFAULT NULL);
|
|
CREATE TABLE `t2_1` (`col1` varchar(20) DEFAULT NULL, `col2` double DEFAULT NULL);
|
|
insert into t1_1 values(12.991, null), (12.991, null);
|
|
insert into t2_1(col2) values(87), (-9.183), (-9.183);
|
|
set @@tidb_enable_vectorized_expression = false;
|
|
select t1.col1, t2.col1, t2.col2 from t1_1 t1 inner join t2_1 t2 on t1.col1 not in (1,t2.col1,t2.col2) order by 1,2,3;
|
|
col1 col1 col2
|
|
set @@tidb_enable_vectorized_expression = true;
|
|
select t1.col1, t2.col1, t2.col2 from t1_1 t1 inner join t2_1 t2 on t1.col1 not in (1,t2.col1,t2.col2) order by 1,2,3;
|
|
col1 col1 col2
|
|
drop table if exists tbl_6, tbl_17;
|
|
create table tbl_6 (col_31 year, index(col_31));
|
|
create table tbl_17 (col_102 int, col_105 int);
|
|
replace into tbl_17 (col_102, col_105) values (9999, 0);
|
|
select tbl_6.col_31 from tbl_6 where col_31 in (select col_102 from tbl_17 where tbl_17.col_102 = 9999 and tbl_17.col_105 = 0);
|
|
col_31
|
|
drop table if exists tbl_0,tbl_9;
|
|
CREATE TABLE `tbl_9` (
|
|
`col_54` mediumint NOT NULL DEFAULT '2412996',
|
|
`col_55` int NOT NULL,
|
|
`col_56` bigint unsigned NOT NULL,
|
|
`col_57` varchar(108) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
|
|
PRIMARY KEY (`col_57`(3),`col_55`,`col_56`,`col_54`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
|
|
CREATE TABLE `tbl_0` (
|
|
`col_76` bigint(20) unsigned DEFAULT NULL,
|
|
`col_1` time NOT NULL DEFAULT '13:11:28',
|
|
`col_2` datetime DEFAULT '1990-07-29 00:00:00',
|
|
`col_3` date NOT NULL DEFAULT '1976-09-16',
|
|
`col_4` date DEFAULT NULL,
|
|
`col_143` varbinary(208) DEFAULT 'lXRTXUkTeWaJ',
|
|
KEY `idx_0` (`col_2`,`col_1`,`col_76`,`col_4`,`col_3`),
|
|
PRIMARY KEY (`col_1`,`col_3`) /*T![clustered_index] NONCLUSTERED */,
|
|
KEY `idx_2` (`col_1`,`col_4`,`col_76`,`col_3`),
|
|
KEY `idx_3` (`col_4`,`col_76`,`col_3`,`col_2`,`col_1`),
|
|
UNIQUE KEY `idx_4` (`col_76`,`col_3`,`col_1`,`col_4`),
|
|
KEY `idx_5` (`col_3`,`col_4`,`col_76`,`col_2`),
|
|
KEY `idx_6` (`col_2`),
|
|
KEY `idx_7` (`col_76`,`col_3`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
|
|
insert into tbl_9 values (-5765442,-597990898,384599625723370089,"ZdfkUJiHcOfi");
|
|
(select col_76,col_1,col_143,col_2 from tbl_0) union (select col_54,col_57,col_55,col_56 from tbl_9);
|
|
col_76 col_1 col_143 col_2
|
|
-5765442 ZdfkUJiHcOfi -597990898 384599625723370089
|
|
drop table if exists t1;
|
|
create table t1 (c_int int not null);
|
|
insert into t1 values (1), (2), (3),(1),(2),(3);
|
|
drop table if exists t2;
|
|
create table t2 (c_int int not null);
|
|
insert into t2 values (1), (2), (3),(1),(2),(3);
|
|
select (select count(distinct c_int) from t2 where c_int >= t1.c_int) c1, (select count(distinct c_int) from t2 where c_int >= t1.c_int) c2 from t1 group by c_int;
|
|
c1 c2
|
|
3 3
|
|
2 2
|
|
1 1
|
|
drop table if exists t;
|
|
CREATE TABLE `t` (
|
|
`COL1` tinyint(45) NOT NULL,
|
|
`COL2` tinyint(45) NOT NULL,
|
|
PRIMARY KEY (`COL1`,`COL2`) /*T![clustered_index] NONCLUSTERED */
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
|
|
insert into t values(122,100),(124,-22),(124,34),(127,103);
|
|
SELECT col2 FROM t AS T1 WHERE ( SELECT count(DISTINCT COL1, COL2) FROM t AS T2 WHERE T2.COL1 > T1.COL1 ) > 2 ;
|
|
col2
|
|
100
|
|
select extract(hour_second from "-838:59:59.00");
|
|
extract(hour_second from "-838:59:59.00")
|
|
-8385959
|
|
drop table if exists t;
|
|
create table t(c1 varchar(100));
|
|
insert into t values('-838:59:59.00'), ('700:59:59.00');
|
|
select extract(hour_second from c1) from t order by c1;
|
|
extract(hour_second from c1)
|
|
-8385959
|
|
7005959
|
|
select a + 1 as f from (select cast(0xfffffffffffffff0 as unsigned) as a union select cast(1 as unsigned)) t having f != 2;
|
|
f
|
|
18446744073709551601
|
|
drop table if exists PK_TCOLLATION3966STROBJSTROBJ;
|
|
CREATE TABLE `PK_TCOLLATION3966STROBJSTROBJ` (
|
|
`COL1` enum('ll','aa','bb','cc','dd','ee') COLLATE utf8_general_ci NOT NULL,
|
|
`COL2` varchar(20) COLLATE utf8_general_ci DEFAULT NULL,
|
|
PRIMARY KEY (`COL1`) /*T![clustered_index] CLUSTERED */
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
|
|
insert into PK_TCOLLATION3966STROBJSTROBJ values("ee", "tttt");
|
|
SELECT col1, COL2 FROM PK_TCOLLATION3966STROBJSTROBJ WHERE COL1 IN ('notexist','6') and col2 not in ("abcd");
|
|
col1 COL2
|
|
drop table if exists perf_offline_day;
|
|
CREATE TABLE perf_offline_day (
|
|
uuid varchar(50),
|
|
ts timestamp NOT NULL,
|
|
user_id varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
|
|
platform varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
|
|
host_id bigint(20) DEFAULT NULL,
|
|
PRIMARY KEY (uuid,ts) /*T![clustered_index] NONCLUSTERED */
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
|
|
PARTITION BY RANGE ( UNIX_TIMESTAMP(ts) ) (
|
|
PARTITION p20210906 VALUES LESS THAN (1630944000),
|
|
PARTITION p20210907 VALUES LESS THAN (1631030400),
|
|
PARTITION p20210908 VALUES LESS THAN (1631116800),
|
|
PARTITION p20210909 VALUES LESS THAN (1631203200)
|
|
);
|
|
set @@tidb_partition_prune_mode = 'static';
|
|
INSERT INTO `perf_offline_day` VALUES ('dd082c8a-3bab-4431-943a-348fe0592abd','2021-09-08 13:00:07','Xg9C8zq81jGNbugM', 'pc', 12345);
|
|
SELECT cast(floor(hour(ts) / 4) as char) as win_start FROM perf_offline_day partition (p20210907, p20210908) GROUP BY win_start;
|
|
win_start
|
|
3
|
|
drop table if exists t;
|
|
create table t(a time(4));
|
|
insert into t values("-838:59:59.000000");
|
|
insert into t values("838:59:59.000000");
|
|
set tidb_enable_vectorized_expression = on;
|
|
select hour(a) from t;
|
|
hour(a)
|
|
838
|
|
838
|
|
set tidb_enable_vectorized_expression = off;
|
|
select hour(a) from t;
|
|
hour(a)
|
|
838
|
|
838
|
|
set tidb_enable_vectorized_expression = default;
|
|
drop table if exists t;
|
|
create table t(a enum("a", "b"), b enum("a", "b"), c bool);
|
|
insert into t values("a", "a", 1);
|
|
select * from t t1 right join t t2 on t1.a=t2.b and t1.a= t2.c;
|
|
a b c a b c
|
|
a a 1 a a 1
|
|
drop table if exists t;
|
|
create table t(a enum("a", "b"), b enum("a", "b"), c bool, d int, index idx(d));
|
|
insert into t values("a", "a", 1, 1);
|
|
select /*+ inl_hash_join(t1) */ * from t t1 right join t t2 on t1.a=t2.b and t1.a= t2.c and t1.d=t2.d;
|
|
a b c d a b c d
|
|
a a 1 1 a a 1 1
|
|
drop table if exists t1;
|
|
create table t1(c1 datetime);
|
|
insert into t1 values('2021-12-12 10:10:10.000');
|
|
set tidb_enable_vectorized_expression = on;
|
|
select greatest(c1, '99999999999999') from t1;
|
|
greatest(c1, '99999999999999')
|
|
99999999999999
|
|
select least(c1, '99999999999999') from t1;
|
|
least(c1, '99999999999999')
|
|
2021-12-12 10:10:10
|
|
set tidb_enable_vectorized_expression = off;
|
|
select greatest(c1, '99999999999999') from t1;
|
|
greatest(c1, '99999999999999')
|
|
99999999999999
|
|
select least(c1, '99999999999999') from t1;
|
|
least(c1, '99999999999999')
|
|
2021-12-12 10:10:10
|
|
set tidb_enable_vectorized_expression = default;
|
|
drop table if exists t;
|
|
create table t(a time(4));
|
|
insert into t values("-700:10:10.123456111");
|
|
insert into t values("700:10:10.123456111");
|
|
set tidb_enable_vectorized_expression = on;
|
|
select microsecond(a) from t;
|
|
microsecond(a)
|
|
123500
|
|
123500
|
|
set tidb_enable_vectorized_expression = off;
|
|
select microsecond(a) from t;
|
|
microsecond(a)
|
|
123500
|
|
123500
|
|
set tidb_enable_vectorized_expression = default;
|
|
set tidb_enable_vectorized_expression = on;
|
|
select char(123, NULL, 123);
|
|
char(123, NULL, 123)
|
|
{{
|
|
select char(NULL, 123, 123);
|
|
char(NULL, 123, 123)
|
|
{{
|
|
set tidb_enable_vectorized_expression = off;
|
|
select char(123, NULL, 123);
|
|
char(123, NULL, 123)
|
|
{{
|
|
select char(NULL, 123, 123);
|
|
char(NULL, 123, 123)
|
|
{{
|
|
set tidb_enable_vectorized_expression = default;
|
|
drop table if exists t1;
|
|
create table t1(c1 bigint unsigned, c2 bigint unsigned);
|
|
insert into t1 values(9223372036854775808, 9223372036854775809);
|
|
select greatest(c1, c2) from t1;
|
|
greatest(c1, c2)
|
|
9223372036854775809
|
|
drop table if exists t;
|
|
create table t(a int);
|
|
insert into t values(1),(1),(2),(2);
|
|
set tidb_window_concurrency = 1;
|
|
select (FIRST_VALUE(1) over (partition by v.a)) as c3 from (select a from t where t.a = (select a from t t2 where t.a = t2.a)) as v;
|
|
Error 1242 (21000): Subquery returns more than 1 row
|
|
drop table if exists t1,t2;
|
|
CREATE TABLE `t1` (
|
|
`c1` enum('Alice','Bob','Charlie','David') NOT NULL,
|
|
`c2` blob NOT NULL,
|
|
PRIMARY KEY (`c2`(5)),
|
|
UNIQUE KEY `idx_89` (`c1`)
|
|
);
|
|
CREATE TABLE `t2` (
|
|
`c1` enum('Alice','Bob','Charlie','David') NOT NULL DEFAULT 'Alice',
|
|
`c2` set('Alice','Bob','Charlie','David') NOT NULL DEFAULT 'David',
|
|
`c3` enum('Alice','Bob','Charlie','David') NOT NULL,
|
|
PRIMARY KEY (`c3`,`c2`)
|
|
);
|
|
insert into t1 values('Charlie','');
|
|
insert into t2 values('Charlie','Charlie','Alice');
|
|
select * from t2 where c3 in (select c2 from t1);
|
|
c1 c2 c3
|
|
select * from t2 where c2 in (select c2 from t1);
|
|
c1 c2 c3
|
|
drop table if exists t1;
|
|
CREATE TABLE t1 (a text)character set utf8 ;
|
|
INSERT INTO t1 VALUES (REPEAT(0125,200000000));
|
|
Error 1301 (HY000): Result of repeat() was larger than max_allowed_packet (67108864) - truncated
|
|
select * from t1;
|
|
a
|
|
insert into t1 (a) values ('a'),('b');
|
|
insert into t1 select REPEAT(a,200000000) from t1;
|
|
Error 1301 (HY000): Result of repeat() was larger than max_allowed_packet (67108864) - truncated
|
|
select a from t1 order by a;
|
|
a
|
|
a
|
|
b
|
|
insert into t1 values (cast("a" as binary(4294967295)));
|
|
Error 1301 (HY000): Result of cast_as_binary() was larger than max_allowed_packet (67108864) - truncated
|
|
select a from t1 order by a;
|
|
a
|
|
a
|
|
b
|
|
INSERT IGNORE INTO t1 VALUES (REPEAT(0125,200000000));
|
|
Level Code Message
|
|
Warning 1301 Result of repeat() was larger than max_allowed_packet (67108864) - truncated
|
|
select a from t1 order by a;
|
|
a
|
|
NULL
|
|
a
|
|
b
|
|
drop table if exists t;
|
|
create table t(a varchar(32)) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
insert into t values('ʞ'), ('İ'), ('ß');
|
|
set @@tidb_enable_vectorized_expression = false;
|
|
select binary upper(a), lower(a) from t order by upper(a);
|
|
binary upper(a) lower(a)
|
|
İ i
|
|
ß ß
|
|
Ʞ ʞ
|
|
select distinct upper(a), lower(a) from t order by upper(a);
|
|
upper(a) lower(a)
|
|
İ i
|
|
ß ß
|
|
Ʞ ʞ
|
|
set @@tidb_enable_vectorized_expression = true;
|
|
select binary upper(a), lower(a) from t order by upper(a);
|
|
binary upper(a) lower(a)
|
|
İ i
|
|
ß ß
|
|
Ʞ ʞ
|
|
select distinct upper(a), lower(a) from t order by upper(a);
|
|
upper(a) lower(a)
|
|
İ i
|
|
ß ß
|
|
Ʞ ʞ
|
|
set @@tidb_enable_vectorized_expression = default;
|
|
drop table if exists t;
|
|
create table t(a varchar(32)) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
insert into t values(''), ('');
|
|
set @@tidb_enable_vectorized_expression = true;
|
|
select compress(a) from t;
|
|
compress(a)
|
|
|
|
|
|
set @@tidb_enable_vectorized_expression = false;
|
|
select compress(a) from t;
|
|
compress(a)
|
|
|
|
|
|
set @@tidb_enable_vectorized_expression = default;
|
|
drop table if exists t;
|
|
create table t(a varchar(32));
|
|
insert into t values(date_add(cast('00:00:00' as time), interval 1.1 second));
|
|
select * from t;
|
|
a
|
|
00:00:01.1
|
|
select cast(date_add(cast('00:00:00' as time), interval 1.1 second) as char);
|
|
cast(date_add(cast('00:00:00' as time), interval 1.1 second) as char)
|
|
00:00:01.1
|
|
select cast(date_add(cast('00:00:00' as time), interval 1.1 microsecond) as char);
|
|
cast(date_add(cast('00:00:00' as time), interval 1.1 microsecond) as char)
|
|
00:00:00.000001
|
|
select cast(date_add(cast('00:00:00' as time), interval 1000000 microsecond) as char);
|
|
cast(date_add(cast('00:00:00' as time), interval 1000000 microsecond) as char)
|
|
00:00:01.000000
|
|
select cast(date_add(cast('00:00:00' as time), interval 1.1111119 second) as char);
|
|
cast(date_add(cast('00:00:00' as time), interval 1.1111119 second) as char)
|
|
00:00:01.111111
|
|
select cast(date_add(cast('00:00:00' as time), interval 1.0 second) as char);
|
|
cast(date_add(cast('00:00:00' as time), interval 1.0 second) as char)
|
|
00:00:01.0
|
|
select cast(date_add(cast('00:00:00' as time), interval 1.1 second_microsecond) as char);
|
|
cast(date_add(cast('00:00:00' as time), interval 1.1 second_microsecond) as char)
|
|
00:00:01.100000
|
|
select cast(date_add(cast('00:00:00' as time), interval 1111111 second_microsecond) as char);
|
|
cast(date_add(cast('00:00:00' as time), interval 1111111 second_microsecond) as char)
|
|
00:00:01.111111
|
|
select cast(date_add(cast('00:00:00' as time), interval 1.1 minute_microsecond) as char);
|
|
cast(date_add(cast('00:00:00' as time), interval 1.1 minute_microsecond) as char)
|
|
00:00:01.100000
|
|
select cast(date_add(cast('00:00:00' as time), interval 1111111 minute_microsecond) as char);
|
|
cast(date_add(cast('00:00:00' as time), interval 1111111 minute_microsecond) as char)
|
|
00:00:01.111111
|
|
select cast(date_add(cast('00:00:00' as time), interval 1.1 minute_second) as char);
|
|
cast(date_add(cast('00:00:00' as time), interval 1.1 minute_second) as char)
|
|
00:01:01
|
|
select cast(date_add(cast('00:00:00' as time), interval 1111111 minute_second) as char);
|
|
cast(date_add(cast('00:00:00' as time), interval 1111111 minute_second) as char)
|
|
308:38:31
|
|
select cast(date_add(cast('00:00:00' as time), interval 1.1 hour_microsecond) as char);
|
|
cast(date_add(cast('00:00:00' as time), interval 1.1 hour_microsecond) as char)
|
|
00:00:01.100000
|
|
select cast(date_add(cast('00:00:00' as time), interval 1111111 hour_microsecond) as char);
|
|
cast(date_add(cast('00:00:00' as time), interval 1111111 hour_microsecond) as char)
|
|
00:00:01.111111
|
|
select cast(date_add(cast('00:00:00' as time), interval 1.1 hour_second) as char);
|
|
cast(date_add(cast('00:00:00' as time), interval 1.1 hour_second) as char)
|
|
00:01:01
|
|
select cast(date_add(cast('00:00:00' as time), interval 1111111 hour_second) as char);
|
|
cast(date_add(cast('00:00:00' as time), interval 1111111 hour_second) as char)
|
|
308:38:31
|
|
select cast(date_add(cast('00:00:00' as time), interval 1.1 hour_minute) as char);
|
|
cast(date_add(cast('00:00:00' as time), interval 1.1 hour_minute) as char)
|
|
01:01:00
|
|
select cast(date_add(cast('00:00:00' as time), interval 1.1 day_microsecond) as char);
|
|
cast(date_add(cast('00:00:00' as time), interval 1.1 day_microsecond) as char)
|
|
00:00:01.100000
|
|
select cast(date_add(cast('00:00:00' as time), interval 1111111 day_microsecond) as char);
|
|
cast(date_add(cast('00:00:00' as time), interval 1111111 day_microsecond) as char)
|
|
00:00:01.111111
|
|
drop table if exists t;
|
|
create table t(i int, c varchar(32));
|
|
insert into t values(1, date_add(cast('2001-01-01 00:00:00' as datetime), interval 1 second));
|
|
insert into t values(2, date_add(cast('2001-01-01 00:00:00' as datetime(6)), interval 1 second));
|
|
insert into t values(3, date_add(cast('2001-01-01 00:00:00' as datetime), interval 1.1 second));
|
|
insert into t values(4, date_add(cast('2001-01-01 00:00:00' as datetime(6)), interval 1.1 second));
|
|
insert into t values(5, date_add(cast('00:00:00' as time), interval 1.1 second));
|
|
select c from t order by i;
|
|
c
|
|
2001-01-01 00:00:01
|
|
2001-01-01 00:00:01.000000
|
|
2001-01-01 00:00:01.1
|
|
2001-01-01 00:00:01.100000
|
|
00:00:01.1
|
|
set time_zone = '+00:00';
|
|
drop table if exists t;
|
|
create table t(ts timestamp(6) not null default current_timestamp(6) on update current_timestamp(6));
|
|
insert into t values('1970-01-01 01:00:01.000000');
|
|
insert into t values('1970-01-01 01:00:01.000001');
|
|
insert into t values('1971-01-01 01:00:00.000000');
|
|
insert into t values('1971-01-01 01:00:00.000001');
|
|
insert into t values('2001-01-01 00:00:00.000000');
|
|
insert into t values('2001-01-01 00:00:00.000001');
|
|
insert into t values('2001-01-01 01:00:00.000000');
|
|
insert into t values('2001-01-01 01:00:00.000001');
|
|
select date_add(ts, interval 1 minute) from t order by ts;
|
|
date_add(ts, interval 1 minute)
|
|
1970-01-01 01:01:01.000000
|
|
1970-01-01 01:01:01.000001
|
|
1971-01-01 01:01:00.000000
|
|
1971-01-01 01:01:00.000001
|
|
2001-01-01 00:01:00.000000
|
|
2001-01-01 00:01:00.000001
|
|
2001-01-01 01:01:00.000000
|
|
2001-01-01 01:01:00.000001
|
|
select date_sub(ts, interval 1 minute) from t order by ts;
|
|
date_sub(ts, interval 1 minute)
|
|
1970-01-01 00:59:01.000000
|
|
1970-01-01 00:59:01.000001
|
|
1971-01-01 00:59:00.000000
|
|
1971-01-01 00:59:00.000001
|
|
2000-12-31 23:59:00.000000
|
|
2000-12-31 23:59:00.000001
|
|
2001-01-01 00:59:00.000000
|
|
2001-01-01 00:59:00.000001
|
|
set time_zone = default;
|
|
set time_zone = '+00:00';
|
|
drop table if exists t;
|
|
create table t (tm_fsp0 time(0), tm_fsp1 time(1), tm_fsp3 time(3),tm_fsp6 time(6), d date, dt_fsp0 datetime(0), dt_fsp1 datetime(1), dt_fsp3 datetime(3), dt_fsp6 datetime(6));
|
|
insert into t values(null, '12:12:01.1', '12:12:02.123', '12:12:03.123456', '20221212', null, '2020/12/11 12:12:11.1', '2020/12/12 12:12:12.123', '2020/12/13 12:12:13.123456');
|
|
insert into t values('12:12:00', null, '12:12:02.123', '12:12:03.123456', '20221212', '2020/12/10 12:12:10', null, '2020/12/12 12:12:12.123', '2020/12/13 12:12:13.123456');
|
|
insert into t values('12:12:00', '12:12:01.1', null, '12:12:03.123456', '20221212', '2020/12/10 12:12:10', '2020/12/11 12:12:11.1', null, '2020/12/13 12:12:13.123456');
|
|
insert into t values('12:12:00', '12:12:01.1', '12:12:02.123', null, '20221212', '2020/12/10 12:12:10', '2020/12/11 12:12:11.1', '2020/12/12 12:12:12.123', null);
|
|
select coalesce(null, tm_fsp0, tm_fsp1, tm_fsp3, tm_fsp6) from t;
|
|
coalesce(null, tm_fsp0, tm_fsp1, tm_fsp3, tm_fsp6)
|
|
12:12:01.100000
|
|
12:12:00.000000
|
|
12:12:00.000000
|
|
12:12:00.000000
|
|
select coalesce(tm_fsp1, tm_fsp0, tm_fsp3) from t;
|
|
coalesce(tm_fsp1, tm_fsp0, tm_fsp3)
|
|
12:12:01.100
|
|
12:12:00.000
|
|
12:12:01.100
|
|
12:12:01.100
|
|
select coalesce(tm_fsp3, tm_fsp0) from t;
|
|
coalesce(tm_fsp3, tm_fsp0)
|
|
12:12:02.123
|
|
12:12:02.123
|
|
12:12:00.000
|
|
12:12:02.123
|
|
select coalesce(tm_fsp6) from t;
|
|
coalesce(tm_fsp6)
|
|
12:12:03.123456
|
|
12:12:03.123456
|
|
12:12:03.123456
|
|
NULL
|
|
select coalesce(null, dt_fsp0, dt_fsp1, dt_fsp3, dt_fsp6) from t;
|
|
coalesce(null, dt_fsp0, dt_fsp1, dt_fsp3, dt_fsp6)
|
|
2020-12-11 12:12:11.100000
|
|
2020-12-10 12:12:10.000000
|
|
2020-12-10 12:12:10.000000
|
|
2020-12-10 12:12:10.000000
|
|
select coalesce(dt_fsp0, dt_fsp1, dt_fsp3) from t;
|
|
coalesce(dt_fsp0, dt_fsp1, dt_fsp3)
|
|
2020-12-11 12:12:11.100
|
|
2020-12-10 12:12:10.000
|
|
2020-12-10 12:12:10.000
|
|
2020-12-10 12:12:10.000
|
|
select coalesce(dt_fsp3, dt_fsp0) from t;
|
|
coalesce(dt_fsp3, dt_fsp0)
|
|
2020-12-12 12:12:12.123
|
|
2020-12-12 12:12:12.123
|
|
2020-12-10 12:12:10.000
|
|
2020-12-12 12:12:12.123
|
|
select coalesce(dt_fsp6) from t;
|
|
coalesce(dt_fsp6)
|
|
2020-12-13 12:12:13.123456
|
|
2020-12-13 12:12:13.123456
|
|
2020-12-13 12:12:13.123456
|
|
NULL
|
|
select coalesce(null, d) from t;
|
|
coalesce(null, d)
|
|
2022-12-12
|
|
2022-12-12
|
|
2022-12-12
|
|
2022-12-12
|
|
set time_zone = default;
|
|
drop table if exists t;
|
|
create table t (c int primary key, c2 enum('a', 'b'));
|
|
set session sql_mode = '';
|
|
insert into t values(4, 'a');
|
|
insert into t values(4, 0) on duplicate key update c=values(c), c2=values(c2);
|
|
Level Code Message
|
|
Warning 1265 Data truncated for column 'c2' at row 1
|
|
Warning 1265 Data truncated for column 'c2' at row 1
|
|
insert into t values(4, 'a') on duplicate key update c=values(c), c2=values(c2);
|
|
set @@sql_mode=default;
|
|
drop table if exists t0, t1;
|
|
CREATE TABLE t0(c0 BOOL, c1 INT);
|
|
CREATE TABLE t1 LIKE t0;
|
|
CREATE definer='root'@'localhost' VIEW v0(c0) AS SELECT IS_IPV4(t0.c1) FROM t0, t1;
|
|
INSERT INTO t0(c0, c1) VALUES (true, 0);
|
|
INSERT INTO t1(c0, c1) VALUES (true, 2);
|
|
SELECT v0.c0 FROM v0 WHERE (v0.c0)NOT LIKE(BINARY v0.c0);
|
|
c0
|
|
SELECT v0.c0 FROM v0 WHERE (v0.c0)NOT LIKE(BINARY v0.c0) or v0.c0 > 0;
|
|
c0
|
|
select greatest(time '21:00', year(date'20220101'), 23);
|
|
greatest(time '21:00', year(date'20220101'), 23)
|
|
23
|
|
select greatest(time '21:00', date'891001', 120000);
|
|
greatest(time '21:00', date'891001', 120000)
|
|
21:00:00
|
|
select greatest(time '20:00', date'101001', 120101);
|
|
greatest(time '20:00', date'101001', 120101)
|
|
20:00:00
|
|
select greatest(date'101001', '19990329', 120101);
|
|
greatest(date'101001', '19990329', 120101)
|
|
2012-01-01
|
|
select greatest(time '20:00', date'691231');
|
|
greatest(time '20:00', date'691231')
|
|
2069-12-31 00:00:00
|
|
select greatest(date '120301', date'691231');
|
|
greatest(date '120301', date'691231')
|
|
2069-12-31
|
|
select greatest(time '203001', time '2230');
|
|
greatest(time '203001', time '2230')
|
|
20:30:01
|
|
select greatest(timestamp '2021-01-31 00:00:01', timestamp '2021-12-31 12:00:00');
|
|
greatest(timestamp '2021-01-31 00:00:01', timestamp '2021-12-31 12:00:00')
|
|
2021-12-31 12:00:00
|
|
select greatest(time '00:00:01', timestamp '2069-12-31 12:00:00');
|
|
greatest(time '00:00:01', timestamp '2069-12-31 12:00:00')
|
|
2069-12-31 12:00:00
|
|
select greatest(date '21000101', timestamp '2069-12-31 12:00:00');
|
|
greatest(date '21000101', timestamp '2069-12-31 12:00:00')
|
|
2100-01-01 00:00:00
|
|
select greatest(cast('1' as JSON), cast('2' as JSON));
|
|
greatest(cast('1' as JSON), cast('2' as JSON))
|
|
2
|
|
select greatest(time '20:00:00', 120000);
|
|
greatest(time '20:00:00', 120000)
|
|
20:00:00
|
|
select greatest(date '2005-05-05', 20010101, 20040404, 20030303);
|
|
greatest(date '2005-05-05', 20010101, 20040404, 20030303)
|
|
2005-05-05
|
|
select greatest(date '1995-05-05', 19910101, 20050505, 19930303);
|
|
greatest(date '1995-05-05', 19910101, 20050505, 19930303)
|
|
2005-05-05
|
|
drop table if exists t1,t2;
|
|
CREATE TABLE `t1` (a datetime, b date, c time);
|
|
insert into t1 values(timestamp'2021-01-31 00:00:01', '2069-12-31', '20:00:01');
|
|
set tidb_enable_vectorized_expression = on;
|
|
select greatest(c, year(date'20220101'), 23) from t1;
|
|
greatest(c, year(date'20220101'), 23)
|
|
23
|
|
select greatest(c, date'891001', 120000) from t1;
|
|
greatest(c, date'891001', 120000)
|
|
20:00:01
|
|
select greatest(c, date'101001', 120101) from t1;
|
|
greatest(c, date'101001', 120101)
|
|
20:00:01
|
|
select greatest(b, '19990329', 120101) from t1;
|
|
greatest(b, '19990329', 120101)
|
|
2069-12-31
|
|
select greatest(time '20:00', b) from t1;
|
|
greatest(time '20:00', b)
|
|
2069-12-31 00:00:00
|
|
select greatest(date '120301', b) from t1;
|
|
greatest(date '120301', b)
|
|
2069-12-31
|
|
select greatest(c, time '2230') from t1;
|
|
greatest(c, time '2230')
|
|
20:00:01
|
|
select greatest(a, timestamp '2021-12-31 12:00:00') from t1;
|
|
greatest(a, timestamp '2021-12-31 12:00:00')
|
|
2021-12-31 12:00:00
|
|
select greatest(c, timestamp '2069-12-31 12:00:00') from t1;
|
|
greatest(c, timestamp '2069-12-31 12:00:00')
|
|
2069-12-31 12:00:00
|
|
select greatest(date '21000101', a) from t1;
|
|
greatest(date '21000101', a)
|
|
2100-01-01 00:00:00
|
|
select greatest(cast(a as JSON), cast('3' as JSON)) from t1;
|
|
greatest(cast(a as JSON), cast('3' as JSON))
|
|
3
|
|
set tidb_enable_vectorized_expression = default;
|
|
set tidb_enable_prepared_plan_cache=ON;
|
|
DROP TABLE IF EXISTS t1;
|
|
CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY auto_increment, a timestamp NOT NULL);
|
|
INSERT INTO t1 VALUES (null, '2020-05-30 20:30:00');
|
|
PREPARE mystmt FROM 'SELECT * FROM t1 WHERE UNIX_TIMESTAMP(a) >= ?';
|
|
SET @a=1590868800;
|
|
EXECUTE mystmt USING @a;
|
|
id a
|
|
select @@last_plan_from_cache;
|
|
@@last_plan_from_cache
|
|
0
|
|
SET @a=1590868801;
|
|
EXECUTE mystmt USING @a;
|
|
id a
|
|
select @@last_plan_from_cache;
|
|
@@last_plan_from_cache
|
|
1
|
|
prepare stmt from 'select unix_timestamp(?)';
|
|
set @a = '2020-05-30 20:30:00';
|
|
execute stmt using @a;
|
|
unix_timestamp(?)
|
|
1590841800
|
|
select @@last_plan_from_cache;
|
|
@@last_plan_from_cache
|
|
0
|
|
set @a = '2020-06-12 13:47:58';
|
|
execute stmt using @a;
|
|
unix_timestamp(?)
|
|
1591940878
|
|
select @@last_plan_from_cache;
|
|
@@last_plan_from_cache
|
|
0
|
|
drop table if exists t;
|
|
set sql_mode=default;
|
|
create table t(id int, value set ('a','b','c') charset utf8mb4 collate utf8mb4_bin default 'a,b ');
|
|
drop table if exists test;
|
|
create table test(id int, value set ('a','b','c') charset utf8mb4 collate utf8mb4_general_ci default 'a,B ,C');
|
|
drop table if exists t;
|
|
create table t(a char(4) collate utf8_general_ci primary key /*T![clustered_index] clustered */);
|
|
insert into t values('`?');
|
|
explain format='plan_tree' select * from t where a like '`%';
|
|
id task access object operator info
|
|
TableReader root data:Selection
|
|
└─Selection cop[tikv] like(expression__issues.t.a, "`%", 92)
|
|
└─TableRangeScan cop[tikv] table:t range:["\x00`","\x00a"), keep order:false, stats:pseudo
|
|
select * from t where a like '`%';
|
|
a
|
|
`?
|
|
drop table if exists t;
|
|
create table t(a char(4) collate binary primary key /*T![clustered_index] clustered */);
|
|
insert into t values('`?');
|
|
explain format='plan_tree' select * from t where a like '`%';
|
|
id task access object operator info
|
|
TableReader root data:TableRangeScan
|
|
└─TableRangeScan cop[tikv] table:t range:["`","a"), keep order:false, stats:pseudo
|
|
select * from t where a like '`%';
|
|
a
|
|
`?
|
|
drop table if exists t;
|
|
CREATE TABLE `t` ( `a` enum('a','b') DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
|
insert into t values('a');
|
|
select * from t where a = 'A';
|
|
a
|
|
a
|
|
drop table if exists tx;
|
|
CREATE TABLE `tx` ( `a` int(11) NOT NULL,`b` varchar(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL);
|
|
insert into tx values (1, 'a'), (2, 'À'), (3, 'á'), (4, 'à'), (5, 'b'), (6, 'c'), (7, ' ');
|
|
select count(distinct(b)) from tx;
|
|
count(distinct(b))
|
|
4
|
|
drop table if exists tx;
|
|
CREATE TABLE `tx` ( `a` int(11) NOT NULL,`b` varchar(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL, `c` varchar(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL);
|
|
insert into tx values (1, 'a', 'a'), (2, 'A ', 'a '), (3, 'A', 'A'), (4, 'a ', 'A ');
|
|
select count(distinct b) from tx;
|
|
count(distinct b)
|
|
1
|
|
select count(distinct c) from tx;
|
|
count(distinct c)
|
|
2
|
|
select count(distinct b, c) from tx where a < 3;
|
|
count(distinct b, c)
|
|
1
|
|
select approx_count_distinct(b) from tx;
|
|
approx_count_distinct(b)
|
|
1
|
|
select approx_count_distinct(c) from tx;
|
|
approx_count_distinct(c)
|
|
2
|
|
select approx_count_distinct(b, c) from tx where a < 3;
|
|
approx_count_distinct(b, c)
|
|
1
|
|
drop table if exists t;
|
|
create table t(a enum('a', 'a ')) charset utf8 collate utf8_bin;
|
|
Error 1291 (HY000): Column 'a' has duplicated value 'a' in ENUM
|
|
create table t(a enum('a', 'Á')) charset utf8 collate utf8_general_ci;
|
|
Error 1291 (HY000): Column 'a' has duplicated value 'Á' in ENUM
|
|
create table t(a enum('a', 'a ')) charset utf8mb4 collate utf8mb4_bin;
|
|
Error 1291 (HY000): Column 'a' has duplicated value 'a' in ENUM
|
|
create table t(a enum('a', 'A')) charset utf8 collate utf8_bin;
|
|
drop table if exists t3;
|
|
create table t3(a enum('a', 'A')) charset utf8mb4 collate utf8mb4_bin;
|
|
drop table if exists t;
|
|
create table t(a varchar(10) collate utf8mb4_bin, b varchar(10) collate utf8mb4_general_ci);
|
|
insert into t (a, b) values ('a', 'A');
|
|
select * from t t1, t t2 where t1.a = t2.b collate utf8mb4_general_ci;
|
|
a b a b
|
|
a A a A
|
|
select * from t t1 left join t t2 on t1.a = t2.b collate utf8mb4_general_ci;
|
|
a b a b
|
|
a A a A
|
|
set names utf8mb4 collate utf8mb4_general_ci;
|
|
select collation(concat(1 collate `binary`));
|
|
collation(concat(1 collate `binary`))
|
|
binary
|
|
select coercibility(concat(1 collate `binary`));
|
|
coercibility(concat(1 collate `binary`))
|
|
0
|
|
select collation(concat(NULL,NULL));
|
|
collation(concat(NULL,NULL))
|
|
binary
|
|
select coercibility(concat(NULL,NULL));
|
|
coercibility(concat(NULL,NULL))
|
|
6
|
|
select collation(concat(1,1));
|
|
collation(concat(1,1))
|
|
utf8mb4_general_ci
|
|
select coercibility(concat(1,1));
|
|
coercibility(concat(1,1))
|
|
4
|
|
select collation(1);
|
|
collation(1)
|
|
binary
|
|
select coercibility(1);
|
|
coercibility(1)
|
|
5
|
|
select coercibility(1=1);
|
|
coercibility(1=1)
|
|
5
|
|
drop table if exists t;
|
|
create table t(a datetime);
|
|
insert into t values ('2020-02-02');
|
|
select collation(concat(unix_timestamp(a))) from t;
|
|
collation(concat(unix_timestamp(a)))
|
|
utf8mb4_general_ci
|
|
select coercibility(concat(unix_timestamp(a))) from t;
|
|
coercibility(concat(unix_timestamp(a)))
|
|
4
|
|
set names default;
|
|
drop table if exists t;
|
|
create table t(a char, b char) collate utf8mb4_general_ci;
|
|
insert into t values('a', 'b');
|
|
insert into t values('a', 'B');
|
|
select * from t where if(a='x', a, b) = 'b';
|
|
a b
|
|
a b
|
|
a B
|
|
select collation(if(a='x', a, b)) from t;
|
|
collation(if(a='x', a, b))
|
|
utf8mb4_general_ci
|
|
utf8mb4_general_ci
|
|
select coercibility(if(a='x', a, b)) from t;
|
|
coercibility(if(a='x', a, b))
|
|
2
|
|
2
|
|
select collation(lag(b, 1, 'B') over w) from t window w as (order by b);
|
|
collation(lag(b, 1, 'B') over w)
|
|
utf8mb4_general_ci
|
|
utf8mb4_general_ci
|
|
select coercibility(lag(b, 1, 'B') over w) from t window w as (order by b);
|
|
coercibility(lag(b, 1, 'B') over w)
|
|
2
|
|
2
|
|
SELECT 'lvuleck' BETWEEN '2008-09-16 22:23:50' AND 0;
|
|
'lvuleck' BETWEEN '2008-09-16 22:23:50' AND 0
|
|
0
|
|
Level Code Message
|
|
Warning 1292 Truncated incorrect DOUBLE value: '2008-09-16 22:23:50'
|
|
Warning 1292 Truncated incorrect DOUBLE value: 'lvuleck'
|
|
SELECT 'aa' BETWEEN 'bb' AND 0;
|
|
'aa' BETWEEN 'bb' AND 0
|
|
1
|
|
Level Code Message
|
|
Warning 1292 Truncated incorrect DOUBLE value: 'aa'
|
|
Warning 1292 Truncated incorrect DOUBLE value: 'bb'
|
|
select 1 between 0 and b'110';
|
|
1 between 0 and b'110'
|
|
1
|
|
select 'b' between 'a' and b'110';
|
|
'b' between 'a' and b'110'
|
|
0
|
|
drop table if exists t;
|
|
create table t(a set('a', 'b', 'c'));
|
|
alter table t change a a set('a', 'b', 'c', 'c');
|
|
Error 1291 (HY000): Column 'a' has duplicated value 'c' in SET
|
|
drop table if exists t;
|
|
create table t(a enum('a', 'b', 'c'));
|
|
alter table t change a a enum('a', 'b', 'c', 'c');
|
|
Error 1291 (HY000): Column 'a' has duplicated value 'c' in ENUM
|
|
drop table if exists t;
|
|
create table t(a set('a', 'b', 'c'));
|
|
alter table t change a a set('a', 'b', 'c', 'd');
|
|
insert into t values('d');
|
|
alter table t change a a set('a', 'b', 'c', 'e', 'f');
|
|
Error 1265 (01000): Data truncated for column 'a', value is 'd'
|
|
set @@character_set_client=utf8mb4;
|
|
set @@collation_connection=utf8_bin;
|
|
CREATE VIEW tview_1 AS SELECT 'a' AS `id`;
|
|
set @@character_set_client=default;
|
|
set @@collation_connection=default;
|
|
drop table if exists t;
|
|
create table t(a enum('a ', 'b\t', ' c '), b set('a ', 'b\t', ' c '));
|
|
show create table t;
|
|
Table Create Table
|
|
t CREATE TABLE `t` (
|
|
`a` enum('a','b ',' c') DEFAULT NULL,
|
|
`b` set('a','b ',' c') DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
|
|
alter table t change a aa enum('a ', 'b\t', ' c ');
|
|
show create table t;
|
|
Table Create Table
|
|
t CREATE TABLE `t` (
|
|
`aa` enum('a','b ',' c') DEFAULT NULL,
|
|
`b` set('a','b ',' c') DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
|
|
select '䇇Հ' collate utf8mb4_bin like '___Հ';
|
|
'䇇Հ' collate utf8mb4_bin like '___Հ'
|
|
0
|
|
drop table if exists t;
|
|
create table t(raw JSON);
|
|
insert into t(raw) values('["a","ab"]'), ('["a"]'), (null);
|
|
SELECT JSON_SEARCH(raw,'one','c') FROM t;
|
|
JSON_SEARCH(raw,'one','c')
|
|
NULL
|
|
NULL
|
|
NULL
|
|
drop table if exists t;
|
|
CREATE TABLE `t` ( `a` char(10) COLLATE utf8mb4_unicode_ci NOT NULL, `b` char(20) COLLATE utf8mb4_general_ci NOT NULL, `c` int(11) NOT NULL, PRIMARY KEY (`a`,`b`,`c`), KEY `idx` (`a`));
|
|
insert into t values ('#', 'C', 10), ('$', 'c', 20), ('$', 'c', 30), ('a', 'a', 10), ('A', 'A', 30);
|
|
analyze table t;
|
|
select * from t where a='#';
|
|
a b c
|
|
# C 10
|
|
CREATE TABLE `tbl_5` ( `col_25` time NOT NULL DEFAULT '05:35:58', `col_26` blob NOT NULL, `col_27` double NOT NULL, `col_28` char(83) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `col_29` timestamp NOT NULL, `col_30` varchar(36) COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'ywzIn', `col_31` binary(85) DEFAULT 'OIstcXsGmAyc', `col_32` datetime NOT NULL DEFAULT '2024-08-02 00:00:00', PRIMARY KEY (`col_26`(3),`col_27`) /*T![clustered_index] CLUSTERED */, UNIQUE KEY `idx_10` (`col_26`(5)));
|
|
insert ignore into tbl_5 set col_28 = 'ZmZIdSnq' , col_25 = '18:50:52.00' on duplicate key update col_26 = 'y';
|
|
drop table if exists t1;
|
|
create table t1(a varchar(36) NOT NULL) ENGINE = InnoDB DEFAULT CHARSET = utf8 COLLATE = utf8_general_ci;
|
|
set names utf8;
|
|
select t2.b from (select t1.a as b from t1 union all select t1.a as b from t1) t2 where case when (t2.b is not null) then t2.b else '' end > '1234567';
|
|
b
|
|
set names default;
|
|
select case 1 when 1 then 'a' collate utf8mb4_unicode_ci else 'b' collate utf8mb4_general_ci end;
|
|
Error 1267 (HY000): Illegal mix of collations (utf8mb4_unicode_ci,EXPLICIT) and (utf8mb4_general_ci,EXPLICIT) for operation 'case'
|
|
select case when 1 then 'a' collate utf8mb4_unicode_ci when 2 then 'b' collate utf8mb4_general_ci end;
|
|
Error 1267 (HY000): Illegal mix of collations (utf8mb4_unicode_ci,EXPLICIT) and (utf8mb4_general_ci,EXPLICIT) for operation 'case'
|
|
select case 1 when 1 then 'a' collate utf8mb4_unicode_ci when 2 then 'b' collate utf8mb4_general_ci else 'b' collate utf8mb4_bin end;
|
|
Error 1270 (HY000): Illegal mix of collations (utf8mb4_unicode_ci,EXPLICIT), (utf8mb4_general_ci,EXPLICIT), (utf8mb4_bin,EXPLICIT) for operation 'case'
|
|
drop table if exists t0,t1;
|
|
create table t0(col1 varchar(255));
|
|
create table t1(col1 int(11));
|
|
insert into t0 values("\\9jM\\M");
|
|
insert into t1 values(0);
|
|
insert into t1 values(null);
|
|
insert into t1 values(null);
|
|
select t0.col1, t1.col1 from t0 left join t1 on t0.col1 not like t0.col1;
|
|
col1 col1
|
|
\9jM\M NULL
|
|
\9jM\M NULL
|
|
\9jM\M 0
|
|
select 'a' like '\\a';
|
|
'a' like '\\a'
|
|
1
|
|
select 'a' like '+a' escape '+';
|
|
'a' like '+a' escape '+'
|
|
1
|
|
drop table if exists table_int;
|
|
CREATE TABLE table_int (
|
|
id_0 int(16) NOT NULL AUTO_INCREMENT,
|
|
col_int_0 int(16) DEFAULT NULL,
|
|
PRIMARY KEY (id_0),
|
|
KEY fvclc (id_0,col_int_0));
|
|
INSERT INTO table_int VALUES (1,NULL),(2,NULL),(3,65535),(4,1),(5,0),(6,NULL),(7,-1),(8,65535),(9,NULL),(10,65535),(11,-1),(12,0),(13,-1),(14,1),(15,65535),(16,0),(17,1),(18,0),(19,0);
|
|
drop table if exists table_varchar;
|
|
CREATE TABLE table_varchar (
|
|
id_2 int(16) NOT NULL AUTO_INCREMENT,
|
|
col_varchar_2 varchar(511) DEFAULT NULL,
|
|
PRIMARY KEY (id_2));
|
|
INSERT INTO table_varchar VALUES (1,''),(2,''),(3,''),(4,''),(5,''),(6,''),(7,''),(8,''),(9,''),(10,''),(11,''),(12,'');
|
|
drop table if exists table_int_float_varchar;
|
|
CREATE TABLE table_int_float_varchar (
|
|
id_6 int(16) NOT NULL AUTO_INCREMENT,
|
|
col_int_6 int(16) NOT NULL,
|
|
col_float_6 float DEFAULT NULL,
|
|
col_varchar_6 varchar(511) DEFAULT NULL,
|
|
PRIMARY KEY (id_6,col_int_6)
|
|
)
|
|
PARTITION BY RANGE ( col_int_6 ) (
|
|
PARTITION p0 VALUES LESS THAN (1),
|
|
PARTITION p2 VALUES LESS THAN (1000),
|
|
PARTITION p3 VALUES LESS THAN (10000),
|
|
PARTITION p5 VALUES LESS THAN (1000000),
|
|
PARTITION p7 VALUES LESS THAN (100000000),
|
|
PARTITION p9 VALUES LESS THAN (10000000000),
|
|
PARTITION p10 VALUES LESS THAN (100000000000),
|
|
PARTITION pn VALUES LESS THAN (MAXVALUE));
|
|
INSERT INTO table_int_float_varchar VALUES (1,-1,0.1,'0000-00-00 00:00:00'),(2,0,0,NULL),(3,-1,1,NULL),(4,0,NULL,NULL),(7,0,0.5,NULL),(8,0,0,NULL),(10,-1,0,'-1'),(5,1,-0.1,NULL),(6,1,0.1,NULL),(9,65535,0,'1');
|
|
drop table if exists table_float;
|
|
CREATE TABLE table_float (
|
|
id_1 int(16) NOT NULL AUTO_INCREMENT,
|
|
col_float_1 float DEFAULT NULL,
|
|
PRIMARY KEY (id_1),
|
|
KEY zbjus (id_1,col_float_1));
|
|
INSERT INTO table_float VALUES (1,NULL),(2,-0.1),(3,-1),(4,NULL),(5,-0.1),(6,0),(7,0),(8,-1),(9,NULL),(10,NULL),(11,0.1),(12,-1);
|
|
drop view if exists view_4;
|
|
CREATE DEFINER='root'@'127.0.0.1' VIEW view_4 (col_1, col_2, col_3, col_4, col_5, col_6, col_7, col_8, col_9, col_10) AS
|
|
SELECT /*+ USE_INDEX(table_int fvclc, fvclc)*/
|
|
tmp1.id_6 AS col_1,
|
|
tmp1.col_int_6 AS col_2,
|
|
tmp1.col_float_6 AS col_3,
|
|
tmp1.col_varchar_6 AS col_4,
|
|
tmp2.id_2 AS col_5,
|
|
tmp2.col_varchar_2 AS col_6,
|
|
tmp3.id_0 AS col_7,
|
|
tmp3.col_int_0 AS col_8,
|
|
tmp4.id_1 AS col_9,
|
|
tmp4.col_float_1 AS col_10
|
|
FROM ((
|
|
expression__issues.table_int_float_varchar AS tmp1 LEFT JOIN
|
|
expression__issues.table_varchar AS tmp2 ON ((NULL<=tmp2.col_varchar_2)) IS NULL
|
|
) JOIN
|
|
expression__issues.table_int AS tmp3 ON (1.117853833115198e-03!=tmp1.col_int_6))
|
|
JOIN
|
|
expression__issues.table_float AS tmp4 ON !((1900370398268920328=0e+00)) WHERE ((''<='{Gm~PcZNb') OR (tmp2.id_2 OR tmp3.col_int_0)) ORDER BY col_1,col_2,col_3,col_4,col_5,col_6,col_7,col_8,col_9,col_10 LIMIT 20580,5;
|
|
drop view if exists view_10;
|
|
CREATE DEFINER='root'@'127.0.0.1' VIEW view_10 (col_1, col_2) AS
|
|
SELECT table_int.id_0 AS col_1,
|
|
table_int.col_int_0 AS col_2
|
|
FROM expression__issues.table_int
|
|
WHERE
|
|
((-1e+00=1) OR (0e+00>=table_int.col_int_0))
|
|
ORDER BY col_1,col_2
|
|
LIMIT 5,9;
|
|
SELECT col_1 FROM expression__issues.view_10;
|
|
col_1
|
|
16
|
|
18
|
|
19
|
|
SELECT col_1 FROM expression__issues.view_4;
|
|
col_1
|
|
8
|
|
8
|
|
8
|
|
8
|
|
8
|
|
SELECT view_10.col_1 FROM view_4 JOIN view_10;
|
|
col_1
|
|
16
|
|
16
|
|
16
|
|
16
|
|
16
|
|
18
|
|
18
|
|
18
|
|
18
|
|
18
|
|
19
|
|
19
|
|
19
|
|
19
|
|
19
|
|
drop table if exists t;
|
|
create table t(a int, b tinyint as(a+1), c int as(b+1));
|
|
set sql_mode='';
|
|
insert into t(a) values(2000);
|
|
create index idx on t(c);
|
|
select c from t;
|
|
c
|
|
128
|
|
admin check table t;
|
|
set @@sql_mode=default;
|
|
DROP TABLE IF EXISTS t1;
|
|
CREATE TABLE t1 ( `pk` int not null primary key auto_increment, `col_smallint_key_signed` smallint , key (`col_smallint_key_signed`));
|
|
INSERT INTO `t1` VALUES (1,0),(2,NULL),(3,NULL),(4,0),(5,0),(6,NULL),(7,NULL),(8,0),(9,0),(10,0);
|
|
SELECT * FROM t1 WHERE ( LOG( `col_smallint_key_signed`, -8297584758403770424 ) ) DIV 1;
|
|
pk col_smallint_key_signed
|
|
drop table if exists t, t1, t2;
|
|
CREATE TABLE t (
|
|
id int(11) NOT NULL AUTO_INCREMENT,
|
|
a char(10) DEFAULT NULL,
|
|
PRIMARY KEY (id)
|
|
);
|
|
CREATE TABLE t1 (
|
|
id int(11) NOT NULL AUTO_INCREMENT,
|
|
a char(10) DEFAULT NULL,
|
|
b char(10) DEFAULT NULL,
|
|
c char(10) DEFAULT NULL,
|
|
PRIMARY KEY (id)
|
|
);
|
|
CREATE TABLE t2 (
|
|
id int(11) NOT NULL AUTO_INCREMENT,
|
|
a char(10) DEFAULT NULL,
|
|
b char(10) DEFAULT NULL,
|
|
PRIMARY KEY (id),
|
|
UNIQUE KEY b (b)
|
|
);
|
|
insert into t1(a,b,c) values('hs4_0004', "04", "101"), ('a01', "01", "101"),('a011', "02", "101");
|
|
insert into t2(a,b) values("02","03");
|
|
insert into t(a) values('101'),('101');
|
|
select ( SELECT t1.a FROM t1, t2 WHERE t1.b = t2.a AND t2.b = '03' AND t1.c = a.a) invode from t a ;
|
|
invode
|
|
a011
|
|
a011
|
|
drop table if exists t, t1;
|
|
CREATE TABLE `t` (`a` bit(10) DEFAULT NULL,`b` int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
|
|
INSERT INTO `t` VALUES (_binary '\0',1),(_binary '\0',2),(_binary '\0',5),(_binary '\0',4),(_binary '\0',2),(_binary '\0 ',4);
|
|
CREATE TABLE `t1` (`a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
|
|
INSERT INTO `t1` VALUES (1,1),(1,5),(2,3),(2,4),(3,3);
|
|
select * from t where t.b > (select min(t1.b) from t1 where t1.a > t.a);
|
|
a b
|
|
2
|
|
5
|
|
4
|
|
2
|
|
drop table if exists t1;
|
|
create table t1(a date);
|
|
insert into t1 values (20100202);
|
|
select a in ('2020-02-02', 20100202) from t1;
|
|
a in ('2020-02-02', 20100202)
|
|
1
|
|
drop table if exists t;
|
|
create table t(b enum('a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z') DEFAULT NULL, c decimal(40,20));
|
|
insert into t values('z', 19.18040000000000000000);
|
|
insert into t values('z', 26.18040000000000000000);
|
|
insert into t values('z', 25.18040000000000000000);
|
|
select * from t where t.b > t.c;
|
|
b c
|
|
z 19.18040000000000000000
|
|
z 25.18040000000000000000
|
|
select * from t where t.b < t.c;
|
|
b c
|
|
z 26.18040000000000000000
|
|
drop table if exists t1;
|
|
CREATE TABLE t1 (c1 INT);
|
|
INSERT INTO t1 VALUES (1), (null);
|
|
SELECT IFNULL(c1, 0.0) from t1;
|
|
IFNULL(c1, 0.0)
|
|
1.0
|
|
0.0
|
|
SELECT if(c1 is not null, c1, 0.0) from t1;
|
|
if(c1 is not null, c1, 0.0)
|
|
1.0
|
|
0.0
|
|
SELECT case when c1 is not null then c1 else 0.0 end from t1;
|
|
case when c1 is not null then c1 else 0.0 end
|
|
1.0
|
|
0.0
|
|
drop table if exists t1;
|
|
CREATE TABLE t1 (v1 datetime(3), v2 datetime(4));
|
|
INSERT INTO t1 VALUES ('2020-01-01 11:11:11.123', null), (null, '2020-01-01 11:11:11.1234');
|
|
SELECT IFNULL(v1, v2) from t1;
|
|
IFNULL(v1, v2)
|
|
2020-01-01 11:11:11.1230
|
|
2020-01-01 11:11:11.1234
|
|
SELECT if(v1 is not null, v1, v2) from t1;
|
|
if(v1 is not null, v1, v2)
|
|
2020-01-01 11:11:11.1230
|
|
2020-01-01 11:11:11.1234
|
|
SELECT case when v1 is not null then v1 else v2 end from t1;
|
|
case when v1 is not null then v1 else v2 end
|
|
2020-01-01 11:11:11.1230
|
|
2020-01-01 11:11:11.1234
|
|
drop table if exists t1;
|
|
CREATE TABLE t1 (v1 timestamp(3), v2 timestamp(4));
|
|
INSERT INTO t1 VALUES ('2020-01-01 11:11:11.123', null), (null, '2020-01-01 11:11:11.1234');
|
|
SELECT IFNULL(v1, v2) from t1;
|
|
IFNULL(v1, v2)
|
|
2020-01-01 11:11:11.1230
|
|
2020-01-01 11:11:11.1234
|
|
SELECT if(v1 is not null, v1, v2) from t1;
|
|
if(v1 is not null, v1, v2)
|
|
2020-01-01 11:11:11.1230
|
|
2020-01-01 11:11:11.1234
|
|
SELECT case when v1 is not null then v1 else v2 end from t1;
|
|
case when v1 is not null then v1 else v2 end
|
|
2020-01-01 11:11:11.1230
|
|
2020-01-01 11:11:11.1234
|
|
drop table if exists t1;
|
|
CREATE TABLE t1 (v1 time(3), v2 time(4));
|
|
INSERT INTO t1 VALUES ('11:11:11.123', null), (null, '11:11:11.1234');
|
|
SELECT IFNULL(v1, v2) from t1;
|
|
IFNULL(v1, v2)
|
|
11:11:11.1230
|
|
11:11:11.1234
|
|
SELECT if(v1 is not null, v1, v2) from t1;
|
|
if(v1 is not null, v1, v2)
|
|
11:11:11.1230
|
|
11:11:11.1234
|
|
SELECT case when v1 is not null then v1 else v2 end from t1;
|
|
case when v1 is not null then v1 else v2 end
|
|
11:11:11.1230
|
|
11:11:11.1234
|
|
drop table if exists t1;
|
|
CREATE TABLE t1 (var_fld int(11) DEFAULT '0' );
|
|
INSERT INTO t1 VALUES (4500), (6000);
|
|
SELECT IF ( var_fld > 5000, ( 00001 / var_fld ) , 5000 ) if_var FROM t1;
|
|
if_var
|
|
5000.0000
|
|
0.0002
|
|
SELECT CASE WHEN var_fld > 5000 THEN ( 1 / var_fld ) ELSE 5000 END case_var FROM t1;
|
|
case_var
|
|
5000.0000
|
|
0.0002
|
|
SELECT CASE var_fld > 5000 WHEN TRUE THEN ( 1 / var_fld ) ELSE 5000 END case_var FROM t1;
|
|
case_var
|
|
5000.0000
|
|
0.0002
|
|
drop table if exists t1;
|
|
CREATE TABLE t1 (c1 TINYINT(1) UNSIGNED NOT NULL);
|
|
INSERT INTO t1 VALUES (0);
|
|
SELECT c1>=CAST('-787360724' AS TIME) FROM t1;
|
|
c1>=CAST('-787360724' AS TIME)
|
|
1
|
|
select '123' union select cast(45678 as char);
|
|
123
|
|
123
|
|
45678
|
|
select '123' union select cast(45678 as char(2));
|
|
123
|
|
123
|
|
45
|
|
drop table if exists t;
|
|
create table t(a int);
|
|
insert into t values(45678);
|
|
select '123' union select cast(a as char) from t;
|
|
123
|
|
123
|
|
45678
|
|
select '123' union select cast(a as char(2)) from t;
|
|
123
|
|
123
|
|
45
|
|
SET time_zone='Europe/Vilnius';
|
|
SELECT UNIX_TIMESTAMP('2020-03-29 03:45:00');
|
|
UNIX_TIMESTAMP('2020-03-29 03:45:00')
|
|
1585443600
|
|
SELECT FROM_UNIXTIME(UNIX_TIMESTAMP('2020-03-29 03:45:00'));
|
|
FROM_UNIXTIME(UNIX_TIMESTAMP('2020-03-29 03:45:00'))
|
|
2020-03-29 04:00:00
|
|
DROP TABLE IF EXISTS t;
|
|
CREATE TABLE t (dt DATETIME NULL);
|
|
INSERT INTO t VALUES ('2021-10-31 02:30:00'), ('2021-03-28 02:30:00'), ('2020-10-04 02:15:00'), ('2020-03-29 03:45:00'), (NULL);
|
|
SELECT dt, UNIX_TIMESTAMP(dt) FROM t;
|
|
dt UNIX_TIMESTAMP(dt)
|
|
NULL NULL
|
|
2020-03-29 03:45:00 1585443600
|
|
2020-10-04 02:15:00 1601766900
|
|
2021-03-28 02:30:00 1616891400
|
|
2021-10-31 02:30:00 1635636600
|
|
SET time_zone=default;
|
|
DROP TABLE IF EXISTS test;
|
|
CREATE TABLE test ( c1 varchar(20));
|
|
INSERT INTO test VALUES (101111),(11100),(101111),(101111);
|
|
set tidb_enable_vectorized_expression = true;
|
|
SELECT DATE_ADD(c1, INTERVAL 1 DAY_HOUR) from test;
|
|
DATE_ADD(c1, INTERVAL 1 DAY_HOUR)
|
|
NULL
|
|
2010-11-11 01:00:00
|
|
2010-11-11 01:00:00
|
|
2010-11-11 01:00:00
|
|
set tidb_enable_vectorized_expression = default;
|
|
drop table if exists t;
|
|
create table t(a timestamp) partition by range(unix_timestamp(a)) (partition p0 values less than(unix_timestamp('2019-02-16 14:20:00')), partition p1 values less than (maxvalue));
|
|
insert into t values('2019-02-16 14:19:59'), ('2019-02-16 14:20:01');
|
|
select * from t where a between timestamp'2019-02-16 14:19:00' and timestamp'2019-02-16 14:21:00';
|
|
a
|
|
2019-02-16 14:19:59
|
|
2019-02-16 14:20:01
|
|
drop table if exists t;
|
|
create table t(a timestamp);
|
|
insert into t values('2019-02-16 14:19:59'), ('2019-02-16 14:20:01');
|
|
select * from t where a < timestamp'2019-02-16 14:21:00';
|
|
a
|
|
2019-02-16 14:19:59
|
|
2019-02-16 14:20:01
|
|
drop table if exists t;
|
|
CREATE TABLE t(c varchar(100), index idx(c(100)));
|
|
INSERT INTO t VALUES (NULL),('1'),('0'),(''),('aaabbb'),('0abc'),('123e456'),('0.0001deadsfeww');
|
|
select * from t where c;
|
|
c
|
|
0.0001deadsfeww
|
|
1
|
|
123e456
|
|
select /*+ USE_INDEX(t, idx) */ * from t where c;
|
|
c
|
|
0.0001deadsfeww
|
|
1
|
|
123e456
|
|
select /*+ IGNORE_INDEX(t, idx) */* from t where c;
|
|
c
|
|
0.0001deadsfeww
|
|
1
|
|
123e456
|
|
drop table if exists t0;
|
|
create table t0 (c1 DATE, c2 TIME, c3 DATETIME, c4 TIMESTAMP);
|
|
insert into t0 values ('1000-01-01', '-838:59:59', '1000-01-01 00:00:00', '1970-01-01 08:00:01');
|
|
insert into t0 values ('9999-12-31', '838:59:59', '9999-12-31 23:59:59', '2038-01-19 11:14:07');
|
|
select avg(c1), avg(c2), avg(c3), avg(c4) from t0;
|
|
avg(c1) avg(c2) avg(c3) avg(c4)
|
|
54995666.0000 0.0000 54995666117979.5000 20040110095704.0000
|
|
select -1.0 % -1.0;
|
|
-1.0 % -1.0
|
|
0.0
|
|
drop table if exists t1;
|
|
create table t1(`pk` int primary key,`col_float_key_signed` float ,key (`col_float_key_signed`));
|
|
insert into t1 values (0, null), (1, 0), (2, -0), (3, 1), (-1,-1);
|
|
select * from t1 where ( `col_float_key_signed` % `col_float_key_signed`) IS FALSE;
|
|
pk col_float_key_signed
|
|
-1 -1
|
|
3 1
|
|
select `col_float_key_signed` , `col_float_key_signed` % `col_float_key_signed` from t1;
|
|
col_float_key_signed `col_float_key_signed` % `col_float_key_signed`
|
|
NULL NULL
|
|
-1 -0
|
|
0 NULL
|
|
0 NULL
|
|
1 0
|
|
select `col_float_key_signed` , (`col_float_key_signed` % `col_float_key_signed`) IS FALSE from t1;
|
|
col_float_key_signed (`col_float_key_signed` % `col_float_key_signed`) IS FALSE
|
|
NULL 0
|
|
-1 1
|
|
0 0
|
|
0 0
|
|
1 1
|
|
drop table if exists author_addresses, authors;
|
|
CREATE TABLE author_addresses (
|
|
id bigint(20) NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
CREATE TABLE authors (
|
|
id bigint(20) NOT NULL AUTO_INCREMENT,
|
|
name varchar(255) NOT NULL,
|
|
author_address_id bigint(20) DEFAULT NULL,
|
|
author_address_extra_id bigint(20) DEFAULT NULL,
|
|
organization_id varchar(255) DEFAULT NULL,
|
|
owned_essay_id varchar(255) DEFAULT NULL,
|
|
PRIMARY KEY (id),
|
|
KEY index_authors_on_author_address_id (author_address_id),
|
|
KEY index_authors_on_author_address_extra_id (author_address_extra_id),
|
|
CONSTRAINT fk_rails_94423a17a3 FOREIGN KEY (author_address_id) REFERENCES author_addresses (id) ON UPDATE CASCADE ON DELETE RESTRICT
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
SELECT fk.referenced_table_name AS 'to_table',
|
|
fk.referenced_column_name AS 'primary_key',
|
|
fk.column_name AS 'column',
|
|
fk.constraint_name AS 'name',
|
|
rc.update_rule AS 'on_update',
|
|
rc.delete_rule AS 'on_delete'
|
|
FROM information_schema.referential_constraints rc
|
|
JOIN information_schema.key_column_usage fk
|
|
USING (constraint_schema, constraint_name)
|
|
WHERE fk.referenced_column_name IS NOT NULL
|
|
AND fk.table_schema = database()
|
|
AND fk.table_name = 'authors';
|
|
to_table primary_key column name on_update on_delete
|
|
author_addresses id author_address_id fk_rails_94423a17a3 CASCADE RESTRICT
|
|
drop table if exists t;
|
|
CREATE TABLE t (a CHAR(5) CHARACTER SET latin1);
|
|
INSERT INTO t VALUES ('oe');
|
|
INSERT INTO t VALUES (0xf6);
|
|
SELECT * FROM t WHERE a= 'oe';
|
|
a
|
|
oe
|
|
SELECT HEX(a) FROM t WHERE a= 0xf6;
|
|
HEX(a)
|
|
F6
|
|
drop table if exists tb;
|
|
create table tb(id int auto_increment primary key, v varchar(32));
|
|
insert into tb(v) (select v from tb);
|
|
SELECT * FROM tb;
|
|
id v
|
|
insert into tb(v) values('hello');
|
|
insert into tb(v) (select v from tb);
|
|
SELECT * FROM tb;
|
|
id v
|
|
1 hello
|
|
2 hello
|
|
drop table if exists t;
|
|
create table t(c varchar(32));
|
|
insert into t values('1e649'),('-1e649');
|
|
SELECT * FROM t where c < 1;
|
|
c
|
|
-1e649
|
|
SELECT * FROM t where c > 1;
|
|
c
|
|
1e649
|
|
drop table if exists t;
|
|
create table t(a int);
|
|
insert t values (1);
|
|
select * from t where cast(a as binary);
|
|
a
|
|
1
|
|
drop table if exists t2;
|
|
create table t2(a int, b varchar(20));
|
|
insert into t2 values(1,"1111"),(2,"2222"),(3,"3333"),(4,"4444"),(5,"5555"),(6,"6666"),(7,"7777"),(8,"8888"),(9,"9999"),(10,"0000");
|
|
select (@j := case when substr(t2.b,1,3)=@i then 1 else @j+1 end) from t2, (select @j := 0, @i := "0") tt limit 10;
|
|
(@j := case when substr(t2.b,1,3)=@i then 1 else @j+1 end)
|
|
1
|
|
2
|
|
3
|
|
4
|
|
5
|
|
6
|
|
7
|
|
8
|
|
9
|
|
10
|
|
select b'10000000' DIV 10;
|
|
b'10000000' DIV 10
|
|
12
|
|
select cast(b'10000000' as unsigned) / 10;
|
|
cast(b'10000000' as unsigned) / 10
|
|
12.8000
|
|
select b'10000000' / 10;
|
|
b'10000000' / 10
|
|
12.8000
|
|
drop table if exists t0;
|
|
create table t0 (c0 int);
|
|
select * from (select (92 / 4) as c4) as subq_0 where exists (
|
|
select 1 as c0
|
|
union all
|
|
select 1 as c0 from (t0 as ref_88) where (subq_0.c4) >= (subq_0.c4)
|
|
);
|
|
c4
|
|
23.0000
|
|
drop table if exists poi_clearing_time_topic;
|
|
CREATE TABLE poi_clearing_time_topic (effective_date datetime DEFAULT NULL , clearing_time int(11) DEFAULT NULL);
|
|
insert into poi_clearing_time_topic values ('2023:08:25', 1);
|
|
SELECT GROUP_CONCAT(effective_date order by stlmnt_hour DESC) FROM ( SELECT (COALESCE(pct.clearing_time, 0)/3600000) AS stlmnt_hour ,COALESCE(pct.effective_date, '1970-01-01 08:00:00') AS effective_date FROM poi_clearing_time_topic pct ORDER BY pct.effective_date DESC ) a;
|
|
GROUP_CONCAT(effective_date order by stlmnt_hour DESC)
|
|
2023-08-25 00:00:00
|
|
SELECT @@information_schema_stats_expiry;
|
|
@@information_schema_stats_expiry
|
|
86400
|
|
/*!80000 SET SESSION information_schema_stats_expiry=0 */;
|
|
SELECT @@information_schema_stats_expiry;
|
|
@@information_schema_stats_expiry
|
|
0
|
|
SELECT @@GLOBAL.information_schema_stats_expiry;
|
|
@@GLOBAL.information_schema_stats_expiry
|
|
86400
|
|
/*!80000 SET GLOBAL information_schema_stats_expiry=0 */;
|
|
SELECT @@GLOBAL.information_schema_stats_expiry;
|
|
@@GLOBAL.information_schema_stats_expiry
|
|
0
|
|
set @@SESSION.information_schema_stats_expiry=default;
|
|
set @@GLOBAL.information_schema_stats_expiry=default;
|
|
drop table if exists t;
|
|
drop table if exists t1;
|
|
create table t(col1 decimal);
|
|
insert into t values(0.00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000);
|
|
select * from t;
|
|
col1
|
|
0
|
|
create table t1(col1 decimal(65,30));
|
|
insert into t1 values(0.00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000);
|
|
select * from t1;
|
|
col1
|
|
0.000000000000000000000000000000
|
|
select 0.00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000;
|
|
0.00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
|
|
0.000000000000000000000000000000000000000000000000000000000000000000000000
|
|
select 0.0000000000000000000000000000000000000000000000000000000000000000000000012;
|
|
0.0000000000000000000000000000000000000000000000000000000000000000000000012
|
|
0.000000000000000000000000000000000000000000000000000000000000000000000001
|
|
select 0.000000000000000000000000000000000000000000000000000000000000000000000001;
|
|
0.000000000000000000000000000000000000000000000000000000000000000000000001
|
|
0.000000000000000000000000000000000000000000000000000000000000000000000001
|
|
drop table if exists dd;
|
|
CREATE TABLE dd(a date, b datetime, c timestamp);
|
|
SET sql_mode='';
|
|
TRUNCATE TABLE dd;
|
|
INSERT INTO dd(a) values('0000-00-00');
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
SELECT a FROM dd;
|
|
a
|
|
0000-00-00
|
|
TRUNCATE TABLE dd;
|
|
INSERT INTO dd(b) values('2000-10-01');
|
|
UPDATE dd SET b = '0000-00-00';
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
SELECT b FROM dd;
|
|
b
|
|
0000-00-00 00:00:00
|
|
TRUNCATE TABLE dd;
|
|
INSERT INTO dd(c) values('0000-00-00 20:00:00');
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Warning 1292 Incorrect timestamp value: '0000-00-00 20:00:00' for column 'c' at row 1
|
|
SELECT c FROM dd;
|
|
c
|
|
0000-00-00 00:00:00
|
|
TRUNCATE TABLE dd;
|
|
INSERT INTO dd(c) values('2000-10-01 20:00:00');
|
|
UPDATE dd SET c = '0000-00-00 20:00:00';
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Warning 1292 Incorrect timestamp value: '0000-00-00 20:00:00'
|
|
SELECT c FROM dd;
|
|
c
|
|
0000-00-00 00:00:00
|
|
SET sql_mode='NO_ZERO_DATE';
|
|
TRUNCATE TABLE dd;
|
|
INSERT INTO dd(b) values('0000-0-00');
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Warning 1292 Incorrect datetime value: '0000-0-00' for column 'b' at row 1
|
|
SELECT b FROM dd;
|
|
b
|
|
0000-00-00 00:00:00
|
|
TRUNCATE TABLE dd;
|
|
INSERT INTO dd(a) values('2000-10-01');
|
|
UPDATE dd SET a = '0000-00-00';
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Warning 1292 Incorrect date value: '0000-00-00'
|
|
SELECT a FROM dd;
|
|
a
|
|
0000-00-00
|
|
TRUNCATE TABLE dd;
|
|
INSERT INTO dd(c) values('2000-10-01 10:00:00');
|
|
UPDATE dd SET c = '0000-00-00 10:00:00';
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Warning 1292 Incorrect timestamp value: '0000-00-00 10:00:00'
|
|
SELECT c FROM dd;
|
|
c
|
|
0000-00-00 00:00:00
|
|
SET sql_mode='NO_ZERO_DATE,STRICT_TRANS_TABLES';
|
|
TRUNCATE TABLE dd;
|
|
INSERT INTO dd(c) VALUES ('0000-00-00 20:00:00');
|
|
Error 1292 (22007): Incorrect timestamp value: '0000-00-00 20:00:00' for column 'c' at row 1
|
|
INSERT IGNORE INTO dd(c) VALUES ('0000-00-00 20:00:00');
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Warning 1292 Incorrect timestamp value: '0000-00-00 20:00:00' for column 'c' at row 1
|
|
SELECT c FROM dd;
|
|
c
|
|
0000-00-00 00:00:00
|
|
TRUNCATE TABLE dd;
|
|
INSERT INTO dd(b) values('2000-10-01');
|
|
UPDATE dd SET b = '0000-00-00';
|
|
Error 1292 (22007): Incorrect datetime value: '0000-00-00'
|
|
UPDATE IGNORE dd SET b = '0000-00-00';
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Warning 1292 Incorrect datetime value: '0000-00-00'
|
|
SELECT b FROM dd;
|
|
b
|
|
0000-00-00 00:00:00
|
|
TRUNCATE TABLE dd;
|
|
INSERT INTO dd(c) values('2000-10-01 10:00:00');
|
|
UPDATE dd SET c = '0000-00-00 00:00:00';
|
|
Error 1292 (22007): Incorrect timestamp value: '0000-00-00 00:00:00'
|
|
UPDATE IGNORE dd SET c = '0000-00-00 00:00:00';
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Warning 1292 Incorrect timestamp value: '0000-00-00 00:00:00'
|
|
SELECT c FROM dd;
|
|
c
|
|
0000-00-00 00:00:00
|
|
SET sql_mode='';
|
|
TRUNCATE TABLE dd;
|
|
INSERT INTO dd(a) values('2000-01-00');
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
SELECT a FROM dd;
|
|
a
|
|
2000-01-00
|
|
INSERT INTO dd(a) values('2000-00-01');
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
SELECT a FROM dd;
|
|
a
|
|
2000-01-00
|
|
2000-00-01
|
|
INSERT INTO dd(a) values('0-01-02');
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
SELECT a FROM dd;
|
|
a
|
|
2000-01-00
|
|
2000-00-01
|
|
2000-01-02
|
|
TRUNCATE TABLE dd;
|
|
INSERT INTO dd(b) values('2000-01-02');
|
|
UPDATE dd SET b = '2000-00-02';
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
SELECT b FROM dd;
|
|
b
|
|
2000-00-02 00:00:00
|
|
TRUNCATE TABLE dd;
|
|
INSERT INTO dd(c) values('2000-01-02 20:00:00');
|
|
UPDATE dd SET c = '0000-01-02 20:00:00';
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Warning 1292 Incorrect timestamp value: '0000-01-02 20:00:00'
|
|
SELECT c FROM dd;
|
|
c
|
|
0000-00-00 00:00:00
|
|
SET sql_mode='NO_ZERO_IN_DATE';
|
|
TRUNCATE TABLE dd;
|
|
INSERT INTO dd(a) values('2000-01-00');
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Warning 1292 Incorrect date value: '2000-01-00' for column 'a' at row 1
|
|
SELECT a FROM dd;
|
|
a
|
|
0000-00-00
|
|
TRUNCATE TABLE dd;
|
|
INSERT INTO dd(a) values('2000-01-02');
|
|
UPDATE dd SET a = '2000-00-02';
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Warning 1292 Incorrect date value: '2000-00-02'
|
|
SELECT a FROM dd;
|
|
a
|
|
0000-00-00
|
|
UPDATE dd SET b = '2000-01-0';
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Warning 1292 Incorrect datetime value: '2000-01-0'
|
|
SELECT b FROM dd;
|
|
b
|
|
0000-00-00 00:00:00
|
|
UPDATE dd SET b = '0-01-02';
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
SELECT b FROM dd;
|
|
b
|
|
2000-01-02 00:00:00
|
|
TRUNCATE TABLE dd;
|
|
INSERT INTO dd(c) values('2000-01-02 20:00:00');
|
|
UPDATE dd SET c = '2000-00-02 20:00:00';
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Warning 1292 Incorrect timestamp value: '2000-00-02 20:00:00'
|
|
SELECT c FROM dd;
|
|
c
|
|
0000-00-00 00:00:00
|
|
SET sql_mode='NO_ZERO_IN_DATE,STRICT_TRANS_TABLES';
|
|
TRUNCATE TABLE dd;
|
|
INSERT INTO dd(b) VALUES ('2000-01-00');
|
|
Error 1292 (22007): Incorrect datetime value: '2000-01-00' for column 'b' at row 1
|
|
INSERT IGNORE INTO dd(b) VALUES ('2000-00-01');
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Warning 1292 Incorrect datetime value: '2000-00-01' for column 'b' at row 1
|
|
SELECT b FROM dd;
|
|
b
|
|
0000-00-00 00:00:00
|
|
TRUNCATE TABLE dd;
|
|
INSERT INTO dd(b) VALUES ('2000-01-02');
|
|
UPDATE dd SET b = '2000-01-00';
|
|
Error 1292 (22007): Incorrect datetime value: '2000-01-00'
|
|
UPDATE IGNORE dd SET b = '2000-01-0';
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Warning 1292 Incorrect datetime value: '2000-01-0'
|
|
SELECT b FROM dd;
|
|
b
|
|
0000-00-00 00:00:00
|
|
UPDATE dd SET b = '0000-1-2';
|
|
SELECT b FROM dd;
|
|
b
|
|
0000-01-02 00:00:00
|
|
UPDATE dd SET c = '0000-01-05';
|
|
Error 1292 (22007): Incorrect timestamp value: '0000-01-05'
|
|
UPDATE IGNORE dd SET c = '0000-01-5';
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Warning 1292 Incorrect timestamp value: '0000-01-5'
|
|
SELECT c FROM dd;
|
|
c
|
|
0000-00-00 00:00:00
|
|
TRUNCATE TABLE dd;
|
|
INSERT INTO dd(c) VALUES ('2000-01-00 20:00:00');
|
|
Error 1292 (22007): Incorrect timestamp value: '2000-01-00 20:00:00' for column 'c' at row 1
|
|
INSERT INTO dd(c) VALUES ('2000-01-02');
|
|
UPDATE dd SET c = '2000-01-00 20:00:00';
|
|
Error 1292 (22007): Incorrect timestamp value: '2000-01-00 20:00:00'
|
|
UPDATE IGNORE dd SET b = '2000-01-00';
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Warning 1292 Incorrect datetime value: '2000-01-00'
|
|
SELECT b FROM dd;
|
|
b
|
|
0000-00-00 00:00:00
|
|
SET sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
|
|
TRUNCATE TABLE dd;
|
|
INSERT INTO dd(a) values('0000-00-00');
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
SELECT a FROM dd;
|
|
a
|
|
0000-00-00
|
|
TRUNCATE TABLE dd;
|
|
INSERT INTO dd(b) values('2000-10-01');
|
|
UPDATE dd SET b = '0000-00-00';
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
SELECT b FROM dd;
|
|
b
|
|
0000-00-00 00:00:00
|
|
TRUNCATE TABLE dd;
|
|
INSERT INTO dd(c) values('0000-00-00 00:00:00');
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
TRUNCATE TABLE dd;
|
|
INSERT INTO dd(c) values('2000-10-01 10:00:00');
|
|
UPDATE dd SET c = '0000-00-00 00:00:00';
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
SELECT c FROM dd;
|
|
c
|
|
0000-00-00 00:00:00
|
|
TRUNCATE TABLE dd;
|
|
INSERT INTO dd(b) VALUES ('2000-01-00');
|
|
Error 1292 (22007): Incorrect datetime value: '2000-01-00' for column 'b' at row 1
|
|
INSERT IGNORE INTO dd(b) VALUES ('2000-00-01');
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Warning 1292 Incorrect datetime value: '2000-00-01' for column 'b' at row 1
|
|
SELECT b FROM dd;
|
|
b
|
|
0000-00-00 00:00:00
|
|
TRUNCATE TABLE dd;
|
|
INSERT INTO dd(b) VALUES ('2000-01-02');
|
|
UPDATE dd SET b = '2000-01-00';
|
|
Error 1292 (22007): Incorrect datetime value: '2000-01-00'
|
|
UPDATE IGNORE dd SET b = '2000-01-0';
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Warning 1292 Incorrect datetime value: '2000-01-0'
|
|
SELECT b FROM dd;
|
|
b
|
|
0000-00-00 00:00:00
|
|
UPDATE dd SET b = '0000-1-2';
|
|
SELECT b FROM dd;
|
|
b
|
|
0000-01-02 00:00:00
|
|
UPDATE dd SET c = '0000-01-05';
|
|
Error 1292 (22007): Incorrect timestamp value: '0000-01-05'
|
|
UPDATE IGNORE dd SET c = '0000-01-5';
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Warning 1292 Incorrect timestamp value: '0000-01-5'
|
|
SELECT c FROM dd;
|
|
c
|
|
0000-00-00 00:00:00
|
|
TRUNCATE TABLE dd;
|
|
INSERT INTO dd(c) VALUES ('2000-01-00 20:00:00');
|
|
Error 1292 (22007): Incorrect timestamp value: '2000-01-00 20:00:00' for column 'c' at row 1
|
|
INSERT INTO dd(c) VALUES ('2000-01-02');
|
|
UPDATE dd SET c = '2000-01-00 20:00:00';
|
|
Error 1292 (22007): Incorrect timestamp value: '2000-01-00 20:00:00'
|
|
UPDATE IGNORE dd SET b = '2000-01-00';
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Warning 1292 Incorrect datetime value: '2000-01-00'
|
|
SELECT b FROM dd;
|
|
b
|
|
0000-00-00 00:00:00
|
|
SET sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
|
|
TRUNCATE TABLE dd;
|
|
INSERT INTO dd(a) values('2000-00-10');
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
SELECT a FROM dd;
|
|
a
|
|
2000-00-10
|
|
TRUNCATE TABLE dd;
|
|
INSERT INTO dd(b) values('2000-10-01');
|
|
UPDATE dd SET b = '2000-00-10';
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
SELECT b FROM dd;
|
|
b
|
|
2000-00-10 00:00:00
|
|
TRUNCATE TABLE dd;
|
|
INSERT INTO dd(c) values('2000-10-01 10:00:00');
|
|
UPDATE dd SET c = '2000-00-10 00:00:00';
|
|
Error 1292 (22007): Incorrect timestamp value: '2000-00-10 00:00:00'
|
|
UPDATE IGNORE dd SET c = '2000-01-00 00:00:00';
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Warning 1292 Incorrect timestamp value: '2000-01-00 00:00:00'
|
|
SELECT c FROM dd;
|
|
c
|
|
0000-00-00 00:00:00
|
|
drop table if exists table_20220419;
|
|
CREATE TABLE table_20220419 (
|
|
id bigint(20) NOT NULL AUTO_INCREMENT,
|
|
lastLoginDate datetime NOT NULL,
|
|
PRIMARY KEY (id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
|
|
set sql_mode='';
|
|
insert into table_20220419 values(1,'0000-00-00 00:00:00');
|
|
set sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
|
|
insert into table_20220419(lastLoginDate) select lastLoginDate from table_20220419;
|
|
Error 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00'
|
|
set sql_mode=default;
|
|
drop table if exists test.t;
|
|
create table test.t (a varchar(10), b tinyint(1));
|
|
insert into test.t values ("abc", 1);
|
|
select * from test.t where (a, b) in (('a', 1), (null, 0));
|
|
a b
|
|
update test.t set b = 0 where (a, b) in (('a', 1), (null, 0));
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
drop table if exists test.t;
|
|
create table if not exists test.ast (i varchar(20));
|
|
create table if not exists test.acc (j varchar(20), k varchar(20), l varchar(20), m varchar(20));
|
|
explain format='plan_tree' with t as(select i, (case when b.j = '20001' then b.l else b.k end) an from test.ast a inner join test.acc b on (a.i = b.m) and a.i = 'astp2019121731703151'), t1 as (select i, group_concat(an order by an separator '; ') an from t group by i) select * from t1;
|
|
id task access object operator info
|
|
Projection root test.ast.i, Column
|
|
└─HashAgg root group by:Column, funcs:group_concat(Column order by Column separator "; ")->Column, funcs:firstrow(Column)->test.ast.i
|
|
└─Projection root case(eq(test.acc.j, 20001), test.acc.l, test.acc.k)->Column, test.ast.i->Column
|
|
└─HashJoin root inner join, equal:[eq(test.ast.i, test.acc.m)]
|
|
├─TableReader(Build) root data:Selection
|
|
│ └─Selection cop[tikv] eq(test.ast.i, "astp2019121731703151")
|
|
│ └─TableFullScan cop[tikv] table:a keep order:false, stats:pseudo
|
|
└─TableReader(Probe) root data:Selection
|
|
└─Selection cop[tikv] eq(test.acc.m, "astp2019121731703151")
|
|
└─TableFullScan cop[tikv] table:b keep order:false, stats:pseudo
|
|
drop table if exists test.t;
|
|
create table test.t (a int);
|
|
insert into test.t values(0),(20015),(20100);
|
|
select date_add(a, interval 12345 DAY_HOUR) from test.t;
|
|
date_add(a, interval 12345 DAY_HOUR)
|
|
NULL
|
|
NULL
|
|
NULL
|
|
drop table if exists test.t;
|
|
create table test.t(f1 char(1));
|
|
insert into test.t values ('a'),('b'),('1');
|
|
select (cast(f1 as float) = 1) or (cast(f1 as float) = 2) from test.t;
|
|
(cast(f1 as float) = 1) or (cast(f1 as float) = 2)
|
|
0
|
|
0
|
|
1
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Warning 1292 Truncated incorrect DOUBLE value: 'a'
|
|
Warning 1292 Truncated incorrect DOUBLE value: 'a'
|
|
Warning 1292 Truncated incorrect DOUBLE value: 'b'
|
|
Warning 1292 Truncated incorrect DOUBLE value: 'b'
|
|
select (cast(f1 as float) != 1) and (cast(f1 as float) != 2) from test.t;
|
|
(cast(f1 as float) != 1) and (cast(f1 as float) != 2)
|
|
1
|
|
1
|
|
0
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Warning 1292 Truncated incorrect DOUBLE value: 'a'
|
|
Warning 1292 Truncated incorrect DOUBLE value: 'a'
|
|
Warning 1292 Truncated incorrect DOUBLE value: 'b'
|
|
Warning 1292 Truncated incorrect DOUBLE value: 'b'
|
|
DROP TABLE IF EXISTS test.t;
|
|
CREATE TABLE test.t (id bigint(11) UNSIGNED PRIMARY KEY);
|
|
INSERT INTO test.t VALUES (1234567890123456);
|
|
SELECT IFNULL(id, 'abcdef') FROM test.t;
|
|
IFNULL(id, 'abcdef')
|
|
1234567890123456
|
|
drop table if exists test.t;
|
|
create table test.t(a double);
|
|
insert into test.t values('20000102030405.0078125');
|
|
select * from test.t where date_add(a, interval 1 second) = '2000-01-02 03:04:06.007813';
|
|
a
|
|
20000102030405.008
|
|
drop table if exists t0;
|
|
create table `t0`
|
|
(
|
|
`vkey` integer,
|
|
`pkey` integer,
|
|
`c0` integer
|
|
);
|
|
insert into `t0`
|
|
values (1, 2, 3);
|
|
select *
|
|
from `t0`
|
|
where (nullif(
|
|
3 ^ 10 & (abs(-50)) ,
|
|
round(case when (((`t0`.`c0`) >= 1) or null) then 91 else 86 end)
|
|
)) in (select `vkey` from `t0` where false);
|
|
vkey pkey c0
|
|
use test;
|
|
drop table if exists t;
|
|
create table t(a int);
|
|
insert into t values (1), (2), (3), (4), (5);
|
|
explain format='plan_tree' select * from t where a in (1, 1, 1, 1, 1, 2);
|
|
id task access object operator info
|
|
TableReader root data:Selection
|
|
└─Selection cop[tikv] in(test.t.a, 1, 2)
|
|
└─TableFullScan cop[tikv] table:t keep order:false, stats:pseudo
|
|
create table t_str(s varchar(10));
|
|
insert into t_str values ('test');
|
|
explain format='plan_tree' select * from t_str where s in ('a', 'a', 'a', 'b', 'a');
|
|
id task access object operator info
|
|
TableReader root data:Selection
|
|
└─Selection cop[tikv] in(test.t_str.s, "a", "b")
|
|
└─TableFullScan cop[tikv] table:t_str keep order:false, stats:pseudo
|
|
create table t_dec(d decimal(10,2));
|
|
insert into t_dec values (1.5), (2.5);
|
|
explain format='plan_tree' select * from t_dec where d in (1.5, 1.5, 2.5, 1.5);
|
|
id task access object operator info
|
|
TableReader root data:Selection
|
|
└─Selection cop[tikv] in(test.t_dec.d, 1.5, 2.5)
|
|
└─TableFullScan cop[tikv] table:t_dec keep order:false, stats:pseudo
|
|
explain format='plan_tree' select * from t where a in (1, NULL, NULL, NULL, 2);
|
|
id task access object operator info
|
|
TableReader root data:Selection
|
|
└─Selection cop[tikv] in(test.t.a, 1, NULL, 2)
|
|
└─TableFullScan cop[tikv] table:t keep order:false, stats:pseudo
|
|
create table t_date(dt date);
|
|
insert into t_date values ('2023-01-01'), ('2023-01-02');
|
|
explain format='plan_tree' select * from t_date where dt in
|
|
('2023-01-01', '2023-01-01', '2023-01-02');
|
|
id task access object operator info
|
|
TableReader root data:Selection
|
|
└─Selection cop[tikv] in(test.t_date.dt, 2023-01-01 00:00:00.000000, 2023-01-02 00:00:00.000000)
|
|
└─TableFullScan cop[tikv] table:t_date keep order:false, stats:pseudo
|
|
select * from t where a in (1, 1, 1, 1, 2);
|
|
a
|
|
1
|
|
2
|
|
select * from t_str where s in ('a', 'a', 'a', 'b', 'a');
|
|
s
|
|
select * from t_dec where d in (1.5, 1.5, 2.5, 1.5);
|
|
d
|
|
1.50
|
|
2.50
|
|
select * from t where a in (1, NULL, NULL, NULL, 2);
|
|
a
|
|
1
|
|
2
|
|
select * from t_date where dt in ('2023-01-01', '2023-01-01', '2023-01-02');
|
|
dt
|
|
2023-01-01
|
|
2023-01-02
|
|
SELECT sec_to_time(b'1111');
|
|
sec_to_time(b'1111')
|
|
00:00:15
|
|
SELECT sec_to_time(0x1E0);
|
|
sec_to_time(0x1E0)
|
|
00:08:00
|
|
drop table if exists lrr_test;
|
|
create table lrr_test( `COL1` tinyint(16),`COL3` bigint(20) DEFAULT NULL,KEY `UM_COL` (`COL1`,`COL3`));
|
|
insert into lrr_test(col3) values(-825024501864323944);
|
|
insert into lrr_test values(-2,2295421130981788987);
|
|
prepare stmt from 'select * from lrr_test t1 join lrr_test t2 on t1.col1 = t2.col1 where t1. col1 + 10 > ? + 10 or t2. col1 + 10 >= ? + 10;';
|
|
set @a=NULL, @b=-2;
|
|
execute stmt using @a,@b;
|
|
COL1 COL3 COL1 COL3
|
|
-2 2295421130981788987 -2 2295421130981788987
|
|
drop table if exists t_aes;
|
|
drop view if exists v_aes;
|
|
create table t_aes (myid int(11), myname varchar(10));
|
|
create view v_aes as select aes_encrypt(myid,'testkey') from t_aes;
|
|
desc v_aes;
|
|
Field Type Null Key Default Extra
|
|
aes_encrypt(myid,'testkey') varbinary(32) YES NULL
|
|
show columns from v_aes;
|
|
Field Type Null Key Default Extra
|
|
aes_encrypt(myid,'testkey') varbinary(32) YES NULL
|
|
drop view v_aes;
|
|
drop table t_aes;
|