START TRANSACTION;
auto commit mode: off
EXPLAIN


select
	l_shipmode,
	sum(case
		when o_orderpriority = '1-URGENT'
			or o_orderpriority = '2-HIGH'
			then 1
		else 0
	end) as high_line_count,
	sum(case
		when o_orderpriority <> '1-URGENT'
			and o_orderpriority <> '2-HIGH'
			then 1
		else 0
	end) as low_line_count
from
	orders,
	lineitem
where
	o_orderkey = l_orderkey
	and l_shipmode in ('REG AIR', 'MAIL')
	and l_commitdate < l_receiptdate
	and l_shipdate < l_commitdate
	and l_receiptdate >= date '1994-01-01'
	and l_receiptdate < date '1994-01-01' + interval '1' year
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| mal                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
+===================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================+
| function user.s1_1(A0:str,A1:str,A2:bte,A3:bte,A4:str,A5:str,A6:bte,A7:bte,A8:str,A9:str,A10:date,A11:date,A12:int):void;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
|     X_156:void := querylog.define("explain\n\n\nselect\n\tl_shipmode,\n\tsum(case\n\t\twhen o_orderpriority = \\'1-URGENT\\'\n\t\t\tor o_orderpriority = \\'2-HIGH\\'\n\t\t\tthen 1\n\t\telse 0\n\tend) as high_line_count,\n\tsum(case\n\t\twhen o_orderpriority <> \\'1-URGENT\\'\n\t\t\tand o_orderpriority <> \\'2-HIGH\\'\n\t\t\tthen 1\n\t\telse 0\n\tend) as low_line_count\nfrom\n\torders,\n\tlineitem\nwhere\n\to_orderkey = l_orderkey\n\tand l_shipmode in (\\'REG AIR\\', \\'MAIL\\')\n\tand l_commitdate < l_receiptdate\n\tand l_shipdate < l_commitdate\n\tand l_receiptdate >= date \\'1994-01-01\\'\n\tand l_receiptdate < date \\'1994-01-01\\' + interval \\'1\\' year\ngroup by\n\tl_shipmode\norder by\n\tl_shipmode;","default_pipe",122); |
| barrier X_211 := language.dataflow();                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
|     X_122 := bat.new(nil:oid,nil:str);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
|     X_130 := bat.append(X_122,"sys.lineitem");                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
|     X_140 := bat.append(X_130,"sys.L1");                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
|     X_149 := bat.append(X_140,"sys.L2");                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
|     X_125 := bat.new(nil:oid,nil:str);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
|     X_132 := bat.append(X_125,"l_shipmode");                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
|     X_142 := bat.append(X_132,"high_line_count");                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
|     X_151 := bat.append(X_142,"low_line_count");                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
|     X_126 := bat.new(nil:oid,nil:str);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
|     X_134 := bat.append(X_126,"char");                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
|     X_144 := bat.append(X_134,"hugeint");                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
|     X_153 := bat.append(X_144,"hugeint");                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
|     X_127 := bat.new(nil:oid,nil:int);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
|     X_136 := bat.append(X_127,10);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
|     X_146 := bat.append(X_136,128);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
|     X_154 := bat.append(X_146,128);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
|     X_129 := bat.new(nil:oid,nil:int);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
|     X_138 := bat.append(X_129,0);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
|     X_148 := bat.append(X_138,0);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
|     X_155 := bat.append(X_148,0);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
|     X_14 := sql.mvc();                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
|     X_15:bat[:oid,:oid] := sql.tid(X_14,"sys","orders");                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
|     X_18:bat[:oid,:int] := sql.bind(X_14,"sys","orders","o_orderkey",0);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
|     (X_21,r1_21) := sql.bind(X_14,"sys","orders","o_orderkey",2);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
|     X_24:bat[:oid,:int] := sql.bind(X_14,"sys","orders","o_orderkey",1);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
|     X_26 := sql.projectdelta(X_15,X_18,X_21,r1_21,X_24);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
|     X_29:bat[:oid,:str] := sql.bind(X_14,"sys","lineitem","l_shipmode",0);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
|     X_34:bat[:oid,:date] := sql.bind(X_14,"sys","lineitem","l_receiptdate",0);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
|     X_42:bat[:oid,:date] := sql.bind(X_14,"sys","lineitem","l_commitdate",0);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
|     (X_45,r1_52) := sql.bind(X_14,"sys","lineitem","l_commitdate",2);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
|     X_47:bat[:oid,:date] := sql.bind(X_14,"sys","lineitem","l_commitdate",1);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
|     X_48 := sql.delta(X_42,X_45,r1_52,X_47);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
|     X_49 := X_48;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
|     X_50:bat[:oid,:date] := sql.bind(X_14,"sys","lineitem","l_shipdate",0);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
|     (X_53,r1_60) := sql.bind(X_14,"sys","lineitem","l_shipdate",2);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
|     X_57:bat[:oid,:date] := sql.bind(X_14,"sys","lineitem","l_shipdate",1);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
|     X_59 := sql.delta(X_50,X_53,r1_60,X_57);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
|     X_60 := X_59;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
|     X_65:bat[:oid,:bit] := batcalc.>(X_49,X_60);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
|     (X_36,r1_40) := sql.bind(X_14,"sys","lineitem","l_receiptdate",2);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
|     X_38:bat[:oid,:date] := sql.bind(X_14,"sys","lineitem","l_receiptdate",1);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
|     X_39 := sql.delta(X_34,X_36,r1_40,X_38);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
|     X_40 := X_39;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
|     X_61:bat[:oid,:bit] := batcalc.<(X_49,X_40);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
|     X_27:bat[:oid,:oid] := sql.tid(X_14,"sys","lineitem");                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
|     X_62 := algebra.subselect(X_61,X_27,true,true,true,true,false);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
|     X_66 := algebra.subselect(X_65,X_62,true,true,true,true,false);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
|     X_41:date := mtime.addmonths(A11,A12);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
|     X_178 := algebra.subselect(X_34,X_66,A10,X_41,true,false,false);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
|     X_179 := algebra.subselect(r1_40,nil:bat[:oid,:oid],A10,X_41,true,false,false);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
|     X_181 := algebra.subselect(X_38,X_66,A10,X_41,true,false,false);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
|     X_67 := sql.subdelta(X_178,X_66,X_36,X_179,X_181);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
|     X_182 := algebra.subselect(X_29,X_67,A8,A8,true,false,false);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
|     (X_31,r1_32) := sql.bind(X_14,"sys","lineitem","l_shipmode",2);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
|     X_183 := algebra.subselect(r1_32,nil:bat[:oid,:oid],A8,A8,true,false,false);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
|     X_33:bat[:oid,:str] := sql.bind(X_14,"sys","lineitem","l_shipmode",1);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
|     X_185 := algebra.subselect(X_33,X_67,A8,A8,true,false,false);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
|     X_68 := sql.subdelta(X_182,X_67,X_31,X_183,X_185);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
|     X_186 := algebra.subselect(X_29,X_67,A9,A9,true,false,false);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
|     X_187 := algebra.subselect(r1_32,nil:bat[:oid,:oid],A9,A9,true,false,false);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
|     X_189 := algebra.subselect(X_33,X_67,A9,A9,true,false,false);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
|     X_69 := sql.subdelta(X_186,X_67,X_31,X_187,X_189);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
|     X_70 := bat.mergecand(X_68,X_69);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
|     X_71:bat[:oid,:int] := sql.bind(X_14,"sys","lineitem","l_orderkey",0);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
|     (X_74,r1_85) := sql.bind(X_14,"sys","lineitem","l_orderkey",2);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
|     X_76:bat[:oid,:int] := sql.bind(X_14,"sys","lineitem","l_orderkey",1);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
|     X_77 := sql.projectdelta(X_70,X_71,X_74,r1_85,X_76);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
|     (X_78,r1_91) := algebra.subjoin(X_26,X_77,nil:BAT,nil:BAT,false,nil:lng);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
|     X_82 := sql.projectdelta(X_70,X_29,X_31,r1_32,X_33);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
|     X_83 := algebra.leftfetchjoin(r1_91,X_82);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
|     (X_84,r1_97,r2_97) := group.subgroupdone(X_83);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
|     X_87 := algebra.leftfetchjoin(r1_97,X_83);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
|     X_92:bat[:oid,:str] := sql.bind(X_14,"sys","orders","o_orderpriority",0);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
|     (X_96,r1_109) := sql.bind(X_14,"sys","orders","o_orderpriority",2);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
|     X_99:bat[:oid,:str] := sql.bind(X_14,"sys","orders","o_orderpriority",1);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
|     X_101 := sql.projectdelta(X_15,X_92,X_96,r1_109,X_99);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
|     X_102 := algebra.leftfetchjoin(X_78,X_101);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
|     X_103:bat[:oid,:bit] := batcalc.==(X_102,A0);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
|     X_104:bat[:oid,:bit] := batcalc.==(X_102,A1);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
|     X_105:bat[:oid,:bit] := batcalc.or(X_103,X_104);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
|     X_106:bat[:oid,:bit] := batcalc.isnil(X_105);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
|     X_108:bat[:oid,:bit] := batcalc.ifthenelse(X_106,false,X_105);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
|     X_109:bat[:oid,:bte] := batcalc.ifthenelse(X_108,A2,A3);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
|     X_110:bat[:oid,:hge] := aggr.subsum(X_109,X_84,r1_97,true,true);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
|     X_113:bat[:oid,:bit] := batcalc.!=(X_102,A4);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
|     X_114:bat[:oid,:bit] := batcalc.!=(X_102,A5);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
|     X_115:bat[:oid,:bit] := batcalc.and(X_113,X_114);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
|     X_116:bat[:oid,:bit] := batcalc.isnil(X_115);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
|     X_117:bat[:oid,:bit] := batcalc.ifthenelse(X_116,false,X_115);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
|     X_118:bat[:oid,:bte] := batcalc.ifthenelse(X_117,A6,A7);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
|     X_119:bat[:oid,:hge] := aggr.subsum(X_118,X_84,r1_97,true,true);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
|     (X_88,r1_101,r2_101) := algebra.subsort(X_87,false,false);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
|     X_91 := algebra.leftfetchjoin(r1_101,X_87);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
|     X_112 := algebra.leftfetchjoin(r1_101,X_110);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
|     X_120 := algebra.leftfetchjoin(r1_101,X_119);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
|     language.pass(X_49);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
|     language.pass(X_34);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
|     language.pass(r1_40);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
|     language.pass(X_38);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
|     language.pass(X_66);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
|     language.pass(X_36);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
|     language.pass(X_67);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
|     language.pass(X_70);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
|     language.pass(X_29);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
|     language.pass(X_31);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
|     language.pass(r1_32);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
|     language.pass(X_33);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
|     language.pass(X_83);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
|     language.pass(X_15);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
|     language.pass(X_105);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
|     language.pass(X_102);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
|     language.pass(X_115);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
|     language.pass(X_84);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
|     language.pass(r1_97);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
|     language.pass(X_87);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
|     language.pass(r1_101);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
| exit X_211;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
|     sql.resultSet(X_149,X_151,X_153,X_154,X_155,X_91,X_112,X_120);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
| end user.s1_1;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
122 tuples
COMMIT;
auto commit mode: on