You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "YeongWei (JIRA)" <ji...@apache.org> on 2015/05/14 10:17:00 UTC
[jira] [Commented] (CALCITE-666) ANTI-semijoins implemented using
EnumerableSemiJoin
[ https://issues.apache.org/jira/browse/CALCITE-666?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14543366#comment-14543366 ]
YeongWei commented on CALCITE-666:
----------------------------------
Hi [~julianhyde],
I believe this issue has been resolved in some other fixes, I think it could be CALCITE-704.
Currently the query presented above will run using the .join(...) method along with the SINGLE_VALUE function.
I have added test cases, please refer to CALCITE-666-UnitTest-0.1.patch.
Thanks!
> 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
> Attachments: CALCITE-666-UnitTest-0.1.patch
>
>
> 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)