[CALCITE-666] Anti-semi-joins against JDBC adapter give wrong results

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.2.0-incubating
• Fix Version/s: 1.5.0
• Assignee: Julian Hyde
• Reporter: Yuri Au Yong
• Created: 06/Apr/15 03:30
• Updated: 27/Feb/24 22:24
• Resolved: 16/Oct/15 18:22

Description

When using JdbcAdapter to execute a query with a WHERE clause wherein the predicate using the <> operator contains a subquery, e.g.:

select * from table1 where c1 <> (select c1 from table2 where rnum =0);

The query is split to two (outer - "select from *
from table1" and inner -"select col2 from table2 where col1 =0") Enumerable objects and then combined with the org.apache.calcite.runtime.Enumerables semiJoin method.

The returned resultset however, is equivalent to the result of executing "select * from table1" and the WHERE clause condition ignored.

Test:
package org.apache.calcite.test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import org.apache.calcite.jdbc.Driver;

public class TestSubqueryInPredicate {

public static void main(String[] args) {
try

{ //testScottdb(); testPredicateSubquery(); }

catch (Exception e)

{ e.printStackTrace(); }

}

public static void testPredicateSubquery() throws SQLException {
String h2dbMemUrl = "jdbc:hsqldb:mem:.";
Connection baseConnection = DriverManager.getConnection(h2dbMemUrl);
Statement statement = baseConnection.createStatement();

statement.execute("CREATE TABLE if not exists table1 (\n"
+ "RNUM INTEGER,\n"
+ "C1 INTEGER,\n"
+ "C2 VARCHAR(2))");

statement.execute("insert into table1 values ( 0, 10, 'BB')");
statement.execute("insert into table1 values ( 1, 15, 'DD')");
statement.execute("insert into table1 values ( 2, NULL, 'EE')");
statement.execute("insert into table1 values ( 3, 10, 'FF')");

statement.execute("CREATE TABLE if not exists table2 (\n"
+ "RNUM INTEGER,\n"
+ "C1 INTEGER,\n"
+ "C2 INTEGER)");

statement.execute("insert into table2 values ( 0, 10, 15)");
statement.execute("insert into table2 values ( 1, 20, 25)");
statement.execute("insert into table2 values ( 2, NULL, 50)");

statement.close();
baseConnection.commit();

Properties info = new Properties();
info.put("model",
"inline:"
+ "{\n"
+ " version: '1.0',\n"
+ " defaultSchema: 'BASEJDBC',\n"
+ " schemas: [\n"
+ "

{\n" + " type: 'jdbc',\n" + " name: 'BASEJDBC',\n" + " jdbcDriver: '" + Driver.class.getName() + "',\n" + " jdbcUrl: '" + h2dbMemUrl + "',\n" + " jdbcCatalog: null,\n" + " jdbcSchema: null\n" + " }

\n"
+ " ]\n"
+ "}");

Connection calciteConnection = DriverManager.getConnection(
"jdbc:calcite:", info);

System.out.println ("select * from table1 where c1 <> (select c1 from table2 > where rnum =0)");
PreparedStatement calcitePS = calciteConnection.prepareStatement(
"select * from table1 where c1 <> (select c1 from table2 where rnum =0)");
ResultSet rs = calcitePS.executeQuery();

while (rs.next())

{ System.out.print ("\n" + "| RNUM:" + (Integer) rs.getObject("RNUM") + " | "); System.out.print ("C1:" + (Integer) rs.getObject("C1") + " | "); System.out.print ("C2:" + rs.getString("C2") + " | "); }

rs.close();
calciteConnection.close();
}
}

Test Result:
select * from table1 where c1 <> (select c1 from table2 > where rnum =0)

RNUM:0	C1:10	C2:BB
RNUM:1	C1:15	C2:DD
RNUM:2	C1:null	C2:EE
RNUM:3	C1:10	C2:FF

Generated Code:
[SELECT *
FROM "TABLE1"]
[SELECT "C1"
FROM "TABLE2"
WHERE "RNUM" = 0]

/* 1 */ org.apache.calcite.DataContext root;
/* 2 */
/* 3 */ public org.apache.calcite.linq4j.Enumerable bind(final org.apache.calcite.DataContext root0) {
/* 4 */ root = root0;
/* 5 */ return org.apache.calcite.runtime.Enumerables.semiJoin(org.apache.calcite.runtime.ResultSetEnumerable.of(((org.apache.calcite.adapter.jdbc.JdbcSchema) root.getRootSchema().getSubSchema("BASEJDBC").unwrap(org.apache.calcite.adapter.jdbc.JdbcSchema.class)).getDataSource(), "SELECT *\nFROM \"TABLE1\"", new org.apache.calcite.linq4j.function.Function1() {
/* 6 */ public org.apache.calcite.linq4j.function.Function0 apply(final java.sql.ResultSet resultSet) {
/* 7 */ return new org.apache.calcite.linq4j.function.Function0() {
/* 8 */ public Object apply() {
/* 9 */ try {
/* 10 */ final Object[] values = new Object[3];
/* 11 */ values[0] = resultSet.getInt(1);
/* 12 */ if (resultSet.wasNull())

{ /* 13 */ values[0] = null; /* 14 */ }

/* 15 */ values[1] = resultSet.getInt(2);
/* 16 */ if (resultSet.wasNull())

{ /* 17 */ values[1] = null; /* 18 */ }

/* 19 */ values[2] = resultSet.getObject(3);
/* 20 */ return values;
/* 21 */ } catch (java.sql.SQLException e)

{ /* 22 */ throw new RuntimeException( /* 23 */ e); /* 24 */ }

/* 25 */ }
/* 26 */ }
/* 27 */ ;
/* 28 */ }
/* 29 */ public Object apply(final Object resultSet)

{ /* 30 */ return apply( /* 31 */ (java.sql.ResultSet) resultSet); /* 32 */ }

/* 33 */ }
/* 34 */ ), org.apache.calcite.runtime.ResultSetEnumerable.of(((org.apache.calcite.adapter.jdbc.JdbcSchema) root.getRootSchema().getSubSchema("BASEJDBC").unwrap(org.apache.calcite.adapter.jdbc.JdbcSchema.class)).getDataSource(), "SELECT \"C1\"\nFROM \"TABLE2\"\nWHERE \"RNUM\" = 0", new org.apache.calcite.linq4j.function.Function1() {
/* 35 */ public org.apache.calcite.linq4j.function.Function0 apply(final java.sql.ResultSet resultSet) {
/* 36 */ return new org.apache.calcite.linq4j.function.Function0() {
/* 37 */ public Object apply() {
/* 38 */ try {
/* 39 */ final Object value;
/* 40 */ value = resultSet.getInt(1);
/* 41 */ if (resultSet.wasNull())

{ /* 42 */ value = null; /* 43 */ }

/* 44 */ return value;
/* 45 */ } catch (java.sql.SQLException e)

{ /* 46 */ throw new RuntimeException( /* 47 */ e); /* 48 */ }

/* 49 */ }
/* 50 */ }
/* 51 */ ;
/* 52 */ }
/* 53 */ public Object apply(final Object resultSet)

{ /* 54 */ return apply( /* 55 */ (java.sql.ResultSet) resultSet); /* 56 */ }

/* 57 */ }
/* 58 */ ), new org.apache.calcite.linq4j.function.Function1() {
/* 59 */ public org.apache.calcite.runtime.FlatLists.ComparableEmptyList apply(Object[] v1)

{ /* 60 */ return org.apache.calcite.runtime.FlatLists.COMPARABLE_EMPTY_LIST; /* 61 */ }

/* 62 */ public Object apply(Object v1)

{ /* 63 */ return apply( /* 64 */ (Object[]) v1); /* 65 */ }

/* 66 */ }
/* 67 */ , new org.apache.calcite.linq4j.function.Function1() {
/* 68 */ public org.apache.calcite.runtime.FlatLists.ComparableEmptyList apply(Integer v1)

{ /* 69 */ return org.apache.calcite.runtime.FlatLists.COMPARABLE_EMPTY_LIST; /* 70 */ }

/* 71 */ public Object apply(Object v1)

{ /* 72 */ return apply( /* 73 */ (Integer) v1); /* 74 */ }

/* 75 */ }
/* 76 */ );
/* 77 */ }
/* 78 */
/* 79 */
/* 80 */ public Class getElementType()

{ /* 81 */ return java.lang.Object[].class; /* 82 */ }

/* 83 */
/* 84 */