An argument to support Circular Joins in Columnstore

drop schema if exists zz;

create schema if not exists zz;

use zz;

CREATE TABLE TEST_CLAIMS (

TRACKING_ID INT(11),

AUD_ST_POL_ID INT(11),

CLASS_CD VARCHAR(4)

)

ENGINE = INNODB;

INSERT INTO TEST_CLAIMS (TRACKING_ID, AUD_ST_POL_ID, CLASS_CD) VALUES (1, 111111111, '1111');

INSERT INTO TEST_CLAIMS (TRACKING_ID, AUD_ST_POL_ID, CLASS_CD) VALUES (1, 222222222, '2222');

INSERT INTO TEST_CLAIMS (TRACKING_ID, AUD_ST_POL_ID, CLASS_CD) VALUES (1, 333333333, '3333');

INSERT INTO TEST_CLAIMS (TRACKING_ID, AUD_ST_POL_ID, CLASS_CD) VALUES (2, 444444444, '1111');

INSERT INTO TEST_CLAIMS (TRACKING_ID, AUD_ST_POL_ID, CLASS_CD) VALUES (2, 555555555, '2222');

INSERT INTO TEST_CLAIMS (TRACKING_ID, AUD_ST_POL_ID, CLASS_CD) VALUES (2, 666666666, '3333');

CREATE TABLE TEST_HEADER (

TRACKING_ID INT(11),

AUD_ST_POL_ID INT(11),

ST_CD INT(11)

)

ENGINE = INNODB ;

INSERT INTO TEST_HEADER (TRACKING_ID, AUD_ST_POL_ID, ST_CD) VALUES (1, 111111111, 1);

INSERT INTO TEST_HEADER (TRACKING_ID, AUD_ST_POL_ID, ST_CD) VALUES (1, 222222222, 2);

INSERT INTO TEST_HEADER (TRACKING_ID, AUD_ST_POL_ID, ST_CD) VALUES (1, 333333333, NULL);

INSERT INTO TEST_HEADER (TRACKING_ID, AUD_ST_POL_ID, ST_CD) VALUES (2, 444444444, 1);

INSERT INTO TEST_HEADER (TRACKING_ID, AUD_ST_POL_ID, ST_CD) VALUES (2, 555555555, 1);

INSERT INTO TEST_HEADER (TRACKING_ID, AUD_ST_POL_ID, ST_CD) VALUES (2, 666666666, 1);

CREATE TABLE TEST_RATEMASTER (

ST_CD INT(11),

CLASS_CD VARCHAR(4)

)

ENGINE = INNODB;

INSERT INTO TEST_RATEMASTER (ST_CD, CLASS_CD) VALUES (1, '1111');

INSERT INTO TEST_RATEMASTER (ST_CD, CLASS_CD) VALUES (1, '2222');

INSERT INTO TEST_RATEMASTER (ST_CD, CLASS_CD) VALUES (1, '3333');

INSERT INTO TEST_RATEMASTER (ST_CD, CLASS_CD) VALUES (2, '1111');

INSERT INTO TEST_RATEMASTER (ST_CD, CLASS_CD) VALUES (2, '2222');

INSERT INTO TEST_RATEMASTER (ST_CD, CLASS_CD) VALUES (2, '3333');

INSERT INTO TEST_RATEMASTER (ST_CD, CLASS_CD) VALUES (3, '1111');

INSERT INTO TEST_RATEMASTER (ST_CD, CLASS_CD) VALUES (3, '2222');

INSERT INTO TEST_RATEMASTER (ST_CD, CLASS_CD) VALUES (3, '3333');

SELECT   C.TRACKING_ID, C.AUD_ST_POL_ID, C.CLASS_CD, H.TRACKING_ID, H.AUD_ST_POL_ID, H.ST_CD, R.ST_CD, R.CLASS_CD

FROM TEST_CLAIMS C

INNER JOIN TEST_HEADER H ON C.TRACKING_ID = H.TRACKING_ID AND C.AUD_ST_POL_ID = H.AUD_ST_POL_ID

LEFT JOIN  TEST_RATEMASTER R ON C.CLASS_CD = R.CLASS_CD AND H.ST_CD = R.ST_CD

WHERE C.TRACKING_ID = 1;

+-------------+---------------+----------+-------------+---------------+-------+-------+----------+

| TRACKING_ID | AUD_ST_POL_ID | CLASS_CD | TRACKING_ID | AUD_ST_POL_ID | ST_CD | ST_CD | CLASS_CD |

+-------------+---------------+----------+-------------+---------------+-------+-------+----------+

|           1 |     111111111 | 1111     |           1 |     111111111 |     1 |     1 | 1111     |

|           1 |     222222222 | 2222     |           1 |     222222222 |     2 |     2 | 2222     |

|           1 |     333333333 | 3333     |           1 |     333333333 |  NULL |  NULL | NULL     |

+-------------+---------------+----------+-------------+---------------+-------+-------+----------+

alter table TEST_CLAIMS engine=columnstore;

alter table TEST_HEADER engine=columnstore;

alter table TEST_RATEMASTER engine=columnstore;

SELECT   C.TRACKING_ID, C.AUD_ST_POL_ID, C.CLASS_CD, H.TRACKING_ID, H.AUD_ST_POL_ID, H.ST_CD, R.ST_CD, R.CLASS_CD

FROM TEST_CLAIMS C

INNER JOIN TEST_HEADER H ON C.TRACKING_ID = H.TRACKING_ID AND C.AUD_ST_POL_ID = H.AUD_ST_POL_ID

LEFT JOIN  TEST_RATEMASTER R ON C.CLASS_CD = R.CLASS_CD AND H.ST_CD = R.ST_CD

WHERE C.TRACKING_ID = 1;

ERROR 1815 (HY000): Internal error: MCS-1003: Circular joins are not supported.