search

metabase / metabase

The simplest, fastest way to get business intelligence and analytics to everyone in your company :yum:
https://metabase.com
Other
38.32k stars 5.08k forks source link

Metabase unable to render bit(16) columns when querying against a Postgres DB #15307

Open ghost opened 3 years ago

ghost commented 3 years ago

Describe the bug

When querying against a Postgres database with a column of type bit(16), Metabase throws an error like so Cannot cast to boolean: "0101100000101100". If the column is null, the query is run successfully and the table is rendered.

Logs

Server Logs:

Click to expand! ``` 2021-03-23 17:19:55,548 DEBUG middleware.log :: POST /api/dataset 202 [ASYNC: completed] 49.5 ms (4 DB calls) App DB connections: 0/4 Jetty threads: 4/50 (2 idle, 0 queued) (60 total active threads) Queries in flight: 0 (0 queued) 2021-03-23 17:25:46,167 ERROR middleware.catch-exceptions :: Error processing query: null {:database_id 2, :started_at #t "2021-03-23T17:25:45.022824Z[GMT]", :via [{:status :failed, :class clojure.lang.ExceptionInfo, :error "Error reducing result rows", :stacktrace ["--> query_processor.context.default$default_reducef$fn__36878.invoke(default.clj:60)" "query_processor.context.default$default_reducef.invokeStatic(default.clj:57)" "query_processor.context.default$default_reducef.invoke(default.clj:48)" "query_processor.context$reducef.invokeStatic(context.clj:69)" "query_processor.context$reducef.invoke(context.clj:62)" "query_processor.context.default$default_runf$respond_STAR___36882.invoke(default.clj:69)" "driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:398)" "driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:375)" "driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:384)" "driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:375)" "driver.sql_jdbc$fn__79191.invokeStatic(sql_jdbc.clj:54)" "driver.sql_jdbc$fn__79191.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__46455.invoke(mbql_to_native.clj:25)" "query_processor.middleware.check_features$check_features$fn__45707.invoke(check_features.clj:41)" "query_processor.middleware.limit$limit$fn__46441.invoke(limit.clj:37)" "query_processor.middleware.cache$maybe_return_cached_results$fn__45365.invoke(cache.clj:211)" "query_processor.middleware.optimize_datetime_filters$optimize_datetime_filters$fn__46620.invoke(optimize_datetime_filters.clj:133)" "query_processor.middleware.auto_parse_filter_values$auto_parse_filter_values$fn__44511.invoke(auto_parse_filter_values.clj:43)" "query_processor.middleware.wrap_value_literals$wrap_value_literals$fn__40828.invoke(wrap_value_literals.clj:147)" "query_processor.middleware.annotate$add_column_info$fn__40691.invoke(annotate.clj:582)" "query_processor.middleware.permissions$check_query_permissions$fn__45582.invoke(permissions.clj:75)" "query_processor.middleware.pre_alias_aggregations$pre_alias_aggregations$fn__47143.invoke(pre_alias_aggregations.clj:40)" "query_processor.middleware.cumulative_aggregations$handle_cumulative_aggregations$fn__45780.invoke(cumulative_aggregations.clj:60)" "query_processor.middleware.resolve_joined_fields$resolve_joined_fields$fn__47456.invoke(resolve_joined_fields.clj:94)" "query_processor.middleware.resolve_joins$resolve_joins$fn__47761.invoke(resolve_joins.clj:178)" "query_processor.middleware.add_implicit_joins$add_implicit_joins$fn__44105.invoke(add_implicit_joins.clj:181)" "query_processor.middleware.large_int_id$convert_id_to_string$fn__46416.invoke(large_int_id.clj:44)" "query_processor.middleware.format_rows$format_rows$fn__46396.invoke(format_rows.clj:74)" "query_processor.middleware.desugar$desugar$fn__45846.invoke(desugar.clj:21)" "query_processor.middleware.binning$update_binning_strategy$fn__44871.invoke(binning.clj:228)" "query_processor.middleware.resolve_fields$resolve_fields$fn__45389.invoke(resolve_fields.clj:24)" "query_processor.middleware.add_dimension_projections$add_remapping$fn__43735.invoke(add_dimension_projections.clj:314)" "query_processor.middleware.add_implicit_clauses$add_implicit_clauses$fn__43966.invoke(add_implicit_clauses.clj:146)" "query_processor.middleware.upgrade_field_literals$upgrade_field_literals$fn__48191.invoke(upgrade_field_literals.clj:45)" "query_processor.middleware.add_source_metadata$add_source_metadata_for_source_queries$fn__44258.invoke(add_source_metadata.clj:122)" "query_processor.middleware.reconcile_breakout_and_order_by_bucketing$reconcile_breakout_and_order_by_bucketing$fn__47340.invoke(reconcile_breakout_and_order_by_bucketing.clj:97)" "query_processor.middleware.auto_bucket_datetimes$auto_bucket_datetimes$fn__44458.invoke(auto_bucket_datetimes.clj:139)" "query_processor.middleware.resolve_source_table$resolve_source_tables$fn__45436.invoke(resolve_source_table.clj:45)" "query_processor.middleware.parameters$substitute_parameters$fn__47125.invoke(parameters.clj:111)" "query_processor.middleware.resolve_referenced$resolve_referenced_card_resources$fn__45488.invoke(resolve_referenced.clj:79)" "query_processor.middleware.expand_macros$expand_macros$fn__46102.invoke(expand_macros.clj:155)" "query_processor.middleware.add_timezone_info$add_timezone_info$fn__44267.invoke(add_timezone_info.clj:15)" "query_processor.middleware.splice_params_in_response$splice_params_in_response$fn__48127.invoke(splice_params_in_response.clj:32)" "query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__47351$fn__47355.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__47351.invoke(resolve_database_and_driver.clj:25)" "query_processor.middleware.fetch_source_query$resolve_card_id_source_tables$fn__46342.invoke(fetch_source_query.clj:274)" "query_processor.middleware.store$initialize_store$fn__48136$fn__48137.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__48136.invoke(store.clj:10)" "query_processor.middleware.validate$validate_query$fn__48198.invoke(validate.clj:10)" "query_processor.middleware.normalize_query$normalize$fn__46468.invoke(normalize_query.clj:22)" "query_processor.middleware.add_rows_truncated$add_rows_truncated$fn__44123.invoke(add_rows_truncated.clj:35)" "query_processor.middleware.results_metadata$record_and_return_metadata_BANG_$fn__48112.invoke(results_metadata.clj:147)" "query_processor.middleware.constraints$add_default_userland_constraints$fn__45723.invoke(constraints.clj:42)" "query_processor.middleware.process_userland_query$process_userland_query$fn__47214.invoke(process_userland_query.clj:135)" "query_processor.middleware.catch_exceptions$catch_exceptions$fn__45666.invoke(catch_exceptions.clj:173)" "query_processor.reducible$async_qp$qp_STAR___37431$thunk__37432.invoke(reducible.clj:103)" "query_processor.reducible$async_qp$qp_STAR___37431.invoke(reducible.clj:109)" "query_processor.reducible$sync_qp$qp_STAR___37440$fn__37443.invoke(reducible.clj:135)" "query_processor.reducible$sync_qp$qp_STAR___37440.invoke(reducible.clj:134)" "query_processor$process_userland_query.invokeStatic(query_processor.clj:237)" "query_processor$process_userland_query.doInvoke(query_processor.clj:233)" "query_processor$fn__48244$process_query_and_save_execution_BANG___48253$fn__48256.invoke(query_processor.clj:249)" "query_processor$fn__48244$process_query_and_save_execution_BANG___48253.invoke(query_processor.clj:241)" "query_processor$fn__48288$process_query_and_save_with_max_results_constraints_BANG___48297$fn__48300.invoke(query_processor.clj:261)" "query_processor$fn__48288$process_query_and_save_with_max_results_constraints_BANG___48297.invoke(query_processor.clj:254)" "api.dataset$run_query_async$fn__54402.invoke(dataset.clj:56)" "query_processor.streaming$streaming_response_STAR_$fn__54381$fn__54382.invoke(streaming.clj:72)" "query_processor.streaming$streaming_response_STAR_$fn__54381.invoke(streaming.clj:71)" "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__16058.invoke(streaming_response.clj:84)"], :error_type :qp, :ex-data {:type :qp}}], :state "42846", :error_type :qp, :json_query {:type "native", :native {:query "SELECT bit_16_column FROM table_with_bits", :template-tags {}}, :database 2, :parameters [], :middleware {:js-int-to-string? true, :add-default-userland-constraints? true}}, :status :failed, :class org.postgresql.util.PSQLException, :stacktrace ["org.postgresql.jdbc.BooleanTypeUtil.cannotCoerceException(BooleanTypeUtil.java:99)" "org.postgresql.jdbc.BooleanTypeUtil.fromString(BooleanTypeUtil.java:67)" "org.postgresql.jdbc.BooleanTypeUtil.castToBoolean(BooleanTypeUtil.java:43)" "org.postgresql.jdbc.PgResultSet.getBoolean(PgResultSet.java:2088)" "org.postgresql.jdbc.PgResultSet.internalGetObject(PgResultSet.java:182)" "org.postgresql.jdbc.PgResultSet.getObject(PgResultSet.java:2726)" "com.mchange.v2.c3p0.impl.NewProxyResultSet.getObject(NewProxyResultSet.java:165)" "--> driver.postgres$fn__78558$fn__78559.invoke(postgres.clj:363)" "driver.sql_jdbc.execute$row_thunk$row_thunk_STAR___77740.invoke(execute.clj:346)" "query_processor.reducible$reducible_rows$reify__37447.reduce(reducible.clj:160)" "query_processor.context.default$default_reducef$fn__36878.invoke(default.clj:58)" "query_processor.context.default$default_reducef.invokeStatic(default.clj:57)" "query_processor.context.default$default_reducef.invoke(default.clj:48)" "query_processor.context$reducef.invokeStatic(context.clj:69)" "query_processor.context$reducef.invoke(context.clj:62)" "query_processor.context.default$default_runf$respond_STAR___36882.invoke(default.clj:69)" "driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:398)" "driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:375)" "driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:384)" "driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:375)" "driver.sql_jdbc$fn__79191.invokeStatic(sql_jdbc.clj:54)" "driver.sql_jdbc$fn__79191.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__46455.invoke(mbql_to_native.clj:25)" "query_processor.middleware.check_features$check_features$fn__45707.invoke(check_features.clj:41)" "query_processor.middleware.limit$limit$fn__46441.invoke(limit.clj:37)" "query_processor.middleware.cache$maybe_return_cached_results$fn__45365.invoke(cache.clj:211)" "query_processor.middleware.optimize_datetime_filters$optimize_datetime_filters$fn__46620.invoke(optimize_datetime_filters.clj:133)" "query_processor.middleware.auto_parse_filter_values$auto_parse_filter_values$fn__44511.invoke(auto_parse_filter_values.clj:43)" "query_processor.middleware.wrap_value_literals$wrap_value_literals$fn__40828.invoke(wrap_value_literals.clj:147)" "query_processor.middleware.annotate$add_column_info$fn__40691.invoke(annotate.clj:582)" "query_processor.middleware.permissions$check_query_permissions$fn__45582.invoke(permissions.clj:75)" "query_processor.middleware.pre_alias_aggregations$pre_alias_aggregations$fn__47143.invoke(pre_alias_aggregations.clj:40)" "query_processor.middleware.cumulative_aggregations$handle_cumulative_aggregations$fn__45780.invoke(cumulative_aggregations.clj:60)" "query_processor.middleware.resolve_joined_fields$resolve_joined_fields$fn__47456.invoke(resolve_joined_fields.clj:94)" "query_processor.middleware.resolve_joins$resolve_joins$fn__47761.invoke(resolve_joins.clj:178)" "query_processor.middleware.add_implicit_joins$add_implicit_joins$fn__44105.invoke(add_implicit_joins.clj:181)" "query_processor.middleware.large_int_id$convert_id_to_string$fn__46416.invoke(large_int_id.clj:44)" "query_processor.middleware.format_rows$format_rows$fn__46396.invoke(format_rows.clj:74)" "query_processor.middleware.desugar$desugar$fn__45846.invoke(desugar.clj:21)" "query_processor.middleware.binning$update_binning_strategy$fn__44871.invoke(binning.clj:228)" "query_processor.middleware.resolve_fields$resolve_fields$fn__45389.invoke(resolve_fields.clj:24)" "query_processor.middleware.add_dimension_projections$add_remapping$fn__43735.invoke(add_dimension_projections.clj:314)" "query_processor.middleware.add_implicit_clauses$add_implicit_clauses$fn__43966.invoke(add_implicit_clauses.clj:146)" "query_processor.middleware.upgrade_field_literals$upgrade_field_literals$fn__48191.invoke(upgrade_field_literals.clj:45)" "query_processor.middleware.add_source_metadata$add_source_metadata_for_source_queries$fn__44258.invoke(add_source_metadata.clj:122)" "query_processor.middleware.reconcile_breakout_and_order_by_bucketing$reconcile_breakout_and_order_by_bucketing$fn__47340.invoke(reconcile_breakout_and_order_by_bucketing.clj:97)" "query_processor.middleware.auto_bucket_datetimes$auto_bucket_datetimes$fn__44458.invoke(auto_bucket_datetimes.clj:139)" "query_processor.middleware.resolve_source_table$resolve_source_tables$fn__45436.invoke(resolve_source_table.clj:45)" "query_processor.middleware.parameters$substitute_parameters$fn__47125.invoke(parameters.clj:111)" "query_processor.middleware.resolve_referenced$resolve_referenced_card_resources$fn__45488.invoke(resolve_referenced.clj:79)" "query_processor.middleware.expand_macros$expand_macros$fn__46102.invoke(expand_macros.clj:155)" "query_processor.middleware.add_timezone_info$add_timezone_info$fn__44267.invoke(add_timezone_info.clj:15)" "query_processor.middleware.splice_params_in_response$splice_params_in_response$fn__48127.invoke(splice_params_in_response.clj:32)" "query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__47351$fn__47355.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__47351.invoke(resolve_database_and_driver.clj:25)" "query_processor.middleware.fetch_source_query$resolve_card_id_source_tables$fn__46342.invoke(fetch_source_query.clj:274)" "query_processor.middleware.store$initialize_store$fn__48136$fn__48137.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__48136.invoke(store.clj:10)" "query_processor.middleware.validate$validate_query$fn__48198.invoke(validate.clj:10)" "query_processor.middleware.normalize_query$normalize$fn__46468.invoke(normalize_query.clj:22)" "query_processor.middleware.add_rows_truncated$add_rows_truncated$fn__44123.invoke(add_rows_truncated.clj:35)" "query_processor.middleware.results_metadata$record_and_return_metadata_BANG_$fn__48112.invoke(results_metadata.clj:147)" "query_processor.middleware.constraints$add_default_userland_constraints$fn__45723.invoke(constraints.clj:42)" "query_processor.middleware.process_userland_query$process_userland_query$fn__47214.invoke(process_userland_query.clj:135)" "query_processor.middleware.catch_exceptions$catch_exceptions$fn__45666.invoke(catch_exceptions.clj:173)" "query_processor.reducible$async_qp$qp_STAR___37431$thunk__37432.invoke(reducible.clj:103)" "query_processor.reducible$async_qp$qp_STAR___37431.invoke(reducible.clj:109)" "query_processor.reducible$sync_qp$qp_STAR___37440$fn__37443.invoke(reducible.clj:135)" "query_processor.reducible$sync_qp$qp_STAR___37440.invoke(reducible.clj:134)" "query_processor$process_userland_query.invokeStatic(query_processor.clj:237)" "query_processor$process_userland_query.doInvoke(query_processor.clj:233)" "query_processor$fn__48244$process_query_and_save_execution_BANG___48253$fn__48256.invoke(query_processor.clj:249)" "query_processor$fn__48244$process_query_and_save_execution_BANG___48253.invoke(query_processor.clj:241)" "query_processor$fn__48288$process_query_and_save_with_max_results_constraints_BANG___48297$fn__48300.invoke(query_processor.clj:261)" "query_processor$fn__48288$process_query_and_save_with_max_results_constraints_BANG___48297.invoke(query_processor.clj:254)" "api.dataset$run_query_async$fn__54402.invoke(dataset.clj:56)" "query_processor.streaming$streaming_response_STAR_$fn__54381$fn__54382.invoke(streaming.clj:72)" "query_processor.streaming$streaming_response_STAR_$fn__54381.invoke(streaming.clj:71)" "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__16058.invoke(streaming_response.clj:84)"], :context :ad-hoc, :error "Cannot cast to boolean: \"0101100000101100\"", :row_count 0, :running_time 0, :data {:rows [], :cols []}} 2021-03-23 17:25:46,182 DEBUG middleware.log :: POST /api/dataset 202 [ASYNC: completed] 1.2 s (4 DB calls) App DB connections: 0/4 Jetty threads: 4/50 (2 idle, 0 queued) (62 total active threads) Queries in flight: 0 (0 queued) ```

Javascript console logs:

error Cannot cast to boolean: "0101100000101100"

To Reproduce Steps to reproduce the behavior:

  1. Create a Postgres table with a bit(16) column type 
    create table table_with_bits
(
id serial not null constraint table_name_pk primary key,
bit_16_column bit(16)
);
  2. Insert a row with data into the table 
    INSERT INTO table_with_bits (id, bit_16_column) VALUES (1, '0101100000101100');
  3. Use Metabase to write and run a query against this table 
    SELECT * FROM table_with_bits
  4. See error

Expected behavior

The table with the bit(16) data is rendered correctly. The data should probably render as text.

Screenshots

localhost_3000_question

Information about your Metabase Installation:

Severity

This bug is blocking a small number of our users of Metabase as they get this error when querying against one of the tables in our DB. There is a workaround which is to cast each of these fields to a text explicitly, in which Metabase renders them correctly.

flamber commented 3 years ago

Quicker way of reproducing on Postgres: select * from (values ('bit16'::text, '0101100000101100'::bit(16))) as x("a", "b")

Not sure if this is specific to Postgres, but MySQL handles it better, since the query works, but shows the output as hex - #9649

Related to #13536, since the QB gets a blocking overlay, which makes it harder to get the actual error.