[jira] [Commented] (CALCITE-2696) Filter containing IN clause not passed to Enumerable.scan

["Dirk Mahler (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-2696?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16695833#comment-16695833 ] 

Dirk Mahler commented on CALCITE-2696:
--------------------------------------

Attached a test case illustrating the behavior.

> Filter containing IN clause not passed to Enumerable.scan
> ---------------------------------------------------------
>
>                 Key: CALCITE-2696
>                 URL: https://issues.apache.org/jira/browse/CALCITE-2696
>             Project: Calcite
>          Issue Type: Bug
>          Components: core
>    Affects Versions: 1.17.0
>            Reporter: Dirk Mahler
>            Assignee: Julian Hyde
>            Priority: Major
>         Attachments: calcite-in-clause.zip
>
>
> I'm using the Calcite JDBC driver with an own SchemaFactory (defined by a model property) that provides a schema containing a ProjectableFilterableTable:
> {code:java}
> String model = "inline:" //
> + "{" //
> + " version: '1.0', " //
> + " defaultSchema: 'test'," //
> + " schemas: [" //
> + " {" //
> + " name: 'test'," //
> + " type: 'custom'," //
> + " factory: '" + TestSchemaFactory.class.getName() + "'" //
> + " }"
> + " ]" //
> + "}";
> Properties properties = new Properties();
> properties.put(CalciteConnectionProperty.MODEL.camelName(), model);
> connection = DriverManager.getConnection("jdbc:calcite:", properties);
> {code}
>  
>  
> {code:java}
> class TestTable extends AbstractQueryableTable implements ProjectableFilterableTable {
>   public Enumerable<Object[]> scan(DataContext root, List<RexNode> filters, int[] projects) {
> ...
>   }
>   ...
> }{code}
>  
> It maps to a Java class and provides two Integer typed columns "value1" and "value2".
> The following query leads to a quite expensive behavior in the scan method if the following statement is executed:
>  
> {code:java}
> SELECT "value" FROM "TEST_TABLE" WHERE "value1" = 1 AND "value2" in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20)
> {code}
> The scan method is invoked with a filter that only covers the part "value1" = 1, the IN clause is completely omitted. The result on the JDBC side is still valid but in my case this still leads to a full scan of a large underlying data set (millions of rows).
> Interestingly the filter part reflecting the IN operator is provided if the number of elements in the list is below 20. It seems that this is controlled by org.apache.calcite.sql2rel.SqlToRelConverter.Config#getInSubQueryThreshold. It would at be very helpful if this behavior could be confgiured on the JDBC property level.



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)