[ https://issues.apache.org/jira/browse/CALCITE-2696?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16696376#comment-16696376 ]
Julian Hyde commented on CALCITE-2696: -------------------------------------- I think you're hitting {{SqlToRelConverter.DEFAULT_IN_SUB_QUERY_THRESHOLD}} (which is 20). Above that threshold, Calcite converts an IN clause to a JOIN to a VALUES. Therefore it's not implemented as a filter on the table, and so it is not in the {{filters}} parameter when your table's {{scan}} method is called. > 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)