Akiban Server

Merge lp:~mmcm/akiban-server/sql-nested-map-folder into lp:~akiban-technologies/akiban-server/trunk

  1. sql-nested-map-folder
  2. Merge into trunk
Proposed by Mike McMahon
Status: Merged
Approved by: Thomas Jones-Low
Approved revision: 2728
Merged at revision: 2726
Proposed branch: lp:~mmcm/akiban-server/sql-nested-map-folder
Merge into: lp:~akiban-technologies/akiban-server/trunk
Diff against target: 597 lines (+314/-24)
31 files modified
src/main/java/com/akiban/sql/optimizer/rule/MapFolder.java (+33/-12)
src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/README.txt (+18/-0)
src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/nested-inner-inner.expected (+18/-0)
src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/nested-inner-inner.sql (+1/-0)
src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/nested-inner-left.expected (+19/-0)
src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/nested-inner-left.sql (+1/-0)
src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/nested-inner-right.expected (+20/-0)
src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/nested-inner-right.sql (+1/-0)
src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/nested-left-inner.expected (+20/-0)
src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/nested-left-inner.sql (+1/-0)
src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/nested-left-left.expected (+21/-0)
src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/nested-left-left.sql (+1/-0)
src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/nested-left-right.expected (+20/-0)
src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/nested-left-right.sql (+1/-0)
src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/nested-right-inner.expected (+19/-0)
src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/nested-right-inner.sql (+1/-0)
src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/nested-right-left.expected (+19/-0)
src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/nested-right-left.sql (+1/-0)
src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/nested-right-right.expected (+21/-0)
src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/nested-right-right.sql (+1/-0)
src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/schema.ddl (+4/-0)
src/test/resources/com/akiban/sql/optimizer/rule/reverse-ins/README.txt (+6/-0)
src/test/resources/com/akiban/sql/optimizer/rule/reverse-ins/map-join-project-nested.expected (+4/-4)
src/test/resources/com/akiban/sql/optimizer/rule/reverse-ins/map-join-project.expected (+3/-3)
src/test/resources/com/akiban/sql/optimizer/rule/reverse-ins/nested-in-not.expected (+14/-0)
src/test/resources/com/akiban/sql/optimizer/rule/reverse-ins/nested-in-not.sql (+3/-0)
src/test/resources/com/akiban/sql/optimizer/rule/reverse-ins/nested-in.expected (+18/-0)
src/test/resources/com/akiban/sql/optimizer/rule/reverse-ins/nested-in.sql (+3/-0)
src/test/resources/com/akiban/sql/optimizer/rule/reverse-ins/nested-not-in.expected (+14/-0)
src/test/resources/com/akiban/sql/optimizer/rule/reverse-ins/nested-not-in.sql (+3/-0)
src/test/resources/com/akiban/sql/optimizer/rule/reverse-ins/not-in-inner.expected (+5/-5)
To merge this branch: bzr merge lp:~mmcm/akiban-server/sql-nested-map-folder
Reviewer Review Type Date Requested Status
Thomas Jones-Low Approve
Review via email: mp+177664@code.launchpad.net

Description of the change

Correctly place operators added to implement non-group outer- and semi- joins.

These (NullIfEmpty, OnlyIfEmpty, Limit(1)) need to stay in the inside of the loop they are put in. They should not migrate down to faster loops the way ordinary single-row operators can.

As a consequence of this, outer joins (NullIfEmpty) need more often to materialize the join via Project. For instance, given t1 LEFT t2 LEFT t3, inside the t2 ⟕ t3 loop, If_Empty to supply a NULL t3, then Project(t2,t3) to implement that join, then outside that loop, but still inside the outer loop, If_Empty to supply the NULL of that join, then another Project to combine t1 and t2,t3 fields.

The code changes are relatively straightforward: the basic mechanisms were already in place, including adding Projects. This branch adds some more tests, so reviewers should convince themselves that the generated plans for those tests are correct for their queries. Also, several existing tests that would have duplicated rows from a semi-join are affected in similar ways.

To post a comment you must log in.
Revision history for this message
Thomas Jones-Low (tjoneslo) wrote :

As described.

review: Approve

Preview Diff

[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
=== modified file 'src/main/java/com/akiban/sql/optimizer/rule/MapFolder.java'
--- src/main/java/com/akiban/sql/optimizer/rule/MapFolder.java 2013-03-22 20:05:57 +0000
+++ src/main/java/com/akiban/sql/optimizer/rule/MapFolder.java 2013-07-30 18:29:34 +0000
@@ -149,18 +149,7 @@
149 map.setInner(new NullIfEmpty(map.getInner()));149 map.setInner(new NullIfEmpty(map.getInner()));
150 break;150 break;
151 case SEMI:151 case SEMI:
152 {152 map.setInner(new Limit(map.getInner(), 1));
153 PlanNode inner = map.getInner();
154 if ((inner instanceof Select) &&
155 ((Select)inner).getConditions().isEmpty())
156 inner = ((Select)inner).getInput();
157 // Right-nested semi-joins only need one Limit, since
158 // all the effects happen inside the fastest loop.
159 if (!((inner instanceof MapJoin) &&
160 ((MapJoin)inner).getJoinType() == JoinNode.JoinType.SEMI))
161 inner = new Limit(map.getInner(), 1);
162 map.setInner(inner);
163 }
164 break;153 break;
165 case ANTI:154 case ANTI:
166 map.setInner(new OnlyIfEmpty(map.getInner()));155 map.setInner(new OnlyIfEmpty(map.getInner()));
@@ -204,11 +193,21 @@
204 (parent instanceof ResultSet) ||193 (parent instanceof ResultSet) ||
205 (parent instanceof AggregateSource) ||194 (parent instanceof AggregateSource) ||
206 (parent instanceof Sort) ||195 (parent instanceof Sort) ||
196 (parent instanceof NullIfEmpty) ||
197 (parent instanceof OnlyIfEmpty) ||
198 (parent instanceof Limit) ||
207 // Captures enough at the edge of the inside.199 // Captures enough at the edge of the inside.
208 (child instanceof Project) ||200 (child instanceof Project) ||
209 (child instanceof UpdateInput)));201 (child instanceof UpdateInput)));
210 if (child != map) {202 if (child != map) {
211 PlanNode inner = map.getInner();203 PlanNode inner = map.getInner();
204 // Add a Project to capture fields within the loop before
205 // leaving the scope of its outer binding, that is,
206 // materialize the join as a projected row.
207 // The cases where this is needed are:
208 // (1) This loop is itself on the outer side of another loop.
209 // (2) It implements the nullable side of an outer join.
210 // (3) Nested inside and so feeding another instance of (2) or (3).
212 if (parent instanceof MapJoin) {211 if (parent instanceof MapJoin) {
213 MapJoinProject nested = findAddedProject((MapJoin)parent, 212 MapJoinProject nested = findAddedProject((MapJoin)parent,
214 mapJoinProjects);213 mapJoinProjects);
@@ -226,12 +225,31 @@
226 nested, mapJoinProjects);225 nested, mapJoinProjects);
227 }226 }
228 }227 }
228 else if (parent instanceof NullIfEmpty) {
229 // Even though we stop at the outer join, we still
230 // need to find the map that it's contained in.
231 PlanNode ancestor = parent;
232 do {
233 ancestor = ancestor.getOutput();
234 } while ((ancestor instanceof Select) ||
235 (ancestor instanceof Project));
236 if (ancestor instanceof MapJoin) {
237 MapJoinProject nested = findAddedProject((MapJoin)ancestor,
238 mapJoinProjects);
239 inner = addProject((MapJoin)ancestor, map, inner,
240 nested, mapJoinProjects);
241 }
242 }
229 map.getOutput().replaceInput(map, inner);243 map.getOutput().replaceInput(map, inner);
230 parent.replaceInput(child, map);244 parent.replaceInput(child, map);
231 map.setInner(child);245 map.setInner(child);
232 }246 }
233 }247 }
234248
249 // A pending Project used to capture bindings inside the loop.
250 // When complete, we will walk the tree to determine which fields,
251 // if any, from its scope are used downstream and capture them or
252 // else throw the Project away.
235 static class MapJoinProject implements PlanVisitor, ExpressionVisitor {253 static class MapJoinProject implements PlanVisitor, ExpressionVisitor {
236 MapJoin parentMap, childMap;254 MapJoin parentMap, childMap;
237 MapJoinProject nested;255 MapJoinProject nested;
@@ -269,6 +287,7 @@
269 this.innerSources = innerSources;287 this.innerSources = innerSources;
270 }288 }
271 289
290 // Are any of the inner bindings used outer?
272 public boolean find() {291 public boolean find() {
273 columns = new ArrayList<>();292 columns = new ArrayList<>();
274 for (MapJoinProject loop = this; loop != null; loop = loop.nested) {293 for (MapJoinProject loop = this; loop != null; loop = loop.nested) {
@@ -280,6 +299,7 @@
280 return foundOuter;299 return foundOuter;
281 }300 }
282301
302 // Actually install field expressions for this Project.
283 public void install() {303 public void install() {
284 Set<ColumnExpression> seen = new HashSet<>();304 Set<ColumnExpression> seen = new HashSet<>();
285 for (ColumnExpression column : columns) {305 for (ColumnExpression column : columns) {
@@ -289,6 +309,7 @@
289 }309 }
290 }310 }
291311
312 // Splice this unneeded Project out.
292 public void remove() {313 public void remove() {
293 project.getOutput().replaceInput(project, project.getInput());314 project.getOutput().replaceInput(project, project.getInput());
294 project = null;315 project = null;
295316
=== modified file 'src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/README.txt'
--- src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/README.txt 2012-09-05 21:30:52 +0000
+++ src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/README.txt 2013-07-30 18:29:34 +0000
@@ -1,5 +1,23 @@
1aggregate: Aggregation with nested loops1aggregate: Aggregation with nested loops
22
3nested-inner-inner: Nested joins: inner then inner
4
5nested-inner-left: Nested joins: inner then left
6
7nested-inner-right: Nested joins: inner then right
8
9nested-left-inner: Nested joins: left then inner
10
11nested-left-left: Nested joins: left then left
12
13nested-left-right: Nested joins: left then right
14
15nested-right-inner: Nested joins: right then inner
16
17nested-right-left: Nested joins: right then left
18
19nested-right-right: Nested joins: right then right
20
3outer: Outer join across groups.21outer: Outer join across groups.
422
5outer-join-condition: Outer join with another condition.23outer-join-condition: Outer join with another condition.
624
=== added file 'src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/nested-inner-inner.expected'
--- src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/nested-inner-inner.expected 1970-01-01 00:00:00 +0000
+++ src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/nested-inner-inner.expected 2013-07-30 18:29:34 +0000
@@ -0,0 +1,18 @@
1SelectQuery@6f8affe
2 ResultSet@30ea5045[id, s, id, s, id, s]
3 MapJoin@ca7bd3d()
4 Flatten@6fe01bb8(t1)
5 GroupScan@5878f648(Group(test.t1), rows = 1, cost = 0.00000)
6 TableSource@1c26ffc6(t1 - TableGroup@44622e9a(t1))
7 MapJoin@6d711e58()
8 Flatten@623c0669(t2)
9 Select@1d7f5b8a[t2.id == t1.id]
10 GroupScan@1452d027(Group(test.t2), rows = 1, cost = 0.00000)
11 TableSource@58fc4d0e(t2 - TableGroup@9e1a389(t2))
12 Project@673bfb19[t1.id, t1.s, t2.id, t2.s, t3.id, t3.s]
13 Select@12e25b42[]
14 Select@48dfbadd[]
15 Flatten@52c909e1(t3)
16 AncestorLookup@5865288b(t3 -> [t3])
17 SingleIndexScan@5948eb03(Index(test.t3.PRIMARY[IndexColumn(id)]), NONE, =t2.id, rows = 1, cost = 11.9848)
18 TableSource@2d3c68ad(t3 - TableGroup@3cb77c70(t3))
0\ No newline at end of file19\ No newline at end of file
120
=== added file 'src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/nested-inner-inner.sql'
--- src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/nested-inner-inner.sql 1970-01-01 00:00:00 +0000
+++ src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/nested-inner-inner.sql 2013-07-30 18:29:34 +0000
@@ -0,0 +1,1 @@
1SELECT * FROM t1 INNER JOIN t2 ON t1.id = t2.id INNER JOIN t3 ON t2.id = t3.id
0\ No newline at end of file2\ No newline at end of file
13
=== added file 'src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/nested-inner-left.expected'
--- src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/nested-inner-left.expected 1970-01-01 00:00:00 +0000
+++ src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/nested-inner-left.expected 2013-07-30 18:29:34 +0000
@@ -0,0 +1,19 @@
1SelectQuery@34a7c644
2 ResultSet@72c9b55d[id, s, id, s, id, s]
3 MapJoin@35f9de2()
4 Flatten@7b020cf2(t1)
5 GroupScan@7a766228(Group(test.t1), rows = 1, cost = 0.00000)
6 TableSource@5e1f9277(t1 - TableGroup@72266ee7(t1))
7 MapJoin@393b3a3d()
8 Flatten@43a022f5(t2)
9 Select@5f6c9825[t2.id == t1.id]
10 GroupScan@5278d614(Group(test.t2), rows = 1, cost = 0.00000)
11 TableSource@42dd975(t2 - TableGroup@7b2ee558(t2))
12 Project@18a2278b[t1.id, t1.s, t2.id, t2.s, t3.id, t3.s]
13 Select@3dd2234f[]
14 Select@43d33396[]
15 NullIfEmpty@5edfdfa3
16 Flatten@46572582(t3)
17 AncestorLookup@3bd376fe(t3 -> [t3])
18 SingleIndexScan@38293c61(Index(test.t3.PRIMARY[IndexColumn(id)]), NONE, =t2.id, rows = 1, cost = 11.9848)
19 TableSource@6ac8f2e7(t3 - TableGroup@1b3b2115(t3))
0\ No newline at end of file20\ No newline at end of file
121
=== added file 'src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/nested-inner-left.sql'
--- src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/nested-inner-left.sql 1970-01-01 00:00:00 +0000
+++ src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/nested-inner-left.sql 2013-07-30 18:29:34 +0000
@@ -0,0 +1,1 @@
1SELECT * FROM t1 INNER JOIN t2 ON t1.id = t2.id LEFT JOIN t3 ON t2.id = t3.id
0\ No newline at end of file2\ No newline at end of file
13
=== added file 'src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/nested-inner-right.expected'
--- src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/nested-inner-right.expected 1970-01-01 00:00:00 +0000
+++ src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/nested-inner-right.expected 2013-07-30 18:29:34 +0000
@@ -0,0 +1,20 @@
1SelectQuery@6dd64b31
2 ResultSet@fe2b64d[id, s, id, s, id, s]
3 MapJoin@788f8d68()
4 Flatten@6a8d819(t3)
5 GroupScan@188ce4ae(Group(test.t3), rows = 1, cost = 0.00000)
6 TableSource@4252736f(t3 - TableGroup@49f96819(t3))
7 Project@6d2e8160[t1.id, t1.s, t2.id, t2.s, t3.id, t3.s]
8 Select@82ffda0[]
9 NullIfEmpty@3966e4fa
10 MapJoin@15216740()
11 Flatten@370354b9(t1)
12 GroupScan@4401a596(Group(test.t1), rows = 1, cost = 0.00000)
13 TableSource@15492050(t1 - TableGroup@481e3dbb(t1))
14 Select@3baf59e6[]
15 Project@406793be[t1.id, t2.id, t1.s, t2.s]
16 Flatten@753729a6(t2)
17 AncestorLookup@1242f687(t2 -> [t2])
18 Select@6a47226e[t3.id == t2.id]
19 SingleIndexScan@64259a44(Index(test.t2.PRIMARY[IndexColumn(id)]), NONE, =t1.id, rows = 1, cost = 11.9848)
20 TableSource@33189e6d(t2 - TableGroup@60b319b9(t2))
0\ No newline at end of file21\ No newline at end of file
122
=== added file 'src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/nested-inner-right.sql'
--- src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/nested-inner-right.sql 1970-01-01 00:00:00 +0000
+++ src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/nested-inner-right.sql 2013-07-30 18:29:34 +0000
@@ -0,0 +1,1 @@
1SELECT * FROM t1 INNER JOIN t2 ON t1.id = t2.id RIGHT JOIN t3 ON t2.id = t3.id
0\ No newline at end of file2\ No newline at end of file
13
=== added file 'src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/nested-left-inner.expected'
--- src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/nested-left-inner.expected 1970-01-01 00:00:00 +0000
+++ src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/nested-left-inner.expected 2013-07-30 18:29:34 +0000
@@ -0,0 +1,20 @@
1SelectQuery@773bf444
2 ResultSet@121d984[id, s, id, s, id, s]
3 MapJoin@14336855()
4 Flatten@12d86228(t1)
5 GroupScan@3b06aecf(Group(test.t1), rows = 1, cost = 0.00000)
6 TableSource@765bf858(t1 - TableGroup@33beb64f(t1))
7 Project@554f69b0[t1.id, t1.s, t2.id, t2.s, t3.id, t3.s]
8 Select@50a3cd91[]
9 NullIfEmpty@17db7729
10 MapJoin@2db31713()
11 Flatten@3c1439c2(t2)
12 Select@48aff7d5[t2.id == t1.id]
13 GroupScan@18b7e63b(Group(test.t2), rows = 1, cost = 0.00000)
14 TableSource@2b72af70(t2 - TableGroup@516c382a(t2))
15 Select@496c30fb[]
16 Project@781ffa5a[t2.id, t2.s, t3.id, t3.s]
17 Flatten@53dc0e5e(t3)
18 AncestorLookup@5e03d465(t3 -> [t3])
19 SingleIndexScan@4d6d631b(Index(test.t3.PRIMARY[IndexColumn(id)]), NONE, =t2.id, rows = 1, cost = 11.9848)
20 TableSource@4ec2506(t3 - TableGroup@4885a953(t3))
0\ No newline at end of file21\ No newline at end of file
122
=== added file 'src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/nested-left-inner.sql'
--- src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/nested-left-inner.sql 1970-01-01 00:00:00 +0000
+++ src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/nested-left-inner.sql 2013-07-30 18:29:34 +0000
@@ -0,0 +1,1 @@
1SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id INNER JOIN t3 ON t2.id = t3.id
0\ No newline at end of file2\ No newline at end of file
13
=== added file 'src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/nested-left-left.expected'
--- src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/nested-left-left.expected 1970-01-01 00:00:00 +0000
+++ src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/nested-left-left.expected 2013-07-30 18:29:34 +0000
@@ -0,0 +1,21 @@
1SelectQuery@5b3a7993
2 ResultSet@5c03d0af[id, s, id, s, id, s]
3 MapJoin@1b85f4a1()
4 Flatten@72b85245(t1)
5 GroupScan@777f8424(Group(test.t1), rows = 1, cost = 0.00000)
6 TableSource@7dc52fe(t1 - TableGroup@54bc94c6(t1))
7 Project@7e79c05a[t1.id, t1.s, t2.id, t2.s, t3.id, t3.s]
8 Select@6b439594[]
9 NullIfEmpty@15282f42
10 MapJoin@28cb7ea0()
11 Flatten@23216ada(t2)
12 Select@47e64f4c[t2.id == t1.id]
13 GroupScan@61602b74(Group(test.t2), rows = 1, cost = 0.00000)
14 TableSource@154587e3(t2 - TableGroup@70c4fe9d(t2))
15 Select@136a0e8[]
16 Project@7d558b0e[t2.id, t2.s, t3.id, t3.s]
17 NullIfEmpty@297ddff7
18 Flatten@66ee203a(t3)
19 AncestorLookup@6fae669(t3 -> [t3])
20 SingleIndexScan@422cfb13(Index(test.t3.PRIMARY[IndexColumn(id)]), NONE, =t2.id, rows = 1, cost = 11.9848)
21 TableSource@36d7da6e(t3 - TableGroup@1717be56(t3))
0\ No newline at end of file22\ No newline at end of file
123
=== added file 'src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/nested-left-left.sql'
--- src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/nested-left-left.sql 1970-01-01 00:00:00 +0000
+++ src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/nested-left-left.sql 2013-07-30 18:29:34 +0000
@@ -0,0 +1,1 @@
1SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id LEFT JOIN t3 ON t2.id = t3.id
0\ No newline at end of file2\ No newline at end of file
13
=== added file 'src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/nested-left-right.expected'
--- src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/nested-left-right.expected 1970-01-01 00:00:00 +0000
+++ src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/nested-left-right.expected 2013-07-30 18:29:34 +0000
@@ -0,0 +1,20 @@
1SelectQuery@47d13cd
2 ResultSet@2f9efa08[id, s, id, s, id, s]
3 MapJoin@41cc788f()
4 Flatten@7138ebd2(t3)
5 GroupScan@56ff1608(Group(test.t3), rows = 1, cost = 0.00000)
6 TableSource@492c032(t3 - TableGroup@4ff93d7(t3))
7 Project@6e313ba4[t1.id, t1.s, t2.id, t2.s, t3.id, t3.s]
8 Select@6242c480[]
9 NullIfEmpty@c2a3624
10 MapJoin@27430ad1()
11 Flatten@50026210(t1)
12 GroupScan@227b337a(Group(test.t1), rows = 1, cost = 0.00000)
13 TableSource@5818428c(t1 - TableGroup@4670a045(t1))
14 Select@4c743179[t3.id == t2.id]
15 Project@7e1c0bf9[t1.id, t2.id, t1.s, t2.s]
16 NullIfEmpty@27487bb9
17 Flatten@605c2625(t2)
18 AncestorLookup@7b2c2d5(t2 -> [t2])
19 SingleIndexScan@691531e5(Index(test.t2.PRIMARY[IndexColumn(id)]), NONE, =t1.id, rows = 1, cost = 11.9848)
20 TableSource@3e8814f6(t2 - TableGroup@6e7ee748(t2))
0\ No newline at end of file21\ No newline at end of file
122
=== added file 'src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/nested-left-right.sql'
--- src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/nested-left-right.sql 1970-01-01 00:00:00 +0000
+++ src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/nested-left-right.sql 2013-07-30 18:29:34 +0000
@@ -0,0 +1,1 @@
1SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id RIGHT JOIN t3 ON t2.id = t3.id
0\ No newline at end of file2\ No newline at end of file
13
=== added file 'src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/nested-right-inner.expected'
--- src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/nested-right-inner.expected 1970-01-01 00:00:00 +0000
+++ src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/nested-right-inner.expected 2013-07-30 18:29:34 +0000
@@ -0,0 +1,19 @@
1SelectQuery@70a66e76
2 ResultSet@3e9e3ec1[id, s, id, s, id, s]
3 MapJoin@73bf3f3f()
4 Flatten@5f0b23d8(t3)
5 GroupScan@10d17c77(Group(test.t3), rows = 1, cost = 0.00000)
6 TableSource@3fbd8be(t3 - TableGroup@29427141(t3))
7 MapJoin@49197790()
8 Flatten@545e6aa7(t2)
9 Select@26f8b46e[t3.id == t2.id]
10 GroupScan@120db5bf(Group(test.t2), rows = 1, cost = 0.00000)
11 TableSource@b52a3fd(t2 - TableGroup@421717db(t2))
12 Project@72a1105[t1.id, t1.s, t2.id, t2.s, t3.id, t3.s]
13 Select@5ac362ef[]
14 Select@7a1f4cc2[]
15 NullIfEmpty@1ce89331
16 Flatten@2ac2537(t1)
17 AncestorLookup@691784ca(t1 -> [t1])
18 SingleIndexScan@70f1fad(Index(test.t1.PRIMARY[IndexColumn(id)]), NONE, =t2.id, rows = 1, cost = 11.9848)
19 TableSource@5e8c3cd9(t1 - TableGroup@71e89a79(t1))
0\ No newline at end of file20\ No newline at end of file
121
=== added file 'src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/nested-right-inner.sql'
--- src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/nested-right-inner.sql 1970-01-01 00:00:00 +0000
+++ src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/nested-right-inner.sql 2013-07-30 18:29:34 +0000
@@ -0,0 +1,1 @@
1SELECT * FROM t1 RIGHT JOIN t2 ON t1.id = t2.id INNER JOIN t3 ON t2.id = t3.id
0\ No newline at end of file2\ No newline at end of file
13
=== added file 'src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/nested-right-left.expected'
--- src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/nested-right-left.expected 1970-01-01 00:00:00 +0000
+++ src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/nested-right-left.expected 2013-07-30 18:29:34 +0000
@@ -0,0 +1,19 @@
1SelectQuery@7eba8a14
2 ResultSet@8bf680c[id, s, id, s, id, s]
3 MapJoin@630fa3df()
4 Flatten@5ddbcc3d(t2)
5 GroupScan@17cfb848(Group(test.t2), rows = 1, cost = 0.00000)
6 TableSource@6f6ce13f(t2 - TableGroup@464b8b2e(t2))
7 MapJoin@99e9d10()
8 NullIfEmpty@c598a5f
9 Flatten@93de8ef(t1)
10 AncestorLookup@4a8b694e(t1 -> [t1])
11 SingleIndexScan@6aea61e(Index(test.t1.PRIMARY[IndexColumn(id)]), NONE, =t2.id, rows = 1, cost = 11.9848)
12 TableSource@5538283f(t1 - TableGroup@341bfeff(t1))
13 Project@4e47f050[t1.id, t1.s, t2.id, t2.s, t3.id, t3.s]
14 Select@54f23c56[]
15 NullIfEmpty@1a023213
16 Flatten@3f87a7a6(t3)
17 AncestorLookup@6258abc(t3 -> [t3])
18 SingleIndexScan@ab73fcb(Index(test.t3.PRIMARY[IndexColumn(id)]), NONE, =t2.id, rows = 1, cost = 11.9848)
19 TableSource@48d07bc1(t3 - TableGroup@4bd2dec(t3))
0\ No newline at end of file20\ No newline at end of file
121
=== added file 'src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/nested-right-left.sql'
--- src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/nested-right-left.sql 1970-01-01 00:00:00 +0000
+++ src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/nested-right-left.sql 2013-07-30 18:29:34 +0000
@@ -0,0 +1,1 @@
1SELECT * FROM t1 RIGHT JOIN t2 ON t1.id = t2.id LEFT JOIN t3 ON t2.id = t3.id
0\ No newline at end of file2\ No newline at end of file
13
=== added file 'src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/nested-right-right.expected'
--- src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/nested-right-right.expected 1970-01-01 00:00:00 +0000
+++ src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/nested-right-right.expected 2013-07-30 18:29:34 +0000
@@ -0,0 +1,21 @@
1SelectQuery@5f42920f
2 ResultSet@b8347a5[id, s, id, s, id, s]
3 MapJoin@2711edd3()
4 Flatten@4d45a08d(t3)
5 GroupScan@a14cbaf(Group(test.t3), rows = 1, cost = 0.00000)
6 TableSource@577ab9(t3 - TableGroup@5b485354(t3))
7 Project@57d4a78a[t1.id, t1.s, t2.id, t2.s, t3.id, t3.s]
8 Select@4a438212[]
9 NullIfEmpty@2013d7b
10 MapJoin@6956de9d()
11 Flatten@162fb09d(t2)
12 Select@46314b72[t3.id == t2.id]
13 GroupScan@4e504d5e(Group(test.t2), rows = 1, cost = 0.00000)
14 TableSource@6f3a07db(t2 - TableGroup@7a077e7c(t2))
15 Select@1f6b5744[]
16 Project@3548a494[t2.id, t1.id, t1.s, t2.s]
17 NullIfEmpty@402e4179
18 Flatten@14f41ecc(t1)
19 AncestorLookup@1ccc8fda(t1 -> [t1])
20 SingleIndexScan@39f83ffb(Index(test.t1.PRIMARY[IndexColumn(id)]), NONE, =t2.id, rows = 1, cost = 11.9848)
21 TableSource@4ad9f3e9(t1 - TableGroup@59309578(t1))
0\ No newline at end of file22\ No newline at end of file
123
=== added file 'src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/nested-right-right.sql'
--- src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/nested-right-right.sql 1970-01-01 00:00:00 +0000
+++ src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/nested-right-right.sql 2013-07-30 18:29:34 +0000
@@ -0,0 +1,1 @@
1SELECT * FROM t1 RIGHT JOIN t2 ON t1.id = t2.id RIGHT JOIN t3 ON t2.id = t3.id
0\ No newline at end of file2\ No newline at end of file
13
=== modified file 'src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/schema.ddl'
--- src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/schema.ddl 2012-02-26 19:35:32 +0000
+++ src/test/resources/com/akiban/sql/optimizer/rule/fold-maps/schema.ddl 2013-07-30 18:29:34 +0000
@@ -41,3 +41,7 @@
41 GROUPING FOREIGN KEY (cid) REFERENCES customers(cid)41 GROUPING FOREIGN KEY (cid) REFERENCES customers(cid)
42);42);
43CREATE INDEX state ON addresses(state);43CREATE INDEX state ON addresses(state);
44
45CREATE TABLE t1(id INT PRIMARY KEY NOT NULL, s VARCHAR(10));
46CREATE TABLE t2(id INT PRIMARY KEY NOT NULL, s VARCHAR(10));
47CREATE TABLE t3(id INT PRIMARY KEY NOT NULL, s VARCHAR(10));
4448
=== modified file 'src/test/resources/com/akiban/sql/optimizer/rule/reverse-ins/README.txt'
--- src/test/resources/com/akiban/sql/optimizer/rule/reverse-ins/README.txt 2013-01-08 23:18:56 +0000
+++ src/test/resources/com/akiban/sql/optimizer/rule/reverse-ins/README.txt 2013-07-30 18:29:34 +0000
@@ -33,3 +33,9 @@
33map-join-project: Need both sides of outer loop.33map-join-project: Need both sides of outer loop.
3434
35map-join-project-nested: Needed in inner loop because outer loop is like that.35map-join-project-nested: Needed in inner loop because outer loop is like that.
36
37nested-in: nested semi-joins
38
39nested-not-in: nested anti-joins
40
41nested-in-not: nested semi inside anti
3642
=== modified file 'src/test/resources/com/akiban/sql/optimizer/rule/reverse-ins/map-join-project-nested.expected'
--- src/test/resources/com/akiban/sql/optimizer/rule/reverse-ins/map-join-project-nested.expected 2013-01-08 22:14:51 +0000
+++ src/test/resources/com/akiban/sql/optimizer/rule/reverse-ins/map-join-project-nested.expected 2013-07-30 18:29:34 +0000
@@ -3,10 +3,10 @@
3 MapJoin@6dca323d()3 MapJoin@6dca323d()
4 SingleIndexScan@75a87b1a(Index(test.customers.name[IndexColumn(name)]), covering/NONE, rows = 100, cost = 74.1740)4 SingleIndexScan@75a87b1a(Index(test.customers.name[IndexColumn(name)]), covering/NONE, rows = 100, cost = 74.1740)
5 MapJoin@82a244f()5 MapJoin@82a244f()
6 MapJoin@6b5c2b9()6 NullIfEmpty@66374dc7
7 Select@7030320[c3.name == c2.name]7 MapJoin@6b5c2b9()
8 SingleIndexScan@56c22d78(Index(test.customers.name[IndexColumn(name)]), covering/NONE, rows = 100, cost = 74.1740)8 Select@7030320[c3.name == c2.name]
9 NullIfEmpty@66374dc79 SingleIndexScan@56c22d78(Index(test.customers.name[IndexColumn(name)]), covering/NONE, rows = 100, cost = 74.1740)
10 Select@4663a6c1[]10 Select@4663a6c1[]
11 MapJoin@38d3783e()11 MapJoin@38d3783e()
12 SingleIndexScan@437b4b97(Index(test.customers.name[IndexColumn(name)]), covering/NONE, =c2.name, rows = 1, cost = 6.04220)12 SingleIndexScan@437b4b97(Index(test.customers.name[IndexColumn(name)]), covering/NONE, =c2.name, rows = 1, cost = 6.04220)
1313
=== modified file 'src/test/resources/com/akiban/sql/optimizer/rule/reverse-ins/map-join-project.expected'
--- src/test/resources/com/akiban/sql/optimizer/rule/reverse-ins/map-join-project.expected 2013-01-08 22:14:51 +0000
+++ src/test/resources/com/akiban/sql/optimizer/rule/reverse-ins/map-join-project.expected 2013-07-30 18:29:34 +0000
@@ -3,9 +3,9 @@
3 MapJoin@273d638c()3 MapJoin@273d638c()
4 SingleIndexScan@2f529074(Index(test.customers.name[IndexColumn(name)]), covering/NONE, rows = 100, cost = 74.1740)4 SingleIndexScan@2f529074(Index(test.customers.name[IndexColumn(name)]), covering/NONE, rows = 100, cost = 74.1740)
5 MapJoin@24ebb33()5 MapJoin@24ebb33()
6 MapJoin@7ee41273()6 NullIfEmpty@4db64e4e
7 SingleIndexScan@2f777d1a(Index(test.customers.name[IndexColumn(name)]), covering/NONE, rows = 100, cost = 74.1740)7 MapJoin@7ee41273()
8 NullIfEmpty@4db64e4e8 SingleIndexScan@2f777d1a(Index(test.customers.name[IndexColumn(name)]), covering/NONE, rows = 100, cost = 74.1740)
9 Select@2ec72eb4[]9 Select@2ec72eb4[]
10 Project@7bcf0abd[c1.name, c1.cid]10 Project@7bcf0abd[c1.name, c1.cid]
11 Select@55ca3dcb[c3.name == c2.name]11 Select@55ca3dcb[c3.name == c2.name]
1212
=== added file 'src/test/resources/com/akiban/sql/optimizer/rule/reverse-ins/nested-in-not.expected'
--- src/test/resources/com/akiban/sql/optimizer/rule/reverse-ins/nested-in-not.expected 1970-01-01 00:00:00 +0000
+++ src/test/resources/com/akiban/sql/optimizer/rule/reverse-ins/nested-in-not.expected 2013-07-30 18:29:34 +0000
@@ -0,0 +1,14 @@
1SelectQuery@338c94e3
2 ResultSet@5282dd85[name]
3 MapJoin@52a6490c()
4 SingleIndexScan@2305dd38(Index(test.customers.name[IndexColumn(name)]), covering/NONE, rows = 100, cost = 74.1740)
5 Project@d9d7115[customers.name]
6 Select@5b6b20ae[]
7 OnlyIfEmpty@2dfd1d0c
8 MapJoin@55fe997e()
9 Select@3e0f4b4d[customers.cid == orders.cid]
10 SingleIndexScan@5e18cc0f(Index(test.orders.__akiban_fk_0[IndexColumn(cid)]), covering/NONE, rows = 1000, cost = 688.942)
11 Select@66261964[]
12 Limit@447e166a(LIMIT 1)
13 Select@4f4d28a0[orders.oid == items.oid]
14 SingleIndexScan@31f1ac47(Index(test.items.sku[IndexColumn(sku)]), covering/NONE, =1234, rows = 97, cost = 158.981)
0\ No newline at end of file15\ No newline at end of file
116
=== added file 'src/test/resources/com/akiban/sql/optimizer/rule/reverse-ins/nested-in-not.sql'
--- src/test/resources/com/akiban/sql/optimizer/rule/reverse-ins/nested-in-not.sql 1970-01-01 00:00:00 +0000
+++ src/test/resources/com/akiban/sql/optimizer/rule/reverse-ins/nested-in-not.sql 2013-07-30 18:29:34 +0000
@@ -0,0 +1,3 @@
1SELECT name FROM customers
2 WHERE cid NOT IN (SELECT cid FROM orders
3 WHERE oid IN (SELECT oid FROM items WHERE sku = '1234'))
0\ No newline at end of file4\ No newline at end of file
15
=== added file 'src/test/resources/com/akiban/sql/optimizer/rule/reverse-ins/nested-in.expected'
--- src/test/resources/com/akiban/sql/optimizer/rule/reverse-ins/nested-in.expected 1970-01-01 00:00:00 +0000
+++ src/test/resources/com/akiban/sql/optimizer/rule/reverse-ins/nested-in.expected 2013-07-30 18:29:34 +0000
@@ -0,0 +1,18 @@
1SelectQuery@270cd144
2 ResultSet@3a7edb63[name]
3 MapJoin@30944cb9()
4 SingleIndexScan@58391e99(Index(test.customers.name[IndexColumn(name)]), covering/NONE, rows = 100, cost = 74.1740)
5 Project@5e755e95[customers.name]
6 Select@6b94b8a5[]
7 Limit@2f7708ec(LIMIT 1)
8 MapJoin@e07fc0f()
9 Select@2e3543fb[customers.cid == orders.cid]
10 SingleIndexScan@2b022b70(Index(test.orders.__akiban_fk_0[IndexColumn(cid)]), covering/NONE, rows = 1000, cost = 688.942)
11 Select@9a6f6ca[]
12 Limit@1b71dc1f(LIMIT 1)
13 Select@b80fa71[]
14 Flatten@457514c6(items)
15 Select@502a854c[items.sku == 1234]
16 AncestorLookup@179569b2(items -> [items])
17 SingleIndexScan@524e3136(Index(test.items.__akiban_fk_1[IndexColumn(oid)]), NONE, =orders.oid, rows = 1, cost = 188.762)
18 TableSource@b849d5c(items - TableGroup@b80fa71(customers))
0\ No newline at end of file19\ No newline at end of file
120
=== added file 'src/test/resources/com/akiban/sql/optimizer/rule/reverse-ins/nested-in.sql'
--- src/test/resources/com/akiban/sql/optimizer/rule/reverse-ins/nested-in.sql 1970-01-01 00:00:00 +0000
+++ src/test/resources/com/akiban/sql/optimizer/rule/reverse-ins/nested-in.sql 2013-07-30 18:29:34 +0000
@@ -0,0 +1,3 @@
1SELECT name FROM customers
2 WHERE cid IN (SELECT cid FROM orders
3 WHERE oid IN (SELECT oid FROM items WHERE sku = '1234'))
0\ No newline at end of file4\ No newline at end of file
15
=== added file 'src/test/resources/com/akiban/sql/optimizer/rule/reverse-ins/nested-not-in.expected'
--- src/test/resources/com/akiban/sql/optimizer/rule/reverse-ins/nested-not-in.expected 1970-01-01 00:00:00 +0000
+++ src/test/resources/com/akiban/sql/optimizer/rule/reverse-ins/nested-not-in.expected 2013-07-30 18:29:34 +0000
@@ -0,0 +1,14 @@
1SelectQuery@173d3c4c
2 ResultSet@3541ad7f[name]
3 MapJoin@6a58f699()
4 SingleIndexScan@76a5ed5a(Index(test.customers.name[IndexColumn(name)]), covering/NONE, rows = 100, cost = 74.1740)
5 Project@6ae58829[customers.name]
6 Select@34e7293[]
7 OnlyIfEmpty@29662a39
8 MapJoin@781ec91()
9 Select@62d22770[customers.cid == orders.cid]
10 SingleIndexScan@531b5cbf(Index(test.orders.__akiban_fk_0[IndexColumn(cid)]), covering/NONE, rows = 1000, cost = 688.942)
11 Select@7cc50827[]
12 OnlyIfEmpty@725e196a
13 Select@6b9a786f[orders.oid == items.oid]
14 SingleIndexScan@7fd6b8ce(Index(test.items.sku[IndexColumn(sku)]), covering/NONE, =1234, rows = 97, cost = 158.981)
0\ No newline at end of file15\ No newline at end of file
116
=== added file 'src/test/resources/com/akiban/sql/optimizer/rule/reverse-ins/nested-not-in.sql'
--- src/test/resources/com/akiban/sql/optimizer/rule/reverse-ins/nested-not-in.sql 1970-01-01 00:00:00 +0000
+++ src/test/resources/com/akiban/sql/optimizer/rule/reverse-ins/nested-not-in.sql 2013-07-30 18:29:34 +0000
@@ -0,0 +1,3 @@
1SELECT name FROM customers
2 WHERE cid NOT IN (SELECT cid FROM orders
3 WHERE oid NOT IN (SELECT oid FROM items WHERE sku = '1234'))
0\ No newline at end of file4\ No newline at end of file
15
=== modified file 'src/test/resources/com/akiban/sql/optimizer/rule/reverse-ins/not-in-inner.expected'
--- src/test/resources/com/akiban/sql/optimizer/rule/reverse-ins/not-in-inner.expected 2012-11-19 16:24:59 +0000
+++ src/test/resources/com/akiban/sql/optimizer/rule/reverse-ins/not-in-inner.expected 2013-07-30 18:29:34 +0000
@@ -2,11 +2,11 @@
2 ResultSet@55de5cd2[cid]2 ResultSet@55de5cd2[cid]
3 MapJoin@5208873f()3 MapJoin@5208873f()
4 SingleIndexScan@2de7632c(Index(test.customers.name[IndexColumn(name)]), covering/NONE, rows = 100, cost = 74.1740)4 SingleIndexScan@2de7632c(Index(test.customers.name[IndexColumn(name)]), covering/NONE, rows = 100, cost = 74.1740)
5 MapJoin@321ff53f()5 Project@79a00b08[customers.cid]
6 SingleIndexScan@501e14ce(Index(test.parent.PRIMARY[IndexColumn(id)]), covering/NONE, rows = 100, cost = 69.9260)6 Select@7a347899[]
7 Project@79a00b08[customers.cid]7 OnlyIfEmpty@66e80979
8 Select@7a347899[]8 MapJoin@321ff53f()
9 OnlyIfEmpty@66e809799 SingleIndexScan@501e14ce(Index(test.parent.PRIMARY[IndexColumn(id)]), covering/NONE, rows = 100, cost = 69.9260)
10 Select@287c958f[]10 Select@287c958f[]
11 Flatten@b3ef40d(items)11 Flatten@b3ef40d(items)
12 Select@bc61bb7[customers.name == items.sku]12 Select@bc61bb7[customers.name == items.sku]

Subscribers

People subscribed via source and target branches