diff options
Diffstat (limited to 'mysql-test/main/derived_split_innodb.result')
| -rw-r--r-- | mysql-test/main/derived_split_innodb.result | 88 |
1 files changed, 88 insertions, 0 deletions
diff --git a/mysql-test/main/derived_split_innodb.result b/mysql-test/main/derived_split_innodb.result index 7ea3b689f23..fcdba0d7d07 100644 --- a/mysql-test/main/derived_split_innodb.result +++ b/mysql-test/main/derived_split_innodb.result @@ -234,4 +234,92 @@ id itemid id id 4 2 4 2 drop table t1,t2,t3; set optimizer_switch='split_materialized=default'; +# +# MDEV-26337: subquery with groupby and ROLLUP returns incorrect results on LEFT JOIN on INDEXED values +# +CREATE TABLE t1 ( +the_date date NOT NULL +, PRIMARY KEY ( the_date ) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; +INSERT INTO t1 VALUES ('2021-08-10'),('2021-08-11'),('2021-08-12'),('2021-08-13'); +CREATE TABLE t2 ( +the_date date NOT NULL, +ptn_id char(5) CHARACTER SET utf8mb3 NOT NULL DEFAULT '', +cco_stk_ttl int, +PRIMARY KEY ( the_date , ptn_id ) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; +INSERT INTO t2 VALUES +('2021-08-11','10002',NULL),('2021-08-11','10741',128), +('2021-08-11','11001',4),('2021-08-11','11003',2048), +('2021-08-12','10001',4096),('2021-08-12','10002',1), +('2021-08-12','10429',256),('2021-08-12','10499',16), +('2021-08-12','10580',8),('2021-08-12','10740',32), +('2021-08-12','10741',64),('2021-08-12','10771',512), +('2021-08-12','11001',2),('2021-08-12','11003',1024); +CREATE TABLE t3 ( +id int NOT NULL AUTO_INCREMENT, +nsc_id char(5) NOT NULL, +dept_id char(4) NOT NULL, +district_id char(3) NOT NULL, +region_id char(2) NOT NULL, +PRIMARY KEY ( id ), +UNIQUE KEY dept_district ( dept_id , district_id ), +KEY region_id ( dept_id , region_id ) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; +INSERT INTO t3 VALUES +(1,'MMD','ADVB','10','1'), (2,'MMD','ADVB','11','1'), +(3,'MMD','ADVB','21','2'),(4,'MMD','ADVB','22','2'); +CREATE TABLE t4 ( +dept_id char(4) CHARACTER SET utf8mb3 NOT NULL, +ptn_id char(5) CHARACTER SET utf8mb3 NOT NULL, +district_id char(3) CHARACTER SET utf8mb3 NOT NULL DEFAULT '0', +nsc_id char(5) CHARACTER SET utf8mb3 NOT NULL +, PRIMARY KEY (ptn_id , dept_id) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; +INSERT INTO t4 VALUES +('ADVB','10001','10','MMD'),('ADVB','10002','10','MMD'), +('ADVB','10003','10','MMD'),('ADVB','10429','22','MMD'), +('ADVB','10740','21','MMD'),('ADVB','10741','21','MMD'), +('ADVB','10771','23','MMD'),('ADVB','11001','11','MMD'), +('ADVB','11002','11','MMD'); +CREATE TABLE `t10` ( +`the_date` date NOT NULL, +`dept_id` char(4) CHARACTER SET utf8mb4 , +`org_id` varchar(3) CHARACTER SET utf8mb4 , +`district_id` char(3) CHARACTER SET utf8mb4 , +`region_id` char(2) CHARACTER SET utf8mb4 +); +insert into t10 +SELECT cal.the_date , +org.dept_id , +coalesce(org.district_id, org.region_id, 'MMD') AS org_id , +org.district_id , +org.region_id +FROM t1 cal +CROSS JOIN t3 org +WHERE org.nsc_id = 'MMD' + AND org.dept_id IN ('ADVB') +AND cal.the_date = '2021-08-12' +GROUP BY cal.the_date, +org.dept_id, +org.region_id, +org.district_id WITH ROLLUP HAVING NOT (cal.the_date IS NULL +OR org.dept_id IS NULL); +explain $q; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY org2 ALL NULL NULL NULL NULL 7 +1 PRIMARY <derived2> ref key0 key0 43 test.org2.the_date,test.org2.dept_id,test.org2.region_id,test.org2.district_id 2 Using where +2 LATERAL DERIVED sub ref PRIMARY PRIMARY 3 test.org2.the_date 1 Using temporary; Using filesort +2 LATERAL DERIVED org ref PRIMARY PRIMARY 15 test.sub.ptn_id 1 Using where +2 LATERAL DERIVED dis eq_ref dept_district,region_id dept_district 28 const,func 1 Using index condition; Using where +$q; +the_date org_id dept_id cco_stk_ttl +2021-08-12 10 ADVB 4097 +2021-08-12 11 ADVB 2 +2021-08-12 1 ADVB 4099 +2021-08-12 21 ADVB 96 +2021-08-12 22 ADVB 256 +2021-08-12 2 ADVB 352 +2021-08-12 MMD ADVB 4451 +drop table t1,t2,t3,t4,t10; # End of 10.3 tests |