|
I have tried to figure out a workaround for a circular join, but I could create one:
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 |
|
+-------------+---------------+----------+-------------+---------------+-------+-------+----------+
|
However, if you switch the tables to engine=columnstore, the same query fails.
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.
|
Is there any plan to make circular joins a supported query in Columnstore? An argument can be made that LEFT/RIGHT circular joins are the only way to include for nulls to join circularly as seen in this example.
|