| /** |
| * Derby - Class org.apache.derbyTesting.functionTests.tests.lang.CaseExpressionTest |
| * |
| * Licensed to the Apache Software Foundation (ASF) under one |
| * or more contributor license agreements. See the NOTICE file |
| * distributed with this work for additional information |
| * regarding copyright ownership. The ASF licenses this file |
| * to you under the Apache License, Version 2.0 (the |
| * "License"); you may not use this file except in compliance |
| * with the License. You may obtain a copy of the License at |
| * |
| * http://www.apache.org/licenses/LICENSE-2.0 |
| * |
| * Unless required by applicable law or agreed to in writing, |
| * software distributed under the License is distributed on an |
| * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY |
| * KIND, either express or implied. See the License for the |
| * specific language governing permissions and limitations |
| * under the License. |
| */ |
| |
| package org.apache.derbyTesting.functionTests.tests.lang; |
| |
| import java.sql.ParameterMetaData; |
| import java.sql.PreparedStatement; |
| import java.sql.ResultSet; |
| import java.sql.ResultSetMetaData; |
| import java.sql.SQLException; |
| import java.sql.Statement; |
| import java.sql.Types; |
| import java.util.ArrayList; |
| import java.util.Arrays; |
| import java.util.concurrent.atomic.AtomicInteger; |
| import junit.framework.Test; |
| import org.apache.derbyTesting.functionTests.util.streams.LoopingAlphabetReader; |
| import org.apache.derbyTesting.functionTests.util.streams.LoopingAlphabetStream; |
| import org.apache.derbyTesting.junit.BaseJDBCTestCase; |
| import org.apache.derbyTesting.junit.BaseTestSuite; |
| import org.apache.derbyTesting.junit.CleanDatabaseTestSetup; |
| import org.apache.derbyTesting.junit.JDBC; |
| import org.apache.derbyTesting.junit.SQLUtilities; |
| import org.apache.derbyTesting.junit.TestConfiguration; |
| |
| |
| public class CaseExpressionTest extends BaseJDBCTestCase { |
| |
| // Results if the Case Expression evaluates to a column reference : |
| // |
| // 1. SELECT CASE WHEN 1 = 1 THEN <column reference> ELSE NULL END |
| // 2. SELECT CASE WHEN 1 = 1 THEN |
| // (CASE WHEN 1 = 1 THEN <column reference> ELSE NULL END) |
| // ELSE NULL END |
| // |
| private static String[][] columnReferenceResults = { |
| /*SMALLINT*/ {null,"0","1","2"}, |
| /*INTEGER*/ {null,"0","1","21"}, |
| /*BIGINT*/ {null,"0","1","22"}, |
| /*DECIMAL(10,5)*/ {null,"0.00000","1.00000","23.00000"}, |
| /*REAL*/ {null,"0.0","1.0","24.0"}, |
| /*DOUBLE*/ {null,"0.0","1.0","25.0"}, |
| /*CHAR(60)*/ { |
| null, |
| "0 ", |
| "aa ", |
| "2.0 "}, |
| /*VARCHAR(60)*/ {null,"0","aa","15:30:20"}, |
| /*LONG VARCHAR*/ {null,"0","aa","2000-01-01 15:30:20"}, |
| /*CHAR(60) FOR BIT DATA*/ { |
| null, |
| "10aa20202020202020202020202020202020202020202020202020202020" + |
| "202020202020202020202020202020202020202020202020202020202020", |
| null, |
| "10aaaa202020202020202020202020202020202020202020202020202020" + |
| "202020202020202020202020202020202020202020202020202020202020"}, |
| /*VARCHAR(60) FOR BIT DATA*/ {null,"10aa",null,"10aaba"}, |
| /*LONG VARCHAR FOR BIT DATA*/ {null,"10aa",null,"10aaca"}, |
| /*CLOB(1k)*/ {null,"13","14",null}, |
| /*DATE*/ {null,"2000-01-01","2000-01-01",null}, |
| /*TIME*/ {null,"15:30:20","15:30:20","15:30:20"}, |
| /*TIMESTAMP*/ { |
| null, |
| "2000-01-01 15:30:20.0", |
| "2000-01-01 15:30:20.0", |
| "2000-01-01 15:30:20.0"}, |
| /*BLOB(1k)*/ {null,null,null,null}, |
| }; |
| |
| |
| |
| // Results if the Case Expression evaluates to a NULL value : |
| // |
| // 3. SELECT CASE WHEN 1 = 1 THEN NULL ELSE <column reference> END |
| // 4. SELECT CASE WHEN 1 = 1 THEN |
| // (CASE WHEN 1 = 1 THEN NULL ELSE <column reference> END) |
| // ELSE NULL END |
| // 5. SELECT CASE WHEN 1 = 1 THEN NULL ELSE |
| // (CASE WHEN 1 = 1 THEN <column reference> ELSE NULL END) END |
| // 6. SELECT CASE WHEN 1 = 1 THEN NULL ELSE |
| // (CASE WHEN 1 = 1 THEN NULL ELSE <column reference> END) END |
| // |
| private static String[][] nullValueResults = { |
| /*SMALLINT*/ {null,null,null,null}, |
| /*INTEGER*/ {null,null,null,null}, |
| /*BIGINT*/ {null,null,null,null}, |
| /*DECIMAL(10,5)*/ {null,null,null,null}, |
| /*REAL*/ {null,null,null,null}, |
| /*DOUBLE*/ {null,null,null,null}, |
| /*CHAR(60)*/ {null,null,null,null}, |
| /*VARCHAR(60)*/ {null,null,null,null}, |
| /*LONG VARCHAR*/ {null,null,null,null}, |
| /*CHAR(60) FOR BIT DATA*/ {null,null,null,null}, |
| /*VARCHAR(60) FOR BIT DATA*/ {null,null,null,null}, |
| /*LONG VARCHAR FOR BIT DATA*/ {null,null,null,null}, |
| /*CLOB(1k)*/ {null,null,null,null}, |
| /*DATE*/ {null,null,null,null}, |
| /*TIME*/ {null,null,null,null}, |
| /*TIMESTAMP*/ {null,null,null,null}, |
| /*BLOB(1k)*/ {null,null,null,null}, |
| }; |
| |
| public CaseExpressionTest(String name) { |
| super(name); |
| } |
| |
| /** |
| * Test various statements that |
| * |
| */ |
| public void testWhenNonBoolean() { |
| |
| // DERBY-2809: BOOLEAN datatype was forced upon |
| // unary expressions that were not BOOLEAN, such |
| // as SQRT(?) |
| String[] unaryOperators = { |
| "SQRT(?)", "SQRT(9)", |
| "UPPER(?)", "UPPER('haight')", |
| "LOWER(?)", "LOWER('HAIGHT')", |
| }; |
| for (int i = 0; i < unaryOperators.length; i++) |
| { |
| assertCompileError("42X88", |
| "VALUES CASE WHEN " + unaryOperators[i] + |
| " THEN 3 ELSE 4 END"); |
| } |
| } |
| |
| public void testAllDatatypesCombinationsForCaseExpressions() |
| throws SQLException |
| { |
| Statement s = createStatement(); |
| |
| /* 1. Column Reference in the THEN node, and NULL in |
| * the ELSE node. |
| */ |
| testCaseExpressionQuery(s, columnReferenceResults, |
| "SELECT CASE WHEN 1 = 1 THEN ", |
| " ELSE NULL END from AllDataTypesTable"); |
| |
| /* 2. Test Column Reference nested in the THEN's node THEN node, |
| * NULL's elsewhere. |
| */ |
| testCaseExpressionQuery(s, columnReferenceResults, |
| "SELECT CASE WHEN 1 = 1 THEN (CASE WHEN 1 = 1 THEN ", |
| " ELSE NULL END) ELSE NULL END from AllDataTypesTable"); |
| |
| /* 3. NULL in the THEN node, and a Column Reference in |
| * the ELSE node. |
| */ |
| testCaseExpressionQuery(s, nullValueResults, |
| "SELECT CASE WHEN 1 = 1 THEN NULL ELSE ", |
| " END from AllDataTypesTable"); |
| |
| /* 4. Test Column Reference nested in the THEN's node ELSE node, |
| * NULL's elsewhere. |
| */ |
| testCaseExpressionQuery(s, nullValueResults, |
| "SELECT CASE WHEN 1 = 1 THEN (CASE WHEN 1 = 1 THEN NULL ELSE ", |
| " END) ELSE NULL END from AllDataTypesTable"); |
| |
| /* 5. Test Column Reference nested in the ELSE's node THEN node, |
| * NULL's elsewhere. |
| */ |
| testCaseExpressionQuery(s, nullValueResults, |
| "SELECT CASE WHEN 1 = 1 THEN NULL ELSE (CASE WHEN 1 = 1 THEN ", |
| " ELSE NULL END) END from AllDataTypesTable"); |
| |
| /* 6. Test Column Reference nested in the ELSE's node ELSE node, |
| * NULL's elsewhere. |
| */ |
| testCaseExpressionQuery(s, nullValueResults, |
| "SELECT CASE WHEN 1 = 1 THEN NULL " + |
| "ELSE (CASE WHEN 1 = 1 THEN NULL ELSE ", |
| " END) END from AllDataTypesTable"); |
| |
| s.close(); |
| } |
| |
| /** |
| * Test a query that has many WHEN conditions in it. This is mostly |
| * checking for the performance regression filed as DERBY-2986. That |
| * regression may not be noticeable in the scope of the full regression |
| * suite, but if this test is run standalone then this fixture could |
| * still be useful. |
| */ |
| public void testMultipleWhens() throws SQLException |
| { |
| Statement s = createStatement(); |
| JDBC.assertFullResultSet( |
| s.executeQuery( |
| "values CASE WHEN 10 = 1 THEN 'a' " + |
| "WHEN 10 = 2 THEN 'b' " + |
| "WHEN 10 = 3 THEN 'c' " + |
| "WHEN 10 = 4 THEN 'd' " + |
| "WHEN 10 = 5 THEN 'e' " + |
| "WHEN 10 = 6 THEN 'f' " + |
| "WHEN 10 = 7 THEN 'g' " + |
| "WHEN 10 = 8 THEN 'h' " + |
| "WHEN 10 = 11 THEN 'i' " + |
| "WHEN 10 = 12 THEN 'j' " + |
| "WHEN 10 = 15 THEN 'k' " + |
| "WHEN 10 = 16 THEN 'l' " + |
| "WHEN 10 = 23 THEN 'm' " + |
| "WHEN 10 = 24 THEN 'n' " + |
| "WHEN 10 = 27 THEN 'o' " + |
| "WHEN 10 = 31 THEN 'p' " + |
| "WHEN 10 = 41 THEN 'q' " + |
| "WHEN 10 = 42 THEN 'r' " + |
| "WHEN 10 = 50 THEN 's' " + |
| "ELSE '*' END"), |
| new String[][] {{"*"}}); |
| |
| s.close(); |
| } |
| |
| /** |
| * Before DERBY-6423, boolean expressions (such as A OR B, or A AND B) |
| * were not accepted in THEN and ELSE clauses. |
| */ |
| public void testBooleanExpressions() throws SQLException { |
| Statement s = createStatement(); |
| |
| // Test both with and without parentheses around the expressions. |
| // Those with parentheses used to work, and those without used to |
| // cause syntax errors. Now both should work. |
| JDBC.assertFullResultSet( |
| s.executeQuery( |
| "select case when a or b then b or c else a or c end,\n" + |
| " case when a and b then b and c else a and c end,\n" + |
| " case when (a or b) then (b or c) else (a or c) end,\n" + |
| " case when (a and b) then (b and c) else (a and c) end\n" + |
| "from (values (true, true, true), (true, true, false),\n" + |
| " (true, false, true), (true, false, false),\n" + |
| " (false, true, true), (false, true, false),\n" + |
| " (false, false, true), (false, false, false)\n" + |
| " ) v(a, b, c)\n" + |
| "order by a desc, b desc, c desc"), |
| new String[][] { |
| { "true", "true", "true", "true" }, |
| { "true", "false", "true", "false" }, |
| { "true", "true", "true", "true" }, |
| { "false", "false", "false", "false" }, |
| { "true", "false", "true", "false" }, |
| { "true", "false", "true", "false" }, |
| { "true", "false", "true", "false" }, |
| { "false", "false", "false", "false" }, |
| }); |
| } |
| |
| /** |
| * Runs the test fixtures in embedded. |
| * |
| * @return test suite |
| */ |
| public static Test suite() |
| { |
| BaseTestSuite suite = (BaseTestSuite) |
| TestConfiguration.embeddedSuite(CaseExpressionTest.class); |
| |
| return new CleanDatabaseTestSetup(suite) { |
| /** |
| * Creates the table used in the test cases. |
| */ |
| protected void decorateSQL(Statement s) throws SQLException { |
| SQLUtilities.createAndPopulateAllDataTypesTable(s); |
| |
| } |
| }; |
| } |
| |
| /** |
| * Execute the received caseExpression on the received Statement |
| * and check the results against the receieved expected array. |
| */ |
| private void testCaseExpressionQuery(Statement st, |
| String [][] expRS, String caseExprBegin, String caseExprEnd) |
| throws SQLException |
| { |
| ResultSet rs; |
| int colType; |
| int row; |
| |
| for (colType = 0; |
| colType < SQLUtilities.SQLTypes.length; |
| colType++) |
| { |
| rs = st.executeQuery( |
| caseExprBegin + |
| SQLUtilities.allDataTypesColumnNames[colType] + |
| caseExprEnd); |
| |
| row = 0; |
| |
| while (rs.next()) { |
| String val = rs.getString(1); |
| assertEquals(expRS[colType][row], val); |
| row++; |
| } |
| rs.close(); |
| } |
| |
| } |
| |
| |
| /** |
| * Test fix for DERBY-3032. Fix ClassCastException if SQL NULL is returned from conditional. |
| * |
| * @throws SQLException |
| */ |
| public void testDerby3032() throws SQLException |
| { |
| Statement s = createStatement(); |
| |
| |
| s.executeUpdate("create table t (d date, vc varchar(30))"); |
| s.executeUpdate("insert into t values(CURRENT_DATE, 'hello')"); |
| ResultSet rs = s.executeQuery("SELECT d from t where d = (SELECT CASE WHEN 1 = 1 THEN CURRENT_DATE ELSE NULL END from t)"); |
| JDBC.assertDrainResults(rs,1); |
| |
| // Make sure null gets cast properly to date type to avoid cast exception. DERBY-3032 |
| rs = s.executeQuery("SELECT d from t where d = (SELECT CASE WHEN 1 = 1 THEN NULL ELSE CURRENT_DATE END from t)"); |
| JDBC.assertEmpty(rs); |
| |
| rs = s.executeQuery("SELECT d from t where d = (SELECT CASE WHEN 1 = 0 THEN CURRENT_DATE ELSE NULL END from t)"); |
| JDBC.assertEmpty(rs); |
| |
| // Make sure metadata has correct type for various null handling |
| rs = s.executeQuery("SELECT CASE WHEN 1 = 1 THEN NULL ELSE CURRENT_DATE END from t"); |
| ResultSetMetaData rsmd = rs.getMetaData(); |
| assertEquals(java.sql.Types.DATE, rsmd.getColumnType(1)); |
| // should be nullable since it returns NULL #:) |
| assertEquals(ResultSetMetaData.columnNullable, rsmd.isNullable(1)); |
| JDBC.assertSingleValueResultSet(rs, null); |
| |
| rs = s.executeQuery("SELECT CASE WHEN 1 = 0 THEN CURRENT_DATE ELSE NULL END from t"); |
| rsmd = rs.getMetaData(); |
| assertEquals(java.sql.Types.DATE, rsmd.getColumnType(1)); |
| // should be nullable since it returns NULL #:) |
| assertEquals(ResultSetMetaData.columnNullable, rsmd.isNullable(1)); |
| JDBC.assertSingleValueResultSet(rs, null); |
| |
| // and with an implicit NULL return. |
| rs = s.executeQuery("SELECT CASE WHEN 1 = 0 THEN CURRENT_DATE END from t"); |
| rsmd = rs.getMetaData(); |
| assertEquals(java.sql.Types.DATE, rsmd.getColumnType(1)); |
| // should be nullable since it returns NULL #:) |
| assertEquals(ResultSetMetaData.columnNullable, rsmd.isNullable(1)); |
| JDBC.assertSingleValueResultSet(rs, null); |
| |
| // and no possible NULL return. |
| |
| rs = s.executeQuery("SELECT CASE WHEN 1 = 0 THEN 6 ELSE 4 END from t"); |
| rsmd = rs.getMetaData(); |
| assertEquals(java.sql.Types.INTEGER, rsmd.getColumnType(1)); |
| // should be nullable since it returns NULL #:) |
| assertEquals(ResultSetMetaData.columnNoNulls, rsmd.isNullable(1)); |
| JDBC.assertSingleValueResultSet(rs, "4"); |
| |
| rs = s.executeQuery("SELECT CASE WHEN 1 = 1 THEN 6 ELSE 4 END from t"); |
| rsmd = rs.getMetaData(); |
| assertEquals(java.sql.Types.INTEGER, rsmd.getColumnType(1)); |
| // should be nullable since it returns NULL #:) |
| assertEquals(ResultSetMetaData.columnNoNulls, rsmd.isNullable(1)); |
| JDBC.assertSingleValueResultSet(rs, "6"); |
| |
| } |
| |
| /** |
| * Verify that NOT elimination produces the correct results. |
| * DERBY-6563. |
| */ |
| public void testNotElimination() throws SQLException { |
| setAutoCommit(false); |
| |
| Statement s = createStatement(); |
| s.execute("create table d6563(b1 boolean, b2 boolean, b3 boolean)"); |
| |
| // Fill the table with all possible combinations of true/false/null. |
| Boolean[] universe = { true, false, null }; |
| PreparedStatement insert = prepareStatement( |
| "insert into d6563 values (?, ?, ?)"); |
| for (Boolean v1 : universe) { |
| insert.setObject(1, v1); |
| for (Boolean v2 : universe) { |
| insert.setObject(2, v2); |
| for (Boolean v3 : universe) { |
| insert.setObject(3, v3); |
| insert.executeUpdate(); |
| } |
| } |
| } |
| |
| // Truth table for |
| // B1, B2, B3, WHEN B1 THEN B2 ELSE B3, NOT (WHEN B1 THEN B2 ELSE B3). |
| Object[][] expectedRows = { |
| { false, false, false, false, true }, |
| { false, false, true, true, false }, |
| { false, false, null, null, null }, |
| { false, true, false, false, true }, |
| { false, true, true, true, false }, |
| { false, true, null, null, null }, |
| { false, null, false, false, true }, |
| { false, null, true, true, false }, |
| { false, null, null, null, null }, |
| { true, false, false, false, true }, |
| { true, false, true, false, true }, |
| { true, false, null, false, true }, |
| { true, true, false, true, false }, |
| { true, true, true, true, false }, |
| { true, true, null, true, false }, |
| { true, null, false, null, null }, |
| { true, null, true, null, null }, |
| { true, null, null, null, null }, |
| { null, false, false, false, true }, |
| { null, false, true, true, false }, |
| { null, false, null, null, null }, |
| { null, true, false, false, true }, |
| { null, true, true, true, false }, |
| { null, true, null, null, null }, |
| { null, null, false, false, true }, |
| { null, null, true, true, false }, |
| { null, null, null, null, null }, |
| }; |
| |
| // Verify the truth table. Since NOT elimination is not performed on |
| // expressions in the SELECT list, this passed even before the fix. |
| JDBC.assertFullResultSet( |
| s.executeQuery( |
| "select b1, b2, b3, case when b1 then b2 else b3 end, " |
| + "not case when b1 then b2 else b3 end " |
| + "from d6563 order by b1, b2, b3"), |
| expectedRows, false); |
| |
| // Now take only those rows where the NOT CASE expression evaluated |
| // to TRUE, and strip off the expression columns at the end. |
| ArrayList<Object[]> rows = new ArrayList<Object[]>(); |
| for (Object[] row : expectedRows) { |
| if (row[4] == Boolean.TRUE) { |
| rows.add(Arrays.copyOf(row, 3)); |
| } |
| } |
| |
| // Assert that those are the only rows returned if the NOT CASE |
| // expression is used as a predicate. This query used to return a |
| // different set of rows before the fix. |
| expectedRows = rows.toArray(new Object[rows.size()][]); |
| JDBC.assertFullResultSet( |
| s.executeQuery("select * from d6563 where " |
| + "not case when b1 then b2 else b3 end " |
| + "order by b1, b2, b3"), |
| expectedRows, false); |
| } |
| |
| /** |
| * Test that parameters can be used in CASE expressions. |
| */ |
| public void testParameters() throws SQLException { |
| // If all of the result expressions are untyped parameters, the |
| // type cannot be determined, and an error should be raised. |
| assertCompileError("42X87", "values case when true then ? else ? end"); |
| |
| // If at least one result expression is typed, the parameter should |
| // get its type from it. |
| PreparedStatement ps = prepareStatement( |
| "values case when true then ? else 1 end"); |
| |
| // DERBY-6567: The result should be nullable, since the parameter |
| // could be set to null. It used to be reported as not nullable. |
| assertEquals(ResultSetMetaData.columnNullable, |
| ps.getMetaData().isNullable(1)); |
| |
| ps.setNull(1, Types.INTEGER); |
| JDBC.assertSingleValueResultSet(ps.executeQuery(), null); |
| |
| ps.setInt(1, 1); |
| JDBC.assertSingleValueResultSet(ps.executeQuery(), "1"); |
| |
| ps = prepareStatement( |
| "values case when true then ? else cast(? as integer) end"); |
| ParameterMetaData params = ps.getParameterMetaData(); |
| assertEquals(Types.INTEGER, params.getParameterType(1)); |
| assertEquals(Types.INTEGER, params.getParameterType(2)); |
| ps.setInt(1, 1); |
| ps.setInt(2, 2); |
| JDBC.assertSingleValueResultSet(ps.executeQuery(), "1"); |
| |
| // Parameters in the WHEN clause can be untyped. They will |
| // implicitly get the BOOLEAN type. |
| ps = prepareStatement("values case when ? then 1 else 0 end"); |
| assertEquals(Types.BOOLEAN, |
| ps.getParameterMetaData().getParameterType(1)); |
| |
| ps.setBoolean(1, true); |
| JDBC.assertSingleValueResultSet(ps.executeQuery(), "1"); |
| |
| ps.setBoolean(1, false); |
| JDBC.assertSingleValueResultSet(ps.executeQuery(), "0"); |
| |
| ps.setNull(1, Types.BOOLEAN); |
| JDBC.assertSingleValueResultSet(ps.executeQuery(), "0"); |
| } |
| |
| /** |
| * Test how untyped NULLs are handled. |
| */ |
| public void testUntypedNulls() throws SQLException { |
| Statement s = createStatement(); |
| |
| // Before DERBY-2002, Derby accepted a CASE expression to have an |
| // untyped NULL in all the result branches. Verify that an error |
| // is raised. |
| String[] allUntyped = { |
| // The SQL standard says at least one result should not be an |
| // untyped NULL, so expect these to fail. |
| "values case when true then null end", |
| "values case when true then null else null end", |
| "values case when true then null " |
| + "when false then null else null end", |
| |
| // We're not able to tell the type if we have a mix of untyped |
| // NULLs and untyped parameters. |
| "values case when true then ? end", // implicit ELSE NULL |
| "values case when true then null else ? end", |
| "values case when true then ? when false then ? else null end", |
| |
| // These ones failed even before DERBY-2002. |
| "values case when true then ? else ? end", |
| "values case when true then ? when false then ? else ? end", |
| }; |
| for (String sql : allUntyped) { |
| assertCompileError("42X87", sql); |
| } |
| |
| // Check that expressions with untyped NULLs compile as long as |
| // there is at least one typed expression. |
| JDBC.assertFullResultSet(s.executeQuery( |
| "select case when a then 1 when b then null end, " |
| + "case when a then null when b then 1 end, " |
| + "case when a then null when b then null else 1 end " |
| + "from (values (false, false), (false, true), " |
| + " (true, false), (true, true)) v(a, b) order by a, b"), |
| new Object[][] { |
| { null, null, 1 }, |
| { null, 1, null }, |
| { 1, null, null }, |
| { 1, null, null }, |
| }, |
| false); |
| |
| // When there is a typed NULL, its type has to be compatible with |
| // the types of the other expressions. |
| assertCompileError("42X89", |
| "values case when 1<>1 then 'abc' else cast(null as smallint) end"); |
| } |
| |
| /** Regression test case for DERBY-6577. */ |
| public void testQuantifiedComparison() throws SQLException { |
| // This query used to return wrong results. |
| JDBC.assertUnorderedResultSet(createStatement().executeQuery( |
| "select c, case when c = all (values 'Y') then true end " |
| + "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"); |
| |
| // A sequence cannot be accessed anywhere in a CASE expression. |
| s.execute("create sequence d1576_s start with 1"); |
| assertCompileError( |
| "42XAH", |
| "values case next value for d1576_s when 1 then 1 else 0 end"); |
| assertCompileError( |
| "42XAH", |
| "values case 1 when next value for d1576_s then 1 else 0 end"); |
| assertCompileError( |
| "42XAH", |
| "values case 1 when 1 then next value for d1576_s else 0 end"); |
| |
| // Instead, access the sequence value in a nested query. |
| JDBC.assertSingleValueResultSet( |
| s.executeQuery( |
| "select case x when 1 then 1 else 0 end from " |
| + "(values next value for d1576_s) v(x)"), |
| "1"); |
| |
| s.execute("drop sequence d1576_s restrict"); |
| |
| // Window functions are allowed. |
| JDBC.assertFullResultSet( |
| s.executeQuery( |
| "select case row_number() over () when 1 then 'one' " |
| + "when 2 then 'two' end from (values 1, 1, 1) v(x)"), |
| new String[][] { {"one"}, {"two"}, {null} }); |
| |
| // Test that you can have a typed parameter in the case operand. |
| PreparedStatement ps = prepareStatement( |
| "values case cast(? as integer) " |
| + "when 1 then 'one' when 2 then 'two' end"); |
| ps.setInt(1, 1); |
| JDBC.assertSingleValueResultSet(ps.executeQuery(), "one"); |
| ps.setInt(1, 2); |
| JDBC.assertSingleValueResultSet(ps.executeQuery(), "two"); |
| ps.setInt(1, 3); |
| JDBC.assertSingleValueResultSet(ps.executeQuery(), null); |
| |
| // This one fails to compile because an integer cannot be checked |
| // with LIKE. |
| assertCompileError("42884", |
| "values case cast(? as integer) " |
| + "when 1 then 1 when like 'abc' then 2 end"); |
| |
| // Untyped parameter in the case operand. Should be able to infer |
| // the type from the WHEN clauses. |
| ps = prepareStatement("values case ? when 1 then 2 when 3 then 4 end"); |
| ParameterMetaData pmd = ps.getParameterMetaData(); |
| assertEquals(Types.INTEGER, pmd.getParameterType(1)); |
| assertEquals(ParameterMetaData.parameterNullable, pmd.isNullable(1)); |
| |
| ps.setInt(1, 1); |
| JDBC.assertSingleValueResultSet(ps.executeQuery(), "2"); |
| |
| ps.setInt(1, 2); |
| JDBC.assertSingleValueResultSet(ps.executeQuery(), null); |
| |
| ps.setInt(1, 3); |
| JDBC.assertSingleValueResultSet(ps.executeQuery(), "4"); |
| |
| ps = prepareStatement( |
| "values case ? when cast(1.1 as double) then true " |
| + "when cast(1.2 as double) then false end"); |
| pmd = ps.getParameterMetaData(); |
| assertEquals(Types.DOUBLE, pmd.getParameterType(1)); |
| assertEquals(ParameterMetaData.parameterNullable, pmd.isNullable(1)); |
| |
| ps.setDouble(1, 1.1); |
| JDBC.assertSingleValueResultSet(ps.executeQuery(), "true"); |
| ps.setDouble(1, 1.2); |
| JDBC.assertSingleValueResultSet(ps.executeQuery(), "false"); |
| ps.setDouble(1, 1.3); |
| JDBC.assertSingleValueResultSet(ps.executeQuery(), null); |
| |
| // Mixed types are accepted, as long as they are compatible. |
| ps = prepareStatement( |
| "values case ? when 1 then 'one' when 2.1 then 'two' end"); |
| pmd = ps.getParameterMetaData(); |
| assertEquals(Types.DECIMAL, pmd.getParameterType(1)); |
| assertEquals(ParameterMetaData.parameterNullable, pmd.isNullable(1)); |
| |
| ps.setInt(1, 1); |
| JDBC.assertSingleValueResultSet(ps.executeQuery(), "one"); |
| ps.setInt(1, 2); |
| JDBC.assertSingleValueResultSet(ps.executeQuery(), null); |
| |
| ps.setDouble(1, 1.1); |
| JDBC.assertSingleValueResultSet(ps.executeQuery(), null); |
| ps.setDouble(1, 2.1); |
| JDBC.assertSingleValueResultSet(ps.executeQuery(), "two"); |
| |
| ps = prepareStatement( |
| "values case ? when 1 then 'one' when 2.1 then 'two'" |
| + " when cast(3 as bigint) then 'three' end"); |
| assertEquals(Types.DECIMAL, pmd.getParameterType(1)); |
| assertEquals(ParameterMetaData.parameterNullable, pmd.isNullable(1)); |
| |
| ps.setInt(1, 1); |
| JDBC.assertSingleValueResultSet(ps.executeQuery(), "one"); |
| ps.setInt(1, 2); |
| JDBC.assertSingleValueResultSet(ps.executeQuery(), null); |
| ps.setInt(1, 3); |
| JDBC.assertSingleValueResultSet(ps.executeQuery(), "three"); |
| |
| ps.setDouble(1, 1.1); |
| JDBC.assertSingleValueResultSet(ps.executeQuery(), null); |
| ps.setDouble(1, 2.1); |
| JDBC.assertSingleValueResultSet(ps.executeQuery(), "two"); |
| ps.setDouble(1, 3.1); |
| JDBC.assertSingleValueResultSet(ps.executeQuery(), null); |
| |
| ps = prepareStatement( |
| "values case ? when 'abcdef' then 1 " |
| + "when cast('abcd' as varchar(4)) then 2 end"); |
| pmd = ps.getParameterMetaData(); |
| assertEquals(Types.VARCHAR, pmd.getParameterType(1)); |
| assertEquals(6, pmd.getPrecision(1)); |
| assertEquals(ParameterMetaData.parameterNullable, pmd.isNullable(1)); |
| |
| ps.setString(1, "abcdef"); |
| JDBC.assertSingleValueResultSet(ps.executeQuery(), "1"); |
| ps.setString(1, "abcd"); |
| JDBC.assertSingleValueResultSet(ps.executeQuery(), "2"); |
| ps.setString(1, "ab"); |
| JDBC.assertSingleValueResultSet(ps.executeQuery(), null); |
| ps.setString(1, "abcdefghi"); |
| JDBC.assertSingleValueResultSet(ps.executeQuery(), null); |
| |
| // The types in the WHEN clauses are incompatible, so the type of |
| // the case operand cannot be inferred. |
| assertCompileError("42818", |
| "values case ? when 1 then true when like 'abc' then false end"); |
| |
| assertCompileError("42818", |
| "values case ? when like 'abc' then true when 1 then false end"); |
| |
| // BLOB and CLOB are not comparable with anything. |
| assertCompileError("42818", |
| "values case ? when cast(x'abcd' as blob) then true end"); |
| assertCompileError("42818", |
| "values case ? when cast('abcd' as clob) then true end"); |
| |
| // Cannot infer type if both sides of the comparison are untyped. |
| assertCompileError("42X35", "values case ? when ? then true end"); |
| assertCompileError("42X35", "values case ? when ? then true " |
| + "when 1 then false end"); |
| |
| // Should be able to infer type when the untyped parameter is prefixed |
| // with plus or minus. |
| ps = prepareStatement( |
| "values (case +? when 1 then 1 when 2.1 then 2 end, " |
| + "case -? when 1 then 1 when 2.1 then 2 end)"); |
| pmd = ps.getParameterMetaData(); |
| assertEquals(Types.DECIMAL, pmd.getParameterType(1)); |
| assertEquals(ParameterMetaData.parameterNullable, pmd.isNullable(1)); |
| assertEquals(Types.DECIMAL, pmd.getParameterType(2)); |
| assertEquals(ParameterMetaData.parameterNullable, pmd.isNullable(2)); |
| |
| ps.setInt(1, 1); |
| ps.setInt(2, -1); |
| JDBC.assertFullResultSet(ps.executeQuery(), |
| new String[][] {{ "1", "1" }}); |
| |
| ps.setInt(1, 2); |
| ps.setInt(2, -2); |
| JDBC.assertFullResultSet(ps.executeQuery(), |
| new String[][] {{ null, null }}); |
| |
| ps.setDouble(1, 1.1); |
| ps.setDouble(2, -1.1); |
| JDBC.assertFullResultSet(ps.executeQuery(), |
| new String[][] {{ null, null }}); |
| |
| ps.setDouble(1, 2.1); |
| ps.setDouble(2, -2.1); |
| JDBC.assertFullResultSet(ps.executeQuery(), |
| new String[][] {{ "2", "2" }}); |
| |
| // If the untyped parameter is part of an arithmetic expression, its |
| // type is inferred from that expression and not from the WHEN clause. |
| ps = prepareStatement( |
| "values case 2*? when 2 then true when 3.0 then false end"); |
| pmd = ps.getParameterMetaData(); |
| assertEquals(Types.INTEGER, pmd.getParameterType(1)); |
| assertEquals(ParameterMetaData.parameterNullable, pmd.isNullable(1)); |
| |
| ps.setInt(1, 1); |
| JDBC.assertSingleValueResultSet(ps.executeQuery(), "true"); |
| ps.setDouble(1, 1.5); |
| JDBC.assertSingleValueResultSet(ps.executeQuery(), "true"); |
| ps.setInt(1, 2); |
| JDBC.assertSingleValueResultSet(ps.executeQuery(), null); |
| |
| ps = prepareStatement( |
| "values case 2.0*? when 2 then true when 3.0 then false end"); |
| pmd = ps.getParameterMetaData(); |
| assertEquals(Types.DECIMAL, pmd.getParameterType(1)); |
| assertEquals(ParameterMetaData.parameterNullable, pmd.isNullable(1)); |
| |
| ps.setInt(1, 1); |
| JDBC.assertSingleValueResultSet(ps.executeQuery(), "true"); |
| ps.setDouble(1, 1.5); |
| JDBC.assertSingleValueResultSet(ps.executeQuery(), "false"); |
| ps.setInt(1, 2); |
| JDBC.assertSingleValueResultSet(ps.executeQuery(), null); |
| |
| // The EXISTS predicate can only be used in the WHEN operand if |
| // the CASE operand is a BOOLEAN. |
| JDBC.assertSingleValueResultSet( |
| s.executeQuery("values case true when exists" + |
| "(select * from sysibm.sysdummy1) then 1 end"), |
| "1"); |
| assertCompileError("42818", |
| "values case 1 when exists" + |
| "(select * from sysibm.sysdummy1) then 1 end"); |
| |
| // Scalar subqueries are allowed in the operands. |
| JDBC.assertSingleValueResultSet( |
| s.executeQuery( |
| "values case (select ibmreqd from sysibm.sysdummy1) " |
| + "when 'N' then 'no' when 'Y' then 'yes' end"), |
| "yes"); |
| JDBC.assertSingleValueResultSet( |
| s.executeQuery("values case 'Y' when " |
| + "(select ibmreqd from sysibm.sysdummy1) " |
| + "then 'yes' end"), |
| "yes"); |
| |
| // Subquery returns two columns - fail. |
| assertCompileError( |
| "42X39", |
| "values case (select ibmreqd, 1 from sysibm.sysdummy1)" |
| + " when 'Y' then true end"); |
| assertCompileError( |
| "42X39", |
| "values case 'Y' when " |
| + "(select ibmreqd, 1 from sysibm.sysdummy1) then true end"); |
| |
| // Subquery returns multiple rows - fail. |
| assertStatementError("21000", s, |
| "values case (select 1 from sys.systables) when 1 then true end"); |
| assertStatementError("21000", s, |
| "values case 1 when (select 1 from sys.systables) then true end"); |
| |
| // Subquery returns zero rows, which is converted to NULL for scalar |
| // subqueries. |
| JDBC.assertSingleValueResultSet(s.executeQuery( |
| "values case (select ibmreqd from sysibm.sysdummy1 where false)" |
| + " when is null then 'yes' end"), |
| "yes"); |
| JDBC.assertSingleValueResultSet(s.executeQuery( |
| "values case true when true then " |
| + "(select ibmreqd from sysibm.sysdummy1 where false) end"), |
| null); |
| |
| // Simple case expressions should work in join conditions. |
| JDBC.assertSingleValueResultSet( |
| s.executeQuery("select x from (values 1, 2, 3) v1(x) " |
| + "join (values 13, 14) v2(y) " |
| + "on case y-x when 10 then true end"), |
| "3"); |
| } |
| |
| /** |
| * Verify that the case operand expression is evaluated only once per |
| * evaluation of the CASE expression. |
| */ |
| public void testSingleEvaluationOfCaseOperand() throws SQLException { |
| setAutoCommit(false); |
| Statement s = createStatement(); |
| |
| s.execute("create function count_me(x int) returns int " |
| + "language java parameter style java external name '" |
| + getClass().getName() + ".countMe' no sql deterministic"); |
| |
| callCount.set(0); |
| |
| JDBC.assertUnorderedResultSet( |
| s.executeQuery( |
| "select case count_me(x) when 1 then 'one' when 2 then 'two' " |
| + "when 3 then 'three' end from (values 1, 2, 3) v(x)"), |
| new String[][] { {"one"}, {"two"}, {"three"} }); |
| |
| // The CASE expression is evaluated once per row. There are three |
| // rows. Expect that the COUNT_ME function was only invoked once |
| // per row. |
| assertEquals(3, callCount.get()); |
| } |
| |
| /** Count how many times countMe() has been called. */ |
| private static final AtomicInteger callCount = new AtomicInteger(); |
| |
| /** |
| * Stored function that keeps track of how many times it has been called. |
| * @param i an integer |
| * @return the integer {@code i} |
| */ |
| public static int countMe(int i) { |
| callCount.incrementAndGet(); |
| return i; |
| } |
| |
| /** |
| * Test that large objects can be used as case operands. |
| */ |
| public void testLobAsCaseOperand() throws SQLException { |
| Statement s = createStatement(); |
| |
| // BLOB and CLOB are allowed in the case operand. |
| JDBC.assertSingleValueResultSet(s.executeQuery( |
| "values case cast(null as blob) when is null then 'yes' end"), |
| "yes"); |
| JDBC.assertSingleValueResultSet(s.executeQuery( |
| "values case cast(null as clob) when is null then 'yes' end"), |
| "yes"); |
| |
| // Comparisons between BLOB and BLOB, or between CLOB and CLOB, are |
| // not allowed, so expect a compile-time error for these queries. |
| assertCompileError("42818", |
| "values case cast(null as blob) " |
| + "when cast(null as blob) then true end"); |
| assertCompileError("42818", |
| "values case cast(null as clob) " |
| + "when cast(null as clob) then true end"); |
| |
| // Now create a table with some actual LOBs in them. |
| s.execute("create table lobs_for_simple_case(" |
| + "id int generated always as identity, b blob, c clob)"); |
| |
| PreparedStatement insert = prepareStatement( |
| "insert into lobs_for_simple_case(b, c) values (?, ?)"); |
| |
| // A small one. |
| insert.setBytes(1, new byte[] {1, 2, 3}); |
| insert.setString(2, "small"); |
| insert.executeUpdate(); |
| |
| // And a big one (larger than 32K means it will be streamed |
| // from store, instead of being returned as a materialized value). |
| insert.setBinaryStream(1, new LoopingAlphabetStream(40000)); |
| insert.setCharacterStream(2, new LoopingAlphabetReader(40000)); |
| insert.executeUpdate(); |
| |
| // And a NULL. |
| insert.setNull(1, Types.BLOB); |
| insert.setNull(2, Types.CLOB); |
| insert.executeUpdate(); |
| |
| // IS [NOT] NULL can be used on both BLOB and CLOB. LIKE can be |
| // used on CLOB. Those are the only predicates supported on BLOB |
| // and CLOB in simple case expressions currently. Test that they |
| // all work. |
| JDBC.assertUnorderedResultSet( |
| s.executeQuery( |
| "select id, case b when is null then 'yes'" |
| + " when is not null then 'no' end, " |
| + "case c when is null then 'yes' when like 'abc' then 'abc'" |
| + " when like 'abc%' then 'abc...' when is not null then 'no'" |
| + " end " |
| + "from lobs_for_simple_case"), |
| new String[][] { |
| { "1", "no", "no" }, |
| { "2", "no", "abc..." }, |
| { "3", "yes", "yes" }, |
| }); |
| |
| s.execute("drop table lobs_for_simple_case"); |
| } |
| } |
