[MCOL-4493] Wrong query results are produced when impossible conditions are used in ON join predicates - Jira

Todd Stoffel (Inactive) created issue - 2021-01-12 21:22

Todd Stoffel (Inactive) made changes - 2021-01-12 21:22

Field	Original Value	New Value
Rank		Ranked higher

Todd Stoffel (Inactive) made changes - 2021-01-12 21:49

Description

Queries involving joins and aggregation could return incorrect results.

A work around is to
{code:java}
SET columnstore_select_handler = OFF;
{code}

However, this could lead to performance degradation. Below is a reproducible example.

{code:java}
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')
;
{code}

This produces expected results:

{code:java}
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'
;
{code}

This version does not:

{code:java}
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'
;
{code}

Queries involving joins and aggregation could return incorrect results.

A work around is to
{code:java}
SET columnstore_select_handler = OFF;
{code}

However, this could lead to performance degradation. Below is a reproducible example.

{code:java}
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')
;
{code}

This produces expected results:

{code:java}
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'
;
{code}

{code:java}
+--------------+------------+---------+-----------+-------------+---------+--------------+
| 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 |
+--------------+------------+---------+-----------+-------------+---------+--------------+
{code}

This version does not:

{code:java}
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'
;
{code}

{code:java}
+--------------+------------+---------+-----------+-------------+---------+--------------+
| surgery_date | location | room_id | region_id | location_id | room_id | holiday_date |
+--------------+------------+---------+-----------+-------------+---------+--------------+
| 2020-05-07 | Location_X | room_1 | R1 | Location_X | {color:red}room_1 | 2020-05-07{color} |
| 2020-05-07 | Location_X | room_1 | R1 | Location_X | {color:red}room_1 | 2020-05-07{color} |
| 2020-05-07 | Location_X | room_1 | R1 | Location_X | {color:red}room_1 | 2020-05-07{color} |
+--------------+------------+---------+-----------+-------------+---------+--------------+
{code}

Todd Stoffel (Inactive) made changes - 2021-01-12 21:49

Description

Queries involving joins and aggregation could return incorrect results.

A work around is to
{code:java}
SET columnstore_select_handler = OFF;
{code}

However, this could lead to performance degradation. Below is a reproducible example.

{code:java}
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')
;
{code}

This produces expected results:

{code:java}
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'
;
{code}

{code:java}
+--------------+------------+---------+-----------+-------------+---------+--------------+
| 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 |
+--------------+------------+---------+-----------+-------------+---------+--------------+
{code}

This version does not:

{code:java}
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'
;
{code}

{code:java}
+--------------+------------+---------+-----------+-------------+---------+--------------+
| surgery_date | location | room_id | region_id | location_id | room_id | holiday_date |
+--------------+------------+---------+-----------+-------------+---------+--------------+
| 2020-05-07 | Location_X | room_1 | R1 | Location_X | {color:red}room_1 | 2020-05-07{color} |
| 2020-05-07 | Location_X | room_1 | R1 | Location_X | {color:red}room_1 | 2020-05-07{color} |
| 2020-05-07 | Location_X | room_1 | R1 | Location_X | {color:red}room_1 | 2020-05-07{color} |
+--------------+------------+---------+-----------+-------------+---------+--------------+
{code}

Queries involving joins and aggregation could return incorrect results.

A work around is to
{code:java}
SET columnstore_select_handler = OFF;
{code}

However, this could lead to performance degradation. Below is a reproducible example.

{code:java}
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')
;
{code}

This produces expected results:

{code:java}
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'
;
{code}

{code:java}
+--------------+------------+---------+-----------+-------------+---------+--------------+
| 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 |
+--------------+------------+---------+-----------+-------------+---------+--------------+
{code}

This version does not:

{code:java}
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'
;
{code}

{code:java}
+--------------+------------+---------+-----------+-------------+---------+--------------+
| 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 |
+--------------+------------+---------+-----------+-------------+---------+--------------+
{code}

Todd Stoffel (Inactive) made changes - 2021-01-12 21:50

Description

Queries involving joins and aggregation could return incorrect results.

A work around is to
{code:java}
SET columnstore_select_handler = OFF;
{code}

However, this could lead to performance degradation. Below is a reproducible example.

{code:java}
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')
;
{code}

This produces expected results:

{code:java}
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'
;
{code}

{code:java}
+--------------+------------+---------+-----------+-------------+---------+--------------+
| 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 |
+--------------+------------+---------+-----------+-------------+---------+--------------+
{code}

This version does not:

{code:java}
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'
;
{code}

{code:java}
+--------------+------------+---------+-----------+-------------+---------+--------------+
| 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 |
+--------------+------------+---------+-----------+-------------+---------+--------------+
{code}

Queries involving joins and aggregation could return incorrect results.

A work around is to
{code:java}
SET columnstore_select_handler = OFF;
{code}

However, this could lead to performance degradation. Below is a reproducible example.

{code:java}
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')
;
{code}

This produces expected results:

{code:java}
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'
;
{code}

{code:java}
+--------------+------------+---------+-----------+-------------+---------+--------------+
| 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 |
+--------------+------------+---------+-----------+-------------+---------+--------------+
{code}

This version does not:

{code:java}
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'
;
{code}

{noformat}
+--------------+------------+---------+-----------+-------------+---------+--------------+
| 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 |
+--------------+------------+---------+-----------+-------------+---------+--------------+
{noformat}

Todd Stoffel (Inactive) made changes - 2021-01-12 21:50

Description

Queries involving joins and aggregation could return incorrect results.

A work around is to
{code:java}
SET columnstore_select_handler = OFF;
{code}

However, this could lead to performance degradation. Below is a reproducible example.

{code:java}
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')
;
{code}

This produces expected results:

{code:java}
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'
;
{code}

{code:java}
+--------------+------------+---------+-----------+-------------+---------+--------------+
| 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 |
+--------------+------------+---------+-----------+-------------+---------+--------------+
{code}

This version does not:

{code:java}
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'
;
{code}

{noformat}
+--------------+------------+---------+-----------+-------------+---------+--------------+
| 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 |
+--------------+------------+---------+-----------+-------------+---------+--------------+
{noformat}

Queries involving joins and aggregation could return incorrect results.

A work around is to
{code:java}
SET columnstore_select_handler = OFF;
{code}

However, this could lead to performance degradation. Below is a reproducible example.

{code:java}
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')
;
{code}

This produces expected results:

{code:java}
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'
;
{code}

{code:java}
+--------------+------------+---------+-----------+-------------+---------+--------------+
| 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 |
+--------------+------------+---------+-----------+-------------+---------+--------------+
{code}

This version does not:

{code:java}
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'
;
{code}

{noformat}
+--------------+------------+---------+-----------+-------------+---------+--------------+
| 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 | {color:red}2020-05-07{color} |
| 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 |
+--------------+------------+---------+-----------+-------------+---------+--------------+
{noformat}

Todd Stoffel (Inactive) made changes - 2021-01-12 21:51

Description

Queries involving joins and aggregation could return incorrect results.

A work around is to
{code:java}
SET columnstore_select_handler = OFF;
{code}

However, this could lead to performance degradation. Below is a reproducible example.

{code:java}
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')
;
{code}

This produces expected results:

{code:java}
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'
;
{code}

{code:java}
+--------------+------------+---------+-----------+-------------+---------+--------------+
| 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 |
+--------------+------------+---------+-----------+-------------+---------+--------------+
{code}

This version does not:

{code:java}
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'
;
{code}

{noformat}
+--------------+------------+---------+-----------+-------------+---------+--------------+
| 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 | {color:red}2020-05-07{color} |
| 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 |
+--------------+------------+---------+-----------+-------------+---------+--------------+
{noformat}

Queries involving joins and aggregation could return incorrect results.

A work around is to
{code:java}
SET columnstore_select_handler = OFF;
{code}

However, this could lead to performance degradation. Below is a reproducible example.

{code:java}
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')
;
{code}

This produces expected results:

{code:java}
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'
;
{code}

{noformat}
+--------------+------------+---------+-----------+-------------+---------+--------------+
| 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 |
+--------------+------------+---------+-----------+-------------+---------+--------------+
{noformat}

This version does not:

{code:java}
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'
;
{code}

{noformat}
+--------------+------------+---------+-----------+-------------+---------+--------------+
| 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 |
+--------------+------------+---------+-----------+-------------+---------+--------------+
{noformat}

Todd Stoffel (Inactive) made changes - 2021-01-12 21:51

Description

Queries involving joins and aggregation could return incorrect results.

A work around is to
{code:java}
SET columnstore_select_handler = OFF;
{code}

However, this could lead to performance degradation. Below is a reproducible example.

{code:java}
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')
;
{code}

This produces expected results:

{code:java}
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'
;
{code}

{noformat}
+--------------+------------+---------+-----------+-------------+---------+--------------+
| 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 |
+--------------+------------+---------+-----------+-------------+---------+--------------+
{noformat}

This version does not:

{code:java}
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'
;
{code}

{noformat}
+--------------+------------+---------+-----------+-------------+---------+--------------+
| 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 |
+--------------+------------+---------+-----------+-------------+---------+--------------+
{noformat}

Queries involving joins and aggregation could return incorrect results.

A work around is to
{noformat}
SET columnstore_select_handler = OFF;
{noformat}

However, this could lead to performance degradation. Below is a reproducible example.

{noformat}
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')
;
{noformat}

This produces expected results:

{noformat}
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'
;
{noformat}

{noformat}
+--------------+------------+---------+-----------+-------------+---------+--------------+
| 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 |
+--------------+------------+---------+-----------+-------------+---------+--------------+
{noformat}

This version does not:

{noformat}
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'
;
{noformat}

{noformat}
+--------------+------------+---------+-----------+-------------+---------+--------------+
| 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 |
+--------------+------------+---------+-----------+-------------+---------+--------------+
{noformat}

Todd Stoffel (Inactive) made changes - 2021-01-12 21:52

Description

Queries involving joins and aggregation could return incorrect results.

A work around is to
{noformat}
SET columnstore_select_handler = OFF;
{noformat}

However, this could lead to performance degradation. Below is a reproducible example.

{noformat}
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')
;
{noformat}

This produces expected results:

{noformat}
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'
;
{noformat}

{noformat}
+--------------+------------+---------+-----------+-------------+---------+--------------+
| 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 |
+--------------+------------+---------+-----------+-------------+---------+--------------+
{noformat}

This version does not:

{noformat}
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'
;
{noformat}

{noformat}
+--------------+------------+---------+-----------+-------------+---------+--------------+
| 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 |
+--------------+------------+---------+-----------+-------------+---------+--------------+
{noformat}

Queries involving joins and aggregation could return incorrect results.

A work around is to
{noformat}
SET columnstore_select_handler = OFF;
{noformat}

However, this could lead to performance degradation. Below is a reproducible example:

{noformat}
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')
;
{noformat}

This produces expected results:

{noformat}
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'
;
{noformat}

{noformat}
+--------------+------------+---------+-----------+-------------+---------+--------------+
| 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 |
+--------------+------------+---------+-----------+-------------+---------+--------------+
{noformat}

This version does not:

{noformat}
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'
;
{noformat}

{noformat}
+--------------+------------+---------+-----------+-------------+---------+--------------+
| 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 |
+--------------+------------+---------+-----------+-------------+---------+--------------+
{noformat}

Todd Stoffel (Inactive) made changes - 2021-01-12 21:53

Affects Version/s

5.5.1 [ 25030 ]

Todd Stoffel (Inactive) made changes - 2021-01-12 21:54

Component/s

Columnstore Select Handler [ 16801 ]

Todd Stoffel (Inactive) made changes - 2021-01-12 21:58

Status

Open [ 1 ]

In Progress [ 3 ]

Todd Stoffel (Inactive) made changes - 2021-01-12 21:58

Issue Type

Task [ 3 ]

Bug [ 1 ]

Todd Stoffel (Inactive) made changes - 2021-01-12 22:03

Assignee

Roman [ drrtuy ]

Gregory Dorman (Inactive) made changes - 2021-01-12 22:39

Assignee

Roman [ drrtuy ]

Gregory Dorman [ gdorman ]

Todd Stoffel (Inactive) made changes - 2021-01-13 20:11

Priority

Major [ 3 ]

Critical [ 2 ]

Todd Stoffel (Inactive) made changes - 2021-01-13 20:14

Rank

Ranked lower

Todd Stoffel (Inactive) made changes - 2021-01-14 23:16

Fix Version/s

5.6.1 [ 25031 ]

Todd Stoffel (Inactive) made changes - 2021-01-14 23:16

Rank

Ranked higher

Todd Stoffel (Inactive) made changes - 2021-01-14 23:16

Priority

Critical [ 2 ]

Blocker [ 1 ]

Gregory Dorman (Inactive) made changes - 2021-01-14 23:40

Summary

Select Handler Providing Incorrect Results

Wrong query restuls are produced when DECIMAL aggregates are used in filters or joins

Gregory Dorman (Inactive) made changes - 2021-01-14 23:41

Summary

Wrong query restuls are produced when DECIMAL aggregates are used in filters or joins

Wrong query restuls are produced when impossible conditions are used in ON join predicates

Gregory Dorman (Inactive) made changes - 2021-01-14 23:45

Assignee

Gregory Dorman [ gdorman ]

Roman [ drrtuy ]

David Hall (Inactive) made changes - 2021-01-18 15:11

Summary

Wrong query restuls are produced when impossible conditions are used in ON join predicates

Wrong query results are produced when impossible conditions are used in ON join predicates

Todd Stoffel (Inactive) made changes - 2021-01-19 19:02

Sprint

2021-1 [ 480 ]

Todd Stoffel (Inactive) made changes - 2021-01-19 19:02

Rank

Ranked lower

Gregory Dorman (Inactive) made changes - 2021-01-25 17:09

Sprint

2021-1 [ 480 ]

2021-1, 2021-2 [ 480, 481 ]

Roman made changes - 2021-01-26 09:05

Assignee

Roman [ drrtuy ]

Gagan Goel [ tntnatbry ]

Gagan Goel (Inactive) made changes - 2021-01-27 14:45

Assignee	Gagan Goel [ tntnatbry ]	Roman [ drrtuy ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Roman made changes - 2021-01-29 10:32

Status

In Review [ 10002 ]

In Testing [ 10301 ]

Roman made changes - 2021-01-29 10:32

Assignee

Roman [ drrtuy ]

Daniel Lee [ dleeyh ]

Gagan Goel (Inactive) made changes - 2021-01-29 13:02

Assignee	Daniel Lee [ dleeyh ]	Gagan Goel [ tntnatbry ]
Status	In Testing [ 10301 ]	Stalled [ 10000 ]

Gagan Goel (Inactive) made changes - 2021-01-29 13:02

Status

Stalled [ 10000 ]

In Progress [ 3 ]

Gagan Goel (Inactive) made changes - 2021-02-01 10:26

Assignee	Gagan Goel [ tntnatbry ]	Roman [ drrtuy ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Roman made changes - 2021-02-01 14:30

Status

In Review [ 10002 ]

In Testing [ 10301 ]

Roman made changes - 2021-02-01 14:30

Assignee

Roman [ drrtuy ]

Daniel Lee [ dleeyh ]

Todd Stoffel (Inactive) made changes - 2021-02-01 17:11

Fix Version/s		5.5.2 [ 25601 ]
Fix Version/s	5.6.1 [ 25031 ]

Daniel Lee (Inactive) made changes - 2021-02-05 16:45

Resolution		Fixed [ 1 ]
Status	In Testing [ 10301 ]	Closed [ 6 ]

MariaDB ColumnStore

Wrong query results are produced when impossible conditions are used in ON join predicates

Details

Description

Attachments

Activity

People

Dates

Git Integration