Error on Query With variables Presto

1

Hello,

Im tying to run the following query but I got an error:

with t1 as (select * from database.fact_order
join database.dim_poc
on database.fact_order.poc_id = database.dim_poc.poc_id
where {{poc_category}}
[[and {{poc_name}}]])

select * from t1
where t1.created_datetime_utc > {{data}}

Error:
Query failed (#20211117_035842_03894_j22ha): line 7:101: IN value and list items must be the same type: varchar

StackTrace:

{:database_id 4,
 :started_at #t "2021-11-17T04:01:57.945001Z[GMT]",
 :via
 [{:status :failed,
   :class java.sql.SQLException,
   :error
   "Query failed (#20211117_040158_04915_j22ha): line 7:101: IN value and list items must be the same type: varchar",
   :stacktrace
   ["com.facebook.presto.jdbc.PrestoResultSet.resultsException(PrestoResultSet.java:1841)"
    "com.facebook.presto.jdbc.PrestoResultSet.getColumns(PrestoResultSet.java:1751)"
    "com.facebook.presto.jdbc.PrestoResultSet.<init>(PrestoResultSet.java:121)"
    "com.facebook.presto.jdbc.PrestoStatement.internalExecute(PrestoStatement.java:272)"
    "com.facebook.presto.jdbc.PrestoStatement.execute(PrestoStatement.java:230)"
    "com.facebook.presto.jdbc.PrestoPreparedStatement.executeQuery(PrestoPreparedStatement.java:99)"
    "com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:431)"
    "--> driver.sql_jdbc.execute$fn__52333.invokeStatic(execute.clj:364)"
    "driver.sql_jdbc.execute$fn__52333.invoke(execute.clj:362)"
    "driver.sql_jdbc.execute$execute_statement_or_prepared_statement_BANG_.invokeStatic(execute.clj:377)"
    "driver.sql_jdbc.execute$execute_statement_or_prepared_statement_BANG_.invoke(execute.clj:373)"
    "driver.sql_jdbc.execute$execute_reducible_query$fn__52415.invoke(execute.clj:502)"
    "driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:501)"
    "driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:487)"
    "driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:496)"
    "driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:487)"
    "driver.sql_jdbc$fn__85057.invokeStatic(sql_jdbc.clj:54)"
    "driver.sql_jdbc$fn__85057.invoke(sql_jdbc.clj:52)"
    "query_processor.context$executef.invokeStatic(context.clj:59)"
    "query_processor.context$executef.invoke(context.clj:48)"
    "query_processor.context.default$default_runf.invokeStatic(default.clj:68)"
    "query_processor.context.default$default_runf.invoke(default.clj:66)"
    "query_processor.context$runf.invokeStatic(context.clj:45)"
    "query_processor.context$runf.invoke(context.clj:39)"
    "query_processor.reducible$pivot.invokeStatic(reducible.clj:34)"
    "query_processor.reducible$pivot.invoke(reducible.clj:31)"
    "query_processor.middleware.mbql_to_native$mbql__GT_native$fn__49067.invoke(mbql_to_native.clj:25)"
    "query_processor.middleware.check_features$check_features$fn__49554.invoke(check_features.clj:39)"
    "query_processor.middleware.limit$limit$fn__47440.invoke(limit.clj:37)"
    "query_processor.middleware.cache$maybe_return_cached_results$fn__49928.invoke(cache.clj:204)"
    "query_processor.middleware.optimize_temporal_filters$optimize_temporal_filters$fn__50921.invoke(optimize_temporal_filters.clj:204)"
    "query_processor.middleware.validate_temporal_bucketing$validate_temporal_bucketing$fn__50965.invoke(validate_temporal_bucketing.clj:50)"
    "query_processor.middleware.auto_parse_filter_values$auto_parse_filter_values$fn__49124.invoke(auto_parse_filter_values.clj:43)"
    "query_processor.middleware.wrap_value_literals$wrap_value_literals$fn__39226.invoke(wrap_value_literals.clj:161)"
    "query_processor.middleware.annotate$add_column_info$fn__43657.invoke(annotate.clj:615)"
    "query_processor.middleware.permissions$check_query_permissions$fn__45595.invoke(permissions.clj:108)"
    "query_processor.middleware.pre_alias_aggregations$pre_alias_aggregations$fn__50105.invoke(pre_alias_aggregations.clj:40)"
    "query_processor.middleware.cumulative_aggregations$handle_cumulative_aggregations$fn__45993.invoke(cumulative_aggregations.clj:60)"
    "query_processor.middleware.visualization_settings$update_viz_settings$fn__45931.invoke(visualization_settings.clj:63)"
    "query_processor.middleware.resolve_joined_fields$resolve_joined_fields$fn__47038.invoke(resolve_joined_fields.clj:102)"
    "query_processor.middleware.resolve_joins$resolve_joins$fn__50691.invoke(resolve_joins.clj:171)"
    "query_processor.middleware.add_implicit_joins$add_implicit_joins$fn__50227.invoke(add_implicit_joins.clj:190)"
    "query_processor.middleware.large_int_id$convert_id_to_string$fn__47057.invoke(large_int_id.clj:59)"
    "query_processor.middleware.format_rows$format_rows$fn__50279.invoke(format_rows.clj:74)"
    "query_processor.middleware.add_default_temporal_unit$add_default_temporal_unit$fn__46429.invoke(add_default_temporal_unit.clj:23)"
    "query_processor.middleware.desugar$desugar$fn__45904.invoke(desugar.clj:21)"
    "query_processor.middleware.binning$update_binning_strategy$fn__39126.invoke(binning.clj:229)"
    "query_processor.middleware.resolve_fields$resolve_fields$fn__45238.invoke(resolve_fields.clj:34)"
    "query_processor.middleware.add_dimension_projections$add_remapping$fn__49479.invoke(add_dimension_projections.clj:312)"
    "query_processor.middleware.add_implicit_clauses$add_implicit_clauses$fn__49777.invoke(add_implicit_clauses.clj:147)"
    "query_processor.middleware.upgrade_field_literals$upgrade_field_literals$fn__47425.invoke(upgrade_field_literals.clj:40)"
    "query_processor.middleware.add_source_metadata$add_source_metadata_for_source_queries$fn__46806.invoke(add_source_metadata.clj:123)"
    "query_processor.middleware.reconcile_breakout_and_order_by_bucketing$reconcile_breakout_and_order_by_bucketing$fn__50027.invoke(reconcile_breakout_and_order_by_bucketing.clj:100)"
    "query_processor.middleware.auto_bucket_datetimes$auto_bucket_datetimes$fn__48376.invoke(auto_bucket_datetimes.clj:147)"
    "query_processor.middleware.resolve_source_table$resolve_source_tables$fn__45219.invoke(resolve_source_table.clj:45)"
    "query_processor.middleware.parameters$substitute_parameters$fn__48030.invoke(parameters.clj:111)"
    "query_processor.middleware.resolve_referenced$resolve_referenced_card_resources$fn__45292.invoke(resolve_referenced.clj:79)"
    "query_processor.middleware.expand_macros$expand_macros$fn__51349.invoke(expand_macros.clj:184)"
    "query_processor.middleware.add_timezone_info$add_timezone_info$fn__47809.invoke(add_timezone_info.clj:15)"
    "query_processor.middleware.splice_params_in_response$splice_params_in_response$fn__50294.invoke(splice_params_in_response.clj:32)"
    "query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__49788$fn__49792.invoke(resolve_database_and_driver.clj:31)"
    "driver$do_with_driver.invokeStatic(driver.clj:60)"
    "driver$do_with_driver.invoke(driver.clj:56)"
    "query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__49788.invoke(resolve_database_and_driver.clj:25)"
    "query_processor.middleware.fetch_source_query$resolve_card_id_source_tables$fn__45842.invoke(fetch_source_query.clj:274)"
    "query_processor.middleware.store$initialize_store$fn__46030$fn__46031.invoke(store.clj:11)"
    "query_processor.store$do_with_store.invokeStatic(store.clj:44)"
    "query_processor.store$do_with_store.invoke(store.clj:38)"
    "query_processor.middleware.store$initialize_store$fn__46030.invoke(store.clj:10)"
    "query_processor.middleware.validate$validate_query$fn__50077.invoke(validate.clj:10)"
    "query_processor.middleware.normalize_query$normalize$fn__50084.invoke(normalize_query.clj:22)"
    "query_processor.middleware.add_rows_truncated$add_rows_truncated$fn__47755.invoke(add_rows_truncated.clj:35)"
    "query_processor.middleware.results_metadata$record_and_return_metadata_BANG_$fn__49053.invoke(results_metadata.clj:147)"
    "query_processor.middleware.constraints$add_default_userland_constraints$fn__47773.invoke(constraints.clj:42)"
    "query_processor.middleware.process_userland_query$process_userland_query$fn__50063.invoke(process_userland_query.clj:146)"
    "query_processor.middleware.catch_exceptions$catch_exceptions$fn__50374.invoke(catch_exceptions.clj:169)"
    "query_processor.reducible$async_qp$qp_STAR___42477$thunk__42478.invoke(reducible.clj:103)"
    "query_processor.reducible$async_qp$qp_STAR___42477.invoke(reducible.clj:109)"
    "query_processor.reducible$sync_qp$qp_STAR___42486$fn__42489.invoke(reducible.clj:135)"
    "query_processor.reducible$sync_qp$qp_STAR___42486.invoke(reducible.clj:134)"
    "query_processor$process_userland_query.invokeStatic(query_processor.clj:241)"
    "query_processor$process_userland_query.doInvoke(query_processor.clj:237)"
    "query_processor$fn__51397$process_query_and_save_execution_BANG___51406$fn__51409.invoke(query_processor.clj:253)"
    "query_processor$fn__51397$process_query_and_save_execution_BANG___51406.invoke(query_processor.clj:245)"
    "query_processor$fn__51441$process_query_and_save_with_max_results_constraints_BANG___51450$fn__51453.invoke(query_processor.clj:265)"
    "query_processor$fn__51441$process_query_and_save_with_max_results_constraints_BANG___51450.invoke(query_processor.clj:258)"
    "api.dataset$run_query_async$fn__57784.invoke(dataset.clj:64)"
    "query_processor.streaming$streaming_response_STAR_$fn__38035$fn__38036.invoke(streaming.clj:148)"
    "query_processor.streaming$streaming_response_STAR_$fn__38035.invoke(streaming.clj:147)"
    "async.streaming_response$do_f_STAR_.invokeStatic(streaming_response.clj:65)"
    "async.streaming_response$do_f_STAR_.invoke(streaming_response.clj:63)"
    "async.streaming_response$do_f_async$fn__25228.invoke(streaming_response.clj:84)"],
   :state nil}
  {:status :failed,
   :class clojure.lang.ExceptionInfo,
   :error "Erro executando query",
   :stacktrace
   ["--> driver.sql_jdbc.execute$execute_reducible_query$fn__52415.invoke(execute.clj:504)"
    "driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:501)"
    "driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:487)"
    "driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:496)"
    "driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:487)"
    "driver.sql_jdbc$fn__85057.invokeStatic(sql_jdbc.clj:54)"
    "driver.sql_jdbc$fn__85057.invoke(sql_jdbc.clj:52)"
    "query_processor.context$executef.invokeStatic(context.clj:59)"
    "query_processor.context$executef.invoke(context.clj:48)"
    "query_processor.context.default$default_runf.invokeStatic(default.clj:68)"
    "query_processor.context.default$default_runf.invoke(default.clj:66)"
    "query_processor.context$runf.invokeStatic(context.clj:45)"
    "query_processor.context$runf.invoke(context.clj:39)"
    "query_processor.reducible$pivot.invokeStatic(reducible.clj:34)"
    "query_processor.reducible$pivot.invoke(reducible.clj:31)"
    "query_processor.middleware.mbql_to_native$mbql__GT_native$fn__49067.invoke(mbql_to_native.clj:25)"
    "query_processor.middleware.check_features$check_features$fn__49554.invoke(check_features.clj:39)"
    "query_processor.middleware.limit$limit$fn__47440.invoke(limit.clj:37)"
    "query_processor.middleware.cache$maybe_return_cached_results$fn__49928.invoke(cache.clj:204)"
    "query_processor.middleware.optimize_temporal_filters$optimize_temporal_filters$fn__50921.invoke(optimize_temporal_filters.clj:204)"
    "query_processor.middleware.validate_temporal_bucketing$validate_temporal_bucketing$fn__50965.invoke(validate_temporal_bucketing.clj:50)"
    "query_processor.middleware.auto_parse_filter_values$auto_parse_filter_values$fn__49124.invoke(auto_parse_filter_values.clj:43)"
    "query_processor.middleware.wrap_value_literals$wrap_value_literals$fn__39226.invoke(wrap_value_literals.clj:161)"
    "query_processor.middleware.annotate$add_column_info$fn__43657.invoke(annotate.clj:615)"
    "query_processor.middleware.permissions$check_query_permissions$fn__45595.invoke(permissions.clj:108)"
    "query_processor.middleware.pre_alias_aggregations$pre_alias_aggregations$fn__50105.invoke(pre_alias_aggregations.clj:40)"
    "query_processor.middleware.cumulative_aggregations$handle_cumulative_aggregations$fn__45993.invoke(cumulative_aggregations.clj:60)"
    "query_processor.middleware.visualization_settings$update_viz_settings$fn__45931.invoke(visualization_settings.clj:63)"
    "query_processor.middleware.resolve_joined_fields$resolve_joined_fields$fn__47038.invoke(resolve_joined_fields.clj:102)"
    "query_processor.middleware.resolve_joins$resolve_joins$fn__50691.invoke(resolve_joins.clj:171)"
    "query_processor.middleware.add_implicit_joins$add_implicit_joins$fn__50227.invoke(add_implicit_joins.clj:190)"
    "query_processor.middleware.large_int_id$convert_id_to_string$fn__47057.invoke(large_int_id.clj:59)"
    "query_processor.middleware.format_rows$format_rows$fn__50279.invoke(format_rows.clj:74)"
    "query_processor.middleware.add_default_temporal_unit$add_default_temporal_unit$fn__46429.invoke(add_default_temporal_unit.clj:23)"
    "query_processor.middleware.desugar$desugar$fn__45904.invoke(desugar.clj:21)"
    "query_processor.middleware.binning$update_binning_strategy$fn__39126.invoke(binning.clj:229)"
    "query_processor.middleware.resolve_fields$resolve_fields$fn__45238.invoke(resolve_fields.clj:34)"
    "query_processor.middleware.add_dimension_projections$add_remapping$fn__49479.invoke(add_dimension_projections.clj:312)"
    "query_processor.middleware.add_implicit_clauses$add_implicit_clauses$fn__49777.invoke(add_implicit_clauses.clj:147)"
    "query_processor.middleware.upgrade_field_literals$upgrade_field_literals$fn__47425.invoke(upgrade_field_literals.clj:40)"
    "query_processor.middleware.add_source_metadata$add_source_metadata_for_source_queries$fn__46806.invoke(add_source_metadata.clj:123)"
    "query_processor.middleware.reconcile_breakout_and_order_by_bucketing$reconcile_breakout_and_order_by_bucketing$fn__50027.invoke(reconcile_breakout_and_order_by_bucketing.clj:100)"
    "query_processor.middleware.auto_bucket_datetimes$auto_bucket_datetimes$fn__48376.invoke(auto_bucket_datetimes.clj:147)"
    "query_processor.middleware.resolve_source_table$resolve_source_tables$fn__45219.invoke(resolve_source_table.clj:45)"
    "query_processor.middleware.parameters$substitute_parameters$fn__48030.invoke(parameters.clj:111)"
    "query_processor.middleware.resolve_referenced$resolve_referenced_card_resources$fn__45292.invoke(resolve_referenced.clj:79)"
    "query_processor.middleware.expand_macros$expand_macros$fn__51349.invoke(expand_macros.clj:184)"
    "query_processor.middleware.add_timezone_info$add_timezone_info$fn__47809.invoke(add_timezone_info.clj:15)"
    "query_processor.middleware.splice_params_in_response$splice_params_in_response$fn__50294.invoke(splice_params_in_response.clj:32)"
    "query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__49788$fn__49792.invoke(resolve_database_and_driver.clj:31)"
    "driver$do_with_driver.invokeStatic(driver.clj:60)"
    "driver$do_with_driver.invoke(driver.clj:56)"
    "query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__49788.invoke(resolve_database_and_driver.clj:25)"
    "query_processor.middleware.fetch_source_query$resolve_card_id_source_tables$fn__45842.invoke(fetch_source_query.clj:274)"
    "query_processor.middleware.store$initialize_store$fn__46030$fn__46031.invoke(store.clj:11)"
    "query_processor.store$do_with_store.invokeStatic(store.clj:44)"
    "query_processor.store$do_with_store.invoke(store.clj:38)"
    "query_processor.middleware.store$initialize_store$fn__46030.invoke(store.clj:10)"
    "query_processor.middleware.validate$validate_query$fn__50077.invoke(validate.clj:10)"
    "query_processor.middleware.normalize_query$normalize$fn__50084.invoke(normalize_query.clj:22)"
    "query_processor.middleware.add_rows_truncated$add_rows_truncated$fn__47755.invoke(add_rows_truncated.clj:35)"
    "query_processor.middleware.results_metadata$record_and_return_metadata_BANG_$fn__49053.invoke(results_metadata.clj:147)"
    "query_processor.middleware.constraints$add_default_userland_constraints$fn__47773.invoke(constraints.clj:42)"
    "query_processor.middleware.process_userland_query$process_userland_query$fn__50063.invoke(process_userland_query.clj:146)"
    "query_processor.middleware.catch_exceptions$catch_exceptions$fn__50374.invoke(catch_exceptions.clj:169)"
    "query_processor.reducible$async_qp$qp_STAR___42477$thunk__42478.invoke(reducible.clj:103)"
    "query_processor.reducible$async_qp$qp_STAR___42477.invoke(reducible.clj:109)"
    "query_processor.reducible$sync_qp$qp_STAR___42486$fn__42489.invoke(reducible.clj:135)"
    "query_processor.reducible$sync_qp$qp_STAR___42486.invoke(reducible.clj:134)"
    "query_processor$process_userland_query.invokeStatic(query_processor.clj:241)"
    "query_processor$process_userland_query.doInvoke(query_processor.clj:237)"
    "query_processor$fn__51397$process_query_and_save_execution_BANG___51406$fn__51409.invoke(query_processor.clj:253)"
    "query_processor$fn__51397$process_query_and_save_execution_BANG___51406.invoke(query_processor.clj:245)"
    "query_processor$fn__51441$process_query_and_save_with_max_results_constraints_BANG___51450$fn__51453.invoke(query_processor.clj:265)"
    "query_processor$fn__51441$process_query_and_save_with_max_results_constraints_BANG___51450.invoke(query_processor.clj:258)"
    "api.dataset$run_query_async$fn__57784.invoke(dataset.clj:64)"
    "query_processor.streaming$streaming_response_STAR_$fn__38035$fn__38036.invoke(streaming.clj:148)"
    "query_processor.streaming$streaming_response_STAR_$fn__38035.invoke(streaming.clj:147)"
    "async.streaming_response$do_f_STAR_.invokeStatic(streaming_response.clj:65)"
    "async.streaming_response$do_f_STAR_.invoke(streaming_response.clj:63)"
    "async.streaming_response$do_f_async$fn__25228.invoke(streaming_response.clj:84)"],
   :error_type :invalid-query,
   :ex-data
   {:sql
    "-- Metabase:: userID: 2 queryType: native queryHash: bc6d4722ac77a26d9aed6f02720e442eecc1b1b724b02776d2730509e1addce9\nwith t1 as (select * from refined_dw.fact_order\njoin refined_dw.dim_poc\non refined_dw.fact_order.poc_id = refined_dw.dim_poc.poc_id\nwhere \"refined_dw\".\"dim_poc\".\"poc_category\" IN (?)\nand \"refined_dw\".\"dim_poc\".\"poc_name\" IN (?))\n\nselect * from t1\nwhere t1.created_datetime_utc > ?",
    :params ["DARK_STORE" "Dark Rock Burger" #t "2021-11-16"],
    :type :invalid-query}}],
 :error_type :invalid-query,
 :json_query
 {:database 4,
  :native
  {:template-tags
   {:poc_category
    {:id "9726d854-da69-ffbf-48f9-86005aa9134d",
     :name "poc_category",
     :display-name "Poc category",
     :type "dimension",
     :dimension ["field" 18036 nil],
     :widget-type "category",
     :default nil},
    :poc_name
    {:id "9a204dc5-cc27-0b37-355e-6fc398836094",
     :name "poc_name",
     :display-name "Poc name",
     :type "dimension",
     :dimension ["field" 18042 nil],
     :widget-type "category",
     :default nil},
    :data
    {:id "85fb1310-55c8-a03b-1581-5434b5a6871c", :name "data", :display-name "Data", :type "date", :default nil}},
   :query
   "with t1 as (select * from refined_dw.fact_order\njoin refined_dw.dim_poc\non refined_dw.fact_order.poc_id = refined_dw.dim_poc.poc_id\nwhere {{poc_category}}\n[[and {{poc_name}}]])\n\nselect * from t1\nwhere t1.created_datetime_utc > {{data}}"},
  :type "native",
  :parameters
  [{:type "category", :value ["DARK_STORE"], :target ["dimension" ["template-tag" "poc_category"]]}
   {:type "category", :value ["Dark Rock Burger"], :target ["dimension" ["template-tag" "poc_name"]]}
   {:type "date/single", :value "2021-11-16", :target ["variable" ["template-tag" "data"]]}],
  :middleware {:js-int-to-string? true, :add-default-userland-constraints? true}},
 :status :failed,
 :class com.facebook.presto.jdbc.internal.client.FailureInfo$FailureException,
 :stacktrace
 ["com.facebook.presto.sql.analyzer.ExpressionAnalyzer$Visitor.coerceToSingleType(ExpressionAnalyzer.java:1396)"
  "com.facebook.presto.sql.analyzer.ExpressionAnalyzer$Visitor.visitInPredicate(ExpressionAnalyzer.java:1107)"
  "com.facebook.presto.sql.analyzer.ExpressionAnalyzer$Visitor.visitInPredicate(ExpressionAnalyzer.java:323)"
  "com.facebook.presto.sql.tree.InPredicate.accept(InPredicate.java:58)"
  "com.facebook.presto.sql.tree.StackableAstVisitor.process(StackableAstVisitor.java:26)"
  "com.facebook.presto.sql.analyzer.ExpressionAnalyzer$Visitor.process(ExpressionAnalyzer.java:346)"
  "com.facebook.presto.sql.analyzer.ExpressionAnalyzer$Visitor.coerceType(ExpressionAnalyzer.java:1356)"
  "com.facebook.presto.sql.analyzer.ExpressionAnalyzer$Visitor.visitLogicalBinaryExpression(ExpressionAnalyzer.java:505)"
  "com.facebook.presto.sql.analyzer.ExpressionAnalyzer$Visitor.visitLogicalBinaryExpression(ExpressionAnalyzer.java:323)"
  "com.facebook.presto.sql.tree.LogicalBinaryExpression.accept(LogicalBinaryExpression.java:88)"
  "com.facebook.presto.sql.tree.StackableAstVisitor.process(StackableAstVisitor.java:26)"
  "com.facebook.presto.sql.analyzer.ExpressionAnalyzer$Visitor.process(ExpressionAnalyzer.java:346)"
  "com.facebook.presto.sql.analyzer.ExpressionAnalyzer.analyze(ExpressionAnalyzer.java:289)"
  "com.facebook.presto.sql.analyzer.ExpressionAnalyzer.analyzeExpression(ExpressionAnalyzer.java:1605)"
  "com.facebook.presto.sql.analyzer.StatementAnalyzer$Visitor.analyzeExpression(StatementAnalyzer.java:2570)"
  "com.facebook.presto.sql.analyzer.StatementAnalyzer$Visitor.analyzeWhere(StatementAnalyzer.java:2417)"
  "com.facebook.presto.sql.analyzer.StatementAnalyzer$Visitor.lambda$visitQuerySpecification$19(StatementAnalyzer.java:1500)"
  "java.util.Optional.ifPresent(Optional.java:159)"
  "com.facebook.presto.sql.analyzer.StatementAnalyzer$Visitor.visitQuerySpecification(StatementAnalyzer.java:1500)"
  "com.facebook.presto.sql.analyzer.StatementAnalyzer$Visitor.visitQuerySpecification(StatementAnalyzer.java:320)"
  "com.facebook.presto.sql.tree.QuerySpecification.accept(QuerySpecification.java:138)"
  "com.facebook.presto.sql.tree.AstVisitor.process(AstVisitor.java:27)"
  "com.facebook.presto.sql.analyzer.StatementAnalyzer$Visitor.process(StatementAnalyzer.java:334)"
  "com.facebook.presto.sql.analyzer.StatementAnalyzer$Visitor.process(StatementAnalyzer.java:344)"
  "com.facebook.presto.sql.analyzer.StatementAnalyzer$Visitor.visitQuery(StatementAnalyzer.java:1067)"
  "com.facebook.presto.sql.analyzer.StatementAnalyzer$Visitor.visitQuery(StatementAnalyzer.java:320)"
  "com.facebook.presto.sql.tree.Query.accept(Query.java:105)"
  "com.facebook.presto.sql.tree.AstVisitor.process(AstVisitor.java:27)"
  "com.facebook.presto.sql.analyzer.StatementAnalyzer$Visitor.process(StatementAnalyzer.java:334)"
  "com.facebook.presto.sql.analyzer.StatementAnalyzer$Visitor.process(StatementAnalyzer.java:344)"
  "com.facebook.presto.sql.analyzer.StatementAnalyzer$Visitor.analyzeWith(StatementAnalyzer.java:2606)"
  "com.facebook.presto.sql.analyzer.StatementAnalyzer$Visitor.visitQuery(StatementAnalyzer.java:1066)"
  "com.facebook.presto.sql.analyzer.StatementAnalyzer$Visitor.visitQuery(StatementAnalyzer.java:320)"
  "com.facebook.presto.sql.tree.Query.accept(Query.java:105)"
  "com.facebook.presto.sql.tree.AstVisitor.process(AstVisitor.java:27)"
  "com.facebook.presto.sql.analyzer.StatementAnalyzer$Visitor.process(StatementAnalyzer.java:334)"
  "com.facebook.presto.sql.analyzer.StatementAnalyzer.analyze(StatementAnalyzer.java:312)"
  "com.facebook.presto.sql.analyzer.Analyzer.analyzeSemantic(Analyzer.java:89)"
  "com.facebook.presto.execution.SqlQueryExecution.<init>(SqlQueryExecution.java:187)"
  "com.facebook.presto.execution.SqlQueryExecution.<init>(SqlQueryExecution.java:95)"
  "com.facebook.presto.execution.SqlQueryExecution$SqlQueryExecutionFactory.createQueryExecution(SqlQueryExecution.java:789)"
  "com.facebook.presto.dispatcher.LocalDispatchQueryFactory.lambda$createDispatchQuery$0(LocalDispatchQueryFactory.java:123)"
  "com.google.common.util.concurrent.TrustedListenableFutureTask$TrustedFutureInterruptibleTask.runInterruptibly(TrustedListenableFutureTask.java:125)"
  "com.google.common.util.concurrent.InterruptibleTask.run(InterruptibleTask.java:57)"
  "com.google.common.util.concurrent.TrustedListenableFutureTask.run(TrustedListenableFutureTask.java:78)"
  "java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)"
  "java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)"
  "java.lang.Thread.run(Thread.java:748)"
  nil],
 :card_id nil,
 :context :ad-hoc,
 :error
 "Query failed (#20211117_040158_04915_j22ha): line 7:101: IN value and list items must be the same type: varchar",
 :row_count 0,
 :running_time 0,
 :data {:rows [], :cols []}}
2

Diagnostic Info:

{
  "browser-info": {
    "language": "pt-BR",
    "platform": "MacIntel",
    "userAgent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/95.0.4638.69 Safari/537.36",
    "vendor": "Google Inc."
  },
  "system-info": {
    "file.encoding": "UTF-8",
    "java.runtime.name": "OpenJDK Runtime Environment",
    "java.runtime.version": "11.0.12+7",
    "java.vendor": "Eclipse Foundation",
    "java.vendor.url": "https://adoptium.net/",
    "java.version": "11.0.12",
    "java.vm.name": "OpenJDK 64-Bit Server VM",
    "java.vm.version": "11.0.12+7",
    "os.name": "Linux",
    "os.version": "5.4.141-67.229.amzn2.x86_64",
    "user.language": "en",
    "user.timezone": "GMT"
  },
  "metabase-info": {
    "databases": [
      "presto-jdbc",
      "redshift"
    ],
    "hosting-env": "unknown",
    "application-database": "postgres",
    "application-database-details": {
      "database": {
        "name": "PostgreSQL",
        "version": "13.3"
      },
      "jdbc-driver": {
        "name": "PostgreSQL JDBC Driver",
        "version": "42.2.23"
      }
    },
    "run-mode": "prod",
    "version": {
      "date": "2021-10-21",
      "tag": "v0.41.1",
      "branch": "release-x.41.x",
      "hash": "76aa4a5"
    },
    "settings": {
      "report-timezone": null
    }
  }
}

If I remove the part
[[and {{poc_name}}]]
the query works
or if I remove the part
t1.created_datetime_utc > {{data}}
query works.

Them same query works on Redshift.

3

I could reproduce and solve the error on Presto.

PREPARE my_select2 FROM

with t1 as ( select * from database.fact_order

join database.dim_poc

on database.fact_order.poc_id = database.dim_poc.poc_id

where "database"."dim_poc"."poc_category" IN (?)

and "database"."dim_poc"."poc_name" IN (?))

select * from t1 where t1.created_datetime_utc > ?

limit 10

EXECUTE my_select2 USING 'DARK_STORE', 'Dark Rock Burger', DATE '2021-11-16'

How to solve the error:

EXECUTE my_select2 USING DATE '2021-11-16', 'DARK_STORE', 'Dark Rock Burger'

Metabase put the parameters on the wrong order

4

Hi @rubenssoto
I don't understand the problem. I'm guessing it's a bug in Presto, since that seems to have been the case the other times.

5

I don't think so... I will use a simpler query:

with t1 as (select * 
from database.fact_order
join database.dim_poc
on database.dim_poc.poc_id = database.fact_order.poc_id
where {{poc_category}})

select * from t1
where created_date_brt = {{data}}
limit 10

This query I had the following error:
Query failed (#20211117_192858_00174_y3a85): line 7:53: IN value and list items must be the same type: varchar

On Presto UI I see the following execution statement:
EXECUTE statement8 USING 'BAKERY', DATE '2021-11-16'

Running the same statment on dbeaver but passing the date value first, the query works.

This way, it worked:
EXECUTE statement8 USING DATE '2021-11-16', 'BAKERY'

6

@rubenssoto If you have an issue, then write steps-to-reproduce using one of the public available sample datasets included in many of the Presto releases. Then it's much easier to test:
It's working fine, when I test:

image
image1920×970 66 KB

7

@flamber
The problem is on Presto datasource, bit it is not a Presto Bug, on redshift it works. The parameters of outside query should be passed first.

Im so sorry, english is not my first language so probably Im having some difficult to expressing myself.

with t1 as (select *
from database.fact_order
join database.dim_poc
on database.dim_poc.poc_id = database.fact_order.poc_id
where {{poc_category}})

select * from t1
where created_date_brt = {{data}}
limit 10

For example this query, the parameter data, must be passed first.

8

@rubenssoto Did you look at my query?
I would recommend that you get a hold of a friend who's great at English and then create a proper issue, which contains all the details requested - including steps-to-reproduce and that doesn't mean your own queries/datasets, but something that anyone can test with.

9

Sorry I didn't realized that you are using Presto, I will do the same tests on a public dataset.

10

@flamber
Which PrestoDb version and which metabase version are you using?

11

@rubenssoto Metabase 0.41.2 with the new driver for Presto against a Trino 358

12

But Im using PrestoDB is different from Trino.

13

@rubenssoto Yes, I know, but it's the same driver used in Metabase, so the bug must then be in Presto.

14

@flamber I will ask presto folks to understand if it is a expected behavior or not.

15

@flamber
It is another bug man...

Two bugs same week :frowning:

16

@rubenssoto Please include a link to the bug, since others might want to follow it too.

17

@flamber sure...

There is a workaround, don't use variables on the last query, when you have CTA.

So, to solve this query:

with t1 as (select *
from database.fact_order
join database.dim_poc
on database.dim_poc.poc_id = database.fact_order.poc_id
where {{poc_category}})

select * from t1
where created_date_brt = {{data}}
limit 10

write like this:

with t1 as (select *
from database.fact_order
join database.dim_poc
on database.dim_poc.poc_id = database.fact_order.poc_id
where {{poc_category}}),

t1 as(select * from t1
where created_date_brt = {{data}})

select * From t1

1 Like