Dremio 1.2.2 breaks reflections using contains

1

Dremio 1.1.0

SELECT count(*) FROM localES.article.Article where contains(source:*ZD*)

228149

Planning
00-06                ElasticScan(resource=[article/Article], columns=[[`*`]], pushdown
 =[{
  "size" : 0,
  "query" : {
    "query_string" : {
      "query" : "source : *ZD*"
    }
  }

Reflection Outcome: query was NOT accelerated

SELECT count(*) FROM localES.article.Article where source like '%ZD%'

228149

Reflection Outcome: query was accelerated

SELECT count(*) FROM localES.article.Article where contains(*ZD*)

Node [rel#2224:Subset#21.PHYSICAL.SINGLETON([]).[]] could not be implemented; planner state: Root: rel#2224:Subset#21.PHYSICAL.SINGLETON([]).[] Original rel: Sets: Set#15, type: RecordType(VARCHAR(65536) snippet) rel#2210:Subset#15.LOGICAL.ANY([]).[], best=rel#2192, importance=0.3874204890000001 rel#2192:FilesystemScanDrel.LOGICAL.ANY([]).[](table="__accelerator"."cb961e5f-2048-474e-899e-7407e2a35dc0"."ac24a426-6b70-4e62-aa86-9e2556752509",columns=snippet,splits=1)2139873943__acceleratord4acfd8f-4f8d-433d-b495-a63468b25164, rowcount=647311.0, cumulative cost={647311.0 rows, 647311.0 cpu, 647311.0 io, 647311.0 network, 0.0 memory} rel#2228:AbstractConverter.LOGICAL.ANY([]).[](input=rel#2227:Subset#15.PHYSICAL.ANY([]).[],convention=LOGICAL,DistributionTraitDef=ANY([]),sort=[]), rowcount=647311.0, cumulative cost={inf} rel#2227:Subset#15.PHYSICAL.ANY([]).[], best=rel#2226, importance=0.3486784401000001 rel#2229:AbstractConverter.PHYSICAL.ANY([]).[](input=rel#2210:Subset#15.LOGICAL.ANY([]).[],convention=PHYSICAL,DistributionTraitDef=ANY([]),sort=[]), rowcount=647311.0, cumulative cost={inf} rel#2226:ParquetScanPrel.PHYSICAL.ANY([]).[](table="__accelerator"."cb961e5f-2048-474e-899e-7407e2a35dc0"."ac24a426-6b70-4e62-aa86-9e2556752509",columns=snippet,splits=1)2139873943__acceleratord4acfd8f-4f8d-433d-b495-a63468b25164, rowcount=647311.0, cumulative cost={647311.0 rows, 647311.0 cpu, 647311.0 io, 647311.0 network, 0.0 memory} Set#16, type: RecordType(VARCHAR(65536) snippet) rel#2213:Subset#16.LOGICAL.ANY([]).[], best=rel#2211, importance=0.4304672100000001 rel#2211:FilterRel.LOGICAL.ANY([]).[](input=rel#2210:Subset#15.LOGICAL.ANY([]).[],condition=contains('*ZD*')), rowcount=161827.75, cumulative cost={1294622.0 rows, 4531177.0 cpu, 647311.0 io, 647311.0 network, 0.0 memory} rel#2304:AbstractConverter.LOGICAL.ANY([]).[](input=rel#2303:Subset#16.PHYSICAL.ANY([]).[],convention=LOGICAL,DistributionTraitDef=ANY([]),sort=[]), rowcount=161827.75, cumulative cost={inf} rel#2303:Subset#16.PHYSICAL.ANY([]).[], best=null, importance=0.3874204890000001 rel#2305:AbstractConverter.PHYSICAL.ANY([]).[](input=rel#2213:Subset#16.LOGICAL.ANY([]).[],convention=PHYSICAL,DistributionTraitDef=ANY([]),sort=[]), rowcount=161827.75, cumulative cost={inf} rel#2308:FilterPrel.PHYSICAL.ANY([]).[](input=rel#2227:Subset#15.PHYSICAL.ANY([]).[],condition=contains('*ZD*')), rowcount=161827.75, cumulative cost={inf} Set#17, type: RecordType(INTEGER $f0) rel#2215:Subset#17.LOGICAL.ANY([]).[], best=rel#2214, importance=0.4782969000000001 rel#2214:ProjectRel.LOGICAL.ANY([]).[](input=rel#2213:Subset#16.LOGICAL.ANY([]).[],$f0=1), rowcount=161827.75, cumulative cost={1456449.75 rows, 5340315.75 cpu, 647311.0 io, 647311.0 network, 0.0 memory} rel#2246:AbstractConverter.LOGICAL.ANY([]).[](input=rel#2245:Subset#17.PHYSICAL.ANY([]).[],convention=LOGICAL,DistributionTraitDef=ANY([]),sort=[]), rowcount=161827.75, cumulative cost={inf} rel#2245:Subset#17.PHYSICAL.ANY([]).[], best=null, importance=0.4304672100000001 rel#2247:AbstractConverter.PHYSICAL.ANY([]).[](input=rel#2215:Subset#17.LOGICAL.ANY([]).[],convention=PHYSICAL,DistributionTraitDef=ANY([]),sort=[]), rowcount=161827.75, cumulative cost={inf} rel#2306:ProjectPrel.PHYSICAL.ANY([]).[](input=rel#2303:Subset#16.PHYSICAL.ANY([]).[],$f0=1), rowcount=161827.75, cumulative cost={inf} Set#18, type: RecordType(BIGINT EXPR$0) rel#2217:Subset#18.LOGICAL.ANY([]).[], best=rel#2216, importance=0.531441 rel#2216:AggregateRel.LOGICAL.ANY([]).[](input=rel#2215:Subset#17.LOGICAL.ANY([]).[],group={},EXPR$0=COUNT($0)), rowcount=1.0, cumulative cost={1456450.75 rows, 7524990.375 cpu, 647311.0 io, 647311.0 network, 0.0 memory} rel#2256:AbstractConverter.LOGICAL.ANY([]).[](input=rel#2255:Subset#18.PHYSICAL.SINGLETON([]).[],convention=LOGICAL,DistributionTraitDef=ANY([]),sort=[]), rowcount=1.0, cumulative cost={inf} rel#2265:AbstractConverter.LOGICAL.ANY([]).[](input=rel#2264:Subset#18.PHYSICAL.ANY([]).[],convention=LOGICAL,DistributionTraitDef=ANY([]),sort=[]), rowcount=1.0, cumulative cost={inf} rel#2255:Subset#18.PHYSICAL.SINGLETON([]).[], best=null, importance=0.4782969000000001 rel#2254:StreamAggPrel.PHYSICAL.SINGLETON([]).[](input=rel#2253:Subset#24.PHYSICAL.SINGLETON([]).[],group={},EXPR$0=$SUM0($0)), rowcount=1.0, cumulative cost={inf} rel#2266:AbstractConverter.PHYSICAL.SINGLETON([]).[](input=rel#2264:Subset#18.PHYSICAL.ANY([]).[],convention=PHYSICAL,DistributionTraitDef=SINGLETON([]),sort=[]), rowcount=1.0, cumulative cost={inf} rel#2275:UnionExchangePrel.PHYSICAL.SINGLETON([]).[](input=rel#2264:Subset#18.PHYSICAL.ANY([]).[]), rowcount=1.0, cumulative cost={inf} rel#2264:Subset#18.PHYSICAL.ANY([]).[], best=null, importance=0.4304672100000001 rel#2254:StreamAggPrel.PHYSICAL.SINGLETON([]).[](input=rel#2253:Subset#24.PHYSICAL.SINGLETON([]).[],group={},EXPR$0=$SUM0($0)), rowcount=1.0, cumulative cost={inf} rel#2266:AbstractConverter.PHYSICAL.SINGLETON([]).[](input=rel#2264:Subset#18.PHYSICAL.ANY([]).[],convention=PHYSICAL,DistributionTraitDef=SINGLETON([]),sort=[]), rowcount=1.0, cumulative cost={inf} rel#2267:AbstractConverter.PHYSICAL.ANY([]).[](input=rel#2217:Subset#18.LOGICAL.ANY([]).[],convention=PHYSICAL,DistributionTraitDef=ANY([]),sort=[]), rowcount=1.0, cumulative cost={inf} rel#2275:UnionExchangePrel.PHYSICAL.SINGLETON([]).[](input=rel#2264:Subset#18.PHYSICAL.ANY([]).[]), rowcount=1.0, cumulative cost={inf} Set#19, type: RecordType(BIGINT EXPR$0) rel#2219:Subset#19.LOGICAL.ANY([]).[], best=rel#2218, importance=0.5904900000000001 rel#2218:ProjectRel.LOGICAL.ANY([]).[](input=rel#2217:Subset#18.LOGICAL.ANY([]).[],EXPR$0=$0), rowcount=1.0, cumulative cost={1456451.75 rows, 7524991.375 cpu, 647311.0 io, 647311.0 network, 0.0 memory} rel#2231:AbstractConverter.LOGICAL.ANY([]).[](input=rel#2230:Subset#19.PHYSICAL.ANY([]).[],convention=LOGICAL,DistributionTraitDef=ANY([]),sort=[]), rowcount=1.0, cumulative cost={inf} rel#2270:AbstractConverter.LOGICAL.ANY([]).[](input=rel#2269:Subset#19.PHYSICAL.SINGLETON([]).[],convention=LOGICAL,DistributionTraitDef=ANY([]),sort=[]), rowcount=1.0, cumulative cost={inf} rel#2230:Subset#19.PHYSICAL.ANY([]).[], best=null, importance=0.6561 rel#2232:AbstractConverter.PHYSICAL.ANY([]).[](input=rel#2219:Subset#19.LOGICAL.ANY([]).[],convention=PHYSICAL,DistributionTraitDef=ANY([]),sort=[]), rowcount=1.0, cumulative cost={inf} rel#2271:AbstractConverter.PHYSICAL.SINGLETON([]).[](input=rel#2230:Subset#19.PHYSICAL.ANY([]).[],convention=PHYSICAL,DistributionTraitDef=SINGLETON([]),sort=[]), rowcount=1.0, cumulative cost={inf} rel#2268:ProjectPrel.PHYSICAL.SINGLETON([]).[](input=rel#2255:Subset#18.PHYSICAL.SINGLETON([]).[],EXPR$0=$0), rowcount=1.0, cumulative cost={inf} rel#2273:ProjectPrel.PHYSICAL.ANY([]).[](input=rel#2264:Subset#18.PHYSICAL.ANY([]).[],EXPR$0=$0), rowcount=1.0, cumulative cost={inf} rel#2274:UnionExchangePrel.PHYSICAL.SINGLETON([]).[](input=rel#2230:Subset#19.PHYSICAL.ANY([]).[]), rowcount=1.0, cumulative cost={inf} rel#2269:Subset#19.PHYSICAL.SINGLETON([]).[], best=null, importance=0.531441 rel#2271:AbstractConverter.PHYSICAL.SINGLETON([]).[](input=rel#2230:Subset#19.PHYSICAL.ANY([]).[],convention=PHYSICAL,DistributionTraitDef=SINGLETON([]),sort=[]), rowcount=1.0, cumulative cost={inf} rel#2268:ProjectPrel.PHYSICAL.SINGLETON([]).[](input=rel#2255:Subset#18.PHYSICAL.SINGLETON([]).[],EXPR$0=$0), rowcount=1.0, cumulative cost={inf} rel#2274:UnionExchangePrel.PHYSICAL.SINGLETON([]).[](input=rel#2230:Subset#19.PHYSICAL.ANY([]).[]), rowcount=1.0, cumulative cost={inf} Set#20, type: RecordType(VARCHAR(65536) Fragment, BIGINT Records, VARCHAR(65536) Path, VARBINARY(65536) Metadata, INTEGER Partition) rel#2221:Subset#20.LOGICAL.ANY([]).[], best=rel#2220, importance=0.45 rel#2220:WriterRel.LOGICAL.ANY([]).[](input=rel#2219:Subset#19.LOGICAL.ANY([]).[]), rowcount=1.0, cumulative cost={1456452.75 rows, 7524992.375 cpu, 647311.0 io, 647311.0 network, 0.0 memory} rel#2240:AbstractConverter.LOGICAL.ANY([]).[](input=rel#2239:Subset#20.PHYSICAL.SINGLETON([]).[],convention=LOGICAL,DistributionTraitDef=ANY([]),sort=[]), rowcount=1.0, cumulative cost={inf} rel#2239:Subset#20.PHYSICAL.SINGLETON([]).[], best=null, importance=0.9 rel#2238:WriterCommitterPrel.PHYSICAL.SINGLETON([]).[](input=rel#2236:Subset#22.PHYSICAL.SINGLETON([]).[],temp=/dremio-community-1.1.0/data/pdfs/results/.261a1752-ae8b-fd55-e149-dd5d8f3d0000-1508239532447,final=/dremio-community-1.1.0/data/pdfs/results/261a1752-ae8b-fd55-e149-dd5d8f3d0000), rowcount=1.0, cumulative cost={inf} rel#2244:WriterCommitterPrel.PHYSICAL.SINGLETON([]).[](input=rel#2236:Subset#22.PHYSICAL.SINGLETON([]).[],temp=/dremio-community-1.1.0/data/pdfs/results/.261a1752-ae8b-fd55-e149-dd5d8f3d0000-1508239532449,final=/dremio-community-1.1.0/data/pdfs/results/261a1752-ae8b-fd55-e149-dd5d8f3d0000), rowcount=1.0, cumulative cost={inf} rel#2278:WriterCommitterPrel.PHYSICAL.SINGLETON([]).[](input=rel#2236:Subset#22.PHYSICAL.SINGLETON([]).[],temp=/dremio-community-1.1.0/data/pdfs/results/.261a1752-ae8b-fd55-e149-dd5d8f3d0000-1508239532458,final=/dremio-community-1.1.0/data/pdfs/results/261a1752-ae8b-fd55-e149-dd5d8f3d0000), rowcount=1.0, cumulative cost={inf} rel#2283:WriterCommitterPrel.PHYSICAL.SINGLETON([]).[](input=rel#2282:Subset#25.PHYSICAL.SINGLETON([]).[],temp=/dremio-community-1.1.0/data/pdfs/results/.261a1752-ae8b-fd55-e149-dd5d8f3d0000-1508239532459,final=/dremio-community-1.1.0/data/pdfs/results/261a1752-ae8b-fd55-e149-dd5d8f3d0000), rowcount=1.0, cumulative cost={inf} rel#2290:WriterCommitterPrel.PHYSICAL.SINGLETON([]).[](input=rel#2236:Subset#22.PHYSICAL.SINGLETON([]).[],temp=/dremio-community-1.1.0/data/pdfs/results/.261a1752-ae8b-fd55-e149-dd5d8f3d0000-1508239532459,final=/dremio-community-1.1.0/data/pdfs/results/261a1752-ae8b-fd55-e149-dd5d8f3d0000), rowcount=1.0, cumulative cost={inf} rel#2294:WriterCommitterPrel.PHYSICAL.SINGLETON([]).[](input=rel#2282:Subset#25.PHYSICAL.SINGLETON([]).[],temp=/dremio-community-1.1.0/data/pdfs/results/.261a1752-ae8b-fd55-e149-dd5d8f3d0000-1508239532460,final=/dremio-community-1.1.0/data/pdfs/results/261a1752-ae8b-fd55-e149-dd5d8f3d0000), rowcount=1.0, cumulative cost={inf} Set#21, type: RecordType(VARCHAR(65536) Fragment, BIGINT Records, VARCHAR(65536) Path, VARBINARY(65536) Metadata, INTEGER Partition) rel#2223:Subset#21.LOGICAL.ANY([]).[], best=rel#2222, importance=0.5 rel#2222:ScreenRel.LOGICAL.ANY([]).[](input=rel#2221:Subset#20.LOGICAL.ANY([]).[]), rowcount=1.0, cumulative cost={1456452.85 rows, 7524992.475 cpu, 647311.0 io, 647311.0 network, 0.0 memory} rel#2225:AbstractConverter.LOGICAL.ANY([]).[](input=rel#2224:Subset#21.PHYSICAL.SINGLETON([]).[],convention=LOGICAL,DistributionTraitDef=ANY([]),sort=[]), rowcount=1.0, cumulative cost={inf} rel#2224:Subset#21.PHYSICAL.SINGLETON([]).[], best=null, importance=1.0 rel#2262:ScreenPrel.PHYSICAL.SINGLETON([]).[](input=rel#2239:Subset#20.PHYSICAL.SINGLETON([]).[]), rowcount=1.0, cumulative cost={inf} Set#22, type: RecordType(VARCHAR(65536) Fragment, BIGINT Records, VARCHAR(65536) Path, VARBINARY(65536) Metadata, INTEGER Partition) rel#2235:Subset#22.PHYSICAL.ANY([]).[], best=null, importance=0.7290000000000001 rel#2234:WriterPrel.PHYSICAL.ANY([]).[](input=rel#2230:Subset#19.PHYSICAL.ANY([]).[]), rowcount=1.0, cumulative cost={inf} rel#2237:AbstractConverter.PHYSICAL.SINGLETON([]).[](input=rel#2235:Subset#22.PHYSICAL.ANY([]).[],convention=PHYSICAL,DistributionTraitDef=SINGLETON([]),sort=[]), rowcount=1.0, cumulative cost={inf} rel#2241:UnionExchangePrel.PHYSICAL.SINGLETON([]).[](input=rel#2235:Subset#22.PHYSICAL.ANY([]).[]), rowcount=1.0, cumulative cost={inf} rel#2236:Subset#22.PHYSICAL.SINGLETON([]).[], best=null, importance=0.81 rel#2237:AbstractConverter.PHYSICAL.SINGLETON([]).[](input=rel#2235:Subset#22.PHYSICAL.ANY([]).[],convention=PHYSICAL,DistributionTraitDef=SINGLETON([]),sort=[]), rowcount=1.0, cumulative cost={inf} rel#2241:UnionExchangePrel.PHYSICAL.SINGLETON([]).[](input=rel#2235:Subset#22.PHYSICAL.ANY([]).[]), rowcount=1.0, cumulative cost={inf} Set#23, type: RecordType(BIGINT EXPR$0) rel#2251:Subset#23.PHYSICAL.ANY([]).[], best=null, importance=0.3874204890000001 rel#2248:StreamAggPrel.PHYSICAL.ANY([]).[](input=rel#2245:Subset#17.PHYSICAL.ANY([]).[],group={},EXPR$0=COUNT($0)), rowcount=1.0, cumulative cost={inf} Set#24, type: RecordType(BIGINT EXPR$0) rel#2253:Subset#24.PHYSICAL.SINGLETON([]).[], best=null, importance=0.4304672100000001 rel#2252:UnionExchangePrel.PHYSICAL.SINGLETON([]).[](input=rel#2251:Subset#23.PHYSICAL.ANY([]).[]), rowcount=1.0, cumulative cost={inf} Set#25, type: RecordType(VARCHAR(65536) Fragment, BIGINT Records, VARCHAR(65536) Path, VARBINARY(65536) Metadata, INTEGER Partition) rel#2282:Subset#25.PHYSICAL.SINGLETON([]).[], best=null, importance=0.81 rel#2280:WriterPrel.PHYSICAL.SINGLETON([]).[](input=rel#2269:Subset#19.PHYSICAL.SINGLETON([]).[]), rowcount=1.0, cumulative cost={inf}

Dremio 1.2.2

SELECT count(*) FROM localES.article.Article where contains(source:*ZD*)

UI Error: Contains operator is not supported for the table, contains($0, '$0 : *ZD*')

Log Error:
         PARSE ERROR: Failure parsing the query. [line 1, column 46]

SQL Query SELECT count(*) FROM localES.article.Article where like '%ZD%'
startLine 1
startColumn 46
endLine 1
endColumn 50
Sql Query SELECT count(*) FROM localES.article.Article where like '%ZD%'


  (org.apache.calcite.sql.parser.SqlParseException) Encountered "where like" at line 1, column 46.
Was expecting one of:
    <EOF> 
    "ORDER" ...
    "LIMIT" ...
    "OFFSET" ...
    "FETCH" ...
    "NATURAL" ...
    "JOIN" ...
    "INNER" ...
    "LEFT" ...
    "RIGHT" ...
    "FULL" ...
    "CROSS" ...
    "," ...
    "OUTER" ...
    "EXTEND" ...
    "(" ...
    "AS" ...
    <IDENTIFIER> ...
    <QUOTED_IDENTIFIER> ...
    <BACK_QUOTED_IDENTIFIER> ...
    <BRACKET_QUOTED_IDENTIFIER> ...
    <UNICODE_QUOTED_IDENTIFIER> ...
    "TABLESAMPLE" ...
    "WHERE" "+" ...
    "WHERE" "-" ...
    "WHERE" "NOT" ...
    "WHERE" "EXISTS" ...
    "WHERE" <UNSIGNED_INTEGER_LITERAL> ...
    "WHERE" <DECIMAL_NUMERIC_LITERAL> ...
    "WHERE" <APPROX_NUMERIC_LITERAL> ...
    "WHERE" <BINARY_STRING_LITERAL> ...
    "WHERE" <PREFIXED_STRING_LITERAL> ...
    "WHERE" <QUOTED_STRING> ...
    "WHERE" <UNICODE_STRING_LITERAL> ...
    "WHERE" "TRUE" ...
    "WHERE" "FALSE" ...
    "WHERE" "UNKNOWN" ...
    "WHERE" "NULL" ...
    "WHERE" <LBRACE_D> ...
    "WHERE" <LBRACE_T> ...
    "WHERE" <LBRACE_TS> ...
    "WHERE" "DATE" ...
    "WHERE" "TIME" ...
    "WHERE" "TIMESTAMP" ...
    "WHERE" "INTERVAL" ...
    "WHERE" "CONTAINS(" ...
    "WHERE" "?" ...
    "WHERE" "CAST" ...
    "WHERE" "EXTRACT" ...
    "WHERE" "POSITION" ...
    "WHERE" "CONVERT" ...
    "WHERE" "TRANSLATE" ...
    "WHERE" "OVERLAY" ...
    "WHERE" "FLOOR" ...
    "WHERE" "CEIL" ...
    "WHERE" "CEILING" ...
    "WHERE" "SUBSTRING" ...
    "WHERE" "TRIM" ...
    "WHERE" <LBRACE_FN> ...
    "WHERE" "MULTISET" ...
    "WHERE" "ARRAY" ...
    "WHERE" "SPECIFIC" ...
    "WHERE" <IDENTIFIER> ...
    "WHERE" <QUOTED_IDENTIFIER> ...
    "WHERE" <BACK_QUOTED_IDENTIFIER> ...
    "WHERE" <BRACKET_QUOTED_IDENTIFIER> ...
    "WHERE" <UNICODE_QUOTED_IDENTIFIER> ...
    "WHERE" "ABS" ...
    "WHERE" "AVG" ...
    "WHERE" "CARDINALITY" ...
    "WHERE" "CHAR_LENGTH" ...
    "WHERE" "CHARACTER_LENGTH" ...
    "WHERE" "COALESCE" ...
    "WHERE" "COLLECT" ...
    "WHERE" "COVAR_POP" ...
    "WHERE" "COVAR_SAMP" ...
    "WHERE" "CUME_DIST" ...
    "WHERE" "COUNT" ...
    "WHERE" "CURRENT_DATE" ...
    "WHERE" "CURRENT_TIME" ...
    "WHERE" "CURRENT_TIMESTAMP" ...
    "WHERE" "DENSE_RANK" ...
    "WHERE" "ELEMENT" ...
    "WHERE" "EXP" ...
    "WHERE" "FIRST_VALUE" ...
    "WHERE" "FUSION" ...
    "WHERE" "GROUPING" ...
    "WHERE" "HOUR" ...
    "WHERE" "LAST_VALUE" ...
    "WHERE" "LN" ...
    "WHERE" "LOCALTIME" ...
    "WHERE" "LOCALTIMESTAMP" ...
    "WHERE" "LOWER" ...
    "WHERE" "MAX" ...
    "WHERE" "MIN" ...
    "WHERE" "MINUTE" ...
    "WHERE" "MOD" ...
    "WHERE" "MONTH" ...
    "WHERE" "NULLIF" ...
    "WHERE" "OCTET_LENGTH" ...
    "WHERE" "PERCENT_RANK" ...
    "WHERE" "POWER" ...
    "WHERE" "RANK" ...
    "WHERE" "REGR_SXX" ...
    "WHERE" "REGR_SYY" ...
    "WHERE" "ROW_NUMBER" ...
    "WHERE" "SECOND" ...
    "WHERE" "SQRT" ...
    "WHERE" "STDDEV_POP" ...
    "WHERE" "STDDEV_SAMP" ...
    "WHERE" "SUM" ...
    "WHERE" "UPPER" ...
    "WHERE" "USER" ...
    "WHERE" "VAR_POP" ...
    "WHERE" "VAR_SAMP" ...
    "WHERE" "YEAR" ...
    "WHERE" "CURRENT_CATALOG" ...
    "WHERE" "CURRENT_DEFAULT_TRANSFORM_GROUP" ...
    "WHERE" "CURRENT_PATH" ...
    "WHERE" "CURRENT_ROLE" ...
    "WHERE" "CURRENT_SCHEMA" ...
    "WHERE" "CURRENT_USER" ...
    "WHERE" "SESSION_USER" ...
    "WHERE" "SYSTEM_USER" ...
    "WHERE" "NEW" ...
    "WHERE" "CASE" ...
    "WHERE" "NEXT" ...
    "WHERE" "CURRENT" ...
    "WHERE" "CURSOR" ...
    "WHERE" "ROW" ...
    "WHERE" "(" ...
    "GROUP" ...
    "HAVING" ...
    "WINDOW" ...
    "UNION" ...
    "INTERSECT" ...
    "EXCEPT" ...
    "MINUS" ...
    "." ...
    "[" ...
    
    com.dremio.exec.planner.sql.parser.impl.ParserImpl.convertException():397
    com.dremio.exec.planner.sql.parser.impl.ParserImpl.normalizeException():130
    org.apache.calcite.sql.parser.SqlParser.parseQuery():138
    org.apache.calcite.sql.parser.SqlParser.parseStmt():163
    com.dremio.exec.planner.sql.SqlConverter.parse():170
    com.dremio.exec.planner.sql.handlers.commands.CommandCreator.getSqlCommand():221
    com.dremio.exec.planner.sql.handlers.commands.CommandCreator.toCommand():188
    com.dremio.exec.work.foreman.AttemptManager.run():276
    java.util.concurrent.ThreadPoolExecutor.runWorker():1149
    java.util.concurrent.ThreadPoolExecutor$Worker.run():624
    java.lang.Thread.run():748
  Caused By (com.dremio.exec.planner.sql.parser.impl.ParseException) Encountered "where like" at line 1, column 46.
Was expecting one of:
    <EOF> 
    "ORDER" ...
    "LIMIT" ...
    "OFFSET" ...
    "FETCH" ...
    "NATURAL" ...
    "JOIN" ...
    "INNER" ...
    "LEFT" ...
    "RIGHT" ...
    "FULL" ...
    "CROSS" ...
    "," ...
    "OUTER" ...
    "EXTEND" ...
    "(" ...
    "AS" ...
    <IDENTIFIER> ...
    <QUOTED_IDENTIFIER> ...
    <BACK_QUOTED_IDENTIFIER> ...
    <BRACKET_QUOTED_IDENTIFIER> ...
    <UNICODE_QUOTED_IDENTIFIER> ...
    "TABLESAMPLE" ...
    "WHERE" "+" ...
    "WHERE" "-" ...
    "WHERE" "NOT" ...
    "WHERE" "EXISTS" ...
    "WHERE" <UNSIGNED_INTEGER_LITERAL> ...
    "WHERE" <DECIMAL_NUMERIC_LITERAL> ...
    "WHERE" <APPROX_NUMERIC_LITERAL> ...
    "WHERE" <BINARY_STRING_LITERAL> ...
    "WHERE" <PREFIXED_STRING_LITERAL> ...
    "WHERE" <QUOTED_STRING> ...
    "WHERE" <UNICODE_STRING_LITERAL> ...
    "WHERE" "TRUE" ...
    "WHERE" "FALSE" ...
    "WHERE" "UNKNOWN" ...
    "WHERE" "NULL" ...
    "WHERE" <LBRACE_D> ...
    "WHERE" <LBRACE_T> ...
    "WHERE" <LBRACE_TS> ...
    "WHERE" "DATE" ...
    "WHERE" "TIME" ...
    "WHERE" "TIMESTAMP" ...
    "WHERE" "INTERVAL" ...
    "WHERE" "CONTAINS(" ...
    "WHERE" "?" ...
    "WHERE" "CAST" ...
    "WHERE" "EXTRACT" ...
    "WHERE" "POSITION" ...
    "WHERE" "CONVERT" ...
    "WHERE" "TRANSLATE" ...
    "WHERE" "OVERLAY" ...
    "WHERE" "FLOOR" ...
    "WHERE" "CEIL" ...
    "WHERE" "CEILING" ...
    "WHERE" "SUBSTRING" ...
    "WHERE" "TRIM" ...
    "WHERE" <LBRACE_FN> ...
    "WHERE" "MULTISET" ...
    "WHERE" "ARRAY" ...
    "WHERE" "SPECIFIC" ...
    "WHERE" <IDENTIFIER> ...
    "WHERE" <QUOTED_IDENTIFIER> ...
    "WHERE" <BACK_QUOTED_IDENTIFIER> ...
    "WHERE" <BRACKET_QUOTED_IDENTIFIER> ...
    "WHERE" <UNICODE_QUOTED_IDENTIFIER> ...
    "WHERE" "ABS" ...
    "WHERE" "AVG" ...
    "WHERE" "CARDINALITY" ...
    "WHERE" "CHAR_LENGTH" ...
    "WHERE" "CHARACTER_LENGTH" ...
    "WHERE" "COALESCE" ...
    "WHERE" "COLLECT" ...
    "WHERE" "COVAR_POP" ...
    "WHERE" "COVAR_SAMP" ...
    "WHERE" "CUME_DIST" ...
    "WHERE" "COUNT" ...
    "WHERE" "CURRENT_DATE" ...
    "WHERE" "CURRENT_TIME" ...
    "WHERE" "CURRENT_TIMESTAMP" ...
    "WHERE" "DENSE_RANK" ...
    "WHERE" "ELEMENT" ...
    "WHERE" "EXP" ...
    "WHERE" "FIRST_VALUE" ...
    "WHERE" "FUSION" ...
    "WHERE" "GROUPING" ...
    "WHERE" "HOUR" ...
    "WHERE" "LAST_VALUE" ...
    "WHERE" "LN" ...
    "WHERE" "LOCALTIME" ...
    "WHERE" "LOCALTIMESTAMP" ...
    "WHERE" "LOWER" ...
    "WHERE" "MAX" ...
    "WHERE" "MIN" ...
    "WHERE" "MINUTE" ...
    "WHERE" "MOD" ...
    "WHERE" "MONTH" ...
    "WHERE" "NULLIF" ...
    "WHERE" "OCTET_LENGTH" ...
    "WHERE" "PERCENT_RANK" ...
    "WHERE" "POWER" ...
    "WHERE" "RANK" ...
    "WHERE" "REGR_SXX" ...
    "WHERE" "REGR_SYY" ...
    "WHERE" "ROW_NUMBER" ...
    "WHERE" "SECOND" ...
    "WHERE" "SQRT" ...
    "WHERE" "STDDEV_POP" ...
    "WHERE" "STDDEV_SAMP" ...
    "WHERE" "SUM" ...
    "WHERE" "UPPER" ...
    "WHERE" "USER" ...
    "WHERE" "VAR_POP" ...
    "WHERE" "VAR_SAMP" ...
    "WHERE" "YEAR" ...
    "WHERE" "CURRENT_CATALOG" ...
    "WHERE" "CURRENT_DEFAULT_TRANSFORM_GROUP" ...
    "WHERE" "CURRENT_PATH" ...
    "WHERE" "CURRENT_ROLE" ...
    "WHERE" "CURRENT_SCHEMA" ...
    "WHERE" "CURRENT_USER" ...
    "WHERE" "SESSION_USER" ...
    "WHERE" "SYSTEM_USER" ...
    "WHERE" "NEW" ...
    "WHERE" "CASE" ...
    "WHERE" "NEXT" ...
    "WHERE" "CURRENT" ...
    "WHERE" "CURSOR" ...
    "WHERE" "ROW" ...
    "WHERE" "(" ...
    "GROUP" ...
    "HAVING" ...
    "WINDOW" ...
    "UNION" ...
    "INTERSECT" ...
    "EXCEPT" ...
    "MINUS" ...
    "." ...
    "[" ...
    
    com.dremio.exec.planner.sql.parser.impl.ParserImpl.generateParseException():23591
    com.dremio.exec.planner.sql.parser.impl.ParserImpl.jj_consume_token():23408
    com.dremio.exec.planner.sql.parser.impl.ParserImpl.SqlStmtEof():864
    com.dremio.exec.planner.sql.parser.impl.ParserImpl.parseSqlStmtEof():186
    com.dremio.exec.planner.sql.parser.impl.ParserWithCompoundIdConverter.parseSqlStmtEof():59
    org.apache.calcite.sql.parser.SqlParser.parseQuery():131
    org.apache.calcite.sql.parser.SqlParser.parseStmt():163
    com.dremio.exec.planner.sql.SqlConverter.parse():170
    com.dremio.exec.planner.sql.handlers.commands.CommandCreator.getSqlCommand():221
    com.dremio.exec.planner.sql.handlers.commands.CommandCreator.toCommand():188
    com.dremio.exec.work.foreman.AttemptManager.run():276
    java.util.concurrent.ThreadPoolExecutor.runWorker():1149
    java.util.concurrent.ThreadPoolExecutor$Worker.run():624
    java.lang.Thread.run():748

SELECT count(*) FROM localES.article.Article where source like '%ZD%'

228149

Reflection Outcome: Query was accelerated

SELECT count(*) FROM localES.article.Article where contains(*ZD*)

Error:

EXPR$0
228149
Node [rel#3318:Subset#21.PHYSICAL.SINGLETON([]).[]] could not be implemented; planner state: Root: rel#3318:Subset#21.PHYSICAL.SINGLETON([]).[] Original rel: Sets: Set#15, type: RecordType(VARCHAR(65536) snippet) rel#3304:Subset#15.LOGICAL.ANY([]).[], best=rel#3286, importance=0.3874204890000001 rel#3286:FilesystemScanDrel.LOGICAL.ANY([]).[](table="__accelerator"."a437f89c-aa7b-4340-a086-f9be7feccbe3"."dbc57315-034b-4ebe-a263-adf937b9c327",columns=`snippet`,splits=1)745018507__acceleratord640e8f3-6627-4e74-9bf2-263962fa8900, rowcount=647311.0, cumulative cost={647311.0 rows, 647311.0 cpu, 647311.0 io, 647311.0 network, 0.0 memory} rel#3322:AbstractConverter.LOGICAL.ANY([]).[](input=rel#3321:Subset#15.PHYSICAL.ANY([]).[],convention=LOGICAL,DistributionTraitDef=ANY([]),sort=[]), rowcount=647311.0, cumulative cost={inf} rel#3321:Subset#15.PHYSICAL.ANY([]).[], best=rel#3320, importance=0.3486784401000001 rel#3323:AbstractConverter.PHYSICAL.ANY([]).[](input=rel#3304:Subset#15.LOGICAL.ANY([]).[],convention=PHYSICAL,DistributionTraitDef=ANY([]),sort=[]), rowcount=647311.0, cumulative cost={inf} rel#3320:ParquetScanPrel.PHYSICAL.ANY([]).[](table="__accelerator"."a437f89c-aa7b-4340-a086-f9be7feccbe3"."dbc57315-034b-4ebe-a263-adf937b9c327",columns=`snippet`,splits=1)745018507__acceleratord640e8f3-6627-4e74-9bf2-263962fa8900, rowcount=647311.0, cumulative cost={647311.0 rows, 647311.0 cpu, 647311.0 io, 647311.0 network, 0.0 memory} Set#16, type: RecordType(VARCHAR(65536) snippet) rel#3307:Subset#16.LOGICAL.ANY([]).[], best=rel#3305, importance=0.4304672100000001 rel#3305:FilterRel.LOGICAL.ANY([]).[](input=rel#3304:Subset#15.LOGICAL.ANY([]).[],condition=contains('*ZD*')), rowcount=161827.75, cumulative cost={1294622.0 rows, 4531177.0 cpu, 647311.0 io, 647311.0 network, 0.0 memory} rel#3398:AbstractConverter.LOGICAL.ANY([]).[](input=rel#3397:Subset#16.PHYSICAL.ANY([]).[],convention=LOGICAL,DistributionTraitDef=ANY([]),sort=[]), rowcount=161827.75, cumulative cost={inf} rel#3397:Subset#16.PHYSICAL.ANY([]).[], best=null, importance=0.3874204890000001 rel#3399:AbstractConverter.PHYSICAL.ANY([]).[](input=rel#3307:Subset#16.LOGICAL.ANY([]).[],convention=PHYSICAL,DistributionTraitDef=ANY([]),sort=[]), rowcount=161827.75, cumulative cost={inf} rel#3402:FilterPrel.PHYSICAL.ANY([]).[]...
2

First of all huge congrats to Dremio for pushing only the right part to the backend, this is amazing!

second… i have the same issue now :slight_smile: was working fine before.

3

Hi @Felixls

Can you please send me the profile for the query that failed with CONTAINS as I was able to run a similar query on 1.2.2 community edition and it ran successfully, see attached file

Thanks,
@balaji.ramaswamy

ES-Contains
ES-Contains.jpg2734×1142 227 KB

4

Hi @balaji.ramaswamy , in my case if the source table is cached (using reflection) the query fail:

5009dacb-8646-4463-a518-29a971b46996.zip (2.3 KB)

Thanks!

5

Hi @Felixls,

Let me also try that step and get back to you. Thanks for uploading the profile

@balaji.ramaswamy

6

Hi @Felixls,

I was able to reproduce the issue once I queried on the reflection. I have opened an internal bug with our engineering team. Kindly let me know if you have any other questions?

Thanks,
@balaji.ramaswamy

7

I came across the same issue. Was this resolved ??

8

@shayeny

We have fixes a number of planner state errors. Are you hitting this on the 4.1.x branch?

Thanks
@balaji.ramaswamy

9

I’m on community edition - build 3.3.1

10

@shayeny

Kindly upgrade to 4.1.4 and validate

Thanks
@balaji.ramaswamy

11

@balaji.ramaswamy I’m on Windows… so 4.1.4 is not available for windows yeah ??

12

@shayeny, We no longer have a Windows version,

13

That’s rather unfortunate :frowning: