| /* |
| |
| Derby - Class org.apache.derbyTesting.functionTests.tests.lang.ExistsWithSubqueriesTest |
| |
| 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.PreparedStatement; |
| import java.sql.ResultSet; |
| import java.sql.SQLException; |
| import java.sql.Statement; |
| import junit.framework.Test; |
| 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.TestConfiguration; |
| |
| /** |
| * This test contains a variety of cases of EXISTS predicates with subqueries. |
| * |
| * Several tests ensure that an EXISTS predicate which wraps a set operation-- |
| * meaning a UNION, INTERSECT, or EXCEPT node--returns the correct results. |
| * For example: |
| * |
| * select * from ( values 'BAD' ) as T |
| * where exists ((values 1) intersect (values 2)) |
| * |
| * should return zero rows. Prompted by DERBY-2370. |
| * |
| * A somewhat unrelated test verifies the DERBY-3033 behavior, which |
| * involves flattening of subqueries with NOT EXISTS predicates. The |
| * issue here is that a flattened NOT EXISTS subquery cannot be used |
| * to perform equi-join transitive closure, because the implied predicate |
| * that results from the flattening is a NOT EQUALS condition. |
| */ |
| public class ExistsWithSubqueriesTest extends BaseJDBCTestCase { |
| |
| private static final String EXISTS_PREFIX_1 = |
| "select * from ( values 'GOT_A_ROW' ) as T where exists ("; |
| |
| private static final String EXISTS_PREFIX_2 = |
| "select j from onerow where exists ("; |
| |
| /** |
| * Create a test with the given name. |
| * @param name name of the test. |
| * |
| */ |
| public ExistsWithSubqueriesTest(String name) |
| { |
| super(name); |
| } |
| |
| /** |
| * Return suite with all tests of the class. |
| */ |
| public static Test suite() |
| { |
| BaseTestSuite suite = new BaseTestSuite("EXISTS with SET operations"); |
| |
| /* This is a language/optimization test so behavior will be the |
| * same for embedded and client/server. Therefore we only need |
| * to run the test against one or the other; we choose embedded. |
| */ |
| suite.addTest( |
| TestConfiguration.embeddedSuite(ExistsWithSubqueriesTest.class)); |
| |
| /* Wrap the suite in a CleanDatabaseTestSetup that will create |
| * and populate the test tables. |
| */ |
| return new CleanDatabaseTestSetup(suite) |
| { |
| /** |
| * Create and populate the test table. |
| */ |
| protected void decorateSQL(Statement s) throws SQLException |
| { |
| s.executeUpdate("create table empty (i int)"); |
| s.executeUpdate("create table onerow (j int)"); |
| s.executeUpdate("insert into onerow values 2"); |
| s.executeUpdate("create table diffrow (k int)"); |
| s.executeUpdate("insert into diffrow values 4"); |
| s.executeUpdate("create table tworows (p int)"); |
| s.executeUpdate("insert into tworows values 2, 4"); |
| s.executeUpdate("create table onerow2col (j1 int, j2 int)"); |
| s.executeUpdate("insert into onerow2col values (2, 2)"); |
| } |
| }; |
| } |
| |
| /** |
| * Test queries where the set operation just involves VALUES |
| * expressions. |
| */ |
| public void testSetOpsWithVALUES() throws Exception |
| { |
| Statement st = createStatement(); |
| String [][] expRS = new String [1][1]; |
| |
| expRS[0][0] = "GOT_A_ROW"; |
| checkQuery(st, expRS, EXISTS_PREFIX_1 + "values 1 union values 1)"); |
| checkQuery(st, expRS, EXISTS_PREFIX_1 + "values 1 intersect values 1)"); |
| checkQuery(st, expRS, EXISTS_PREFIX_1 + "values 1 except values 0)"); |
| |
| checkQuery(st, null, EXISTS_PREFIX_1 + "values 1 intersect values 0)"); |
| checkQuery(st, null, EXISTS_PREFIX_1 + "values 1 except values 1)"); |
| st.close(); |
| } |
| |
| /** |
| * Test queries where the set operation has subqueries which are not |
| * correlated to the outer query. It's important to check for cases |
| * where we have explicit columns _and_ cases where we have "*" because |
| * the binding codepaths differ and we want to verify both. |
| */ |
| public void testNonCorrelatedSetOps() throws Exception |
| { |
| Statement st = createStatement(); |
| String [][] expRS = new String [1][1]; |
| |
| expRS[0][0] = "GOT_A_ROW"; |
| |
| // Expect 1 row for the following. |
| |
| // Simple UNION with "*". |
| checkQuery(st, expRS, EXISTS_PREFIX_1 + |
| "select * from diffrow union select * from onerow)"); |
| |
| // Simple UNION with explicit columns. |
| checkQuery(st, expRS, EXISTS_PREFIX_1 + |
| "select k from diffrow union select j from onerow)"); |
| |
| // Simple INTERSECT with "*". |
| checkQuery(st, expRS, EXISTS_PREFIX_1 + |
| "select * from diffrow intersect select 4 from onerow)"); |
| |
| // Simple INTERSECT with explicit columns. |
| checkQuery(st, expRS, EXISTS_PREFIX_1 + |
| "select k from diffrow intersect select 4 from onerow)"); |
| |
| // Simple EXCEPT with "*". |
| checkQuery(st, expRS, EXISTS_PREFIX_1 + |
| "select * from diffrow except select * from onerow)"); |
| |
| // Simple EXCEPT with explicit columns. |
| checkQuery(st, expRS, EXISTS_PREFIX_1 + |
| "select k from diffrow except select j from onerow)"); |
| |
| // EXCEPT with "*" where left and right children have their |
| // own preds. |
| checkQuery(st, expRS, EXISTS_PREFIX_1 + |
| "(select * from tworows where p = 2) except " + |
| "(select * from tworows where p <> 2))"); |
| |
| // INTERSECT with "*" where left and right children have their |
| // own preds. |
| checkQuery(st, expRS, EXISTS_PREFIX_1 + |
| "(select * from tworows where p = 2) intersect " + |
| "(select * from tworows where p = 2))"); |
| |
| // Expect 0 rows for the following. Similar queries to |
| // above except modified to return no rows. |
| |
| checkQuery(st, null, EXISTS_PREFIX_1 + |
| "select i from empty union select * from empty)"); |
| |
| checkQuery(st, null, EXISTS_PREFIX_1 + |
| "select * from onerow intersect select * from empty)"); |
| |
| checkQuery(st, null, EXISTS_PREFIX_1 + |
| "select j from onerow intersect select i from empty)"); |
| |
| checkQuery(st, null, EXISTS_PREFIX_1 + |
| "select * from empty except select * from onerow)"); |
| |
| checkQuery(st, null, EXISTS_PREFIX_1 + |
| "select i from empty except select j from onerow)"); |
| |
| checkQuery(st, null, EXISTS_PREFIX_1 + |
| "select * from onerow intersect select * from diffrow)"); |
| |
| checkQuery(st, null, EXISTS_PREFIX_1 + |
| "select j from onerow intersect select k from diffrow)"); |
| |
| checkQuery(st, null, EXISTS_PREFIX_1 + |
| "select * from onerow except select * from onerow)"); |
| |
| checkQuery(st, null, EXISTS_PREFIX_1 + |
| "select j from onerow except select j from onerow)"); |
| |
| checkQuery(st, null, EXISTS_PREFIX_1 + |
| "(select * from tworows where p = 2) intersect " + |
| "(select * from tworows where p <> 2))"); |
| |
| checkQuery(st, null, EXISTS_PREFIX_1 + |
| "(select * from tworows where p = 2) except " + |
| "(select * from tworows where p = 2))"); |
| |
| // Negative cases. These should fail because "oops" is not |
| // a valid column in ONEROW. |
| |
| assertCompileError("42X04", EXISTS_PREFIX_1 + |
| "(select * from onerow where j = 2) intersect " + |
| "(select oops from onerow where j <> 2))"); |
| |
| assertCompileError("42X04", EXISTS_PREFIX_1 + |
| "(select * from onerow where j = 2) intersect " + |
| "(select * from onerow where oops <> 2))"); |
| |
| st.close(); |
| } |
| |
| /** |
| * Test queries where the set operation has subqueries which are |
| * correlated to the outer query. Subqueries should still be able |
| * reference the outer query table and execute without error. |
| */ |
| public void testCorrelatedSetOps() throws Exception |
| { |
| Statement st = createStatement(); |
| String [][] expRS = new String [1][1]; |
| |
| // "2" here is the value that was inserted into "onerow". |
| expRS[0][0] = "2"; |
| |
| // Expect 1 row for the following. |
| |
| // Right child of UNION has "*" for RCL and references table |
| // from outer query. |
| checkQuery(st, expRS, EXISTS_PREFIX_2 + |
| "select 1 from diffrow where 1 = 1 union " + |
| "select * from diffrow where onerow.j < k)"); |
| |
| // Right child of UNION has qualified "*" for RCL and references |
| // table from outer query. |
| checkQuery(st, expRS, EXISTS_PREFIX_2 + |
| "select 1 from diffrow where 1 = 1 union " + |
| "select diffrow.* from diffrow where onerow.j < k)"); |
| |
| // Right child of UNION has explicit RCL and references |
| // table from outer query. |
| checkQuery(st, expRS, EXISTS_PREFIX_2 + |
| "select 1 from diffrow where 1 = 1 union " + |
| "select k from diffrow where onerow.j < k)"); |
| |
| /* Right child of UNION is itself another EXISTS query whose |
| * child is another set operator (INTERSECT). The INTERSECT in |
| * turn has a right child which references a table from the |
| * outer-most query. |
| */ |
| checkQuery(st, expRS, EXISTS_PREFIX_2 + |
| "select 1 from diffrow where 1 = 0 union " + |
| "select * from diffrow where exists " + |
| " (select 2 from diffrow intersect " + |
| " select 2 from diffrow where onerow.j < k))"); |
| |
| /* Right child of UNION is itself another EXISTS query whose |
| * child is another set operator (INTERSECT). The INTERSECT in |
| * turn has a right child which references a table from the |
| * outer-most query. In this one the INTERSECT returns zero |
| * rows. |
| */ |
| checkQuery(st, expRS, EXISTS_PREFIX_2 + |
| "select 1 from diffrow where 1 = 1 union " + |
| "select * from diffrow where exists " + |
| " (select 2 from empty intersect " + |
| " select 3 from empty where onerow.j < i))"); |
| |
| /* Right child of UNION is itself another EXISTS query whose |
| * child is another set operator (INTERSECT). The INTERSECT in |
| * turn has a right child which references 1) a table from the |
| * outer-most query, and 2) a table from the INTERSECT node's |
| * "parent" subquery (i.e. from the UNION's right subquery). |
| */ |
| checkQuery(st, expRS, EXISTS_PREFIX_2 + |
| "select 1 from diffrow where 1 = 1 union " + |
| "select * from diffrow where exists " + |
| " (select 2 from onerow2col intersect " + |
| " select 3 from empty where onerow.j < diffrow.k))"); |
| |
| /* Right child of UNION is itself another EXISTS query whose |
| * child is another set operator (INTERSECT). The INTERSECT in |
| * turn has a right child which references 1) a table from the |
| * outer-most query, and 2) a table from the INTERSECT node's |
| * "parent" query. In addition, add another predicate to the |
| * UNION's right subquery and make that predicate reference |
| * both 1) a table from the outer-most query, and 2) a table |
| * in the subquery's own FROM list. All of this to ensure |
| * that binding finds the correct columns at all levels of |
| * the query. |
| */ |
| checkQuery(st, expRS, EXISTS_PREFIX_2 + |
| "select 1 from diffrow where 1 = 1 union " + |
| "select * from diffrow where exists " + |
| " (select 2 from onerow2col intersect " + |
| " select 3 from empty where onerow.j < k) " + |
| " and (onerow.j < diffrow.k))"); |
| |
| // Expect 0 rows for the following. Similar queries to |
| // above except modified to return no rows. |
| |
| checkQuery(st, null, EXISTS_PREFIX_2 + |
| "select 1 from diffrow where 1 = 0 union " + |
| "select * from diffrow where onerow.j > k)"); |
| |
| checkQuery(st, null, EXISTS_PREFIX_2 + |
| "select 1 from diffrow where 1 = 0 union " + |
| "select * from diffrow where exists " + |
| " (select 2 from diffrow intersect " + |
| " select 3 from diffrow where onerow.j < k))"); |
| |
| checkQuery(st, null, EXISTS_PREFIX_2 + |
| "select 1 from diffrow where 1 = 0 union " + |
| "select * from diffrow where exists " + |
| " (select 2 from empty intersect " + |
| " select 3 from empty where onerow.j < i))"); |
| |
| checkQuery(st, null, EXISTS_PREFIX_2 + |
| "select 1 from diffrow where 1 = 0 union " + |
| "select * from diffrow where exists " + |
| " (select 2 from onerow2col intersect " + |
| " select 3 from empty where onerow.j < diffrow.k))"); |
| |
| checkQuery(st, null, EXISTS_PREFIX_2 + |
| "select 1 from diffrow where 1 = 0 union " + |
| "select * from diffrow where exists " + |
| " (select 2 from onerow2col intersect " + |
| " select 3 from empty where onerow.j < k) " + |
| " and (onerow.j < diffrow.k))"); |
| |
| // Negative cases. |
| |
| // Should fail because left and right children of the UNION |
| // have different RCL sizes. (NOTE: Would have passed prior |
| // to DERBY-2370, but that was incorrect). |
| assertCompileError("42X58", EXISTS_PREFIX_2 + |
| "select 1 from diffrow where 1 = 0 union " + |
| "select * from onerow2col where onerow.j < j)"); |
| |
| /* Should fail because there is an explicit subquery ("SELECT *") |
| * within the EXISTS query and such a subquery is not allowed to |
| * reference outer tables. So we will be unable to find the |
| * column "onerow.j" in this case. |
| */ |
| assertCompileError("42X04", EXISTS_PREFIX_2 + |
| "select * from (select 1 from diffrow where 1 = 0 " + |
| "union select * from diffrow where onerow.j < k) x)"); |
| |
| /* Should fail because the UNION's right subquery is trying to |
| * select from an outer table. While the subquery is allowed |
| * to reference the outer table in expressions, it cannot |
| * include the outer table in its RCL. |
| */ |
| assertCompileError("42X10", EXISTS_PREFIX_2 + |
| "select 1 from diffrow where 1 = 1 union " + |
| "select onerow.* from diffrow where onerow.j < k)"); |
| |
| st.close(); |
| } |
| |
| /** |
| * Simple helper method to assert the results of the received |
| * query. If the array representing expected results is null |
| * then we assert that the query returns no rows. |
| */ |
| private void checkQuery(Statement st, String [][] expRS, |
| String query) throws Exception |
| { |
| ResultSet rs = st.executeQuery(query); |
| if (expRS == null) |
| JDBC.assertEmpty(rs); |
| else |
| JDBC.assertFullResultSet(rs, expRS); |
| rs.close(); |
| } |
| |
| /** |
| * Regression test for Derby-3033. |
| * |
| * This method constructs a query with the property that it: |
| * - contains a NOT EXISTS condition against a correlated subquery |
| * - such that if that subquery is flattened, the result is 3 tables |
| * which all have join predicates on the same key. |
| * The point of the test is that it is *not* correct to construct |
| * a new equijoin predicate between table d3033_a and d3033_c via |
| * transitive closure, because the join condition between d3033_b and |
| * d3033_c is NOT EXISTS. |
| * |
| * In the original bug, the compiler/optimizer erroneously generated |
| * the extra equijoin predicate, which caused NPE exceptions at |
| * runtime due to attempts to reference the non-existent (NOT EXISTS) row. |
| * |
| * So this test succeeds if it gets the right results and no NPE. |
| */ |
| public void testDerby3033() |
| throws Exception |
| { |
| setupDerby3033(); |
| |
| PreparedStatement pstmt = prepareStatement( |
| "select c1, c2_b " + |
| "from (select distinct st.c1,st.c2_b,dsr.c3_a,st.c3_b " + |
| " from " + |
| " d3033_a dsr, " + // Table order matters here! |
| " d3033_b st " + |
| " where dsr.c4_a is null " + |
| " and dsr.c2 = ? " + |
| " and dsr.c1 = st.c1 " + |
| " and not exists ( " + |
| " select 1 " + |
| " from d3033_c " + |
| " where d3033_c.c1 = st.c1 " + |
| " and d3033_c.c2 = ? " + |
| " and d3033_c.c3_c = ? " + |
| " ) " + |
| ") temp " |
| ); |
| |
| pstmt.setInt(1, 4); |
| pstmt.setInt(2, 4); |
| pstmt.setInt(3, 100); |
| |
| String [][]expected = { |
| { "1", "100" }, |
| { "2", "200" }, |
| { "3", "300" }, |
| }; |
| ResultSet rs = pstmt.executeQuery(); |
| JDBC.assertFullResultSet(rs, expected); |
| pstmt.close(); |
| } |
| |
| /** |
| * Ensure that the #rows statistics are updated |
| */ |
| private void updateStats(Statement st, String tName) |
| throws Exception |
| { |
| ResultSet rs = st.executeQuery("select * from " + tName); |
| int numRows = 0; |
| while (rs.next()) |
| numRows ++; |
| rs.close(); |
| } |
| |
| private void setupDerby3033() |
| throws Exception |
| { |
| // The pattern of inserting the data is fairly important, as we |
| // are going to do a combination of joins between the three tables |
| // and we want both matching data and non-matching data. We load: |
| // |
| // d3033_a d3033_b d3033_c |
| // -------- -------- -------- |
| // 1 1 1 |
| // 2 2 3 |
| // 3 3 |
| // 4 |
| // |
| // We also load a whole pile of irrelevant data into tables a and c |
| // so that the index becomes relevant in the optimizer's analysis, |
| // then we create some constraints and indexes and delete the rows |
| // from table d3033_c (the NOT EXISTS table). |
| // |
| Statement s = createStatement(); |
| |
| s.executeUpdate("create table d3033_a "+ |
| "(c1 int, c2 int, c3_a int, c4_a date)"); |
| s.executeUpdate("create table d3033_b "+ |
| "(c1 int primary key not null, c2_b int, c3_b date)"); |
| s.executeUpdate("create table d3033_c (c1 int, c2 int, c3_c int)"); |
| s.executeUpdate("insert into d3033_a (c1,c2,c3_a) values(1, 4, 10)"); |
| s.executeUpdate("insert into d3033_a (c1,c2,c3_a) values(2, 4, 20)"); |
| s.executeUpdate("insert into d3033_a (c1,c2,c3_a) values(3, 4, 30)"); |
| s.executeUpdate("insert into d3033_b values(1, 100, CURRENT_DATE)"); |
| s.executeUpdate("insert into d3033_b values(2, 200, CURRENT_DATE)"); |
| s.executeUpdate("insert into d3033_b values(3, 300, CURRENT_DATE)"); |
| s.executeUpdate("insert into d3033_b values(4, 400, CURRENT_DATE)"); |
| s.executeUpdate("insert into d3033_c values(1, 4, 100)"); |
| s.executeUpdate("insert into d3033_c values(3, 4, 100)"); |
| |
| PreparedStatement pstmt2 = prepareStatement( |
| "insert into d3033_a (c1, c2, c3_a) values (?,?,?)"); |
| |
| PreparedStatement pstmt = prepareStatement( |
| "insert into d3033_b (c1, c2_b, c3_b) values (?,?,?)"); |
| |
| java.util.Date now = new java.util.Date(); |
| java.sql.Timestamp nowTS = new java.sql.Timestamp(now.getTime()); |
| for (int i = 0; i < 15; i++) |
| { |
| pstmt.setInt(1, 100+i); |
| pstmt.setInt(2, 100+i); |
| pstmt.setTimestamp(3, nowTS); |
| pstmt.executeUpdate(); |
| |
| for (int j = 0; j < 200; j++) |
| { |
| pstmt2.setInt(1, 1000+j); |
| pstmt2.setInt(2, 100+i); // note "i" here (FK) |
| pstmt2.setInt(3, 1000 + (j+1)*10); |
| pstmt2.executeUpdate(); |
| } |
| } |
| |
| |
| s.executeUpdate("alter table d3033_a add constraint " + |
| "d3033_a_fk foreign key (c2) references d3033_b(c1) " + |
| "on delete cascade on update no action"); |
| |
| s.executeUpdate("alter table d3033_c add constraint " + |
| "d3033_c_fk foreign key (c1) references d3033_b(c1) " + |
| "on delete cascade on update no action"); |
| |
| s.executeUpdate("delete from d3033_c"); |
| |
| // Update the statistics on the 3 tables: |
| updateStats(s, "d3033_a"); |
| updateStats(s, "d3033_b"); |
| updateStats(s, "d3033_c"); |
| |
| s.close(); |
| } |
| |
| /** |
| * Some EXISTS subqueries (and IN subqueries transformed to EXISTS) |
| * returned NULL instead of TRUE or FALSE before DERBY-6408. This test |
| * case verifies the fix. |
| */ |
| public void testDerby6408() throws SQLException { |
| setAutoCommit(false); |
| Statement s = createStatement(); |
| |
| // This statement used to return only NULLs. |
| JDBC.assertFullResultSet( |
| s.executeQuery("values (exists(select * from empty), " |
| + "not exists (select * from empty), " |
| + "not (exists (select * from empty)), " |
| + "not (not exists (select * from empty)))"), |
| new String[][] {{"false", "true", "true", "false" }}); |
| |
| // This similar statement worked even before the fix. |
| JDBC.assertFullResultSet( |
| s.executeQuery("values (exists(select * from onerow), " |
| + "not exists (select * from onerow), " |
| + "not (exists (select * from onerow)), " |
| + "not (not exists (select * from onerow)))"), |
| new String[][] {{"true", "false", "false", "true" }}); |
| |
| // Now put the same expressions in the SELECT list. Used to return |
| // only NULLs. |
| JDBC.assertFullResultSet( |
| s.executeQuery("select exists(select * from empty), " |
| + "not exists (select * from empty), " |
| + "not (exists (select * from empty)), " |
| + "not (not exists (select * from empty)) from onerow"), |
| new String[][] {{"false", "true", "true", "false" }}); |
| |
| // Check the returned value when used in a WHERE predicate. All of |
| // these queries returned one row, but they should return no rows |
| // because EXISTS shouldn't return null. |
| JDBC.assertEmpty(s.executeQuery("select * from onerow " |
| + "where (exists (select * from empty)) is null")); |
| JDBC.assertEmpty(s.executeQuery("select * from onerow " |
| + "where (not exists (select * from empty)) is null")); |
| JDBC.assertEmpty(s.executeQuery("select * from onerow " |
| + "where (not (not exists (select * from empty))) is null")); |
| |
| // The results were correct even before the fix if the subquery |
| // wasn't empty. Verify that they still are. |
| JDBC.assertEmpty(s.executeQuery("select * from onerow " |
| + "where (exists (select * from onerow)) is null")); |
| JDBC.assertEmpty(s.executeQuery("select * from onerow " |
| + "where (not exists (select * from onerow)) is null")); |
| JDBC.assertEmpty(s.executeQuery("select * from onerow " |
| + "where (not (not exists (select * from onerow))) is null")); |
| |
| // Similar problems were seen in IN subqueries that were rewritten |
| // to EXISTS subqueries internally. For example, this query used |
| // to return NULL. |
| JDBC.assertSingleValueResultSet( |
| s.executeQuery("values 1 in (select j from onerow)"), "false"); |
| |
| // If it should evaluate to TRUE, it worked even before the fix. |
| JDBC.assertSingleValueResultSet( |
| s.executeQuery("values 2 in (select j from onerow)"), "true"); |
| |
| // DERBY-6409: Quantified comparisons can also be rewritten to EXISTS, |
| // and these two queries returned wrong results before the fix. |
| JDBC.assertSingleValueResultSet( |
| s.executeQuery("values 1 > all (select 2 from tworows)"), "false"); |
| JDBC.assertSingleValueResultSet( |
| s.executeQuery("values 1 < all (select 2 from tworows)"), "true"); |
| |
| // Verify that EXISTS works in INSERT and UPDATE. |
| s.execute("create table d6408(id int generated by default as identity," |
| + " b boolean not null)"); |
| |
| // This used to fail with |
| // ERROR 23502: Column 'B' cannot accept a NULL value. |
| s.execute("insert into d6408(b) values exists (select * from empty), " |
| + "not exists (select * from empty), " |
| + "exists (select * from onerow), " |
| + "not exists (select * from onerow)"); |
| |
| JDBC.assertFullResultSet( |
| s.executeQuery("select b from d6408 order by id"), |
| new String[][] {{"false"}, {"true"}, {"true"}, {"false"}}); |
| |
| // These used to fail with |
| // ERROR 23502: Column 'B' cannot accept a NULL value. |
| s.execute("update d6408 set b = exists (select * from empty)"); |
| JDBC.assertSingleValueResultSet( |
| s.executeQuery("select distinct b from d6408"), "false"); |
| s.execute("update d6408 set b = not exists (select * from empty)"); |
| JDBC.assertSingleValueResultSet( |
| s.executeQuery("select distinct b from d6408"), "true"); |
| |
| // These passed even before the fix. |
| s.execute("update d6408 set b = exists (select * from onerow)"); |
| JDBC.assertSingleValueResultSet( |
| s.executeQuery("select distinct b from d6408"), "true"); |
| s.execute("update d6408 set b = not exists (select * from onerow)"); |
| JDBC.assertSingleValueResultSet( |
| s.executeQuery("select distinct b from d6408"), "false"); |
| } |
| } |
