Dremio queries es using in (xxx) More than 20 elements cause a full table scan

1

dremio queries es using in (xxx) More than 20 elements cause a full table scan

SQL is : SELECT * FROM “es-test”.“cif.tom_orders”.“default” where _id in( 5000,5001,5002,5003,5004,5005,5006,5007,5008,5009,5010,5011,5012,5013,5014,5015,5016,5017,5018,5019,5020,5021,5022,5023,5024,5025,5026,5027,5028,5029,5030,5031,5032,5033,5034,5035,5036,5037,5038,5039,5040,5041,5042,5043,5044,5045,5046,5047,5048,5049,5050 )

Error message:

SYSTEM ERROR: AssertionError: to VARCHAR(65536) from 5000

(com.dremio.exec.work.foreman.ForemanException) Unexpected exception during fragment initialization: Internal error: Error while applying rule ValuesRule, args [rel#339312:LogicalValues.NONE.ANY([]).[[0]](type=RecordType(VARCHAR(65536) ROW_VALUE),tuples=[{ 5000 }, { 5001 }, { 5002 }, { 5003 }, { 5004 }, { 5005 }, { 5006 }, { 5007 }, { 5008 }, { 5009 }, { 5010 }, { 5011 }, { 5012 }, { 5013 }, { 5014 }, { 5015 }, { 5016 }, { 5017 }, { 5018 }, { 5019 }, { 5020 }, { 5021 }, { 5022 }, { 5023 }, { 5024 }, { 5025 }, { 5026 }, { 5027 }, { 5028 }, { 5029 }, { 5030 }, { 5031 }, { 5032 }, { 5033 }, { 5034 }, { 5035 }, { 5036 }, { 5037 }, { 5038 }, { 5039 }, { 5040 }, { 5041 }, { 5042 }, { 5043 }, { 5044 }, { 5045 }, { 5046 }, { 5047 }, { 5048 }, { 5049 }, { 5050 }])]
com.dremio.exec.work.foreman.AttemptManager.run():335
java.util.concurrent.ThreadPoolExecutor.runWorker():1142
java.util.concurrent.ThreadPoolExecutor$Worker.run():617
java.lang.Thread.run():745
Caused By (java.lang.AssertionError) Internal error: Error while applying rule ValuesRule, args [rel#339312:LogicalValues.NONE.ANY([]).[[0]](type=RecordType(VARCHAR(65536) ROW_VALUE),tuples=[{ 5000 }, { 5001 }, { 5002 }, { 5003 }, { 5004 }, { 5005 }, { 5006 }, { 5007 }, { 5008 }, { 5009 }, { 5010 }, { 5011 }, { 5012 }, { 5013 }, { 5014 }, { 5015 }, { 5016 }, { 5017 }, { 5018 }, { 5019 }, { 5020 }, { 5021 }, { 5022 }, { 5023 }, { 5024 }, { 5025 }, { 5026 }, { 5027 }, { 5028 }, { 5029 }, { 5030 }, { 5031 }, { 5032 }, { 5033 }, { 5034 }, { 5035 }, { 5036 }, { 5037 }, { 5038 }, { 5039 }, { 5040 }, { 5041 }, { 5042 }, { 5043 }, { 5044 }, { 5045 }, { 5046 }, { 5047 }, { 5048 }, { 5049 }, { 5050 }])]
org.apache.calcite.util.Util.newInternal():795
org.apache.calcite.plan.volcano.VolcanoRuleCall.onMatch():236
org.apache.calcite.plan.volcano.VolcanoPlanner.findBestExp():812
com.dremio.exec.planner.DremioVolcanoPlanner.findBestExp():75
org.apache.calcite.tools.Programs$RuleSetProgram.run():339
com.dremio.exec.planner.sql.handlers.PrelTransformer.transform():392
com.dremio.exec.planner.sql.handlers.PrelTransformer.convertToDrel():203
com.dremio.exec.planner.sql.handlers.PrelTransformer.convertToDrel():249
com.dremio.exec.planner.sql.handlers.query.NormalHandler.getPlan():47
com.dremio.exec.planner.sql.handlers.commands.HandlerToExec.plan():66
com.dremio.exec.work.foreman.AttemptManager.run():287
java.util.concurrent.ThreadPoolExecutor.runWorker():1142
java.util.concurrent.ThreadPoolExecutor$Worker.run():617
java.lang.Thread.run():745
Caused By (java.lang.AssertionError) to VARCHAR(65536) from 5000
com.dremio.exec.planner.logical.ValuesRel.verifyRowType():99
com.dremio.exec.planner.logical.ValuesRel.():69
com.dremio.exec.planner.logical.ValuesRule.onMatch():38
org.apache.calcite.plan.volcano.VolcanoRuleCall.onMatch():213
org.apache.calcite.plan.volcano.VolcanoPlanner.findBestExp():812
com.dremio.exec.planner.DremioVolcanoPlanner.findBestExp():75
org.apache.calcite.tools.Programs$RuleSetProgram.run():339
com.dremio.exec.planner.sql.handlers.PrelTransformer.transform():392
com.dremio.exec.planner.sql.handlers.PrelTransformer.convertToDrel():203
com.dremio.exec.planner.sql.handlers.PrelTransformer.convertToDrel():249
com.dremio.exec.planner.sql.handlers.query.NormalHandler.getPlan():47
com.dremio.exec.planner.sql.handlers.commands.HandlerToExec.plan():66
com.dremio.exec.work.foreman.AttemptManager.run():287
java.util.concurrent.ThreadPoolExecutor.runWorker():1142
java.util.concurrent.ThreadPoolExecutor$Worker.run():617
java.lang.Thread.run():745

2

Hi @JoyJava

Does using more than 20 elements throw above error or cause FTS?

Can you please try the below workaround?

1) Navigate to admin-> advanced settings page
2) On Bottom right, you will see a text box with "suppport key" text
3) In the text box type, planner.in.subquery.threshold
4) Update this value from 20 to 50 and save the setting
5) Rerun the query

Thanks,
@balaji.ramaswamy

3

Cool, got it. It seems that we can restore the modified code. thank you very much