You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "Yuri Au Yong (JIRA)" <ji...@apache.org> on 2015/04/06 05:31:12 UTC
[jira] [Created] (CALCITE-666) ANTI-semijoins implemented using
EnumerableSemiJoin
Yuri Au Yong created CALCITE-666:
------------------------------------
Summary: ANTI-semijoins implemented using EnumerableSemiJoin
Key: CALCITE-666
URL: https://issues.apache.org/jira/browse/CALCITE-666
Project: Calcite
Issue Type: Bug
Affects Versions: 1.2.0-incubating
Reporter: Yuri Au Yong
Assignee: Julian Hyde
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 */
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)