801 lines
36 KiB
Plaintext
801 lines
36 KiB
Plaintext
# TestIssue23993
|
|
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;
|
|
select a from t_issue_23993 where cast(a as time);
|
|
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;
|
|
select a from t_issue_23993 where cast(a as time);
|
|
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;
|
|
select a from t_issue_23993 where cast(a as time);
|
|
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;
|
|
select a from t_issue_23993 where cast(a as time);
|
|
|
|
# TestIssue22201
|
|
--enable_warnings
|
|
SELECT HEX(WEIGHT_STRING('ab' AS BINARY(1000000000000000000)));
|
|
SELECT HEX(WEIGHT_STRING('ab' AS char(1000000000000000000)));
|
|
--disable_warnings
|
|
|
|
# TestIssue22941
|
|
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';
|
|
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';
|
|
|
|
# TestIssue23609
|
|
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;
|
|
select * from t1 x cross join t1 y on x.a>y.b order by x.a, x.b, y.a, y.b;
|
|
select * from t1 where a = b;
|
|
# to check warning count
|
|
--enable_warnings
|
|
select * from t1 where a < b;
|
|
--disable_warnings
|
|
|
|
# TestIssue24091
|
|
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;
|
|
select * from t;
|
|
|
|
# TestIssue26348
|
|
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;
|
|
select cast(a as double) * cast(c as double) *cast(d/36000 as double) from t;
|
|
select 20210606*50000.00*(5.04600000/36000);
|
|
# differs from MySQL cause constant-fold
|
|
select "20210606"*50000.00*(5.04600000/36000);
|
|
select cast("20210606" as double)*50000.00*(5.04600000/36000);
|
|
|
|
# TestIssue25447
|
|
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;
|
|
|
|
# TestIssue23602
|
|
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;
|
|
EXPLAIN FORMAT = 'plan_tree' SELECT a FROM t WHERE a BETWEEN 0x1 AND 0x2;
|
|
SELECT a FROM t WHERE a BETWEEN 0xFFFFFFFFFFFFFFF5 AND X'FFFFFFFFFFFFFFFA';
|
|
|
|
# TestIssue28935
|
|
set @@tidb_enable_vectorized_expression=true;
|
|
select trim(leading from " a "), trim(both from " a "), trim(trailing from " a ");
|
|
select trim(leading null from " a "), trim(both null from " a "), trim(trailing null from " a ");
|
|
select trim(null from " a ");
|
|
set @@tidb_enable_vectorized_expression=false;
|
|
select trim(leading from " a "), trim(both from " a "), trim(trailing from " a ");
|
|
select trim(leading null from " a "), trim(both null from " a "), trim(trailing null from " a ");
|
|
select trim(null from " a ");
|
|
set tidb_enable_vectorized_expression=default;
|
|
|
|
# TestIssue29412
|
|
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));
|
|
|
|
# TestIssue12201
|
|
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;
|
|
select * from e where case 1 when 1 then e end;
|
|
select * from e where case e when 1 then e end;
|
|
select * from e where case 1 when e then e end;
|
|
|
|
# TestIssue21451
|
|
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;
|
|
select min(en) from t;
|
|
select * from t order by en;
|
|
drop table t;
|
|
create table t(s set('c', 'b', 'a'));
|
|
insert into t values ('a'), ('b'), ('c');
|
|
select max(s) from t;
|
|
select min(s) from t;
|
|
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;
|
|
select id, min(en) from t where id=1 group by id;
|
|
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;
|
|
select id, min(s) from t where id=1 group by id;
|
|
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;
|
|
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;
|
|
drop table t;
|
|
|
|
# TestIssue15563
|
|
select distinct 0.7544678906163867 / 0.68234634;
|
|
|
|
# TestIssue22231
|
|
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');
|
|
--enable_warnings
|
|
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';
|
|
select cast('2020-10-22 10:31-10:12' as datetime);
|
|
select cast('2020-05-28 23:59:59 00:00:00' as datetime);
|
|
SELECT CAST("1111111111-" AS DATE);
|
|
--disable_warnings
|
|
|
|
# TestIssue2612
|
|
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;
|
|
|
|
# TestIssue345
|
|
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;
|
|
SELECT * FROM t2;
|
|
update t1 as a, t2 as t1 set a.c1 = 1, t1.c2 = 2;
|
|
SELECT * FROM t1;
|
|
SELECT * FROM t2;
|
|
--error 1054
|
|
update t1 as a, t2 set t1.c1 = 10;
|
|
|
|
# TestIssue5055
|
|
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;
|
|
|
|
# TestIssue4024
|
|
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;
|
|
select * from executor__issues2.t;
|
|
update executor__issues.t, executor__issues2.t set executor__issues.t.a=3;
|
|
select * from t;
|
|
select * from executor__issues2.t;
|
|
drop database executor__issues2;
|
|
|
|
# TestIssue5666
|
|
set @@profiling=1;
|
|
SELECT QUERY_ID, SUM(DURATION) AS SUM_DURATION FROM INFORMATION_SCHEMA.PROFILING GROUP BY QUERY_ID;
|
|
|
|
# TestIssue5341
|
|
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;
|
|
|
|
# TestIssue16921
|
|
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`;
|
|
select `a` from `t` ignore index (a) where !`a`;
|
|
select `a` from `t` use index (a) where `a`;
|
|
select `a` from `t` ignore index (a) where `a`;
|
|
select a from t use index (a) where not a is true;
|
|
select a from t use index (a) where not not a is true;
|
|
select a from t use index (a) where not not a;
|
|
select a from t use index (a) where not not not a is true;
|
|
select a from t use index (a) where not not not a;
|
|
|
|
# TestIssue19100
|
|
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;
|
|
select count(*) from t2 where not c;
|
|
select count(*) from t1 where c;
|
|
select count(*) from t2 where c;
|
|
|
|
# TestIssue27232
|
|
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);
|
|
select * from t where timestampadd(hour, 1, a ) = timestampadd(hour, 1, "2038-01-19 03:14:07");
|
|
|
|
# TestIssue15718
|
|
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;
|
|
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;
|
|
select b or c as d, b, c from tt order by d limit 1;
|
|
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);
|
|
|
|
# TestIssue15767
|
|
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;
|
|
|
|
# TestIssue16025
|
|
drop table if exists t0;
|
|
CREATE TABLE t0(c0 NUMERIC PRIMARY KEY);
|
|
INSERT IGNORE INTO t0(c0) VALUES (NULL);
|
|
SELECT * FROM t0 WHERE c0;
|
|
|
|
# TestIssue16854
|
|
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;
|
|
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;
|
|
set @@tidb_max_chunk_size=default;
|
|
|
|
# TestIssue20305
|
|
drop table if exists t2;
|
|
create table t2 (a year(4));
|
|
insert into t2 values(69);
|
|
select * from t2 where a <= 69;
|
|
# the following test is a regression test that matches MySQL's behavior.
|
|
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;
|
|
|
|
# TestIssue22817
|
|
drop table if exists t3;
|
|
create table t3 (a year);
|
|
insert into t3 values (1991), ("1992"), ("93"), (94);
|
|
select * from t3 where a >= NULL;
|
|
|
|
# TestIssue13953
|
|
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;
|
|
|
|
# TestIssue17780
|
|
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;
|
|
|
|
# TestIssue9918
|
|
drop table if exists t;
|
|
create table t (a year);
|
|
insert into t values(0);
|
|
select cast(a as char) from t;
|
|
|
|
# TestIssue13004
|
|
# see https://dev.mysql.com/doc/refman/5.6/en/date-and-time-literals.html, timestamp here actually produces a datetime
|
|
SELECT TIMESTAMP '9999-01-01 00:00:00';
|
|
|
|
# TestIssue12178
|
|
drop table if exists ta;
|
|
create table ta(id decimal(60,2));
|
|
insert into ta values (JSON_EXTRACT('{"c": "1234567890123456789012345678901234567890123456789012345"}', '$.c'));
|
|
select * from ta;
|
|
|
|
# TestIssue11883
|
|
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");
|
|
select f1 from t1 where json_extract(f1, '$') = 'asd';
|
|
# MySQL produces empty row for the following SQL, I doubt it should be MySQL's bug.
|
|
select f1 from t1 where case json_extract(f1,"$") when "asd" then 1 else 0 end;
|
|
delete from t1;
|
|
insert into t1 values ('{"a": 1}');
|
|
# the first value in the tuple should be interpreted as string instead of JSON, so no row will be returned
|
|
select f1 from t1 where f1 in ('{"a": 1}', 'asdf', 'asdf');
|
|
# and if we explicitly cast it into a JSON value, the check will pass
|
|
select f1 from t1 where f1 in (cast('{"a": 1}' as JSON), 'asdf', 'asdf');
|
|
select json_extract('"asd"', '$') = 'asd';
|
|
select json_extract('"asd"', '$') <=> 'asd';
|
|
select json_extract('"asd"', '$') <> 'asd';
|
|
select json_extract('{"f": 1.0}', '$.f') = 1.0;
|
|
select json_extract('{"f": 1.0}', '$.f') = '1.0';
|
|
select json_extract('{"n": 1}', '$') = '{"n": 1}';
|
|
select json_extract('{"n": 1}', '$') <> '{"n": 1}';
|
|
|
|
# TestIssue15492
|
|
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;
|
|
|
|
# TestIssue982
|
|
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;
|
|
|
|
# TestIssue24627
|
|
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;
|
|
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;
|
|
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;
|
|
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;
|
|
drop table test2;
|
|
|
|
# TestIssue39618
|
|
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;
|
|
|
|
# TestIssue40158
|
|
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;
|
|
|
|
# TestIssue40596
|
|
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' );
|
|
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' );
|
|
set sql_mode=default;
|
|
set tidb_partition_prune_mode=default;
|
|
|
|
# TestIssue41778
|
|
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);
|
|
-- error 1815
|
|
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) ) ) ) ) );
|
|
|
|
# TestIssue15662
|
|
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;
|
|
|
|
# TestIssue30382
|
|
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;
|
|
--sorted_result
|
|
select * from t1 where c_str <> any (select c_str from t2 where c_decimal < 5) for update;
|
|
explain format = 'plan_tree' select * from t1 where c_str <> any (select c_str from t2 where c_decimal < 5) for update;
|
|
commit;
|
|
set tidb_cost_model_version=default;
|
|
|
|
# TestFix31537
|
|
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 */
|
|
);
|
|
--disable_result_log
|
|
--enable_result_log
|
|
set @@foreign_key_checks=default;
|
|
|
|
# TestIssue48007
|
|
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;
|
|
-- replace_regex /conn=[-0-9]+/conn=<num>/
|
|
-- error 8175
|
|
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;
|
|
SET GLOBAL tidb_mem_oom_action = DEFAULT;
|
|
set @@tidb_mem_quota_query=default;
|
|
|
|
|
|
# TestIssue49369
|
|
drop table if exists issue49369;
|
|
CREATE TABLE `issue49369` (
|
|
`x` varchar(32) COLLATE utf8mb4_bin DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
|
|
--error 1690
|
|
insert into t select round(cast('88888899999999999888888888888888888888888888888888888.11111111111111111111' as decimal(18,12)) * cast('88888899999999999888888888888888888888888888888888888.11111111111111111111' as decimal(42,18)) );
|
|
set @@sql_mode = '';
|
|
insert into t select round(cast('88888899999999999888888888888888888888888888888888888.11111111111111111111' as decimal(18,12)) * cast('88888899999999999888888888888888888888888888888888888.11111111111111111111' as decimal(42,18)) );
|
|
show warnings;
|
|
select * from t;
|
|
set @@sql_mode = default;
|
|
|
|
# TestIssue49902
|
|
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`;
|
|
SELECT count(`t`.`c`) FROM (`s`) JOIN `t` GROUP BY `t`.`c`;
|
|
set @@tidb_max_chunk_size = default;
|
|
|
|
# TestIssue52672
|
|
select tan(9021874879467600608071521900001091070693729763119983979);
|
|
|
|
|
|
# TestIssue54969
|
|
# For order by query, the distsql concurrency should be @@tidb_distsql_scan_concurrency.
|
|
# For simple limit N query and N < 10000, the distsql concurrency should be 1 (normal table)
|
|
# For this case nn partition table, concurrency should be partition number.
|
|
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;
|
|
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;
|
|
analyze table pt all columns;
|
|
|
|
set @@tidb_distsql_scan_concurrency = default;
|
|
-- replace_column 8 <memory> 9 <disk> 3 <actRows>
|
|
-- replace_regex /.*max_distsql_concurrency: (?P<num>[0-9]+).*/max_distsql_concurrency: $num/ /tikv_task:.*// /time:.*// /data:.*//
|
|
explain analyze select * from t order by id; # expected distsql concurrency 2
|
|
|
|
-- replace_column 8 <memory> 9 <disk> 3 <actRows>
|
|
-- replace_regex /.*max_distsql_concurrency: (?P<num>[0-9]+).*/max_distsql_concurrency: $num/ /tikv_task:.*// /time:.*// /data:.*//
|
|
explain analyze select * from t limit 100; # expected distsql concurrency 1
|
|
|
|
-- replace_column 8 <memory> 9 <disk> 3 <actRows>
|
|
-- replace_regex /.*max_distsql_concurrency: (?P<num>[0-9]+).*/max_distsql_concurrency: $num/ /tikv_task:.*// /time:.*// /data:.*//
|
|
explain analyze select * from t limit 100000; # expected distsql concurrency 15
|
|
|
|
-- replace_column 8 <memory> 9 <disk> 3 <actRows>
|
|
-- replace_regex /.*max_distsql_concurrency: (?P<num>[0-9]+).*/max_distsql_concurrency: $num/ /tikv_task:.*// /time:.*// /data:.*//
|
|
explain analyze select * from t where c = 'abc' limit 100; # expected distsql concurrency 15
|
|
|
|
-- replace_column 8 <memory> 9 <disk> 3 <actRows>
|
|
-- replace_regex /.*max_distsql_concurrency: (?P<num>[0-9]+).*/max_distsql_concurrency: $num/ /tikv_task:.*// /time:.*// /data:.*//
|
|
explain analyze select * from t where c = 'abc' limit 100000; # expected distsql concurrency 15
|
|
|
|
-- replace_column 8 <memory> 9 <disk> 3 <actRows>
|
|
-- replace_regex /.*max_distsql_concurrency: (?P<num>[0-9]+).*/max_distsql_concurrency: $num/ /tikv_task:.*// /time:.*// /data:.*//
|
|
explain analyze select * from t order by id limit 100; # expected distsql concurrency 1
|
|
|
|
-- replace_column 8 <memory> 9 <disk> 3 <actRows>
|
|
-- replace_regex /.*max_distsql_concurrency: (?P<num>[0-9]+).*/max_distsql_concurrency: $num/ /tikv_task:.*// /time:.*// /data:.*//
|
|
explain analyze select * from t order by id limit 100000; # expected distsql concurrency 15
|
|
|
|
-- replace_column 8 <memory> 9 <disk> 3 <actRows>
|
|
-- replace_regex /.*max_distsql_concurrency: (?P<num>[0-9]+).*/max_distsql_concurrency: $num/ /tikv_task:.*// /time:.*// /data:.*//
|
|
explain analyze select * from t where c = 'abd' order by id limit 100;
|
|
select @@tidb_partition_prune_mode;
|
|
|
|
-- replace_column 8 <memory> 9 <disk> 3 <actRows>
|
|
-- replace_regex /.*max_distsql_concurrency: (?P<num>[0-9]+).*/max_distsql_concurrency: $num/ /tikv_task:.*// /time:.*// /data:.*//
|
|
explain analyze select * from pt order by id; # expected distsql concurrency 2
|
|
|
|
-- replace_column 8 <memory> 9 <disk> 3 <actRows>
|
|
-- replace_regex /.*max_distsql_concurrency: (?P<num>[0-9]+).*/max_distsql_concurrency: $num/ /tikv_task:.*// /time:.*// /data:.*//
|
|
explain analyze select * from pt limit 100; # expected distsql concurrency 7
|
|
|
|
-- replace_column 8 <memory> 9 <disk> 3 <actRows>
|
|
-- replace_regex /.*max_distsql_concurrency: (?P<num>[0-9]+).*/max_distsql_concurrency: $num/ /tikv_task:.*// /time:.*// /data:.*//
|
|
explain analyze select * from pt limit 100000; # expected distsql concurrency 15
|
|
|
|
-- replace_column 8 <memory> 9 <disk> 3 <actRows>
|
|
-- replace_regex /.*max_distsql_concurrency: (?P<num>[0-9]+).*/max_distsql_concurrency: $num/ /tikv_task:.*// /time:.*// /data:.*//
|
|
explain analyze select * from pt where val = 125 limit 100; # expected distsql concurrency 15
|
|
|
|
-- replace_column 8 <memory> 9 <disk> 3 <actRows>
|
|
-- replace_regex /.*max_distsql_concurrency: (?P<num>[0-9]+).*/max_distsql_concurrency: $num/ /tikv_task:.*// /time:.*// /data:.*//
|
|
explain analyze select * from pt where val = 125 limit 100000; # expected distsql concurrency 15
|
|
|
|
-- replace_column 8 <memory> 9 <disk> 3 <actRows>
|
|
-- replace_regex /.*max_distsql_concurrency: (?P<num>[0-9]+).*/max_distsql_concurrency: $num/ /tikv_task:.*// /time:.*// /data:.*//
|
|
explain analyze select * from pt order by id limit 100; # expected distsql concurrency 7, but currently get 1, see issue #55190
|
|
|
|
-- replace_column 8 <memory> 9 <disk> 3 <actRows>
|
|
-- replace_regex /.*max_distsql_concurrency: (?P<num>[0-9]+).*/max_distsql_concurrency: $num/ /tikv_task:.*// /time:.*// /data:.*//
|
|
explain analyze select * from pt order by id limit 100000; # expected distsql concurrency 15
|
|
|
|
-- replace_column 8 <memory> 9 <disk> 3 <actRows>
|
|
-- replace_regex /.*max_distsql_concurrency: (?P<num>[0-9]+).*/max_distsql_concurrency: $num/ /tikv_task:.*// /time:.*// /data:.*//
|
|
explain analyze select * from pt where val = 126 order by id limit 100; # expected distsql concurrency 15
|
|
|
|
-- replace_column 8 <memory> 9 <disk> 3 <actRows>
|
|
-- replace_regex /.*max_distsql_concurrency: (?P<num>[0-9]+).*/max_distsql_concurrency: $num/ /tikv_task:.*// /time:.*// /data:.*//
|
|
explain analyze select /*+ set_var(tidb_distsql_scan_concurrency=5)*/ * from t order by id; # expected distsql concurrency 5
|
|
|
|
-- replace_column 8 <memory> 9 <disk> 3 <actRows>
|
|
-- replace_regex /.*max_distsql_concurrency: (?P<num>[0-9]+).*/max_distsql_concurrency: $num/ /tikv_task:.*// /time:.*// /data:.*//
|
|
explain analyze select /*+ set_var(tidb_distsql_scan_concurrency=15)*/ * from t order by id; # expected distsql concurrency 2
|
|
|
|
|
|
# TestIssue55837
|
|
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;
|
|
|
|
# TestIssue56641
|
|
select ((exists (select 1)) * -5) as c1;
|
|
|
|
# TestIssue59457
|
|
SELECT IS_UUID(' 6ccd780c-baba-1026-8567-4cc3505b2a62 ');
|
|
-- error 1411
|
|
SELECT UUID_TO_BIN(' 6ccd780c-baba-1026-9564-5b8c656024db ');
|
|
# TestIssue59456
|
|
SELECT is_ipv4_mapped(NULL);
|
|
SELECT IS_IPV4(NULL);
|
|
SELECT IS_IPV4(IF(1, NULL, '127.0.0.1'));
|
|
SELECT IS_IPV4_COMPAT(NULL);
|
|
SELECT IS_IPV6(NULL + INTERVAL 1 DAY);
|
|
|
|
# TestIssue62458 should no error
|
|
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;
|
|
# TestIssue62457 should raise error
|
|
CREATE TABLE test_62457 (c INT) PARTITION BY RANGE (c) (PARTITION p VALUES LESS THAN (10));
|
|
-- error 3146
|
|
ALTER TABLE test_62457 ADD COLUMN c2 INT AS (JSON_EXTRACT(c, '$.number')) VIRTUAL;
|