1045 lines
44 KiB
Plaintext
1045 lines
44 KiB
Plaintext
drop table if exists t_issue_23993;
|
|
create table t_issue_23993(a double);
|
|
insert into t_issue_23993 values(-790822912);
|
|
select cast(a as time) from t_issue_23993;
|
|
cast(a as time)
|
|
NULL
|
|
select a from t_issue_23993 where cast(a as time);
|
|
a
|
|
drop table if exists t_issue_23993;
|
|
create table t_issue_23993(a int);
|
|
insert into t_issue_23993 values(-790822912);
|
|
select cast(a as time) from t_issue_23993;
|
|
cast(a as time)
|
|
NULL
|
|
select a from t_issue_23993 where cast(a as time);
|
|
a
|
|
drop table if exists t_issue_23993;
|
|
create table t_issue_23993(a decimal);
|
|
insert into t_issue_23993 values(-790822912);
|
|
select cast(a as time) from t_issue_23993;
|
|
cast(a as time)
|
|
NULL
|
|
select a from t_issue_23993 where cast(a as time);
|
|
a
|
|
drop table if exists t_issue_23993;
|
|
create table t_issue_23993(a varchar(255));
|
|
insert into t_issue_23993 values('-790822912');
|
|
select cast(a as time) from t_issue_23993;
|
|
cast(a as time)
|
|
-838:59:59
|
|
select a from t_issue_23993 where cast(a as time);
|
|
a
|
|
-790822912
|
|
SELECT HEX(WEIGHT_STRING('ab' AS BINARY(1000000000000000000)));
|
|
HEX(WEIGHT_STRING('ab' AS BINARY(1000000000000000000)))
|
|
NULL
|
|
Level Code Message
|
|
Warning 1301 Result of cast_as_binary() was larger than max_allowed_packet (67108864) - truncated
|
|
SELECT HEX(WEIGHT_STRING('ab' AS char(1000000000000000000)));
|
|
HEX(WEIGHT_STRING('ab' AS char(1000000000000000000)))
|
|
NULL
|
|
Level Code Message
|
|
Warning 1301 Result of weight_string() was larger than max_allowed_packet (67108864) - truncated
|
|
drop table if exists m, mp;
|
|
CREATE TABLE m (
|
|
mid varchar(50) NOT NULL,
|
|
ParentId varchar(50) DEFAULT NULL,
|
|
PRIMARY KEY (mid),
|
|
KEY ind_bm_parent (ParentId,mid)
|
|
);
|
|
CREATE TABLE mp (
|
|
mpid bigint(20) unsigned NOT NULL DEFAULT '0',
|
|
mid varchar(50) DEFAULT NULL COMMENT '模块主键',
|
|
sid int,
|
|
PRIMARY KEY (mpid)
|
|
);
|
|
insert into mp values("1","1","0");
|
|
insert into m values("0", "0");
|
|
SELECT ( SELECT COUNT(1) FROM m WHERE ParentId = c.mid ) expand, bmp.mpid, bmp.mpid IS NULL,bmp.mpid IS NOT NULL, sid FROM m c LEFT JOIN mp bmp ON c.mid = bmp.mid WHERE c.ParentId = '0';
|
|
expand mpid bmp.mpid IS NULL bmp.mpid IS NOT NULL sid
|
|
1 NULL 1 0 NULL
|
|
SELECT bmp.mpid, bmp.mpid IS NULL,bmp.mpid IS NOT NULL FROM m c LEFT JOIN mp bmp ON c.mid = bmp.mid WHERE c.ParentId = '0';
|
|
mpid bmp.mpid IS NULL bmp.mpid IS NOT NULL
|
|
NULL 1 0
|
|
drop table if exists t1;
|
|
CREATE TABLE `t1` (
|
|
`a` timestamp NULL DEFAULT NULL,
|
|
`b` year(4) DEFAULT NULL,
|
|
KEY `a` (`a`),
|
|
KEY `b` (`b`)
|
|
);
|
|
insert into t1 values("2002-10-03 04:28:53",2000), ("2002-10-03 04:28:53",2002), (NULL, 2002);
|
|
select /*+ inl_join (x,y) */ * from t1 x cross join t1 y on x.a=y.b;
|
|
a b a b
|
|
select * from t1 x cross join t1 y on x.a>y.b order by x.a, x.b, y.a, y.b;
|
|
a b a b
|
|
2002-10-03 04:28:53 2000 NULL 2002
|
|
2002-10-03 04:28:53 2000 2002-10-03 04:28:53 2000
|
|
2002-10-03 04:28:53 2000 2002-10-03 04:28:53 2002
|
|
2002-10-03 04:28:53 2002 NULL 2002
|
|
2002-10-03 04:28:53 2002 2002-10-03 04:28:53 2000
|
|
2002-10-03 04:28:53 2002 2002-10-03 04:28:53 2002
|
|
select * from t1 where a = b;
|
|
a b
|
|
select * from t1 where a < b;
|
|
a b
|
|
drop table if exists t;
|
|
create table t(a int) partition by hash (a div 0) partitions 10;
|
|
insert into t values (NULL);
|
|
select null div 0;
|
|
null div 0
|
|
NULL
|
|
select * from t;
|
|
a
|
|
NULL
|
|
drop table if exists t;
|
|
CREATE TABLE t (
|
|
a varchar(8) DEFAULT NULL,
|
|
b varchar(8) DEFAULT NULL,
|
|
c decimal(20,2) DEFAULT NULL,
|
|
d decimal(15,8) DEFAULT NULL
|
|
);
|
|
insert into t values(20210606, 20210606, 50000.00, 5.04600000);
|
|
select a * c *(d/36000) from t;
|
|
a * c *(d/36000)
|
|
141642663.71666598
|
|
select cast(a as double) * cast(c as double) *cast(d/36000 as double) from t;
|
|
cast(a as double) * cast(c as double) *cast(d/36000 as double)
|
|
141642663.71666598
|
|
select 20210606*50000.00*(5.04600000/36000);
|
|
20210606*50000.00*(5.04600000/36000)
|
|
141642663.71666599297980
|
|
select "20210606"*50000.00*(5.04600000/36000);
|
|
"20210606"*50000.00*(5.04600000/36000)
|
|
141642663.71666598
|
|
select cast("20210606" as double)*50000.00*(5.04600000/36000);
|
|
cast("20210606" as double)*50000.00*(5.04600000/36000)
|
|
141642663.71666598
|
|
drop table if exists t1, t2;
|
|
create table t1(a int, b varchar(8));
|
|
insert into t1 values(1,'1');
|
|
create table t2(a int , b varchar(8) GENERATED ALWAYS AS (c) VIRTUAL, c varchar(8), PRIMARY KEY (a));
|
|
insert into t2(a) values(1);
|
|
select /*+ tidb_inlj(t2) */ t2.b, t1.b from t1 join t2 ON t2.a=t1.a;
|
|
b b
|
|
NULL 1
|
|
drop table if exists t;
|
|
CREATE TABLE t (a bigint unsigned PRIMARY KEY);
|
|
INSERT INTO t VALUES (0),(1),(2),(3),(18446744073709551600),(18446744073709551605),(18446744073709551610),(18446744073709551615);
|
|
ANALYZE TABLE t;
|
|
EXPLAIN FORMAT = 'plan_tree' SELECT a FROM t WHERE a >= 0x1 AND a <= 0x2;
|
|
id task access object operator info
|
|
TableReader root data:TableRangeScan
|
|
└─TableRangeScan cop[tikv] table:t range:[1,2], keep order:false
|
|
EXPLAIN FORMAT = 'plan_tree' SELECT a FROM t WHERE a BETWEEN 0x1 AND 0x2;
|
|
id task access object operator info
|
|
TableReader root data:TableRangeScan
|
|
└─TableRangeScan cop[tikv] table:t range:[1,2], keep order:false
|
|
SELECT a FROM t WHERE a BETWEEN 0xFFFFFFFFFFFFFFF5 AND X'FFFFFFFFFFFFFFFA';
|
|
a
|
|
18446744073709551605
|
|
18446744073709551610
|
|
set @@tidb_enable_vectorized_expression=true;
|
|
select trim(leading from " a "), trim(both from " a "), trim(trailing from " a ");
|
|
trim(leading from " a ") trim(both from " a ") trim(trailing from " a ")
|
|
a a a
|
|
select trim(leading null from " a "), trim(both null from " a "), trim(trailing null from " a ");
|
|
trim(leading null from " a ") trim(both null from " a ") trim(trailing null from " a ")
|
|
NULL NULL NULL
|
|
select trim(null from " a ");
|
|
trim(null from " a ")
|
|
NULL
|
|
set @@tidb_enable_vectorized_expression=false;
|
|
select trim(leading from " a "), trim(both from " a "), trim(trailing from " a ");
|
|
trim(leading from " a ") trim(both from " a ") trim(trailing from " a ")
|
|
a a a
|
|
select trim(leading null from " a "), trim(both null from " a "), trim(trailing null from " a ");
|
|
trim(leading null from " a ") trim(both null from " a ") trim(trailing null from " a ")
|
|
NULL NULL NULL
|
|
select trim(null from " a ");
|
|
trim(null from " a ")
|
|
NULL
|
|
set tidb_enable_vectorized_expression=default;
|
|
drop table if exists t29142_1;
|
|
drop table if exists t29142_2;
|
|
create table t29142_1(a int);
|
|
create table t29142_2(a double);
|
|
insert into t29142_1 value(20);
|
|
select sum(distinct a) as x from t29142_1 having x > some ( select a from t29142_2 where x in (a));
|
|
x
|
|
drop table if exists e;
|
|
create table e (e enum('a', 'b'));
|
|
insert into e values ('a'), ('b');
|
|
select * from e where case 1 when 0 then e end;
|
|
e
|
|
select * from e where case 1 when 1 then e end;
|
|
e
|
|
a
|
|
b
|
|
select * from e where case e when 1 then e end;
|
|
e
|
|
a
|
|
select * from e where case 1 when e then e end;
|
|
e
|
|
a
|
|
drop table if exists t;
|
|
create table t (en enum('c', 'b', 'a'));
|
|
insert into t values ('a'), ('b'), ('c');
|
|
select max(en) from t;
|
|
max(en)
|
|
c
|
|
select min(en) from t;
|
|
min(en)
|
|
a
|
|
select * from t order by en;
|
|
en
|
|
c
|
|
b
|
|
a
|
|
drop table t;
|
|
create table t(s set('c', 'b', 'a'));
|
|
insert into t values ('a'), ('b'), ('c');
|
|
select max(s) from t;
|
|
max(s)
|
|
c
|
|
select min(s) from t;
|
|
min(s)
|
|
a
|
|
drop table t;
|
|
create table t(id int, en enum('c', 'b', 'a'));
|
|
insert into t values (1, 'a'),(2, 'b'), (3, 'c'), (1, 'c');
|
|
select id, max(en) from t where id=1 group by id;
|
|
id max(en)
|
|
1 c
|
|
select id, min(en) from t where id=1 group by id;
|
|
id min(en)
|
|
1 a
|
|
drop table t;
|
|
create table t(id int, s set('c', 'b', 'a'));
|
|
insert into t values (1, 'a'),(2, 'b'), (3, 'c'), (1, 'c');
|
|
select id, max(s) from t where id=1 group by id;
|
|
id max(s)
|
|
1 c
|
|
select id, min(s) from t where id=1 group by id;
|
|
id min(s)
|
|
1 a
|
|
drop table t;
|
|
create table t(e enum('e','d','c','b','a'));
|
|
insert into t values ('e'),('d'),('c'),('b'),('a');
|
|
select * from t order by e limit 1;
|
|
e
|
|
e
|
|
drop table t;
|
|
create table t(s set('e', 'd', 'c', 'b', 'a'));
|
|
insert into t values ('e'),('d'),('c'),('b'),('a');
|
|
select * from t order by s limit 1;
|
|
s
|
|
e
|
|
drop table t;
|
|
select distinct 0.7544678906163867 / 0.68234634;
|
|
0.7544678906163867 / 0.68234634
|
|
1.10569639842486251190
|
|
drop table if exists t_issue_22231;
|
|
create table t_issue_22231(a datetime);
|
|
insert into t_issue_22231 values('2020--05-20 01:22:12');
|
|
select * from t_issue_22231 where a >= '2020-05-13 00:00:00 00:00:00' and a <= '2020-05-28 23:59:59 00:00:00';
|
|
a
|
|
2020-05-20 01:22:12
|
|
Level Code Message
|
|
Warning 1292 Truncated incorrect datetime value: '2020-05-13 00:00:00 00:00:00'
|
|
Warning 1292 Truncated incorrect datetime value: '2020-05-28 23:59:59 00:00:00'
|
|
select cast('2020-10-22 10:31-10:12' as datetime);
|
|
cast('2020-10-22 10:31-10:12' as datetime)
|
|
2020-10-22 10:31:10
|
|
Level Code Message
|
|
Warning 1292 Truncated incorrect datetime value: '2020-10-22 10:31-10:12'
|
|
select cast('2020-05-28 23:59:59 00:00:00' as datetime);
|
|
cast('2020-05-28 23:59:59 00:00:00' as datetime)
|
|
2020-05-28 23:59:59
|
|
Level Code Message
|
|
Warning 1292 Truncated incorrect datetime value: '2020-05-28 23:59:59 00:00:00'
|
|
SELECT CAST("1111111111-" AS DATE);
|
|
CAST("1111111111-" AS DATE)
|
|
NULL
|
|
Level Code Message
|
|
Warning 1292 Incorrect datetime value: '1111111111-'
|
|
drop table if exists t;
|
|
create table t (
|
|
create_at datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
|
|
finish_at datetime NOT NULL DEFAULT '1000-01-01 00:00:00');
|
|
insert into t values ('2016-02-13 15:32:24', '2016-02-11 17:23:22');
|
|
select timediff(finish_at, create_at) from t;
|
|
timediff(finish_at, create_at)
|
|
-46:09:02
|
|
drop table if exists t1, t2;
|
|
create table t1 (c1 int);
|
|
create table t2 (c2 int);
|
|
insert into t1 values (1);
|
|
insert into t2 values (2);
|
|
update t1, t2 set t1.c1 = 2, t2.c2 = 1;
|
|
update t1, t2 set c1 = 2, c2 = 1;
|
|
update t1 as a, t2 as b set a.c1 = 2, b.c2 = 1;
|
|
SELECT * FROM t1;
|
|
c1
|
|
2
|
|
SELECT * FROM t2;
|
|
c2
|
|
1
|
|
update t1 as a, t2 as t1 set a.c1 = 1, t1.c2 = 2;
|
|
SELECT * FROM t1;
|
|
c1
|
|
1
|
|
SELECT * FROM t2;
|
|
c2
|
|
2
|
|
update t1 as a, t2 set t1.c1 = 10;
|
|
Error 1054 (42S22): Unknown column 'c1' in 'field list'
|
|
drop table if exists t1, t2;
|
|
create table t1 (a int);
|
|
create table t2 (a int);
|
|
insert into t1 values(1);
|
|
insert into t2 values(1);
|
|
select tbl1.* from (select t1.a, 1 from t1) tbl1 left join t2 tbl2 on tbl1.a = tbl2.a order by tbl1.a desc limit 1;
|
|
a 1
|
|
1 1
|
|
create database executor__issues2;
|
|
use executor__issues2;
|
|
create table t(a int);
|
|
insert into t values(1);
|
|
use executor__issues;
|
|
drop table if exists t;
|
|
create table t(a int);
|
|
insert into t values(1);
|
|
update t, executor__issues2.t set executor__issues2.t.a=2;
|
|
select * from t;
|
|
a
|
|
1
|
|
select * from executor__issues2.t;
|
|
a
|
|
2
|
|
update executor__issues.t, executor__issues2.t set executor__issues.t.a=3;
|
|
select * from t;
|
|
a
|
|
3
|
|
select * from executor__issues2.t;
|
|
a
|
|
2
|
|
drop database executor__issues2;
|
|
set @@profiling=1;
|
|
SELECT QUERY_ID, SUM(DURATION) AS SUM_DURATION FROM INFORMATION_SCHEMA.PROFILING GROUP BY QUERY_ID;
|
|
QUERY_ID SUM_DURATION
|
|
0 0
|
|
drop table if exists t;
|
|
create table t(a char);
|
|
insert into t value('a');
|
|
select * from t where a < 1 order by a limit 0;
|
|
a
|
|
drop table if exists t;
|
|
create table t (a float);
|
|
create index a on t(a);
|
|
insert into t values (1.0), (NULL), (0), (2.0);
|
|
select `a` from `t` use index (a) where !`a`;
|
|
a
|
|
0
|
|
select `a` from `t` ignore index (a) where !`a`;
|
|
a
|
|
0
|
|
select `a` from `t` use index (a) where `a`;
|
|
a
|
|
1
|
|
2
|
|
select `a` from `t` ignore index (a) where `a`;
|
|
a
|
|
1
|
|
2
|
|
select a from t use index (a) where not a is true;
|
|
a
|
|
NULL
|
|
0
|
|
select a from t use index (a) where not not a is true;
|
|
a
|
|
1
|
|
2
|
|
select a from t use index (a) where not not a;
|
|
a
|
|
1
|
|
2
|
|
select a from t use index (a) where not not not a is true;
|
|
a
|
|
NULL
|
|
0
|
|
select a from t use index (a) where not not not a;
|
|
a
|
|
0
|
|
drop table if exists t1, t2;
|
|
create table t1 (c decimal);
|
|
create table t2 (c decimal, key(c));
|
|
insert into t1 values (null);
|
|
insert into t2 values (null);
|
|
select count(*) from t1 where not c;
|
|
count(*)
|
|
0
|
|
select count(*) from t2 where not c;
|
|
count(*)
|
|
0
|
|
select count(*) from t1 where c;
|
|
count(*)
|
|
0
|
|
select count(*) from t2 where c;
|
|
count(*)
|
|
0
|
|
drop table if exists t;
|
|
create table t (a timestamp);
|
|
insert into t values ("1970-07-23 10:04:59"), ("2038-01-19 03:14:07");
|
|
select * from t where date_sub(a, interval 10 month) = date_sub("1970-07-23 10:04:59", interval 10 month);
|
|
a
|
|
1970-07-23 10:04:59
|
|
select * from t where timestampadd(hour, 1, a ) = timestampadd(hour, 1, "2038-01-19 03:14:07");
|
|
a
|
|
2038-01-19 03:14:07
|
|
drop table if exists tt;
|
|
create table tt(a decimal(10, 0), b varchar(1), c time);
|
|
insert into tt values(0, '2', null), (7, null, '1122'), (NULL, 'w', null), (NULL, '2', '3344'), (NULL, NULL, '0'), (7, 'f', '33');
|
|
select a and b as d, a or c as e from tt;
|
|
d e
|
|
0 NULL
|
|
NULL 1
|
|
0 NULL
|
|
NULL 1
|
|
NULL NULL
|
|
0 1
|
|
drop table if exists tt;
|
|
create table tt(a decimal(10, 0), b varchar(1), c time);
|
|
insert into tt values(0, '2', '123'), (7, null, '1122'), (null, 'w', null);
|
|
select a and b as d, a, b from tt order by d limit 1;
|
|
d a b
|
|
NULL 7 NULL
|
|
select b or c as d, b, c from tt order by d limit 1;
|
|
d b c
|
|
NULL w NULL
|
|
drop table if exists t0;
|
|
CREATE TABLE t0(c0 FLOAT);
|
|
INSERT INTO t0(c0) VALUES (NULL);
|
|
SELECT * FROM t0 WHERE NOT(0 OR t0.c0);
|
|
c0
|
|
drop table if exists t;
|
|
create table t(a int, b char);
|
|
insert into t values (1,'s'),(2,'b'),(1,'c'),(2,'e'),(1,'a');
|
|
insert into t select * from t;
|
|
insert into t select * from t;
|
|
insert into t select * from t;
|
|
select b, count(*) from ( select b from t order by a limit 20 offset 2) as s group by b order by b;
|
|
b count(*)
|
|
a 6
|
|
c 7
|
|
s 7
|
|
drop table if exists t0;
|
|
CREATE TABLE t0(c0 NUMERIC PRIMARY KEY);
|
|
INSERT IGNORE INTO t0(c0) VALUES (NULL);
|
|
SELECT * FROM t0 WHERE c0;
|
|
c0
|
|
drop table if exists t;
|
|
CREATE TABLE `t` ( `a` enum('WAITING','PRINTED','STOCKUP','CHECKED','OUTSTOCK','PICKEDUP','WILLBACK','BACKED') DEFAULT NULL);
|
|
insert into t values(1),(2),(3),(4),(5),(6),(7);
|
|
insert into t select * from t;
|
|
insert into t select * from t;
|
|
insert into t select * from t;
|
|
insert into t select * from t;
|
|
insert into t select * from t;
|
|
insert into t select * from t;
|
|
insert into t select * from t;
|
|
set @@tidb_max_chunk_size=100;
|
|
select distinct a from t order by a;
|
|
a
|
|
WAITING
|
|
PRINTED
|
|
STOCKUP
|
|
CHECKED
|
|
OUTSTOCK
|
|
PICKEDUP
|
|
WILLBACK
|
|
drop table t;
|
|
CREATE TABLE `t` ( `a` set('WAITING','PRINTED','STOCKUP','CHECKED','OUTSTOCK','PICKEDUP','WILLBACK','BACKED') DEFAULT NULL);
|
|
insert into t values(1),(2),(3),(4),(5),(6),(7);
|
|
insert into t select * from t;
|
|
insert into t select * from t;
|
|
insert into t select * from t;
|
|
insert into t select * from t;
|
|
insert into t select * from t;
|
|
insert into t select * from t;
|
|
insert into t select * from t;
|
|
set @@tidb_max_chunk_size=100;
|
|
select distinct a from t order by a;
|
|
a
|
|
WAITING
|
|
PRINTED
|
|
WAITING,PRINTED
|
|
STOCKUP
|
|
WAITING,STOCKUP
|
|
PRINTED,STOCKUP
|
|
WAITING,PRINTED,STOCKUP
|
|
set @@tidb_max_chunk_size=default;
|
|
drop table if exists t2;
|
|
create table t2 (a year(4));
|
|
insert into t2 values(69);
|
|
select * from t2 where a <= 69;
|
|
a
|
|
2069
|
|
drop table if exists t3;
|
|
CREATE TABLE `t3` (`y` year DEFAULT NULL, `a` int DEFAULT NULL);
|
|
INSERT INTO `t3` VALUES (2069, 70), (2010, 11), (2155, 2156), (2069, 69);
|
|
SELECT * FROM `t3` where y <= a;
|
|
y a
|
|
2155 2156
|
|
drop table if exists t3;
|
|
create table t3 (a year);
|
|
insert into t3 values (1991), ("1992"), ("93"), (94);
|
|
select * from t3 where a >= NULL;
|
|
a
|
|
drop table if exists t;
|
|
CREATE TABLE `t` (`id` int(11) DEFAULT NULL, `tp_bigint` bigint(20) DEFAULT NULL );
|
|
insert into t values(0,1),(1,9215570218099803537);
|
|
select A.tp_bigint,B.id from t A join t B on A.id < B.id * 16 where A.tp_bigint = B.id;
|
|
tp_bigint id
|
|
1 1
|
|
drop table if exists t0;
|
|
create table t0 (c0 double);
|
|
insert into t0 values (1e30);
|
|
update t0 set c0=0 where t0.c0 like 0;
|
|
select count(*) from t0 where c0 = 0;
|
|
count(*)
|
|
0
|
|
drop table if exists t;
|
|
create table t (a year);
|
|
insert into t values(0);
|
|
select cast(a as char) from t;
|
|
cast(a as char)
|
|
0000
|
|
SELECT TIMESTAMP '9999-01-01 00:00:00';
|
|
TIMESTAMP '9999-01-01 00:00:00'
|
|
9999-01-01 00:00:00
|
|
drop table if exists ta;
|
|
create table ta(id decimal(60,2));
|
|
insert into ta values (JSON_EXTRACT('{"c": "1234567890123456789012345678901234567890123456789012345"}', '$.c'));
|
|
select * from ta;
|
|
id
|
|
1234567890123456789012345678901234567890123456789012345.00
|
|
drop table if exists t1;
|
|
create table t1 (f1 json);
|
|
insert into t1(f1) values ('"asd"'),('"asdf"'),('"asasas"');
|
|
select f1 from t1 where json_extract(f1,"$") in ("asd","asasas","asdf");
|
|
f1
|
|
"asd"
|
|
"asdf"
|
|
"asasas"
|
|
select f1 from t1 where json_extract(f1, '$') = 'asd';
|
|
f1
|
|
"asd"
|
|
select f1 from t1 where case json_extract(f1,"$") when "asd" then 1 else 0 end;
|
|
f1
|
|
"asd"
|
|
delete from t1;
|
|
insert into t1 values ('{"a": 1}');
|
|
select f1 from t1 where f1 in ('{"a": 1}', 'asdf', 'asdf');
|
|
f1
|
|
select f1 from t1 where f1 in (cast('{"a": 1}' as JSON), 'asdf', 'asdf');
|
|
f1
|
|
{"a": 1}
|
|
select json_extract('"asd"', '$') = 'asd';
|
|
json_extract('"asd"', '$') = 'asd'
|
|
1
|
|
select json_extract('"asd"', '$') <=> 'asd';
|
|
json_extract('"asd"', '$') <=> 'asd'
|
|
1
|
|
select json_extract('"asd"', '$') <> 'asd';
|
|
json_extract('"asd"', '$') <> 'asd'
|
|
0
|
|
select json_extract('{"f": 1.0}', '$.f') = 1.0;
|
|
json_extract('{"f": 1.0}', '$.f') = 1.0
|
|
1
|
|
select json_extract('{"f": 1.0}', '$.f') = '1.0';
|
|
json_extract('{"f": 1.0}', '$.f') = '1.0'
|
|
0
|
|
select json_extract('{"n": 1}', '$') = '{"n": 1}';
|
|
json_extract('{"n": 1}', '$') = '{"n": 1}'
|
|
0
|
|
select json_extract('{"n": 1}', '$') <> '{"n": 1}';
|
|
json_extract('{"n": 1}', '$') <> '{"n": 1}'
|
|
1
|
|
drop table if exists t;
|
|
create table t (a int, b int);
|
|
insert into t values (2, 20), (1, 10), (3, 30);
|
|
select a + 1 as field1, a as field2 from t order by field1, field2 limit 2;
|
|
field1 field2
|
|
2 1
|
|
3 2
|
|
drop table if exists t;
|
|
create table t (c int auto_increment, key(c)) auto_id_cache 1;
|
|
insert into t values();
|
|
insert into t values();
|
|
select * from t;
|
|
c
|
|
1
|
|
2
|
|
drop table if exists test;
|
|
create table test(id float primary key clustered AUTO_INCREMENT, col1 int);
|
|
replace into test(col1) values(1);
|
|
replace into test(col1) values(2);
|
|
select * from test;
|
|
id col1
|
|
1 1
|
|
2 2
|
|
drop table test;
|
|
drop table if exists test;
|
|
create table test(id float primary key nonclustered AUTO_INCREMENT, col1 int) AUTO_ID_CACHE 1;
|
|
replace into test(col1) values(1);
|
|
replace into test(col1) values(2);
|
|
select * from test;
|
|
id col1
|
|
1 1
|
|
2 2
|
|
drop table test;
|
|
create table test2(id double primary key clustered AUTO_INCREMENT, col1 int);
|
|
replace into test2(col1) values(1);
|
|
insert into test2(col1) values(1);
|
|
replace into test2(col1) values(1);
|
|
insert into test2(col1) values(1);
|
|
replace into test2(col1) values(1);
|
|
replace into test2(col1) values(1);
|
|
select * from test2;
|
|
id col1
|
|
1 1
|
|
2 1
|
|
3 1
|
|
4 1
|
|
5 1
|
|
6 1
|
|
drop table test2;
|
|
create table test2(id double primary key nonclustered AUTO_INCREMENT, col1 int) AUTO_ID_CACHE 1;
|
|
replace into test2(col1) values(1);
|
|
insert into test2(col1) values(1);
|
|
replace into test2(col1) values(1);
|
|
insert into test2(col1) values(1);
|
|
replace into test2(col1) values(1);
|
|
replace into test2(col1) values(1);
|
|
select * from test2;
|
|
id col1
|
|
1 1
|
|
2 1
|
|
3 1
|
|
4 1
|
|
5 1
|
|
6 1
|
|
drop table test2;
|
|
drop table if exists t1;
|
|
CREATE TABLE t1 (
|
|
c_int int(11) NOT NULL,
|
|
c_str varbinary(40) NOT NULL,
|
|
c_datetime datetime DEFAULT NULL,
|
|
c_timestamp timestamp NULL DEFAULT NULL,
|
|
c_double double DEFAULT NULL,
|
|
c_decimal decimal(12,6) DEFAULT NULL,
|
|
c_enum enum('blue','green','red','yellow','white','orange','purple') DEFAULT NULL,
|
|
PRIMARY KEY (c_int,c_str) /*T![clustered_index] CLUSTERED */,
|
|
KEY c_int_2 (c_int),
|
|
KEY c_decimal (c_decimal),
|
|
KEY c_datetime (c_datetime)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
|
|
PARTITION BY LIST COLUMNS(c_int)
|
|
(PARTITION p0 VALUES IN (1,5,9,13,17,21,25,29,33,37),
|
|
PARTITION p1 VALUES IN (2,6,10,14,18,22,26,30,34,38),
|
|
PARTITION p2 VALUES IN (3,7,11,15,19,23,27,31,35,39),
|
|
PARTITION p3 VALUES IN (4,8,12,16,20,24,28,32,36,40));
|
|
INSERT INTO t1 VALUES (3,'bold goldberg','2020-01-07 12:08:19','2020-06-19 08:13:35',0.941002,5.303000,'yellow'),(1,'crazy wescoff','2020-03-24 21:51:02','2020-06-19 08:13:35',47.565275,6.313000,'orange'),(5,'relaxed gagarin','2020-05-20 11:36:26','2020-06-19 08:13:35',38.948617,3.143000,'green'),(9,'gifted vaughan','2020-04-09 16:19:45','2020-06-19 08:13:35',95.922976,8.708000,'yellow'),(2,'focused taussig','2020-05-17 17:58:34','2020-06-19 08:13:35',4.137803,4.902000,'white'),(6,'fervent yonath','2020-05-26 03:55:25','2020-06-19 08:13:35',72.394272,6.491000,'white'),(18,'mystifying bhaskara','2020-02-19 10:41:48','2020-06-19 08:13:35',10.832397,9.707000,'red'),(4,'goofy saha','2020-03-11 13:24:31','2020-06-19 08:13:35',39.007216,2.446000,'blue'),(20,'mystifying bhaskara','2020-04-03 11:33:27','2020-06-19 08:13:35',85.190386,6.787000,'blue');
|
|
DROP TABLE IF EXISTS t2;
|
|
CREATE TABLE t2 (
|
|
c_int int(11) NOT NULL,
|
|
c_str varbinary(40) NOT NULL,
|
|
c_datetime datetime DEFAULT NULL,
|
|
c_timestamp timestamp NULL DEFAULT NULL,
|
|
c_double double DEFAULT NULL,
|
|
c_decimal decimal(12,6) DEFAULT NULL,
|
|
c_enum enum('blue','green','red','yellow','white','orange','purple') DEFAULT NULL,
|
|
PRIMARY KEY (c_int,c_str) /*T![clustered_index] CLUSTERED */,
|
|
KEY c_int_2 (c_int),
|
|
KEY c_decimal (c_decimal),
|
|
KEY c_datetime (c_datetime)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
|
|
PARTITION BY LIST COLUMNS(c_int)
|
|
(PARTITION p0 VALUES IN (1,5,9,13,17,21,25,29,33,37),
|
|
PARTITION p1 VALUES IN (2,6,10,14,18,22,26,30,34,38),
|
|
PARTITION p2 VALUES IN (3,7,11,15,19,23,27,31,35,39),
|
|
PARTITION p3 VALUES IN (4,8,12,16,20,24,28,32,36,40));
|
|
INSERT INTO t2 VALUES (1,'crazy wescoff','2020-03-24 21:51:02','2020-04-01 12:11:56',47.565275,6.313000,'orange'),(1,'unruffled johnson','2020-06-30 03:42:58','2020-06-14 00:16:50',35.444084,1.090000,'red'),(5,'relaxed gagarin','2020-05-20 11:36:26','2020-02-19 12:25:48',38.948617,3.143000,'green'),(9,'eloquent archimedes','2020-02-16 04:20:21','2020-05-23 15:42:33',32.310878,5.855000,'orange'),(9,'gifted vaughan','2020-04-09 16:19:45','2020-05-15 01:42:16',95.922976,8.708000,'yellow'),(13,'dreamy benz','2020-04-27 17:43:44','2020-03-27 06:33:03',39.539233,4.823000,'red'),(3,'bold goldberg','2020-01-07 12:08:19','2020-03-10 18:37:09',0.941002,5.303000,'yellow'),(3,'youthful yonath','2020-01-12 17:10:39','2020-06-10 15:13:44',66.288511,6.046000,'white'),(7,'upbeat bhabha','2020-04-29 01:17:05','2020-03-11 22:58:43',23.316987,9.026000,'yellow'),(11,'quizzical ritchie','2020-05-16 08:21:36','2020-03-05 19:23:25',75.019379,0.260000,'purple'),(2,'dazzling kepler','2020-04-11 04:38:59','2020-05-06 04:42:32',78.798503,2.274000,'purple'),(2,'focused taussig','2020-05-17 17:58:34','2020-02-25 09:11:03',4.137803,4.902000,'white'),(2,'sharp ptolemy',NULL,'2020-05-17 18:04:19',NULL,5.573000,'purple'),(6,'fervent yonath','2020-05-26 03:55:25','2020-05-06 14:23:44',72.394272,6.491000,'white'),(10,'musing wu','2020-04-03 11:33:27','2020-05-24 06:11:56',85.190386,6.787000,'blue'),(8,'hopeful keller','2020-02-19 10:41:48','2020-04-19 17:10:36',10.832397,9.707000,'red'),(12,'exciting boyd',NULL,'2020-03-28 18:27:23',NULL,9.249000,'blue');
|
|
set tidb_txn_assertion_level=strict;
|
|
begin;
|
|
delete t1, t2 from t1, t2 where t1.c_enum in ('blue');
|
|
commit;
|
|
set tidb_txn_assertion_level=default;
|
|
drop table if exists t1;
|
|
create table t1 (_id int PRIMARY KEY, c1 char, index (c1));
|
|
insert into t1 values (1, null);
|
|
select * from t1 where c1 is null and _id < 1;
|
|
_id c1
|
|
drop table if exists t1, t2;
|
|
CREATE TABLE t1 (
|
|
c1 double DEFAULT '1.335088259490289',
|
|
c2 set('mj','4s7ht','z','3i','b26','9','cg11','uvzcp','c','ns','fl9') NOT NULL DEFAULT 'mj,z,3i,9,cg11,c',
|
|
PRIMARY KEY (c2) /*T![clustered_index] CLUSTERED */,
|
|
KEY i1 (c1),
|
|
KEY i2 (c1),
|
|
KEY i3 (c1)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=gbk COLLATE=gbk_chinese_ci;
|
|
INSERT INTO t1 VALUES (634.2783557491367,''),(2000.5041449792013,'4s7ht'),(634.2783557491367,'3i'),(634.2783557491367,'9'),(7803.173688589342,'uvzcp'),(634.2783557491367,'ns'),(634.2783557491367,'fl9');
|
|
CREATE TABLE t2 (
|
|
c3 decimal(56,16) DEFAULT '931359772706767457132645278260455518957.9866038319986886',
|
|
c4 set('3bqx','g','6op3','2g','jf','arkd3','y0b','jdy','1g','ff5z','224b') DEFAULT '3bqx,2g,ff5z,224b',
|
|
c5 smallint(6) NOT NULL DEFAULT '-25973',
|
|
c6 year(4) DEFAULT '2122',
|
|
c7 text DEFAULT NULL,
|
|
PRIMARY KEY (c5) /*T![clustered_index] CLUSTERED */,
|
|
KEY i4 (c6),
|
|
KEY i5 (c5)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT=''
|
|
PARTITION BY HASH (c5) PARTITIONS 4;
|
|
INSERT INTO t2 VALUES (465.0000000000000000,'jdy',-8542,2008,'FgZXe');
|
|
set @@sql_mode='';
|
|
set tidb_partition_prune_mode=dynamic;
|
|
analyze table t1;
|
|
analyze table t2;
|
|
select /*+ inl_join( t1 , t2 ) */ avg( t2.c5 ) as r0 , repeat( t2.c7 , t2.c5 ) as r1 , locate( t2.c7 , t2.c7 ) as r2 , unhex( t1.c1 ) as r3 from t1 right join t2 on t1.c2 = t2.c5 where not( t2.c5 in ( -7860 ,-13384 ,-12940 ) ) and not( t1.c2 between '4s7ht' and 'mj' );
|
|
r0 r1 r2 r3
|
|
NULL NULL NULL NULL
|
|
select /*+ inl_join (t1, t2) */ t2.c5 from t1 right join t2 on t1.c2 = t2.c5 where not( t1.c2 between '4s7ht' and 'mj' );
|
|
c5
|
|
set sql_mode=default;
|
|
set tidb_partition_prune_mode=default;
|
|
drop table if exists ta, tb, tc;
|
|
CREATE TABLE ta (
|
|
a1 json DEFAULT NULL,
|
|
a2 decimal(31, 1) DEFAULT '0'
|
|
);
|
|
CREATE TABLE tb (
|
|
b1 smallint(6) DEFAULT '-11385',
|
|
b2 decimal(63, 14) DEFAULT '-6197127648752447138876497216172307937317445669286.98661563645110'
|
|
);
|
|
CREATE TABLE tc (
|
|
c1 text DEFAULT NULL,
|
|
c2 float NOT NULL DEFAULT '1.8132474',
|
|
PRIMARY KEY (c2)
|
|
/*T![clustered_index] CLUSTERED */
|
|
);
|
|
insert into ta
|
|
values (NULL, 1228.0);
|
|
insert into ta
|
|
values ('"json string1"', 623.8);
|
|
insert into ta
|
|
values (NULL, 1337.0);
|
|
select count(*)from ta where not ( ta.a1 in ( select b2 from tb where not ( ta.a1 in ( select c1 from tc where ta.a2 in ( select b2 from tb where IsNull(ta.a1) ) ) ) ) );
|
|
Error 1815 (HY000): expression isnull(cast(executor__issues.ta.a1, var_string(4294967295))) cannot be pushed down
|
|
drop table if exists V, F;
|
|
create table V (id int primary key, col_int int);
|
|
insert into V values (1, 8);
|
|
create table F (id int primary key, col_int int);
|
|
insert into F values (1, 8);
|
|
select table1.`col_int` as field1, table1.`col_int` as field2 from V as table1 left join F as table2 on table1.`col_int` = table2.`col_int` order by field1, field2 desc limit 2;
|
|
field1 field2
|
|
8 8
|
|
set tidb_cost_model_version=2;
|
|
drop table if exists t1, t2;
|
|
create table t1 (c_int int, c_str varchar(40), c_decimal decimal(12, 6), primary key (c_int) , key(c_str(2)) , key(c_decimal) ) partition by list (c_int) ( partition p0 values IN (1, 5, 9, 13, 17, 21, 25, 29, 33, 37), partition p1 values IN (2, 6, 10, 14, 18, 22, 26, 30, 34, 38), partition p2 values IN (3, 7, 11, 15, 19, 23, 27, 31, 35, 39), partition p3 values IN (4, 8, 12, 16, 20, 24, 28, 32, 36, 40)) ;
|
|
create table t2 (c_int int, c_str varchar(40), c_decimal decimal(12, 6), primary key (c_int) , key(c_str) , key(c_decimal) ) partition by hash (c_int) partitions 4;
|
|
insert into t1 values (6, 'musing mayer', 1.280), (7, 'wizardly heisenberg', 6.589), (8, 'optimistic swirles', 9.633), (9, 'hungry haslett', 2.659), (10, 'stupefied wiles', 2.336);
|
|
insert into t2 select * from t1 ;
|
|
analyze table t1;
|
|
analyze table t2;
|
|
begin;
|
|
select * from t1 where c_str <> any (select c_str from t2 where c_decimal < 5) for update;
|
|
c_int c_str c_decimal
|
|
10 stupefied wiles 2.336000
|
|
6 musing mayer 1.280000
|
|
7 wizardly heisenberg 6.589000
|
|
8 optimistic swirles 9.633000
|
|
9 hungry haslett 2.659000
|
|
explain format = 'plan_tree' select * from t1 where c_str <> any (select c_str from t2 where c_decimal < 5) for update;
|
|
id task access object operator info
|
|
SelectLock root for update 0
|
|
└─HashJoin root CARTESIAN inner join, other cond:or(gt(Column, 1), or(ne(executor__issues.t1.c_str, Column), if(ne(Column, 0), NULL, 0)))
|
|
├─Selection(Build) root ne(Column, 0)
|
|
│ └─StreamAgg root funcs:max(Column)->Column, funcs:count(distinct Column)->Column, funcs:sum(Column)->Column, funcs:count(1)->Column
|
|
│ └─Projection root executor__issues.t2.c_str->Column, executor__issues.t2.c_str->Column, cast(isnull(executor__issues.t2.c_str), decimal(20,0) BINARY)->Column
|
|
│ └─TableReader root partition:all data:Selection
|
|
│ └─Selection cop[tikv] lt(executor__issues.t2.c_decimal, 5)
|
|
│ └─TableFullScan cop[tikv] table:t2 keep order:false
|
|
└─TableReader(Probe) root partition:all data:Selection
|
|
└─Selection cop[tikv] if(isnull(executor__issues.t1.c_str), NULL, 1)
|
|
└─TableFullScan cop[tikv] table:t1 keep order:false
|
|
commit;
|
|
set tidb_cost_model_version=default;
|
|
drop table if exists trade, trade_history, status_type;
|
|
set @@foreign_key_checks=0;
|
|
CREATE TABLE trade (
|
|
t_id bigint(16) NOT NULL AUTO_INCREMENT,
|
|
t_dts datetime NOT NULL,
|
|
t_st_id char(4) NOT NULL,
|
|
t_tt_id char(3) NOT NULL,
|
|
t_is_cash tinyint(1) NOT NULL,
|
|
t_s_symb char(15) NOT NULL,
|
|
t_qty mediumint(7) NOT NULL,
|
|
t_bid_price decimal(8,2) NOT NULL,
|
|
t_ca_id bigint(12) NOT NULL,
|
|
t_exec_name varchar(49) NOT NULL,
|
|
t_trade_price decimal(8,2) DEFAULT NULL,
|
|
t_chrg decimal(10,2) NOT NULL,
|
|
t_comm decimal(10,2) NOT NULL,
|
|
t_tax decimal(10,2) NOT NULL,
|
|
t_lifo tinyint(1) NOT NULL,
|
|
PRIMARY KEY (t_id) /*T![clustered_index] CLUSTERED */,
|
|
KEY i_t_ca_id_dts (t_ca_id,t_dts),
|
|
KEY i_t_s_symb_dts (t_s_symb,t_dts),
|
|
CONSTRAINT fk_trade_st FOREIGN KEY (t_st_id) REFERENCES status_type (st_id),
|
|
CONSTRAINT fk_trade_tt FOREIGN KEY (t_tt_id) REFERENCES trade_type (tt_id),
|
|
CONSTRAINT fk_trade_s FOREIGN KEY (t_s_symb) REFERENCES security (s_symb),
|
|
CONSTRAINT fk_trade_ca FOREIGN KEY (t_ca_id) REFERENCES customer_account (ca_id)
|
|
) ;
|
|
CREATE TABLE trade_history (
|
|
th_t_id bigint(16) NOT NULL,
|
|
th_dts datetime NOT NULL,
|
|
th_st_id char(4) NOT NULL,
|
|
PRIMARY KEY (th_t_id,th_st_id) /*T![clustered_index] NONCLUSTERED */,
|
|
KEY i_th_t_id_dts (th_t_id,th_dts),
|
|
CONSTRAINT fk_trade_history_t FOREIGN KEY (th_t_id) REFERENCES trade (t_id),
|
|
CONSTRAINT fk_trade_history_st FOREIGN KEY (th_st_id) REFERENCES status_type (st_id)
|
|
);
|
|
CREATE TABLE status_type (
|
|
st_id char(4) NOT NULL,
|
|
st_name char(10) NOT NULL,
|
|
PRIMARY KEY (st_id) /*T![clustered_index] NONCLUSTERED */
|
|
);
|
|
set @@foreign_key_checks=default;
|
|
drop table if exists partsupp, supplier, nation;
|
|
SET GLOBAL tidb_mem_oom_action='CANCEL';
|
|
CREATE TABLE `partsupp` ( `PS_PARTKEY` bigint(20) NOT NULL,`PS_SUPPKEY` bigint(20) NOT NULL,`PS_AVAILQTY` bigint(20) NOT NULL,`PS_SUPPLYCOST` decimal(15,2) NOT NULL,`PS_COMMENT` varchar(199) NOT NULL,PRIMARY KEY (`PS_PARTKEY`,`PS_SUPPKEY`) /*T![clustered_index] CLUSTERED */) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
|
|
CREATE TABLE `supplier` (`S_SUPPKEY` bigint(20) NOT NULL,`S_NAME` char(25) NOT NULL,`S_ADDRESS` varchar(40) NOT NULL,`S_NATIONKEY` bigint(20) NOT NULL,`S_PHONE` char(15) NOT NULL,`S_ACCTBAL` decimal(15,2) NOT NULL,`S_COMMENT` varchar(101) NOT NULL,PRIMARY KEY (`S_SUPPKEY`) /*T![clustered_index] CLUSTERED */) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
|
|
CREATE TABLE `nation` (`N_NATIONKEY` bigint(20) NOT NULL,`N_NAME` char(25) NOT NULL,`N_REGIONKEY` bigint(20) NOT NULL,`N_COMMENT` varchar(152) DEFAULT NULL,PRIMARY KEY (`N_NATIONKEY`) /*T![clustered_index] CLUSTERED */) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
|
|
set @@tidb_mem_quota_query=128;
|
|
explain select ps_partkey, sum(ps_supplycost * ps_availqty) as value from partsupp, supplier, nation where ps_suppkey = s_suppkey and s_nationkey = n_nationkey and n_name = 'MOZAMBIQUE' group by ps_partkey having sum(ps_supplycost * ps_availqty) > ( select sum(ps_supplycost * ps_availqty) * 0.0001000000 from partsupp, supplier, nation where ps_suppkey = s_suppkey and s_nationkey = n_nationkey and n_name = 'MOZAMBIQUE' ) order by value desc;
|
|
Error 8175 (HY000): Your query has been cancelled due to exceeding the allowed memory limit for a single SQL query. Please try narrowing your query scope or increase the tidb_mem_quota_query limit and try again.[conn=<num>]
|
|
SET GLOBAL tidb_mem_oom_action = DEFAULT;
|
|
set @@tidb_mem_quota_query=default;
|
|
drop table if exists issue49369;
|
|
CREATE TABLE `issue49369` (
|
|
`x` varchar(32) COLLATE utf8mb4_bin DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
|
|
insert into t select round(cast('88888899999999999888888888888888888888888888888888888.11111111111111111111' as decimal(18,12)) * cast('88888899999999999888888888888888888888888888888888888.11111111111111111111' as decimal(42,18)) );
|
|
Error 1690 (22003): DECIMAL value is out of range in '(18, 12)'
|
|
set @@sql_mode = '';
|
|
insert into t select round(cast('88888899999999999888888888888888888888888888888888888.11111111111111111111' as decimal(18,12)) * cast('88888899999999999888888888888888888888888888888888888.11111111111111111111' as decimal(42,18)) );
|
|
show warnings;
|
|
Level Code Message
|
|
Warning 1690 DECIMAL value is out of range in '(18, 12)'
|
|
Warning 1690 DECIMAL value is out of range in '(42, 18)'
|
|
Warning 1690 %s value is out of range in '%s'
|
|
select * from t;
|
|
c
|
|
1
|
|
2
|
|
2147483647
|
|
set @@sql_mode = default;
|
|
set @@tidb_max_chunk_size = 32;
|
|
drop table if exists t, s;
|
|
CREATE TABLE `t` (`c` char(1)) COLLATE=utf8_general_ci ;
|
|
insert into t values("V"),("v");
|
|
insert into t values("V"),("v"),("v");
|
|
CREATE TABLE `s` (`col_61` int);
|
|
insert into s values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1);
|
|
insert into s values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1);
|
|
SELECT /*+ stream_agg()*/ count(`t`.`c`) FROM (`s`) JOIN `t` GROUP BY `t`.`c`;
|
|
count(`t`.`c`)
|
|
170
|
|
SELECT count(`t`.`c`) FROM (`s`) JOIN `t` GROUP BY `t`.`c`;
|
|
count(`t`.`c`)
|
|
170
|
|
set @@tidb_max_chunk_size = default;
|
|
select tan(9021874879467600608071521900001091070693729763119983979);
|
|
tan(9021874879467600608071521900001091070693729763119983979)
|
|
8.068627196084492
|
|
drop table if exists t;
|
|
create table t (id int auto_increment, c char(120), primary key(id));
|
|
create table pt (id int primary key auto_increment, val int) partition by range (id)
|
|
(PARTITION p1 VALUES LESS THAN (100),
|
|
PARTITION p2 VALUES LESS THAN (200),
|
|
PARTITION p3 VALUES LESS THAN (300),
|
|
PARTITION p4 VALUES LESS THAN (400),
|
|
PARTITION p5 VALUES LESS THAN (500),
|
|
PARTITION p6 VALUES LESS THAN (600),
|
|
PARTITION p7 VALUES LESS THAN (700));
|
|
insert into t (c) values ('abc'), ('def'), ('ghi'), ('jkl');
|
|
insert into t (c) select (c) from t;
|
|
insert into t (c) select (c) from t;
|
|
insert into t (c) select (c) from t;
|
|
insert into t (c) select (c) from t;
|
|
insert into t (c) select (c) from t;
|
|
insert into t (c) select (c) from t;
|
|
split table t between (0) and (40960) regions 30;
|
|
TOTAL_SPLIT_REGION SCATTER_FINISH_RATIO
|
|
29 1
|
|
analyze table t;
|
|
insert into pt (val) values (123),(456),(789),(1112);
|
|
insert into pt (val) select (val) from pt;
|
|
insert into pt (val) select (val) from pt;
|
|
insert into pt (val) select (val) from pt;
|
|
insert into pt (val) select (val) from pt;
|
|
insert into pt (val) select (val) from pt;
|
|
insert into pt (val) select (val) from pt;
|
|
split table pt between (0) and (40960) regions 30;
|
|
TOTAL_SPLIT_REGION SCATTER_FINISH_RATIO
|
|
203 1
|
|
analyze table pt all columns;
|
|
set @@tidb_distsql_scan_concurrency = default;
|
|
explain analyze select * from t order by id;
|
|
id estRows actRows task access object execution info operator info memory disk
|
|
TableReader_13 256.00 <actRows> root NULL max_distsql_concurrency: 2 NULL <memory> <disk>
|
|
└─TableFullScan_12 256.00 <actRows> cop[tikv] table:t NULL keep order:true <memory> <disk>
|
|
explain analyze select * from t limit 100;
|
|
id estRows actRows task access object execution info operator info memory disk
|
|
Limit_8 100.00 <actRows> root NULL NULL offset:0, count:100 <memory> <disk>
|
|
└─TableReader_14 100.00 <actRows> root NULL max_distsql_concurrency: 1 NULL <memory> <disk>
|
|
└─Limit_13 100.00 <actRows> cop[tikv] NULL NULL offset:0, count:100 <memory> <disk>
|
|
└─TableFullScan_12 100.00 <actRows> cop[tikv] table:t NULL keep order:false <memory> <disk>
|
|
explain analyze select * from t limit 100000;
|
|
id estRows actRows task access object execution info operator info memory disk
|
|
Limit_8 256.00 <actRows> root NULL NULL offset:0, count:100000 <memory> <disk>
|
|
└─TableReader_14 256.00 <actRows> root NULL max_distsql_concurrency: 15 NULL <memory> <disk>
|
|
└─Limit_13 256.00 <actRows> cop[tikv] NULL NULL offset:0, count:100000 <memory> <disk>
|
|
└─TableFullScan_12 256.00 <actRows> cop[tikv] table:t NULL keep order:false <memory> <disk>
|
|
explain analyze select * from t where c = 'abc' limit 100;
|
|
id estRows actRows task access object execution info operator info memory disk
|
|
Limit_9 64.00 <actRows> root NULL NULL offset:0, count:100 <memory> <disk>
|
|
└─TableReader_16 64.00 <actRows> root NULL max_distsql_concurrency: 15 NULL <memory> <disk>
|
|
└─Limit_15 64.00 <actRows> cop[tikv] NULL NULL offset:0, count:100 <memory> <disk>
|
|
└─Selection_14 64.00 <actRows> cop[tikv] NULL NULL eq(executor__issues.t.c, "abc") <memory> <disk>
|
|
└─TableFullScan_13 256.00 <actRows> cop[tikv] table:t NULL keep order:false <memory> <disk>
|
|
explain analyze select * from t where c = 'abc' limit 100000;
|
|
id estRows actRows task access object execution info operator info memory disk
|
|
Limit_9 64.00 <actRows> root NULL NULL offset:0, count:100000 <memory> <disk>
|
|
└─TableReader_16 64.00 <actRows> root NULL max_distsql_concurrency: 15 NULL <memory> <disk>
|
|
└─Limit_15 64.00 <actRows> cop[tikv] NULL NULL offset:0, count:100000 <memory> <disk>
|
|
└─Selection_14 64.00 <actRows> cop[tikv] NULL NULL eq(executor__issues.t.c, "abc") <memory> <disk>
|
|
└─TableFullScan_13 256.00 <actRows> cop[tikv] table:t NULL keep order:false <memory> <disk>
|
|
explain analyze select * from t order by id limit 100;
|
|
id estRows actRows task access object execution info operator info memory disk
|
|
Limit_12 100.00 <actRows> root NULL NULL offset:0, count:100 <memory> <disk>
|
|
└─TableReader_22 100.00 <actRows> root NULL max_distsql_concurrency: 1 NULL <memory> <disk>
|
|
└─Limit_21 100.00 <actRows> cop[tikv] NULL NULL offset:0, count:100 <memory> <disk>
|
|
└─TableFullScan_20 101.56 <actRows> cop[tikv] table:t NULL keep order:true <memory> <disk>
|
|
explain analyze select * from t order by id limit 100000;
|
|
id estRows actRows task access object execution info operator info memory disk
|
|
Limit_12 256.00 <actRows> root NULL NULL offset:0, count:100000 <memory> <disk>
|
|
└─TableReader_22 256.00 <actRows> root NULL max_distsql_concurrency: 15 NULL <memory> <disk>
|
|
└─Limit_21 256.00 <actRows> cop[tikv] NULL NULL offset:0, count:100000 <memory> <disk>
|
|
└─TableFullScan_20 256.00 <actRows> cop[tikv] table:t NULL keep order:true <memory> <disk>
|
|
explain analyze select * from t where c = 'abd' order by id limit 100;
|
|
id estRows actRows task access object execution info operator info memory disk
|
|
Limit_13 1.00 <actRows> root NULL NULL offset:0, count:100 <memory> <disk>
|
|
└─TableReader_26 1.00 <actRows> root NULL max_distsql_concurrency: 15 NULL <memory> <disk>
|
|
└─Limit_25 1.00 <actRows> cop[tikv] NULL NULL offset:0, count:100 <memory> <disk>
|
|
└─Selection_24 1.00 <actRows> cop[tikv] NULL NULL eq(executor__issues.t.c, "abd") <memory> <disk>
|
|
└─TableFullScan_23 256.00 <actRows> cop[tikv] table:t NULL keep order:true <memory> <disk>
|
|
select @@tidb_partition_prune_mode;
|
|
@@tidb_partition_prune_mode
|
|
dynamic
|
|
explain analyze select * from pt order by id;
|
|
id estRows actRows task access object execution info operator info memory disk
|
|
TableReader_13 256.00 <actRows> root partition:all max_distsql_concurrency: 2 NULL <memory> <disk>
|
|
└─TableFullScan_12 256.00 <actRows> cop[tikv] table:pt NULL keep order:true <memory> <disk>
|
|
explain analyze select * from pt limit 100;
|
|
id estRows actRows task access object execution info operator info memory disk
|
|
Limit_8 100.00 <actRows> root NULL NULL offset:0, count:100 <memory> <disk>
|
|
└─TableReader_14 100.00 <actRows> root partition:all max_distsql_concurrency: 7 NULL <memory> <disk>
|
|
└─Limit_13 100.00 <actRows> cop[tikv] NULL NULL offset:0, count:100 <memory> <disk>
|
|
└─TableFullScan_12 100.00 <actRows> cop[tikv] table:pt NULL keep order:false <memory> <disk>
|
|
explain analyze select * from pt limit 100000;
|
|
id estRows actRows task access object execution info operator info memory disk
|
|
Limit_8 256.00 <actRows> root NULL NULL offset:0, count:100000 <memory> <disk>
|
|
└─TableReader_14 256.00 <actRows> root partition:all max_distsql_concurrency: 15 NULL <memory> <disk>
|
|
└─Limit_13 256.00 <actRows> cop[tikv] NULL NULL offset:0, count:100000 <memory> <disk>
|
|
└─TableFullScan_12 256.00 <actRows> cop[tikv] table:pt NULL keep order:false <memory> <disk>
|
|
explain analyze select * from pt where val = 125 limit 100;
|
|
id estRows actRows task access object execution info operator info memory disk
|
|
Limit_9 1.00 <actRows> root NULL NULL offset:0, count:100 <memory> <disk>
|
|
└─TableReader_16 1.00 <actRows> root partition:all max_distsql_concurrency: 15 NULL <memory> <disk>
|
|
└─Limit_15 1.00 <actRows> cop[tikv] NULL NULL offset:0, count:100 <memory> <disk>
|
|
└─Selection_14 1.00 <actRows> cop[tikv] NULL NULL eq(executor__issues.pt.val, 125) <memory> <disk>
|
|
└─TableFullScan_13 256.00 <actRows> cop[tikv] table:pt NULL keep order:false <memory> <disk>
|
|
explain analyze select * from pt where val = 125 limit 100000;
|
|
id estRows actRows task access object execution info operator info memory disk
|
|
Limit_9 1.00 <actRows> root NULL NULL offset:0, count:100000 <memory> <disk>
|
|
└─TableReader_16 1.00 <actRows> root partition:all max_distsql_concurrency: 15 NULL <memory> <disk>
|
|
└─Limit_15 1.00 <actRows> cop[tikv] NULL NULL offset:0, count:100000 <memory> <disk>
|
|
└─Selection_14 1.00 <actRows> cop[tikv] NULL NULL eq(executor__issues.pt.val, 125) <memory> <disk>
|
|
└─TableFullScan_13 256.00 <actRows> cop[tikv] table:pt NULL keep order:false <memory> <disk>
|
|
explain analyze select * from pt order by id limit 100;
|
|
id estRows actRows task access object execution info operator info memory disk
|
|
Limit_12 100.00 <actRows> root NULL NULL offset:0, count:100 <memory> <disk>
|
|
└─TableReader_22 100.00 <actRows> root partition:all max_distsql_concurrency: 1 NULL <memory> <disk>
|
|
└─Limit_21 100.00 <actRows> cop[tikv] NULL NULL offset:0, count:100 <memory> <disk>
|
|
└─TableFullScan_20 101.56 <actRows> cop[tikv] table:pt NULL keep order:true <memory> <disk>
|
|
explain analyze select * from pt order by id limit 100000;
|
|
id estRows actRows task access object execution info operator info memory disk
|
|
Limit_12 256.00 <actRows> root NULL NULL offset:0, count:100000 <memory> <disk>
|
|
└─TableReader_22 256.00 <actRows> root partition:all max_distsql_concurrency: 15 NULL <memory> <disk>
|
|
└─Limit_21 256.00 <actRows> cop[tikv] NULL NULL offset:0, count:100000 <memory> <disk>
|
|
└─TableFullScan_20 256.00 <actRows> cop[tikv] table:pt NULL keep order:true <memory> <disk>
|
|
explain analyze select * from pt where val = 126 order by id limit 100;
|
|
id estRows actRows task access object execution info operator info memory disk
|
|
Limit_13 1.00 <actRows> root NULL NULL offset:0, count:100 <memory> <disk>
|
|
└─TableReader_26 1.00 <actRows> root partition:all max_distsql_concurrency: 15 NULL <memory> <disk>
|
|
└─Limit_25 1.00 <actRows> cop[tikv] NULL NULL offset:0, count:100 <memory> <disk>
|
|
└─Selection_24 1.00 <actRows> cop[tikv] NULL NULL eq(executor__issues.pt.val, 126) <memory> <disk>
|
|
└─TableFullScan_23 256.00 <actRows> cop[tikv] table:pt NULL keep order:true <memory> <disk>
|
|
explain analyze select /*+ set_var(tidb_distsql_scan_concurrency=5)*/ * from t order by id;
|
|
id estRows actRows task access object execution info operator info memory disk
|
|
TableReader_13 256.00 <actRows> root NULL max_distsql_concurrency: 5 NULL <memory> <disk>
|
|
└─TableFullScan_12 256.00 <actRows> cop[tikv] table:t NULL keep order:true <memory> <disk>
|
|
explain analyze select /*+ set_var(tidb_distsql_scan_concurrency=15)*/ * from t order by id;
|
|
id estRows actRows task access object execution info operator info memory disk
|
|
TableReader_13 256.00 <actRows> root NULL max_distsql_concurrency: 2 NULL <memory> <disk>
|
|
└─TableFullScan_12 256.00 <actRows> cop[tikv] table:t NULL keep order:true <memory> <disk>
|
|
CREATE TABLE test_55837 (col1 int(4) NOT NULL, col2 bigint(4) NOT NULL, KEY col2_index (col2));
|
|
insert into test_55837 values(0,1725292800),(0,1725292800);
|
|
select from_unixtime( if(col2 >9999999999, col2/1000, col2), '%Y-%m-%d %H:%i:%s') as result from test_55837;
|
|
result
|
|
2024-09-03 00:00:00
|
|
2024-09-03 00:00:00
|
|
select ((exists (select 1)) * -5) as c1;
|
|
c1
|
|
-5
|
|
SELECT IS_UUID(' 6ccd780c-baba-1026-8567-4cc3505b2a62 ');
|
|
IS_UUID(' 6ccd780c-baba-1026-8567-4cc3505b2a62 ')
|
|
0
|
|
SELECT UUID_TO_BIN(' 6ccd780c-baba-1026-9564-5b8c656024db ');
|
|
Error 1411 (HY000): Incorrect string value: ' 6ccd780c-baba-1026-9564-5b8c656024db ' for function uuid_to_bin
|
|
SELECT is_ipv4_mapped(NULL);
|
|
is_ipv4_mapped(NULL)
|
|
NULL
|
|
SELECT IS_IPV4(NULL);
|
|
IS_IPV4(NULL)
|
|
NULL
|
|
SELECT IS_IPV4(IF(1, NULL, '127.0.0.1'));
|
|
IS_IPV4(IF(1, NULL, '127.0.0.1'))
|
|
NULL
|
|
SELECT IS_IPV4_COMPAT(NULL);
|
|
IS_IPV4_COMPAT(NULL)
|
|
NULL
|
|
SELECT IS_IPV6(NULL + INTERVAL 1 DAY);
|
|
IS_IPV6(NULL + INTERVAL 1 DAY)
|
|
NULL
|
|
CREATE TABLE test_62458 (c1 INT, c2 VARCHAR(255)) PARTITION BY LIST (c1) (PARTITION p1 VALUES IN (1, 2, 3), PARTITION p2 VALUES IN (4, 5, 6));
|
|
CREATE UNIQUE INDEX i ON test_62458 (c1);
|
|
DELETE FROM test_62458 PARTITION (p1) WHERE c1 = 15;
|
|
CREATE TABLE test_62457 (c INT) PARTITION BY RANGE (c) (PARTITION p VALUES LESS THAN (10));
|
|
ALTER TABLE test_62457 ADD COLUMN c2 INT AS (JSON_EXTRACT(c, '$.number')) VIRTUAL;
|
|
Error 3146 (22032): Invalid data type for JSON data in argument 1 to function json_extract; a JSON string or JSON type is required.
|