Changeset: 573f914b2f9e for MonetDB
URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=573f914b2f9e
Modified Files:
sql/test/BugTracker-2012/Tests/rewrite_like_into_likesubselect.Bug-3179.sql
sql/test/BugTracker-2012/Tests/rewrite_like_into_likesubselect.Bug-3179.stable.out
Branch: default
Log Message:
Merge with Feb2013 branch.
diffs (truncated from 913 to 300 lines):
diff --git
a/sql/test/BugTracker-2012/Tests/rewrite_like_into_likesubselect.Bug-3179.sql
b/sql/test/BugTracker-2012/Tests/rewrite_like_into_likesubselect.Bug-3179.sql
---
a/sql/test/BugTracker-2012/Tests/rewrite_like_into_likesubselect.Bug-3179.sql
+++
b/sql/test/BugTracker-2012/Tests/rewrite_like_into_likesubselect.Bug-3179.sql
@@ -1,3 +1,6 @@
+-- disable parallelism (mitosis & dataflow) to avoid ambiguous results
+set optimizer='sequential_pipe';
+
start transaction;
-- only explain output because not like's give to many rows.
diff --git
a/sql/test/BugTracker-2012/Tests/rewrite_like_into_likesubselect.Bug-3179.stable.out
b/sql/test/BugTracker-2012/Tests/rewrite_like_into_likesubselect.Bug-3179.stable.out
---
a/sql/test/BugTracker-2012/Tests/rewrite_like_into_likesubselect.Bug-3179.stable.out
+++
b/sql/test/BugTracker-2012/Tests/rewrite_like_into_likesubselect.Bug-3179.stable.out
@@ -29,365 +29,105 @@ Ready.
% .explain # table_name
% mal # name
% clob # type
-% 96 # length
+% 68 # length
function user.s0_1(A0:str):void;
- X_11 := nil:bat[:oid,:str];
- X_95 := nil:bat[:oid,:str];
- X_94 := nil:bat[:oid,:str];
- X_93 := nil:bat[:oid,:str];
- X_92 := nil:bat[:oid,:str];
-barrier X_105 := language.dataflow();
X_2 := sql.mvc();
- X_48:bat[:oid,:str] := sql.bind(X_2,"sys","functions","name",0,0,4);
- X_41:bat[:oid,:oid] := sql.tid(X_2,"sys","functions",0,4);
- X_76 := algebra.likesubselect(X_48,X_41,A0,"":str,false);
- (X_52:bat[:oid,:oid] ,X_53:bat[:oid,:str] ) :=
sql.bind(X_2,"sys","functions","name",2,0,4);
- X_80 := algebra.likesubselect(X_53,A0,"":str,false);
- X_84 := sql.subdelta(X_76,X_52,X_80);
- X_88 := sql.projectdelta(X_84,X_48,X_52,X_53);
- X_49:bat[:oid,:str] := sql.bind(X_2,"sys","functions","name",0,1,4);
- X_43:bat[:oid,:oid] := sql.tid(X_2,"sys","functions",1,4);
- X_77 := algebra.likesubselect(X_49,X_43,A0,"":str,false);
- (X_54:bat[:oid,:oid] ,X_55:bat[:oid,:str] ) :=
sql.bind(X_2,"sys","functions","name",2,1,4);
- X_81 := algebra.likesubselect(X_55,A0,"":str,false);
- X_85 := sql.subdelta(X_77,X_54,X_81);
- X_89 := sql.projectdelta(X_85,X_49,X_54,X_55);
- X_50:bat[:oid,:str] := sql.bind(X_2,"sys","functions","name",0,2,4);
- X_45:bat[:oid,:oid] := sql.tid(X_2,"sys","functions",2,4);
- X_78 := algebra.likesubselect(X_50,X_45,A0,"":str,false);
- (X_56:bat[:oid,:oid] ,X_57:bat[:oid,:str] ) :=
sql.bind(X_2,"sys","functions","name",2,2,4);
- X_82 := algebra.likesubselect(X_57,A0,"":str,false);
- X_86 := sql.subdelta(X_78,X_56,X_82);
- X_90 := sql.projectdelta(X_86,X_50,X_56,X_57);
- X_51:bat[:oid,:str] := sql.bind(X_2,"sys","functions","name",0,3,4);
- X_47:bat[:oid,:oid] := sql.tid(X_2,"sys","functions",3,4);
- X_79 := algebra.likesubselect(X_51,X_47,A0,"":str,false);
- (X_58:bat[:oid,:oid] ,X_59:bat[:oid,:str] ) :=
sql.bind(X_2,"sys","functions","name",2,3,4);
- X_83 := algebra.likesubselect(X_59,A0,"":str,false);
- X_8 := sql.bind(X_2,"sys","functions","name",1);
- X_38 := algebra.likesubselect(X_8,X_47,A0,"":str,false);
- X_87 := sql.subdelta(X_79,X_58,X_83,X_38);
- X_91 := sql.projectdelta(X_87,X_51,X_58,X_59,X_8);
- X_97 := mat.packIncrement(X_88,4);
- X_98 := mat.packIncrement(X_97,X_89);
- X_99 := mat.packIncrement(X_98,X_90);
- X_11 := mat.packIncrement(X_99,X_91);
- X_63:bat[:oid,:str] := sql.bind(X_2,"sys","functions","func",0,3,4);
- (X_70:bat[:oid,:oid] ,X_71:bat[:oid,:str] ) :=
sql.bind(X_2,"sys","functions","func",2,3,4);
- X_13 := sql.bind(X_2,"sys","functions","func",1);
- X_95 := sql.projectdelta(X_87,X_63,X_70,X_71,X_13);
- X_62:bat[:oid,:str] := sql.bind(X_2,"sys","functions","func",0,2,4);
- (X_68:bat[:oid,:oid] ,X_69:bat[:oid,:str] ) :=
sql.bind(X_2,"sys","functions","func",2,2,4);
- X_94 := sql.projectdelta(X_86,X_62,X_68,X_69);
- X_61:bat[:oid,:str] := sql.bind(X_2,"sys","functions","func",0,1,4);
- (X_66:bat[:oid,:oid] ,X_67:bat[:oid,:str] ) :=
sql.bind(X_2,"sys","functions","func",2,1,4);
- X_93 := sql.projectdelta(X_85,X_61,X_66,X_67);
- X_60:bat[:oid,:str] := sql.bind(X_2,"sys","functions","func",0,0,4);
- (X_64:bat[:oid,:oid] ,X_65:bat[:oid,:str] ) :=
sql.bind(X_2,"sys","functions","func",2,0,4);
- X_92 := sql.projectdelta(X_84,X_60,X_64,X_65);
- language.pass(X_48);
- language.pass(X_52);
- language.pass(X_53);
- language.pass(X_49);
- language.pass(X_54);
- language.pass(X_55);
- language.pass(X_50);
- language.pass(X_56);
- language.pass(X_57);
- language.pass(X_47);
- language.pass(X_51);
- language.pass(X_58);
- language.pass(X_59);
- language.pass(X_8);
- language.pass(X_87);
- language.pass(X_86);
- language.pass(X_85);
- language.pass(X_84);
-exit X_105;
- X_100:bat[:oid,:str] := nil:bat[:oid,:str];
- X_104:bat[:oid,:str] := nil:bat[:oid,:str];
- X_15 := sql.resultSet(2,1,X_11);
- sql.rsColumn(X_15,"sys.functions","name","varchar",256,0,X_11);
- X_101 := mat.packIncrement(X_92,4);
- X_102 := mat.packIncrement(X_101,X_93);
- X_103 := mat.packIncrement(X_102,X_94);
- X_14 := mat.packIncrement(X_103,X_95);
- sql.rsColumn(X_15,"sys.functions","func","varchar",8196,0,X_14);
- X_24 := io.stdout();
- sql.exportResult(X_24,X_15);
+ X_6 := sql.bind(X_2,"sys","functions","name",0);
+ X_3:bat[:oid,:oid] := sql.tid(X_2,"sys","functions");
+ X_44 := algebra.likesubselect(X_6,X_3,A0,"":str,false);
+ (X_9,r1_9) := sql.bind(X_2,"sys","functions","name",2);
+ X_45 := algebra.likesubselect(r1_9,A0,"":str,false);
+ X_12 := sql.bind(X_2,"sys","functions","name",1);
+ X_46 := algebra.likesubselect(X_12,X_3,A0,"":str,false);
+ X_14 := sql.subdelta(X_44,X_9,X_45,X_46);
+ X_16 := sql.projectdelta(X_14,X_6,X_9,r1_9,X_12);
+ X_17 := sql.bind(X_2,"sys","functions","func",0);
+ (X_19,r1_28) := sql.bind(X_2,"sys","functions","func",2);
+ X_21 := sql.bind(X_2,"sys","functions","func",1);
+ X_22 := sql.projectdelta(X_14,X_17,X_19,r1_28,X_21);
+ X_23 := sql.resultSet(2,1,X_16);
+ sql.rsColumn(X_23,"sys.functions","name","varchar",256,0,X_16);
+ sql.rsColumn(X_23,"sys.functions","func","varchar",8196,0,X_22);
+ X_32 := io.stdout();
+ sql.exportResult(X_32,X_23);
end s0_1;
#explain select name,func from functions where name not like '%optimizers%';
% .explain # table_name
% mal # name
% clob # type
-% 96 # length
+% 68 # length
function user.s1_1(A0:str):void;
- X_11 := nil:bat[:oid,:str];
- X_95 := nil:bat[:oid,:str];
- X_94 := nil:bat[:oid,:str];
- X_93 := nil:bat[:oid,:str];
- X_92 := nil:bat[:oid,:str];
-barrier X_105 := language.dataflow();
X_2 := sql.mvc();
- X_48:bat[:oid,:str] := sql.bind(X_2,"sys","functions","name",0,0,4);
- X_41:bat[:oid,:oid] := sql.tid(X_2,"sys","functions",0,4);
- X_76 := algebra.likesubselect(X_48,X_41,A0,"":str,true);
- (X_52:bat[:oid,:oid] ,X_53:bat[:oid,:str] ) :=
sql.bind(X_2,"sys","functions","name",2,0,4);
- X_80 := algebra.likesubselect(X_53,A0,"":str,true);
- X_84 := sql.subdelta(X_76,X_52,X_80);
- X_88 := sql.projectdelta(X_84,X_48,X_52,X_53);
- X_49:bat[:oid,:str] := sql.bind(X_2,"sys","functions","name",0,1,4);
- X_43:bat[:oid,:oid] := sql.tid(X_2,"sys","functions",1,4);
- X_77 := algebra.likesubselect(X_49,X_43,A0,"":str,true);
- (X_54:bat[:oid,:oid] ,X_55:bat[:oid,:str] ) :=
sql.bind(X_2,"sys","functions","name",2,1,4);
- X_81 := algebra.likesubselect(X_55,A0,"":str,true);
- X_85 := sql.subdelta(X_77,X_54,X_81);
- X_89 := sql.projectdelta(X_85,X_49,X_54,X_55);
- X_50:bat[:oid,:str] := sql.bind(X_2,"sys","functions","name",0,2,4);
- X_45:bat[:oid,:oid] := sql.tid(X_2,"sys","functions",2,4);
- X_78 := algebra.likesubselect(X_50,X_45,A0,"":str,true);
- (X_56:bat[:oid,:oid] ,X_57:bat[:oid,:str] ) :=
sql.bind(X_2,"sys","functions","name",2,2,4);
- X_82 := algebra.likesubselect(X_57,A0,"":str,true);
- X_86 := sql.subdelta(X_78,X_56,X_82);
- X_90 := sql.projectdelta(X_86,X_50,X_56,X_57);
- X_51:bat[:oid,:str] := sql.bind(X_2,"sys","functions","name",0,3,4);
- X_47:bat[:oid,:oid] := sql.tid(X_2,"sys","functions",3,4);
- X_79 := algebra.likesubselect(X_51,X_47,A0,"":str,true);
- (X_58:bat[:oid,:oid] ,X_59:bat[:oid,:str] ) :=
sql.bind(X_2,"sys","functions","name",2,3,4);
- X_83 := algebra.likesubselect(X_59,A0,"":str,true);
- X_8 := sql.bind(X_2,"sys","functions","name",1);
- X_38 := algebra.likesubselect(X_8,X_47,A0,"":str,true);
- X_87 := sql.subdelta(X_79,X_58,X_83,X_38);
- X_91 := sql.projectdelta(X_87,X_51,X_58,X_59,X_8);
- X_97 := mat.packIncrement(X_88,4);
- X_98 := mat.packIncrement(X_97,X_89);
- X_99 := mat.packIncrement(X_98,X_90);
- X_11 := mat.packIncrement(X_99,X_91);
- X_63:bat[:oid,:str] := sql.bind(X_2,"sys","functions","func",0,3,4);
- (X_70:bat[:oid,:oid] ,X_71:bat[:oid,:str] ) :=
sql.bind(X_2,"sys","functions","func",2,3,4);
- X_13 := sql.bind(X_2,"sys","functions","func",1);
- X_95 := sql.projectdelta(X_87,X_63,X_70,X_71,X_13);
- X_62:bat[:oid,:str] := sql.bind(X_2,"sys","functions","func",0,2,4);
- (X_68:bat[:oid,:oid] ,X_69:bat[:oid,:str] ) :=
sql.bind(X_2,"sys","functions","func",2,2,4);
- X_94 := sql.projectdelta(X_86,X_62,X_68,X_69);
- X_61:bat[:oid,:str] := sql.bind(X_2,"sys","functions","func",0,1,4);
- (X_66:bat[:oid,:oid] ,X_67:bat[:oid,:str] ) :=
sql.bind(X_2,"sys","functions","func",2,1,4);
- X_93 := sql.projectdelta(X_85,X_61,X_66,X_67);
- X_60:bat[:oid,:str] := sql.bind(X_2,"sys","functions","func",0,0,4);
- (X_64:bat[:oid,:oid] ,X_65:bat[:oid,:str] ) :=
sql.bind(X_2,"sys","functions","func",2,0,4);
- X_92 := sql.projectdelta(X_84,X_60,X_64,X_65);
- language.pass(X_48);
- language.pass(X_52);
- language.pass(X_53);
- language.pass(X_49);
- language.pass(X_54);
- language.pass(X_55);
- language.pass(X_50);
- language.pass(X_56);
- language.pass(X_57);
- language.pass(X_47);
- language.pass(X_51);
- language.pass(X_58);
- language.pass(X_59);
- language.pass(X_8);
- language.pass(X_87);
- language.pass(X_86);
- language.pass(X_85);
- language.pass(X_84);
-exit X_105;
- X_100:bat[:oid,:str] := nil:bat[:oid,:str];
- X_104:bat[:oid,:str] := nil:bat[:oid,:str];
- X_15 := sql.resultSet(2,1,X_11);
- sql.rsColumn(X_15,"sys.functions","name","varchar",256,0,X_11);
- X_101 := mat.packIncrement(X_92,4);
- X_102 := mat.packIncrement(X_101,X_93);
- X_103 := mat.packIncrement(X_102,X_94);
- X_14 := mat.packIncrement(X_103,X_95);
- sql.rsColumn(X_15,"sys.functions","func","varchar",8196,0,X_14);
- X_24 := io.stdout();
- sql.exportResult(X_24,X_15);
+ X_6 := sql.bind(X_2,"sys","functions","name",0);
+ X_3:bat[:oid,:oid] := sql.tid(X_2,"sys","functions");
+ X_44 := algebra.likesubselect(X_6,X_3,A0,"":str,true);
+ (X_9,r1_9) := sql.bind(X_2,"sys","functions","name",2);
+ X_45 := algebra.likesubselect(r1_9,A0,"":str,true);
+ X_12 := sql.bind(X_2,"sys","functions","name",1);
+ X_46 := algebra.likesubselect(X_12,X_3,A0,"":str,true);
+ X_14 := sql.subdelta(X_44,X_9,X_45,X_46);
+ X_16 := sql.projectdelta(X_14,X_6,X_9,r1_9,X_12);
+ X_17 := sql.bind(X_2,"sys","functions","func",0);
+ (X_19,r1_28) := sql.bind(X_2,"sys","functions","func",2);
+ X_21 := sql.bind(X_2,"sys","functions","func",1);
+ X_22 := sql.projectdelta(X_14,X_17,X_19,r1_28,X_21);
+ X_23 := sql.resultSet(2,1,X_16);
+ sql.rsColumn(X_23,"sys.functions","name","varchar",256,0,X_16);
+ sql.rsColumn(X_23,"sys.functions","func","varchar",8196,0,X_22);
+ X_32 := io.stdout();
+ sql.exportResult(X_32,X_23);
end s1_1;
#explain select name,func from functions where name ilike '%optimizers%';
% .explain # table_name
% mal # name
% clob # type
-% 96 # length
+% 68 # length
function user.s2_1(A0:str):void;
- X_11 := nil:bat[:oid,:str];
- X_95 := nil:bat[:oid,:str];
- X_94 := nil:bat[:oid,:str];
- X_93 := nil:bat[:oid,:str];
- X_92 := nil:bat[:oid,:str];
-barrier X_105 := language.dataflow();
X_2 := sql.mvc();
- X_48:bat[:oid,:str] := sql.bind(X_2,"sys","functions","name",0,0,4);
- X_41:bat[:oid,:oid] := sql.tid(X_2,"sys","functions",0,4);
- X_76 := algebra.ilikesubselect(X_48,X_41,A0,"":str,false);
- (X_52:bat[:oid,:oid] ,X_53:bat[:oid,:str] ) :=
sql.bind(X_2,"sys","functions","name",2,0,4);
- X_80 := algebra.ilikesubselect(X_53,A0,"":str,false);
- X_84 := sql.subdelta(X_76,X_52,X_80);
- X_88 := sql.projectdelta(X_84,X_48,X_52,X_53);
- X_49:bat[:oid,:str] := sql.bind(X_2,"sys","functions","name",0,1,4);
- X_43:bat[:oid,:oid] := sql.tid(X_2,"sys","functions",1,4);
- X_77 := algebra.ilikesubselect(X_49,X_43,A0,"":str,false);
- (X_54:bat[:oid,:oid] ,X_55:bat[:oid,:str] ) :=
sql.bind(X_2,"sys","functions","name",2,1,4);
- X_81 := algebra.ilikesubselect(X_55,A0,"":str,false);
- X_85 := sql.subdelta(X_77,X_54,X_81);
- X_89 := sql.projectdelta(X_85,X_49,X_54,X_55);
- X_50:bat[:oid,:str] := sql.bind(X_2,"sys","functions","name",0,2,4);
- X_45:bat[:oid,:oid] := sql.tid(X_2,"sys","functions",2,4);
- X_78 := algebra.ilikesubselect(X_50,X_45,A0,"":str,false);
- (X_56:bat[:oid,:oid] ,X_57:bat[:oid,:str] ) :=
sql.bind(X_2,"sys","functions","name",2,2,4);
- X_82 := algebra.ilikesubselect(X_57,A0,"":str,false);
- X_86 := sql.subdelta(X_78,X_56,X_82);
- X_90 := sql.projectdelta(X_86,X_50,X_56,X_57);
- X_51:bat[:oid,:str] := sql.bind(X_2,"sys","functions","name",0,3,4);
- X_47:bat[:oid,:oid] := sql.tid(X_2,"sys","functions",3,4);
- X_79 := algebra.ilikesubselect(X_51,X_47,A0,"":str,false);
- (X_58:bat[:oid,:oid] ,X_59:bat[:oid,:str] ) :=
sql.bind(X_2,"sys","functions","name",2,3,4);
- X_83 := algebra.ilikesubselect(X_59,A0,"":str,false);
- X_8 := sql.bind(X_2,"sys","functions","name",1);
- X_38 := algebra.ilikesubselect(X_8,X_47,A0,"":str,false);
- X_87 := sql.subdelta(X_79,X_58,X_83,X_38);
- X_91 := sql.projectdelta(X_87,X_51,X_58,X_59,X_8);
- X_97 := mat.packIncrement(X_88,4);
- X_98 := mat.packIncrement(X_97,X_89);
- X_99 := mat.packIncrement(X_98,X_90);
- X_11 := mat.packIncrement(X_99,X_91);
- X_63:bat[:oid,:str] := sql.bind(X_2,"sys","functions","func",0,3,4);
- (X_70:bat[:oid,:oid] ,X_71:bat[:oid,:str] ) :=
sql.bind(X_2,"sys","functions","func",2,3,4);
- X_13 := sql.bind(X_2,"sys","functions","func",1);
- X_95 := sql.projectdelta(X_87,X_63,X_70,X_71,X_13);
- X_62:bat[:oid,:str] := sql.bind(X_2,"sys","functions","func",0,2,4);
- (X_68:bat[:oid,:oid] ,X_69:bat[:oid,:str] ) :=
sql.bind(X_2,"sys","functions","func",2,2,4);
- X_94 := sql.projectdelta(X_86,X_62,X_68,X_69);
- X_61:bat[:oid,:str] := sql.bind(X_2,"sys","functions","func",0,1,4);
- (X_66:bat[:oid,:oid] ,X_67:bat[:oid,:str] ) :=
sql.bind(X_2,"sys","functions","func",2,1,4);
- X_93 := sql.projectdelta(X_85,X_61,X_66,X_67);
- X_60:bat[:oid,:str] := sql.bind(X_2,"sys","functions","func",0,0,4);
- (X_64:bat[:oid,:oid] ,X_65:bat[:oid,:str] ) :=
sql.bind(X_2,"sys","functions","func",2,0,4);
- X_92 := sql.projectdelta(X_84,X_60,X_64,X_65);
- language.pass(X_48);
- language.pass(X_52);
- language.pass(X_53);
- language.pass(X_49);
- language.pass(X_54);
- language.pass(X_55);
_______________________________________________
checkin-list mailing list
[email protected]
http://mail.monetdb.org/mailman/listinfo/checkin-list
