START TRANSACTION; auto commit mode: off create view revenue1 (supplier_no, total_revenue) as select l_suppkey, sum(l_extendedprice * (1 - l_discount)) from lineitem where l_shipdate >= date '1997-08-01' and l_shipdate < date '1997-08-01' + interval '90' day group by l_suppkey; operation successful EXPLAIN select s_suppkey, s_name, s_address, s_phone, total_revenue from supplier, revenue1 where s_suppkey = supplier_no and total_revenue = ( select max(total_revenue) from revenue1 ) order by s_suppkey; +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | mal | +=========================================================================================================================================================================================================================================================================================================================================+ | function user.s2_1():void; | | X_168:void := querylog.define("explain\n\n\nselect\n\ts_suppkey,\n\ts_name,\n\ts_address,\n\ts_phone,\n\ttotal_revenue\nfrom\n\tsupplier,\n\trevenue1\nwhere\n\ts_suppkey = supplier_no\n\tand total_revenue = (\n\t\tselect\n\t\t\tmax(total_revenue)\n\t\tfrom\n\t\t\trevenue1\n\t)\norder by\n\ts_suppkey;","default_pipe",114); | | barrier X_205 := language.dataflow(); | | X_117 := bat.new(nil:oid,nil:str); | | X_125 := bat.append(X_117,"sys.supplier"); | | X_135 := bat.append(X_125,"sys.supplier"); | | X_143 := bat.append(X_135,"sys.supplier"); | | X_151 := bat.append(X_143,"sys.supplier"); | | X_158 := bat.append(X_151,"sys.revenue1"); | | X_120 := bat.new(nil:oid,nil:str); | | X_127 := bat.append(X_120,"s_suppkey"); | | X_136 := bat.append(X_127,"s_name"); | | X_144 := bat.append(X_136,"s_address"); | | X_152 := bat.append(X_144,"s_phone"); | | X_160 := bat.append(X_152,"total_revenue"); | | X_121 := bat.new(nil:oid,nil:str); | | X_129 := bat.append(X_121,"int"); | | X_138 := bat.append(X_129,"char"); | | X_146 := bat.append(X_138,"varchar"); | | X_154 := bat.append(X_146,"char"); | | X_162 := bat.append(X_154,"decimal"); | | X_122 := bat.new(nil:oid,nil:int); | | X_131 := bat.append(X_122,32); | | X_140 := bat.append(X_131,25); | | X_148 := bat.append(X_140,40); | | X_155 := bat.append(X_148,15); | | X_164 := bat.append(X_155,39); | | X_124 := bat.new(nil:oid,nil:int); | | X_133 := bat.append(X_124,0); | | X_142 := bat.append(X_133,0); | | X_150 := bat.append(X_142,0); | | X_157 := bat.append(X_150,0); | | X_166 := bat.append(X_157,4); | | X_1 := sql.mvc(); | | X_2:bat[:oid,:oid] := sql.tid(X_1,"sys","supplier"); | | X_5:bat[:oid,:int] := sql.bind(X_1,"sys","supplier","s_suppkey",0); | | (X_8,r1_8) := sql.bind(X_1,"sys","supplier","s_suppkey",2); | | X_11:bat[:oid,:int] := sql.bind(X_1,"sys","supplier","s_suppkey",1); | | X_13 := sql.delta(X_5,X_8,r1_8,X_11); | | X_14 := algebra.leftfetchjoin(X_2,X_13); | | X_15:bat[:oid,:oid] := sql.tid(X_1,"sys","lineitem"); | | X_17:bat[:oid,:date] := sql.bind(X_1,"sys","lineitem","l_shipdate",0); | | (X_19,r1_19) := sql.bind(X_1,"sys","lineitem","l_shipdate",2); | | X_21:bat[:oid,:date] := sql.bind(X_1,"sys","lineitem","l_shipdate",1); | | X_22 := sql.delta(X_17,X_19,r1_19,X_21); | | X_23 := algebra.leftfetchjoin(X_15,X_22); | | X_26:date := mtime.date_add_msec_interval("1997-08-01",7776000000); | | X_27 := algebra.subselect(X_23,"1997-08-01",X_26,true,false,false); | | X_30:bat[:oid,:int] := sql.bind(X_1,"sys","lineitem","l_suppkey",0); | | (X_32,r1_35) := sql.bind(X_1,"sys","lineitem","l_suppkey",2); | | X_35:bat[:oid,:int] := sql.bind(X_1,"sys","lineitem","l_suppkey",1); | | X_36 := sql.delta(X_30,X_32,r1_35,X_35); | | X_37 := algebra.leftfetchjoin(X_15,X_36); | | X_38 := algebra.leftfetchjoin(X_27,X_37); | | (X_39,r1_42,r2_42) := group.subgroupdone(X_38); | | X_42 := algebra.leftfetchjoin(r1_42,X_38); | | X_49:bat[:oid,:lng] := sql.bind(X_1,"sys","lineitem","l_extendedprice",0); | | (X_53,r1_56) := sql.bind(X_1,"sys","lineitem","l_extendedprice",2); | | X_56:bat[:oid,:lng] := sql.bind(X_1,"sys","lineitem","l_extendedprice",1); | | X_58 := sql.delta(X_49,X_53,r1_56,X_56); | | X_59 := algebra.leftfetchjoin(X_15,X_58); | | X_60 := algebra.leftfetchjoin(X_27,X_59); | | X_61:bat[:oid,:lng] := sql.bind(X_1,"sys","lineitem","l_discount",0); | | (X_64,r1_75) := sql.bind(X_1,"sys","lineitem","l_discount",2); | | X_66:bat[:oid,:lng] := sql.bind(X_1,"sys","lineitem","l_discount",1); | | X_67 := sql.delta(X_61,X_64,r1_75,X_66); | | X_68 := algebra.leftfetchjoin(X_15,X_67); | | X_69 := algebra.leftfetchjoin(X_27,X_68); | | X_70:bat[:oid,:lng] := batcalc.-(100:lng,X_69); | | X_71:bat[:oid,:hge] := batcalc.*(X_60,X_70); | | X_72:bat[:oid,:hge] := aggr.subsum(X_71,X_39,r1_42,true,true); | | (X_43,r1_46) := algebra.subjoin(X_14,X_42,nil:BAT,nil:BAT,false,nil:lng); | | X_47 := algebra.leftfetchjoin(X_43,X_14); | | X_48 := bat.mirror(X_47); | | X_74 := algebra.leftfetchjoin(r1_46,X_72); | | X_76 := aggr.max(X_72); | | X_77 := sql.single(X_76); | | (X_78,r1_157) := algebra.subjoin(X_74,X_77,nil:BAT,nil:BAT,false,nil:lng); | | X_83 := algebra.tinter(X_48,X_78); | | X_84 := algebra.leftfetchjoin(X_83,X_47); | | (X_85,r1_164,r2_164) := algebra.subsort(X_84,false,false); | | X_88 := algebra.leftfetchjoin(r1_164,X_84); | | X_89:bat[:oid,:str] := sql.bind(X_1,"sys","supplier","s_name",0); | | (X_93,r1_172) := sql.bind(X_1,"sys","supplier","s_name",2); | | X_96:bat[:oid,:str] := sql.bind(X_1,"sys","supplier","s_name",1); | | X_99 := sql.delta(X_89,X_93,r1_172,X_96); | | X_100:bat[:oid,:str] := algebra.leftfetchjoinPath(r1_164,X_83,X_43,X_2,X_99); | | X_101:bat[:oid,:str] := sql.bind(X_1,"sys","supplier","s_address",0); | | (X_103,r1_185) := sql.bind(X_1,"sys","supplier","s_address",2); | | X_105:bat[:oid,:str] := sql.bind(X_1,"sys","supplier","s_address",1); | | X_106 := sql.delta(X_101,X_103,r1_185,X_105); | | X_107:bat[:oid,:str] := algebra.leftfetchjoinPath(r1_164,X_83,X_43,X_2,X_106); | | X_108:bat[:oid,:str] := sql.bind(X_1,"sys","supplier","s_phone",0); | | (X_110,r1_195) := sql.bind(X_1,"sys","supplier","s_phone",2); | | X_112:bat[:oid,:str] := sql.bind(X_1,"sys","supplier","s_phone",1); | | X_113 := sql.delta(X_108,X_110,r1_195,X_112); | | X_114:bat[:oid,:str] := algebra.leftfetchjoinPath(r1_164,X_83,X_43,X_2,X_113); | | X_115:bat[:oid,:hge] := algebra.leftfetchjoinPath(r1_164,X_83,X_74); | | language.pass(X_38); | | language.pass(X_15); | | language.pass(X_27); | | language.pass(r1_42); | | language.pass(X_14); | | language.pass(X_72); | | language.pass(X_47); | | language.pass(X_84); | | language.pass(X_43); | | language.pass(X_2); | | language.pass(r1_164); | | language.pass(X_83); | | language.pass(X_74); | | exit X_205; | | sql.resultSet(X_158,X_160,X_162,X_164,X_166,X_88,X_100,X_107,X_114,X_115); | | end user.s2_1; | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 114 tuples drop view revenue1; operation successful COMMIT; auto commit mode: on