Details
Critical Description
Wrong result, duplicate group by values and wrong aggregation "row"-data (row 1,2 and 5,6):
CREATE OR REPLACE TABLE test_kpi_date ( |
the_date date NOT NULL PRIMARY KEY |
);
|
the_date org_type org_id dept_id cco_stk_ttl
|
2021-08-12 dis 10 ADVB 1 <- ?????
|
2021-08-12 dis 10 ADVB 4096 <- ?????
|
2021-08-12 dis 11 ADVB 2
|
2021-08-12 dis 12 ADVB <null>
|
2021-08-12 reg 1 ADVB 1 <- ?????
|
2021-08-12 reg 1 ADVB 4098 <- ?????
|
2021-08-12 dis 21 ADVB 96
|
2021-08-12 dis 22 ADVB 256
|
2021-08-12 dis 23 ADVB 512
|
2021-08-12 reg 2 ADVB 864
|
2021-08-12 nsc MMD ADVB 4963
|
generated with:
CREATE OR REPLACE TABLE test_kpi_date ( |
"id" int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, |
the_date date NOT NULL |
);
|
result looks good:
the_date org_type org_id dept_id cco_stk_ttl
|
2021-08-12 dis 10 ADVB 4097
|
2021-08-12 dis 11 ADVB 2
|
2021-08-12 dis 12 ADVB <null>
|
2021-08-12 reg 1 ADVB 4099
|
2021-08-12 dis 21 ADVB 96
|
2021-08-12 dis 22 ADVB 256
|
2021-08-12 dis 23 ADVB 512
|
2021-08-12 reg 2 ADVB 864
|
2021-08-12 nsc MMD ADVB 4963
|
It looks like MDEV-25714.
Test Data:
SET sql_mode = 'REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI,ONLY_FULL_GROUP_BY' |
|
|
CREATE OR REPLACE TABLE test_kpi_date ( |
the_date date NOT NULL |
, PRIMARY KEY ("the_date") |
);
|
|
|
SELECT * |
FROM test_kpi_date tkd |
|
|
INSERT INTO test_kpi_date VALUES (current_date), (current_date-1), (current_date-2), (current_date-3); |
|
|
CREATE OR REPLACE TABLE test_org_district ( |
"id" int(11) 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") |
);
|
|
|
INSERT INTO test_org_district (nsc_id,dept_id,district_id,region_id) VALUES |
('MMD','ADVB','10','1'), |
('MMD','ADVB','11','1'), |
('MMD','ADVB','21','2'), |
('MMD','ADVB','22','2'); |
|
|
CREATE OR REPLACE TABLE "test_org_partner" ( |
"dept_id" char(4) CHARACTER SET utf8 NOT NULL, |
"ptn_id" char(5) CHARACTER SET utf8, |
"district_id" char(3) CHARACTER SET utf8 NOT NULL DEFAULT '0', |
"nsc_id" char(5) CHARACTER SET utf8 NOT NULL, |
PRIMARY KEY ("ptn_id","dept_id"), |
KEY "dept_ptn" ("dept_id","ptn_id"), |
KEY "dept_district_ptn" ("dept_id","district_id","ptn_id") |
);
|
|
|
INSERT INTO test_org_partner (dept_id,ptn_id,district_id,nsc_id) VALUES |
('ADVB','10001','10','MMD'), |
('ADVB','10002','10','MMD'), |
('ADVB','10003','10','MMD'), |
('ADVB','11001','11','MMD'), |
('ADVB','11002','11','MMD'), |
('ADVB','10740','21','MMD'), |
('ADVB','10741','21','MMD'), |
('ADVB','10429','22','MMD'), |
('ADVB','10771','23','MMD'); |
|
CREATE OR REPLACE TABLE "test_kpi_measure" ( |
"the_date" date DEFAULT NULL, |
"ptn_id" char(5) CHARACTER SET utf8 NOT NULL DEFAULT '', |
"cco_stk_ttl" int(11) DEFAULT NULL, |
PRIMARY KEY ("the_date","ptn_id"), |
KEY "ptn_id_idx" ("ptn_id") |
);
|
|
|
INSERT INTO test_kpi_measure (the_date,ptn_id,cco_stk_ttl) VALUES |
(current_date-1,'10001',4096), |
(current_date-1,'10002',1), |
(current_date-2,'10002',null), |
(current_date-1,'11001',2), |
(current_date-2,'11001',4), |
(current_date-1,'11003',1024), |
(current_date-2,'11003',2048), |
(current_date-1,'10580',8), |
(current_date-1,'10499',16), |
(current_date-1,'10740',32), |
(current_date-1,'10741',64), |
(current_date-2,'10741',128), |
(current_date-1,'10429',256), |
(current_date-1,'10771',512); |
SQL with wrong result:
SELECT org.the_date |
, org.org_type
|
, org.org_id
|
, org.dept_id
|
, msr.`cco_stk_ttl`
|
FROM ( |
SELECT cal.the_date |
, org.dept_id
|
, case when org.region_id <=> null then 'nsc' |
when org.district_id <=> null then 'reg' |
else 'dis' |
end as org_type |
, coalesce(org.district_id, org.region_id, 'MMD') as org_id |
, org.district_id
|
, org.region_id
|
FROM test_kpi_date cal |
CROSS JOIN test_org_district org |
WHERE org.nsc_id = 'MMD' and org.dept_id IN ('ADVB') |
AND cal.the_date = CURRENT_DATE() - INTERVAL 1 DAY |
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) |
) org
|
LEFT JOIN ( |
SELECT sub.the_date |
, dis.dept_id
|
, dis.region_id
|
, dis.district_id
|
, SUM(sub.cco_stk_ttl) as cco_stk_ttl |
FROM test_kpi_measure sub |
JOIN test_org_partner org ON org.ptn_id = sub.ptn_id |
JOIN test_org_district dis ON dis.dept_id = org.dept_id AND dis.district_id = org.district_id |
WHERE dis.nsc_id = 'MMD' and dis.dept_id IN ('ADVB') |
-- AND sub.the_date = CURRENT_DATE() - INTERVAL 1 DAY
|
GROUP BY sub.the_date, dis.dept_id, dis.region_id, dis.district_id WITH ROLLUP |
) msr
|
ON msr.the_date = org.the_date |
and msr.dept_id <=> org.dept_id |
and msr.region_id <=> org.region_id |
and msr.district_id <=> org.district_id |
the result is correct if there is no index on the column the_date:
CREATE OR REPLACE TABLE test_kpi_date ( |
"id" int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, |
the_date date NOT NULL |
-- , PRIMARY KEY ("the_date")
|
-- , UNIQUE (the_date)
|
);
|
You can see, we try to use different definitions with the same behavior (wrong) when the_date get an index (PRIMARY/UNIQUE).
Without UNIQUE/PRIMARY Index on the_date it works fine.
Attachments
Issue Links
- relates to
-
MDEV-27496 Can Split Materialization work with WITH ROLLUP?
-
- Open
-
Activity
| Field | Original Value | New Value |
|---|---|---|
| Description |
A SQL returns invalid data if using field from "other" table:
Sample: {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-09 dis 90 ADVB 2021-08-09 dis 99 ADVB 2021-08-09 reg 9 ADVB 2021-08-09 nsc MMD ADVB 335 2021-08-09 nsc MMD ADVB 335 {code} correct would be: {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-09 dis 90 ADVB 2021-08-09 dis 99 ADVB 2021-08-09 reg 9 ADVB 2021-08-09 nsc MMD ADVB 335 {code} |
A SQL returns invalid data if using field from "other" table:
Sample: {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-09 dis 90 ADVB 2021-08-09 dis 99 ADVB 2021-08-09 reg 9 ADVB 2021-08-09 nsc MMD ADVB 335 2021-08-09 nsc MMD ADVB 335 {code} correct would be: {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-09 dis 90 ADVB 2021-08-09 dis 99 ADVB 2021-08-09 reg 9 ADVB 2021-08-09 nsc MMD ADVB 335 {code} It looks like |
| Description |
A SQL returns invalid data if using field from "other" table:
Sample: {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-09 dis 90 ADVB 2021-08-09 dis 99 ADVB 2021-08-09 reg 9 ADVB 2021-08-09 nsc MMD ADVB 335 2021-08-09 nsc MMD ADVB 335 {code} correct would be: {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-09 dis 90 ADVB 2021-08-09 dis 99 ADVB 2021-08-09 reg 9 ADVB 2021-08-09 nsc MMD ADVB 335 {code} It looks like |
A SQL returns invalid data if using field from "other" table:
Sample: {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-09 dis 90 ADVB 2021-08-09 dis 99 ADVB 2021-08-09 reg 9 ADVB 2021-08-09 nsc MMD ADVB 335 2021-08-09 nsc MMD ADVB 335 {code} correct would be: {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-09 dis 90 ADVB 2021-08-09 dis 99 ADVB 2021-08-09 reg 9 ADVB 2021-08-09 nsc MMD ADVB 335 {code} It looks like Test Data: {code:java} CREATE TABLE test_kpi_date (the_date date); INSERT INTO test_kpi_date VALUES (current_date), (current_date-1); -- cockpit.org_district definition CREATE TABLE test_org_district ( "nsc_id" char(5) NOT NULL, "dept_id" char(4) NOT NULL, "district_id" char(3) NOT NULL, "region_id" char(2) NOT NULL ); INSERT INTO test_org_district (nsc_id,dept_id,district_id,region_id) VALUES ('MMD','ADVB','11','1'), ('MMD','ADVB','12','1'), ('MMD','ADVB','13','1'), ('MMD','ADVB','14','1'), ('MMD','ADVB','15','1'), ('MMD','ADVB','16','1'), ('MMD','ADVB','17','1'), ('MMD','ADVB','18','1'), ('MMD','ADVB','10','1'), ('MMD','ADVB','21','2'); INSERT INTO test_org_district (nsc_id,dept_id,district_id,region_id) VALUES ('MMD','ADVB','23','2'), ('MMD','ADVB','24','2'), ('MMD','ADVB','25','2'), ('MMD','ADVB','22','2'), ('MMD','ADVB','26','2'), ('MMD','ADVB','27','2'), ('MMD','ADVB','28','2'), ('MMD','ADVB','99','9'), ('MMD','ADVB','90','9'); CREATE TABLE "test_org_partner" ( "dept_id" char(4) CHARACTER SET utf8 NOT NULL, "ptn_id" char(5) CHARACTER SET utf8, "district_id" char(3) CHARACTER SET utf8 NOT NULL DEFAULT '0', "nsc_id" char(5) CHARACTER SET utf8 NOT NULL ); INSERT INTO test_org_partner (dept_id,ptn_id,district_id,nsc_id) VALUES ('ADVB','10284','10','MMD'), ('ADVB','10580','11','MMD'), ('ADVB','10499','12','MMD'), ('ADVB','11078','13','MMD'), ('ADVB','10220','14','MMD'), ('ADVB','10400','15','MMD'), ('ADVB','10030','16','MMD'), ('ADVB','10254','17','MMD'), ('ADVB','10881','18','MMD'), ('ADVB','10740','21','MMD'), ('ADVB','10429','22','MMD'), ('ADVB','10771','23','MMD'), ('ADVB','11206','24','MMD'), ('ADVB','10048','25','MMD'), ('ADVB','10034','26','MMD'), ('ADVB','10369','27','MMD'), ('ADVB','10307','28','MMD'); CREATE TABLE "test_kpi_measure" ( "the_date" date DEFAULT NULL, "ptn_id" char(5) CHARACTER SET utf8 NOT NULL DEFAULT '', "cco_stk_ttl" int(11) DEFAULT NULL ); INSERT INTO kpi_measure (the_date,ptn_id,cco_stk_ttl) VALUES (current_date-1,'10284',20), (current_date-1,'10580',11), (current_date-1,'10499',2), (current_date-1,'11078',39), (current_date-1,'10220',19), (current_date-1,'10400',18), (current_date-1,'10030',22), (current_date-1,'10254',6), (current_date-1,'10881',6), (current_date-1,'10740',19), (current_date-1,'10429',8), (current_date-1,'10771',13), (current_date-1,'11206',91), (current_date-1,'10048',13), (current_date-1,'10034',5), (current_date-1,'10369',19), (current_date-1,'10307',24); {code} |
| Description |
A SQL returns invalid data if using field from "other" table:
Sample: {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-09 dis 90 ADVB 2021-08-09 dis 99 ADVB 2021-08-09 reg 9 ADVB 2021-08-09 nsc MMD ADVB 335 2021-08-09 nsc MMD ADVB 335 {code} correct would be: {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-09 dis 90 ADVB 2021-08-09 dis 99 ADVB 2021-08-09 reg 9 ADVB 2021-08-09 nsc MMD ADVB 335 {code} It looks like Test Data: {code:java} CREATE TABLE test_kpi_date (the_date date); INSERT INTO test_kpi_date VALUES (current_date), (current_date-1); -- cockpit.org_district definition CREATE TABLE test_org_district ( "nsc_id" char(5) NOT NULL, "dept_id" char(4) NOT NULL, "district_id" char(3) NOT NULL, "region_id" char(2) NOT NULL ); INSERT INTO test_org_district (nsc_id,dept_id,district_id,region_id) VALUES ('MMD','ADVB','11','1'), ('MMD','ADVB','12','1'), ('MMD','ADVB','13','1'), ('MMD','ADVB','14','1'), ('MMD','ADVB','15','1'), ('MMD','ADVB','16','1'), ('MMD','ADVB','17','1'), ('MMD','ADVB','18','1'), ('MMD','ADVB','10','1'), ('MMD','ADVB','21','2'); INSERT INTO test_org_district (nsc_id,dept_id,district_id,region_id) VALUES ('MMD','ADVB','23','2'), ('MMD','ADVB','24','2'), ('MMD','ADVB','25','2'), ('MMD','ADVB','22','2'), ('MMD','ADVB','26','2'), ('MMD','ADVB','27','2'), ('MMD','ADVB','28','2'), ('MMD','ADVB','99','9'), ('MMD','ADVB','90','9'); CREATE TABLE "test_org_partner" ( "dept_id" char(4) CHARACTER SET utf8 NOT NULL, "ptn_id" char(5) CHARACTER SET utf8, "district_id" char(3) CHARACTER SET utf8 NOT NULL DEFAULT '0', "nsc_id" char(5) CHARACTER SET utf8 NOT NULL ); INSERT INTO test_org_partner (dept_id,ptn_id,district_id,nsc_id) VALUES ('ADVB','10284','10','MMD'), ('ADVB','10580','11','MMD'), ('ADVB','10499','12','MMD'), ('ADVB','11078','13','MMD'), ('ADVB','10220','14','MMD'), ('ADVB','10400','15','MMD'), ('ADVB','10030','16','MMD'), ('ADVB','10254','17','MMD'), ('ADVB','10881','18','MMD'), ('ADVB','10740','21','MMD'), ('ADVB','10429','22','MMD'), ('ADVB','10771','23','MMD'), ('ADVB','11206','24','MMD'), ('ADVB','10048','25','MMD'), ('ADVB','10034','26','MMD'), ('ADVB','10369','27','MMD'), ('ADVB','10307','28','MMD'); CREATE TABLE "test_kpi_measure" ( "the_date" date DEFAULT NULL, "ptn_id" char(5) CHARACTER SET utf8 NOT NULL DEFAULT '', "cco_stk_ttl" int(11) DEFAULT NULL ); INSERT INTO kpi_measure (the_date,ptn_id,cco_stk_ttl) VALUES (current_date-1,'10284',20), (current_date-1,'10580',11), (current_date-1,'10499',2), (current_date-1,'11078',39), (current_date-1,'10220',19), (current_date-1,'10400',18), (current_date-1,'10030',22), (current_date-1,'10254',6), (current_date-1,'10881',6), (current_date-1,'10740',19), (current_date-1,'10429',8), (current_date-1,'10771',13), (current_date-1,'11206',91), (current_date-1,'10048',13), (current_date-1,'10034',5), (current_date-1,'10369',19), (current_date-1,'10307',24); {code} |
A SQL returns invalid data if using field from "other" table:
Sample: {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-09 dis 90 ADVB 2021-08-09 dis 99 ADVB 2021-08-09 reg 9 ADVB 2021-08-09 nsc MMD ADVB 335 2021-08-09 nsc MMD ADVB 335 {code} correct would be: {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-09 dis 90 ADVB 2021-08-09 dis 99 ADVB 2021-08-09 reg 9 ADVB 2021-08-09 nsc MMD ADVB 335 {code} It looks like Test Data: {code:java} CREATE TABLE test_kpi_date (the_date date); INSERT INTO test_kpi_date VALUES (current_date), (current_date-1); -- cockpit.org_district definition CREATE TABLE test_org_district ( "nsc_id" char(5) NOT NULL, "dept_id" char(4) NOT NULL, "district_id" char(3) NOT NULL, "region_id" char(2) NOT NULL ); INSERT INTO test_org_district (nsc_id,dept_id,district_id,region_id) VALUES ('MMD','ADVB','11','1'), ('MMD','ADVB','12','1'), ('MMD','ADVB','13','1'), ('MMD','ADVB','14','1'), ('MMD','ADVB','15','1'), ('MMD','ADVB','16','1'), ('MMD','ADVB','17','1'), ('MMD','ADVB','18','1'), ('MMD','ADVB','10','1'), ('MMD','ADVB','21','2'); INSERT INTO test_org_district (nsc_id,dept_id,district_id,region_id) VALUES ('MMD','ADVB','23','2'), ('MMD','ADVB','24','2'), ('MMD','ADVB','25','2'), ('MMD','ADVB','22','2'), ('MMD','ADVB','26','2'), ('MMD','ADVB','27','2'), ('MMD','ADVB','28','2'), ('MMD','ADVB','99','9'), ('MMD','ADVB','90','9'); CREATE TABLE "test_org_partner" ( "dept_id" char(4) CHARACTER SET utf8 NOT NULL, "ptn_id" char(5) CHARACTER SET utf8, "district_id" char(3) CHARACTER SET utf8 NOT NULL DEFAULT '0', "nsc_id" char(5) CHARACTER SET utf8 NOT NULL ); INSERT INTO test_org_partner (dept_id,ptn_id,district_id,nsc_id) VALUES ('ADVB','10284','10','MMD'), ('ADVB','10580','11','MMD'), ('ADVB','10499','12','MMD'), ('ADVB','11078','13','MMD'), ('ADVB','10220','14','MMD'), ('ADVB','10400','15','MMD'), ('ADVB','10030','16','MMD'), ('ADVB','10254','17','MMD'), ('ADVB','10881','18','MMD'), ('ADVB','10740','21','MMD'), ('ADVB','10429','22','MMD'), ('ADVB','10771','23','MMD'), ('ADVB','11206','24','MMD'), ('ADVB','10048','25','MMD'), ('ADVB','10034','26','MMD'), ('ADVB','10369','27','MMD'), ('ADVB','10307','28','MMD'); CREATE TABLE "test_kpi_measure" ( "the_date" date DEFAULT NULL, "ptn_id" char(5) CHARACTER SET utf8 NOT NULL DEFAULT '', "cco_stk_ttl" int(11) DEFAULT NULL ); INSERT INTO kpi_measure (the_date,ptn_id,cco_stk_ttl) VALUES (current_date-1,'10284',20), (current_date-1,'10580',11), (current_date-1,'10499',2), (current_date-1,'11078',39), (current_date-1,'10220',19), (current_date-1,'10400',18), (current_date-1,'10030',22), (current_date-1,'10254',6), (current_date-1,'10881',6), (current_date-1,'10740',19), (current_date-1,'10429',8), (current_date-1,'10771',13), (current_date-1,'11206',91), (current_date-1,'10048',13), (current_date-1,'10034',5), (current_date-1,'10369',19), (current_date-1,'10307',24); {code} SQL with wrong result: {code:java} SELECT org.the_date , org.org_type , org.org_id , org.dept_id , msr.`cco_stk_ttl` FROM ( SELECT cal.the_date , dis.dept_id , case when dis.region_id <=> null then 'nsc' when dis.district_id <=> null then 'reg' else 'dis' end as org_type , coalesce(dis.district_id, dis.region_id, 'MMD') as org_id -- , null as ptn_id , dis.district_id , dis.region_id -- , 1 as is_valid_ptn -- @todo is_show_pale ? FROM test_kpi_date cal CROSS JOIN test_org_district dis WHERE dis.nsc_id = 'MMD' and dis.dept_id IN ('ADVB') AND cal.the_date = CURRENT_DATE() - INTERVAL 1 DAY GROUP BY cal.the_date, dis.dept_id, dis.region_id, dis.district_id WITH ROLLUP HAVING NOT (cal.the_date IS NULL OR dis.dept_id IS NULL) ) org -- LEFT JOIN ( SELECT sub.the_date , org.dept_id , dis.region_id , dis.district_id , coalesce(dis.district_id, dis.region_id, 'MMD') as org_id , SUM(sub.cco_stk_ttl) as cco_stk_ttl FROM test_kpi_measure sub JOIN test_org_partner org ON org.ptn_id = sub.ptn_id JOIN test_org_district dis ON dis.dept_id = org.dept_id AND dis.district_id = org.district_id WHERE org.nsc_id = 'MMD' and org.dept_id IN ('ADVB') GROUP BY sub.the_date, dis.dept_id, dis.region_id, dis.district_id WITH ROLLUP ) msr ON msr.the_date = org.the_date and msr.dept_id <=> org.dept_id and msr.region_id <=> org.region_id and msr.district_id <=> org.district_id {code} Code that works: {code:java} SELECT org.the_date , org.org_type , org.org_id , org.dept_id , msr.`cco_stk_ttl` FROM ( SELECT cal.the_date , dis.dept_id , case when dis.region_id <=> null then 'nsc' when dis.district_id <=> null then 'reg' else 'dis' end as org_type , coalesce(dis.district_id, dis.region_id, 'MMD') as org_id -- , null as ptn_id , dis.district_id , dis.region_id -- , 1 as is_valid_ptn -- @todo is_show_pale ? FROM test_kpi_date cal CROSS JOIN test_org_district dis WHERE dis.nsc_id = 'MMD' and dis.dept_id IN ('ADVB') AND cal.the_date = CURRENT_DATE() - INTERVAL 1 DAY GROUP BY cal.the_date, dis.dept_id, dis.region_id, dis.district_id WITH ROLLUP HAVING NOT (cal.the_date IS NULL OR dis.dept_id IS NULL) ) org -- LEFT JOIN ( SELECT sub.the_date , org.dept_id , dis.region_id , dis.district_id , coalesce(dis.district_id, dis.region_id, 'MMD') as org_id , SUM(sub.cco_stk_ttl) as cco_stk_ttl FROM test_kpi_measure sub JOIN test_org_partner org ON org.ptn_id = sub.ptn_id JOIN test_org_district dis ON dis.dept_id = org.dept_id AND dis.district_id = org.district_id WHERE org.nsc_id = 'MMD' and org.dept_id IN ('ADVB') GROUP BY sub.the_date, org.dept_id, dis.region_id, dis.district_id WITH ROLLUP -- <<<< ONLY changing dis -> org ) msr ON msr.the_date = org.the_date and msr.dept_id <=> org.dept_id and msr.region_id <=> org.region_id and msr.district_id <=> org.district_id {code} |
| Affects Version/s | 10.5.12 [ 26025 ] | |
| Affects Version/s | 10.5.10 [ 25204 ] |
| Summary | group by returns incorrect results | subquery with group by joined with nullsafe oprator returns incorrect results |
| Description |
A SQL returns invalid data if using field from "other" table:
Sample: {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-09 dis 90 ADVB 2021-08-09 dis 99 ADVB 2021-08-09 reg 9 ADVB 2021-08-09 nsc MMD ADVB 335 2021-08-09 nsc MMD ADVB 335 {code} correct would be: {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-09 dis 90 ADVB 2021-08-09 dis 99 ADVB 2021-08-09 reg 9 ADVB 2021-08-09 nsc MMD ADVB 335 {code} It looks like Test Data: {code:java} CREATE TABLE test_kpi_date (the_date date); INSERT INTO test_kpi_date VALUES (current_date), (current_date-1); -- cockpit.org_district definition CREATE TABLE test_org_district ( "nsc_id" char(5) NOT NULL, "dept_id" char(4) NOT NULL, "district_id" char(3) NOT NULL, "region_id" char(2) NOT NULL ); INSERT INTO test_org_district (nsc_id,dept_id,district_id,region_id) VALUES ('MMD','ADVB','11','1'), ('MMD','ADVB','12','1'), ('MMD','ADVB','13','1'), ('MMD','ADVB','14','1'), ('MMD','ADVB','15','1'), ('MMD','ADVB','16','1'), ('MMD','ADVB','17','1'), ('MMD','ADVB','18','1'), ('MMD','ADVB','10','1'), ('MMD','ADVB','21','2'); INSERT INTO test_org_district (nsc_id,dept_id,district_id,region_id) VALUES ('MMD','ADVB','23','2'), ('MMD','ADVB','24','2'), ('MMD','ADVB','25','2'), ('MMD','ADVB','22','2'), ('MMD','ADVB','26','2'), ('MMD','ADVB','27','2'), ('MMD','ADVB','28','2'), ('MMD','ADVB','99','9'), ('MMD','ADVB','90','9'); CREATE TABLE "test_org_partner" ( "dept_id" char(4) CHARACTER SET utf8 NOT NULL, "ptn_id" char(5) CHARACTER SET utf8, "district_id" char(3) CHARACTER SET utf8 NOT NULL DEFAULT '0', "nsc_id" char(5) CHARACTER SET utf8 NOT NULL ); INSERT INTO test_org_partner (dept_id,ptn_id,district_id,nsc_id) VALUES ('ADVB','10284','10','MMD'), ('ADVB','10580','11','MMD'), ('ADVB','10499','12','MMD'), ('ADVB','11078','13','MMD'), ('ADVB','10220','14','MMD'), ('ADVB','10400','15','MMD'), ('ADVB','10030','16','MMD'), ('ADVB','10254','17','MMD'), ('ADVB','10881','18','MMD'), ('ADVB','10740','21','MMD'), ('ADVB','10429','22','MMD'), ('ADVB','10771','23','MMD'), ('ADVB','11206','24','MMD'), ('ADVB','10048','25','MMD'), ('ADVB','10034','26','MMD'), ('ADVB','10369','27','MMD'), ('ADVB','10307','28','MMD'); CREATE TABLE "test_kpi_measure" ( "the_date" date DEFAULT NULL, "ptn_id" char(5) CHARACTER SET utf8 NOT NULL DEFAULT '', "cco_stk_ttl" int(11) DEFAULT NULL ); INSERT INTO kpi_measure (the_date,ptn_id,cco_stk_ttl) VALUES (current_date-1,'10284',20), (current_date-1,'10580',11), (current_date-1,'10499',2), (current_date-1,'11078',39), (current_date-1,'10220',19), (current_date-1,'10400',18), (current_date-1,'10030',22), (current_date-1,'10254',6), (current_date-1,'10881',6), (current_date-1,'10740',19), (current_date-1,'10429',8), (current_date-1,'10771',13), (current_date-1,'11206',91), (current_date-1,'10048',13), (current_date-1,'10034',5), (current_date-1,'10369',19), (current_date-1,'10307',24); {code} SQL with wrong result: {code:java} SELECT org.the_date , org.org_type , org.org_id , org.dept_id , msr.`cco_stk_ttl` FROM ( SELECT cal.the_date , dis.dept_id , case when dis.region_id <=> null then 'nsc' when dis.district_id <=> null then 'reg' else 'dis' end as org_type , coalesce(dis.district_id, dis.region_id, 'MMD') as org_id -- , null as ptn_id , dis.district_id , dis.region_id -- , 1 as is_valid_ptn -- @todo is_show_pale ? FROM test_kpi_date cal CROSS JOIN test_org_district dis WHERE dis.nsc_id = 'MMD' and dis.dept_id IN ('ADVB') AND cal.the_date = CURRENT_DATE() - INTERVAL 1 DAY GROUP BY cal.the_date, dis.dept_id, dis.region_id, dis.district_id WITH ROLLUP HAVING NOT (cal.the_date IS NULL OR dis.dept_id IS NULL) ) org -- LEFT JOIN ( SELECT sub.the_date , org.dept_id , dis.region_id , dis.district_id , coalesce(dis.district_id, dis.region_id, 'MMD') as org_id , SUM(sub.cco_stk_ttl) as cco_stk_ttl FROM test_kpi_measure sub JOIN test_org_partner org ON org.ptn_id = sub.ptn_id JOIN test_org_district dis ON dis.dept_id = org.dept_id AND dis.district_id = org.district_id WHERE org.nsc_id = 'MMD' and org.dept_id IN ('ADVB') GROUP BY sub.the_date, dis.dept_id, dis.region_id, dis.district_id WITH ROLLUP ) msr ON msr.the_date = org.the_date and msr.dept_id <=> org.dept_id and msr.region_id <=> org.region_id and msr.district_id <=> org.district_id {code} Code that works: {code:java} SELECT org.the_date , org.org_type , org.org_id , org.dept_id , msr.`cco_stk_ttl` FROM ( SELECT cal.the_date , dis.dept_id , case when dis.region_id <=> null then 'nsc' when dis.district_id <=> null then 'reg' else 'dis' end as org_type , coalesce(dis.district_id, dis.region_id, 'MMD') as org_id -- , null as ptn_id , dis.district_id , dis.region_id -- , 1 as is_valid_ptn -- @todo is_show_pale ? FROM test_kpi_date cal CROSS JOIN test_org_district dis WHERE dis.nsc_id = 'MMD' and dis.dept_id IN ('ADVB') AND cal.the_date = CURRENT_DATE() - INTERVAL 1 DAY GROUP BY cal.the_date, dis.dept_id, dis.region_id, dis.district_id WITH ROLLUP HAVING NOT (cal.the_date IS NULL OR dis.dept_id IS NULL) ) org -- LEFT JOIN ( SELECT sub.the_date , org.dept_id , dis.region_id , dis.district_id , coalesce(dis.district_id, dis.region_id, 'MMD') as org_id , SUM(sub.cco_stk_ttl) as cco_stk_ttl FROM test_kpi_measure sub JOIN test_org_partner org ON org.ptn_id = sub.ptn_id JOIN test_org_district dis ON dis.dept_id = org.dept_id AND dis.district_id = org.district_id WHERE org.nsc_id = 'MMD' and org.dept_id IN ('ADVB') GROUP BY sub.the_date, org.dept_id, dis.region_id, dis.district_id WITH ROLLUP -- <<<< ONLY changing dis -> org ) msr ON msr.the_date = org.the_date and msr.dept_id <=> org.dept_id and msr.region_id <=> org.region_id and msr.district_id <=> org.district_id {code} |
A SQL returns invalid data if using field from "other" table:
Sample: {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-09 dis 90 ADVB 2021-08-09 dis 99 ADVB 2021-08-09 reg 9 ADVB 2021-08-09 nsc MMD ADVB 335 2021-08-09 nsc MMD ADVB 335 {code} correct would be: {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-09 dis 90 ADVB 2021-08-09 dis 99 ADVB 2021-08-09 reg 9 ADVB 2021-08-09 nsc MMD ADVB 335 {code} It looks like Test Data: {code:java} CREATE OR REPLACE TABLE test_kpi_date (the_date date); INSERT INTO test_kpi_date VALUES (current_date), (current_date-1); -- cockpit.org_district definition CREATE OR REPLACE TABLE test_org_district ( "nsc_id" char(5) NOT NULL, "dept_id" char(4) NOT NULL, "district_id" char(3) NOT NULL, "region_id" char(2) NOT NULL ); INSERT INTO test_org_district (nsc_id,dept_id,district_id,region_id) VALUES ('MMD','ADVB','10','1'), ('MMD','ADVB','11','1'), ('MMD','ADVB','12','1'), ('MMD','ADVB','21','2'), ('MMD','ADVB','22','2'), ('MMD','ADVB','23','2'); CREATE OR REPLACE TABLE "test_org_partner" ( "dept_id" char(4) CHARACTER SET utf8 NOT NULL, "ptn_id" char(5) CHARACTER SET utf8, "district_id" char(3) CHARACTER SET utf8 NOT NULL DEFAULT '0', "nsc_id" char(5) CHARACTER SET utf8 NOT NULL ); INSERT INTO test_org_partner (dept_id,ptn_id,district_id,nsc_id) VALUES ('ADVB','10284','10','MMD'), ('ADVB','10580','11','MMD'), ('ADVB','10499','12','MMD'), ('ADVB','10740','21','MMD'), ('ADVB','10429','22','MMD'), ('ADVB','10771','23','MMD'); CREATE OR REPLACE TABLE "test_kpi_measure" ( "the_date" date DEFAULT NULL, "ptn_id" char(5) CHARACTER SET utf8 NOT NULL DEFAULT '', "cco_stk_ttl" int(11) DEFAULT NULL ); INSERT INTO test_kpi_measure (the_date,ptn_id,cco_stk_ttl) VALUES (current_date-1,'10284',30), (current_date-1,'10580',30), (current_date-1,'10499',40), (current_date-1,'10740',30), (current_date-1,'10429',30), (current_date-1,'10771',40); {code} SQL with wrong result: {code:java} SELECT sub.the_date , org.dept_id , case when dis.region_id <=> null then 'nsc' when dis.district_id <=> null then 'reg' else 'dis' end as org_type , coalesce(dis.district_id, dis.region_id, 'MMD') as org_id , SUM(sub.cco_stk_ttl) as cco_stk_ttl FROM test_kpi_measure sub JOIN test_org_partner org ON org.ptn_id = sub.ptn_id JOIN test_org_district dis ON dis.dept_id = org.dept_id AND dis.district_id = org.district_id WHERE org.nsc_id = 'MMD' and org.dept_id IN ('ADVB') -- AND sub.the_date = CURRENT_DATE() - INTERVAL 1 DAY GROUP BY sub.the_date, dis.dept_id, dis.region_id, dis.district_id WITH ROLLUP {code} Code that works: {code:java} SELECT sub.the_date , org.dept_id , case when dis.region_id <=> null then 'nsc' when dis.district_id <=> null then 'reg' else 'dis' end as org_type , coalesce(dis.district_id, dis.region_id, 'MMD') as org_id , SUM(sub.cco_stk_ttl) as cco_stk_ttl FROM test_kpi_measure sub JOIN test_org_partner org ON org.ptn_id = sub.ptn_id JOIN test_org_district dis ON dis.dept_id = org.dept_id AND dis.district_id = org.district_id WHERE org.nsc_id = 'MMD' and org.dept_id IN ('ADVB') -- AND sub.the_date = CURRENT_DATE() - INTERVAL 1 DAY GROUP BY sub.the_date, org.dept_id, dis.region_id, dis.district_id WITH ROLLUP -- <<<< ONLY changing dis -> org {code} |
| Summary | subquery with group by joined with nullsafe oprator returns incorrect results | query with group and ROLLUP returns incorrect results |
| Description |
A SQL returns invalid data if using field from "other" table:
Sample: {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-09 dis 90 ADVB 2021-08-09 dis 99 ADVB 2021-08-09 reg 9 ADVB 2021-08-09 nsc MMD ADVB 335 2021-08-09 nsc MMD ADVB 335 {code} correct would be: {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-09 dis 90 ADVB 2021-08-09 dis 99 ADVB 2021-08-09 reg 9 ADVB 2021-08-09 nsc MMD ADVB 335 {code} It looks like Test Data: {code:java} CREATE OR REPLACE TABLE test_kpi_date (the_date date); INSERT INTO test_kpi_date VALUES (current_date), (current_date-1); -- cockpit.org_district definition CREATE OR REPLACE TABLE test_org_district ( "nsc_id" char(5) NOT NULL, "dept_id" char(4) NOT NULL, "district_id" char(3) NOT NULL, "region_id" char(2) NOT NULL ); INSERT INTO test_org_district (nsc_id,dept_id,district_id,region_id) VALUES ('MMD','ADVB','10','1'), ('MMD','ADVB','11','1'), ('MMD','ADVB','12','1'), ('MMD','ADVB','21','2'), ('MMD','ADVB','22','2'), ('MMD','ADVB','23','2'); CREATE OR REPLACE TABLE "test_org_partner" ( "dept_id" char(4) CHARACTER SET utf8 NOT NULL, "ptn_id" char(5) CHARACTER SET utf8, "district_id" char(3) CHARACTER SET utf8 NOT NULL DEFAULT '0', "nsc_id" char(5) CHARACTER SET utf8 NOT NULL ); INSERT INTO test_org_partner (dept_id,ptn_id,district_id,nsc_id) VALUES ('ADVB','10284','10','MMD'), ('ADVB','10580','11','MMD'), ('ADVB','10499','12','MMD'), ('ADVB','10740','21','MMD'), ('ADVB','10429','22','MMD'), ('ADVB','10771','23','MMD'); CREATE OR REPLACE TABLE "test_kpi_measure" ( "the_date" date DEFAULT NULL, "ptn_id" char(5) CHARACTER SET utf8 NOT NULL DEFAULT '', "cco_stk_ttl" int(11) DEFAULT NULL ); INSERT INTO test_kpi_measure (the_date,ptn_id,cco_stk_ttl) VALUES (current_date-1,'10284',30), (current_date-1,'10580',30), (current_date-1,'10499',40), (current_date-1,'10740',30), (current_date-1,'10429',30), (current_date-1,'10771',40); {code} SQL with wrong result: {code:java} SELECT sub.the_date , org.dept_id , case when dis.region_id <=> null then 'nsc' when dis.district_id <=> null then 'reg' else 'dis' end as org_type , coalesce(dis.district_id, dis.region_id, 'MMD') as org_id , SUM(sub.cco_stk_ttl) as cco_stk_ttl FROM test_kpi_measure sub JOIN test_org_partner org ON org.ptn_id = sub.ptn_id JOIN test_org_district dis ON dis.dept_id = org.dept_id AND dis.district_id = org.district_id WHERE org.nsc_id = 'MMD' and org.dept_id IN ('ADVB') -- AND sub.the_date = CURRENT_DATE() - INTERVAL 1 DAY GROUP BY sub.the_date, dis.dept_id, dis.region_id, dis.district_id WITH ROLLUP {code} Code that works: {code:java} SELECT sub.the_date , org.dept_id , case when dis.region_id <=> null then 'nsc' when dis.district_id <=> null then 'reg' else 'dis' end as org_type , coalesce(dis.district_id, dis.region_id, 'MMD') as org_id , SUM(sub.cco_stk_ttl) as cco_stk_ttl FROM test_kpi_measure sub JOIN test_org_partner org ON org.ptn_id = sub.ptn_id JOIN test_org_district dis ON dis.dept_id = org.dept_id AND dis.district_id = org.district_id WHERE org.nsc_id = 'MMD' and org.dept_id IN ('ADVB') -- AND sub.the_date = CURRENT_DATE() - INTERVAL 1 DAY GROUP BY sub.the_date, org.dept_id, dis.region_id, dis.district_id WITH ROLLUP -- <<<< ONLY changing dis -> org {code} |
A SQL returns invalid data if using field from "other" table:
Sample: {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-09 dis 90 ADVB 2021-08-09 dis 99 ADVB 2021-08-09 reg 9 ADVB 2021-08-09 nsc MMD ADVB 335 2021-08-09 nsc MMD ADVB 335 {code} correct would be: {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-09 dis 90 ADVB 2021-08-09 dis 99 ADVB 2021-08-09 reg 9 ADVB 2021-08-09 nsc MMD ADVB 335 {code} It looks like Test Data: {code:java} CREATE OR REPLACE TABLE test_kpi_date (the_date date); INSERT INTO test_kpi_date VALUES (current_date), (current_date-1); -- cockpit.org_district definition CREATE OR REPLACE TABLE test_org_district ( "nsc_id" char(5) NOT NULL, "dept_id" char(4) NOT NULL, "district_id" char(3) NOT NULL, "region_id" char(2) NOT NULL ); INSERT INTO test_org_district (nsc_id,dept_id,district_id,region_id) VALUES ('MMD','ADVB','10','1'), ('MMD','ADVB','11','1'), ('MMD','ADVB','12','1'), ('MMD','ADVB','21','2'), ('MMD','ADVB','22','2'), ('MMD','ADVB','23','2'); CREATE OR REPLACE TABLE "test_org_partner" ( "dept_id" char(4) CHARACTER SET utf8 NOT NULL, "ptn_id" char(5) CHARACTER SET utf8, "district_id" char(3) CHARACTER SET utf8 NOT NULL DEFAULT '0', "nsc_id" char(5) CHARACTER SET utf8 NOT NULL ); INSERT INTO test_org_partner (dept_id,ptn_id,district_id,nsc_id) VALUES ('ADVB','10284','10','MMD'), ('ADVB','10580','11','MMD'), ('ADVB','10499','12','MMD'), ('ADVB','10740','21','MMD'), ('ADVB','10429','22','MMD'), ('ADVB','10771','23','MMD'); CREATE OR REPLACE TABLE "test_kpi_measure" ( "the_date" date DEFAULT NULL, "ptn_id" char(5) CHARACTER SET utf8 NOT NULL DEFAULT '', "cco_stk_ttl" int(11) DEFAULT NULL ); INSERT INTO test_kpi_measure (the_date,ptn_id,cco_stk_ttl) VALUES (current_date-1,'10284',30), (current_date-1,'10580',30), (current_date-1,'10499',40), (current_date-1,'10740',30), (current_date-1,'10429',30), (current_date-1,'10771',40); {code} SQL with wrong result: {code:java} SELECT sub.the_date , org.dept_id , case when dis.region_id <=> null then 'nsc' when dis.district_id <=> null then 'reg' else 'dis' end as org_type , coalesce(dis.district_id, dis.region_id, 'MMD') as org_id , SUM(sub.cco_stk_ttl) as cco_stk_ttl FROM test_kpi_measure sub JOIN test_org_partner org ON org.ptn_id = sub.ptn_id JOIN test_org_district dis ON dis.dept_id = org.dept_id AND dis.district_id = org.district_id WHERE org.nsc_id = 'MMD' and org.dept_id IN ('ADVB') -- AND sub.the_date = CURRENT_DATE() - INTERVAL 1 DAY GROUP BY sub.the_date, dis.dept_id, dis.region_id, dis.district_id WITH ROLLUP {code} Code that works: {code:java} SELECT sub.the_date , org.dept_id , case when dis.region_id <=> null then 'nsc' when dis.district_id <=> null then 'reg' else 'dis' end as org_type , coalesce(dis.district_id, dis.region_id, 'MMD') as org_id , SUM(sub.cco_stk_ttl) as cco_stk_ttl FROM test_kpi_measure sub JOIN test_org_partner org ON org.ptn_id = sub.ptn_id JOIN test_org_district dis ON dis.dept_id = org.dept_id AND dis.district_id = org.district_id WHERE org.nsc_id = 'MMD' and org.dept_id IN ('ADVB') -- AND sub.the_date = CURRENT_DATE() - INTERVAL 1 DAY GROUP BY sub.the_date, org.dept_id, dis.region_id, dis.district_id WITH ROLLUP -- <<<< ONLY changing dis -> org {code} dis.dept_id = org.dept_id are equal, but showing different content with GROUP BY and ROLLUP. |
| Priority | Major [ 3 ] | Trivial [ 5 ] |
| Description |
A SQL returns invalid data if using field from "other" table:
Sample: {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-09 dis 90 ADVB 2021-08-09 dis 99 ADVB 2021-08-09 reg 9 ADVB 2021-08-09 nsc MMD ADVB 335 2021-08-09 nsc MMD ADVB 335 {code} correct would be: {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-09 dis 90 ADVB 2021-08-09 dis 99 ADVB 2021-08-09 reg 9 ADVB 2021-08-09 nsc MMD ADVB 335 {code} It looks like Test Data: {code:java} CREATE OR REPLACE TABLE test_kpi_date (the_date date); INSERT INTO test_kpi_date VALUES (current_date), (current_date-1); -- cockpit.org_district definition CREATE OR REPLACE TABLE test_org_district ( "nsc_id" char(5) NOT NULL, "dept_id" char(4) NOT NULL, "district_id" char(3) NOT NULL, "region_id" char(2) NOT NULL ); INSERT INTO test_org_district (nsc_id,dept_id,district_id,region_id) VALUES ('MMD','ADVB','10','1'), ('MMD','ADVB','11','1'), ('MMD','ADVB','12','1'), ('MMD','ADVB','21','2'), ('MMD','ADVB','22','2'), ('MMD','ADVB','23','2'); CREATE OR REPLACE TABLE "test_org_partner" ( "dept_id" char(4) CHARACTER SET utf8 NOT NULL, "ptn_id" char(5) CHARACTER SET utf8, "district_id" char(3) CHARACTER SET utf8 NOT NULL DEFAULT '0', "nsc_id" char(5) CHARACTER SET utf8 NOT NULL ); INSERT INTO test_org_partner (dept_id,ptn_id,district_id,nsc_id) VALUES ('ADVB','10284','10','MMD'), ('ADVB','10580','11','MMD'), ('ADVB','10499','12','MMD'), ('ADVB','10740','21','MMD'), ('ADVB','10429','22','MMD'), ('ADVB','10771','23','MMD'); CREATE OR REPLACE TABLE "test_kpi_measure" ( "the_date" date DEFAULT NULL, "ptn_id" char(5) CHARACTER SET utf8 NOT NULL DEFAULT '', "cco_stk_ttl" int(11) DEFAULT NULL ); INSERT INTO test_kpi_measure (the_date,ptn_id,cco_stk_ttl) VALUES (current_date-1,'10284',30), (current_date-1,'10580',30), (current_date-1,'10499',40), (current_date-1,'10740',30), (current_date-1,'10429',30), (current_date-1,'10771',40); {code} SQL with wrong result: {code:java} SELECT sub.the_date , org.dept_id , case when dis.region_id <=> null then 'nsc' when dis.district_id <=> null then 'reg' else 'dis' end as org_type , coalesce(dis.district_id, dis.region_id, 'MMD') as org_id , SUM(sub.cco_stk_ttl) as cco_stk_ttl FROM test_kpi_measure sub JOIN test_org_partner org ON org.ptn_id = sub.ptn_id JOIN test_org_district dis ON dis.dept_id = org.dept_id AND dis.district_id = org.district_id WHERE org.nsc_id = 'MMD' and org.dept_id IN ('ADVB') -- AND sub.the_date = CURRENT_DATE() - INTERVAL 1 DAY GROUP BY sub.the_date, dis.dept_id, dis.region_id, dis.district_id WITH ROLLUP {code} Code that works: {code:java} SELECT sub.the_date , org.dept_id , case when dis.region_id <=> null then 'nsc' when dis.district_id <=> null then 'reg' else 'dis' end as org_type , coalesce(dis.district_id, dis.region_id, 'MMD') as org_id , SUM(sub.cco_stk_ttl) as cco_stk_ttl FROM test_kpi_measure sub JOIN test_org_partner org ON org.ptn_id = sub.ptn_id JOIN test_org_district dis ON dis.dept_id = org.dept_id AND dis.district_id = org.district_id WHERE org.nsc_id = 'MMD' and org.dept_id IN ('ADVB') -- AND sub.the_date = CURRENT_DATE() - INTERVAL 1 DAY GROUP BY sub.the_date, org.dept_id, dis.region_id, dis.district_id WITH ROLLUP -- <<<< ONLY changing dis -> org {code} dis.dept_id = org.dept_id are equal, but showing different content with GROUP BY and ROLLUP. |
PLEASE CLOSE! IT IS OBSOLETE!
A SQL returns invalid data if using field from "other" table: Sample: {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-09 dis 90 ADVB 2021-08-09 dis 99 ADVB 2021-08-09 reg 9 ADVB 2021-08-09 nsc MMD ADVB 335 2021-08-09 nsc MMD ADVB 335 {code} correct would be: {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-09 dis 90 ADVB 2021-08-09 dis 99 ADVB 2021-08-09 reg 9 ADVB 2021-08-09 nsc MMD ADVB 335 {code} It looks like Test Data: {code:java} CREATE OR REPLACE TABLE test_kpi_date (the_date date); INSERT INTO test_kpi_date VALUES (current_date), (current_date-1); -- cockpit.org_district definition CREATE OR REPLACE TABLE test_org_district ( "nsc_id" char(5) NOT NULL, "dept_id" char(4) NOT NULL, "district_id" char(3) NOT NULL, "region_id" char(2) NOT NULL ); INSERT INTO test_org_district (nsc_id,dept_id,district_id,region_id) VALUES ('MMD','ADVB','10','1'), ('MMD','ADVB','11','1'), ('MMD','ADVB','12','1'), ('MMD','ADVB','21','2'), ('MMD','ADVB','22','2'), ('MMD','ADVB','23','2'); CREATE OR REPLACE TABLE "test_org_partner" ( "dept_id" char(4) CHARACTER SET utf8 NOT NULL, "ptn_id" char(5) CHARACTER SET utf8, "district_id" char(3) CHARACTER SET utf8 NOT NULL DEFAULT '0', "nsc_id" char(5) CHARACTER SET utf8 NOT NULL ); INSERT INTO test_org_partner (dept_id,ptn_id,district_id,nsc_id) VALUES ('ADVB','10284','10','MMD'), ('ADVB','10580','11','MMD'), ('ADVB','10499','12','MMD'), ('ADVB','10740','21','MMD'), ('ADVB','10429','22','MMD'), ('ADVB','10771','23','MMD'); CREATE OR REPLACE TABLE "test_kpi_measure" ( "the_date" date DEFAULT NULL, "ptn_id" char(5) CHARACTER SET utf8 NOT NULL DEFAULT '', "cco_stk_ttl" int(11) DEFAULT NULL ); INSERT INTO test_kpi_measure (the_date,ptn_id,cco_stk_ttl) VALUES (current_date-1,'10284',30), (current_date-1,'10580',30), (current_date-1,'10499',40), (current_date-1,'10740',30), (current_date-1,'10429',30), (current_date-1,'10771',40); {code} SQL with wrong result: {code:java} SELECT sub.the_date , org.dept_id , case when dis.region_id <=> null then 'nsc' when dis.district_id <=> null then 'reg' else 'dis' end as org_type , coalesce(dis.district_id, dis.region_id, 'MMD') as org_id , SUM(sub.cco_stk_ttl) as cco_stk_ttl FROM test_kpi_measure sub JOIN test_org_partner org ON org.ptn_id = sub.ptn_id JOIN test_org_district dis ON dis.dept_id = org.dept_id AND dis.district_id = org.district_id WHERE org.nsc_id = 'MMD' and org.dept_id IN ('ADVB') -- AND sub.the_date = CURRENT_DATE() - INTERVAL 1 DAY GROUP BY sub.the_date, dis.dept_id, dis.region_id, dis.district_id WITH ROLLUP {code} Code that works: {code:java} SELECT sub.the_date , org.dept_id , case when dis.region_id <=> null then 'nsc' when dis.district_id <=> null then 'reg' else 'dis' end as org_type , coalesce(dis.district_id, dis.region_id, 'MMD') as org_id , SUM(sub.cco_stk_ttl) as cco_stk_ttl FROM test_kpi_measure sub JOIN test_org_partner org ON org.ptn_id = sub.ptn_id JOIN test_org_district dis ON dis.dept_id = org.dept_id AND dis.district_id = org.district_id WHERE org.nsc_id = 'MMD' and org.dept_id IN ('ADVB') -- AND sub.the_date = CURRENT_DATE() - INTERVAL 1 DAY GROUP BY sub.the_date, org.dept_id, dis.region_id, dis.district_id WITH ROLLUP -- <<<< ONLY changing dis -> org {code} dis.dept_id = org.dept_id are equal, but showing different content with GROUP BY and ROLLUP. |
| Description |
PLEASE CLOSE! IT IS OBSOLETE!
A SQL returns invalid data if using field from "other" table: Sample: {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-09 dis 90 ADVB 2021-08-09 dis 99 ADVB 2021-08-09 reg 9 ADVB 2021-08-09 nsc MMD ADVB 335 2021-08-09 nsc MMD ADVB 335 {code} correct would be: {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-09 dis 90 ADVB 2021-08-09 dis 99 ADVB 2021-08-09 reg 9 ADVB 2021-08-09 nsc MMD ADVB 335 {code} It looks like Test Data: {code:java} CREATE OR REPLACE TABLE test_kpi_date (the_date date); INSERT INTO test_kpi_date VALUES (current_date), (current_date-1); -- cockpit.org_district definition CREATE OR REPLACE TABLE test_org_district ( "nsc_id" char(5) NOT NULL, "dept_id" char(4) NOT NULL, "district_id" char(3) NOT NULL, "region_id" char(2) NOT NULL ); INSERT INTO test_org_district (nsc_id,dept_id,district_id,region_id) VALUES ('MMD','ADVB','10','1'), ('MMD','ADVB','11','1'), ('MMD','ADVB','12','1'), ('MMD','ADVB','21','2'), ('MMD','ADVB','22','2'), ('MMD','ADVB','23','2'); CREATE OR REPLACE TABLE "test_org_partner" ( "dept_id" char(4) CHARACTER SET utf8 NOT NULL, "ptn_id" char(5) CHARACTER SET utf8, "district_id" char(3) CHARACTER SET utf8 NOT NULL DEFAULT '0', "nsc_id" char(5) CHARACTER SET utf8 NOT NULL ); INSERT INTO test_org_partner (dept_id,ptn_id,district_id,nsc_id) VALUES ('ADVB','10284','10','MMD'), ('ADVB','10580','11','MMD'), ('ADVB','10499','12','MMD'), ('ADVB','10740','21','MMD'), ('ADVB','10429','22','MMD'), ('ADVB','10771','23','MMD'); CREATE OR REPLACE TABLE "test_kpi_measure" ( "the_date" date DEFAULT NULL, "ptn_id" char(5) CHARACTER SET utf8 NOT NULL DEFAULT '', "cco_stk_ttl" int(11) DEFAULT NULL ); INSERT INTO test_kpi_measure (the_date,ptn_id,cco_stk_ttl) VALUES (current_date-1,'10284',30), (current_date-1,'10580',30), (current_date-1,'10499',40), (current_date-1,'10740',30), (current_date-1,'10429',30), (current_date-1,'10771',40); {code} SQL with wrong result: {code:java} SELECT sub.the_date , org.dept_id , case when dis.region_id <=> null then 'nsc' when dis.district_id <=> null then 'reg' else 'dis' end as org_type , coalesce(dis.district_id, dis.region_id, 'MMD') as org_id , SUM(sub.cco_stk_ttl) as cco_stk_ttl FROM test_kpi_measure sub JOIN test_org_partner org ON org.ptn_id = sub.ptn_id JOIN test_org_district dis ON dis.dept_id = org.dept_id AND dis.district_id = org.district_id WHERE org.nsc_id = 'MMD' and org.dept_id IN ('ADVB') -- AND sub.the_date = CURRENT_DATE() - INTERVAL 1 DAY GROUP BY sub.the_date, dis.dept_id, dis.region_id, dis.district_id WITH ROLLUP {code} Code that works: {code:java} SELECT sub.the_date , org.dept_id , case when dis.region_id <=> null then 'nsc' when dis.district_id <=> null then 'reg' else 'dis' end as org_type , coalesce(dis.district_id, dis.region_id, 'MMD') as org_id , SUM(sub.cco_stk_ttl) as cco_stk_ttl FROM test_kpi_measure sub JOIN test_org_partner org ON org.ptn_id = sub.ptn_id JOIN test_org_district dis ON dis.dept_id = org.dept_id AND dis.district_id = org.district_id WHERE org.nsc_id = 'MMD' and org.dept_id IN ('ADVB') -- AND sub.the_date = CURRENT_DATE() - INTERVAL 1 DAY GROUP BY sub.the_date, org.dept_id, dis.region_id, dis.district_id WITH ROLLUP -- <<<< ONLY changing dis -> org {code} dis.dept_id = org.dept_id are equal, but showing different content with GROUP BY and ROLLUP. |
A SQL returns invalid data if using field from "other" table:
Sample: {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-09 dis 90 ADVB 2021-08-09 dis 99 ADVB 2021-08-09 reg 9 ADVB 2021-08-09 nsc MMD ADVB 335 2021-08-09 nsc MMD ADVB 335 {code} correct would be: {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-09 dis 90 ADVB 2021-08-09 dis 99 ADVB 2021-08-09 reg 9 ADVB 2021-08-09 nsc MMD ADVB 335 {code} It looks like Test Data: {code:java} CREATE OR REPLACE TABLE test_kpi_date (the_date date); INSERT INTO test_kpi_date VALUES (current_date), (current_date-1); -- cockpit.org_district definition CREATE OR REPLACE TABLE test_org_district ( "nsc_id" char(5) NOT NULL, "dept_id" char(4) NOT NULL, "district_id" char(3) NOT NULL, "region_id" char(2) NOT NULL ); INSERT INTO test_org_district (nsc_id,dept_id,district_id,region_id) VALUES ('MMD','ADVB','10','1'), ('MMD','ADVB','11','1'), ('MMD','ADVB','12','1'), ('MMD','ADVB','21','2'), ('MMD','ADVB','22','2'), ('MMD','ADVB','23','2'); CREATE OR REPLACE TABLE "test_org_partner" ( "dept_id" char(4) CHARACTER SET utf8 NOT NULL, "ptn_id" char(5) CHARACTER SET utf8, "district_id" char(3) CHARACTER SET utf8 NOT NULL DEFAULT '0', "nsc_id" char(5) CHARACTER SET utf8 NOT NULL ); INSERT INTO test_org_partner (dept_id,ptn_id,district_id,nsc_id) VALUES ('ADVB','10284','10','MMD'), ('ADVB','10580','11','MMD'), ('ADVB','10499','12','MMD'), ('ADVB','10740','21','MMD'), ('ADVB','10429','22','MMD'), ('ADVB','10771','23','MMD'); CREATE OR REPLACE TABLE "test_kpi_measure" ( "the_date" date DEFAULT NULL, "ptn_id" char(5) CHARACTER SET utf8 NOT NULL DEFAULT '', "cco_stk_ttl" int(11) DEFAULT NULL ); INSERT INTO test_kpi_measure (the_date,ptn_id,cco_stk_ttl) VALUES (current_date-1,'10284',30), (current_date-1,'10580',30), (current_date-1,'10499',40), (current_date-1,'10740',30), (current_date-1,'10429',30), (current_date-1,'10771',40); {code} SQL with wrong result: {code:java} SELECT sub.the_date , org.dept_id , case when dis.region_id <=> null then 'nsc' when dis.district_id <=> null then 'reg' else 'dis' end as org_type , coalesce(dis.district_id, dis.region_id, 'MMD') as org_id , SUM(sub.cco_stk_ttl) as cco_stk_ttl FROM test_kpi_measure sub JOIN test_org_partner org ON org.ptn_id = sub.ptn_id JOIN test_org_district dis ON dis.dept_id = org.dept_id AND dis.district_id = org.district_id WHERE org.nsc_id = 'MMD' and org.dept_id IN ('ADVB') -- AND sub.the_date = CURRENT_DATE() - INTERVAL 1 DAY GROUP BY sub.the_date, dis.dept_id, dis.region_id, dis.district_id WITH ROLLUP {code} Code that works: {code:java} SELECT sub.the_date , org.dept_id , case when dis.region_id <=> null then 'nsc' when dis.district_id <=> null then 'reg' else 'dis' end as org_type , coalesce(dis.district_id, dis.region_id, 'MMD') as org_id , SUM(sub.cco_stk_ttl) as cco_stk_ttl FROM test_kpi_measure sub JOIN test_org_partner org ON org.ptn_id = sub.ptn_id JOIN test_org_district dis ON dis.dept_id = org.dept_id AND dis.district_id = org.district_id WHERE org.nsc_id = 'MMD' and org.dept_id IN ('ADVB') -- AND sub.the_date = CURRENT_DATE() - INTERVAL 1 DAY GROUP BY sub.the_date, org.dept_id, dis.region_id, dis.district_id WITH ROLLUP -- <<<< ONLY changing dis -> org {code} dis.dept_id = org.dept_id are equal, but showing different content with GROUP BY and ROLLUP. |
| Summary | query with group and ROLLUP returns incorrect results | subquery with groupby and ROLLUP returns incorrect results on |
| Summary | subquery with groupby and ROLLUP returns incorrect results on | subquery with groupby and ROLLUP returns incorrect results |
| Description |
A SQL returns invalid data if using field from "other" table:
Sample: {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-09 dis 90 ADVB 2021-08-09 dis 99 ADVB 2021-08-09 reg 9 ADVB 2021-08-09 nsc MMD ADVB 335 2021-08-09 nsc MMD ADVB 335 {code} correct would be: {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-09 dis 90 ADVB 2021-08-09 dis 99 ADVB 2021-08-09 reg 9 ADVB 2021-08-09 nsc MMD ADVB 335 {code} It looks like Test Data: {code:java} CREATE OR REPLACE TABLE test_kpi_date (the_date date); INSERT INTO test_kpi_date VALUES (current_date), (current_date-1); -- cockpit.org_district definition CREATE OR REPLACE TABLE test_org_district ( "nsc_id" char(5) NOT NULL, "dept_id" char(4) NOT NULL, "district_id" char(3) NOT NULL, "region_id" char(2) NOT NULL ); INSERT INTO test_org_district (nsc_id,dept_id,district_id,region_id) VALUES ('MMD','ADVB','10','1'), ('MMD','ADVB','11','1'), ('MMD','ADVB','12','1'), ('MMD','ADVB','21','2'), ('MMD','ADVB','22','2'), ('MMD','ADVB','23','2'); CREATE OR REPLACE TABLE "test_org_partner" ( "dept_id" char(4) CHARACTER SET utf8 NOT NULL, "ptn_id" char(5) CHARACTER SET utf8, "district_id" char(3) CHARACTER SET utf8 NOT NULL DEFAULT '0', "nsc_id" char(5) CHARACTER SET utf8 NOT NULL ); INSERT INTO test_org_partner (dept_id,ptn_id,district_id,nsc_id) VALUES ('ADVB','10284','10','MMD'), ('ADVB','10580','11','MMD'), ('ADVB','10499','12','MMD'), ('ADVB','10740','21','MMD'), ('ADVB','10429','22','MMD'), ('ADVB','10771','23','MMD'); CREATE OR REPLACE TABLE "test_kpi_measure" ( "the_date" date DEFAULT NULL, "ptn_id" char(5) CHARACTER SET utf8 NOT NULL DEFAULT '', "cco_stk_ttl" int(11) DEFAULT NULL ); INSERT INTO test_kpi_measure (the_date,ptn_id,cco_stk_ttl) VALUES (current_date-1,'10284',30), (current_date-1,'10580',30), (current_date-1,'10499',40), (current_date-1,'10740',30), (current_date-1,'10429',30), (current_date-1,'10771',40); {code} SQL with wrong result: {code:java} SELECT sub.the_date , org.dept_id , case when dis.region_id <=> null then 'nsc' when dis.district_id <=> null then 'reg' else 'dis' end as org_type , coalesce(dis.district_id, dis.region_id, 'MMD') as org_id , SUM(sub.cco_stk_ttl) as cco_stk_ttl FROM test_kpi_measure sub JOIN test_org_partner org ON org.ptn_id = sub.ptn_id JOIN test_org_district dis ON dis.dept_id = org.dept_id AND dis.district_id = org.district_id WHERE org.nsc_id = 'MMD' and org.dept_id IN ('ADVB') -- AND sub.the_date = CURRENT_DATE() - INTERVAL 1 DAY GROUP BY sub.the_date, dis.dept_id, dis.region_id, dis.district_id WITH ROLLUP {code} Code that works: {code:java} SELECT sub.the_date , org.dept_id , case when dis.region_id <=> null then 'nsc' when dis.district_id <=> null then 'reg' else 'dis' end as org_type , coalesce(dis.district_id, dis.region_id, 'MMD') as org_id , SUM(sub.cco_stk_ttl) as cco_stk_ttl FROM test_kpi_measure sub JOIN test_org_partner org ON org.ptn_id = sub.ptn_id JOIN test_org_district dis ON dis.dept_id = org.dept_id AND dis.district_id = org.district_id WHERE org.nsc_id = 'MMD' and org.dept_id IN ('ADVB') -- AND sub.the_date = CURRENT_DATE() - INTERVAL 1 DAY GROUP BY sub.the_date, org.dept_id, dis.region_id, dis.district_id WITH ROLLUP -- <<<< ONLY changing dis -> org {code} dis.dept_id = org.dept_id are equal, but showing different content with GROUP BY and ROLLUP. |
A SQL returns invalid data if using field from "other" table:
Sample: {code:csv} 2021-08-12 dis 10 ADVB 1 2021-08-12 dis 10 ADVB 0 2021-08-12 dis 11 ADVB 2 2021-08-12 dis 12 ADVB 2021-08-12 reg 1 ADVB 1 2021-08-12 reg 1 ADVB 2 2021-08-12 dis 21 ADVB 96 2021-08-12 dis 22 ADVB 256 2021-08-12 dis 23 ADVB 512 2021-08-12 reg 2 ADVB 864 2021-08-12 nsc MMD ADVB 867 {code} correct would be: {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-09 dis 90 ADVB 2021-08-09 dis 99 ADVB 2021-08-09 reg 9 ADVB 2021-08-09 nsc MMD ADVB 335 {code} It looks like Test Data: {code:java} CREATE OR REPLACE TABLE test_kpi_date (the_date date); INSERT INTO test_kpi_date VALUES (current_date), (current_date-1); -- cockpit.org_district definition CREATE OR REPLACE TABLE test_org_district ( "nsc_id" char(5) NOT NULL, "dept_id" char(4) NOT NULL, "district_id" char(3) NOT NULL, "region_id" char(2) NOT NULL ); INSERT INTO test_org_district (nsc_id,dept_id,district_id,region_id) VALUES ('MMD','ADVB','10','1'), ('MMD','ADVB','11','1'), ('MMD','ADVB','12','1'), ('MMD','ADVB','21','2'), ('MMD','ADVB','22','2'), ('MMD','ADVB','23','2'); CREATE OR REPLACE TABLE "test_org_partner" ( "dept_id" char(4) CHARACTER SET utf8 NOT NULL, "ptn_id" char(5) CHARACTER SET utf8, "district_id" char(3) CHARACTER SET utf8 NOT NULL DEFAULT '0', "nsc_id" char(5) CHARACTER SET utf8 NOT NULL ); INSERT INTO test_org_partner (dept_id,ptn_id,district_id,nsc_id) VALUES ('ADVB','10284','10','MMD'), ('ADVB','10580','11','MMD'), ('ADVB','10499','12','MMD'), ('ADVB','10740','21','MMD'), ('ADVB','10429','22','MMD'), ('ADVB','10771','23','MMD'); CREATE OR REPLACE TABLE "test_kpi_measure" ( "the_date" date DEFAULT NULL, "ptn_id" char(5) CHARACTER SET utf8 NOT NULL DEFAULT '', "cco_stk_ttl" int(11) DEFAULT NULL ); INSERT INTO test_kpi_measure (the_date,ptn_id,cco_stk_ttl) VALUES (current_date-1,'10284',30), (current_date-1,'10580',30), (current_date-1,'10499',40), (current_date-1,'10740',30), (current_date-1,'10429',30), (current_date-1,'10771',40); {code} SQL with wrong result: {code:java} SELECT sub.the_date , org.dept_id , case when dis.region_id <=> null then 'nsc' when dis.district_id <=> null then 'reg' else 'dis' end as org_type , coalesce(dis.district_id, dis.region_id, 'MMD') as org_id , SUM(sub.cco_stk_ttl) as cco_stk_ttl FROM test_kpi_measure sub JOIN test_org_partner org ON org.ptn_id = sub.ptn_id JOIN test_org_district dis ON dis.dept_id = org.dept_id AND dis.district_id = org.district_id WHERE org.nsc_id = 'MMD' and org.dept_id IN ('ADVB') -- AND sub.the_date = CURRENT_DATE() - INTERVAL 1 DAY GROUP BY sub.the_date, dis.dept_id, dis.region_id, dis.district_id WITH ROLLUP {code} Code that works: {code:java} SELECT sub.the_date , org.dept_id , case when dis.region_id <=> null then 'nsc' when dis.district_id <=> null then 'reg' else 'dis' end as org_type , coalesce(dis.district_id, dis.region_id, 'MMD') as org_id , SUM(sub.cco_stk_ttl) as cco_stk_ttl FROM test_kpi_measure sub JOIN test_org_partner org ON org.ptn_id = sub.ptn_id JOIN test_org_district dis ON dis.dept_id = org.dept_id AND dis.district_id = org.district_id WHERE org.nsc_id = 'MMD' and org.dept_id IN ('ADVB') -- AND sub.the_date = CURRENT_DATE() - INTERVAL 1 DAY GROUP BY sub.the_date, org.dept_id, dis.region_id, dis.district_id WITH ROLLUP -- <<<< ONLY changing dis -> org {code} dis.dept_id = org.dept_id are equal, but showing different content with GROUP BY and ROLLUP. |
| Description |
A SQL returns invalid data if using field from "other" table:
Sample: {code:csv} 2021-08-12 dis 10 ADVB 1 2021-08-12 dis 10 ADVB 0 2021-08-12 dis 11 ADVB 2 2021-08-12 dis 12 ADVB 2021-08-12 reg 1 ADVB 1 2021-08-12 reg 1 ADVB 2 2021-08-12 dis 21 ADVB 96 2021-08-12 dis 22 ADVB 256 2021-08-12 dis 23 ADVB 512 2021-08-12 reg 2 ADVB 864 2021-08-12 nsc MMD ADVB 867 {code} correct would be: {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-09 dis 90 ADVB 2021-08-09 dis 99 ADVB 2021-08-09 reg 9 ADVB 2021-08-09 nsc MMD ADVB 335 {code} It looks like Test Data: {code:java} CREATE OR REPLACE TABLE test_kpi_date (the_date date); INSERT INTO test_kpi_date VALUES (current_date), (current_date-1); -- cockpit.org_district definition CREATE OR REPLACE TABLE test_org_district ( "nsc_id" char(5) NOT NULL, "dept_id" char(4) NOT NULL, "district_id" char(3) NOT NULL, "region_id" char(2) NOT NULL ); INSERT INTO test_org_district (nsc_id,dept_id,district_id,region_id) VALUES ('MMD','ADVB','10','1'), ('MMD','ADVB','11','1'), ('MMD','ADVB','12','1'), ('MMD','ADVB','21','2'), ('MMD','ADVB','22','2'), ('MMD','ADVB','23','2'); CREATE OR REPLACE TABLE "test_org_partner" ( "dept_id" char(4) CHARACTER SET utf8 NOT NULL, "ptn_id" char(5) CHARACTER SET utf8, "district_id" char(3) CHARACTER SET utf8 NOT NULL DEFAULT '0', "nsc_id" char(5) CHARACTER SET utf8 NOT NULL ); INSERT INTO test_org_partner (dept_id,ptn_id,district_id,nsc_id) VALUES ('ADVB','10284','10','MMD'), ('ADVB','10580','11','MMD'), ('ADVB','10499','12','MMD'), ('ADVB','10740','21','MMD'), ('ADVB','10429','22','MMD'), ('ADVB','10771','23','MMD'); CREATE OR REPLACE TABLE "test_kpi_measure" ( "the_date" date DEFAULT NULL, "ptn_id" char(5) CHARACTER SET utf8 NOT NULL DEFAULT '', "cco_stk_ttl" int(11) DEFAULT NULL ); INSERT INTO test_kpi_measure (the_date,ptn_id,cco_stk_ttl) VALUES (current_date-1,'10284',30), (current_date-1,'10580',30), (current_date-1,'10499',40), (current_date-1,'10740',30), (current_date-1,'10429',30), (current_date-1,'10771',40); {code} SQL with wrong result: {code:java} SELECT sub.the_date , org.dept_id , case when dis.region_id <=> null then 'nsc' when dis.district_id <=> null then 'reg' else 'dis' end as org_type , coalesce(dis.district_id, dis.region_id, 'MMD') as org_id , SUM(sub.cco_stk_ttl) as cco_stk_ttl FROM test_kpi_measure sub JOIN test_org_partner org ON org.ptn_id = sub.ptn_id JOIN test_org_district dis ON dis.dept_id = org.dept_id AND dis.district_id = org.district_id WHERE org.nsc_id = 'MMD' and org.dept_id IN ('ADVB') -- AND sub.the_date = CURRENT_DATE() - INTERVAL 1 DAY GROUP BY sub.the_date, dis.dept_id, dis.region_id, dis.district_id WITH ROLLUP {code} Code that works: {code:java} SELECT sub.the_date , org.dept_id , case when dis.region_id <=> null then 'nsc' when dis.district_id <=> null then 'reg' else 'dis' end as org_type , coalesce(dis.district_id, dis.region_id, 'MMD') as org_id , SUM(sub.cco_stk_ttl) as cco_stk_ttl FROM test_kpi_measure sub JOIN test_org_partner org ON org.ptn_id = sub.ptn_id JOIN test_org_district dis ON dis.dept_id = org.dept_id AND dis.district_id = org.district_id WHERE org.nsc_id = 'MMD' and org.dept_id IN ('ADVB') -- AND sub.the_date = CURRENT_DATE() - INTERVAL 1 DAY GROUP BY sub.the_date, org.dept_id, dis.region_id, dis.district_id WITH ROLLUP -- <<<< ONLY changing dis -> org {code} dis.dept_id = org.dept_id are equal, but showing different content with GROUP BY and ROLLUP. |
A SQL returns invalid data if using field from "other" table:
Sample: {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-12 dis 10 ADVB 1 2021-08-12 dis 10 ADVB 0 2021-08-12 dis 11 ADVB 2 2021-08-12 dis 12 ADVB 2021-08-12 reg 1 ADVB 1 2021-08-12 reg 1 ADVB 2 2021-08-12 dis 21 ADVB 96 2021-08-12 dis 22 ADVB 256 2021-08-12 dis 23 ADVB 512 2021-08-12 reg 2 ADVB 864 2021-08-12 nsc MMD ADVB 867 {code} correct would be: {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-09 dis 90 ADVB 2021-08-09 dis 99 ADVB 2021-08-09 reg 9 ADVB 2021-08-09 nsc MMD ADVB 335 {code} It looks like Test Data: {code:java} CREATE OR REPLACE TABLE test_kpi_date (the_date date); INSERT INTO test_kpi_date VALUES (current_date), (current_date-1); -- cockpit.org_district definition CREATE OR REPLACE TABLE test_org_district ( "nsc_id" char(5) NOT NULL, "dept_id" char(4) NOT NULL, "district_id" char(3) NOT NULL, "region_id" char(2) NOT NULL ); INSERT INTO test_org_district (nsc_id,dept_id,district_id,region_id) VALUES ('MMD','ADVB','10','1'), ('MMD','ADVB','11','1'), ('MMD','ADVB','12','1'), ('MMD','ADVB','21','2'), ('MMD','ADVB','22','2'), ('MMD','ADVB','23','2'); CREATE OR REPLACE TABLE "test_org_partner" ( "dept_id" char(4) CHARACTER SET utf8 NOT NULL, "ptn_id" char(5) CHARACTER SET utf8, "district_id" char(3) CHARACTER SET utf8 NOT NULL DEFAULT '0', "nsc_id" char(5) CHARACTER SET utf8 NOT NULL ); INSERT INTO test_org_partner (dept_id,ptn_id,district_id,nsc_id) VALUES ('ADVB','10284','10','MMD'), ('ADVB','10580','11','MMD'), ('ADVB','10499','12','MMD'), ('ADVB','10740','21','MMD'), ('ADVB','10429','22','MMD'), ('ADVB','10771','23','MMD'); CREATE OR REPLACE TABLE "test_kpi_measure" ( "the_date" date DEFAULT NULL, "ptn_id" char(5) CHARACTER SET utf8 NOT NULL DEFAULT '', "cco_stk_ttl" int(11) DEFAULT NULL ); INSERT INTO test_kpi_measure (the_date,ptn_id,cco_stk_ttl) VALUES (current_date-1,'10284',30), (current_date-1,'10580',30), (current_date-1,'10499',40), (current_date-1,'10740',30), (current_date-1,'10429',30), (current_date-1,'10771',40); {code} SQL with wrong result: {code:java} SELECT sub.the_date , org.dept_id , case when dis.region_id <=> null then 'nsc' when dis.district_id <=> null then 'reg' else 'dis' end as org_type , coalesce(dis.district_id, dis.region_id, 'MMD') as org_id , SUM(sub.cco_stk_ttl) as cco_stk_ttl FROM test_kpi_measure sub JOIN test_org_partner org ON org.ptn_id = sub.ptn_id JOIN test_org_district dis ON dis.dept_id = org.dept_id AND dis.district_id = org.district_id WHERE org.nsc_id = 'MMD' and org.dept_id IN ('ADVB') -- AND sub.the_date = CURRENT_DATE() - INTERVAL 1 DAY GROUP BY sub.the_date, dis.dept_id, dis.region_id, dis.district_id WITH ROLLUP {code} Code that works: {code:java} SELECT sub.the_date , org.dept_id , case when dis.region_id <=> null then 'nsc' when dis.district_id <=> null then 'reg' else 'dis' end as org_type , coalesce(dis.district_id, dis.region_id, 'MMD') as org_id , SUM(sub.cco_stk_ttl) as cco_stk_ttl FROM test_kpi_measure sub JOIN test_org_partner org ON org.ptn_id = sub.ptn_id JOIN test_org_district dis ON dis.dept_id = org.dept_id AND dis.district_id = org.district_id WHERE org.nsc_id = 'MMD' and org.dept_id IN ('ADVB') -- AND sub.the_date = CURRENT_DATE() - INTERVAL 1 DAY GROUP BY sub.the_date, org.dept_id, dis.region_id, dis.district_id WITH ROLLUP -- <<<< ONLY changing dis -> org {code} dis.dept_id = org.dept_id are equal, but showing different content with GROUP BY and ROLLUP. |
| Description |
A SQL returns invalid data if using field from "other" table:
Sample: {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-12 dis 10 ADVB 1 2021-08-12 dis 10 ADVB 0 2021-08-12 dis 11 ADVB 2 2021-08-12 dis 12 ADVB 2021-08-12 reg 1 ADVB 1 2021-08-12 reg 1 ADVB 2 2021-08-12 dis 21 ADVB 96 2021-08-12 dis 22 ADVB 256 2021-08-12 dis 23 ADVB 512 2021-08-12 reg 2 ADVB 864 2021-08-12 nsc MMD ADVB 867 {code} correct would be: {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-09 dis 90 ADVB 2021-08-09 dis 99 ADVB 2021-08-09 reg 9 ADVB 2021-08-09 nsc MMD ADVB 335 {code} It looks like Test Data: {code:java} CREATE OR REPLACE TABLE test_kpi_date (the_date date); INSERT INTO test_kpi_date VALUES (current_date), (current_date-1); -- cockpit.org_district definition CREATE OR REPLACE TABLE test_org_district ( "nsc_id" char(5) NOT NULL, "dept_id" char(4) NOT NULL, "district_id" char(3) NOT NULL, "region_id" char(2) NOT NULL ); INSERT INTO test_org_district (nsc_id,dept_id,district_id,region_id) VALUES ('MMD','ADVB','10','1'), ('MMD','ADVB','11','1'), ('MMD','ADVB','12','1'), ('MMD','ADVB','21','2'), ('MMD','ADVB','22','2'), ('MMD','ADVB','23','2'); CREATE OR REPLACE TABLE "test_org_partner" ( "dept_id" char(4) CHARACTER SET utf8 NOT NULL, "ptn_id" char(5) CHARACTER SET utf8, "district_id" char(3) CHARACTER SET utf8 NOT NULL DEFAULT '0', "nsc_id" char(5) CHARACTER SET utf8 NOT NULL ); INSERT INTO test_org_partner (dept_id,ptn_id,district_id,nsc_id) VALUES ('ADVB','10284','10','MMD'), ('ADVB','10580','11','MMD'), ('ADVB','10499','12','MMD'), ('ADVB','10740','21','MMD'), ('ADVB','10429','22','MMD'), ('ADVB','10771','23','MMD'); CREATE OR REPLACE TABLE "test_kpi_measure" ( "the_date" date DEFAULT NULL, "ptn_id" char(5) CHARACTER SET utf8 NOT NULL DEFAULT '', "cco_stk_ttl" int(11) DEFAULT NULL ); INSERT INTO test_kpi_measure (the_date,ptn_id,cco_stk_ttl) VALUES (current_date-1,'10284',30), (current_date-1,'10580',30), (current_date-1,'10499',40), (current_date-1,'10740',30), (current_date-1,'10429',30), (current_date-1,'10771',40); {code} SQL with wrong result: {code:java} SELECT sub.the_date , org.dept_id , case when dis.region_id <=> null then 'nsc' when dis.district_id <=> null then 'reg' else 'dis' end as org_type , coalesce(dis.district_id, dis.region_id, 'MMD') as org_id , SUM(sub.cco_stk_ttl) as cco_stk_ttl FROM test_kpi_measure sub JOIN test_org_partner org ON org.ptn_id = sub.ptn_id JOIN test_org_district dis ON dis.dept_id = org.dept_id AND dis.district_id = org.district_id WHERE org.nsc_id = 'MMD' and org.dept_id IN ('ADVB') -- AND sub.the_date = CURRENT_DATE() - INTERVAL 1 DAY GROUP BY sub.the_date, dis.dept_id, dis.region_id, dis.district_id WITH ROLLUP {code} Code that works: {code:java} SELECT sub.the_date , org.dept_id , case when dis.region_id <=> null then 'nsc' when dis.district_id <=> null then 'reg' else 'dis' end as org_type , coalesce(dis.district_id, dis.region_id, 'MMD') as org_id , SUM(sub.cco_stk_ttl) as cco_stk_ttl FROM test_kpi_measure sub JOIN test_org_partner org ON org.ptn_id = sub.ptn_id JOIN test_org_district dis ON dis.dept_id = org.dept_id AND dis.district_id = org.district_id WHERE org.nsc_id = 'MMD' and org.dept_id IN ('ADVB') -- AND sub.the_date = CURRENT_DATE() - INTERVAL 1 DAY GROUP BY sub.the_date, org.dept_id, dis.region_id, dis.district_id WITH ROLLUP -- <<<< ONLY changing dis -> org {code} dis.dept_id = org.dept_id are equal, but showing different content with GROUP BY and ROLLUP. |
A SQL returns invalid data if using field from "other" table:
Sample: {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-12 dis 10 ADVB 1 2021-08-12 dis 10 ADVB 0 2021-08-12 dis 11 ADVB 2 2021-08-12 dis 12 ADVB 2021-08-12 reg 1 ADVB 1 2021-08-12 reg 1 ADVB 2 2021-08-12 dis 21 ADVB 96 2021-08-12 dis 22 ADVB 256 2021-08-12 dis 23 ADVB 512 2021-08-12 reg 2 ADVB 864 2021-08-12 nsc MMD ADVB 867 {code} correct would be: {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-12 dis 10 ADVB 1 2021-08-12 dis 11 ADVB 2 2021-08-12 dis 12 ADVB 2021-08-12 reg 1 ADVB 3 2021-08-12 dis 21 ADVB 96 2021-08-12 dis 22 ADVB 256 2021-08-12 dis 23 ADVB 512 2021-08-12 reg 2 ADVB 864 2021-08-12 nsc MMD ADVB 867 {code} It looks like Test Data: {code:java} CREATE OR REPLACE TABLE test_kpi_date (the_date date); INSERT INTO test_kpi_date VALUES (current_date), (current_date-1); -- cockpit.org_district definition CREATE OR REPLACE TABLE test_org_district ( "nsc_id" char(5) NOT NULL, "dept_id" char(4) NOT NULL, "district_id" char(3) NOT NULL, "region_id" char(2) NOT NULL ); INSERT INTO test_org_district (nsc_id,dept_id,district_id,region_id) VALUES ('MMD','ADVB','10','1'), ('MMD','ADVB','11','1'), ('MMD','ADVB','12','1'), ('MMD','ADVB','21','2'), ('MMD','ADVB','22','2'), ('MMD','ADVB','23','2'); CREATE OR REPLACE TABLE "test_org_partner" ( "dept_id" char(4) CHARACTER SET utf8 NOT NULL, "ptn_id" char(5) CHARACTER SET utf8, "district_id" char(3) CHARACTER SET utf8 NOT NULL DEFAULT '0', "nsc_id" char(5) CHARACTER SET utf8 NOT NULL ); INSERT INTO test_org_partner (dept_id,ptn_id,district_id,nsc_id) VALUES ('ADVB','10284','10','MMD'), ('ADVB','10580','11','MMD'), ('ADVB','10499','12','MMD'), ('ADVB','10740','21','MMD'), ('ADVB','10429','22','MMD'), ('ADVB','10771','23','MMD'); CREATE OR REPLACE TABLE "test_kpi_measure" ( "the_date" date DEFAULT NULL, "ptn_id" char(5) CHARACTER SET utf8 NOT NULL DEFAULT '', "cco_stk_ttl" int(11) DEFAULT NULL ); INSERT INTO test_kpi_measure (the_date,ptn_id,cco_stk_ttl) VALUES (current_date-1,'10284',30), (current_date-1,'10580',30), (current_date-1,'10499',40), (current_date-1,'10740',30), (current_date-1,'10429',30), (current_date-1,'10771',40); {code} SQL with wrong result: {code:java} SELECT sub.the_date , org.dept_id , case when dis.region_id <=> null then 'nsc' when dis.district_id <=> null then 'reg' else 'dis' end as org_type , coalesce(dis.district_id, dis.region_id, 'MMD') as org_id , SUM(sub.cco_stk_ttl) as cco_stk_ttl FROM test_kpi_measure sub JOIN test_org_partner org ON org.ptn_id = sub.ptn_id JOIN test_org_district dis ON dis.dept_id = org.dept_id AND dis.district_id = org.district_id WHERE org.nsc_id = 'MMD' and org.dept_id IN ('ADVB') -- AND sub.the_date = CURRENT_DATE() - INTERVAL 1 DAY GROUP BY sub.the_date, dis.dept_id, dis.region_id, dis.district_id WITH ROLLUP {code} Code that works: {code:java} SELECT sub.the_date , org.dept_id , case when dis.region_id <=> null then 'nsc' when dis.district_id <=> null then 'reg' else 'dis' end as org_type , coalesce(dis.district_id, dis.region_id, 'MMD') as org_id , SUM(sub.cco_stk_ttl) as cco_stk_ttl FROM test_kpi_measure sub JOIN test_org_partner org ON org.ptn_id = sub.ptn_id JOIN test_org_district dis ON dis.dept_id = org.dept_id AND dis.district_id = org.district_id WHERE org.nsc_id = 'MMD' and org.dept_id IN ('ADVB') -- AND sub.the_date = CURRENT_DATE() - INTERVAL 1 DAY GROUP BY sub.the_date, org.dept_id, dis.region_id, dis.district_id WITH ROLLUP -- <<<< ONLY changing dis -> org {code} dis.dept_id = org.dept_id are equal, but showing different content with GROUP BY and ROLLUP. |
| Description |
A SQL returns invalid data if using field from "other" table:
Sample: {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-12 dis 10 ADVB 1 2021-08-12 dis 10 ADVB 0 2021-08-12 dis 11 ADVB 2 2021-08-12 dis 12 ADVB 2021-08-12 reg 1 ADVB 1 2021-08-12 reg 1 ADVB 2 2021-08-12 dis 21 ADVB 96 2021-08-12 dis 22 ADVB 256 2021-08-12 dis 23 ADVB 512 2021-08-12 reg 2 ADVB 864 2021-08-12 nsc MMD ADVB 867 {code} correct would be: {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-12 dis 10 ADVB 1 2021-08-12 dis 11 ADVB 2 2021-08-12 dis 12 ADVB 2021-08-12 reg 1 ADVB 3 2021-08-12 dis 21 ADVB 96 2021-08-12 dis 22 ADVB 256 2021-08-12 dis 23 ADVB 512 2021-08-12 reg 2 ADVB 864 2021-08-12 nsc MMD ADVB 867 {code} It looks like Test Data: {code:java} CREATE OR REPLACE TABLE test_kpi_date (the_date date); INSERT INTO test_kpi_date VALUES (current_date), (current_date-1); -- cockpit.org_district definition CREATE OR REPLACE TABLE test_org_district ( "nsc_id" char(5) NOT NULL, "dept_id" char(4) NOT NULL, "district_id" char(3) NOT NULL, "region_id" char(2) NOT NULL ); INSERT INTO test_org_district (nsc_id,dept_id,district_id,region_id) VALUES ('MMD','ADVB','10','1'), ('MMD','ADVB','11','1'), ('MMD','ADVB','12','1'), ('MMD','ADVB','21','2'), ('MMD','ADVB','22','2'), ('MMD','ADVB','23','2'); CREATE OR REPLACE TABLE "test_org_partner" ( "dept_id" char(4) CHARACTER SET utf8 NOT NULL, "ptn_id" char(5) CHARACTER SET utf8, "district_id" char(3) CHARACTER SET utf8 NOT NULL DEFAULT '0', "nsc_id" char(5) CHARACTER SET utf8 NOT NULL ); INSERT INTO test_org_partner (dept_id,ptn_id,district_id,nsc_id) VALUES ('ADVB','10284','10','MMD'), ('ADVB','10580','11','MMD'), ('ADVB','10499','12','MMD'), ('ADVB','10740','21','MMD'), ('ADVB','10429','22','MMD'), ('ADVB','10771','23','MMD'); CREATE OR REPLACE TABLE "test_kpi_measure" ( "the_date" date DEFAULT NULL, "ptn_id" char(5) CHARACTER SET utf8 NOT NULL DEFAULT '', "cco_stk_ttl" int(11) DEFAULT NULL ); INSERT INTO test_kpi_measure (the_date,ptn_id,cco_stk_ttl) VALUES (current_date-1,'10284',30), (current_date-1,'10580',30), (current_date-1,'10499',40), (current_date-1,'10740',30), (current_date-1,'10429',30), (current_date-1,'10771',40); {code} SQL with wrong result: {code:java} SELECT sub.the_date , org.dept_id , case when dis.region_id <=> null then 'nsc' when dis.district_id <=> null then 'reg' else 'dis' end as org_type , coalesce(dis.district_id, dis.region_id, 'MMD') as org_id , SUM(sub.cco_stk_ttl) as cco_stk_ttl FROM test_kpi_measure sub JOIN test_org_partner org ON org.ptn_id = sub.ptn_id JOIN test_org_district dis ON dis.dept_id = org.dept_id AND dis.district_id = org.district_id WHERE org.nsc_id = 'MMD' and org.dept_id IN ('ADVB') -- AND sub.the_date = CURRENT_DATE() - INTERVAL 1 DAY GROUP BY sub.the_date, dis.dept_id, dis.region_id, dis.district_id WITH ROLLUP {code} Code that works: {code:java} SELECT sub.the_date , org.dept_id , case when dis.region_id <=> null then 'nsc' when dis.district_id <=> null then 'reg' else 'dis' end as org_type , coalesce(dis.district_id, dis.region_id, 'MMD') as org_id , SUM(sub.cco_stk_ttl) as cco_stk_ttl FROM test_kpi_measure sub JOIN test_org_partner org ON org.ptn_id = sub.ptn_id JOIN test_org_district dis ON dis.dept_id = org.dept_id AND dis.district_id = org.district_id WHERE org.nsc_id = 'MMD' and org.dept_id IN ('ADVB') -- AND sub.the_date = CURRENT_DATE() - INTERVAL 1 DAY GROUP BY sub.the_date, org.dept_id, dis.region_id, dis.district_id WITH ROLLUP -- <<<< ONLY changing dis -> org {code} dis.dept_id = org.dept_id are equal, but showing different content with GROUP BY and ROLLUP. |
A SQL returns invalid data if using field from "other" table:
Sample: {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-12 dis 10 ADVB 1 2021-08-12 dis 10 ADVB 0 2021-08-12 dis 11 ADVB 2 2021-08-12 dis 12 ADVB 2021-08-12 reg 1 ADVB 1 2021-08-12 reg 1 ADVB 2 2021-08-12 dis 21 ADVB 96 2021-08-12 dis 22 ADVB 256 2021-08-12 dis 23 ADVB 512 2021-08-12 reg 2 ADVB 864 2021-08-12 nsc MMD ADVB 867 {code} correct would be: {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-12 dis 10 ADVB 1 2021-08-12 dis 11 ADVB 2 2021-08-12 dis 12 ADVB 2021-08-12 reg 1 ADVB 3 2021-08-12 dis 21 ADVB 96 2021-08-12 dis 22 ADVB 256 2021-08-12 dis 23 ADVB 512 2021-08-12 reg 2 ADVB 864 2021-08-12 nsc MMD ADVB 867 {code} It looks like Test Data: {code:sql} SET sql_mode = 'REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI,ONLY_FULL_GROUP_BY' CREATE OR REPLACE TABLE test_kpi_date ( the_date date NOT NULL , PRIMARY KEY ("the_date") ); SELECT * FROM test_kpi_date tkd INSERT INTO test_kpi_date VALUES (current_date), (current_date-1), (current_date-2), (current_date-3); CREATE OR REPLACE TABLE test_org_district ( "id" int(11) 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") ); INSERT INTO test_org_district (nsc_id,dept_id,district_id,region_id) VALUES ('MMD','ADVB','10','1'), ('MMD','ADVB','11','1'), ('MMD','ADVB','21','2'), ('MMD','ADVB','22','2'); CREATE OR REPLACE TABLE "test_org_partner" ( "dept_id" char(4) CHARACTER SET utf8 NOT NULL, "ptn_id" char(5) CHARACTER SET utf8, "district_id" char(3) CHARACTER SET utf8 NOT NULL DEFAULT '0', "nsc_id" char(5) CHARACTER SET utf8 NOT NULL, PRIMARY KEY ("ptn_id","dept_id"), KEY "dept_ptn" ("dept_id","ptn_id"), KEY "dept_district_ptn" ("dept_id","district_id","ptn_id") ); INSERT INTO test_org_partner (dept_id,ptn_id,district_id,nsc_id) VALUES ('ADVB','10001','10','MMD'), ('ADVB','10002','10','MMD'), ('ADVB','10003','10','MMD'), ('ADVB','11001','11','MMD'), ('ADVB','11002','11','MMD'), ('ADVB','10740','21','MMD'), ('ADVB','10741','21','MMD'), ('ADVB','10429','22','MMD'), ('ADVB','10771','23','MMD'); CREATE OR REPLACE TABLE "test_kpi_measure" ( "the_date" date DEFAULT NULL, "ptn_id" char(5) CHARACTER SET utf8 NOT NULL DEFAULT '', "cco_stk_ttl" int(11) DEFAULT NULL, PRIMARY KEY ("the_date","ptn_id"), KEY "ptn_id_idx" ("ptn_id") ); INSERT INTO test_kpi_measure (the_date,ptn_id,cco_stk_ttl) VALUES (current_date-1,'10001',0), (current_date-1,'10002',1), (current_date-2,'10002',null), (current_date-1,'11001',2), (current_date-2,'11001',4), (current_date-1,'11003',1024), (current_date-2,'11003',2048), (current_date-1,'10580',8), (current_date-1,'10499',16), (current_date-1,'10740',32), (current_date-1,'10741',64), (current_date-2,'10741',128), (current_date-1,'10429',256), (current_date-1,'10771',512); {code} SQL with wrong result: {code:java} SELECT sub.the_date , org.dept_id , case when dis.region_id <=> null then 'nsc' when dis.district_id <=> null then 'reg' else 'dis' end as org_type , coalesce(dis.district_id, dis.region_id, 'MMD') as org_id , SUM(sub.cco_stk_ttl) as cco_stk_ttl FROM test_kpi_measure sub JOIN test_org_partner org ON org.ptn_id = sub.ptn_id JOIN test_org_district dis ON dis.dept_id = org.dept_id AND dis.district_id = org.district_id WHERE org.nsc_id = 'MMD' and org.dept_id IN ('ADVB') -- AND sub.the_date = CURRENT_DATE() - INTERVAL 1 DAY GROUP BY sub.the_date, dis.dept_id, dis.region_id, dis.district_id WITH ROLLUP {code} Code that works: {code:java} SELECT sub.the_date , org.dept_id , case when dis.region_id <=> null then 'nsc' when dis.district_id <=> null then 'reg' else 'dis' end as org_type , coalesce(dis.district_id, dis.region_id, 'MMD') as org_id , SUM(sub.cco_stk_ttl) as cco_stk_ttl FROM test_kpi_measure sub JOIN test_org_partner org ON org.ptn_id = sub.ptn_id JOIN test_org_district dis ON dis.dept_id = org.dept_id AND dis.district_id = org.district_id WHERE org.nsc_id = 'MMD' and org.dept_id IN ('ADVB') -- AND sub.the_date = CURRENT_DATE() - INTERVAL 1 DAY GROUP BY sub.the_date, org.dept_id, dis.region_id, dis.district_id WITH ROLLUP -- <<<< ONLY changing dis -> org {code} dis.dept_id = org.dept_id are equal, but showing different content with GROUP BY and ROLLUP. |
| Description |
A SQL returns invalid data if using field from "other" table:
Sample: {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-12 dis 10 ADVB 1 2021-08-12 dis 10 ADVB 0 2021-08-12 dis 11 ADVB 2 2021-08-12 dis 12 ADVB 2021-08-12 reg 1 ADVB 1 2021-08-12 reg 1 ADVB 2 2021-08-12 dis 21 ADVB 96 2021-08-12 dis 22 ADVB 256 2021-08-12 dis 23 ADVB 512 2021-08-12 reg 2 ADVB 864 2021-08-12 nsc MMD ADVB 867 {code} correct would be: {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-12 dis 10 ADVB 1 2021-08-12 dis 11 ADVB 2 2021-08-12 dis 12 ADVB 2021-08-12 reg 1 ADVB 3 2021-08-12 dis 21 ADVB 96 2021-08-12 dis 22 ADVB 256 2021-08-12 dis 23 ADVB 512 2021-08-12 reg 2 ADVB 864 2021-08-12 nsc MMD ADVB 867 {code} It looks like Test Data: {code:sql} SET sql_mode = 'REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI,ONLY_FULL_GROUP_BY' CREATE OR REPLACE TABLE test_kpi_date ( the_date date NOT NULL , PRIMARY KEY ("the_date") ); SELECT * FROM test_kpi_date tkd INSERT INTO test_kpi_date VALUES (current_date), (current_date-1), (current_date-2), (current_date-3); CREATE OR REPLACE TABLE test_org_district ( "id" int(11) 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") ); INSERT INTO test_org_district (nsc_id,dept_id,district_id,region_id) VALUES ('MMD','ADVB','10','1'), ('MMD','ADVB','11','1'), ('MMD','ADVB','21','2'), ('MMD','ADVB','22','2'); CREATE OR REPLACE TABLE "test_org_partner" ( "dept_id" char(4) CHARACTER SET utf8 NOT NULL, "ptn_id" char(5) CHARACTER SET utf8, "district_id" char(3) CHARACTER SET utf8 NOT NULL DEFAULT '0', "nsc_id" char(5) CHARACTER SET utf8 NOT NULL, PRIMARY KEY ("ptn_id","dept_id"), KEY "dept_ptn" ("dept_id","ptn_id"), KEY "dept_district_ptn" ("dept_id","district_id","ptn_id") ); INSERT INTO test_org_partner (dept_id,ptn_id,district_id,nsc_id) VALUES ('ADVB','10001','10','MMD'), ('ADVB','10002','10','MMD'), ('ADVB','10003','10','MMD'), ('ADVB','11001','11','MMD'), ('ADVB','11002','11','MMD'), ('ADVB','10740','21','MMD'), ('ADVB','10741','21','MMD'), ('ADVB','10429','22','MMD'), ('ADVB','10771','23','MMD'); CREATE OR REPLACE TABLE "test_kpi_measure" ( "the_date" date DEFAULT NULL, "ptn_id" char(5) CHARACTER SET utf8 NOT NULL DEFAULT '', "cco_stk_ttl" int(11) DEFAULT NULL, PRIMARY KEY ("the_date","ptn_id"), KEY "ptn_id_idx" ("ptn_id") ); INSERT INTO test_kpi_measure (the_date,ptn_id,cco_stk_ttl) VALUES (current_date-1,'10001',0), (current_date-1,'10002',1), (current_date-2,'10002',null), (current_date-1,'11001',2), (current_date-2,'11001',4), (current_date-1,'11003',1024), (current_date-2,'11003',2048), (current_date-1,'10580',8), (current_date-1,'10499',16), (current_date-1,'10740',32), (current_date-1,'10741',64), (current_date-2,'10741',128), (current_date-1,'10429',256), (current_date-1,'10771',512); {code} SQL with wrong result: {code:java} SELECT sub.the_date , org.dept_id , case when dis.region_id <=> null then 'nsc' when dis.district_id <=> null then 'reg' else 'dis' end as org_type , coalesce(dis.district_id, dis.region_id, 'MMD') as org_id , SUM(sub.cco_stk_ttl) as cco_stk_ttl FROM test_kpi_measure sub JOIN test_org_partner org ON org.ptn_id = sub.ptn_id JOIN test_org_district dis ON dis.dept_id = org.dept_id AND dis.district_id = org.district_id WHERE org.nsc_id = 'MMD' and org.dept_id IN ('ADVB') -- AND sub.the_date = CURRENT_DATE() - INTERVAL 1 DAY GROUP BY sub.the_date, dis.dept_id, dis.region_id, dis.district_id WITH ROLLUP {code} Code that works: {code:java} SELECT sub.the_date , org.dept_id , case when dis.region_id <=> null then 'nsc' when dis.district_id <=> null then 'reg' else 'dis' end as org_type , coalesce(dis.district_id, dis.region_id, 'MMD') as org_id , SUM(sub.cco_stk_ttl) as cco_stk_ttl FROM test_kpi_measure sub JOIN test_org_partner org ON org.ptn_id = sub.ptn_id JOIN test_org_district dis ON dis.dept_id = org.dept_id AND dis.district_id = org.district_id WHERE org.nsc_id = 'MMD' and org.dept_id IN ('ADVB') -- AND sub.the_date = CURRENT_DATE() - INTERVAL 1 DAY GROUP BY sub.the_date, org.dept_id, dis.region_id, dis.district_id WITH ROLLUP -- <<<< ONLY changing dis -> org {code} dis.dept_id = org.dept_id are equal, but showing different content with GROUP BY and ROLLUP. |
A SQL returns invalid data if using field from "other" table:
Sample: {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-12 dis 10 ADVB 1 2021-08-12 dis 10 ADVB 0 2021-08-12 dis 11 ADVB 2 2021-08-12 dis 12 ADVB 2021-08-12 reg 1 ADVB 1 2021-08-12 reg 1 ADVB 2 2021-08-12 dis 21 ADVB 96 2021-08-12 dis 22 ADVB 256 2021-08-12 dis 23 ADVB 512 2021-08-12 reg 2 ADVB 864 2021-08-12 nsc MMD ADVB 867 {code} correct would be: {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-12 dis 10 ADVB 1 2021-08-12 dis 11 ADVB 2 2021-08-12 dis 12 ADVB 2021-08-12 reg 1 ADVB 3 2021-08-12 dis 21 ADVB 96 2021-08-12 dis 22 ADVB 256 2021-08-12 dis 23 ADVB 512 2021-08-12 reg 2 ADVB 864 2021-08-12 nsc MMD ADVB 867 {code} It looks like Test Data: {code:sql} SET sql_mode = 'REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI,ONLY_FULL_GROUP_BY' CREATE OR REPLACE TABLE test_kpi_date ( the_date date NOT NULL , PRIMARY KEY ("the_date") ); SELECT * FROM test_kpi_date tkd INSERT INTO test_kpi_date VALUES (current_date), (current_date-1), (current_date-2), (current_date-3); CREATE OR REPLACE TABLE test_org_district ( "id" int(11) 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") ); INSERT INTO test_org_district (nsc_id,dept_id,district_id,region_id) VALUES ('MMD','ADVB','10','1'), ('MMD','ADVB','11','1'), ('MMD','ADVB','21','2'), ('MMD','ADVB','22','2'); CREATE OR REPLACE TABLE "test_org_partner" ( "dept_id" char(4) CHARACTER SET utf8 NOT NULL, "ptn_id" char(5) CHARACTER SET utf8, "district_id" char(3) CHARACTER SET utf8 NOT NULL DEFAULT '0', "nsc_id" char(5) CHARACTER SET utf8 NOT NULL, PRIMARY KEY ("ptn_id","dept_id"), KEY "dept_ptn" ("dept_id","ptn_id"), KEY "dept_district_ptn" ("dept_id","district_id","ptn_id") ); INSERT INTO test_org_partner (dept_id,ptn_id,district_id,nsc_id) VALUES ('ADVB','10001','10','MMD'), ('ADVB','10002','10','MMD'), ('ADVB','10003','10','MMD'), ('ADVB','11001','11','MMD'), ('ADVB','11002','11','MMD'), ('ADVB','10740','21','MMD'), ('ADVB','10741','21','MMD'), ('ADVB','10429','22','MMD'), ('ADVB','10771','23','MMD'); CREATE OR REPLACE TABLE "test_kpi_measure" ( "the_date" date DEFAULT NULL, "ptn_id" char(5) CHARACTER SET utf8 NOT NULL DEFAULT '', "cco_stk_ttl" int(11) DEFAULT NULL, PRIMARY KEY ("the_date","ptn_id"), KEY "ptn_id_idx" ("ptn_id") ); INSERT INTO test_kpi_measure (the_date,ptn_id,cco_stk_ttl) VALUES (current_date-1,'10001',4096), (current_date-1,'10002',1), (current_date-2,'10002',null), (current_date-1,'11001',2), (current_date-2,'11001',4), (current_date-1,'11003',1024), (current_date-2,'11003',2048), (current_date-1,'10580',8), (current_date-1,'10499',16), (current_date-1,'10740',32), (current_date-1,'10741',64), (current_date-2,'10741',128), (current_date-1,'10429',256), (current_date-1,'10771',512); {code} SQL with wrong result: {code:sql} SELECT org.the_date , org.org_type , org.org_id , org.dept_id , msr.`cco_stk_ttl` FROM ( SELECT cal.the_date , org.dept_id , case when org.region_id <=> null then 'nsc' when org.district_id <=> null then 'reg' else 'dis' end as org_type , coalesce(org.district_id, org.region_id, 'MMD') as org_id , org.district_id , org.region_id FROM test_kpi_date cal CROSS JOIN test_org_district org WHERE org.nsc_id = 'MMD' and org.dept_id IN ('ADVB') AND cal.the_date = CURRENT_DATE() - INTERVAL 1 DAY 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) ) org LEFT JOIN ( SELECT sub.the_date , dis.dept_id , dis.region_id , dis.district_id , SUM(sub.cco_stk_ttl) as cco_stk_ttl FROM test_kpi_measure sub JOIN test_org_partner org ON org.ptn_id = sub.ptn_id JOIN test_org_district dis ON dis.dept_id = org.dept_id AND dis.district_id = org.district_id WHERE dis.nsc_id = 'MMD' and dis.dept_id IN ('ADVB') -- AND sub.the_date = CURRENT_DATE() - INTERVAL 1 DAY GROUP BY sub.the_date, dis.dept_id, dis.region_id, dis.district_id WITH ROLLUP ) msr ON msr.the_date = org.the_date and msr.dept_id <=> org.dept_id and msr.region_id <=> org.region_id and msr.district_id <=> org.district_id {code} Code that works: {code:sql} SELECT sub.the_date , org.dept_id , case when dis.region_id <=> null then 'nsc' when dis.district_id <=> null then 'reg' else 'dis' end as org_type , coalesce(dis.district_id, dis.region_id, 'MMD') as org_id , SUM(sub.cco_stk_ttl) as cco_stk_ttl FROM test_kpi_measure sub JOIN test_org_partner org ON org.ptn_id = sub.ptn_id JOIN test_org_district dis ON dis.dept_id = org.dept_id AND dis.district_id = org.district_id WHERE org.nsc_id = 'MMD' and org.dept_id IN ('ADVB') -- AND sub.the_date = CURRENT_DATE() - INTERVAL 1 DAY GROUP BY sub.the_date, org.dept_id, dis.region_id, dis.district_id WITH ROLLUP -- <<<< ONLY changing dis -> org {code} dis.dept_id = org.dept_id are equal, but showing different content with GROUP BY and ROLLUP. |
| Description |
A SQL returns invalid data if using field from "other" table:
Sample: {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-12 dis 10 ADVB 1 2021-08-12 dis 10 ADVB 0 2021-08-12 dis 11 ADVB 2 2021-08-12 dis 12 ADVB 2021-08-12 reg 1 ADVB 1 2021-08-12 reg 1 ADVB 2 2021-08-12 dis 21 ADVB 96 2021-08-12 dis 22 ADVB 256 2021-08-12 dis 23 ADVB 512 2021-08-12 reg 2 ADVB 864 2021-08-12 nsc MMD ADVB 867 {code} correct would be: {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-12 dis 10 ADVB 1 2021-08-12 dis 11 ADVB 2 2021-08-12 dis 12 ADVB 2021-08-12 reg 1 ADVB 3 2021-08-12 dis 21 ADVB 96 2021-08-12 dis 22 ADVB 256 2021-08-12 dis 23 ADVB 512 2021-08-12 reg 2 ADVB 864 2021-08-12 nsc MMD ADVB 867 {code} It looks like Test Data: {code:sql} SET sql_mode = 'REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI,ONLY_FULL_GROUP_BY' CREATE OR REPLACE TABLE test_kpi_date ( the_date date NOT NULL , PRIMARY KEY ("the_date") ); SELECT * FROM test_kpi_date tkd INSERT INTO test_kpi_date VALUES (current_date), (current_date-1), (current_date-2), (current_date-3); CREATE OR REPLACE TABLE test_org_district ( "id" int(11) 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") ); INSERT INTO test_org_district (nsc_id,dept_id,district_id,region_id) VALUES ('MMD','ADVB','10','1'), ('MMD','ADVB','11','1'), ('MMD','ADVB','21','2'), ('MMD','ADVB','22','2'); CREATE OR REPLACE TABLE "test_org_partner" ( "dept_id" char(4) CHARACTER SET utf8 NOT NULL, "ptn_id" char(5) CHARACTER SET utf8, "district_id" char(3) CHARACTER SET utf8 NOT NULL DEFAULT '0', "nsc_id" char(5) CHARACTER SET utf8 NOT NULL, PRIMARY KEY ("ptn_id","dept_id"), KEY "dept_ptn" ("dept_id","ptn_id"), KEY "dept_district_ptn" ("dept_id","district_id","ptn_id") ); INSERT INTO test_org_partner (dept_id,ptn_id,district_id,nsc_id) VALUES ('ADVB','10001','10','MMD'), ('ADVB','10002','10','MMD'), ('ADVB','10003','10','MMD'), ('ADVB','11001','11','MMD'), ('ADVB','11002','11','MMD'), ('ADVB','10740','21','MMD'), ('ADVB','10741','21','MMD'), ('ADVB','10429','22','MMD'), ('ADVB','10771','23','MMD'); CREATE OR REPLACE TABLE "test_kpi_measure" ( "the_date" date DEFAULT NULL, "ptn_id" char(5) CHARACTER SET utf8 NOT NULL DEFAULT '', "cco_stk_ttl" int(11) DEFAULT NULL, PRIMARY KEY ("the_date","ptn_id"), KEY "ptn_id_idx" ("ptn_id") ); INSERT INTO test_kpi_measure (the_date,ptn_id,cco_stk_ttl) VALUES (current_date-1,'10001',4096), (current_date-1,'10002',1), (current_date-2,'10002',null), (current_date-1,'11001',2), (current_date-2,'11001',4), (current_date-1,'11003',1024), (current_date-2,'11003',2048), (current_date-1,'10580',8), (current_date-1,'10499',16), (current_date-1,'10740',32), (current_date-1,'10741',64), (current_date-2,'10741',128), (current_date-1,'10429',256), (current_date-1,'10771',512); {code} SQL with wrong result: {code:sql} SELECT org.the_date , org.org_type , org.org_id , org.dept_id , msr.`cco_stk_ttl` FROM ( SELECT cal.the_date , org.dept_id , case when org.region_id <=> null then 'nsc' when org.district_id <=> null then 'reg' else 'dis' end as org_type , coalesce(org.district_id, org.region_id, 'MMD') as org_id , org.district_id , org.region_id FROM test_kpi_date cal CROSS JOIN test_org_district org WHERE org.nsc_id = 'MMD' and org.dept_id IN ('ADVB') AND cal.the_date = CURRENT_DATE() - INTERVAL 1 DAY 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) ) org LEFT JOIN ( SELECT sub.the_date , dis.dept_id , dis.region_id , dis.district_id , SUM(sub.cco_stk_ttl) as cco_stk_ttl FROM test_kpi_measure sub JOIN test_org_partner org ON org.ptn_id = sub.ptn_id JOIN test_org_district dis ON dis.dept_id = org.dept_id AND dis.district_id = org.district_id WHERE dis.nsc_id = 'MMD' and dis.dept_id IN ('ADVB') -- AND sub.the_date = CURRENT_DATE() - INTERVAL 1 DAY GROUP BY sub.the_date, dis.dept_id, dis.region_id, dis.district_id WITH ROLLUP ) msr ON msr.the_date = org.the_date and msr.dept_id <=> org.dept_id and msr.region_id <=> org.region_id and msr.district_id <=> org.district_id {code} Code that works: {code:sql} SELECT sub.the_date , org.dept_id , case when dis.region_id <=> null then 'nsc' when dis.district_id <=> null then 'reg' else 'dis' end as org_type , coalesce(dis.district_id, dis.region_id, 'MMD') as org_id , SUM(sub.cco_stk_ttl) as cco_stk_ttl FROM test_kpi_measure sub JOIN test_org_partner org ON org.ptn_id = sub.ptn_id JOIN test_org_district dis ON dis.dept_id = org.dept_id AND dis.district_id = org.district_id WHERE org.nsc_id = 'MMD' and org.dept_id IN ('ADVB') -- AND sub.the_date = CURRENT_DATE() - INTERVAL 1 DAY GROUP BY sub.the_date, org.dept_id, dis.region_id, dis.district_id WITH ROLLUP -- <<<< ONLY changing dis -> org {code} dis.dept_id = org.dept_id are equal, but showing different content with GROUP BY and ROLLUP. |
A SQL returns invalid data if using field from "other" table:
Sample: {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-12 dis 10 ADVB 1 2021-08-12 dis 10 ADVB 0 2021-08-12 dis 11 ADVB 2 2021-08-12 dis 12 ADVB 2021-08-12 reg 1 ADVB 1 2021-08-12 reg 1 ADVB 2 2021-08-12 dis 21 ADVB 96 2021-08-12 dis 22 ADVB 256 2021-08-12 dis 23 ADVB 512 2021-08-12 reg 2 ADVB 864 2021-08-12 nsc MMD ADVB 867 {code} correct would be: {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-12 dis 10 ADVB 1 2021-08-12 dis 11 ADVB 2 2021-08-12 dis 12 ADVB 2021-08-12 reg 1 ADVB 3 2021-08-12 dis 21 ADVB 96 2021-08-12 dis 22 ADVB 256 2021-08-12 dis 23 ADVB 512 2021-08-12 reg 2 ADVB 864 2021-08-12 nsc MMD ADVB 867 {code} It looks like Test Data: {code:sql} SET sql_mode = 'REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI,ONLY_FULL_GROUP_BY' CREATE OR REPLACE TABLE test_kpi_date ( the_date date NOT NULL , PRIMARY KEY ("the_date") ); SELECT * FROM test_kpi_date tkd INSERT INTO test_kpi_date VALUES (current_date), (current_date-1), (current_date-2), (current_date-3); CREATE OR REPLACE TABLE test_org_district ( "id" int(11) 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") ); INSERT INTO test_org_district (nsc_id,dept_id,district_id,region_id) VALUES ('MMD','ADVB','10','1'), ('MMD','ADVB','11','1'), ('MMD','ADVB','21','2'), ('MMD','ADVB','22','2'); CREATE OR REPLACE TABLE "test_org_partner" ( "dept_id" char(4) CHARACTER SET utf8 NOT NULL, "ptn_id" char(5) CHARACTER SET utf8, "district_id" char(3) CHARACTER SET utf8 NOT NULL DEFAULT '0', "nsc_id" char(5) CHARACTER SET utf8 NOT NULL, PRIMARY KEY ("ptn_id","dept_id"), KEY "dept_ptn" ("dept_id","ptn_id"), KEY "dept_district_ptn" ("dept_id","district_id","ptn_id") ); INSERT INTO test_org_partner (dept_id,ptn_id,district_id,nsc_id) VALUES ('ADVB','10001','10','MMD'), ('ADVB','10002','10','MMD'), ('ADVB','10003','10','MMD'), ('ADVB','11001','11','MMD'), ('ADVB','11002','11','MMD'), ('ADVB','10740','21','MMD'), ('ADVB','10741','21','MMD'), ('ADVB','10429','22','MMD'), ('ADVB','10771','23','MMD'); CREATE OR REPLACE TABLE "test_kpi_measure" ( "the_date" date DEFAULT NULL, "ptn_id" char(5) CHARACTER SET utf8 NOT NULL DEFAULT '', "cco_stk_ttl" int(11) DEFAULT NULL, PRIMARY KEY ("the_date","ptn_id"), KEY "ptn_id_idx" ("ptn_id") ); INSERT INTO test_kpi_measure (the_date,ptn_id,cco_stk_ttl) VALUES (current_date-1,'10001',4096), (current_date-1,'10002',1), (current_date-2,'10002',null), (current_date-1,'11001',2), (current_date-2,'11001',4), (current_date-1,'11003',1024), (current_date-2,'11003',2048), (current_date-1,'10580',8), (current_date-1,'10499',16), (current_date-1,'10740',32), (current_date-1,'10741',64), (current_date-2,'10741',128), (current_date-1,'10429',256), (current_date-1,'10771',512); {code} SQL with wrong result: {code:sql} SELECT org.the_date , org.org_type , org.org_id , org.dept_id , msr.`cco_stk_ttl` FROM ( SELECT cal.the_date , org.dept_id , case when org.region_id <=> null then 'nsc' when org.district_id <=> null then 'reg' else 'dis' end as org_type , coalesce(org.district_id, org.region_id, 'MMD') as org_id , org.district_id , org.region_id FROM test_kpi_date cal CROSS JOIN test_org_district org WHERE org.nsc_id = 'MMD' and org.dept_id IN ('ADVB') AND cal.the_date = CURRENT_DATE() - INTERVAL 1 DAY 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) ) org LEFT JOIN ( SELECT sub.the_date , dis.dept_id , dis.region_id , dis.district_id , SUM(sub.cco_stk_ttl) as cco_stk_ttl FROM test_kpi_measure sub JOIN test_org_partner org ON org.ptn_id = sub.ptn_id JOIN test_org_district dis ON dis.dept_id = org.dept_id AND dis.district_id = org.district_id WHERE dis.nsc_id = 'MMD' and dis.dept_id IN ('ADVB') -- AND sub.the_date = CURRENT_DATE() - INTERVAL 1 DAY GROUP BY sub.the_date, dis.dept_id, dis.region_id, dis.district_id WITH ROLLUP ) msr ON msr.the_date = org.the_date and msr.dept_id <=> org.dept_id and msr.region_id <=> org.region_id and msr.district_id <=> org.district_id {code} Code that works: {code:sql} CREATE OR REPLACE TABLE test_kpi_date ( "id" int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, the_date date NOT NULL -- , PRIMARY KEY ("the_date") -- , UNIQUE (the_date) ); {code} dis.dept_id = org.dept_id are equal, but showing different content with GROUP BY and ROLLUP. |
| Description |
A SQL returns invalid data if using field from "other" table:
Sample: {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-12 dis 10 ADVB 1 2021-08-12 dis 10 ADVB 0 2021-08-12 dis 11 ADVB 2 2021-08-12 dis 12 ADVB 2021-08-12 reg 1 ADVB 1 2021-08-12 reg 1 ADVB 2 2021-08-12 dis 21 ADVB 96 2021-08-12 dis 22 ADVB 256 2021-08-12 dis 23 ADVB 512 2021-08-12 reg 2 ADVB 864 2021-08-12 nsc MMD ADVB 867 {code} correct would be: {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-12 dis 10 ADVB 1 2021-08-12 dis 11 ADVB 2 2021-08-12 dis 12 ADVB 2021-08-12 reg 1 ADVB 3 2021-08-12 dis 21 ADVB 96 2021-08-12 dis 22 ADVB 256 2021-08-12 dis 23 ADVB 512 2021-08-12 reg 2 ADVB 864 2021-08-12 nsc MMD ADVB 867 {code} It looks like Test Data: {code:sql} SET sql_mode = 'REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI,ONLY_FULL_GROUP_BY' CREATE OR REPLACE TABLE test_kpi_date ( the_date date NOT NULL , PRIMARY KEY ("the_date") ); SELECT * FROM test_kpi_date tkd INSERT INTO test_kpi_date VALUES (current_date), (current_date-1), (current_date-2), (current_date-3); CREATE OR REPLACE TABLE test_org_district ( "id" int(11) 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") ); INSERT INTO test_org_district (nsc_id,dept_id,district_id,region_id) VALUES ('MMD','ADVB','10','1'), ('MMD','ADVB','11','1'), ('MMD','ADVB','21','2'), ('MMD','ADVB','22','2'); CREATE OR REPLACE TABLE "test_org_partner" ( "dept_id" char(4) CHARACTER SET utf8 NOT NULL, "ptn_id" char(5) CHARACTER SET utf8, "district_id" char(3) CHARACTER SET utf8 NOT NULL DEFAULT '0', "nsc_id" char(5) CHARACTER SET utf8 NOT NULL, PRIMARY KEY ("ptn_id","dept_id"), KEY "dept_ptn" ("dept_id","ptn_id"), KEY "dept_district_ptn" ("dept_id","district_id","ptn_id") ); INSERT INTO test_org_partner (dept_id,ptn_id,district_id,nsc_id) VALUES ('ADVB','10001','10','MMD'), ('ADVB','10002','10','MMD'), ('ADVB','10003','10','MMD'), ('ADVB','11001','11','MMD'), ('ADVB','11002','11','MMD'), ('ADVB','10740','21','MMD'), ('ADVB','10741','21','MMD'), ('ADVB','10429','22','MMD'), ('ADVB','10771','23','MMD'); CREATE OR REPLACE TABLE "test_kpi_measure" ( "the_date" date DEFAULT NULL, "ptn_id" char(5) CHARACTER SET utf8 NOT NULL DEFAULT '', "cco_stk_ttl" int(11) DEFAULT NULL, PRIMARY KEY ("the_date","ptn_id"), KEY "ptn_id_idx" ("ptn_id") ); INSERT INTO test_kpi_measure (the_date,ptn_id,cco_stk_ttl) VALUES (current_date-1,'10001',4096), (current_date-1,'10002',1), (current_date-2,'10002',null), (current_date-1,'11001',2), (current_date-2,'11001',4), (current_date-1,'11003',1024), (current_date-2,'11003',2048), (current_date-1,'10580',8), (current_date-1,'10499',16), (current_date-1,'10740',32), (current_date-1,'10741',64), (current_date-2,'10741',128), (current_date-1,'10429',256), (current_date-1,'10771',512); {code} SQL with wrong result: {code:sql} SELECT org.the_date , org.org_type , org.org_id , org.dept_id , msr.`cco_stk_ttl` FROM ( SELECT cal.the_date , org.dept_id , case when org.region_id <=> null then 'nsc' when org.district_id <=> null then 'reg' else 'dis' end as org_type , coalesce(org.district_id, org.region_id, 'MMD') as org_id , org.district_id , org.region_id FROM test_kpi_date cal CROSS JOIN test_org_district org WHERE org.nsc_id = 'MMD' and org.dept_id IN ('ADVB') AND cal.the_date = CURRENT_DATE() - INTERVAL 1 DAY 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) ) org LEFT JOIN ( SELECT sub.the_date , dis.dept_id , dis.region_id , dis.district_id , SUM(sub.cco_stk_ttl) as cco_stk_ttl FROM test_kpi_measure sub JOIN test_org_partner org ON org.ptn_id = sub.ptn_id JOIN test_org_district dis ON dis.dept_id = org.dept_id AND dis.district_id = org.district_id WHERE dis.nsc_id = 'MMD' and dis.dept_id IN ('ADVB') -- AND sub.the_date = CURRENT_DATE() - INTERVAL 1 DAY GROUP BY sub.the_date, dis.dept_id, dis.region_id, dis.district_id WITH ROLLUP ) msr ON msr.the_date = org.the_date and msr.dept_id <=> org.dept_id and msr.region_id <=> org.region_id and msr.district_id <=> org.district_id {code} Code that works: {code:sql} CREATE OR REPLACE TABLE test_kpi_date ( "id" int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, the_date date NOT NULL -- , PRIMARY KEY ("the_date") -- , UNIQUE (the_date) ); {code} dis.dept_id = org.dept_id are equal, but showing different content with GROUP BY and ROLLUP. |
A SQL returns invalid data if using field from "other" table:
Sample: {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-12 dis 10 ADVB 1 2021-08-12 dis 10 ADVB 0 2021-08-12 dis 11 ADVB 2 2021-08-12 dis 12 ADVB 2021-08-12 reg 1 ADVB 1 2021-08-12 reg 1 ADVB 2 2021-08-12 dis 21 ADVB 96 2021-08-12 dis 22 ADVB 256 2021-08-12 dis 23 ADVB 512 2021-08-12 reg 2 ADVB 864 2021-08-12 nsc MMD ADVB 867 {code} correct would be: {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-12 dis 10 ADVB 1 2021-08-12 dis 11 ADVB 2 2021-08-12 dis 12 ADVB 2021-08-12 reg 1 ADVB 3 2021-08-12 dis 21 ADVB 96 2021-08-12 dis 22 ADVB 256 2021-08-12 dis 23 ADVB 512 2021-08-12 reg 2 ADVB 864 2021-08-12 nsc MMD ADVB 867 {code} It looks like Test Data: {code:sql} SET sql_mode = 'REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI,ONLY_FULL_GROUP_BY' CREATE OR REPLACE TABLE test_kpi_date ( the_date date NOT NULL , PRIMARY KEY ("the_date") ); SELECT * FROM test_kpi_date tkd INSERT INTO test_kpi_date VALUES (current_date), (current_date-1), (current_date-2), (current_date-3); CREATE OR REPLACE TABLE test_org_district ( "id" int(11) 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") ); INSERT INTO test_org_district (nsc_id,dept_id,district_id,region_id) VALUES ('MMD','ADVB','10','1'), ('MMD','ADVB','11','1'), ('MMD','ADVB','21','2'), ('MMD','ADVB','22','2'); CREATE OR REPLACE TABLE "test_org_partner" ( "dept_id" char(4) CHARACTER SET utf8 NOT NULL, "ptn_id" char(5) CHARACTER SET utf8, "district_id" char(3) CHARACTER SET utf8 NOT NULL DEFAULT '0', "nsc_id" char(5) CHARACTER SET utf8 NOT NULL, PRIMARY KEY ("ptn_id","dept_id"), KEY "dept_ptn" ("dept_id","ptn_id"), KEY "dept_district_ptn" ("dept_id","district_id","ptn_id") ); INSERT INTO test_org_partner (dept_id,ptn_id,district_id,nsc_id) VALUES ('ADVB','10001','10','MMD'), ('ADVB','10002','10','MMD'), ('ADVB','10003','10','MMD'), ('ADVB','11001','11','MMD'), ('ADVB','11002','11','MMD'), ('ADVB','10740','21','MMD'), ('ADVB','10741','21','MMD'), ('ADVB','10429','22','MMD'), ('ADVB','10771','23','MMD'); CREATE OR REPLACE TABLE "test_kpi_measure" ( "the_date" date DEFAULT NULL, "ptn_id" char(5) CHARACTER SET utf8 NOT NULL DEFAULT '', "cco_stk_ttl" int(11) DEFAULT NULL, PRIMARY KEY ("the_date","ptn_id"), KEY "ptn_id_idx" ("ptn_id") ); INSERT INTO test_kpi_measure (the_date,ptn_id,cco_stk_ttl) VALUES (current_date-1,'10001',4096), (current_date-1,'10002',1), (current_date-2,'10002',null), (current_date-1,'11001',2), (current_date-2,'11001',4), (current_date-1,'11003',1024), (current_date-2,'11003',2048), (current_date-1,'10580',8), (current_date-1,'10499',16), (current_date-1,'10740',32), (current_date-1,'10741',64), (current_date-2,'10741',128), (current_date-1,'10429',256), (current_date-1,'10771',512); {code} SQL with wrong result: {code:sql} SELECT org.the_date , org.org_type , org.org_id , org.dept_id , msr.`cco_stk_ttl` FROM ( SELECT cal.the_date , org.dept_id , case when org.region_id <=> null then 'nsc' when org.district_id <=> null then 'reg' else 'dis' end as org_type , coalesce(org.district_id, org.region_id, 'MMD') as org_id , org.district_id , org.region_id FROM test_kpi_date cal CROSS JOIN test_org_district org WHERE org.nsc_id = 'MMD' and org.dept_id IN ('ADVB') AND cal.the_date = CURRENT_DATE() - INTERVAL 1 DAY 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) ) org LEFT JOIN ( SELECT sub.the_date , dis.dept_id , dis.region_id , dis.district_id , SUM(sub.cco_stk_ttl) as cco_stk_ttl FROM test_kpi_measure sub JOIN test_org_partner org ON org.ptn_id = sub.ptn_id JOIN test_org_district dis ON dis.dept_id = org.dept_id AND dis.district_id = org.district_id WHERE dis.nsc_id = 'MMD' and dis.dept_id IN ('ADVB') -- AND sub.the_date = CURRENT_DATE() - INTERVAL 1 DAY GROUP BY sub.the_date, dis.dept_id, dis.region_id, dis.district_id WITH ROLLUP ) msr ON msr.the_date = org.the_date and msr.dept_id <=> org.dept_id and msr.region_id <=> org.region_id and msr.district_id <=> org.district_id {code} Code that works: {code:sql} CREATE OR REPLACE TABLE test_kpi_date ( "id" int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, the_date date NOT NULL -- , PRIMARY KEY ("the_date") -- , UNIQUE (the_date) ); {code} You can see, we try to use different definitions with the same behavior (wrong) when the_date get an index (PRIMARY/UNIQUE). Without UNIQUE/PRIMARY Index on the_date it works fine. |
| Summary | subquery with groupby and ROLLUP returns incorrect results | subquery with groupby and ROLLUP returns incorrect results on LEFT JOIN on INDEXED values |
| Description |
A SQL returns invalid data if using field from "other" table:
Sample: {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-12 dis 10 ADVB 1 2021-08-12 dis 10 ADVB 0 2021-08-12 dis 11 ADVB 2 2021-08-12 dis 12 ADVB 2021-08-12 reg 1 ADVB 1 2021-08-12 reg 1 ADVB 2 2021-08-12 dis 21 ADVB 96 2021-08-12 dis 22 ADVB 256 2021-08-12 dis 23 ADVB 512 2021-08-12 reg 2 ADVB 864 2021-08-12 nsc MMD ADVB 867 {code} correct would be: {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-12 dis 10 ADVB 1 2021-08-12 dis 11 ADVB 2 2021-08-12 dis 12 ADVB 2021-08-12 reg 1 ADVB 3 2021-08-12 dis 21 ADVB 96 2021-08-12 dis 22 ADVB 256 2021-08-12 dis 23 ADVB 512 2021-08-12 reg 2 ADVB 864 2021-08-12 nsc MMD ADVB 867 {code} It looks like Test Data: {code:sql} SET sql_mode = 'REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI,ONLY_FULL_GROUP_BY' CREATE OR REPLACE TABLE test_kpi_date ( the_date date NOT NULL , PRIMARY KEY ("the_date") ); SELECT * FROM test_kpi_date tkd INSERT INTO test_kpi_date VALUES (current_date), (current_date-1), (current_date-2), (current_date-3); CREATE OR REPLACE TABLE test_org_district ( "id" int(11) 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") ); INSERT INTO test_org_district (nsc_id,dept_id,district_id,region_id) VALUES ('MMD','ADVB','10','1'), ('MMD','ADVB','11','1'), ('MMD','ADVB','21','2'), ('MMD','ADVB','22','2'); CREATE OR REPLACE TABLE "test_org_partner" ( "dept_id" char(4) CHARACTER SET utf8 NOT NULL, "ptn_id" char(5) CHARACTER SET utf8, "district_id" char(3) CHARACTER SET utf8 NOT NULL DEFAULT '0', "nsc_id" char(5) CHARACTER SET utf8 NOT NULL, PRIMARY KEY ("ptn_id","dept_id"), KEY "dept_ptn" ("dept_id","ptn_id"), KEY "dept_district_ptn" ("dept_id","district_id","ptn_id") ); INSERT INTO test_org_partner (dept_id,ptn_id,district_id,nsc_id) VALUES ('ADVB','10001','10','MMD'), ('ADVB','10002','10','MMD'), ('ADVB','10003','10','MMD'), ('ADVB','11001','11','MMD'), ('ADVB','11002','11','MMD'), ('ADVB','10740','21','MMD'), ('ADVB','10741','21','MMD'), ('ADVB','10429','22','MMD'), ('ADVB','10771','23','MMD'); CREATE OR REPLACE TABLE "test_kpi_measure" ( "the_date" date DEFAULT NULL, "ptn_id" char(5) CHARACTER SET utf8 NOT NULL DEFAULT '', "cco_stk_ttl" int(11) DEFAULT NULL, PRIMARY KEY ("the_date","ptn_id"), KEY "ptn_id_idx" ("ptn_id") ); INSERT INTO test_kpi_measure (the_date,ptn_id,cco_stk_ttl) VALUES (current_date-1,'10001',4096), (current_date-1,'10002',1), (current_date-2,'10002',null), (current_date-1,'11001',2), (current_date-2,'11001',4), (current_date-1,'11003',1024), (current_date-2,'11003',2048), (current_date-1,'10580',8), (current_date-1,'10499',16), (current_date-1,'10740',32), (current_date-1,'10741',64), (current_date-2,'10741',128), (current_date-1,'10429',256), (current_date-1,'10771',512); {code} SQL with wrong result: {code:sql} SELECT org.the_date , org.org_type , org.org_id , org.dept_id , msr.`cco_stk_ttl` FROM ( SELECT cal.the_date , org.dept_id , case when org.region_id <=> null then 'nsc' when org.district_id <=> null then 'reg' else 'dis' end as org_type , coalesce(org.district_id, org.region_id, 'MMD') as org_id , org.district_id , org.region_id FROM test_kpi_date cal CROSS JOIN test_org_district org WHERE org.nsc_id = 'MMD' and org.dept_id IN ('ADVB') AND cal.the_date = CURRENT_DATE() - INTERVAL 1 DAY 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) ) org LEFT JOIN ( SELECT sub.the_date , dis.dept_id , dis.region_id , dis.district_id , SUM(sub.cco_stk_ttl) as cco_stk_ttl FROM test_kpi_measure sub JOIN test_org_partner org ON org.ptn_id = sub.ptn_id JOIN test_org_district dis ON dis.dept_id = org.dept_id AND dis.district_id = org.district_id WHERE dis.nsc_id = 'MMD' and dis.dept_id IN ('ADVB') -- AND sub.the_date = CURRENT_DATE() - INTERVAL 1 DAY GROUP BY sub.the_date, dis.dept_id, dis.region_id, dis.district_id WITH ROLLUP ) msr ON msr.the_date = org.the_date and msr.dept_id <=> org.dept_id and msr.region_id <=> org.region_id and msr.district_id <=> org.district_id {code} Code that works: {code:sql} CREATE OR REPLACE TABLE test_kpi_date ( "id" int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, the_date date NOT NULL -- , PRIMARY KEY ("the_date") -- , UNIQUE (the_date) ); {code} You can see, we try to use different definitions with the same behavior (wrong) when the_date get an index (PRIMARY/UNIQUE). Without UNIQUE/PRIMARY Index on the_date it works fine. |
A SQL returns invalid data if using field from "other" table:
Sample: {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-12 dis 10 ADVB 1 2021-08-12 dis 10 ADVB 0 2021-08-12 dis 11 ADVB 2 2021-08-12 dis 12 ADVB <null> 2021-08-12 reg 1 ADVB 1 2021-08-12 reg 1 ADVB 2 2021-08-12 dis 21 ADVB 96 2021-08-12 dis 22 ADVB 256 2021-08-12 dis 23 ADVB 512 2021-08-12 reg 2 ADVB 864 2021-08-12 nsc MMD ADVB 867 {code} correct would be: {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-12 dis 10 ADVB 1 2021-08-12 dis 11 ADVB 2 2021-08-12 dis 12 ADVB <null> 2021-08-12 reg 1 ADVB 3 2021-08-12 dis 21 ADVB 96 2021-08-12 dis 22 ADVB 256 2021-08-12 dis 23 ADVB 512 2021-08-12 reg 2 ADVB 864 2021-08-12 nsc MMD ADVB 867 {code} It looks like Test Data: {code:sql} SET sql_mode = 'REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI,ONLY_FULL_GROUP_BY' CREATE OR REPLACE TABLE test_kpi_date ( the_date date NOT NULL , PRIMARY KEY ("the_date") ); SELECT * FROM test_kpi_date tkd INSERT INTO test_kpi_date VALUES (current_date), (current_date-1), (current_date-2), (current_date-3); CREATE OR REPLACE TABLE test_org_district ( "id" int(11) 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") ); INSERT INTO test_org_district (nsc_id,dept_id,district_id,region_id) VALUES ('MMD','ADVB','10','1'), ('MMD','ADVB','11','1'), ('MMD','ADVB','21','2'), ('MMD','ADVB','22','2'); CREATE OR REPLACE TABLE "test_org_partner" ( "dept_id" char(4) CHARACTER SET utf8 NOT NULL, "ptn_id" char(5) CHARACTER SET utf8, "district_id" char(3) CHARACTER SET utf8 NOT NULL DEFAULT '0', "nsc_id" char(5) CHARACTER SET utf8 NOT NULL, PRIMARY KEY ("ptn_id","dept_id"), KEY "dept_ptn" ("dept_id","ptn_id"), KEY "dept_district_ptn" ("dept_id","district_id","ptn_id") ); INSERT INTO test_org_partner (dept_id,ptn_id,district_id,nsc_id) VALUES ('ADVB','10001','10','MMD'), ('ADVB','10002','10','MMD'), ('ADVB','10003','10','MMD'), ('ADVB','11001','11','MMD'), ('ADVB','11002','11','MMD'), ('ADVB','10740','21','MMD'), ('ADVB','10741','21','MMD'), ('ADVB','10429','22','MMD'), ('ADVB','10771','23','MMD'); CREATE OR REPLACE TABLE "test_kpi_measure" ( "the_date" date DEFAULT NULL, "ptn_id" char(5) CHARACTER SET utf8 NOT NULL DEFAULT '', "cco_stk_ttl" int(11) DEFAULT NULL, PRIMARY KEY ("the_date","ptn_id"), KEY "ptn_id_idx" ("ptn_id") ); INSERT INTO test_kpi_measure (the_date,ptn_id,cco_stk_ttl) VALUES (current_date-1,'10001',4096), (current_date-1,'10002',1), (current_date-2,'10002',null), (current_date-1,'11001',2), (current_date-2,'11001',4), (current_date-1,'11003',1024), (current_date-2,'11003',2048), (current_date-1,'10580',8), (current_date-1,'10499',16), (current_date-1,'10740',32), (current_date-1,'10741',64), (current_date-2,'10741',128), (current_date-1,'10429',256), (current_date-1,'10771',512); {code} SQL with wrong result: {code:sql} SELECT org.the_date , org.org_type , org.org_id , org.dept_id , msr.`cco_stk_ttl` FROM ( SELECT cal.the_date , org.dept_id , case when org.region_id <=> null then 'nsc' when org.district_id <=> null then 'reg' else 'dis' end as org_type , coalesce(org.district_id, org.region_id, 'MMD') as org_id , org.district_id , org.region_id FROM test_kpi_date cal CROSS JOIN test_org_district org WHERE org.nsc_id = 'MMD' and org.dept_id IN ('ADVB') AND cal.the_date = CURRENT_DATE() - INTERVAL 1 DAY 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) ) org LEFT JOIN ( SELECT sub.the_date , dis.dept_id , dis.region_id , dis.district_id , SUM(sub.cco_stk_ttl) as cco_stk_ttl FROM test_kpi_measure sub JOIN test_org_partner org ON org.ptn_id = sub.ptn_id JOIN test_org_district dis ON dis.dept_id = org.dept_id AND dis.district_id = org.district_id WHERE dis.nsc_id = 'MMD' and dis.dept_id IN ('ADVB') -- AND sub.the_date = CURRENT_DATE() - INTERVAL 1 DAY GROUP BY sub.the_date, dis.dept_id, dis.region_id, dis.district_id WITH ROLLUP ) msr ON msr.the_date = org.the_date and msr.dept_id <=> org.dept_id and msr.region_id <=> org.region_id and msr.district_id <=> org.district_id {code} Code that works: {code:sql} CREATE OR REPLACE TABLE test_kpi_date ( "id" int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, the_date date NOT NULL -- , PRIMARY KEY ("the_date") -- , UNIQUE (the_date) ); {code} You can see, we try to use different definitions with the same behavior (wrong) when the_date get an index (PRIMARY/UNIQUE). Without UNIQUE/PRIMARY Index on the_date it works fine. |
| Description |
A SQL returns invalid data if using field from "other" table:
Sample: {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-12 dis 10 ADVB 1 2021-08-12 dis 10 ADVB 0 2021-08-12 dis 11 ADVB 2 2021-08-12 dis 12 ADVB <null> 2021-08-12 reg 1 ADVB 1 2021-08-12 reg 1 ADVB 2 2021-08-12 dis 21 ADVB 96 2021-08-12 dis 22 ADVB 256 2021-08-12 dis 23 ADVB 512 2021-08-12 reg 2 ADVB 864 2021-08-12 nsc MMD ADVB 867 {code} correct would be: {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-12 dis 10 ADVB 1 2021-08-12 dis 11 ADVB 2 2021-08-12 dis 12 ADVB <null> 2021-08-12 reg 1 ADVB 3 2021-08-12 dis 21 ADVB 96 2021-08-12 dis 22 ADVB 256 2021-08-12 dis 23 ADVB 512 2021-08-12 reg 2 ADVB 864 2021-08-12 nsc MMD ADVB 867 {code} It looks like Test Data: {code:sql} SET sql_mode = 'REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI,ONLY_FULL_GROUP_BY' CREATE OR REPLACE TABLE test_kpi_date ( the_date date NOT NULL , PRIMARY KEY ("the_date") ); SELECT * FROM test_kpi_date tkd INSERT INTO test_kpi_date VALUES (current_date), (current_date-1), (current_date-2), (current_date-3); CREATE OR REPLACE TABLE test_org_district ( "id" int(11) 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") ); INSERT INTO test_org_district (nsc_id,dept_id,district_id,region_id) VALUES ('MMD','ADVB','10','1'), ('MMD','ADVB','11','1'), ('MMD','ADVB','21','2'), ('MMD','ADVB','22','2'); CREATE OR REPLACE TABLE "test_org_partner" ( "dept_id" char(4) CHARACTER SET utf8 NOT NULL, "ptn_id" char(5) CHARACTER SET utf8, "district_id" char(3) CHARACTER SET utf8 NOT NULL DEFAULT '0', "nsc_id" char(5) CHARACTER SET utf8 NOT NULL, PRIMARY KEY ("ptn_id","dept_id"), KEY "dept_ptn" ("dept_id","ptn_id"), KEY "dept_district_ptn" ("dept_id","district_id","ptn_id") ); INSERT INTO test_org_partner (dept_id,ptn_id,district_id,nsc_id) VALUES ('ADVB','10001','10','MMD'), ('ADVB','10002','10','MMD'), ('ADVB','10003','10','MMD'), ('ADVB','11001','11','MMD'), ('ADVB','11002','11','MMD'), ('ADVB','10740','21','MMD'), ('ADVB','10741','21','MMD'), ('ADVB','10429','22','MMD'), ('ADVB','10771','23','MMD'); CREATE OR REPLACE TABLE "test_kpi_measure" ( "the_date" date DEFAULT NULL, "ptn_id" char(5) CHARACTER SET utf8 NOT NULL DEFAULT '', "cco_stk_ttl" int(11) DEFAULT NULL, PRIMARY KEY ("the_date","ptn_id"), KEY "ptn_id_idx" ("ptn_id") ); INSERT INTO test_kpi_measure (the_date,ptn_id,cco_stk_ttl) VALUES (current_date-1,'10001',4096), (current_date-1,'10002',1), (current_date-2,'10002',null), (current_date-1,'11001',2), (current_date-2,'11001',4), (current_date-1,'11003',1024), (current_date-2,'11003',2048), (current_date-1,'10580',8), (current_date-1,'10499',16), (current_date-1,'10740',32), (current_date-1,'10741',64), (current_date-2,'10741',128), (current_date-1,'10429',256), (current_date-1,'10771',512); {code} SQL with wrong result: {code:sql} SELECT org.the_date , org.org_type , org.org_id , org.dept_id , msr.`cco_stk_ttl` FROM ( SELECT cal.the_date , org.dept_id , case when org.region_id <=> null then 'nsc' when org.district_id <=> null then 'reg' else 'dis' end as org_type , coalesce(org.district_id, org.region_id, 'MMD') as org_id , org.district_id , org.region_id FROM test_kpi_date cal CROSS JOIN test_org_district org WHERE org.nsc_id = 'MMD' and org.dept_id IN ('ADVB') AND cal.the_date = CURRENT_DATE() - INTERVAL 1 DAY 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) ) org LEFT JOIN ( SELECT sub.the_date , dis.dept_id , dis.region_id , dis.district_id , SUM(sub.cco_stk_ttl) as cco_stk_ttl FROM test_kpi_measure sub JOIN test_org_partner org ON org.ptn_id = sub.ptn_id JOIN test_org_district dis ON dis.dept_id = org.dept_id AND dis.district_id = org.district_id WHERE dis.nsc_id = 'MMD' and dis.dept_id IN ('ADVB') -- AND sub.the_date = CURRENT_DATE() - INTERVAL 1 DAY GROUP BY sub.the_date, dis.dept_id, dis.region_id, dis.district_id WITH ROLLUP ) msr ON msr.the_date = org.the_date and msr.dept_id <=> org.dept_id and msr.region_id <=> org.region_id and msr.district_id <=> org.district_id {code} Code that works: {code:sql} CREATE OR REPLACE TABLE test_kpi_date ( "id" int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, the_date date NOT NULL -- , PRIMARY KEY ("the_date") -- , UNIQUE (the_date) ); {code} You can see, we try to use different definitions with the same behavior (wrong) when the_date get an index (PRIMARY/UNIQUE). Without UNIQUE/PRIMARY Index on the_date it works fine. |
Sample:
{code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-12 dis 10 ADVB 1 2021-08-12 dis 10 ADVB 0 2021-08-12 dis 11 ADVB 2 2021-08-12 dis 12 ADVB <null> 2021-08-12 reg 1 ADVB 1 2021-08-12 reg 1 ADVB 2 2021-08-12 dis 21 ADVB 96 2021-08-12 dis 22 ADVB 256 2021-08-12 dis 23 ADVB 512 2021-08-12 reg 2 ADVB 864 2021-08-12 nsc MMD ADVB 867 {code} correct would be: {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-12 dis 10 ADVB 1 2021-08-12 dis 11 ADVB 2 2021-08-12 dis 12 ADVB <null> 2021-08-12 reg 1 ADVB 3 2021-08-12 dis 21 ADVB 96 2021-08-12 dis 22 ADVB 256 2021-08-12 dis 23 ADVB 512 2021-08-12 reg 2 ADVB 864 2021-08-12 nsc MMD ADVB 867 {code} It looks like Test Data: {code:sql} SET sql_mode = 'REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI,ONLY_FULL_GROUP_BY' CREATE OR REPLACE TABLE test_kpi_date ( the_date date NOT NULL , PRIMARY KEY ("the_date") ); SELECT * FROM test_kpi_date tkd INSERT INTO test_kpi_date VALUES (current_date), (current_date-1), (current_date-2), (current_date-3); CREATE OR REPLACE TABLE test_org_district ( "id" int(11) 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") ); INSERT INTO test_org_district (nsc_id,dept_id,district_id,region_id) VALUES ('MMD','ADVB','10','1'), ('MMD','ADVB','11','1'), ('MMD','ADVB','21','2'), ('MMD','ADVB','22','2'); CREATE OR REPLACE TABLE "test_org_partner" ( "dept_id" char(4) CHARACTER SET utf8 NOT NULL, "ptn_id" char(5) CHARACTER SET utf8, "district_id" char(3) CHARACTER SET utf8 NOT NULL DEFAULT '0', "nsc_id" char(5) CHARACTER SET utf8 NOT NULL, PRIMARY KEY ("ptn_id","dept_id"), KEY "dept_ptn" ("dept_id","ptn_id"), KEY "dept_district_ptn" ("dept_id","district_id","ptn_id") ); INSERT INTO test_org_partner (dept_id,ptn_id,district_id,nsc_id) VALUES ('ADVB','10001','10','MMD'), ('ADVB','10002','10','MMD'), ('ADVB','10003','10','MMD'), ('ADVB','11001','11','MMD'), ('ADVB','11002','11','MMD'), ('ADVB','10740','21','MMD'), ('ADVB','10741','21','MMD'), ('ADVB','10429','22','MMD'), ('ADVB','10771','23','MMD'); CREATE OR REPLACE TABLE "test_kpi_measure" ( "the_date" date DEFAULT NULL, "ptn_id" char(5) CHARACTER SET utf8 NOT NULL DEFAULT '', "cco_stk_ttl" int(11) DEFAULT NULL, PRIMARY KEY ("the_date","ptn_id"), KEY "ptn_id_idx" ("ptn_id") ); INSERT INTO test_kpi_measure (the_date,ptn_id,cco_stk_ttl) VALUES (current_date-1,'10001',4096), (current_date-1,'10002',1), (current_date-2,'10002',null), (current_date-1,'11001',2), (current_date-2,'11001',4), (current_date-1,'11003',1024), (current_date-2,'11003',2048), (current_date-1,'10580',8), (current_date-1,'10499',16), (current_date-1,'10740',32), (current_date-1,'10741',64), (current_date-2,'10741',128), (current_date-1,'10429',256), (current_date-1,'10771',512); {code} SQL with wrong result: {code:sql} SELECT org.the_date , org.org_type , org.org_id , org.dept_id , msr.`cco_stk_ttl` FROM ( SELECT cal.the_date , org.dept_id , case when org.region_id <=> null then 'nsc' when org.district_id <=> null then 'reg' else 'dis' end as org_type , coalesce(org.district_id, org.region_id, 'MMD') as org_id , org.district_id , org.region_id FROM test_kpi_date cal CROSS JOIN test_org_district org WHERE org.nsc_id = 'MMD' and org.dept_id IN ('ADVB') AND cal.the_date = CURRENT_DATE() - INTERVAL 1 DAY 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) ) org LEFT JOIN ( SELECT sub.the_date , dis.dept_id , dis.region_id , dis.district_id , SUM(sub.cco_stk_ttl) as cco_stk_ttl FROM test_kpi_measure sub JOIN test_org_partner org ON org.ptn_id = sub.ptn_id JOIN test_org_district dis ON dis.dept_id = org.dept_id AND dis.district_id = org.district_id WHERE dis.nsc_id = 'MMD' and dis.dept_id IN ('ADVB') -- AND sub.the_date = CURRENT_DATE() - INTERVAL 1 DAY GROUP BY sub.the_date, dis.dept_id, dis.region_id, dis.district_id WITH ROLLUP ) msr ON msr.the_date = org.the_date and msr.dept_id <=> org.dept_id and msr.region_id <=> org.region_id and msr.district_id <=> org.district_id {code} Code that works: {code:sql} CREATE OR REPLACE TABLE test_kpi_date ( "id" int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, the_date date NOT NULL -- , PRIMARY KEY ("the_date") -- , UNIQUE (the_date) ); {code} You can see, we try to use different definitions with the same behavior (wrong) when the_date get an index (PRIMARY/UNIQUE). Without UNIQUE/PRIMARY Index on the_date it works fine. |
| Description |
Sample:
{code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-12 dis 10 ADVB 1 2021-08-12 dis 10 ADVB 0 2021-08-12 dis 11 ADVB 2 2021-08-12 dis 12 ADVB <null> 2021-08-12 reg 1 ADVB 1 2021-08-12 reg 1 ADVB 2 2021-08-12 dis 21 ADVB 96 2021-08-12 dis 22 ADVB 256 2021-08-12 dis 23 ADVB 512 2021-08-12 reg 2 ADVB 864 2021-08-12 nsc MMD ADVB 867 {code} correct would be: {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-12 dis 10 ADVB 1 2021-08-12 dis 11 ADVB 2 2021-08-12 dis 12 ADVB <null> 2021-08-12 reg 1 ADVB 3 2021-08-12 dis 21 ADVB 96 2021-08-12 dis 22 ADVB 256 2021-08-12 dis 23 ADVB 512 2021-08-12 reg 2 ADVB 864 2021-08-12 nsc MMD ADVB 867 {code} It looks like Test Data: {code:sql} SET sql_mode = 'REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI,ONLY_FULL_GROUP_BY' CREATE OR REPLACE TABLE test_kpi_date ( the_date date NOT NULL , PRIMARY KEY ("the_date") ); SELECT * FROM test_kpi_date tkd INSERT INTO test_kpi_date VALUES (current_date), (current_date-1), (current_date-2), (current_date-3); CREATE OR REPLACE TABLE test_org_district ( "id" int(11) 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") ); INSERT INTO test_org_district (nsc_id,dept_id,district_id,region_id) VALUES ('MMD','ADVB','10','1'), ('MMD','ADVB','11','1'), ('MMD','ADVB','21','2'), ('MMD','ADVB','22','2'); CREATE OR REPLACE TABLE "test_org_partner" ( "dept_id" char(4) CHARACTER SET utf8 NOT NULL, "ptn_id" char(5) CHARACTER SET utf8, "district_id" char(3) CHARACTER SET utf8 NOT NULL DEFAULT '0', "nsc_id" char(5) CHARACTER SET utf8 NOT NULL, PRIMARY KEY ("ptn_id","dept_id"), KEY "dept_ptn" ("dept_id","ptn_id"), KEY "dept_district_ptn" ("dept_id","district_id","ptn_id") ); INSERT INTO test_org_partner (dept_id,ptn_id,district_id,nsc_id) VALUES ('ADVB','10001','10','MMD'), ('ADVB','10002','10','MMD'), ('ADVB','10003','10','MMD'), ('ADVB','11001','11','MMD'), ('ADVB','11002','11','MMD'), ('ADVB','10740','21','MMD'), ('ADVB','10741','21','MMD'), ('ADVB','10429','22','MMD'), ('ADVB','10771','23','MMD'); CREATE OR REPLACE TABLE "test_kpi_measure" ( "the_date" date DEFAULT NULL, "ptn_id" char(5) CHARACTER SET utf8 NOT NULL DEFAULT '', "cco_stk_ttl" int(11) DEFAULT NULL, PRIMARY KEY ("the_date","ptn_id"), KEY "ptn_id_idx" ("ptn_id") ); INSERT INTO test_kpi_measure (the_date,ptn_id,cco_stk_ttl) VALUES (current_date-1,'10001',4096), (current_date-1,'10002',1), (current_date-2,'10002',null), (current_date-1,'11001',2), (current_date-2,'11001',4), (current_date-1,'11003',1024), (current_date-2,'11003',2048), (current_date-1,'10580',8), (current_date-1,'10499',16), (current_date-1,'10740',32), (current_date-1,'10741',64), (current_date-2,'10741',128), (current_date-1,'10429',256), (current_date-1,'10771',512); {code} SQL with wrong result: {code:sql} SELECT org.the_date , org.org_type , org.org_id , org.dept_id , msr.`cco_stk_ttl` FROM ( SELECT cal.the_date , org.dept_id , case when org.region_id <=> null then 'nsc' when org.district_id <=> null then 'reg' else 'dis' end as org_type , coalesce(org.district_id, org.region_id, 'MMD') as org_id , org.district_id , org.region_id FROM test_kpi_date cal CROSS JOIN test_org_district org WHERE org.nsc_id = 'MMD' and org.dept_id IN ('ADVB') AND cal.the_date = CURRENT_DATE() - INTERVAL 1 DAY 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) ) org LEFT JOIN ( SELECT sub.the_date , dis.dept_id , dis.region_id , dis.district_id , SUM(sub.cco_stk_ttl) as cco_stk_ttl FROM test_kpi_measure sub JOIN test_org_partner org ON org.ptn_id = sub.ptn_id JOIN test_org_district dis ON dis.dept_id = org.dept_id AND dis.district_id = org.district_id WHERE dis.nsc_id = 'MMD' and dis.dept_id IN ('ADVB') -- AND sub.the_date = CURRENT_DATE() - INTERVAL 1 DAY GROUP BY sub.the_date, dis.dept_id, dis.region_id, dis.district_id WITH ROLLUP ) msr ON msr.the_date = org.the_date and msr.dept_id <=> org.dept_id and msr.region_id <=> org.region_id and msr.district_id <=> org.district_id {code} Code that works: {code:sql} CREATE OR REPLACE TABLE test_kpi_date ( "id" int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, the_date date NOT NULL -- , PRIMARY KEY ("the_date") -- , UNIQUE (the_date) ); {code} You can see, we try to use different definitions with the same behavior (wrong) when the_date get an index (PRIMARY/UNIQUE). Without UNIQUE/PRIMARY Index on the_date it works fine. |
Sample:
{code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-12 dis 10 ADVB 1 2021-08-12 dis 10 ADVB 4096 2021-08-12 dis 11 ADVB 2 2021-08-12 dis 12 ADVB <null> 2021-08-12 reg 1 ADVB 1 2021-08-12 reg 1 ADVB 4098 2021-08-12 dis 21 ADVB 96 2021-08-12 dis 22 ADVB 256 2021-08-12 dis 23 ADVB 512 2021-08-12 reg 2 ADVB 864 2021-08-12 nsc MMD ADVB 4963 {code} correct would be: {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-12 dis 10 ADVB 4097 2021-08-12 dis 11 ADVB 2 2021-08-12 dis 12 ADVB <null> 2021-08-12 reg 1 ADVB 4099 2021-08-12 dis 21 ADVB 96 2021-08-12 dis 22 ADVB 256 2021-08-12 dis 23 ADVB 512 2021-08-12 reg 2 ADVB 864 2021-08-12 nsc MMD ADVB 4963 {code} It looks like Test Data: {code:sql} SET sql_mode = 'REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI,ONLY_FULL_GROUP_BY' CREATE OR REPLACE TABLE test_kpi_date ( the_date date NOT NULL , PRIMARY KEY ("the_date") ); SELECT * FROM test_kpi_date tkd INSERT INTO test_kpi_date VALUES (current_date), (current_date-1), (current_date-2), (current_date-3); CREATE OR REPLACE TABLE test_org_district ( "id" int(11) 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") ); INSERT INTO test_org_district (nsc_id,dept_id,district_id,region_id) VALUES ('MMD','ADVB','10','1'), ('MMD','ADVB','11','1'), ('MMD','ADVB','21','2'), ('MMD','ADVB','22','2'); CREATE OR REPLACE TABLE "test_org_partner" ( "dept_id" char(4) CHARACTER SET utf8 NOT NULL, "ptn_id" char(5) CHARACTER SET utf8, "district_id" char(3) CHARACTER SET utf8 NOT NULL DEFAULT '0', "nsc_id" char(5) CHARACTER SET utf8 NOT NULL, PRIMARY KEY ("ptn_id","dept_id"), KEY "dept_ptn" ("dept_id","ptn_id"), KEY "dept_district_ptn" ("dept_id","district_id","ptn_id") ); INSERT INTO test_org_partner (dept_id,ptn_id,district_id,nsc_id) VALUES ('ADVB','10001','10','MMD'), ('ADVB','10002','10','MMD'), ('ADVB','10003','10','MMD'), ('ADVB','11001','11','MMD'), ('ADVB','11002','11','MMD'), ('ADVB','10740','21','MMD'), ('ADVB','10741','21','MMD'), ('ADVB','10429','22','MMD'), ('ADVB','10771','23','MMD'); CREATE OR REPLACE TABLE "test_kpi_measure" ( "the_date" date DEFAULT NULL, "ptn_id" char(5) CHARACTER SET utf8 NOT NULL DEFAULT '', "cco_stk_ttl" int(11) DEFAULT NULL, PRIMARY KEY ("the_date","ptn_id"), KEY "ptn_id_idx" ("ptn_id") ); INSERT INTO test_kpi_measure (the_date,ptn_id,cco_stk_ttl) VALUES (current_date-1,'10001',4096), (current_date-1,'10002',1), (current_date-2,'10002',null), (current_date-1,'11001',2), (current_date-2,'11001',4), (current_date-1,'11003',1024), (current_date-2,'11003',2048), (current_date-1,'10580',8), (current_date-1,'10499',16), (current_date-1,'10740',32), (current_date-1,'10741',64), (current_date-2,'10741',128), (current_date-1,'10429',256), (current_date-1,'10771',512); {code} SQL with wrong result: {code:sql} SELECT org.the_date , org.org_type , org.org_id , org.dept_id , msr.`cco_stk_ttl` FROM ( SELECT cal.the_date , org.dept_id , case when org.region_id <=> null then 'nsc' when org.district_id <=> null then 'reg' else 'dis' end as org_type , coalesce(org.district_id, org.region_id, 'MMD') as org_id , org.district_id , org.region_id FROM test_kpi_date cal CROSS JOIN test_org_district org WHERE org.nsc_id = 'MMD' and org.dept_id IN ('ADVB') AND cal.the_date = CURRENT_DATE() - INTERVAL 1 DAY 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) ) org LEFT JOIN ( SELECT sub.the_date , dis.dept_id , dis.region_id , dis.district_id , SUM(sub.cco_stk_ttl) as cco_stk_ttl FROM test_kpi_measure sub JOIN test_org_partner org ON org.ptn_id = sub.ptn_id JOIN test_org_district dis ON dis.dept_id = org.dept_id AND dis.district_id = org.district_id WHERE dis.nsc_id = 'MMD' and dis.dept_id IN ('ADVB') -- AND sub.the_date = CURRENT_DATE() - INTERVAL 1 DAY GROUP BY sub.the_date, dis.dept_id, dis.region_id, dis.district_id WITH ROLLUP ) msr ON msr.the_date = org.the_date and msr.dept_id <=> org.dept_id and msr.region_id <=> org.region_id and msr.district_id <=> org.district_id {code} Code that works: {code:sql} CREATE OR REPLACE TABLE test_kpi_date ( "id" int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, the_date date NOT NULL -- , PRIMARY KEY ("the_date") -- , UNIQUE (the_date) ); {code} You can see, we try to use different definitions with the same behavior (wrong) when the_date get an index (PRIMARY/UNIQUE). Without UNIQUE/PRIMARY Index on the_date it works fine. |
| Description |
Sample:
{code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-12 dis 10 ADVB 1 2021-08-12 dis 10 ADVB 4096 2021-08-12 dis 11 ADVB 2 2021-08-12 dis 12 ADVB <null> 2021-08-12 reg 1 ADVB 1 2021-08-12 reg 1 ADVB 4098 2021-08-12 dis 21 ADVB 96 2021-08-12 dis 22 ADVB 256 2021-08-12 dis 23 ADVB 512 2021-08-12 reg 2 ADVB 864 2021-08-12 nsc MMD ADVB 4963 {code} correct would be: {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-12 dis 10 ADVB 4097 2021-08-12 dis 11 ADVB 2 2021-08-12 dis 12 ADVB <null> 2021-08-12 reg 1 ADVB 4099 2021-08-12 dis 21 ADVB 96 2021-08-12 dis 22 ADVB 256 2021-08-12 dis 23 ADVB 512 2021-08-12 reg 2 ADVB 864 2021-08-12 nsc MMD ADVB 4963 {code} It looks like Test Data: {code:sql} SET sql_mode = 'REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI,ONLY_FULL_GROUP_BY' CREATE OR REPLACE TABLE test_kpi_date ( the_date date NOT NULL , PRIMARY KEY ("the_date") ); SELECT * FROM test_kpi_date tkd INSERT INTO test_kpi_date VALUES (current_date), (current_date-1), (current_date-2), (current_date-3); CREATE OR REPLACE TABLE test_org_district ( "id" int(11) 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") ); INSERT INTO test_org_district (nsc_id,dept_id,district_id,region_id) VALUES ('MMD','ADVB','10','1'), ('MMD','ADVB','11','1'), ('MMD','ADVB','21','2'), ('MMD','ADVB','22','2'); CREATE OR REPLACE TABLE "test_org_partner" ( "dept_id" char(4) CHARACTER SET utf8 NOT NULL, "ptn_id" char(5) CHARACTER SET utf8, "district_id" char(3) CHARACTER SET utf8 NOT NULL DEFAULT '0', "nsc_id" char(5) CHARACTER SET utf8 NOT NULL, PRIMARY KEY ("ptn_id","dept_id"), KEY "dept_ptn" ("dept_id","ptn_id"), KEY "dept_district_ptn" ("dept_id","district_id","ptn_id") ); INSERT INTO test_org_partner (dept_id,ptn_id,district_id,nsc_id) VALUES ('ADVB','10001','10','MMD'), ('ADVB','10002','10','MMD'), ('ADVB','10003','10','MMD'), ('ADVB','11001','11','MMD'), ('ADVB','11002','11','MMD'), ('ADVB','10740','21','MMD'), ('ADVB','10741','21','MMD'), ('ADVB','10429','22','MMD'), ('ADVB','10771','23','MMD'); CREATE OR REPLACE TABLE "test_kpi_measure" ( "the_date" date DEFAULT NULL, "ptn_id" char(5) CHARACTER SET utf8 NOT NULL DEFAULT '', "cco_stk_ttl" int(11) DEFAULT NULL, PRIMARY KEY ("the_date","ptn_id"), KEY "ptn_id_idx" ("ptn_id") ); INSERT INTO test_kpi_measure (the_date,ptn_id,cco_stk_ttl) VALUES (current_date-1,'10001',4096), (current_date-1,'10002',1), (current_date-2,'10002',null), (current_date-1,'11001',2), (current_date-2,'11001',4), (current_date-1,'11003',1024), (current_date-2,'11003',2048), (current_date-1,'10580',8), (current_date-1,'10499',16), (current_date-1,'10740',32), (current_date-1,'10741',64), (current_date-2,'10741',128), (current_date-1,'10429',256), (current_date-1,'10771',512); {code} SQL with wrong result: {code:sql} SELECT org.the_date , org.org_type , org.org_id , org.dept_id , msr.`cco_stk_ttl` FROM ( SELECT cal.the_date , org.dept_id , case when org.region_id <=> null then 'nsc' when org.district_id <=> null then 'reg' else 'dis' end as org_type , coalesce(org.district_id, org.region_id, 'MMD') as org_id , org.district_id , org.region_id FROM test_kpi_date cal CROSS JOIN test_org_district org WHERE org.nsc_id = 'MMD' and org.dept_id IN ('ADVB') AND cal.the_date = CURRENT_DATE() - INTERVAL 1 DAY 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) ) org LEFT JOIN ( SELECT sub.the_date , dis.dept_id , dis.region_id , dis.district_id , SUM(sub.cco_stk_ttl) as cco_stk_ttl FROM test_kpi_measure sub JOIN test_org_partner org ON org.ptn_id = sub.ptn_id JOIN test_org_district dis ON dis.dept_id = org.dept_id AND dis.district_id = org.district_id WHERE dis.nsc_id = 'MMD' and dis.dept_id IN ('ADVB') -- AND sub.the_date = CURRENT_DATE() - INTERVAL 1 DAY GROUP BY sub.the_date, dis.dept_id, dis.region_id, dis.district_id WITH ROLLUP ) msr ON msr.the_date = org.the_date and msr.dept_id <=> org.dept_id and msr.region_id <=> org.region_id and msr.district_id <=> org.district_id {code} Code that works: {code:sql} CREATE OR REPLACE TABLE test_kpi_date ( "id" int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, the_date date NOT NULL -- , PRIMARY KEY ("the_date") -- , UNIQUE (the_date) ); {code} You can see, we try to use different definitions with the same behavior (wrong) when the_date get an index (PRIMARY/UNIQUE). Without UNIQUE/PRIMARY Index on the_date it works fine. |
Sample with
CREATE OR REPLACE TABLE test_kpi_date ( the_date date NOT NULL PRIMARY KEY ); {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-12 dis 10 ADVB 1 2021-08-12 dis 10 ADVB 4096 2021-08-12 dis 11 ADVB 2 2021-08-12 dis 12 ADVB <null> 2021-08-12 reg 1 ADVB 1 2021-08-12 reg 1 ADVB 4098 2021-08-12 dis 21 ADVB 96 2021-08-12 dis 22 ADVB 256 2021-08-12 dis 23 ADVB 512 2021-08-12 reg 2 ADVB 864 2021-08-12 nsc MMD ADVB 4963 {code} correct would be: {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-12 dis 10 ADVB 4097 2021-08-12 dis 11 ADVB 2 2021-08-12 dis 12 ADVB <null> 2021-08-12 reg 1 ADVB 4099 2021-08-12 dis 21 ADVB 96 2021-08-12 dis 22 ADVB 256 2021-08-12 dis 23 ADVB 512 2021-08-12 reg 2 ADVB 864 2021-08-12 nsc MMD ADVB 4963 {code} It looks like Test Data: {code:sql} SET sql_mode = 'REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI,ONLY_FULL_GROUP_BY' CREATE OR REPLACE TABLE test_kpi_date ( the_date date NOT NULL , PRIMARY KEY ("the_date") ); SELECT * FROM test_kpi_date tkd INSERT INTO test_kpi_date VALUES (current_date), (current_date-1), (current_date-2), (current_date-3); CREATE OR REPLACE TABLE test_org_district ( "id" int(11) 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") ); INSERT INTO test_org_district (nsc_id,dept_id,district_id,region_id) VALUES ('MMD','ADVB','10','1'), ('MMD','ADVB','11','1'), ('MMD','ADVB','21','2'), ('MMD','ADVB','22','2'); CREATE OR REPLACE TABLE "test_org_partner" ( "dept_id" char(4) CHARACTER SET utf8 NOT NULL, "ptn_id" char(5) CHARACTER SET utf8, "district_id" char(3) CHARACTER SET utf8 NOT NULL DEFAULT '0', "nsc_id" char(5) CHARACTER SET utf8 NOT NULL, PRIMARY KEY ("ptn_id","dept_id"), KEY "dept_ptn" ("dept_id","ptn_id"), KEY "dept_district_ptn" ("dept_id","district_id","ptn_id") ); INSERT INTO test_org_partner (dept_id,ptn_id,district_id,nsc_id) VALUES ('ADVB','10001','10','MMD'), ('ADVB','10002','10','MMD'), ('ADVB','10003','10','MMD'), ('ADVB','11001','11','MMD'), ('ADVB','11002','11','MMD'), ('ADVB','10740','21','MMD'), ('ADVB','10741','21','MMD'), ('ADVB','10429','22','MMD'), ('ADVB','10771','23','MMD'); CREATE OR REPLACE TABLE "test_kpi_measure" ( "the_date" date DEFAULT NULL, "ptn_id" char(5) CHARACTER SET utf8 NOT NULL DEFAULT '', "cco_stk_ttl" int(11) DEFAULT NULL, PRIMARY KEY ("the_date","ptn_id"), KEY "ptn_id_idx" ("ptn_id") ); INSERT INTO test_kpi_measure (the_date,ptn_id,cco_stk_ttl) VALUES (current_date-1,'10001',4096), (current_date-1,'10002',1), (current_date-2,'10002',null), (current_date-1,'11001',2), (current_date-2,'11001',4), (current_date-1,'11003',1024), (current_date-2,'11003',2048), (current_date-1,'10580',8), (current_date-1,'10499',16), (current_date-1,'10740',32), (current_date-1,'10741',64), (current_date-2,'10741',128), (current_date-1,'10429',256), (current_date-1,'10771',512); {code} SQL with wrong result: {code:sql} SELECT org.the_date , org.org_type , org.org_id , org.dept_id , msr.`cco_stk_ttl` FROM ( SELECT cal.the_date , org.dept_id , case when org.region_id <=> null then 'nsc' when org.district_id <=> null then 'reg' else 'dis' end as org_type , coalesce(org.district_id, org.region_id, 'MMD') as org_id , org.district_id , org.region_id FROM test_kpi_date cal CROSS JOIN test_org_district org WHERE org.nsc_id = 'MMD' and org.dept_id IN ('ADVB') AND cal.the_date = CURRENT_DATE() - INTERVAL 1 DAY 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) ) org LEFT JOIN ( SELECT sub.the_date , dis.dept_id , dis.region_id , dis.district_id , SUM(sub.cco_stk_ttl) as cco_stk_ttl FROM test_kpi_measure sub JOIN test_org_partner org ON org.ptn_id = sub.ptn_id JOIN test_org_district dis ON dis.dept_id = org.dept_id AND dis.district_id = org.district_id WHERE dis.nsc_id = 'MMD' and dis.dept_id IN ('ADVB') -- AND sub.the_date = CURRENT_DATE() - INTERVAL 1 DAY GROUP BY sub.the_date, dis.dept_id, dis.region_id, dis.district_id WITH ROLLUP ) msr ON msr.the_date = org.the_date and msr.dept_id <=> org.dept_id and msr.region_id <=> org.region_id and msr.district_id <=> org.district_id {code} Code that works: {code:sql} CREATE OR REPLACE TABLE test_kpi_date ( "id" int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, the_date date NOT NULL -- , PRIMARY KEY ("the_date") -- , UNIQUE (the_date) ); {code} You can see, we try to use different definitions with the same behavior (wrong) when the_date get an index (PRIMARY/UNIQUE). Without UNIQUE/PRIMARY Index on the_date it works fine. |
| Description |
Sample with
CREATE OR REPLACE TABLE test_kpi_date ( the_date date NOT NULL PRIMARY KEY ); {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-12 dis 10 ADVB 1 2021-08-12 dis 10 ADVB 4096 2021-08-12 dis 11 ADVB 2 2021-08-12 dis 12 ADVB <null> 2021-08-12 reg 1 ADVB 1 2021-08-12 reg 1 ADVB 4098 2021-08-12 dis 21 ADVB 96 2021-08-12 dis 22 ADVB 256 2021-08-12 dis 23 ADVB 512 2021-08-12 reg 2 ADVB 864 2021-08-12 nsc MMD ADVB 4963 {code} correct would be: {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-12 dis 10 ADVB 4097 2021-08-12 dis 11 ADVB 2 2021-08-12 dis 12 ADVB <null> 2021-08-12 reg 1 ADVB 4099 2021-08-12 dis 21 ADVB 96 2021-08-12 dis 22 ADVB 256 2021-08-12 dis 23 ADVB 512 2021-08-12 reg 2 ADVB 864 2021-08-12 nsc MMD ADVB 4963 {code} It looks like Test Data: {code:sql} SET sql_mode = 'REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI,ONLY_FULL_GROUP_BY' CREATE OR REPLACE TABLE test_kpi_date ( the_date date NOT NULL , PRIMARY KEY ("the_date") ); SELECT * FROM test_kpi_date tkd INSERT INTO test_kpi_date VALUES (current_date), (current_date-1), (current_date-2), (current_date-3); CREATE OR REPLACE TABLE test_org_district ( "id" int(11) 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") ); INSERT INTO test_org_district (nsc_id,dept_id,district_id,region_id) VALUES ('MMD','ADVB','10','1'), ('MMD','ADVB','11','1'), ('MMD','ADVB','21','2'), ('MMD','ADVB','22','2'); CREATE OR REPLACE TABLE "test_org_partner" ( "dept_id" char(4) CHARACTER SET utf8 NOT NULL, "ptn_id" char(5) CHARACTER SET utf8, "district_id" char(3) CHARACTER SET utf8 NOT NULL DEFAULT '0', "nsc_id" char(5) CHARACTER SET utf8 NOT NULL, PRIMARY KEY ("ptn_id","dept_id"), KEY "dept_ptn" ("dept_id","ptn_id"), KEY "dept_district_ptn" ("dept_id","district_id","ptn_id") ); INSERT INTO test_org_partner (dept_id,ptn_id,district_id,nsc_id) VALUES ('ADVB','10001','10','MMD'), ('ADVB','10002','10','MMD'), ('ADVB','10003','10','MMD'), ('ADVB','11001','11','MMD'), ('ADVB','11002','11','MMD'), ('ADVB','10740','21','MMD'), ('ADVB','10741','21','MMD'), ('ADVB','10429','22','MMD'), ('ADVB','10771','23','MMD'); CREATE OR REPLACE TABLE "test_kpi_measure" ( "the_date" date DEFAULT NULL, "ptn_id" char(5) CHARACTER SET utf8 NOT NULL DEFAULT '', "cco_stk_ttl" int(11) DEFAULT NULL, PRIMARY KEY ("the_date","ptn_id"), KEY "ptn_id_idx" ("ptn_id") ); INSERT INTO test_kpi_measure (the_date,ptn_id,cco_stk_ttl) VALUES (current_date-1,'10001',4096), (current_date-1,'10002',1), (current_date-2,'10002',null), (current_date-1,'11001',2), (current_date-2,'11001',4), (current_date-1,'11003',1024), (current_date-2,'11003',2048), (current_date-1,'10580',8), (current_date-1,'10499',16), (current_date-1,'10740',32), (current_date-1,'10741',64), (current_date-2,'10741',128), (current_date-1,'10429',256), (current_date-1,'10771',512); {code} SQL with wrong result: {code:sql} SELECT org.the_date , org.org_type , org.org_id , org.dept_id , msr.`cco_stk_ttl` FROM ( SELECT cal.the_date , org.dept_id , case when org.region_id <=> null then 'nsc' when org.district_id <=> null then 'reg' else 'dis' end as org_type , coalesce(org.district_id, org.region_id, 'MMD') as org_id , org.district_id , org.region_id FROM test_kpi_date cal CROSS JOIN test_org_district org WHERE org.nsc_id = 'MMD' and org.dept_id IN ('ADVB') AND cal.the_date = CURRENT_DATE() - INTERVAL 1 DAY 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) ) org LEFT JOIN ( SELECT sub.the_date , dis.dept_id , dis.region_id , dis.district_id , SUM(sub.cco_stk_ttl) as cco_stk_ttl FROM test_kpi_measure sub JOIN test_org_partner org ON org.ptn_id = sub.ptn_id JOIN test_org_district dis ON dis.dept_id = org.dept_id AND dis.district_id = org.district_id WHERE dis.nsc_id = 'MMD' and dis.dept_id IN ('ADVB') -- AND sub.the_date = CURRENT_DATE() - INTERVAL 1 DAY GROUP BY sub.the_date, dis.dept_id, dis.region_id, dis.district_id WITH ROLLUP ) msr ON msr.the_date = org.the_date and msr.dept_id <=> org.dept_id and msr.region_id <=> org.region_id and msr.district_id <=> org.district_id {code} Code that works: {code:sql} CREATE OR REPLACE TABLE test_kpi_date ( "id" int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, the_date date NOT NULL -- , PRIMARY KEY ("the_date") -- , UNIQUE (the_date) ); {code} You can see, we try to use different definitions with the same behavior (wrong) when the_date get an index (PRIMARY/UNIQUE). Without UNIQUE/PRIMARY Index on the_date it works fine. |
Sample with
CREATE OR REPLACE TABLE test_kpi_date ( the_date date NOT NULL PRIMARY KEY ); {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-12 dis 10 ADVB 1 2021-08-12 dis 10 ADVB 4096 2021-08-12 dis 11 ADVB 2 2021-08-12 dis 12 ADVB <null> 2021-08-12 reg 1 ADVB 1 2021-08-12 reg 1 ADVB 4098 2021-08-12 dis 21 ADVB 96 2021-08-12 dis 22 ADVB 256 2021-08-12 dis 23 ADVB 512 2021-08-12 reg 2 ADVB 864 2021-08-12 nsc MMD ADVB 4963 {code} correct would be: CREATE OR REPLACE TABLE test_kpi_date ( "id" int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, the_date date NOT NULL ); {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-12 dis 10 ADVB 4097 2021-08-12 dis 11 ADVB 2 2021-08-12 dis 12 ADVB <null> 2021-08-12 reg 1 ADVB 4099 2021-08-12 dis 21 ADVB 96 2021-08-12 dis 22 ADVB 256 2021-08-12 dis 23 ADVB 512 2021-08-12 reg 2 ADVB 864 2021-08-12 nsc MMD ADVB 4963 {code} It looks like Test Data: {code:sql} SET sql_mode = 'REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI,ONLY_FULL_GROUP_BY' CREATE OR REPLACE TABLE test_kpi_date ( the_date date NOT NULL , PRIMARY KEY ("the_date") ); SELECT * FROM test_kpi_date tkd INSERT INTO test_kpi_date VALUES (current_date), (current_date-1), (current_date-2), (current_date-3); CREATE OR REPLACE TABLE test_org_district ( "id" int(11) 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") ); INSERT INTO test_org_district (nsc_id,dept_id,district_id,region_id) VALUES ('MMD','ADVB','10','1'), ('MMD','ADVB','11','1'), ('MMD','ADVB','21','2'), ('MMD','ADVB','22','2'); CREATE OR REPLACE TABLE "test_org_partner" ( "dept_id" char(4) CHARACTER SET utf8 NOT NULL, "ptn_id" char(5) CHARACTER SET utf8, "district_id" char(3) CHARACTER SET utf8 NOT NULL DEFAULT '0', "nsc_id" char(5) CHARACTER SET utf8 NOT NULL, PRIMARY KEY ("ptn_id","dept_id"), KEY "dept_ptn" ("dept_id","ptn_id"), KEY "dept_district_ptn" ("dept_id","district_id","ptn_id") ); INSERT INTO test_org_partner (dept_id,ptn_id,district_id,nsc_id) VALUES ('ADVB','10001','10','MMD'), ('ADVB','10002','10','MMD'), ('ADVB','10003','10','MMD'), ('ADVB','11001','11','MMD'), ('ADVB','11002','11','MMD'), ('ADVB','10740','21','MMD'), ('ADVB','10741','21','MMD'), ('ADVB','10429','22','MMD'), ('ADVB','10771','23','MMD'); CREATE OR REPLACE TABLE "test_kpi_measure" ( "the_date" date DEFAULT NULL, "ptn_id" char(5) CHARACTER SET utf8 NOT NULL DEFAULT '', "cco_stk_ttl" int(11) DEFAULT NULL, PRIMARY KEY ("the_date","ptn_id"), KEY "ptn_id_idx" ("ptn_id") ); INSERT INTO test_kpi_measure (the_date,ptn_id,cco_stk_ttl) VALUES (current_date-1,'10001',4096), (current_date-1,'10002',1), (current_date-2,'10002',null), (current_date-1,'11001',2), (current_date-2,'11001',4), (current_date-1,'11003',1024), (current_date-2,'11003',2048), (current_date-1,'10580',8), (current_date-1,'10499',16), (current_date-1,'10740',32), (current_date-1,'10741',64), (current_date-2,'10741',128), (current_date-1,'10429',256), (current_date-1,'10771',512); {code} SQL with wrong result: {code:sql} SELECT org.the_date , org.org_type , org.org_id , org.dept_id , msr.`cco_stk_ttl` FROM ( SELECT cal.the_date , org.dept_id , case when org.region_id <=> null then 'nsc' when org.district_id <=> null then 'reg' else 'dis' end as org_type , coalesce(org.district_id, org.region_id, 'MMD') as org_id , org.district_id , org.region_id FROM test_kpi_date cal CROSS JOIN test_org_district org WHERE org.nsc_id = 'MMD' and org.dept_id IN ('ADVB') AND cal.the_date = CURRENT_DATE() - INTERVAL 1 DAY 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) ) org LEFT JOIN ( SELECT sub.the_date , dis.dept_id , dis.region_id , dis.district_id , SUM(sub.cco_stk_ttl) as cco_stk_ttl FROM test_kpi_measure sub JOIN test_org_partner org ON org.ptn_id = sub.ptn_id JOIN test_org_district dis ON dis.dept_id = org.dept_id AND dis.district_id = org.district_id WHERE dis.nsc_id = 'MMD' and dis.dept_id IN ('ADVB') -- AND sub.the_date = CURRENT_DATE() - INTERVAL 1 DAY GROUP BY sub.the_date, dis.dept_id, dis.region_id, dis.district_id WITH ROLLUP ) msr ON msr.the_date = org.the_date and msr.dept_id <=> org.dept_id and msr.region_id <=> org.region_id and msr.district_id <=> org.district_id {code} Code that works: {code:sql} CREATE OR REPLACE TABLE test_kpi_date ( "id" int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, the_date date NOT NULL -- , PRIMARY KEY ("the_date") -- , UNIQUE (the_date) ); {code} You can see, we try to use different definitions with the same behavior (wrong) when the_date get an index (PRIMARY/UNIQUE). Without UNIQUE/PRIMARY Index on the_date it works fine. |
| Description |
Sample with
CREATE OR REPLACE TABLE test_kpi_date ( the_date date NOT NULL PRIMARY KEY ); {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-12 dis 10 ADVB 1 2021-08-12 dis 10 ADVB 4096 2021-08-12 dis 11 ADVB 2 2021-08-12 dis 12 ADVB <null> 2021-08-12 reg 1 ADVB 1 2021-08-12 reg 1 ADVB 4098 2021-08-12 dis 21 ADVB 96 2021-08-12 dis 22 ADVB 256 2021-08-12 dis 23 ADVB 512 2021-08-12 reg 2 ADVB 864 2021-08-12 nsc MMD ADVB 4963 {code} correct would be: CREATE OR REPLACE TABLE test_kpi_date ( "id" int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, the_date date NOT NULL ); {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-12 dis 10 ADVB 4097 2021-08-12 dis 11 ADVB 2 2021-08-12 dis 12 ADVB <null> 2021-08-12 reg 1 ADVB 4099 2021-08-12 dis 21 ADVB 96 2021-08-12 dis 22 ADVB 256 2021-08-12 dis 23 ADVB 512 2021-08-12 reg 2 ADVB 864 2021-08-12 nsc MMD ADVB 4963 {code} It looks like Test Data: {code:sql} SET sql_mode = 'REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI,ONLY_FULL_GROUP_BY' CREATE OR REPLACE TABLE test_kpi_date ( the_date date NOT NULL , PRIMARY KEY ("the_date") ); SELECT * FROM test_kpi_date tkd INSERT INTO test_kpi_date VALUES (current_date), (current_date-1), (current_date-2), (current_date-3); CREATE OR REPLACE TABLE test_org_district ( "id" int(11) 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") ); INSERT INTO test_org_district (nsc_id,dept_id,district_id,region_id) VALUES ('MMD','ADVB','10','1'), ('MMD','ADVB','11','1'), ('MMD','ADVB','21','2'), ('MMD','ADVB','22','2'); CREATE OR REPLACE TABLE "test_org_partner" ( "dept_id" char(4) CHARACTER SET utf8 NOT NULL, "ptn_id" char(5) CHARACTER SET utf8, "district_id" char(3) CHARACTER SET utf8 NOT NULL DEFAULT '0', "nsc_id" char(5) CHARACTER SET utf8 NOT NULL, PRIMARY KEY ("ptn_id","dept_id"), KEY "dept_ptn" ("dept_id","ptn_id"), KEY "dept_district_ptn" ("dept_id","district_id","ptn_id") ); INSERT INTO test_org_partner (dept_id,ptn_id,district_id,nsc_id) VALUES ('ADVB','10001','10','MMD'), ('ADVB','10002','10','MMD'), ('ADVB','10003','10','MMD'), ('ADVB','11001','11','MMD'), ('ADVB','11002','11','MMD'), ('ADVB','10740','21','MMD'), ('ADVB','10741','21','MMD'), ('ADVB','10429','22','MMD'), ('ADVB','10771','23','MMD'); CREATE OR REPLACE TABLE "test_kpi_measure" ( "the_date" date DEFAULT NULL, "ptn_id" char(5) CHARACTER SET utf8 NOT NULL DEFAULT '', "cco_stk_ttl" int(11) DEFAULT NULL, PRIMARY KEY ("the_date","ptn_id"), KEY "ptn_id_idx" ("ptn_id") ); INSERT INTO test_kpi_measure (the_date,ptn_id,cco_stk_ttl) VALUES (current_date-1,'10001',4096), (current_date-1,'10002',1), (current_date-2,'10002',null), (current_date-1,'11001',2), (current_date-2,'11001',4), (current_date-1,'11003',1024), (current_date-2,'11003',2048), (current_date-1,'10580',8), (current_date-1,'10499',16), (current_date-1,'10740',32), (current_date-1,'10741',64), (current_date-2,'10741',128), (current_date-1,'10429',256), (current_date-1,'10771',512); {code} SQL with wrong result: {code:sql} SELECT org.the_date , org.org_type , org.org_id , org.dept_id , msr.`cco_stk_ttl` FROM ( SELECT cal.the_date , org.dept_id , case when org.region_id <=> null then 'nsc' when org.district_id <=> null then 'reg' else 'dis' end as org_type , coalesce(org.district_id, org.region_id, 'MMD') as org_id , org.district_id , org.region_id FROM test_kpi_date cal CROSS JOIN test_org_district org WHERE org.nsc_id = 'MMD' and org.dept_id IN ('ADVB') AND cal.the_date = CURRENT_DATE() - INTERVAL 1 DAY 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) ) org LEFT JOIN ( SELECT sub.the_date , dis.dept_id , dis.region_id , dis.district_id , SUM(sub.cco_stk_ttl) as cco_stk_ttl FROM test_kpi_measure sub JOIN test_org_partner org ON org.ptn_id = sub.ptn_id JOIN test_org_district dis ON dis.dept_id = org.dept_id AND dis.district_id = org.district_id WHERE dis.nsc_id = 'MMD' and dis.dept_id IN ('ADVB') -- AND sub.the_date = CURRENT_DATE() - INTERVAL 1 DAY GROUP BY sub.the_date, dis.dept_id, dis.region_id, dis.district_id WITH ROLLUP ) msr ON msr.the_date = org.the_date and msr.dept_id <=> org.dept_id and msr.region_id <=> org.region_id and msr.district_id <=> org.district_id {code} Code that works: {code:sql} CREATE OR REPLACE TABLE test_kpi_date ( "id" int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, the_date date NOT NULL -- , PRIMARY KEY ("the_date") -- , UNIQUE (the_date) ); {code} You can see, we try to use different definitions with the same behavior (wrong) when the_date get an index (PRIMARY/UNIQUE). Without UNIQUE/PRIMARY Index on the_date it works fine. |
Sample with
CREATE OR REPLACE TABLE test_kpi_date ( the_date date NOT NULL PRIMARY KEY ); {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-12 dis 10 ADVB 1 2021-08-12 dis 10 ADVB 4096 2021-08-12 dis 11 ADVB 2 2021-08-12 dis 12 ADVB <null> 2021-08-12 reg 1 ADVB 1 2021-08-12 reg 1 ADVB 4098 2021-08-12 dis 21 ADVB 96 2021-08-12 dis 22 ADVB 256 2021-08-12 dis 23 ADVB 512 2021-08-12 reg 2 ADVB 864 2021-08-12 nsc MMD ADVB 4963 {code} generated with: CREATE OR REPLACE TABLE test_kpi_date ( "id" int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, the_date date NOT NULL ); result looks good: {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-12 dis 10 ADVB 4097 2021-08-12 dis 11 ADVB 2 2021-08-12 dis 12 ADVB <null> 2021-08-12 reg 1 ADVB 4099 2021-08-12 dis 21 ADVB 96 2021-08-12 dis 22 ADVB 256 2021-08-12 dis 23 ADVB 512 2021-08-12 reg 2 ADVB 864 2021-08-12 nsc MMD ADVB 4963 {code} It looks like Test Data: {code:sql} SET sql_mode = 'REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI,ONLY_FULL_GROUP_BY' CREATE OR REPLACE TABLE test_kpi_date ( the_date date NOT NULL , PRIMARY KEY ("the_date") ); SELECT * FROM test_kpi_date tkd INSERT INTO test_kpi_date VALUES (current_date), (current_date-1), (current_date-2), (current_date-3); CREATE OR REPLACE TABLE test_org_district ( "id" int(11) 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") ); INSERT INTO test_org_district (nsc_id,dept_id,district_id,region_id) VALUES ('MMD','ADVB','10','1'), ('MMD','ADVB','11','1'), ('MMD','ADVB','21','2'), ('MMD','ADVB','22','2'); CREATE OR REPLACE TABLE "test_org_partner" ( "dept_id" char(4) CHARACTER SET utf8 NOT NULL, "ptn_id" char(5) CHARACTER SET utf8, "district_id" char(3) CHARACTER SET utf8 NOT NULL DEFAULT '0', "nsc_id" char(5) CHARACTER SET utf8 NOT NULL, PRIMARY KEY ("ptn_id","dept_id"), KEY "dept_ptn" ("dept_id","ptn_id"), KEY "dept_district_ptn" ("dept_id","district_id","ptn_id") ); INSERT INTO test_org_partner (dept_id,ptn_id,district_id,nsc_id) VALUES ('ADVB','10001','10','MMD'), ('ADVB','10002','10','MMD'), ('ADVB','10003','10','MMD'), ('ADVB','11001','11','MMD'), ('ADVB','11002','11','MMD'), ('ADVB','10740','21','MMD'), ('ADVB','10741','21','MMD'), ('ADVB','10429','22','MMD'), ('ADVB','10771','23','MMD'); CREATE OR REPLACE TABLE "test_kpi_measure" ( "the_date" date DEFAULT NULL, "ptn_id" char(5) CHARACTER SET utf8 NOT NULL DEFAULT '', "cco_stk_ttl" int(11) DEFAULT NULL, PRIMARY KEY ("the_date","ptn_id"), KEY "ptn_id_idx" ("ptn_id") ); INSERT INTO test_kpi_measure (the_date,ptn_id,cco_stk_ttl) VALUES (current_date-1,'10001',4096), (current_date-1,'10002',1), (current_date-2,'10002',null), (current_date-1,'11001',2), (current_date-2,'11001',4), (current_date-1,'11003',1024), (current_date-2,'11003',2048), (current_date-1,'10580',8), (current_date-1,'10499',16), (current_date-1,'10740',32), (current_date-1,'10741',64), (current_date-2,'10741',128), (current_date-1,'10429',256), (current_date-1,'10771',512); {code} SQL with wrong result: {code:sql} SELECT org.the_date , org.org_type , org.org_id , org.dept_id , msr.`cco_stk_ttl` FROM ( SELECT cal.the_date , org.dept_id , case when org.region_id <=> null then 'nsc' when org.district_id <=> null then 'reg' else 'dis' end as org_type , coalesce(org.district_id, org.region_id, 'MMD') as org_id , org.district_id , org.region_id FROM test_kpi_date cal CROSS JOIN test_org_district org WHERE org.nsc_id = 'MMD' and org.dept_id IN ('ADVB') AND cal.the_date = CURRENT_DATE() - INTERVAL 1 DAY 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) ) org LEFT JOIN ( SELECT sub.the_date , dis.dept_id , dis.region_id , dis.district_id , SUM(sub.cco_stk_ttl) as cco_stk_ttl FROM test_kpi_measure sub JOIN test_org_partner org ON org.ptn_id = sub.ptn_id JOIN test_org_district dis ON dis.dept_id = org.dept_id AND dis.district_id = org.district_id WHERE dis.nsc_id = 'MMD' and dis.dept_id IN ('ADVB') -- AND sub.the_date = CURRENT_DATE() - INTERVAL 1 DAY GROUP BY sub.the_date, dis.dept_id, dis.region_id, dis.district_id WITH ROLLUP ) msr ON msr.the_date = org.the_date and msr.dept_id <=> org.dept_id and msr.region_id <=> org.region_id and msr.district_id <=> org.district_id {code} Code that works: {code:sql} CREATE OR REPLACE TABLE test_kpi_date ( "id" int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, the_date date NOT NULL -- , PRIMARY KEY ("the_date") -- , UNIQUE (the_date) ); {code} You can see, we try to use different definitions with the same behavior (wrong) when the_date get an index (PRIMARY/UNIQUE). Without UNIQUE/PRIMARY Index on the_date it works fine. |
| Description |
Sample with
CREATE OR REPLACE TABLE test_kpi_date ( the_date date NOT NULL PRIMARY KEY ); {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-12 dis 10 ADVB 1 2021-08-12 dis 10 ADVB 4096 2021-08-12 dis 11 ADVB 2 2021-08-12 dis 12 ADVB <null> 2021-08-12 reg 1 ADVB 1 2021-08-12 reg 1 ADVB 4098 2021-08-12 dis 21 ADVB 96 2021-08-12 dis 22 ADVB 256 2021-08-12 dis 23 ADVB 512 2021-08-12 reg 2 ADVB 864 2021-08-12 nsc MMD ADVB 4963 {code} generated with: CREATE OR REPLACE TABLE test_kpi_date ( "id" int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, the_date date NOT NULL ); result looks good: {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-12 dis 10 ADVB 4097 2021-08-12 dis 11 ADVB 2 2021-08-12 dis 12 ADVB <null> 2021-08-12 reg 1 ADVB 4099 2021-08-12 dis 21 ADVB 96 2021-08-12 dis 22 ADVB 256 2021-08-12 dis 23 ADVB 512 2021-08-12 reg 2 ADVB 864 2021-08-12 nsc MMD ADVB 4963 {code} It looks like Test Data: {code:sql} SET sql_mode = 'REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI,ONLY_FULL_GROUP_BY' CREATE OR REPLACE TABLE test_kpi_date ( the_date date NOT NULL , PRIMARY KEY ("the_date") ); SELECT * FROM test_kpi_date tkd INSERT INTO test_kpi_date VALUES (current_date), (current_date-1), (current_date-2), (current_date-3); CREATE OR REPLACE TABLE test_org_district ( "id" int(11) 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") ); INSERT INTO test_org_district (nsc_id,dept_id,district_id,region_id) VALUES ('MMD','ADVB','10','1'), ('MMD','ADVB','11','1'), ('MMD','ADVB','21','2'), ('MMD','ADVB','22','2'); CREATE OR REPLACE TABLE "test_org_partner" ( "dept_id" char(4) CHARACTER SET utf8 NOT NULL, "ptn_id" char(5) CHARACTER SET utf8, "district_id" char(3) CHARACTER SET utf8 NOT NULL DEFAULT '0', "nsc_id" char(5) CHARACTER SET utf8 NOT NULL, PRIMARY KEY ("ptn_id","dept_id"), KEY "dept_ptn" ("dept_id","ptn_id"), KEY "dept_district_ptn" ("dept_id","district_id","ptn_id") ); INSERT INTO test_org_partner (dept_id,ptn_id,district_id,nsc_id) VALUES ('ADVB','10001','10','MMD'), ('ADVB','10002','10','MMD'), ('ADVB','10003','10','MMD'), ('ADVB','11001','11','MMD'), ('ADVB','11002','11','MMD'), ('ADVB','10740','21','MMD'), ('ADVB','10741','21','MMD'), ('ADVB','10429','22','MMD'), ('ADVB','10771','23','MMD'); CREATE OR REPLACE TABLE "test_kpi_measure" ( "the_date" date DEFAULT NULL, "ptn_id" char(5) CHARACTER SET utf8 NOT NULL DEFAULT '', "cco_stk_ttl" int(11) DEFAULT NULL, PRIMARY KEY ("the_date","ptn_id"), KEY "ptn_id_idx" ("ptn_id") ); INSERT INTO test_kpi_measure (the_date,ptn_id,cco_stk_ttl) VALUES (current_date-1,'10001',4096), (current_date-1,'10002',1), (current_date-2,'10002',null), (current_date-1,'11001',2), (current_date-2,'11001',4), (current_date-1,'11003',1024), (current_date-2,'11003',2048), (current_date-1,'10580',8), (current_date-1,'10499',16), (current_date-1,'10740',32), (current_date-1,'10741',64), (current_date-2,'10741',128), (current_date-1,'10429',256), (current_date-1,'10771',512); {code} SQL with wrong result: {code:sql} SELECT org.the_date , org.org_type , org.org_id , org.dept_id , msr.`cco_stk_ttl` FROM ( SELECT cal.the_date , org.dept_id , case when org.region_id <=> null then 'nsc' when org.district_id <=> null then 'reg' else 'dis' end as org_type , coalesce(org.district_id, org.region_id, 'MMD') as org_id , org.district_id , org.region_id FROM test_kpi_date cal CROSS JOIN test_org_district org WHERE org.nsc_id = 'MMD' and org.dept_id IN ('ADVB') AND cal.the_date = CURRENT_DATE() - INTERVAL 1 DAY 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) ) org LEFT JOIN ( SELECT sub.the_date , dis.dept_id , dis.region_id , dis.district_id , SUM(sub.cco_stk_ttl) as cco_stk_ttl FROM test_kpi_measure sub JOIN test_org_partner org ON org.ptn_id = sub.ptn_id JOIN test_org_district dis ON dis.dept_id = org.dept_id AND dis.district_id = org.district_id WHERE dis.nsc_id = 'MMD' and dis.dept_id IN ('ADVB') -- AND sub.the_date = CURRENT_DATE() - INTERVAL 1 DAY GROUP BY sub.the_date, dis.dept_id, dis.region_id, dis.district_id WITH ROLLUP ) msr ON msr.the_date = org.the_date and msr.dept_id <=> org.dept_id and msr.region_id <=> org.region_id and msr.district_id <=> org.district_id {code} Code that works: {code:sql} CREATE OR REPLACE TABLE test_kpi_date ( "id" int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, the_date date NOT NULL -- , PRIMARY KEY ("the_date") -- , UNIQUE (the_date) ); {code} You can see, we try to use different definitions with the same behavior (wrong) when the_date get an index (PRIMARY/UNIQUE). Without UNIQUE/PRIMARY Index on the_date it works fine. |
Sample with
CREATE OR REPLACE TABLE test_kpi_date ( the_date date NOT NULL PRIMARY KEY ); {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-12 dis 10 ADVB 1 2021-08-12 dis 10 ADVB 4096 2021-08-12 dis 11 ADVB 2 2021-08-12 dis 12 ADVB <null> 2021-08-12 reg 1 ADVB 1 2021-08-12 reg 1 ADVB 4098 2021-08-12 dis 21 ADVB 96 2021-08-12 dis 22 ADVB 256 2021-08-12 dis 23 ADVB 512 2021-08-12 reg 2 ADVB 864 2021-08-12 nsc MMD ADVB 4963 {code} generated with: {code:sql} CREATE OR REPLACE TABLE test_kpi_date ( "id" int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, the_date date NOT NULL ); {code} result looks good: {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-12 dis 10 ADVB 4097 2021-08-12 dis 11 ADVB 2 2021-08-12 dis 12 ADVB <null> 2021-08-12 reg 1 ADVB 4099 2021-08-12 dis 21 ADVB 96 2021-08-12 dis 22 ADVB 256 2021-08-12 dis 23 ADVB 512 2021-08-12 reg 2 ADVB 864 2021-08-12 nsc MMD ADVB 4963 {code} It looks like Test Data: {code:sql} SET sql_mode = 'REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI,ONLY_FULL_GROUP_BY' CREATE OR REPLACE TABLE test_kpi_date ( the_date date NOT NULL , PRIMARY KEY ("the_date") ); SELECT * FROM test_kpi_date tkd INSERT INTO test_kpi_date VALUES (current_date), (current_date-1), (current_date-2), (current_date-3); CREATE OR REPLACE TABLE test_org_district ( "id" int(11) 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") ); INSERT INTO test_org_district (nsc_id,dept_id,district_id,region_id) VALUES ('MMD','ADVB','10','1'), ('MMD','ADVB','11','1'), ('MMD','ADVB','21','2'), ('MMD','ADVB','22','2'); CREATE OR REPLACE TABLE "test_org_partner" ( "dept_id" char(4) CHARACTER SET utf8 NOT NULL, "ptn_id" char(5) CHARACTER SET utf8, "district_id" char(3) CHARACTER SET utf8 NOT NULL DEFAULT '0', "nsc_id" char(5) CHARACTER SET utf8 NOT NULL, PRIMARY KEY ("ptn_id","dept_id"), KEY "dept_ptn" ("dept_id","ptn_id"), KEY "dept_district_ptn" ("dept_id","district_id","ptn_id") ); INSERT INTO test_org_partner (dept_id,ptn_id,district_id,nsc_id) VALUES ('ADVB','10001','10','MMD'), ('ADVB','10002','10','MMD'), ('ADVB','10003','10','MMD'), ('ADVB','11001','11','MMD'), ('ADVB','11002','11','MMD'), ('ADVB','10740','21','MMD'), ('ADVB','10741','21','MMD'), ('ADVB','10429','22','MMD'), ('ADVB','10771','23','MMD'); CREATE OR REPLACE TABLE "test_kpi_measure" ( "the_date" date DEFAULT NULL, "ptn_id" char(5) CHARACTER SET utf8 NOT NULL DEFAULT '', "cco_stk_ttl" int(11) DEFAULT NULL, PRIMARY KEY ("the_date","ptn_id"), KEY "ptn_id_idx" ("ptn_id") ); INSERT INTO test_kpi_measure (the_date,ptn_id,cco_stk_ttl) VALUES (current_date-1,'10001',4096), (current_date-1,'10002',1), (current_date-2,'10002',null), (current_date-1,'11001',2), (current_date-2,'11001',4), (current_date-1,'11003',1024), (current_date-2,'11003',2048), (current_date-1,'10580',8), (current_date-1,'10499',16), (current_date-1,'10740',32), (current_date-1,'10741',64), (current_date-2,'10741',128), (current_date-1,'10429',256), (current_date-1,'10771',512); {code} SQL with wrong result: {code:sql} SELECT org.the_date , org.org_type , org.org_id , org.dept_id , msr.`cco_stk_ttl` FROM ( SELECT cal.the_date , org.dept_id , case when org.region_id <=> null then 'nsc' when org.district_id <=> null then 'reg' else 'dis' end as org_type , coalesce(org.district_id, org.region_id, 'MMD') as org_id , org.district_id , org.region_id FROM test_kpi_date cal CROSS JOIN test_org_district org WHERE org.nsc_id = 'MMD' and org.dept_id IN ('ADVB') AND cal.the_date = CURRENT_DATE() - INTERVAL 1 DAY 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) ) org LEFT JOIN ( SELECT sub.the_date , dis.dept_id , dis.region_id , dis.district_id , SUM(sub.cco_stk_ttl) as cco_stk_ttl FROM test_kpi_measure sub JOIN test_org_partner org ON org.ptn_id = sub.ptn_id JOIN test_org_district dis ON dis.dept_id = org.dept_id AND dis.district_id = org.district_id WHERE dis.nsc_id = 'MMD' and dis.dept_id IN ('ADVB') -- AND sub.the_date = CURRENT_DATE() - INTERVAL 1 DAY GROUP BY sub.the_date, dis.dept_id, dis.region_id, dis.district_id WITH ROLLUP ) msr ON msr.the_date = org.the_date and msr.dept_id <=> org.dept_id and msr.region_id <=> org.region_id and msr.district_id <=> org.district_id {code} Code that works: {code:sql} CREATE OR REPLACE TABLE test_kpi_date ( "id" int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, the_date date NOT NULL -- , PRIMARY KEY ("the_date") -- , UNIQUE (the_date) ); {code} You can see, we try to use different definitions with the same behavior (wrong) when the_date get an index (PRIMARY/UNIQUE). Without UNIQUE/PRIMARY Index on the_date it works fine. |
| Description |
Sample with
CREATE OR REPLACE TABLE test_kpi_date ( the_date date NOT NULL PRIMARY KEY ); {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-12 dis 10 ADVB 1 2021-08-12 dis 10 ADVB 4096 2021-08-12 dis 11 ADVB 2 2021-08-12 dis 12 ADVB <null> 2021-08-12 reg 1 ADVB 1 2021-08-12 reg 1 ADVB 4098 2021-08-12 dis 21 ADVB 96 2021-08-12 dis 22 ADVB 256 2021-08-12 dis 23 ADVB 512 2021-08-12 reg 2 ADVB 864 2021-08-12 nsc MMD ADVB 4963 {code} generated with: {code:sql} CREATE OR REPLACE TABLE test_kpi_date ( "id" int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, the_date date NOT NULL ); {code} result looks good: {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-12 dis 10 ADVB 4097 2021-08-12 dis 11 ADVB 2 2021-08-12 dis 12 ADVB <null> 2021-08-12 reg 1 ADVB 4099 2021-08-12 dis 21 ADVB 96 2021-08-12 dis 22 ADVB 256 2021-08-12 dis 23 ADVB 512 2021-08-12 reg 2 ADVB 864 2021-08-12 nsc MMD ADVB 4963 {code} It looks like Test Data: {code:sql} SET sql_mode = 'REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI,ONLY_FULL_GROUP_BY' CREATE OR REPLACE TABLE test_kpi_date ( the_date date NOT NULL , PRIMARY KEY ("the_date") ); SELECT * FROM test_kpi_date tkd INSERT INTO test_kpi_date VALUES (current_date), (current_date-1), (current_date-2), (current_date-3); CREATE OR REPLACE TABLE test_org_district ( "id" int(11) 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") ); INSERT INTO test_org_district (nsc_id,dept_id,district_id,region_id) VALUES ('MMD','ADVB','10','1'), ('MMD','ADVB','11','1'), ('MMD','ADVB','21','2'), ('MMD','ADVB','22','2'); CREATE OR REPLACE TABLE "test_org_partner" ( "dept_id" char(4) CHARACTER SET utf8 NOT NULL, "ptn_id" char(5) CHARACTER SET utf8, "district_id" char(3) CHARACTER SET utf8 NOT NULL DEFAULT '0', "nsc_id" char(5) CHARACTER SET utf8 NOT NULL, PRIMARY KEY ("ptn_id","dept_id"), KEY "dept_ptn" ("dept_id","ptn_id"), KEY "dept_district_ptn" ("dept_id","district_id","ptn_id") ); INSERT INTO test_org_partner (dept_id,ptn_id,district_id,nsc_id) VALUES ('ADVB','10001','10','MMD'), ('ADVB','10002','10','MMD'), ('ADVB','10003','10','MMD'), ('ADVB','11001','11','MMD'), ('ADVB','11002','11','MMD'), ('ADVB','10740','21','MMD'), ('ADVB','10741','21','MMD'), ('ADVB','10429','22','MMD'), ('ADVB','10771','23','MMD'); CREATE OR REPLACE TABLE "test_kpi_measure" ( "the_date" date DEFAULT NULL, "ptn_id" char(5) CHARACTER SET utf8 NOT NULL DEFAULT '', "cco_stk_ttl" int(11) DEFAULT NULL, PRIMARY KEY ("the_date","ptn_id"), KEY "ptn_id_idx" ("ptn_id") ); INSERT INTO test_kpi_measure (the_date,ptn_id,cco_stk_ttl) VALUES (current_date-1,'10001',4096), (current_date-1,'10002',1), (current_date-2,'10002',null), (current_date-1,'11001',2), (current_date-2,'11001',4), (current_date-1,'11003',1024), (current_date-2,'11003',2048), (current_date-1,'10580',8), (current_date-1,'10499',16), (current_date-1,'10740',32), (current_date-1,'10741',64), (current_date-2,'10741',128), (current_date-1,'10429',256), (current_date-1,'10771',512); {code} SQL with wrong result: {code:sql} SELECT org.the_date , org.org_type , org.org_id , org.dept_id , msr.`cco_stk_ttl` FROM ( SELECT cal.the_date , org.dept_id , case when org.region_id <=> null then 'nsc' when org.district_id <=> null then 'reg' else 'dis' end as org_type , coalesce(org.district_id, org.region_id, 'MMD') as org_id , org.district_id , org.region_id FROM test_kpi_date cal CROSS JOIN test_org_district org WHERE org.nsc_id = 'MMD' and org.dept_id IN ('ADVB') AND cal.the_date = CURRENT_DATE() - INTERVAL 1 DAY 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) ) org LEFT JOIN ( SELECT sub.the_date , dis.dept_id , dis.region_id , dis.district_id , SUM(sub.cco_stk_ttl) as cco_stk_ttl FROM test_kpi_measure sub JOIN test_org_partner org ON org.ptn_id = sub.ptn_id JOIN test_org_district dis ON dis.dept_id = org.dept_id AND dis.district_id = org.district_id WHERE dis.nsc_id = 'MMD' and dis.dept_id IN ('ADVB') -- AND sub.the_date = CURRENT_DATE() - INTERVAL 1 DAY GROUP BY sub.the_date, dis.dept_id, dis.region_id, dis.district_id WITH ROLLUP ) msr ON msr.the_date = org.the_date and msr.dept_id <=> org.dept_id and msr.region_id <=> org.region_id and msr.district_id <=> org.district_id {code} Code that works: {code:sql} CREATE OR REPLACE TABLE test_kpi_date ( "id" int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, the_date date NOT NULL -- , PRIMARY KEY ("the_date") -- , UNIQUE (the_date) ); {code} You can see, we try to use different definitions with the same behavior (wrong) when the_date get an index (PRIMARY/UNIQUE). Without UNIQUE/PRIMARY Index on the_date it works fine. |
Sample with
{code:sql} CREATE OR REPLACE TABLE test_kpi_date ( the_date date NOT NULL PRIMARY KEY ); {code} {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-12 dis 10 ADVB 1 2021-08-12 dis 10 ADVB 4096 2021-08-12 dis 11 ADVB 2 2021-08-12 dis 12 ADVB <null> 2021-08-12 reg 1 ADVB 1 2021-08-12 reg 1 ADVB 4098 2021-08-12 dis 21 ADVB 96 2021-08-12 dis 22 ADVB 256 2021-08-12 dis 23 ADVB 512 2021-08-12 reg 2 ADVB 864 2021-08-12 nsc MMD ADVB 4963 {code} generated with: {code:sql} CREATE OR REPLACE TABLE test_kpi_date ( "id" int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, the_date date NOT NULL ); {code} result looks good: {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-12 dis 10 ADVB 4097 2021-08-12 dis 11 ADVB 2 2021-08-12 dis 12 ADVB <null> 2021-08-12 reg 1 ADVB 4099 2021-08-12 dis 21 ADVB 96 2021-08-12 dis 22 ADVB 256 2021-08-12 dis 23 ADVB 512 2021-08-12 reg 2 ADVB 864 2021-08-12 nsc MMD ADVB 4963 {code} It looks like Test Data: {code:sql} SET sql_mode = 'REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI,ONLY_FULL_GROUP_BY' CREATE OR REPLACE TABLE test_kpi_date ( the_date date NOT NULL , PRIMARY KEY ("the_date") ); SELECT * FROM test_kpi_date tkd INSERT INTO test_kpi_date VALUES (current_date), (current_date-1), (current_date-2), (current_date-3); CREATE OR REPLACE TABLE test_org_district ( "id" int(11) 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") ); INSERT INTO test_org_district (nsc_id,dept_id,district_id,region_id) VALUES ('MMD','ADVB','10','1'), ('MMD','ADVB','11','1'), ('MMD','ADVB','21','2'), ('MMD','ADVB','22','2'); CREATE OR REPLACE TABLE "test_org_partner" ( "dept_id" char(4) CHARACTER SET utf8 NOT NULL, "ptn_id" char(5) CHARACTER SET utf8, "district_id" char(3) CHARACTER SET utf8 NOT NULL DEFAULT '0', "nsc_id" char(5) CHARACTER SET utf8 NOT NULL, PRIMARY KEY ("ptn_id","dept_id"), KEY "dept_ptn" ("dept_id","ptn_id"), KEY "dept_district_ptn" ("dept_id","district_id","ptn_id") ); INSERT INTO test_org_partner (dept_id,ptn_id,district_id,nsc_id) VALUES ('ADVB','10001','10','MMD'), ('ADVB','10002','10','MMD'), ('ADVB','10003','10','MMD'), ('ADVB','11001','11','MMD'), ('ADVB','11002','11','MMD'), ('ADVB','10740','21','MMD'), ('ADVB','10741','21','MMD'), ('ADVB','10429','22','MMD'), ('ADVB','10771','23','MMD'); CREATE OR REPLACE TABLE "test_kpi_measure" ( "the_date" date DEFAULT NULL, "ptn_id" char(5) CHARACTER SET utf8 NOT NULL DEFAULT '', "cco_stk_ttl" int(11) DEFAULT NULL, PRIMARY KEY ("the_date","ptn_id"), KEY "ptn_id_idx" ("ptn_id") ); INSERT INTO test_kpi_measure (the_date,ptn_id,cco_stk_ttl) VALUES (current_date-1,'10001',4096), (current_date-1,'10002',1), (current_date-2,'10002',null), (current_date-1,'11001',2), (current_date-2,'11001',4), (current_date-1,'11003',1024), (current_date-2,'11003',2048), (current_date-1,'10580',8), (current_date-1,'10499',16), (current_date-1,'10740',32), (current_date-1,'10741',64), (current_date-2,'10741',128), (current_date-1,'10429',256), (current_date-1,'10771',512); {code} SQL with wrong result: {code:sql} SELECT org.the_date , org.org_type , org.org_id , org.dept_id , msr.`cco_stk_ttl` FROM ( SELECT cal.the_date , org.dept_id , case when org.region_id <=> null then 'nsc' when org.district_id <=> null then 'reg' else 'dis' end as org_type , coalesce(org.district_id, org.region_id, 'MMD') as org_id , org.district_id , org.region_id FROM test_kpi_date cal CROSS JOIN test_org_district org WHERE org.nsc_id = 'MMD' and org.dept_id IN ('ADVB') AND cal.the_date = CURRENT_DATE() - INTERVAL 1 DAY 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) ) org LEFT JOIN ( SELECT sub.the_date , dis.dept_id , dis.region_id , dis.district_id , SUM(sub.cco_stk_ttl) as cco_stk_ttl FROM test_kpi_measure sub JOIN test_org_partner org ON org.ptn_id = sub.ptn_id JOIN test_org_district dis ON dis.dept_id = org.dept_id AND dis.district_id = org.district_id WHERE dis.nsc_id = 'MMD' and dis.dept_id IN ('ADVB') -- AND sub.the_date = CURRENT_DATE() - INTERVAL 1 DAY GROUP BY sub.the_date, dis.dept_id, dis.region_id, dis.district_id WITH ROLLUP ) msr ON msr.the_date = org.the_date and msr.dept_id <=> org.dept_id and msr.region_id <=> org.region_id and msr.district_id <=> org.district_id {code} Code that works: {code:sql} CREATE OR REPLACE TABLE test_kpi_date ( "id" int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, the_date date NOT NULL -- , PRIMARY KEY ("the_date") -- , UNIQUE (the_date) ); {code} You can see, we try to use different definitions with the same behavior (wrong) when the_date get an index (PRIMARY/UNIQUE). Without UNIQUE/PRIMARY Index on the_date it works fine. |
| Description |
Sample with
{code:sql} CREATE OR REPLACE TABLE test_kpi_date ( the_date date NOT NULL PRIMARY KEY ); {code} {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-12 dis 10 ADVB 1 2021-08-12 dis 10 ADVB 4096 2021-08-12 dis 11 ADVB 2 2021-08-12 dis 12 ADVB <null> 2021-08-12 reg 1 ADVB 1 2021-08-12 reg 1 ADVB 4098 2021-08-12 dis 21 ADVB 96 2021-08-12 dis 22 ADVB 256 2021-08-12 dis 23 ADVB 512 2021-08-12 reg 2 ADVB 864 2021-08-12 nsc MMD ADVB 4963 {code} generated with: {code:sql} CREATE OR REPLACE TABLE test_kpi_date ( "id" int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, the_date date NOT NULL ); {code} result looks good: {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-12 dis 10 ADVB 4097 2021-08-12 dis 11 ADVB 2 2021-08-12 dis 12 ADVB <null> 2021-08-12 reg 1 ADVB 4099 2021-08-12 dis 21 ADVB 96 2021-08-12 dis 22 ADVB 256 2021-08-12 dis 23 ADVB 512 2021-08-12 reg 2 ADVB 864 2021-08-12 nsc MMD ADVB 4963 {code} It looks like Test Data: {code:sql} SET sql_mode = 'REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI,ONLY_FULL_GROUP_BY' CREATE OR REPLACE TABLE test_kpi_date ( the_date date NOT NULL , PRIMARY KEY ("the_date") ); SELECT * FROM test_kpi_date tkd INSERT INTO test_kpi_date VALUES (current_date), (current_date-1), (current_date-2), (current_date-3); CREATE OR REPLACE TABLE test_org_district ( "id" int(11) 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") ); INSERT INTO test_org_district (nsc_id,dept_id,district_id,region_id) VALUES ('MMD','ADVB','10','1'), ('MMD','ADVB','11','1'), ('MMD','ADVB','21','2'), ('MMD','ADVB','22','2'); CREATE OR REPLACE TABLE "test_org_partner" ( "dept_id" char(4) CHARACTER SET utf8 NOT NULL, "ptn_id" char(5) CHARACTER SET utf8, "district_id" char(3) CHARACTER SET utf8 NOT NULL DEFAULT '0', "nsc_id" char(5) CHARACTER SET utf8 NOT NULL, PRIMARY KEY ("ptn_id","dept_id"), KEY "dept_ptn" ("dept_id","ptn_id"), KEY "dept_district_ptn" ("dept_id","district_id","ptn_id") ); INSERT INTO test_org_partner (dept_id,ptn_id,district_id,nsc_id) VALUES ('ADVB','10001','10','MMD'), ('ADVB','10002','10','MMD'), ('ADVB','10003','10','MMD'), ('ADVB','11001','11','MMD'), ('ADVB','11002','11','MMD'), ('ADVB','10740','21','MMD'), ('ADVB','10741','21','MMD'), ('ADVB','10429','22','MMD'), ('ADVB','10771','23','MMD'); CREATE OR REPLACE TABLE "test_kpi_measure" ( "the_date" date DEFAULT NULL, "ptn_id" char(5) CHARACTER SET utf8 NOT NULL DEFAULT '', "cco_stk_ttl" int(11) DEFAULT NULL, PRIMARY KEY ("the_date","ptn_id"), KEY "ptn_id_idx" ("ptn_id") ); INSERT INTO test_kpi_measure (the_date,ptn_id,cco_stk_ttl) VALUES (current_date-1,'10001',4096), (current_date-1,'10002',1), (current_date-2,'10002',null), (current_date-1,'11001',2), (current_date-2,'11001',4), (current_date-1,'11003',1024), (current_date-2,'11003',2048), (current_date-1,'10580',8), (current_date-1,'10499',16), (current_date-1,'10740',32), (current_date-1,'10741',64), (current_date-2,'10741',128), (current_date-1,'10429',256), (current_date-1,'10771',512); {code} SQL with wrong result: {code:sql} SELECT org.the_date , org.org_type , org.org_id , org.dept_id , msr.`cco_stk_ttl` FROM ( SELECT cal.the_date , org.dept_id , case when org.region_id <=> null then 'nsc' when org.district_id <=> null then 'reg' else 'dis' end as org_type , coalesce(org.district_id, org.region_id, 'MMD') as org_id , org.district_id , org.region_id FROM test_kpi_date cal CROSS JOIN test_org_district org WHERE org.nsc_id = 'MMD' and org.dept_id IN ('ADVB') AND cal.the_date = CURRENT_DATE() - INTERVAL 1 DAY 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) ) org LEFT JOIN ( SELECT sub.the_date , dis.dept_id , dis.region_id , dis.district_id , SUM(sub.cco_stk_ttl) as cco_stk_ttl FROM test_kpi_measure sub JOIN test_org_partner org ON org.ptn_id = sub.ptn_id JOIN test_org_district dis ON dis.dept_id = org.dept_id AND dis.district_id = org.district_id WHERE dis.nsc_id = 'MMD' and dis.dept_id IN ('ADVB') -- AND sub.the_date = CURRENT_DATE() - INTERVAL 1 DAY GROUP BY sub.the_date, dis.dept_id, dis.region_id, dis.district_id WITH ROLLUP ) msr ON msr.the_date = org.the_date and msr.dept_id <=> org.dept_id and msr.region_id <=> org.region_id and msr.district_id <=> org.district_id {code} Code that works: {code:sql} CREATE OR REPLACE TABLE test_kpi_date ( "id" int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, the_date date NOT NULL -- , PRIMARY KEY ("the_date") -- , UNIQUE (the_date) ); {code} You can see, we try to use different definitions with the same behavior (wrong) when the_date get an index (PRIMARY/UNIQUE). Without UNIQUE/PRIMARY Index on the_date it works fine. |
Wrong result, duplicate group by values and wrong aggregation "row"-data (row 1,2 and 5,6):
{code:sql} CREATE OR REPLACE TABLE test_kpi_date ( the_date date NOT NULL PRIMARY KEY ); {code} {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-12 dis 10 ADVB 1 2021-08-12 dis 10 ADVB 4096 2021-08-12 dis 11 ADVB 2 2021-08-12 dis 12 ADVB <null> 2021-08-12 reg 1 ADVB 1 2021-08-12 reg 1 ADVB 4098 2021-08-12 dis 21 ADVB 96 2021-08-12 dis 22 ADVB 256 2021-08-12 dis 23 ADVB 512 2021-08-12 reg 2 ADVB 864 2021-08-12 nsc MMD ADVB 4963 {code} generated with: {code:sql} CREATE OR REPLACE TABLE test_kpi_date ( "id" int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, the_date date NOT NULL ); {code} result looks good: {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-12 dis 10 ADVB 4097 2021-08-12 dis 11 ADVB 2 2021-08-12 dis 12 ADVB <null> 2021-08-12 reg 1 ADVB 4099 2021-08-12 dis 21 ADVB 96 2021-08-12 dis 22 ADVB 256 2021-08-12 dis 23 ADVB 512 2021-08-12 reg 2 ADVB 864 2021-08-12 nsc MMD ADVB 4963 {code} It looks like Test Data: {code:sql} SET sql_mode = 'REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI,ONLY_FULL_GROUP_BY' CREATE OR REPLACE TABLE test_kpi_date ( the_date date NOT NULL , PRIMARY KEY ("the_date") ); SELECT * FROM test_kpi_date tkd INSERT INTO test_kpi_date VALUES (current_date), (current_date-1), (current_date-2), (current_date-3); CREATE OR REPLACE TABLE test_org_district ( "id" int(11) 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") ); INSERT INTO test_org_district (nsc_id,dept_id,district_id,region_id) VALUES ('MMD','ADVB','10','1'), ('MMD','ADVB','11','1'), ('MMD','ADVB','21','2'), ('MMD','ADVB','22','2'); CREATE OR REPLACE TABLE "test_org_partner" ( "dept_id" char(4) CHARACTER SET utf8 NOT NULL, "ptn_id" char(5) CHARACTER SET utf8, "district_id" char(3) CHARACTER SET utf8 NOT NULL DEFAULT '0', "nsc_id" char(5) CHARACTER SET utf8 NOT NULL, PRIMARY KEY ("ptn_id","dept_id"), KEY "dept_ptn" ("dept_id","ptn_id"), KEY "dept_district_ptn" ("dept_id","district_id","ptn_id") ); INSERT INTO test_org_partner (dept_id,ptn_id,district_id,nsc_id) VALUES ('ADVB','10001','10','MMD'), ('ADVB','10002','10','MMD'), ('ADVB','10003','10','MMD'), ('ADVB','11001','11','MMD'), ('ADVB','11002','11','MMD'), ('ADVB','10740','21','MMD'), ('ADVB','10741','21','MMD'), ('ADVB','10429','22','MMD'), ('ADVB','10771','23','MMD'); CREATE OR REPLACE TABLE "test_kpi_measure" ( "the_date" date DEFAULT NULL, "ptn_id" char(5) CHARACTER SET utf8 NOT NULL DEFAULT '', "cco_stk_ttl" int(11) DEFAULT NULL, PRIMARY KEY ("the_date","ptn_id"), KEY "ptn_id_idx" ("ptn_id") ); INSERT INTO test_kpi_measure (the_date,ptn_id,cco_stk_ttl) VALUES (current_date-1,'10001',4096), (current_date-1,'10002',1), (current_date-2,'10002',null), (current_date-1,'11001',2), (current_date-2,'11001',4), (current_date-1,'11003',1024), (current_date-2,'11003',2048), (current_date-1,'10580',8), (current_date-1,'10499',16), (current_date-1,'10740',32), (current_date-1,'10741',64), (current_date-2,'10741',128), (current_date-1,'10429',256), (current_date-1,'10771',512); {code} SQL with wrong result: {code:sql} SELECT org.the_date , org.org_type , org.org_id , org.dept_id , msr.`cco_stk_ttl` FROM ( SELECT cal.the_date , org.dept_id , case when org.region_id <=> null then 'nsc' when org.district_id <=> null then 'reg' else 'dis' end as org_type , coalesce(org.district_id, org.region_id, 'MMD') as org_id , org.district_id , org.region_id FROM test_kpi_date cal CROSS JOIN test_org_district org WHERE org.nsc_id = 'MMD' and org.dept_id IN ('ADVB') AND cal.the_date = CURRENT_DATE() - INTERVAL 1 DAY 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) ) org LEFT JOIN ( SELECT sub.the_date , dis.dept_id , dis.region_id , dis.district_id , SUM(sub.cco_stk_ttl) as cco_stk_ttl FROM test_kpi_measure sub JOIN test_org_partner org ON org.ptn_id = sub.ptn_id JOIN test_org_district dis ON dis.dept_id = org.dept_id AND dis.district_id = org.district_id WHERE dis.nsc_id = 'MMD' and dis.dept_id IN ('ADVB') -- AND sub.the_date = CURRENT_DATE() - INTERVAL 1 DAY GROUP BY sub.the_date, dis.dept_id, dis.region_id, dis.district_id WITH ROLLUP ) msr ON msr.the_date = org.the_date and msr.dept_id <=> org.dept_id and msr.region_id <=> org.region_id and msr.district_id <=> org.district_id {code} Code that works: {code:sql} CREATE OR REPLACE TABLE test_kpi_date ( "id" int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, the_date date NOT NULL -- , PRIMARY KEY ("the_date") -- , UNIQUE (the_date) ); {code} You can see, we try to use different definitions with the same behavior (wrong) when the_date get an index (PRIMARY/UNIQUE). Without UNIQUE/PRIMARY Index on the_date it works fine. |
| Description |
Wrong result, duplicate group by values and wrong aggregation "row"-data (row 1,2 and 5,6):
{code:sql} CREATE OR REPLACE TABLE test_kpi_date ( the_date date NOT NULL PRIMARY KEY ); {code} {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-12 dis 10 ADVB 1 2021-08-12 dis 10 ADVB 4096 2021-08-12 dis 11 ADVB 2 2021-08-12 dis 12 ADVB <null> 2021-08-12 reg 1 ADVB 1 2021-08-12 reg 1 ADVB 4098 2021-08-12 dis 21 ADVB 96 2021-08-12 dis 22 ADVB 256 2021-08-12 dis 23 ADVB 512 2021-08-12 reg 2 ADVB 864 2021-08-12 nsc MMD ADVB 4963 {code} generated with: {code:sql} CREATE OR REPLACE TABLE test_kpi_date ( "id" int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, the_date date NOT NULL ); {code} result looks good: {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-12 dis 10 ADVB 4097 2021-08-12 dis 11 ADVB 2 2021-08-12 dis 12 ADVB <null> 2021-08-12 reg 1 ADVB 4099 2021-08-12 dis 21 ADVB 96 2021-08-12 dis 22 ADVB 256 2021-08-12 dis 23 ADVB 512 2021-08-12 reg 2 ADVB 864 2021-08-12 nsc MMD ADVB 4963 {code} It looks like Test Data: {code:sql} SET sql_mode = 'REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI,ONLY_FULL_GROUP_BY' CREATE OR REPLACE TABLE test_kpi_date ( the_date date NOT NULL , PRIMARY KEY ("the_date") ); SELECT * FROM test_kpi_date tkd INSERT INTO test_kpi_date VALUES (current_date), (current_date-1), (current_date-2), (current_date-3); CREATE OR REPLACE TABLE test_org_district ( "id" int(11) 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") ); INSERT INTO test_org_district (nsc_id,dept_id,district_id,region_id) VALUES ('MMD','ADVB','10','1'), ('MMD','ADVB','11','1'), ('MMD','ADVB','21','2'), ('MMD','ADVB','22','2'); CREATE OR REPLACE TABLE "test_org_partner" ( "dept_id" char(4) CHARACTER SET utf8 NOT NULL, "ptn_id" char(5) CHARACTER SET utf8, "district_id" char(3) CHARACTER SET utf8 NOT NULL DEFAULT '0', "nsc_id" char(5) CHARACTER SET utf8 NOT NULL, PRIMARY KEY ("ptn_id","dept_id"), KEY "dept_ptn" ("dept_id","ptn_id"), KEY "dept_district_ptn" ("dept_id","district_id","ptn_id") ); INSERT INTO test_org_partner (dept_id,ptn_id,district_id,nsc_id) VALUES ('ADVB','10001','10','MMD'), ('ADVB','10002','10','MMD'), ('ADVB','10003','10','MMD'), ('ADVB','11001','11','MMD'), ('ADVB','11002','11','MMD'), ('ADVB','10740','21','MMD'), ('ADVB','10741','21','MMD'), ('ADVB','10429','22','MMD'), ('ADVB','10771','23','MMD'); CREATE OR REPLACE TABLE "test_kpi_measure" ( "the_date" date DEFAULT NULL, "ptn_id" char(5) CHARACTER SET utf8 NOT NULL DEFAULT '', "cco_stk_ttl" int(11) DEFAULT NULL, PRIMARY KEY ("the_date","ptn_id"), KEY "ptn_id_idx" ("ptn_id") ); INSERT INTO test_kpi_measure (the_date,ptn_id,cco_stk_ttl) VALUES (current_date-1,'10001',4096), (current_date-1,'10002',1), (current_date-2,'10002',null), (current_date-1,'11001',2), (current_date-2,'11001',4), (current_date-1,'11003',1024), (current_date-2,'11003',2048), (current_date-1,'10580',8), (current_date-1,'10499',16), (current_date-1,'10740',32), (current_date-1,'10741',64), (current_date-2,'10741',128), (current_date-1,'10429',256), (current_date-1,'10771',512); {code} SQL with wrong result: {code:sql} SELECT org.the_date , org.org_type , org.org_id , org.dept_id , msr.`cco_stk_ttl` FROM ( SELECT cal.the_date , org.dept_id , case when org.region_id <=> null then 'nsc' when org.district_id <=> null then 'reg' else 'dis' end as org_type , coalesce(org.district_id, org.region_id, 'MMD') as org_id , org.district_id , org.region_id FROM test_kpi_date cal CROSS JOIN test_org_district org WHERE org.nsc_id = 'MMD' and org.dept_id IN ('ADVB') AND cal.the_date = CURRENT_DATE() - INTERVAL 1 DAY 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) ) org LEFT JOIN ( SELECT sub.the_date , dis.dept_id , dis.region_id , dis.district_id , SUM(sub.cco_stk_ttl) as cco_stk_ttl FROM test_kpi_measure sub JOIN test_org_partner org ON org.ptn_id = sub.ptn_id JOIN test_org_district dis ON dis.dept_id = org.dept_id AND dis.district_id = org.district_id WHERE dis.nsc_id = 'MMD' and dis.dept_id IN ('ADVB') -- AND sub.the_date = CURRENT_DATE() - INTERVAL 1 DAY GROUP BY sub.the_date, dis.dept_id, dis.region_id, dis.district_id WITH ROLLUP ) msr ON msr.the_date = org.the_date and msr.dept_id <=> org.dept_id and msr.region_id <=> org.region_id and msr.district_id <=> org.district_id {code} Code that works: {code:sql} CREATE OR REPLACE TABLE test_kpi_date ( "id" int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, the_date date NOT NULL -- , PRIMARY KEY ("the_date") -- , UNIQUE (the_date) ); {code} You can see, we try to use different definitions with the same behavior (wrong) when the_date get an index (PRIMARY/UNIQUE). Without UNIQUE/PRIMARY Index on the_date it works fine. |
Wrong result, duplicate group by values and wrong aggregation "row"-data (row 1,2 and 5,6):
{code:sql} CREATE OR REPLACE TABLE test_kpi_date ( the_date date NOT NULL PRIMARY KEY ); {code} {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-12 dis 10 ADVB 1 2021-08-12 dis 10 ADVB 4096 2021-08-12 dis 11 ADVB 2 2021-08-12 dis 12 ADVB <null> 2021-08-12 reg 1 ADVB 1 2021-08-12 reg 1 ADVB 4098 2021-08-12 dis 21 ADVB 96 2021-08-12 dis 22 ADVB 256 2021-08-12 dis 23 ADVB 512 2021-08-12 reg 2 ADVB 864 2021-08-12 nsc MMD ADVB 4963 {code} generated with: {code:sql} CREATE OR REPLACE TABLE test_kpi_date ( "id" int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, the_date date NOT NULL ); {code} result looks good: {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-12 dis 10 ADVB 4097 2021-08-12 dis 11 ADVB 2 2021-08-12 dis 12 ADVB <null> 2021-08-12 reg 1 ADVB 4099 2021-08-12 dis 21 ADVB 96 2021-08-12 dis 22 ADVB 256 2021-08-12 dis 23 ADVB 512 2021-08-12 reg 2 ADVB 864 2021-08-12 nsc MMD ADVB 4963 {code} It looks like Test Data: {code:sql} SET sql_mode = 'REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI,ONLY_FULL_GROUP_BY' CREATE OR REPLACE TABLE test_kpi_date ( the_date date NOT NULL , PRIMARY KEY ("the_date") ); SELECT * FROM test_kpi_date tkd INSERT INTO test_kpi_date VALUES (current_date), (current_date-1), (current_date-2), (current_date-3); CREATE OR REPLACE TABLE test_org_district ( "id" int(11) 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") ); INSERT INTO test_org_district (nsc_id,dept_id,district_id,region_id) VALUES ('MMD','ADVB','10','1'), ('MMD','ADVB','11','1'), ('MMD','ADVB','21','2'), ('MMD','ADVB','22','2'); CREATE OR REPLACE TABLE "test_org_partner" ( "dept_id" char(4) CHARACTER SET utf8 NOT NULL, "ptn_id" char(5) CHARACTER SET utf8, "district_id" char(3) CHARACTER SET utf8 NOT NULL DEFAULT '0', "nsc_id" char(5) CHARACTER SET utf8 NOT NULL, PRIMARY KEY ("ptn_id","dept_id"), KEY "dept_ptn" ("dept_id","ptn_id"), KEY "dept_district_ptn" ("dept_id","district_id","ptn_id") ); INSERT INTO test_org_partner (dept_id,ptn_id,district_id,nsc_id) VALUES ('ADVB','10001','10','MMD'), ('ADVB','10002','10','MMD'), ('ADVB','10003','10','MMD'), ('ADVB','11001','11','MMD'), ('ADVB','11002','11','MMD'), ('ADVB','10740','21','MMD'), ('ADVB','10741','21','MMD'), ('ADVB','10429','22','MMD'), ('ADVB','10771','23','MMD'); CREATE OR REPLACE TABLE "test_kpi_measure" ( "the_date" date DEFAULT NULL, "ptn_id" char(5) CHARACTER SET utf8 NOT NULL DEFAULT '', "cco_stk_ttl" int(11) DEFAULT NULL, PRIMARY KEY ("the_date","ptn_id"), KEY "ptn_id_idx" ("ptn_id") ); INSERT INTO test_kpi_measure (the_date,ptn_id,cco_stk_ttl) VALUES (current_date-1,'10001',4096), (current_date-1,'10002',1), (current_date-2,'10002',null), (current_date-1,'11001',2), (current_date-2,'11001',4), (current_date-1,'11003',1024), (current_date-2,'11003',2048), (current_date-1,'10580',8), (current_date-1,'10499',16), (current_date-1,'10740',32), (current_date-1,'10741',64), (current_date-2,'10741',128), (current_date-1,'10429',256), (current_date-1,'10771',512); {code} SQL with wrong result: {code:sql} SELECT org.the_date , org.org_type , org.org_id , org.dept_id , msr.`cco_stk_ttl` FROM ( SELECT cal.the_date , org.dept_id , case when org.region_id <=> null then 'nsc' when org.district_id <=> null then 'reg' else 'dis' end as org_type , coalesce(org.district_id, org.region_id, 'MMD') as org_id , org.district_id , org.region_id FROM test_kpi_date cal CROSS JOIN test_org_district org WHERE org.nsc_id = 'MMD' and org.dept_id IN ('ADVB') AND cal.the_date = CURRENT_DATE() - INTERVAL 1 DAY 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) ) org LEFT JOIN ( SELECT sub.the_date , dis.dept_id , dis.region_id , dis.district_id , SUM(sub.cco_stk_ttl) as cco_stk_ttl FROM test_kpi_measure sub JOIN test_org_partner org ON org.ptn_id = sub.ptn_id JOIN test_org_district dis ON dis.dept_id = org.dept_id AND dis.district_id = org.district_id WHERE dis.nsc_id = 'MMD' and dis.dept_id IN ('ADVB') -- AND sub.the_date = CURRENT_DATE() - INTERVAL 1 DAY GROUP BY sub.the_date, dis.dept_id, dis.region_id, dis.district_id WITH ROLLUP ) msr ON msr.the_date = org.the_date and msr.dept_id <=> org.dept_id and msr.region_id <=> org.region_id and msr.district_id <=> org.district_id {code} the result is correct if there is no index on the column the_date: {code:sql} CREATE OR REPLACE TABLE test_kpi_date ( "id" int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, the_date date NOT NULL -- , PRIMARY KEY ("the_date") -- , UNIQUE (the_date) ); {code} You can see, we try to use different definitions with the same behavior (wrong) when the_date get an index (PRIMARY/UNIQUE). Without UNIQUE/PRIMARY Index on the_date it works fine. |
| Priority | Trivial [ 5 ] | Major [ 3 ] |
| Description |
Wrong result, duplicate group by values and wrong aggregation "row"-data (row 1,2 and 5,6):
{code:sql} CREATE OR REPLACE TABLE test_kpi_date ( the_date date NOT NULL PRIMARY KEY ); {code} {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-12 dis 10 ADVB 1 2021-08-12 dis 10 ADVB 4096 2021-08-12 dis 11 ADVB 2 2021-08-12 dis 12 ADVB <null> 2021-08-12 reg 1 ADVB 1 2021-08-12 reg 1 ADVB 4098 2021-08-12 dis 21 ADVB 96 2021-08-12 dis 22 ADVB 256 2021-08-12 dis 23 ADVB 512 2021-08-12 reg 2 ADVB 864 2021-08-12 nsc MMD ADVB 4963 {code} generated with: {code:sql} CREATE OR REPLACE TABLE test_kpi_date ( "id" int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, the_date date NOT NULL ); {code} result looks good: {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-12 dis 10 ADVB 4097 2021-08-12 dis 11 ADVB 2 2021-08-12 dis 12 ADVB <null> 2021-08-12 reg 1 ADVB 4099 2021-08-12 dis 21 ADVB 96 2021-08-12 dis 22 ADVB 256 2021-08-12 dis 23 ADVB 512 2021-08-12 reg 2 ADVB 864 2021-08-12 nsc MMD ADVB 4963 {code} It looks like Test Data: {code:sql} SET sql_mode = 'REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI,ONLY_FULL_GROUP_BY' CREATE OR REPLACE TABLE test_kpi_date ( the_date date NOT NULL , PRIMARY KEY ("the_date") ); SELECT * FROM test_kpi_date tkd INSERT INTO test_kpi_date VALUES (current_date), (current_date-1), (current_date-2), (current_date-3); CREATE OR REPLACE TABLE test_org_district ( "id" int(11) 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") ); INSERT INTO test_org_district (nsc_id,dept_id,district_id,region_id) VALUES ('MMD','ADVB','10','1'), ('MMD','ADVB','11','1'), ('MMD','ADVB','21','2'), ('MMD','ADVB','22','2'); CREATE OR REPLACE TABLE "test_org_partner" ( "dept_id" char(4) CHARACTER SET utf8 NOT NULL, "ptn_id" char(5) CHARACTER SET utf8, "district_id" char(3) CHARACTER SET utf8 NOT NULL DEFAULT '0', "nsc_id" char(5) CHARACTER SET utf8 NOT NULL, PRIMARY KEY ("ptn_id","dept_id"), KEY "dept_ptn" ("dept_id","ptn_id"), KEY "dept_district_ptn" ("dept_id","district_id","ptn_id") ); INSERT INTO test_org_partner (dept_id,ptn_id,district_id,nsc_id) VALUES ('ADVB','10001','10','MMD'), ('ADVB','10002','10','MMD'), ('ADVB','10003','10','MMD'), ('ADVB','11001','11','MMD'), ('ADVB','11002','11','MMD'), ('ADVB','10740','21','MMD'), ('ADVB','10741','21','MMD'), ('ADVB','10429','22','MMD'), ('ADVB','10771','23','MMD'); CREATE OR REPLACE TABLE "test_kpi_measure" ( "the_date" date DEFAULT NULL, "ptn_id" char(5) CHARACTER SET utf8 NOT NULL DEFAULT '', "cco_stk_ttl" int(11) DEFAULT NULL, PRIMARY KEY ("the_date","ptn_id"), KEY "ptn_id_idx" ("ptn_id") ); INSERT INTO test_kpi_measure (the_date,ptn_id,cco_stk_ttl) VALUES (current_date-1,'10001',4096), (current_date-1,'10002',1), (current_date-2,'10002',null), (current_date-1,'11001',2), (current_date-2,'11001',4), (current_date-1,'11003',1024), (current_date-2,'11003',2048), (current_date-1,'10580',8), (current_date-1,'10499',16), (current_date-1,'10740',32), (current_date-1,'10741',64), (current_date-2,'10741',128), (current_date-1,'10429',256), (current_date-1,'10771',512); {code} SQL with wrong result: {code:sql} SELECT org.the_date , org.org_type , org.org_id , org.dept_id , msr.`cco_stk_ttl` FROM ( SELECT cal.the_date , org.dept_id , case when org.region_id <=> null then 'nsc' when org.district_id <=> null then 'reg' else 'dis' end as org_type , coalesce(org.district_id, org.region_id, 'MMD') as org_id , org.district_id , org.region_id FROM test_kpi_date cal CROSS JOIN test_org_district org WHERE org.nsc_id = 'MMD' and org.dept_id IN ('ADVB') AND cal.the_date = CURRENT_DATE() - INTERVAL 1 DAY 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) ) org LEFT JOIN ( SELECT sub.the_date , dis.dept_id , dis.region_id , dis.district_id , SUM(sub.cco_stk_ttl) as cco_stk_ttl FROM test_kpi_measure sub JOIN test_org_partner org ON org.ptn_id = sub.ptn_id JOIN test_org_district dis ON dis.dept_id = org.dept_id AND dis.district_id = org.district_id WHERE dis.nsc_id = 'MMD' and dis.dept_id IN ('ADVB') -- AND sub.the_date = CURRENT_DATE() - INTERVAL 1 DAY GROUP BY sub.the_date, dis.dept_id, dis.region_id, dis.district_id WITH ROLLUP ) msr ON msr.the_date = org.the_date and msr.dept_id <=> org.dept_id and msr.region_id <=> org.region_id and msr.district_id <=> org.district_id {code} the result is correct if there is no index on the column the_date: {code:sql} CREATE OR REPLACE TABLE test_kpi_date ( "id" int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, the_date date NOT NULL -- , PRIMARY KEY ("the_date") -- , UNIQUE (the_date) ); {code} You can see, we try to use different definitions with the same behavior (wrong) when the_date get an index (PRIMARY/UNIQUE). Without UNIQUE/PRIMARY Index on the_date it works fine. |
Wrong result, duplicate group by values and wrong aggregation "row"-data (row 1,2 and 5,6):
{code:sql} CREATE OR REPLACE TABLE test_kpi_date ( the_date date NOT NULL PRIMARY KEY ); {code} {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-12 dis 10 ADVB 1 <- ????? 2021-08-12 dis 10 ADVB 4096 <- ????? 2021-08-12 dis 11 ADVB 2 2021-08-12 dis 12 ADVB <null> 2021-08-12 reg 1 ADVB 1 <- ????? 2021-08-12 reg 1 ADVB 4098 <- ????? 2021-08-12 dis 21 ADVB 96 2021-08-12 dis 22 ADVB 256 2021-08-12 dis 23 ADVB 512 2021-08-12 reg 2 ADVB 864 2021-08-12 nsc MMD ADVB 4963 {code} generated with: {code:sql} CREATE OR REPLACE TABLE test_kpi_date ( "id" int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, the_date date NOT NULL ); {code} result looks good: {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-12 dis 10 ADVB 4097 2021-08-12 dis 11 ADVB 2 2021-08-12 dis 12 ADVB <null> 2021-08-12 reg 1 ADVB 4099 2021-08-12 dis 21 ADVB 96 2021-08-12 dis 22 ADVB 256 2021-08-12 dis 23 ADVB 512 2021-08-12 reg 2 ADVB 864 2021-08-12 nsc MMD ADVB 4963 {code} It looks like Test Data: {code:sql} SET sql_mode = 'REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI,ONLY_FULL_GROUP_BY' CREATE OR REPLACE TABLE test_kpi_date ( the_date date NOT NULL , PRIMARY KEY ("the_date") ); SELECT * FROM test_kpi_date tkd INSERT INTO test_kpi_date VALUES (current_date), (current_date-1), (current_date-2), (current_date-3); CREATE OR REPLACE TABLE test_org_district ( "id" int(11) 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") ); INSERT INTO test_org_district (nsc_id,dept_id,district_id,region_id) VALUES ('MMD','ADVB','10','1'), ('MMD','ADVB','11','1'), ('MMD','ADVB','21','2'), ('MMD','ADVB','22','2'); CREATE OR REPLACE TABLE "test_org_partner" ( "dept_id" char(4) CHARACTER SET utf8 NOT NULL, "ptn_id" char(5) CHARACTER SET utf8, "district_id" char(3) CHARACTER SET utf8 NOT NULL DEFAULT '0', "nsc_id" char(5) CHARACTER SET utf8 NOT NULL, PRIMARY KEY ("ptn_id","dept_id"), KEY "dept_ptn" ("dept_id","ptn_id"), KEY "dept_district_ptn" ("dept_id","district_id","ptn_id") ); INSERT INTO test_org_partner (dept_id,ptn_id,district_id,nsc_id) VALUES ('ADVB','10001','10','MMD'), ('ADVB','10002','10','MMD'), ('ADVB','10003','10','MMD'), ('ADVB','11001','11','MMD'), ('ADVB','11002','11','MMD'), ('ADVB','10740','21','MMD'), ('ADVB','10741','21','MMD'), ('ADVB','10429','22','MMD'), ('ADVB','10771','23','MMD'); CREATE OR REPLACE TABLE "test_kpi_measure" ( "the_date" date DEFAULT NULL, "ptn_id" char(5) CHARACTER SET utf8 NOT NULL DEFAULT '', "cco_stk_ttl" int(11) DEFAULT NULL, PRIMARY KEY ("the_date","ptn_id"), KEY "ptn_id_idx" ("ptn_id") ); INSERT INTO test_kpi_measure (the_date,ptn_id,cco_stk_ttl) VALUES (current_date-1,'10001',4096), (current_date-1,'10002',1), (current_date-2,'10002',null), (current_date-1,'11001',2), (current_date-2,'11001',4), (current_date-1,'11003',1024), (current_date-2,'11003',2048), (current_date-1,'10580',8), (current_date-1,'10499',16), (current_date-1,'10740',32), (current_date-1,'10741',64), (current_date-2,'10741',128), (current_date-1,'10429',256), (current_date-1,'10771',512); {code} SQL with wrong result: {code:sql} SELECT org.the_date , org.org_type , org.org_id , org.dept_id , msr.`cco_stk_ttl` FROM ( SELECT cal.the_date , org.dept_id , case when org.region_id <=> null then 'nsc' when org.district_id <=> null then 'reg' else 'dis' end as org_type , coalesce(org.district_id, org.region_id, 'MMD') as org_id , org.district_id , org.region_id FROM test_kpi_date cal CROSS JOIN test_org_district org WHERE org.nsc_id = 'MMD' and org.dept_id IN ('ADVB') AND cal.the_date = CURRENT_DATE() - INTERVAL 1 DAY 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) ) org LEFT JOIN ( SELECT sub.the_date , dis.dept_id , dis.region_id , dis.district_id , SUM(sub.cco_stk_ttl) as cco_stk_ttl FROM test_kpi_measure sub JOIN test_org_partner org ON org.ptn_id = sub.ptn_id JOIN test_org_district dis ON dis.dept_id = org.dept_id AND dis.district_id = org.district_id WHERE dis.nsc_id = 'MMD' and dis.dept_id IN ('ADVB') -- AND sub.the_date = CURRENT_DATE() - INTERVAL 1 DAY GROUP BY sub.the_date, dis.dept_id, dis.region_id, dis.district_id WITH ROLLUP ) msr ON msr.the_date = org.the_date and msr.dept_id <=> org.dept_id and msr.region_id <=> org.region_id and msr.district_id <=> org.district_id {code} the result is correct if there is no index on the column the_date: {code:sql} CREATE OR REPLACE TABLE test_kpi_date ( "id" int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, the_date date NOT NULL -- , PRIMARY KEY ("the_date") -- , UNIQUE (the_date) ); {code} You can see, we try to use different definitions with the same behavior (wrong) when the_date get an index (PRIMARY/UNIQUE). Without UNIQUE/PRIMARY Index on the_date it works fine. |
| Description |
Wrong result, duplicate group by values and wrong aggregation "row"-data (row 1,2 and 5,6):
{code:sql} CREATE OR REPLACE TABLE test_kpi_date ( the_date date NOT NULL PRIMARY KEY ); {code} {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-12 dis 10 ADVB 1 <- ????? 2021-08-12 dis 10 ADVB 4096 <- ????? 2021-08-12 dis 11 ADVB 2 2021-08-12 dis 12 ADVB <null> 2021-08-12 reg 1 ADVB 1 <- ????? 2021-08-12 reg 1 ADVB 4098 <- ????? 2021-08-12 dis 21 ADVB 96 2021-08-12 dis 22 ADVB 256 2021-08-12 dis 23 ADVB 512 2021-08-12 reg 2 ADVB 864 2021-08-12 nsc MMD ADVB 4963 {code} generated with: {code:sql} CREATE OR REPLACE TABLE test_kpi_date ( "id" int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, the_date date NOT NULL ); {code} result looks good: {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-12 dis 10 ADVB 4097 2021-08-12 dis 11 ADVB 2 2021-08-12 dis 12 ADVB <null> 2021-08-12 reg 1 ADVB 4099 2021-08-12 dis 21 ADVB 96 2021-08-12 dis 22 ADVB 256 2021-08-12 dis 23 ADVB 512 2021-08-12 reg 2 ADVB 864 2021-08-12 nsc MMD ADVB 4963 {code} It looks like Test Data: {code:sql} SET sql_mode = 'REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI,ONLY_FULL_GROUP_BY' CREATE OR REPLACE TABLE test_kpi_date ( the_date date NOT NULL , PRIMARY KEY ("the_date") ); SELECT * FROM test_kpi_date tkd INSERT INTO test_kpi_date VALUES (current_date), (current_date-1), (current_date-2), (current_date-3); CREATE OR REPLACE TABLE test_org_district ( "id" int(11) 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") ); INSERT INTO test_org_district (nsc_id,dept_id,district_id,region_id) VALUES ('MMD','ADVB','10','1'), ('MMD','ADVB','11','1'), ('MMD','ADVB','21','2'), ('MMD','ADVB','22','2'); CREATE OR REPLACE TABLE "test_org_partner" ( "dept_id" char(4) CHARACTER SET utf8 NOT NULL, "ptn_id" char(5) CHARACTER SET utf8, "district_id" char(3) CHARACTER SET utf8 NOT NULL DEFAULT '0', "nsc_id" char(5) CHARACTER SET utf8 NOT NULL, PRIMARY KEY ("ptn_id","dept_id"), KEY "dept_ptn" ("dept_id","ptn_id"), KEY "dept_district_ptn" ("dept_id","district_id","ptn_id") ); INSERT INTO test_org_partner (dept_id,ptn_id,district_id,nsc_id) VALUES ('ADVB','10001','10','MMD'), ('ADVB','10002','10','MMD'), ('ADVB','10003','10','MMD'), ('ADVB','11001','11','MMD'), ('ADVB','11002','11','MMD'), ('ADVB','10740','21','MMD'), ('ADVB','10741','21','MMD'), ('ADVB','10429','22','MMD'), ('ADVB','10771','23','MMD'); CREATE OR REPLACE TABLE "test_kpi_measure" ( "the_date" date DEFAULT NULL, "ptn_id" char(5) CHARACTER SET utf8 NOT NULL DEFAULT '', "cco_stk_ttl" int(11) DEFAULT NULL, PRIMARY KEY ("the_date","ptn_id"), KEY "ptn_id_idx" ("ptn_id") ); INSERT INTO test_kpi_measure (the_date,ptn_id,cco_stk_ttl) VALUES (current_date-1,'10001',4096), (current_date-1,'10002',1), (current_date-2,'10002',null), (current_date-1,'11001',2), (current_date-2,'11001',4), (current_date-1,'11003',1024), (current_date-2,'11003',2048), (current_date-1,'10580',8), (current_date-1,'10499',16), (current_date-1,'10740',32), (current_date-1,'10741',64), (current_date-2,'10741',128), (current_date-1,'10429',256), (current_date-1,'10771',512); {code} SQL with wrong result: {code:sql} SELECT org.the_date , org.org_type , org.org_id , org.dept_id , msr.`cco_stk_ttl` FROM ( SELECT cal.the_date , org.dept_id , case when org.region_id <=> null then 'nsc' when org.district_id <=> null then 'reg' else 'dis' end as org_type , coalesce(org.district_id, org.region_id, 'MMD') as org_id , org.district_id , org.region_id FROM test_kpi_date cal CROSS JOIN test_org_district org WHERE org.nsc_id = 'MMD' and org.dept_id IN ('ADVB') AND cal.the_date = CURRENT_DATE() - INTERVAL 1 DAY 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) ) org LEFT JOIN ( SELECT sub.the_date , dis.dept_id , dis.region_id , dis.district_id , SUM(sub.cco_stk_ttl) as cco_stk_ttl FROM test_kpi_measure sub JOIN test_org_partner org ON org.ptn_id = sub.ptn_id JOIN test_org_district dis ON dis.dept_id = org.dept_id AND dis.district_id = org.district_id WHERE dis.nsc_id = 'MMD' and dis.dept_id IN ('ADVB') -- AND sub.the_date = CURRENT_DATE() - INTERVAL 1 DAY GROUP BY sub.the_date, dis.dept_id, dis.region_id, dis.district_id WITH ROLLUP ) msr ON msr.the_date = org.the_date and msr.dept_id <=> org.dept_id and msr.region_id <=> org.region_id and msr.district_id <=> org.district_id {code} the result is correct if there is no index on the column the_date: {code:sql} CREATE OR REPLACE TABLE test_kpi_date ( "id" int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, the_date date NOT NULL -- , PRIMARY KEY ("the_date") -- , UNIQUE (the_date) ); {code} You can see, we try to use different definitions with the same behavior (wrong) when the_date get an index (PRIMARY/UNIQUE). Without UNIQUE/PRIMARY Index on the_date it works fine. |
Wrong result, duplicate group by values and wrong aggregation "row"-data (row 1,2 and 5,6):
{code:sql} CREATE OR REPLACE TABLE test_kpi_date ( the_date date NOT NULL PRIMARY KEY ); {code} {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-12 dis 10 ADVB 1 <- ????? 2021-08-12 dis 10 ADVB 4096 <- ????? 2021-08-12 dis 11 ADVB 2 2021-08-12 dis 12 ADVB <null> 2021-08-12 reg 1 ADVB 1 <- ????? 2021-08-12 reg 1 ADVB 4098 <- ????? 2021-08-12 dis 21 ADVB 96 2021-08-12 dis 22 ADVB 256 2021-08-12 dis 23 ADVB 512 2021-08-12 reg 2 ADVB 864 2021-08-12 nsc MMD ADVB 4963 {code} generated with: {code:sql} CREATE OR REPLACE TABLE test_kpi_date ( "id" int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, the_date date NOT NULL ); {code} result looks good: {code:csv} the_date org_type org_id dept_id cco_stk_ttl 2021-08-12 dis 10 ADVB 4097 2021-08-12 dis 11 ADVB 2 2021-08-12 dis 12 ADVB <null> 2021-08-12 reg 1 ADVB 4099 2021-08-12 dis 21 ADVB 96 2021-08-12 dis 22 ADVB 256 2021-08-12 dis 23 ADVB 512 2021-08-12 reg 2 ADVB 864 2021-08-12 nsc MMD ADVB 4963 {code} It looks like Test Data: {code:sql} SET sql_mode = 'REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI,ONLY_FULL_GROUP_BY' CREATE OR REPLACE TABLE test_kpi_date ( the_date date NOT NULL , PRIMARY KEY ("the_date") ); SELECT * FROM test_kpi_date tkd INSERT INTO test_kpi_date VALUES (current_date), (current_date-1), (current_date-2), (current_date-3); CREATE OR REPLACE TABLE test_org_district ( "id" int(11) 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") ); INSERT INTO test_org_district (nsc_id,dept_id,district_id,region_id) VALUES ('MMD','ADVB','10','1'), ('MMD','ADVB','11','1'), ('MMD','ADVB','21','2'), ('MMD','ADVB','22','2'); CREATE OR REPLACE TABLE "test_org_partner" ( "dept_id" char(4) CHARACTER SET utf8 NOT NULL, "ptn_id" char(5) CHARACTER SET utf8, "district_id" char(3) CHARACTER SET utf8 NOT NULL DEFAULT '0', "nsc_id" char(5) CHARACTER SET utf8 NOT NULL, PRIMARY KEY ("ptn_id","dept_id"), KEY "dept_ptn" ("dept_id","ptn_id"), KEY "dept_district_ptn" ("dept_id","district_id","ptn_id") ); INSERT INTO test_org_partner (dept_id,ptn_id,district_id,nsc_id) VALUES ('ADVB','10001','10','MMD'), ('ADVB','10002','10','MMD'), ('ADVB','10003','10','MMD'), ('ADVB','11001','11','MMD'), ('ADVB','11002','11','MMD'), ('ADVB','10740','21','MMD'), ('ADVB','10741','21','MMD'), ('ADVB','10429','22','MMD'), ('ADVB','10771','23','MMD'); CREATE OR REPLACE TABLE "test_kpi_measure" ( "the_date" date DEFAULT NULL, "ptn_id" char(5) CHARACTER SET utf8 NOT NULL DEFAULT '', "cco_stk_ttl" int(11) DEFAULT NULL, PRIMARY KEY ("the_date","ptn_id"), KEY "ptn_id_idx" ("ptn_id") ); INSERT INTO test_kpi_measure (the_date,ptn_id,cco_stk_ttl) VALUES (current_date-1,'10001',4096), (current_date-1,'10002',1), (current_date-2,'10002',null), (current_date-1,'11001',2), (current_date-2,'11001',4), (current_date-1,'11003',1024), (current_date-2,'11003',2048), (current_date-1,'10580',8), (current_date-1,'10499',16), (current_date-1,'10740',32), (current_date-1,'10741',64), (current_date-2,'10741',128), (current_date-1,'10429',256), (current_date-1,'10771',512); {code} SQL with wrong result: {code:sql} SELECT org.the_date , org.org_type , org.org_id , org.dept_id , msr.`cco_stk_ttl` FROM ( SELECT cal.the_date , org.dept_id , case when org.region_id <=> null then 'nsc' when org.district_id <=> null then 'reg' else 'dis' end as org_type , coalesce(org.district_id, org.region_id, 'MMD') as org_id , org.district_id , org.region_id FROM test_kpi_date cal CROSS JOIN test_org_district org WHERE org.nsc_id = 'MMD' and org.dept_id IN ('ADVB') AND cal.the_date = CURRENT_DATE() - INTERVAL 1 DAY 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) ) org LEFT JOIN ( SELECT sub.the_date , dis.dept_id , dis.region_id , dis.district_id , SUM(sub.cco_stk_ttl) as cco_stk_ttl FROM test_kpi_measure sub JOIN test_org_partner org ON org.ptn_id = sub.ptn_id JOIN test_org_district dis ON dis.dept_id = org.dept_id AND dis.district_id = org.district_id WHERE dis.nsc_id = 'MMD' and dis.dept_id IN ('ADVB') -- AND sub.the_date = CURRENT_DATE() - INTERVAL 1 DAY GROUP BY sub.the_date, dis.dept_id, dis.region_id, dis.district_id WITH ROLLUP ) msr ON msr.the_date = org.the_date and msr.dept_id <=> org.dept_id and msr.region_id <=> org.region_id and msr.district_id <=> org.district_id {code} the result is correct if there is no index on the column the_date: {code:sql} CREATE OR REPLACE TABLE test_kpi_date ( "id" int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, the_date date NOT NULL -- , PRIMARY KEY ("the_date") -- , UNIQUE (the_date) ); {code} You can see, we try to use different definitions with the same behavior (wrong) when the_date get an index (PRIMARY/UNIQUE). Without UNIQUE/PRIMARY Index on the_date it works fine. |
| Priority | Major [ 3 ] | Critical [ 2 ] |
| Affects Version/s | 10.6.4 [ 26033 ] |
| Labels | need_feedback |
| Attachment | z-custom.cnf [ 59085 ] |
| Fix Version/s | 10.3 [ 22126 ] | |
| Fix Version/s | 10.4 [ 22408 ] | |
| Fix Version/s | 10.5 [ 23123 ] | |
| Fix Version/s | 10.6 [ 24028 ] |
| Affects Version/s | 10.3 [ 22126 ] | |
| Affects Version/s | 10.4 [ 22408 ] | |
| Affects Version/s | 10.5 [ 23123 ] | |
| Affects Version/s | 10.6 [ 24028 ] |
| Labels | need_feedback |
| Status | Open [ 1 ] | Confirmed [ 10101 ] |
Thank you!
Now I was able to reproduce it - after setting character_set_server=utf8mb4.
It is repeatable on MariaDB 10.3-10.6, 10.2 returned correct results.
|
|
-- source include/have_innodb.inc
|
|
|
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'); |
|
|
|
|
|
|
SELECT sql_no_cache org.the_date , |
org.org_id ,
|
org.dept_id ,
|
msr. cco_stk_ttl
|
FROM
|
( 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) ) org |
LEFT JOIN |
( SELECT sub.the_date , |
dis.dept_id ,
|
dis.region_id ,
|
dis.district_id ,
|
sum(sub.cco_stk_ttl) AS cco_stk_ttl |
FROM t2 sub |
JOIN t4 org ON org.ptn_id = sub.ptn_id |
JOIN t3 dis ON dis.dept_id = org.dept_id |
AND dis.district_id = org.district_id |
WHERE dis.nsc_id = 'MMD' |
AND dis.dept_id IN ('ADVB') |
GROUP BY sub.the_date, |
dis.dept_id,
|
dis.region_id,
|
dis.district_id WITH ROLLUP ) msr ON msr.the_date = org.the_date |
AND msr.dept_id <=> org.dept_id |
AND msr.region_id <=> org.region_id |
AND msr.district_id <=> org.district_id; |
|
that query returns:
2021-08-12 10 ADVB 1
|
2021-08-12 10 ADVB 4096
|
2021-08-12 11 ADVB 2
|
2021-08-12 1 ADVB 1
|
2021-08-12 1 ADVB 4098
|
2021-08-12 21 ADVB 96
|
2021-08-12 22 ADVB 256
|
2021-08-12 2 ADVB 352
|
2021-08-12 MMD ADVB 4451
|
without index on t1:
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
|
if there is no index on t4 (PRIMARY KEY ( ptn_id , dept_id )) :
the_date org_id dept_id cco_stk_ttl
|
2021-08-12 10 ADVB 18033
|
2021-08-12 11 ADVB 12022
|
2021-08-12 1 ADVB 30055
|
2021-08-12 21 ADVB 12022
|
2021-08-12 22 ADVB 6011
|
2021-08-12 2 ADVB 18033
|
2021-08-12 MMD ADVB 48088
|
I do not want to simplify the case as I am afraid to lose smth on the way, with those indexes, etc
| Assignee | Sergei Petrunia [ psergey ] |
Trying Alice's testcase.
EXPLAIN:
+------+-----------------+------------+--------+-------------------------+---------------+---------+--------------------------------------------------------+------+------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-----------------+------------+--------+-------------------------+---------------+---------+--------------------------------------------------------+------+------------------------------------+
|
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 4 | |
|
| 1 | PRIMARY | <derived3> | ref | key0 | key0 | 43 | org.the_date,org.dept_id,org.region_id,org.district_id | 2 | Using where |
|
| 3 | LATERAL DERIVED | sub | ref | PRIMARY | PRIMARY | 3 | org.the_date | 1 | Using where; Using filesort |
|
| 3 | LATERAL DERIVED | org | ref | PRIMARY | PRIMARY | 15 | j2.sub.ptn_id | 1 | Using where |
|
| 3 | LATERAL DERIVED | dis | eq_ref | dept_district,region_id | dept_district | 28 | const,func | 1 | Using index condition; Using where |
|
| 2 | DERIVED | cal | const | PRIMARY | PRIMARY | 3 | const | 1 | Using index |
|
| 2 | DERIVED | org | ALL | dept_district,region_id | NULL | NULL | NULL | 4 | Using where; Using filesort |
|
+------+-----------------+------------+--------+-------------------------+---------------+---------+--------------------------------------------------------+------+------------------------------------+
|
Note the "LATERAL DERIVED". Lateral Derived optimization is used to execute subquery that has "WITH ROLLUP". As far as I understand LATERAL DERIVED execution strategy, it is not capable of producing WITH ROLLUP's super-aggregate rows.
EXPLAIN with no index on t1:
+------+-------------+------------+------+-------------------------+---------+---------+--------------------------------------------------------+------+-------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+------------+------+-------------------------+---------+---------+--------------------------------------------------------+------+-------------------------------------------------+
|
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 16 | |
|
| 1 | PRIMARY | <derived3> | ref | key0 | key0 | 43 | org.the_date,org.dept_id,org.region_id,org.district_id | 5 | Using where |
|
| 3 | DERIVED | sub | ALL | PRIMARY | NULL | NULL | NULL | 14 | Using temporary; Using filesort |
|
| 3 | DERIVED | org | ref | PRIMARY | PRIMARY | 15 | j2.sub.ptn_id | 1 | Using where |
|
| 3 | DERIVED | dis | ALL | dept_district,region_id | NULL | NULL | NULL | 4 | Using where; Using join buffer (flat, BNL join) |
|
| 2 | DERIVED | cal | ALL | NULL | NULL | NULL | NULL | 4 | Using where; Using temporary; Using filesort |
|
| 2 | DERIVED | org | ALL | dept_district,region_id | NULL | NULL | NULL | 4 | Using where; Using join buffer (flat, BNL join) |
|
+------+-------------+------------+------+-------------------------+---------+---------+--------------------------------------------------------+------+-------------------------------------------------+
|
EXPLAIN if there is no index on t4 (PRIMARY KEY ( ptn_id , dept_id )) :
+------+-----------------+------------+-------+-------------------------+---------+---------+--------------------------------------------------------+------+--------------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-----------------+------------+-------+-------------------------+---------+---------+--------------------------------------------------------+------+--------------------------------------------------------+
|
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 4 | |
|
| 1 | PRIMARY | <derived3> | ref | key0 | key0 | 43 | org.the_date,org.dept_id,org.region_id,org.district_id | 2 | Using where |
|
| 3 | LATERAL DERIVED | sub | ref | PRIMARY | PRIMARY | 3 | org.the_date | 1 | Using temporary; Using filesort |
|
| 3 | LATERAL DERIVED | dis | ALL | dept_district,region_id | NULL | NULL | NULL | 4 | Using where; Using join buffer (flat, BNL join) |
|
| 3 | LATERAL DERIVED | org | ALL | NULL | NULL | NULL | NULL | 9 | Using where; Using join buffer (incremental, BNL join) |
|
| 2 | DERIVED | cal | const | PRIMARY | PRIMARY | 3 | const | 1 | Using index |
|
| 2 | DERIVED | org | ALL | dept_district,region_id | NULL | NULL | NULL | 4 | Using where; Using filesort |
|
+------+-----------------+------------+-------+-------------------------+---------+---------+--------------------------------------------------------+------+--------------------------------------------------------+
|
LATERAL is still used (although the join order inside the LATERAL is different).
Query result with no index on t1 is the same as what I get when I put the subqueries' results into temp. tables and join the these temp.tables.
Changed the alias in the top-level select from org to org2.
Also, materialized one of the derived tables, it's irrelevant.
Result:
https://gist.github.com/spetrunia/38fee60313471877279aafb933452baf
EXPLAIN output:
EXPLAIN: {
|
"query_block": {
|
"select_id": 1,
|
"const_condition": "1",
|
"table": {
|
"table_name": "org2",
|
"access_type": "ALL",
|
"rows": 7,
|
"filtered": 100
|
},
|
"table": {
|
"table_name": "<derived2>",
|
"access_type": "ref",
|
"possible_keys": ["key0"],
|
"key": "key0",
|
"key_length": "43",
|
"used_key_parts": ["the_date", "dept_id", "region_id", "district_id"],
|
"ref": [
|
"j2.org2.the_date",
|
"j2.org2.dept_id",
|
"j2.org2.region_id",
|
"j2.org2.district_id"
|
],
|
"rows": 2,
|
"filtered": 100,
|
"attached_condition": "trigcond(msr.dept_id <=> org2.dept_id and msr.region_id <=> org2.region_id and msr.district_id <=> org2.district_id)",
|
"materialized": {
|
"lateral": 1,
|
"query_block": {
|
"select_id": 2,
|
"read_sorted_file": {
|
"filesort": {
|
"sort_key": "sub.the_date, dis.dept_id, dis.region_id, dis.district_id",
|
"table": {
|
"table_name": "sub",
|
"access_type": "ref",
|
"possible_keys": ["PRIMARY"],
|
"key": "PRIMARY",
|
"key_length": "3",
|
"used_key_parts": ["the_date"],
|
"ref": ["j2.org2.the_date"],
|
"rows": 1,
|
"filtered": 100,
|
"attached_condition": "sub.the_date <=> org2.the_date"
|
}
|
}
|
},
|
"table": {
|
"table_name": "org",
|
"access_type": "ref",
|
"possible_keys": ["PRIMARY"],
|
"key": "PRIMARY",
|
"key_length": "15",
|
"used_key_parts": ["ptn_id"],
|
"ref": ["j2.sub.ptn_id"],
|
"rows": 1,
|
"filtered": 100,
|
"attached_condition": "convert(org.dept_id using utf8mb4) = 'ADVB'"
|
},
|
"table": {
|
"table_name": "dis",
|
"access_type": "eq_ref",
|
"possible_keys": ["dept_district", "region_id"],
|
"key": "dept_district",
|
"key_length": "28",
|
"used_key_parts": ["dept_id", "district_id"],
|
"ref": ["const", "func"],
|
"rows": 1,
|
"filtered": 100,
|
"index_condition": "dis.dept_id = 'ADVB' and dis.district_id = convert(org.district_id using utf8mb4)",
|
"attached_condition": "dis.nsc_id = 'MMD'"
|
}
|
}
|
}
|
}
|
}
|
}
|
The interesting part is here,:
"attached_condition": "trigcond(msr.dept_id <=> org2.dept_id and msr.region_id <=> org2.region_id and msr.district_id <=> org2.district_id)",
|
"materialized": {
|
"lateral": 1,
|
"query_block": {
|
"select_id": 2,
|
"read_sorted_file": {
|
"filesort": {
|
"sort_key": "sub.the_date, dis.dept_id, dis.region_id, dis.district_id",
|
"table": {
|
"table_name": "sub",
|
....
|
} // Table sub
|
} // filesort
|
}, // read_sorted_file
|
...
|
"table": {
|
"table_name": "dis",
|
....
|
Note that the sort_key uses fields from table "dis", which is joined with the sort result!
This happens because of the remove_const() call:
group_list= remove_const(this, group_list, conds, |
rollup.state == ROLLUP::STATE_NONE,
|
&simple_group);
|
remove_const() calls const_expression_in_where() here:
if (cond && const_expression_in_where(cond,order->item[0])) |
{
|
DBUG_PRINT("info",("removing: %s", order->item[0]->full_name())); |
continue; |
}
|
where
order->item[0]= "dis.region_id"
|
cond=
|
is.nsc_id = 'MMD' and dis.dept_id = 'ADVB' and
|
convert(org.dept_id using utf8mb4) = 'ADVB' and
|
dis.district_id = convert(org.district_id using utf8mb4) and
|
dis.dept_id = org2.dept_id and dis.region_id = org2.region_id and
|
dis.district_id = org2.district_id and sub.the_date = org2.the_date and
|
org.ptn_id = sub.ptn_id
|
Note the "dis.region_id = org2.region_id" part in the WHERE.
Looks like it's ok?
... No, actually this is the reason why we get the wrong query result.
The wrong query output looks like this:
Incorrect Result:
|
+------------+--------+---------+-------------+
|
| the_date | org_id | dept_id | cco_stk_ttl |
|
+------------+--------+---------+-------------+
|
| 2021-08-12 | 10 | ADVB | 1 | WRONG: this and the next row should have been
|
| 2021-08-12 | 10 | ADVB | 4096 | WRONG: ...one row with cco_stk_ttl=4097!
|
|
|
| 2021-08-12 | 11 | ADVB | 2 |
|
|
|
| 2021-08-12 | 1 | ADVB | 1 | WRONG: this and the next row should have been
|
| 2021-08-12 | 1 | ADVB | 4098 | WRONG:... one row with cco_stk_ttl=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 |
|
+------------+--------+---------+-------------+
|
Debugging how the first row read was obtained, I can see these table reads:
org2(the_date,dept_id,org_id,district_id,region_id)=(2021-08-12,ADVB,10,10,1)
|
|
|
sub(the_date,ptn_id,cco_stk_ttl)=(2021-08-12,10001,4096)
|
org(dept_id,ptn_id,district_id)=(ADVB,10001,10)
|
dis(id,nsc_id,dept_id,district_id,region_id)=(1,MMD,ADVB,10,1)
|
sub(the_date,ptn_id,cco_stk_ttl)=(2021-08-12,11001,2)
|
org(dept_id,ptn_id,district_id)=(ADVB,11001,11)
|
dis(id,nsc_id,dept_id,district_id,region_id)=(2,MMD,ADVB,11,1)
|
send_data
|
### ROW1!
|
|
|
|
|
sub(the_date,ptn_id,cco_stk_ttl)=(2021-08-12,10771,512)
|
org(dept_id,ptn_id,district_id)=(ADVB,10771,23)
|
sub(the_date,ptn_id,cco_stk_ttl)=(2021-08-12,10741,64)
|
org(dept_id,ptn_id,district_id)=(ADVB,10741,21)
|
dis(id,nsc_id,dept_id,district_id,region_id)=(3,MMD,ADVB,21,2)
|
send_data
|
|
|
|
|
sub(the_date,ptn_id,cco_stk_ttl)=(2021-08-12,10740,32)
|
org(dept_id,ptn_id,district_id)=(ADVB,10740,21)
|
dis(id,nsc_id,dept_id,district_id,region_id)=(3,MMD,ADVB,21,2)
|
|
|
sub(the_date,ptn_id,cco_stk_ttl)=(2021-08-12,10580,8)
|
sub(the_date,ptn_id,cco_stk_ttl)=(2021-08-12,10499,16)
|
sub(the_date,ptn_id,cco_stk_ttl)=(2021-08-12,10429,256)
|
org(dept_id,ptn_id,district_id)=(ADVB,10429,22)
|
dis(id,nsc_id,dept_id,district_id,region_id)=(4,MMD,ADVB,22,2)
|
send_data
|
|
|
|
|
sub(the_date,ptn_id,cco_stk_ttl)=(2021-08-12,10002,1)
|
org(dept_id,ptn_id,district_id)=(ADVB,10002,10)
|
dis(id,nsc_id,dept_id,district_id,region_id)=(1,MMD,ADVB,10,1)
|
## ^ ROW2!
|
send_data
|
|
|
sub(the_date,ptn_id,cco_stk_ttl)=(2021-08-12,11003,1024)
|
send_data
|
|
|
msr(the_date,dept_id,region_id,district_id,cco_stk_ttl)=(2021-08-12,ADVB,1,10,1)
|
msr(the_date,dept_id,region_id,district_id,cco_stk_ttl)=(2021-08-12,ADVB,1,10,4096)
|
Looking at the above and subquery's GROUP BY clause:
GROUP BY sub.the_date, dis.dept_id, dis.region_id, dis.district_id
|
FIXED FIXED Changes
|
In the considered record combinations, region_id is 1 initially, then it changes to 2, and then back to 1!
This is why two rows from the same group were not grouped together in the query's output.
But one can clearly see that the table outside the subquery:
org2(the_date,dept_id,org_id,district_id,region_id)=(2021-08-12,ADVB,10,10,1)
|
has the value for region_id and it's 1! Why does the subquery enumerate record combinations that do not have region_id=1 ?
As shown a few comments above,
join->conds= .... AND dis.region_id = org2.region_id AND ....
|
however, it doesn't get attached to any table. make_join_select() calls make_cond_for_table( retain_ref_cond=false)
it makes this call:
/*
|
If cond is an equality injected for split optimization then
|
a. when retain_ref_cond == false : cond is removed unconditionally
|
(cond that supports ref access is removed by the preceding code)
|
b. when retain_ref_cond == true : cond is removed if it does not
|
support ref access
|
*/
|
if (left_item->type() == Item::FIELD_ITEM && |
is_eq_cond_injected_for_split_opt((Item_func_eq *) cond) &&
|
Then, is_eq_cond_injected_for_split_opt looks into join->spl_opt_info->inj_cond_list which has these conditions:
dis.dept_id = org2.dept_id
|
dis.region_id = org2.region_id // Match
|
dis.district_id = org2.district_id
|
sub.the_date = org2.the_date
|
finds the condition there. This causes make_cond_for_table() to discard it.
A patch that fixes the problem described above:
http://lists.askmonty.org/pipermail/commits/2021-November/014779.html
LATERAL DERIVED can run WITH ROLLUP queries.
Suppose, the GROUP BY clause is
GROUP BY col1, col2, ... colN WITH ROLLUP
|
Suppose, LATERAL DERIVED code pushes down a condition on colK, that is, we have "colK=...".
The execution code is able to generate all super-aggregate rows that have summaries for columns col_N where N>K.
Note that the restriction "colK=..." means we are not interested in super-aggregate rows that have summaries over column colK. This also implies that we are not interested in super-aggregate rows that have summaries over any column col_J where J < K.
From this, it follows that LATERAL DERIVED optimization is capable of executing WITH ROLLUP queries.
| Status | Confirmed [ 10101 ] | In Progress [ 3 ] |
| Assignee | Sergei Petrunia [ psergey ] | Igor Babaev [ igor ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
| Workflow | MariaDB v3 [ 124193 ] | MariaDB v4 [ 143798 ] |
Takeaways from yesterday call:
- It is not clear why the suggested patch would NOT work (that is, no counterexamples were provided)
- The participants lack confidence in this patch and consider this fix a feature, not a bugfix.
- Decided to use approach #1: just disable LATERAL DERIVED for WITH ROLLUP queries.
http://lists.askmonty.org/pipermail/commits/2022-January/014833.html . This Implements the conclusions from the last call. igor, please review.
Here' the explanation why the current implementation of the split optimization cannot be used with ROLLUP.
Consider tables t1,t2
create table t1 (a int, b int, c int) |
create table t2 (a int, b int, c int, key (a,b)); |
and the query
select s from t1, (select sum(c) as s from t2 group by a,b) dt where t1.a=dt.a and t1.b=dt.b and t1.a in (1,3,5) and t1.b between 2 and 4; |
In this case the split optimization can be applied that will return certain rows from dt. These rows can follow in any order. For example first for the group (a=3,b=2), then for the group (a=2,b=2), then for the group (a=5,b=4), then for the group (a=3,b=4). It's obviously that in this case we cannot have one register to calculate sum(c) with the same value of a.
| Assignee | Igor Babaev [ igor ] | Sergei Petrunia [ psergey ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
| Link | This issue relates to MDEV-27496 [ MDEV-27496 ] |
Filed MDEV-27496 to figure out whether Split Materialized can be used with WITH ROLLUP.
Pushed the "basic" fix variant which makes Split Materialized not to be used for WITH ROLLUP queries.
| Component/s | Optimizer [ 10200 ] | |
| Fix Version/s | 10.3.33 [ 26805 ] | |
| Fix Version/s | 10.4.23 [ 26807 ] | |
| Fix Version/s | 10.5.14 [ 26809 ] | |
| Fix Version/s | 10.6.6 [ 26811 ] | |
| Fix Version/s | 10.7.2 [ 26813 ] | |
| Fix Version/s | 10.3 [ 22126 ] | |
| Fix Version/s | 10.4 [ 22408 ] | |
| Fix Version/s | 10.5 [ 23123 ] | |
| Fix Version/s | 10.6 [ 24028 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | Stalled [ 10000 ] | Closed [ 6 ] |
| Zendesk Related Tickets | 150990 |
Could you please add .cnf file(s)? I tried to repeat the issue, but I was getting the expected result.