mirror of
https://github.com/mariadb-corporation/mariadb-columnstore-engine.git
synced 2025-04-29 21:36:45 +03:00
1d5f309b8f
This patch adds support for queries with circular joins. Currently support added for inner joins only.
124 lines
4.6 KiB
Plaintext
124 lines
4.6 KiB
Plaintext
|
|
#-- source ../include/have_columnstore.inc
|
|
|
|
--disable_warnings
|
|
DROP DATABASE IF EXISTS mcol1205;
|
|
--enable_warnings
|
|
|
|
CREATE DATABASE mcol1205;
|
|
|
|
USE mcol1205;
|
|
|
|
/* simple circular inner join `=` operator */
|
|
create table t1 (a int, b int) engine=columnstore;
|
|
create table t2 (b int, c int) engine=columnstore;
|
|
create table t3 (a int, c int) engine=columnstore;
|
|
|
|
insert into t1 values (1, 2), (2, 3), (3, 4), (4, 5), (5, 6), (6, 7);
|
|
insert into t2 values (1, 2), (2, 3), (3, 4), (4, 5), (5, 6), (6, 7);
|
|
insert into t3 values (2, 4), (3, 5), (4, 6), (5, 10), (6, 12);
|
|
|
|
select * from t1 inner join t2 on (t1.b = t2.b) inner join t3 on (t1.a = t3.a and t2.c = t3.c);
|
|
|
|
drop table t1;
|
|
drop table t2;
|
|
drop table t3;
|
|
|
|
/* simple circular inner join with `where` filter */
|
|
create table t1 (a int, b int) engine=columnstore;
|
|
create table t2 (b int, c int) engine=columnstore;
|
|
create table t3 (a int, c int) engine=columnstore;
|
|
|
|
insert into t1 values (1, 2), (2, 3), (3, 4), (4, 5), (5, 6), (6, 7);
|
|
insert into t2 values (1, 2), (2, 3), (3, 4), (4, 5), (5, 6), (6, 7);
|
|
insert into t3 values (2, 4), (3, 5), (4, 6), (5, 10), (6, 12);
|
|
|
|
select * from t1 inner join t2 on (t1.b = t2.b) inner join t3 on (t1.a = t3.a) where t2.c = t3.c;
|
|
|
|
drop table t1;
|
|
drop table t2;
|
|
drop table t3;
|
|
|
|
/* circular inner join with filter */
|
|
create table t1 (a int, b int, f int) engine=columnstore;
|
|
create table t2 (b int, c int) engine=columnstore;
|
|
create table t3 (a int, c int, f int) engine=columnstore;
|
|
|
|
insert into t1 values (1, 2, 1), (2, 3, 2), (3, 4, 3), (4, 5, 4), (5, 6, 5), (6, 7, 6);
|
|
insert into t2 values (1, 2), (2, 3), (3, 4), (4, 5), (5, 6), (6, 7);
|
|
insert into t3 values (2, 4, 1), (3, 5, 2), (4, 6, 4), (5, 10, 5), (6, 12, 6);
|
|
|
|
select * from t1 inner join t2 on (t1.b = t2.b) inner join t3 on (t1.a = t3.a and t2.c = t3.c) where t1.f > t3.f;
|
|
|
|
drop table t1;
|
|
drop table t2;
|
|
drop table t3;
|
|
|
|
/* circular `where` node inner join with filter */
|
|
create table t1 (a int, b int, f int) engine=columnstore;
|
|
create table t2 (b int, c int) engine=columnstore;
|
|
create table t3 (a int, c int, f int) engine=columnstore;
|
|
|
|
insert into t1 values (1, 2, 1), (2, 3, 2), (3, 4, 3), (4, 5, 4), (5, 6, 5), (6, 7, 6);
|
|
insert into t2 values (1, 2), (2, 3), (3, 4), (4, 5), (5, 6), (6, 7);
|
|
insert into t3 values (2, 4, 1), (3, 5, 2), (4, 6, 4), (5, 10, 5), (6, 12, 6);
|
|
|
|
select * from t1 inner join t2 on (t1.b = t2.b) inner join t3 on (t1.a = t3.a) where t2.c = t3.c and t1.f > t3.f;
|
|
|
|
drop table t1;
|
|
drop table t2;
|
|
drop table t3;
|
|
|
|
/* circular join more than 2 join keys */
|
|
create table t1 (a int, b int, f int) engine=columnstore;
|
|
create table t2 (b int, c int) engine=columnstore;
|
|
create table t3 (a int, c int, f int) engine=columnstore;
|
|
|
|
insert into t1 values (1, 2, 1), (2, 3, 2), (3, 4, 3), (4, 5, 4), (5, 6, 5), (6, 7, 6);
|
|
insert into t2 values (1, 2), (2, 3), (3, 4), (4, 5), (5, 6), (6, 7);
|
|
insert into t3 values (2, 4, 1), (3, 5, 2), (4, 6, 4), (5, 10, 5), (6, 12, 6);
|
|
|
|
select * from t1 inner join t2 on (t1.b = t2.b) inner join t3 on (t1.a = t3.a and t2.c = t3.c and t1.f = t3.f);
|
|
|
|
drop table t1;
|
|
drop table t2;
|
|
drop table t3;
|
|
|
|
/* circular join with 3 loops */
|
|
create table t1 (a int, b int, f int) engine=columnstore;
|
|
create table t2 (b int, c int) engine=columnstore;
|
|
create table t3 (a int, c int, f int) engine=columnstore;
|
|
create table t4 (a int, b int) engine=columnstore;
|
|
create table t5 (a int, b int) engine=columnstore;
|
|
|
|
insert into t1 values (1, 2, 1), (2, 3, 2), (3, 4, 3), (4, 5, 4), (5, 6, 5), (6, 7, 6);
|
|
insert into t2 values (1, 2), (2, 3), (3, 4), (4, 5), (5, 6), (6, 7);
|
|
insert into t3 values (2, 4, 1), (3, 5, 2), (4, 6, 4), (5, 10, 5), (6, 12, 6);
|
|
insert into t4 values (1, 2), (2, 3), (3, 4), (4, 5), (5, 6), (6, 7);
|
|
insert into t5 values (1, 2), (2, 3), (3, 4), (4, 5), (5, 6), (6, 7);
|
|
|
|
select * from t1 inner join t2 on (t1.b = t2.b) inner join t3 on (t1.a = t3.a and t2.c = t3.c) inner join t4 on (t1.a = t4.a and t2.b = t4.b) inner join t5 on (t4.a = t5.a and t3.a = t5.a);
|
|
|
|
drop table t1;
|
|
drop table t2;
|
|
drop table t3;
|
|
drop table t4;
|
|
drop table t5;
|
|
|
|
/* circular joins for dict */
|
|
create table t1 (a varchar(255), b varchar(255)) engine=columnstore;
|
|
create table t2 (b varchar(255), c varchar(255)) engine=columnstore;
|
|
create table t3 (a varchar(255), c varchar(255)) engine=columnstore;
|
|
|
|
insert into t1 values ("1","2"), ("2","3"), ("3","4"), ("4","5"), ("5", "6"), ("6","7");
|
|
insert into t2 values ("1","2"), ("2","3"), ("3","4"), ("4","5"), ("5","6"), ("6","7");
|
|
insert into t3 values ("2","4"), ("3","5"), ("4","6"), ("5", "10"), ("6", "12");
|
|
|
|
select * from t1 inner join t2 on (t1.b = t2.b) inner join t3 on (t1.a = t3.a and t2.c = t3.c);
|
|
|
|
drop table t1;
|
|
drop table t2;
|
|
drop table t3;
|
|
|
|
DROP DATABASE mcol1205;
|