db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From kahat...@apache.org
Subject svn commit: r1595778 - in /db/derby/code/trunk/java: engine/org/apache/derby/iapi/sql/compile/ engine/org/apache/derby/impl/sql/compile/ engine/org/apache/derby/loc/ shared/org/apache/derby/shared/common/reference/ testing/org/apache/derbyTesting/funct...
Date Mon, 19 May 2014 08:23:11 GMT
Author: kahatlen
Date: Mon May 19 08:23:11 2014
New Revision: 1595778

URL: http://svn.apache.org/r1595778
Log:
DERBY-1576: Extend the CASE expression syntax for "simple case"

Implement simple case as described by the SQL:1999 standard, including the
SQL:2003 extension F262 "Extended CASE expression", and the SQL:2011
extension F263 "Comma-separated predicates in simple CASE expression".

Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/compile/CompilerContext.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ConditionalNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/QueryTreeNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
    db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml
    db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CaseExpressionTest.java

Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/compile/CompilerContext.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/compile/CompilerContext.java?rev=1595778&r1=1595777&r2=1595778&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/compile/CompilerContext.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/compile/CompilerContext.java
Mon May 19 08:23:11 2014
@@ -132,6 +132,10 @@ public interface CompilerContext extends
 	public	static	final	int			CONDITIONAL_RESTRICTION		= NEXT_VALUE_FOR_ILLEGAL;
 	public	static	final	int			GROUP_BY_RESTRICTION		= NEXT_VALUE_FOR_ILLEGAL;
 
+    public static final int CASE_OPERAND_RESTRICTION =
+            CONDITIONAL_RESTRICTION | NON_DETERMINISTIC_ILLEGAL |
+            MODIFIES_SQL_DATA_PROCEDURE_ILLEGAL;
+
     public  static  final   String  WHERE_SCOPE = "whereScope";
     
 	/////////////////////////////////////////////////////////////////////////////////////

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ConditionalNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ConditionalNode.java?rev=1595778&r1=1595777&r2=1595778&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ConditionalNode.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ConditionalNode.java
Mon May 19 08:23:11 2014
@@ -46,6 +46,12 @@ import org.apache.derby.iapi.util.JBitSe
 
 class ConditionalNode extends ValueNode
 {
+    /**
+     * The case operand if this is a simple case expression. Otherwise, it
+     * is {@code null}.
+     */
+    private ValueNode caseOperand;
+
     /** The list of test conditions in the WHEN clauses. */
     private ValueNodeList testConditions;
 
@@ -58,15 +64,19 @@ class ConditionalNode extends ValueNode
 	/**
      * Constructor for a ConditionalNode
 	 *
+     * @param caseOperand       The case operand if this is a simple case
+     *                          expression, or {@code null} otherwise
      * @param testConditions    The boolean test conditions
 	 * @param thenElseList		ValueNodeList with then and else expressions
      * @param cm                The context manager
 	 */
-    ConditionalNode(ValueNodeList testConditions,
+    ConditionalNode(ValueNode caseOperand,
+                    ValueNodeList testConditions,
                     ValueNodeList thenElseList,
                     ContextManager cm)
 	{
         super(cm);
+        this.caseOperand = caseOperand;
         this.testConditions = testConditions;
         this.thenElseList = thenElseList;
 	}
@@ -184,7 +194,9 @@ class ConditionalNode extends ValueNode
         CompilerContext cc = getCompilerContext();
         
         int previousReliability = orReliability( CompilerContext.CONDITIONAL_RESTRICTION
);
-        
+
+        bindCaseOperand(cc, fromList, subqueryList, aggregates);
+
         testConditions.bindExpression(fromList,
 			subqueryList,
             aggregates);
@@ -278,6 +290,27 @@ class ConditionalNode extends ValueNode
 		return this;
 	}
 
+    /**
+     * Bind the case operand, if there is one, and check that it doesn't
+     * contain anything that's illegal in a case operand (such as calls to
+     * routines that are non-deterministic or modifies SQL).
+     */
+    private void bindCaseOperand(CompilerContext cc, FromList fromList,
+                                 SubqueryList subqueryList,
+                                List<AggregateNode> aggregates)
+            throws StandardException {
+
+        if (caseOperand != null) {
+            int previousReliability = orReliability(
+                    CompilerContext.CASE_OPERAND_RESTRICTION);
+
+            caseOperand = caseOperand.bindExpression(
+                    fromList, subqueryList, aggregates);
+
+            cc.setReliability(previousReliability);
+        }
+    }
+
 	/**
 	 * Preprocess an expression tree.  We do a number of transformations
 	 * here (including subqueries, IN lists, LIKE and BETWEEN) plus

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/QueryTreeNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/QueryTreeNode.java?rev=1595778&r1=1595777&r2=1595778&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/QueryTreeNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/QueryTreeNode.java Mon
May 19 08:23:11 2014
@@ -1471,15 +1471,16 @@ public abstract class QueryTreeNode impl
      */
     private void throwReliabilityException( String fragmentType, int fragmentBitMask ) throws
StandardException
     {
+        final int reliability = getCompilerContext().getReliability();
         String sqlState;
 		/* Error string somewhat dependent on operation due to different
 		 * nodes being allowed for different operations.
 		 */
-		if (getCompilerContext().getReliability() == CompilerContext.DEFAULT_RESTRICTION)
+        if (reliability == CompilerContext.DEFAULT_RESTRICTION)
 		{
             sqlState = SQLState.LANG_INVALID_DEFAULT_DEFINITION;
 		}
-		else if (getCompilerContext().getReliability() == CompilerContext.GENERATION_CLAUSE_RESTRICTION)
+        else if (reliability == CompilerContext.GENERATION_CLAUSE_RESTRICTION)
 		{
             switch ( fragmentBitMask )
             {
@@ -1493,13 +1494,17 @@ public abstract class QueryTreeNode impl
             }
 		}
         else if (
-                 (getCompilerContext().getReliability() & fragmentBitMask & CompilerContext.SQL_IN_ROUTINES_ILLEGAL)
+                 (reliability & fragmentBitMask & CompilerContext.SQL_IN_ROUTINES_ILLEGAL)
                  != 0
                  )
         {
             sqlState = SQLState.LANG_ROUTINE_CANT_PERMIT_SQL;
         }
-		else
+        else if (reliability == CompilerContext.CHECK_CONSTRAINT)
+        {
+            sqlState = SQLState.LANG_UNRELIABLE_CHECK_CONSTRAINT;
+        }
+        else
 		{
             sqlState = SQLState.LANG_UNRELIABLE_QUERY_FRAGMENT;
 		}

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj?rev=1595778&r1=1595777&r2=1595778&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj Mon May
19 08:23:11 2014
@@ -818,6 +818,7 @@ public class SQLParser
 		  case LESS_THAN_OR_EQUALS_OPERATOR:
 		  case GREATER_THAN_OR_EQUALS_OPERATOR:
 		  case IN:
+          case IS:
 		  case LIKE:
 		  case BETWEEN:
 		  	retval = true;
@@ -5444,33 +5445,12 @@ andExpression(ValueNode farLeftOperand) 
 ValueNode
 isSearchCondition() throws StandardException :
 {
-	ValueNode	result;
 	ValueNode	booleanPrimary;
-	Token		isToken = null;
-	Token		notToken = null;
-	Token		truthValue = null;
 }
 {
 	booleanPrimary = booleanPrimary()
-	[
-		isToken = <IS> [ notToken = <NOT> ] <NULL>
-	]
 	{
-		if ( isToken != null )
-		{
-            result = new IsNullNode(booleanPrimary,
-                                    false,
-                                    getContextManager());
-		
-            /* Put the NOT on top of the tree */
-				if (notToken != null)
-				{
-                result = new NotNode(result, getContextManager());
-            }
-        }
-		else { result = booleanPrimary; }
-
-		return result;
+        return booleanPrimary;
 	}
 }
 
@@ -5602,6 +5582,7 @@ remainingNegatablePredicate(ValueNode le
 	ValueNode	betweenLeft;
 	ValueNode	betweenRight;
 	ValueNode	escapeValue = null;
+    Token       notToken = null;
 }
 {
 	<IN> tree = inPredicateValue(leftOperand)
@@ -5609,6 +5590,12 @@ remainingNegatablePredicate(ValueNode le
 		return tree;
 	}
 |
+    <IS> [ notToken = <NOT> ] <NULL>
+    {
+        return new IsNullNode(
+                leftOperand, (notToken != null), getContextManager());
+    }
+|
 	<LIKE> likePattern = additiveExpression(null, 0)
             [
                 <ESCAPE> escapeValue = additiveExpression(null, 0) |
@@ -12553,6 +12540,7 @@ valueSpecification() throws StandardExce
 	ValueNode	   value;
 	ValueNode	   leftExpression;
 	ValueNode	   rightExpression;
+    ValueNode      caseOperand;
 }
 {
 	value = literal()
@@ -12586,14 +12574,21 @@ valueSpecification() throws StandardExce
        thenElseList.addElement(new UntypedNullConstantNode(cm));
 		thenElseList.addElement(leftExpression);
 
-        return new ConditionalNode(whenList, thenElseList, cm);
+        return new ConditionalNode(null, whenList, thenElseList, cm);
 	}
 |
 	// CASE WHEN P1 THEN [T1 | NULL] (WHEN Pi THEN [Ti | NULL])* [ELSE E | NULL] END
+    LOOKAHEAD({ getToken(1).kind == CASE && getToken(2).kind == WHEN })
     <CASE> value = searchedCaseExpression()
 	{
 		return value;
 	}
+|
+    <CASE> caseOperand = valueExpression()
+           value = simpleCaseExpression(caseOperand)
+    {
+        return value;
+    }
 }
 
 ConditionalNode searchedCaseExpression() throws StandardException :
@@ -12614,7 +12609,7 @@ ConditionalNode searchedCaseExpression()
         }
         thenElseList.addElement(elseExpr);
 
-        return new ConditionalNode(whenList, thenElseList, cm);
+        return new ConditionalNode(null, whenList, thenElseList, cm);
     }
 }
 
@@ -12658,6 +12653,85 @@ thenElseExpression() throws StandardExce
 	}
 }
 
+ConditionalNode simpleCaseExpression(ValueNode caseOperand)
+throws StandardException :
+{
+    ContextManager cm = getContextManager();
+    ValueNodeList whenList = new ValueNodeList(cm);
+    ValueNodeList thenElseList = new ValueNodeList(cm);
+    ValueNode elseExpr = null;
+}
+{
+    ( simpleWhenClause(caseOperand, whenList, thenElseList) ) +
+    ( <ELSE> elseExpr = thenElseExpression() ) ?
+    <END>
+    {
+        if (elseExpr == null) {
+            // ELSE NULL is implicit if there is no ELSE clause.
+            elseExpr = new UntypedNullConstantNode(cm);
+        }
+        thenElseList.addElement(elseExpr);
+
+        return new ConditionalNode(caseOperand, whenList, thenElseList, cm);
+    }
+}
+
+void
+simpleWhenClause(ValueNode caseOperand,
+                 ValueNodeList whenList, ValueNodeList thenElseList)
+throws StandardException :
+{
+    ValueNode expr;
+    ValueNode thenExpr;
+}
+{
+    <WHEN> expr = whenOperandList(caseOperand)
+    <THEN> thenExpr = thenElseExpression()
+    {
+        whenList.addElement(expr);
+        thenElseList.addElement(thenExpr);
+    }
+}
+
+ValueNode whenOperandList(ValueNode caseOperand) throws StandardException :
+{
+    ValueNode first;
+    ValueNode rest = null;
+}
+{
+    first = whenOperand(caseOperand)
+    ( <COMMA> rest = whenOperandList(caseOperand) )?
+    {
+        if (rest == null) {
+            return first;
+        } else {
+            return new OrNode(first, rest, getContextManager());
+        }
+    }
+}
+
+ValueNode whenOperand(ValueNode caseOperand) throws StandardException :
+{
+    ContextManager cm = getContextManager();
+    ValueNode expr;
+}
+{
+    // Both remainingPredicate and valueExpression can start with NOT, so
+    // let's look ahead first.
+    LOOKAHEAD({ remainingPredicateFollows() })
+    expr = remainingPredicate(caseOperand)
+    {
+        return expr;
+    }
+|
+    expr = valueExpression()
+    {
+        return new BinaryRelationalOperatorNode(
+                BinaryRelationalOperatorNode.K_EQUALS,
+                caseOperand, expr, false, cm);
+    }
+}
+
 TableElementNode
 tableConstraintDefinition() throws StandardException :
 {

Modified: db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml?rev=1595778&r1=1595777&r2=1595778&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml Mon May 19 08:23:11
2014
@@ -2843,6 +2843,12 @@ Guide.
             </msg>
 
             <msg>
+                <name>42Y98</name>
+                <text>'{0}' may not appear in this context because it may return unreliable
results.</text>
+                <arg>sqlObjectName</arg>
+            </msg>
+
+            <msg>
                 <name>42Z02</name>
                 <text>Multiple DISTINCT aggregates are not supported at this time.</text>
             </msg>

Modified: db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java?rev=1595778&r1=1595777&r2=1595778&view=diff
==============================================================================
--- db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java
(original)
+++ db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java
Mon May 19 08:23:11 2014
@@ -1003,7 +1003,7 @@ public interface SQLState {
 	String LANG_INVALID_COL_REF_GROUPED_SELECT_LIST                    = "42Y36";
 	String LANG_TYPE_DOESNT_EXIST3                                     = "42Y37";
 	String LANG_INVALID_BULK_INSERT_REPLACE                            = "42Y38";
-	String LANG_UNRELIABLE_QUERY_FRAGMENT                              = "42Y39";
+    String LANG_UNRELIABLE_CHECK_CONSTRAINT                            = "42Y39";
 	String LANG_DUPLICATE_COLUMN_IN_TRIGGER_UPDATE                     = "42Y40";
 	String LANG_TRIGGER_SPS_CANNOT_BE_EXECED                           = "42Y41";
 	String LANG_INVALID_DECIMAL_SCALE                                  = "42Y42";
@@ -1042,6 +1042,7 @@ public interface SQLState {
 	String LANG_BINARY_LOGICAL_NON_BOOLEAN                             = "42Y94";
 	String LANG_BINARY_OPERATOR_NOT_SUPPORTED                          = "42Y95";
 	String LANG_INVALID_ESCAPE										   = "42Y97";
+    String LANG_UNRELIABLE_QUERY_FRAGMENT                              = "42Y98";
 	String LANG_JAVA_METHOD_CALL_OR_FIELD_REF						   = "42Z00.U";
 	String LANG_UNTYPED												   = "42Z01.U";
 	// TEMPORARY COMPILATION RESTRICTIONS

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CaseExpressionTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CaseExpressionTest.java?rev=1595778&r1=1595777&r2=1595778&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CaseExpressionTest.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CaseExpressionTest.java
Mon May 19 08:23:11 2014
@@ -556,4 +556,174 @@ public class CaseExpressionTest extends 
                 + "from (values 'Y', 'N') v(c)"),
             new String[][] { { "N", null }, { "Y", "true" }});
     }
+
+    /**
+     * Tests for the simple case syntax added in DERBY-1576.
+     */
+    public void testSimpleCaseSyntax() throws SQLException {
+        Statement s = createStatement();
+
+        // Simplest of the simple cases. SQL:1999 syntax, which allows a
+        // single operand per WHEN clause, and the operand is a value
+        // expression.
+        JDBC.assertUnorderedResultSet(s.executeQuery(
+                "select i, case i when 0 then 'zero' "
+                + "when 1 then 'one' when 1+1 then 'two' "
+                + "else 'many' end from "
+                + "(values 0, 1, 2, 3, cast(null as int)) v(i)"),
+            new String[][] {
+                {"0", "zero"},
+                {"1", "one"},
+                {"2", "two"},
+                {"3", "many"},
+                {null, "many"}
+            });
+
+        // SQL:2003 added feature F262 Extended CASE Expression, which
+        // allows more complex WHEN operands. Essentially, it allows any
+        // last part of a predicate (everything after the left operand).
+        JDBC.assertFullResultSet(s.executeQuery(
+                "select i, case i when < 0 then 'negative' "
+                        + "when < 10 then 'small' "
+                        + "when between 10 and 20 then 'medium' "
+                        + "when in (19, 23, 29, 37, 41) then 'prime' "
+                        + "when = some (values 7, 42) then 'lucky number' "
+                        + "when >= 40 then 'big' end "
+                        + "from (values -1, 0, 1, 2, 3, 8, 9, 10, 17, 19, "
+                        + "29, 37, 38, 39, 40, 41, 42, 50) v(i) order by i"),
+            new String[][] {
+                { "-1", "negative" },
+                { "0", "small" },
+                { "1", "small" },
+                { "2", "small" },
+                { "3", "small" },
+                { "8", "small" },
+                { "9", "small" },
+                { "10", "medium" },
+                { "17", "medium" },
+                { "19", "medium" },
+                { "29", "prime" },
+                { "37", "prime" },
+                { "38", null },
+                { "39", null },
+                { "40", "big" },
+                { "41", "prime" },
+                { "42", "lucky number" },
+                { "50", "big" },
+            });
+
+        JDBC.assertUnorderedResultSet(s.executeQuery(
+                "select c, case c "
+                + "when like 'abc%' then 0 "
+                + "when like 'x%%' escape 'x' then 1 "
+                + "when = all (select ibmreqd from sysibm.sysdummy1) then 2 "
+                + "when 'xyz' || 'zyx' then 3 "
+                + "when is null then 4 "
+                + "when is not null then 5 end "
+                + "from (values 'abcdef', 'xyzzyx', '%s', 'hello', "
+                + "cast(null as char(1)), 'Y', 'N') v(c)"),
+            new String[][] {
+                { "abcdef", "0" },
+                { "xyzzyx", "3" },
+                { "%s", "1" },
+                { "hello", "5" },
+                { null, "4" },
+                { "Y", "2" },
+                { "N", "5" },
+            });
+
+        // SQL:2011 added feature F263 Comma-separated predicates in simple
+        // CASE expression, which allows multiple operands per WHEN clause.
+        JDBC.assertFullResultSet(s.executeQuery(
+                "select i, case i "
+                + "when between 2 and 3, 5, =7 then 'prime' "
+                + "when <1, >7 then 'out of range' "
+                + "when is not null then 'small' end "
+                + "from (values 0, 1, 2, 3, 4, 5, 6, 7, 8, cast(null as int)) "
+                + "as v(i) order by i"),
+            new String[][] {
+                { "0", "out of range" },
+                { "1", "small" },
+                { "2", "prime" },
+                { "3", "prime" },
+                { "4", "small" },
+                { "5", "prime" },
+                { "6", "small" },
+                { "7", "prime" },
+                { "8", "out of range" },
+                { null, null },
+            });
+
+        JDBC.assertUnorderedResultSet(s.executeQuery(
+                "select c, case c "
+                + "when in ('ab', 'cd'), like '_' then 'matched' "
+                + "else 'not matched' end "
+                + "from (values cast('a' as varchar(1)), 'b', 'c', 'ab', "
+                + "'cd', 'ac', 'abc') v(c)"),
+            new String[][] {
+                { "a",   "matched" },
+                { "b",   "matched" },
+                { "c",   "matched" },
+                { "ab",  "matched" },
+                { "cd",  "matched" },
+                { "ac",  "not matched" },
+                { "abc", "not matched" },
+            });
+
+        // Untyped null is not allowed as CASE operand. Use typed null instead.
+        assertCompileError("42X01", "values case null when 1 then 'one' end");
+        JDBC.assertSingleValueResultSet(s.executeQuery(
+                "values case cast(null as int) when 1 then 'one' end"),
+            null);
+
+        // Untyped null is not allowed as WHEN operand. Use IS NULL instead.
+        assertCompileError("42X01", "values case 1 when null then 'null' end");
+        JDBC.assertUnorderedResultSet(s.executeQuery(
+                "select i, case i when is null then 1 when is not null "
+                + "then 2 else 3 end from (values 1, cast(null as int)) v(i)"),
+            new String[][] { { "1", "2" }, { null, "1" } });
+
+        // Non-deterministic functions are not allowed in the case operand.
+        assertCompileError("42Y98",
+                "values case sysfun.random() when 1 then true else false end");
+        assertCompileError("42Y98",
+                "values case (values sysfun.random()) "
+                + "when 1 then true else false end");
+
+        // Deterministic functions, on the other hand, are allowed.
+        JDBC.assertFullResultSet(s.executeQuery(
+                "select case sysfun.sin(angle) when < 0 then 'negative' "
+                        + "when > 0 then 'positive' end "
+                        + "from (values -pi()/2, 0, pi()/2) v(angle) "
+                        + "order by angle"),
+            new String[][] { {"negative"}, {null}, {"positive"} });
+
+        // Non-deterministic functions can be used outside of the case operand.
+        JDBC.assertDrainResults(
+            s.executeQuery(
+                "values case 1 when sysfun.random() then sysfun.random() end"),
+            1);
+
+        // Scalar subqueries are allowed in the case operand.
+        JDBC.assertSingleValueResultSet(
+                s.executeQuery("values case (values 1) when 1 then true end"),
+                "true");
+
+        // Non-scalar subqueries are not allowed.
+        assertCompileError(
+                "42X39", "values case (values (1, 2)) when 1 then true end");
+        assertStatementError(
+                "21000", s, "values case (values 1, 2) when 1 then true end");
+
+        // The type of the CASE operand must be compatible with the types
+        // of all the WHEN operands.
+        assertCompileError("42818", "values case 1 when true then 'yes' end");
+        assertCompileError("42818",
+                "values case 1 when 1 then 'yes' when 2 then 'no' "
+                + "when 'three' then 'maybe' end");
+        JDBC.assertSingleValueResultSet(s.executeQuery(
+                "values case cast(1 as bigint)"
+                + " when cast(1 as smallint) then 'yes' end"),
+            "yes");
+    }
 }



Mime
View raw message