Merge lp:~mmcm/akiban-server/sql-flatten-expr-subquery into lp:~akiban-technologies/akiban-server/trunk

[Mike McMahon]

Fix flattening of comparison with unique expression subquery.

If a subquery is unique (returns at most one row -- this is the basic criterion of flattening), < and < ANY are the same (etc. -- and provided that things are simple enough that UNKNOWN vs. FALSE doesn't matter). The flattener understood this, but dropped the ball at the very end in formulating the new comparison node. Its type comes from the outer comparison, not the inside of the subquery, which will be simply EXPRESSION.

[Nathan Williams (nwilliams)]

Looks good.

review: Approve

=== modified file 'src/main/java/com/akiban/sql/optimizer/SubqueryFlattener.java'
--- src/main/java/com/akiban/sql/optimizer/SubqueryFlattener.java	2013-06-22 18:35:49 +0000
+++ src/main/java/com/akiban/sql/optimizer/SubqueryFlattener.java	2013-08-07 19:29:28 +0000
@@ -221,42 +221,46 @@
         }
 
         int nodeType = 0;
-        switch (subqueryNode.getSubqueryType()) {
-            // TODO: The ALL and NOT_IN cases aren't actually supported here yet.
-        case IN:
-        case EQ_ANY:
-        case NOT_IN:
-        case NE_ALL:
-            nodeType = NodeTypes.BINARY_EQUALS_OPERATOR_NODE;
-            break;
-
-        case NE_ANY:
-        case EQ_ALL:
-            nodeType = NodeTypes.BINARY_NOT_EQUALS_OPERATOR_NODE;
-            break;
-
-        case LE_ANY:
-        case GT_ALL:
-            nodeType = NodeTypes.BINARY_LESS_EQUALS_OPERATOR_NODE;
-            break;
-
-        case LT_ANY:
-        case GE_ALL:
-            nodeType = NodeTypes.BINARY_LESS_THAN_OPERATOR_NODE;
-            break;
-
-        case GE_ANY:
-        case LT_ALL:
-            nodeType = NodeTypes.BINARY_GREATER_EQUALS_OPERATOR_NODE;
-            break;
-
-        case GT_ANY:
-        case LE_ALL:
-            nodeType = NodeTypes.BINARY_GREATER_THAN_OPERATOR_NODE;
-            break;
-
-        default:
-            assert false;
+        if (parentComparisonOperator != null)
+            nodeType = parentComparisonOperator.getNodeType();
+        else {
+            switch (subqueryNode.getSubqueryType()) {
+                // TODO: The ALL and NOT_IN cases aren't actually supported here yet.
+            case IN:
+            case EQ_ANY:
+            case NOT_IN:
+            case NE_ALL:
+                nodeType = NodeTypes.BINARY_EQUALS_OPERATOR_NODE;
+                break;
+
+            case NE_ANY:
+            case EQ_ALL:
+                nodeType = NodeTypes.BINARY_NOT_EQUALS_OPERATOR_NODE;
+                break;
+
+            case LE_ANY:
+            case GT_ALL:
+                nodeType = NodeTypes.BINARY_LESS_EQUALS_OPERATOR_NODE;
+                break;
+
+            case LT_ANY:
+            case GE_ALL:
+                nodeType = NodeTypes.BINARY_LESS_THAN_OPERATOR_NODE;
+                break;
+
+            case GE_ANY:
+            case LT_ALL:
+                nodeType = NodeTypes.BINARY_GREATER_EQUALS_OPERATOR_NODE;
+                break;
+
+            case GT_ANY:
+            case LE_ALL:
+                nodeType = NodeTypes.BINARY_GREATER_THAN_OPERATOR_NODE;
+                break;
+
+            default:
+                assert false;
+            }
         }
         ValueNode newNode = (ValueNode)nodeFactory.getNode(nodeType, leftOperand, rightOperand, parserContext);
         newNode.setType(new DataTypeDescriptor(TypeId.BOOLEAN_ID, false));

=== modified file 'src/test/resources/com/akiban/sql/optimizer/flatten/README.txt'
--- src/test/resources/com/akiban/sql/optimizer/flatten/README.txt	2013-06-22 18:32:07 +0000
+++ src/test/resources/com/akiban/sql/optimizer/flatten/README.txt	2013-08-07 19:29:28 +0000
@@ -23,3 +23,5 @@
 select-9: VALUES single row
 
 select-9x: VALUES multiple rows (no change)
+
+select-10: outer comparison instead of ANY

=== added file 'src/test/resources/com/akiban/sql/optimizer/flatten/select-10.expected'
--- src/test/resources/com/akiban/sql/optimizer/flatten/select-10.expected	1970-01-01 00:00:00 +0000
+++ src/test/resources/com/akiban/sql/optimizer/flatten/select-10.expected	2013-08-07 19:29:28 +0000
@@ -0,0 +1,1 @@
+SELECT test.child.name AS name FROM test.child, test.parent WHERE (test.parent.name = 'foo') AND ((test.child.pid > test.parent.id) AND TRUE)
\ No newline at end of file

=== added file 'src/test/resources/com/akiban/sql/optimizer/flatten/select-10.sql'
--- src/test/resources/com/akiban/sql/optimizer/flatten/select-10.sql	1970-01-01 00:00:00 +0000
+++ src/test/resources/com/akiban/sql/optimizer/flatten/select-10.sql	2013-08-07 19:29:28 +0000
@@ -0,0 +1,1 @@
+SELECT name FROM child WHERE pid > (SELECT parent.id FROM parent WHERE parent.name = 'foo')