in regression-test/suites/nereids_rules_p0/mv/dimension_2_join_agg/dimension_2_join_agg.groovy [163:860]
left join (select l_orderkey, l_partkey, l_suppkey, l_quantity, l_shipdate from lineitem group by l_orderkey, l_partkey, l_suppkey, l_quantity, l_shipdate) as t1
on t1.l_orderkey = orders.o_orderkey
group by
t1.l_shipdate, t1.l_quantity, t1.l_orderkey """
def left_mv_stmt_7 = """select t1.l_shipdate, t1.col1, t1.l_orderkey
from orders
left join (select l_orderkey, l_partkey, l_suppkey, l_quantity, l_shipdate, sum(l_quantity) as col1 from lineitem group by l_orderkey, l_partkey, l_suppkey, l_quantity, l_shipdate) as t1
on t1.l_orderkey = orders.o_orderkey
group by
t1.l_shipdate, t1.col1, t1.l_orderkey
"""
def left_mv_stmt_8 = """select t1.l_shipdate, t1.col1, t1.l_orderkey
from orders
left join (select l_orderkey, l_partkey, l_suppkey, l_quantity, l_shipdate, sum(l_quantity) as col1 from lineitem where l_orderkey > 1 + 1 group by l_orderkey, l_partkey, l_suppkey, l_quantity, l_shipdate) as t1
on t1.l_orderkey = orders.o_orderkey
group by
t1.l_shipdate, t1.col1, t1.l_orderkey
"""
def left_mv_stmt_9 = """select t1.l_shipdate, t1.col1, t1.l_orderkey + t1.col2 as col3
from orders
left join (select l_orderkey, l_partkey, l_suppkey, l_quantity, l_shipdate, sum(l_quantity) as col1, count(*) as col2 from lineitem where l_orderkey > 1 + 1 group by l_orderkey, l_partkey, l_suppkey, l_quantity, l_shipdate) as t1
on t1.l_orderkey = orders.o_orderkey
group by
t1.l_shipdate, t1.col1, col3
"""
def left_mv_stmt_10 = """select t1.sum_total, max_total+min_total as col3, count_all
from orders
left join (select l_orderkey, sum(l_quantity) as sum_total,
max(l_quantity) as max_total,
min(l_quantity) as min_total,
count(*) as count_all,
sum(l_quantity) + count(*) as col5,
bitmap_union(to_bitmap(case when l_quantity > 1 and l_orderkey IN (1, 3) then l_partkey else null end)) as cnt_1,
bitmap_union(to_bitmap(case when l_quantity > 2 and l_orderkey IN (2) then l_partkey else null end)) as cnt_2
from lineitem where l_orderkey > 1 + 1 group by l_orderkey) as t1
on t1.l_orderkey = orders.o_orderkey
group by
t1.sum_total, col3, count_all
"""
// inner join + agg (function + group by + +-*/ + filter)
def inner_mv_stmt_1 = """select t1.o_orderdate, t1.o_shippriority, t1.o_orderkey
from (select o_orderkey, o_custkey, o_orderstatus, o_orderdate, o_shippriority from orders group by o_orderkey, o_custkey, o_orderstatus, o_orderdate, o_shippriority) as t1
inner join lineitem on lineitem.l_orderkey = t1.o_orderkey
group by
t1.o_orderdate, t1.o_shippriority, t1.o_orderkey"""
def inner_mv_stmt_2 = """select t1.o_orderdate, t1.o_orderkey, t1.col1
from (select o_orderkey, o_custkey, o_orderstatus, o_orderdate, sum(o_shippriority) as col1 from orders group by o_orderkey, o_custkey, o_orderstatus, o_orderdate) as t1
inner join lineitem on lineitem.l_orderkey = t1.o_orderkey
group by
t1.o_orderdate,t1.o_orderkey, t1.col1
"""
def inner_mv_stmt_3 = """select t1.o_orderdate, t1.o_orderkey, t1.col1
from (select o_orderkey, o_custkey, o_orderstatus, o_orderdate, sum(o_shippriority) as col1 from orders where o_orderkey > 1 + 1 group by o_orderkey, o_custkey, o_orderstatus, o_orderdate) as t1
inner join lineitem on lineitem.l_orderkey = t1.o_orderkey
group by
t1.o_orderdate,t1.o_orderkey, t1.col1
"""
def inner_mv_stmt_4 = """select t1.o_orderdate, (t1.o_orderkey+t1.col2) as col3, t1.col1
from (select o_orderkey, o_custkey, o_orderstatus, o_orderdate, sum(o_shippriority) as col1, count(*) as col2 from orders where o_orderkey >= 1 + 1 group by o_orderkey, o_custkey, o_orderstatus, o_orderdate) as t1
inner join lineitem on lineitem.l_orderkey = t1.o_orderkey
group by
t1.o_orderdate,col3, t1.col1
"""
def inner_mv_stmt_5 = """select t1.sum_total, max_total+min_total as col3, count_all
from (select o_orderkey, sum(o_totalprice) as sum_total,
max(o_totalprice) as max_total,
min(o_totalprice) as min_total,
count(*) as count_all,
sum(o_totalprice) + count(*) as col5,
bitmap_union(to_bitmap(case when o_shippriority > 1 and o_orderkey IN (1, 3) then o_custkey else null end)) as cnt_1,
bitmap_union(to_bitmap(case when o_shippriority > 2 and o_orderkey IN (2) then o_custkey else null end)) as cnt_2
from orders where o_orderkey >= 1 + 1 group by o_orderkey) as t1
inner join lineitem on lineitem.l_orderkey = t1.o_orderkey
group by t1.sum_total, col3, count_all
"""
def inner_mv_stmt_6 = """select t1.l_shipdate, t1.l_quantity, t1.l_orderkey
from orders
inner join (select l_orderkey, l_partkey, l_suppkey, l_quantity, l_shipdate from lineitem group by l_orderkey, l_partkey, l_suppkey, l_quantity, l_shipdate) as t1
on t1.l_orderkey = orders.o_orderkey
group by
t1.l_shipdate, t1.l_quantity, t1.l_orderkey """
def inner_mv_stmt_7 = """select t1.l_shipdate, t1.col1, t1.l_orderkey
from orders
inner join (select l_orderkey, l_partkey, l_suppkey, l_quantity, l_shipdate, sum(l_quantity) as col1 from lineitem group by l_orderkey, l_partkey, l_suppkey, l_quantity, l_shipdate) as t1
on t1.l_orderkey = orders.o_orderkey
group by
t1.l_shipdate, t1.col1, t1.l_orderkey
"""
def inner_mv_stmt_8 = """select t1.l_shipdate, t1.col1, t1.l_orderkey
from orders
inner join (select l_orderkey, l_partkey, l_suppkey, l_quantity, l_shipdate, sum(l_quantity) as col1 from lineitem where l_orderkey > 1 + 1 group by l_orderkey, l_partkey, l_suppkey, l_quantity, l_shipdate) as t1
on t1.l_orderkey = orders.o_orderkey
group by
t1.l_shipdate, t1.col1, t1.l_orderkey
"""
def inner_mv_stmt_9 = """select t1.l_shipdate, t1.col1, t1.l_orderkey + t1.col2 as col3
from orders
inner join (select l_orderkey, l_partkey, l_suppkey, l_quantity, l_shipdate, sum(l_quantity) as col1, count(*) as col2 from lineitem where l_orderkey > 1 + 1 group by l_orderkey, l_partkey, l_suppkey, l_quantity, l_shipdate) as t1
on t1.l_orderkey = orders.o_orderkey
group by
t1.l_shipdate, t1.col1, col3
"""
def inner_mv_stmt_10 = """select t1.sum_total, max_total+min_total as col3, count_all
from orders
inner join (select l_orderkey, sum(l_quantity) as sum_total,
max(l_quantity) as max_total,
min(l_quantity) as min_total,
count(*) as count_all,
sum(l_quantity) + count(*) as col5,
bitmap_union(to_bitmap(case when l_quantity > 1 and l_orderkey IN (1, 3) then l_partkey else null end)) as cnt_1,
bitmap_union(to_bitmap(case when l_quantity > 2 and l_orderkey IN (2) then l_partkey else null end)) as cnt_2
from lineitem where l_orderkey > 1 + 1 group by l_orderkey) as t1
on t1.l_orderkey = orders.o_orderkey
group by
t1.sum_total, col3, count_all
"""
// right join + agg (function + group by + +-*/ + filter)
def right_mv_stmt_1 = """select t1.o_orderdate, t1.o_shippriority, t1.o_orderkey
from (select o_orderkey, o_custkey, o_orderstatus, o_orderdate, o_shippriority from orders group by o_orderkey, o_custkey, o_orderstatus, o_orderdate, o_shippriority) as t1
right join lineitem on lineitem.l_orderkey = t1.o_orderkey
group by
t1.o_orderdate, t1.o_shippriority, t1.o_orderkey"""
def right_mv_stmt_2 = """select t1.o_orderdate, t1.o_orderkey, t1.col1
from (select o_orderkey, o_custkey, o_orderstatus, o_orderdate, sum(o_shippriority) as col1 from orders group by o_orderkey, o_custkey, o_orderstatus, o_orderdate) as t1
right join lineitem on lineitem.l_orderkey = t1.o_orderkey
group by
t1.o_orderdate,t1.o_orderkey, t1.col1
"""
def right_mv_stmt_3 = """select t1.o_orderdate, t1.o_orderkey, t1.col1
from (select o_orderkey, o_custkey, o_orderstatus, o_orderdate, sum(o_shippriority) as col1 from orders where o_orderkey > 1 + 1 group by o_orderkey, o_custkey, o_orderstatus, o_orderdate) as t1
right join lineitem on lineitem.l_orderkey = t1.o_orderkey
group by
t1.o_orderdate,t1.o_orderkey, t1.col1
"""
def right_mv_stmt_4 = """select t1.o_orderdate, (t1.o_orderkey+t1.col2) as col3, t1.col1
from (select o_orderkey, o_custkey, o_orderstatus, o_orderdate, sum(o_shippriority) as col1, count(*) as col2 from orders where o_orderkey >= 1 + 1 group by o_orderkey, o_custkey, o_orderstatus, o_orderdate) as t1
right join lineitem on lineitem.l_orderkey = t1.o_orderkey
group by
t1.o_orderdate,col3, t1.col1
"""
def right_mv_stmt_5 = """select t1.sum_total, max_total+min_total as col3, count_all
from (select o_orderkey, sum(o_totalprice) as sum_total,
max(o_totalprice) as max_total,
min(o_totalprice) as min_total,
count(*) as count_all,
sum(o_totalprice) + count(*) as col5,
bitmap_union(to_bitmap(case when o_shippriority > 1 and o_orderkey IN (1, 3) then o_custkey else null end)) as cnt_1,
bitmap_union(to_bitmap(case when o_shippriority > 2 and o_orderkey IN (2) then o_custkey else null end)) as cnt_2
from orders where o_orderkey >= 1 + 1 group by o_orderkey) as t1
right join lineitem on lineitem.l_orderkey = t1.o_orderkey
group by t1.sum_total, col3, count_all
"""
def right_mv_stmt_6 = """select t1.l_shipdate, t1.l_quantity, t1.l_orderkey
from orders
right join (select l_orderkey, l_partkey, l_suppkey, l_quantity, l_shipdate from lineitem group by l_orderkey, l_partkey, l_suppkey, l_quantity, l_shipdate) as t1
on t1.l_orderkey = orders.o_orderkey
group by
t1.l_shipdate, t1.l_quantity, t1.l_orderkey """
def right_mv_stmt_7 = """select t1.l_shipdate, t1.col1, t1.l_orderkey
from orders
right join (select l_orderkey, l_partkey, l_suppkey, l_quantity, l_shipdate, sum(l_quantity) as col1 from lineitem group by l_orderkey, l_partkey, l_suppkey, l_quantity, l_shipdate) as t1
on t1.l_orderkey = orders.o_orderkey
group by
t1.l_shipdate, t1.col1, t1.l_orderkey
"""
def right_mv_stmt_8 = """select t1.l_shipdate, t1.col1, t1.l_orderkey
from orders
right join (select l_orderkey, l_partkey, l_suppkey, l_quantity, l_shipdate, sum(l_quantity) as col1 from lineitem where l_orderkey > 1 + 1 group by l_orderkey, l_partkey, l_suppkey, l_quantity, l_shipdate) as t1
on t1.l_orderkey = orders.o_orderkey
group by
t1.l_shipdate, t1.col1, t1.l_orderkey
"""
def right_mv_stmt_9 = """select t1.l_shipdate, t1.col1, t1.l_orderkey + t1.col2 as col3
from orders
right join (select l_orderkey, l_partkey, l_suppkey, l_quantity, l_shipdate, sum(l_quantity) as col1, count(*) as col2 from lineitem where l_orderkey > 1 + 1 group by l_orderkey, l_partkey, l_suppkey, l_quantity, l_shipdate) as t1
on t1.l_orderkey = orders.o_orderkey
group by
t1.l_shipdate, t1.col1, col3
"""
def right_mv_stmt_10 = """select t1.sum_total, max_total+min_total as col3, count_all
from orders
right join (select l_orderkey, sum(l_quantity) as sum_total,
max(l_quantity) as max_total,
min(l_quantity) as min_total,
count(*) as count_all,
sum(l_quantity) + count(*) as col5,
bitmap_union(to_bitmap(case when l_quantity > 1 and l_orderkey IN (1, 3) then l_partkey else null end)) as cnt_1,
bitmap_union(to_bitmap(case when l_quantity > 2 and l_orderkey IN (2) then l_partkey else null end)) as cnt_2
from lineitem where l_orderkey > 1 + 1 group by l_orderkey) as t1
on t1.l_orderkey = orders.o_orderkey
group by
t1.sum_total, col3, count_all
"""
// Todo: full/cross/semi/anti join + agg (function + group by + +-*/ + filter)
def full_mv_stmt_1 = """select t1.o_orderdate, t1.o_shippriority, t1.o_orderkey
from (select o_orderkey, o_custkey, o_orderstatus, o_orderdate, o_shippriority from orders group by o_orderkey, o_custkey, o_orderstatus, o_orderdate, o_shippriority) as t1
full join lineitem on lineitem.l_orderkey = t1.o_orderkey
group by
t1.o_orderdate, t1.o_shippriority, t1.o_orderkey"""
def full_mv_stmt_2 = """select t1.o_orderdate, t1.o_orderkey, t1.col1
from (select o_orderkey, o_custkey, o_orderstatus, o_orderdate, sum(o_shippriority) as col1 from orders group by o_orderkey, o_custkey, o_orderstatus, o_orderdate) as t1
full join lineitem on lineitem.l_orderkey = t1.o_orderkey
group by
t1.o_orderdate,t1.o_orderkey, t1.col1
"""
def full_mv_stmt_3 = """select t1.o_orderdate, t1.o_orderkey, t1.col1
from (select o_orderkey, o_custkey, o_orderstatus, o_orderdate, sum(o_shippriority) as col1 from orders where o_orderkey > 1 + 1 group by o_orderkey, o_custkey, o_orderstatus, o_orderdate) as t1
full join lineitem on lineitem.l_orderkey = t1.o_orderkey
group by
t1.o_orderdate,t1.o_orderkey, t1.col1
"""
def full_mv_stmt_4 = """select t1.o_orderdate, (t1.o_orderkey+t1.col2) as col3, t1.col1
from (select o_orderkey, o_custkey, o_orderstatus, o_orderdate, sum(o_shippriority) as col1, count(*) as col2 from orders where o_orderkey >= 1 + 1 group by o_orderkey, o_custkey, o_orderstatus, o_orderdate) as t1
full join lineitem on lineitem.l_orderkey = t1.o_orderkey
group by
t1.o_orderdate,col3, t1.col1
"""
def full_mv_stmt_5 = """select t1.sum_total, max_total+min_total as col3, count_all
from (select o_orderkey, sum(o_totalprice) as sum_total,
max(o_totalprice) as max_total,
min(o_totalprice) as min_total,
count(*) as count_all,
sum(o_totalprice) + count(*) as col5,
bitmap_union(to_bitmap(case when o_shippriority > 1 and o_orderkey IN (1, 3) then o_custkey else null end)) as cnt_1,
bitmap_union(to_bitmap(case when o_shippriority > 2 and o_orderkey IN (2) then o_custkey else null end)) as cnt_2
from orders where o_orderkey >= 1 + 1 group by o_orderkey) as t1
full join lineitem on lineitem.l_orderkey = t1.o_orderkey
group by t1.sum_total, col3, count_all
"""
def full_mv_stmt_6 = """select t1.l_shipdate, t1.l_quantity, t1.l_orderkey
from orders
full join (select l_orderkey, l_partkey, l_suppkey, l_quantity, l_shipdate from lineitem group by l_orderkey, l_partkey, l_suppkey, l_quantity, l_shipdate) as t1
on t1.l_orderkey = orders.o_orderkey
group by
t1.l_shipdate, t1.l_quantity, t1.l_orderkey """
def full_mv_stmt_7 = """select t1.l_shipdate, t1.col1, t1.l_orderkey
from orders
full join (select l_orderkey, l_partkey, l_suppkey, l_quantity, l_shipdate, sum(l_quantity) as col1 from lineitem group by l_orderkey, l_partkey, l_suppkey, l_quantity, l_shipdate) as t1
on t1.l_orderkey = orders.o_orderkey
group by
t1.l_shipdate, t1.col1, t1.l_orderkey
"""
def full_mv_stmt_8 = """select t1.l_shipdate, t1.col1, t1.l_orderkey
from orders
full join (select l_orderkey, l_partkey, l_suppkey, l_quantity, l_shipdate, sum(l_quantity) as col1 from lineitem where l_orderkey > 1 + 1 group by l_orderkey, l_partkey, l_suppkey, l_quantity, l_shipdate) as t1
on t1.l_orderkey = orders.o_orderkey
group by
t1.l_shipdate, t1.col1, t1.l_orderkey
"""
def full_mv_stmt_9 = """select t1.l_shipdate, t1.col1, t1.l_orderkey + t1.col2 as col3
from orders
full join (select l_orderkey, l_partkey, l_suppkey, l_quantity, l_shipdate, sum(l_quantity) as col1, count(*) as col2 from lineitem where l_orderkey > 1 + 1 group by l_orderkey, l_partkey, l_suppkey, l_quantity, l_shipdate) as t1
on t1.l_orderkey = orders.o_orderkey
group by
t1.l_shipdate, t1.col1, col3
"""
def full_mv_stmt_10 = """select t1.sum_total, max_total+min_total as col3, count_all
from orders
full join (select l_orderkey, sum(l_quantity) as sum_total,
max(l_quantity) as max_total,
min(l_quantity) as min_total,
count(*) as count_all,
sum(l_quantity) + count(*) as col5,
bitmap_union(to_bitmap(case when l_quantity > 1 and l_orderkey IN (1, 3) then l_partkey else null end)) as cnt_1,
bitmap_union(to_bitmap(case when l_quantity > 2 and l_orderkey IN (2) then l_partkey else null end)) as cnt_2
from lineitem where l_orderkey > 1 + 1 group by l_orderkey) as t1
on t1.l_orderkey = orders.o_orderkey
group by
t1.sum_total, col3, count_all
"""
def cross_mv_stmt_1 = """select t1.o_orderdate, t1.o_shippriority, t1.o_orderkey
from (select o_orderkey, o_custkey, o_orderstatus, o_orderdate, o_shippriority from orders group by o_orderkey, o_custkey, o_orderstatus, o_orderdate, o_shippriority) as t1
cross join lineitem
group by
t1.o_orderdate, t1.o_shippriority, t1.o_orderkey"""
def cross_mv_stmt_2 = """select t1.o_orderdate, t1.o_orderkey, t1.col1
from (select o_orderkey, o_custkey, o_orderstatus, o_orderdate, sum(o_shippriority) as col1 from orders group by o_orderkey, o_custkey, o_orderstatus, o_orderdate) as t1
cross join lineitem
group by
t1.o_orderdate,t1.o_orderkey, t1.col1
"""
def cross_mv_stmt_3 = """select t1.o_orderdate, t1.o_orderkey, t1.col1
from (select o_orderkey, o_custkey, o_orderstatus, o_orderdate, sum(o_shippriority) as col1 from orders where o_orderkey > 1 + 1 group by o_orderkey, o_custkey, o_orderstatus, o_orderdate) as t1
cross join lineitem
group by
t1.o_orderdate,t1.o_orderkey, t1.col1
"""
def cross_mv_stmt_4 = """select t1.o_orderdate, (t1.o_orderkey+t1.col2) as col3, t1.col1
from (select o_orderkey, o_custkey, o_orderstatus, o_orderdate, sum(o_shippriority) as col1, count(*) as col2 from orders where o_orderkey >= 1 + 1 group by o_orderkey, o_custkey, o_orderstatus, o_orderdate) as t1
cross join lineitem
group by
t1.o_orderdate,col3, t1.col1
"""
def cross_mv_stmt_5 = """select t1.sum_total, max_total+min_total as col3, count_all
from (select o_orderkey, sum(o_totalprice) as sum_total,
max(o_totalprice) as max_total,
min(o_totalprice) as min_total,
count(*) as count_all,
sum(o_totalprice) + count(*) as col5,
bitmap_union(to_bitmap(case when o_shippriority > 1 and o_orderkey IN (1, 3) then o_custkey else null end)) as cnt_1,
bitmap_union(to_bitmap(case when o_shippriority > 2 and o_orderkey IN (2) then o_custkey else null end)) as cnt_2
from orders where o_orderkey >= 1 + 1 group by o_orderkey) as t1
cross join lineitem
group by t1.sum_total, col3, count_all
"""
def cross_mv_stmt_6 = """select t1.l_shipdate, t1.l_quantity, t1.l_orderkey
from orders
cross join (select l_orderkey, l_partkey, l_suppkey, l_quantity, l_shipdate from lineitem group by l_orderkey, l_partkey, l_suppkey, l_quantity, l_shipdate) as t1
group by
t1.l_shipdate, t1.l_quantity, t1.l_orderkey """
def cross_mv_stmt_7 = """select t1.l_shipdate, t1.col1, t1.l_orderkey
from orders
cross join (select l_orderkey, l_partkey, l_suppkey, l_quantity, l_shipdate, sum(l_quantity) as col1 from lineitem group by l_orderkey, l_partkey, l_suppkey, l_quantity, l_shipdate) as t1
group by
t1.l_shipdate, t1.col1, t1.l_orderkey
"""
def cross_mv_stmt_8 = """select t1.l_shipdate, t1.col1, t1.l_orderkey
from orders
cross join (select l_orderkey, l_partkey, l_suppkey, l_quantity, l_shipdate, sum(l_quantity) as col1 from lineitem where l_orderkey > 1 + 1 group by l_orderkey, l_partkey, l_suppkey, l_quantity, l_shipdate) as t1
group by
t1.l_shipdate, t1.col1, t1.l_orderkey
"""
def cross_mv_stmt_9 = """select t1.l_shipdate, t1.col1, t1.l_orderkey + t1.col2 as col3
from orders
cross join (select l_orderkey, l_partkey, l_suppkey, l_quantity, l_shipdate, sum(l_quantity) as col1, count(*) as col2 from lineitem where l_orderkey > 1 + 1 group by l_orderkey, l_partkey, l_suppkey, l_quantity, l_shipdate) as t1
group by
t1.l_shipdate, t1.col1, col3
"""
def cross_mv_stmt_10 = """select t1.sum_total, max_total+min_total as col3, count_all
from orders
cross join (select l_orderkey, sum(l_quantity) as sum_total,
max(l_quantity) as max_total,
min(l_quantity) as min_total,
count(*) as count_all,
sum(l_quantity) + count(*) as col5,
bitmap_union(to_bitmap(case when l_quantity > 1 and l_orderkey IN (1, 3) then l_partkey else null end)) as cnt_1,
bitmap_union(to_bitmap(case when l_quantity > 2 and l_orderkey IN (2) then l_partkey else null end)) as cnt_2
from lineitem where l_orderkey > 1 + 1 group by l_orderkey) as t1
group by
t1.sum_total, col3, count_all
"""
def left_semi_mv_stmt_1 = """select t1.o_orderdate, t1.o_shippriority, t1.o_orderkey
from (select o_orderkey, o_custkey, o_orderstatus, o_orderdate, o_shippriority from orders group by o_orderkey, o_custkey, o_orderstatus, o_orderdate, o_shippriority) as t1
left semi join lineitem on lineitem.l_orderkey = t1.o_orderkey
group by
t1.o_orderdate, t1.o_shippriority, t1.o_orderkey"""
def left_semi_mv_stmt_2 = """select t1.o_orderdate, t1.o_orderkey, t1.col1
from (select o_orderkey, o_custkey, o_orderstatus, o_orderdate, sum(o_shippriority) as col1 from orders group by o_orderkey, o_custkey, o_orderstatus, o_orderdate) as t1
left semi join lineitem on lineitem.l_orderkey = t1.o_orderkey
group by
t1.o_orderdate,t1.o_orderkey, t1.col1
"""
def left_semi_mv_stmt_3 = """select t1.o_orderdate, t1.o_orderkey, t1.col1
from (select o_orderkey, o_custkey, o_orderstatus, o_orderdate, sum(o_shippriority) as col1 from orders where o_orderkey > 1 + 1 group by o_orderkey, o_custkey, o_orderstatus, o_orderdate) as t1
left semi join lineitem on lineitem.l_orderkey = t1.o_orderkey
group by
t1.o_orderdate,t1.o_orderkey, t1.col1
"""
def left_semi_mv_stmt_4 = """select t1.o_orderdate, (t1.o_orderkey+t1.col2) as col3, t1.col1
from (select o_orderkey, o_custkey, o_orderstatus, o_orderdate, sum(o_shippriority) as col1, count(*) as col2 from orders where o_orderkey >= 1 + 1 group by o_orderkey, o_custkey, o_orderstatus, o_orderdate) as t1
left semi join lineitem on lineitem.l_orderkey = t1.o_orderkey
group by
t1.o_orderdate,col3, t1.col1
"""
def left_semi_mv_stmt_5 = """select t1.sum_total, max_total+min_total as col3, count_all
from (select o_orderkey, sum(o_totalprice) as sum_total,
max(o_totalprice) as max_total,
min(o_totalprice) as min_total,
count(*) as count_all,
sum(o_totalprice) + count(*) as col5,
bitmap_union(to_bitmap(case when o_shippriority > 1 and o_orderkey IN (1, 3) then o_custkey else null end)) as cnt_1,
bitmap_union(to_bitmap(case when o_shippriority > 2 and o_orderkey IN (2) then o_custkey else null end)) as cnt_2
from orders where o_orderkey >= 1 + 1 group by o_orderkey) as t1
left semi join lineitem on lineitem.l_orderkey = t1.o_orderkey
group by t1.sum_total, col3, count_all
"""
def left_semi_mv_stmt_6 = """select orders.o_orderdate, orders.o_shippriority, orders.o_orderkey
from orders
left semi join (select l_orderkey, l_partkey, l_suppkey, l_quantity, l_shipdate from lineitem group by l_orderkey, l_partkey, l_suppkey, l_quantity, l_shipdate) as t1
on t1.l_orderkey = orders.o_orderkey
group by
orders.o_orderdate, orders.o_shippriority, orders.o_orderkey """
def left_semi_mv_stmt_7 = """select orders.o_orderdate, orders.o_shippriority, orders.o_orderkey
from orders
left semi join (select l_orderkey, l_partkey, l_suppkey, l_quantity, l_shipdate, sum(l_quantity) as col1 from lineitem group by l_orderkey, l_partkey, l_suppkey, l_quantity, l_shipdate) as t1
on t1.l_orderkey = orders.o_orderkey
group by
orders.o_orderdate, orders.o_shippriority, orders.o_orderkey
"""
def left_semi_mv_stmt_8 = """select orders.o_orderdate, orders.o_shippriority, orders.o_orderkey
from orders
left semi join (select l_orderkey, l_partkey, l_suppkey, l_quantity, l_shipdate, sum(l_quantity) as col1 from lineitem where l_orderkey > 1 + 1 group by l_orderkey, l_partkey, l_suppkey, l_quantity, l_shipdate) as t1
on t1.l_orderkey = orders.o_orderkey
group by
orders.o_orderdate, orders.o_shippriority, orders.o_orderkey
"""
def left_semi_mv_stmt_9 = """select orders.o_orderdate, orders.o_shippriority, orders.o_orderkey
from orders
left semi join (select l_orderkey, l_partkey, l_suppkey, l_quantity, l_shipdate, sum(l_quantity) as col1, count(*) as col2 from lineitem where l_orderkey > 1 + 1 group by l_orderkey, l_partkey, l_suppkey, l_quantity, l_shipdate) as t1
on t1.l_orderkey = orders.o_orderkey
group by
orders.o_orderdate, orders.o_shippriority, orders.o_orderkey
"""
def left_semi_mv_stmt_10 = """select orders.o_orderdate, orders.o_shippriority, orders.o_orderkey
from orders
left semi join (select l_orderkey, sum(l_quantity) as sum_total,
max(l_quantity) as max_total,
min(l_quantity) as min_total,
count(*) as count_all,
sum(l_quantity) + count(*) as col5,
bitmap_union(to_bitmap(case when l_quantity > 1 and l_orderkey IN (1, 3) then l_partkey else null end)) as cnt_1,
bitmap_union(to_bitmap(case when l_quantity > 2 and l_orderkey IN (2) then l_partkey else null end)) as cnt_2
from lineitem where l_orderkey > 1 + 1 group by l_orderkey) as t1
on t1.l_orderkey = orders.o_orderkey
group by
orders.o_orderdate, orders.o_shippriority, orders.o_orderkey
"""
def right_semi_mv_stmt_1 = """select l_orderkey, l_shipdate, l_partkey
from (select o_orderkey, o_custkey, o_orderstatus, o_orderdate, o_shippriority from orders group by o_orderkey, o_custkey, o_orderstatus, o_orderdate, o_shippriority) as t1
right semi join lineitem on lineitem.l_orderkey = t1.o_orderkey
group by
l_orderkey, l_shipdate, l_partkey """
def right_semi_mv_stmt_2 = """select l_orderkey, l_shipdate, l_partkey
from (select o_orderkey, o_custkey, o_orderstatus, o_orderdate, sum(o_shippriority) as col1 from orders group by o_orderkey, o_custkey, o_orderstatus, o_orderdate) as t1
right semi join lineitem on lineitem.l_orderkey = t1.o_orderkey
group by
l_orderkey, l_shipdate, l_partkey
"""
def right_semi_mv_stmt_3 = """select l_orderkey, l_shipdate, l_partkey
from (select o_orderkey, o_custkey, o_orderstatus, o_orderdate, sum(o_shippriority) as col1 from orders where o_orderkey > 1 + 1 group by o_orderkey, o_custkey, o_orderstatus, o_orderdate) as t1
right semi join lineitem on lineitem.l_orderkey = t1.o_orderkey
group by
l_orderkey, l_shipdate, l_partkey
"""
def right_semi_mv_stmt_4 = """select l_orderkey, l_shipdate, l_partkey
from (select o_orderkey, o_custkey, o_orderstatus, o_orderdate, sum(o_shippriority) as col1, count(*) as col2 from orders where o_orderkey >= 1 + 1 group by o_orderkey, o_custkey, o_orderstatus, o_orderdate) as t1
right semi join lineitem on lineitem.l_orderkey = t1.o_orderkey
group by
l_orderkey, l_shipdate, l_partkey
"""
def right_semi_mv_stmt_5 = """select l_orderkey, l_shipdate, l_partkey
from (select o_orderkey, sum(o_totalprice) as sum_total,
max(o_totalprice) as max_total,
min(o_totalprice) as min_total,
count(*) as count_all,
sum(o_totalprice) + count(*) as col5,
bitmap_union(to_bitmap(case when o_shippriority > 1 and o_orderkey IN (1, 3) then o_custkey else null end)) as cnt_1,
bitmap_union(to_bitmap(case when o_shippriority > 2 and o_orderkey IN (2) then o_custkey else null end)) as cnt_2
from orders where o_orderkey >= 1 + 1 group by o_orderkey) as t1
right semi join lineitem on lineitem.l_orderkey = t1.o_orderkey
group by l_orderkey, l_shipdate, l_partkey
"""
def right_semi_mv_stmt_6 = """select t1.l_orderkey, t1.l_shipdate, t1.l_partkey
from orders
right semi join (select l_orderkey, l_partkey, l_suppkey, l_quantity, l_shipdate from lineitem group by l_orderkey, l_partkey, l_suppkey, l_quantity, l_shipdate) as t1
on t1.l_orderkey = orders.o_orderkey
group by
t1.l_orderkey, t1.l_shipdate, t1.l_partkey """
def right_semi_mv_stmt_7 = """select t1.l_orderkey, t1.l_shipdate, t1.l_partkey
from orders
right semi join (select l_orderkey, l_partkey, l_suppkey, l_quantity, l_shipdate, sum(l_quantity) as col1 from lineitem group by l_orderkey, l_partkey, l_suppkey, l_quantity, l_shipdate) as t1
on t1.l_orderkey = orders.o_orderkey
group by
t1.l_orderkey, t1.l_shipdate, t1.l_partkey
"""
def right_semi_mv_stmt_8 = """select t1.l_orderkey, t1.l_shipdate, t1.l_partkey
from orders
right semi join (select l_orderkey, l_partkey, l_suppkey, l_quantity, l_shipdate, sum(l_quantity) as col1 from lineitem where l_shipdate >= "2023-10-17" group by l_orderkey, l_partkey, l_suppkey, l_quantity, l_shipdate) as t1
on t1.l_orderkey = orders.o_orderkey
group by
t1.l_orderkey, t1.l_shipdate, t1.l_partkey
"""
def right_semi_mv_stmt_9 = """select t1.l_orderkey, t1.l_shipdate, t1.l_partkey
from orders
right semi join (select l_orderkey, l_partkey, l_suppkey, l_quantity, l_shipdate, sum(l_quantity) as col1, count(*) as col2 from lineitem where l_shipdate >= "2023-10-17" group by l_orderkey, l_partkey, l_suppkey, l_quantity, l_shipdate) as t1
on t1.l_orderkey = orders.o_orderkey
group by
t1.l_orderkey, t1.l_shipdate, t1.l_partkey
"""
def right_semi_mv_stmt_10 = """select t1.sum_total, max_total+min_total as col3, count_all
from orders
right semi join (select l_orderkey, sum(l_quantity) as sum_total,
max(l_quantity) as max_total,
min(l_quantity) as min_total,
count(*) as count_all,
sum(l_quantity) + count(*) as col5,
bitmap_union(to_bitmap(case when l_quantity > 1 and l_orderkey IN (1, 3) then l_partkey else null end)) as cnt_1,
bitmap_union(to_bitmap(case when l_quantity > 2 and l_orderkey IN (2) then l_partkey else null end)) as cnt_2
from lineitem where l_shipdate >= "2023-10-17" group by l_orderkey) as t1
on t1.l_orderkey = orders.o_orderkey
group by
t1.sum_total, col3, count_all
"""
def left_anti_mv_stmt_1 = """select t1.o_orderdate, t1.o_shippriority, t1.o_orderkey
from (select o_orderkey, o_custkey, o_orderstatus, o_orderdate, o_shippriority from orders group by o_orderkey, o_custkey, o_orderstatus, o_orderdate, o_shippriority) as t1
left anti join lineitem on lineitem.l_orderkey = t1.o_orderkey
group by
t1.o_orderdate, t1.o_shippriority, t1.o_orderkey"""
def left_anti_mv_stmt_2 = """select t1.o_orderdate, t1.o_orderkey, t1.col1
from (select o_orderkey, o_custkey, o_orderstatus, o_orderdate, sum(o_shippriority) as col1 from orders group by o_orderkey, o_custkey, o_orderstatus, o_orderdate) as t1
left anti join lineitem on lineitem.l_orderkey = t1.o_orderkey
group by
t1.o_orderdate,t1.o_orderkey, t1.col1
"""
def left_anti_mv_stmt_3 = """select t1.o_orderdate, t1.o_orderkey, t1.col1
from (select o_orderkey, o_custkey, o_orderstatus, o_orderdate, sum(o_shippriority) as col1 from orders where o_orderkey > 1 + 1 group by o_orderkey, o_custkey, o_orderstatus, o_orderdate) as t1
left anti join lineitem on lineitem.l_orderkey = t1.o_orderkey
group by
t1.o_orderdate,t1.o_orderkey, t1.col1
"""
def left_anti_mv_stmt_4 = """select t1.o_orderdate, (t1.o_orderkey+t1.col2) as col3, t1.col1
from (select o_orderkey, o_custkey, o_orderstatus, o_orderdate, sum(o_shippriority) as col1, count(*) as col2 from orders where o_orderkey >= 1 + 1 group by o_orderkey, o_custkey, o_orderstatus, o_orderdate) as t1
left anti join lineitem on lineitem.l_orderkey = t1.o_orderkey
group by
t1.o_orderdate,col3, t1.col1
"""
def left_anti_mv_stmt_5 = """select t1.sum_total, max_total+min_total as col3, count_all
from (select o_orderkey, sum(o_totalprice) as sum_total,
max(o_totalprice) as max_total,
min(o_totalprice) as min_total,
count(*) as count_all,
sum(o_totalprice) + count(*) as col5,
bitmap_union(to_bitmap(case when o_shippriority > 1 and o_orderkey IN (1, 3) then o_custkey else null end)) as cnt_1,
bitmap_union(to_bitmap(case when o_shippriority > 2 and o_orderkey IN (2) then o_custkey else null end)) as cnt_2
from orders where o_orderkey >= 1 + 1 group by o_orderkey) as t1
left anti join lineitem on lineitem.l_orderkey = t1.o_orderkey
group by t1.sum_total, col3, count_all
"""
def left_anti_mv_stmt_6 = """select orders.o_orderdate, orders.o_shippriority, orders.o_orderkey
from orders
left anti join (select l_orderkey, l_partkey, l_suppkey, l_quantity, l_shipdate from lineitem group by l_orderkey, l_partkey, l_suppkey, l_quantity, l_shipdate) as t1
on t1.l_orderkey = orders.o_orderkey
group by
orders.o_orderdate, orders.o_shippriority, orders.o_orderkey"""
def left_anti_mv_stmt_7 = """select orders.o_orderdate, orders.o_shippriority, orders.o_orderkey
from orders
left anti join (select l_orderkey, l_partkey, l_suppkey, l_quantity, l_shipdate, sum(l_quantity) as col1 from lineitem group by l_orderkey, l_partkey, l_suppkey, l_quantity, l_shipdate) as t1
on t1.l_orderkey = orders.o_orderkey
group by
orders.o_orderdate, orders.o_shippriority, orders.o_orderkey
"""
def left_anti_mv_stmt_8 = """select orders.o_orderdate, orders.o_shippriority, orders.o_orderkey
from orders
left anti join (select l_orderkey, l_partkey, l_suppkey, l_quantity, l_shipdate, sum(l_quantity) as col1 from lineitem where l_orderkey > 1 + 1 group by l_orderkey, l_partkey, l_suppkey, l_quantity, l_shipdate) as t1
on t1.l_orderkey = orders.o_orderkey
group by
orders.o_orderdate, orders.o_shippriority, orders.o_orderkey
"""
def left_anti_mv_stmt_9 = """select orders.o_orderdate, orders.o_shippriority, orders.o_orderkey
from orders
left anti join (select l_orderkey, l_partkey, l_suppkey, l_quantity, l_shipdate, sum(l_quantity) as col1, count(*) as col2 from lineitem where l_orderkey > 1 + 1 group by l_orderkey, l_partkey, l_suppkey, l_quantity, l_shipdate) as t1
on t1.l_orderkey = orders.o_orderkey
group by
orders.o_orderdate, orders.o_shippriority, orders.o_orderkey
"""
def left_anti_mv_stmt_10 = """select orders.o_orderdate, orders.o_shippriority, orders.o_orderkey
from orders
left anti join (select l_orderkey, sum(l_quantity) as sum_total,
max(l_quantity) as max_total,
min(l_quantity) as min_total,
count(*) as count_all,
sum(l_quantity) + count(*) as col5,
bitmap_union(to_bitmap(case when l_quantity > 1 and l_orderkey IN (1, 3) then l_partkey else null end)) as cnt_1,
bitmap_union(to_bitmap(case when l_quantity > 2 and l_orderkey IN (2) then l_partkey else null end)) as cnt_2
from lineitem where l_orderkey > 1 + 1 group by l_orderkey) as t1
on t1.l_orderkey = orders.o_orderkey
group by
orders.o_orderdate, orders.o_shippriority, orders.o_orderkey
"""
def right_anti_mv_stmt_1 = """select l_orderkey, l_shipdate, l_partkey
from (select o_orderkey, o_custkey, o_orderstatus, o_orderdate, o_shippriority from orders group by o_orderkey, o_custkey, o_orderstatus, o_orderdate, o_shippriority) as t1
right anti join lineitem on lineitem.l_orderkey = t1.o_orderkey
group by
l_orderkey, l_shipdate, l_partkey"""
def right_anti_mv_stmt_2 = """select l_orderkey, l_shipdate, l_partkey
from (select o_orderkey, o_custkey, o_orderstatus, o_orderdate, sum(o_shippriority) as col1 from orders group by o_orderkey, o_custkey, o_orderstatus, o_orderdate) as t1
right anti join lineitem on lineitem.l_orderkey = t1.o_orderkey
group by
l_orderkey, l_shipdate, l_partkey
"""
def right_anti_mv_stmt_3 = """select l_orderkey, l_shipdate, l_partkey
from (select o_orderkey, o_custkey, o_orderstatus, o_orderdate, sum(o_shippriority) as col1 from orders where o_orderkey > 1 + 1 group by o_orderkey, o_custkey, o_orderstatus, o_orderdate) as t1
right anti join lineitem on lineitem.l_orderkey = t1.o_orderkey
group by
l_orderkey, l_shipdate, l_partkey
"""
def right_anti_mv_stmt_4 = """select l_orderkey, l_shipdate, l_partkey
from (select o_orderkey, o_custkey, o_orderstatus, o_orderdate, sum(o_shippriority) as col1, count(*) as col2 from orders where o_orderkey >= 1 + 1 group by o_orderkey, o_custkey, o_orderstatus, o_orderdate) as t1
right anti join lineitem on lineitem.l_orderkey = t1.o_orderkey
group by
l_orderkey, l_shipdate, l_partkey
"""
def right_anti_mv_stmt_5 = """select l_orderkey, l_shipdate, l_partkey
from (select o_orderkey, sum(o_totalprice) as sum_total,
max(o_totalprice) as max_total,
min(o_totalprice) as min_total,
count(*) as count_all,
sum(o_totalprice) + count(*) as col5,
bitmap_union(to_bitmap(case when o_shippriority > 1 and o_orderkey IN (1, 3) then o_custkey else null end)) as cnt_1,
bitmap_union(to_bitmap(case when o_shippriority > 2 and o_orderkey IN (2) then o_custkey else null end)) as cnt_2
from orders where o_orderkey >= 1 + 1 group by o_orderkey) as t1
right anti join lineitem on lineitem.l_orderkey = t1.o_orderkey
group by l_orderkey, l_shipdate, l_partkey
"""
def right_anti_mv_stmt_6 = """select t1.l_shipdate, t1.l_quantity, t1.l_orderkey
from orders
right anti join (select l_orderkey, l_partkey, l_suppkey, l_quantity, l_shipdate from lineitem group by l_orderkey, l_partkey, l_suppkey, l_quantity, l_shipdate) as t1
on t1.l_orderkey = orders.o_orderkey
group by
t1.l_shipdate, t1.l_quantity, t1.l_orderkey """
def right_anti_mv_stmt_7 = """select t1.l_shipdate, t1.col1, t1.l_orderkey
from orders
right anti join (select l_orderkey, l_partkey, l_suppkey, l_quantity, l_shipdate, sum(l_quantity) as col1 from lineitem group by l_orderkey, l_partkey, l_suppkey, l_quantity, l_shipdate) as t1
on t1.l_orderkey = orders.o_orderkey
group by
t1.l_shipdate, t1.col1, t1.l_orderkey
"""
def right_anti_mv_stmt_8 = """select t1.l_shipdate, t1.col1, t1.l_orderkey
from orders
right anti join (select l_orderkey, l_partkey, l_suppkey, l_quantity, l_shipdate, sum(l_quantity) as col1 from lineitem where l_shipdate >= "2023-10-17" group by l_orderkey, l_partkey, l_suppkey, l_quantity, l_shipdate) as t1
on t1.l_orderkey = orders.o_orderkey
group by
t1.l_shipdate, t1.col1, t1.l_orderkey
"""
def right_anti_mv_stmt_9 = """select t1.l_shipdate, t1.col1, t1.l_orderkey + t1.col2 as col3
from orders
right anti join (select l_orderkey, l_partkey, l_suppkey, l_quantity, l_shipdate, sum(l_quantity) as col1, count(*) as col2 from lineitem where l_shipdate >= "2023-10-17" group by l_orderkey, l_partkey, l_suppkey, l_quantity, l_shipdate) as t1
on t1.l_orderkey = orders.o_orderkey
group by
t1.l_shipdate, t1.col1, col3
"""
def right_anti_mv_stmt_10 = """select t1.sum_total, max_total+min_total as col3, count_all
from orders
right anti join (select l_orderkey, sum(l_quantity) as sum_total,
max(l_quantity) as max_total,
min(l_quantity) as min_total,
count(*) as count_all,
sum(l_quantity) + count(*) as col5,
bitmap_union(to_bitmap(case when l_quantity > 1 and l_orderkey IN (1, 3) then l_partkey else null end)) as cnt_1,
bitmap_union(to_bitmap(case when l_quantity > 2 and l_orderkey IN (2) then l_partkey else null end)) as cnt_2
from lineitem where l_shipdate >= "2023-10-17" group by l_orderkey) as t1
on t1.l_orderkey = orders.o_orderkey
group by
t1.sum_total, col3, count_all
"""
def sql_list = [
left_mv_stmt_1,left_mv_stmt_2,left_mv_stmt_3,left_mv_stmt_4,left_mv_stmt_5,left_mv_stmt_6,left_mv_stmt_7,left_mv_stmt_8,left_mv_stmt_9,left_mv_stmt_10,
right_mv_stmt_1,right_mv_stmt_2,right_mv_stmt_3,right_mv_stmt_4,right_mv_stmt_5,right_mv_stmt_6,right_mv_stmt_7,right_mv_stmt_8,right_mv_stmt_9,right_mv_stmt_10,
inner_mv_stmt_1,inner_mv_stmt_2,inner_mv_stmt_3,inner_mv_stmt_4,inner_mv_stmt_5,inner_mv_stmt_6,inner_mv_stmt_7,inner_mv_stmt_8,inner_mv_stmt_9,inner_mv_stmt_10,
full_mv_stmt_1,full_mv_stmt_2,full_mv_stmt_3,full_mv_stmt_4,full_mv_stmt_5,full_mv_stmt_6,full_mv_stmt_7,full_mv_stmt_8,full_mv_stmt_9,full_mv_stmt_10,
// agg pulled up, and the struct info is invalid. need to support aggregate merge then support following query rewriting
// left_semi_mv_stmt_1,left_semi_mv_stmt_2,left_semi_mv_stmt_3,left_semi_mv_stmt_4,left_semi_mv_stmt_5,left_semi_mv_stmt_6,left_semi_mv_stmt_7,left_semi_mv_stmt_8,left_semi_mv_stmt_9,left_semi_mv_stmt_10,
// left_anti_mv_stmt_1,left_anti_mv_stmt_2,left_anti_mv_stmt_3,left_anti_mv_stmt_4,left_anti_mv_stmt_5,left_anti_mv_stmt_6,left_anti_mv_stmt_7,left_anti_mv_stmt_8,left_anti_mv_stmt_9,left_anti_mv_stmt_10,
// right_semi_mv_stmt_1,right_semi_mv_stmt_2,right_semi_mv_stmt_3,right_semi_mv_stmt_4,right_semi_mv_stmt_5,right_semi_mv_stmt_6,right_semi_mv_stmt_7,right_semi_mv_stmt_8,right_semi_mv_stmt_9,right_semi_mv_stmt_10,
// right_anti_mv_stmt_1,right_anti_mv_stmt_2,right_anti_mv_stmt_3,right_anti_mv_stmt_4,right_anti_mv_stmt_5,right_anti_mv_stmt_6,right_anti_mv_stmt_7,right_anti_mv_stmt_8,right_anti_mv_stmt_9,right_anti_mv_stmt_10
// query rewrite by materialized view doesn't support cross join currently
// cross_mv_stmt_1,cross_mv_stmt_2,cross_mv_stmt_3,cross_mv_stmt_4,cross_mv_stmt_5,cross_mv_stmt_6,cross_mv_stmt_7,cross_mv_stmt_8,cross_mv_stmt_9,cross_mv_stmt_10,
]
for (int i = 0; i < sql_list.size(); i++) {
def origin_res = sql sql_list[i]
assert (origin_res.size() > 0)
}