Details
-
Bug
-
Status: Closed (View Workflow)
-
Blocker
-
Resolution: Fixed
-
5.5.1
-
None
-
2021-1, 2021-2
Description
Queries involving joins and aggregation could return incorrect results.
A work around is to
SET columnstore_select_handler = OFF;
|
However, this could lead to performance degradation. Below is a reproducible example:
drop schema if exists poc;
|
create schema poc;
|
use poc;
|
|
drop table if exists holiday_config;
|
create table holiday_config(
|
region_id varchar(32) NOT NULL,
|
metric_name varchar(64) NOT NULL,
|
holiday_type varchar(32) NOT NULL
|
)ENGINE=innodb DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
|
|
drop table if exists surgery_holiday;
|
create table surgery_holiday(
|
region_id varchar(32) NOT NULL,
|
holiday_date date NOT NULL,
|
holiday_type varchar(32) NOT NULL
|
)ENGINE=innodb DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
|
|
drop table if exists location_master;
|
create table location_master(
|
region_id varchar(32) NOT NULL,
|
location_id varchar(32) NOT NULL
|
)ENGINE=innodb DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
|
|
drop table if exists room_master;
|
create table room_master(
|
room_id varchar(32) NOT NULL,
|
location_id varchar(32) NOT NULL
|
)ENGINE=innodb DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
|
|
drop table if exists orcase_essentials;
|
CREATE TABLE orcase_essentials (
|
surgery_date date NOT NULL,
|
location varchar(32) DEFAULT NULL,
|
room_id varchar(32) DEFAULT NULL
|
) ENGINE=innodb DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
|
|
drop table if exists orcase_essentials_cs;
|
CREATE TABLE orcase_essentials_cs (
|
surgery_date date NOT NULL,
|
location varchar(32) DEFAULT NULL,
|
room_id varchar(32) DEFAULT NULL
|
) ENGINE=columnstore DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
|
|
|
truncate table holiday_config;
|
truncate table surgery_holiday;
|
truncate table location_master;
|
truncate table room_master;
|
truncate table orcase_essentials;
|
truncate table orcase_essentials_cs;
|
|
INSERT INTO holiday_config (region_id,metric_name,holiday_type) VALUES
|
('R1','ADD_ON_CASE_RATIO','REGULAR')
|
,('R1','BLOCK_UTILIZATION','COVID')
|
,('R1','BLOCK_UTILIZATION','REGULAR')
|
,('R1','FIRST_CASE_DELAY_MINUTES','REGULAR')
|
,('R1','FIRST_CASE_ON_TIME_PERCENT','REGULAR')
|
,('R1','ROOM_UTILIZATION','COVID')
|
,('R1','ROOM_UTILIZATION','REGULAR')
|
,('R1','STAFFED_ROOM_UTILIZATION','COVID')
|
,('R1','STAFFED_ROOM_UTILIZATION','REGULAR')
|
,('R1','TURNOVER_MINUTES','REGULAR')
|
;
|
|
INSERT INTO location_master (region_id,location_id) VALUES
|
('R1','Location_X')
|
;
|
|
INSERT INTO room_master (room_id,location_id) VALUES
|
('room_1','Location_X')
|
;
|
|
|
INSERT INTO surgery_holiday (region_id,holiday_date,holiday_type) VALUES
|
('R1','2020-05-07','COVID')
|
;
|
|
INSERT INTO orcase_essentials (surgery_date,location,room_id) VALUES
|
('2020-05-07','Location_X','room_1')
|
,('2020-05-07','Location_X','room_1')
|
,('2020-05-07','Location_X','room_1')
|
;
|
|
INSERT INTO orcase_essentials_cs (surgery_date,location,room_id) VALUES
|
('2020-05-07','Location_X','room_1')
|
,('2020-05-07','Location_X','room_1')
|
,('2020-05-07','Location_X','room_1')
|
;
|
This produces expected results:
SELECT *
|
FROM orcase_essentials _e
|
INNER JOIN location_master _lm
|
ON _e.location = _lm.location_id
|
LEFT JOIN (
|
SELECT DISTINCT rm.room_id, _sh.holiday_date
|
FROM surgery_holiday _sh
|
INNER JOIN holiday_config _hc
|
ON _sh.region_id = _hc.region_id
|
AND _hc.metric_name = 'CASE_LENGTH_ACCURACY'
|
AND _sh.holiday_type = _hc.holiday_type
|
INNER JOIN location_master lm
|
ON _sh.region_id = lm.region_id
|
INNER JOIN room_master rm
|
ON lm.location_id = rm.location_id
|
) _h
|
ON _e.room_id = _h.room_id
|
AND _e.surgery_date = _h.holiday_date
|
WHERE _e.surgery_date = '2020-05-07'
|
;
|
+--------------+------------+---------+-----------+-------------+---------+--------------+
|
| surgery_date | location | room_id | region_id | location_id | room_id | holiday_date |
|
+--------------+------------+---------+-----------+-------------+---------+--------------+
|
| 2020-05-07 | Location_X | room_1 | R1 | Location_X | NULL | NULL |
|
| 2020-05-07 | Location_X | room_1 | R1 | Location_X | NULL | NULL |
|
| 2020-05-07 | Location_X | room_1 | R1 | Location_X | NULL | NULL |
|
+--------------+------------+---------+-----------+-------------+---------+--------------+
|
This version does not:
SELECT *
|
FROM orcase_essentials_cs _e
|
INNER JOIN location_master _lm
|
ON _e.location = _lm.location_id
|
LEFT JOIN (
|
SELECT DISTINCT rm.room_id, _sh.holiday_date
|
FROM surgery_holiday _sh
|
INNER JOIN holiday_config _hc
|
ON _sh.region_id = _hc.region_id
|
AND _hc.metric_name = 'CASE_LENGTH_ACCURACY'
|
AND _sh.holiday_type = _hc.holiday_type
|
INNER JOIN location_master lm
|
ON _sh.region_id = lm.region_id
|
INNER JOIN room_master rm
|
ON lm.location_id = rm.location_id
|
) _h
|
ON _e.room_id = _h.room_id
|
AND _e.surgery_date = _h.holiday_date
|
WHERE _e.surgery_date = '2020-05-07'
|
;
|
+--------------+------------+---------+-----------+-------------+---------+--------------+
|
| surgery_date | location | room_id | region_id | location_id | room_id | holiday_date |
|
+--------------+------------+---------+-----------+-------------+---------+--------------+
|
| 2020-05-07 | Location_X | room_1 | R1 | Location_X | room_1 | 2020-05-07 |
|
| 2020-05-07 | Location_X | room_1 | R1 | Location_X | room_1 | 2020-05-07 |
|
| 2020-05-07 | Location_X | room_1 | R1 | Location_X | room_1 | 2020-05-07 |
|
+--------------+------------+---------+-----------+-------------+---------+--------------+
|