Description
The following query results in listifies in the optimized query plan.
USE tpch;
SELECT l_returnflag AS l_returnflag,
l_linestatus AS l_linestatus,
coll_count(cheap) AS count_cheaps,
coll_count(expensive) AS count_expensives
FROM LineItem AS l
WHERE l.l_shipdate <= '1998-09-02'
/* +hash */
GROUP BY l.l_returnflag AS l_returnflag,l.l_linestatus AS l_linestatus
GROUP AS g
LET cheap = (
SELECT ELEMENT m
FROM (FROM g SELECT VALUE l) AS m
WHERE m.l_discount > 0.05
),
expensive = (
SELECT ELEMENT a
FROM (FROM g SELECT VALUE l) AS a
WHERE a.l_discount <= 0.05
)
ORDER BY l_returnflag,l_linestatus
;