Field filter causing error in a cross tab query

himanshugupta9690 · April 30, 2021, 2:36pm

Hi Everyone

I am trying to run a query with crosstab syntax (Postgres) in our database (You can have look at query in the end). The results are perfect till the time I don't select a field filter.

However, as soon as I select a field filter, I am getting the error "The column index is out of range: 1, number of columns: 0.".

Not sure what is causing this error. Thanks in advance for helping here.

The query looks like this:

SELECT *
FROM crosstab($$select
businessunit,
customername,
case when due_days <= 30 then 'days_lt_30'
when due_days <= 60 then 'days_30_60'
when due_days <= 90 then 'days_60_90'
when due_days <= 180 then 'days_90_180'
else 'days_gt_180' end as due_bucket,
sum(due_amount) as due_amount
from mart_invoice
where 1=1
[[AND {{BU}}]]
group by case when due_days <= 30 then 'days_lt_30'
when due_days <= 60 then 'days_30_60'
when due_days <= 90 then 'days_60_90'
when due_days <= 180 then 'days_90_180'
else 'days_gt_180' end,
businessunit,
customername$$,
$$ Select distinct due_bucket
from
((select case when due_days <= 30 then 'days_lt_30'
when due_days <= 60 then 'days_30_60'
when due_days <= 90 then 'days_60_90'
when due_days <= 180 then 'days_90_180'
else 'days_gt_180' end as due_bucket,
businessunit,
customername,
contract,

sum(due_amount) as due_amount

from mart_invoice

group by case when due_days <= 30 then 'days_lt_30'
when due_days <= 60 then 'days_30_60'
when due_days <= 90 then 'days_60_90'
when due_days <= 180 then 'days_90_180'
else 'days_gt_180' end,
businessunit,
customername,
contract
order by due_bucket)
union all
(select 'days_30_60' as due_bucket,NULL as businessunit,NULL as customername,NULL as contract, NULL as due_amount)
union all
(select 'days_60_90' as due_bucket,NULL as businessunit,NULL as customername,NULL as contract, NULL as due_amount )
union all
(select 'days_90_180' as due_bucket,NULL as businessunit,NULL as customername,NULL as contract, NULL as due_amount)
union all
(select 'days_gt_180' as due_bucket,NULL as businessunit,NULL as customername,NULL as contract, NULL as due_amount)
union all
(select 'days_lt_30' as due_bucket,NULL as businessunit,NULL as customername,NULL as contract, NULL as due_amount)
)a
order by due_bucket
$$)
AS ct(businessunit TEXT, customername TEXT, contract TEXT, days_30_60 NUMERIC,days_60_90 NUMERIC,days_90_180 NUMERIC,days_gt_180 NUMERIC ,days_lt_30 NUMERIC)

flamber · April 30, 2021, 2:58pm

Hi @himanshugupta9690
Post "Diagnostic Info" from Admin > Troubleshooting.
You're writing opposite things - does the query when you have the filter defined or not?
Check the log for the full stacktrace - Admin > Troubleshooting > Logs - or check the Postgres debug log, which might provide more insight.

himanshugupta9690 · May 3, 2021, 11:48am

Hi Flamber,

I have defined the field filter as you can see [[AND {{BU}}]]. However, the query runs fine only if I don't make a selection in the filter. As soon as I make a selection in the filter, I get this error:

"The column index is out of range: 1, number of columns: 0."

Also the filtering works fine if I don't make it a part of a crosstab query. Is there something in the crosstab syntax that I need to follow so that filters/variables work correctly?

flamber · May 3, 2021, 11:56am

@himanshugupta9690

Post "Diagnostic Info" from Admin > Troubleshooting.
Check the log for the full stacktrace - Admin > Troubleshooting > Logs
Check the Postgres debug log, which might provide more insight

When using a Field Filter, then it generates the SQL based on column reference defined in the sidebar and the values selected.
For reference: https://www.metabase.com/learn/building-analytics/sql-templates/field-filters.html

himanshugupta9690 · May 3, 2021, 11:57am

Found something on the logs, however could not make sense of it:

[86195d50-416b-4334-9b2f-5f1d56752d5d] 2021-05-03T17:19:11+05:30 DEBUG metabase.server.middleware.log POST /api/card/460/query 202 [ASYNC: completed] 164.4 ms (9 DB calls) App DB connections: 1/15 Jetty threads: 2/50 (12 idle, 0 queued) (134 total active threads) Queries in flight: 0 (0 queued)
[86195d50-416b-4334-9b2f-5f1d56752d5d] 2021-05-03T17:19:16+05:30 ERROR metabase.query-processor.middleware.catch-exceptions Error processing query: null
{:database_id 3,
:started_at #t "2021-05-03T11:49:16.466Z[Etc/UTC]",
:state "22023",
:json_query
{:constraints {:max-results 10000, :max-results-bare-rows 2000},
:type :native,
:middleware {:js-int-to-string? true},
:native
{:template-tags
{"BU" {:id "81c3b5e5-b4e2-5281-fcbb-303e0ec087e2", :name "BU", :display-name "Bu", :type :dimension, :dimension [:field-id 19101], :widget-type :category, :default nil},
"Customer" {:id "0ef9195d-150f-f341-1947-75fe260a0b01", :name "Customer", :display-name "Customer", :type :dimension, :dimension [:field-id 19097], :default nil, :widget-type :category},
"Contract" {:id "b711d627-4a51-5cce-9454-8290cde59bcf", :name "Contract", :display-name "Contract", :type :dimension, :dimension [:field-id 19104], :widget-type :category, :default nil}},
:query
"SELECT businessunit,\nsum(coalesce(days_lt_30,0)) as days_lt_30,\nsum(coalesce(days_30_60,0)) as days_30_60,\nsum(coalesce(days_60_90,0)) as days_60_90,\nsum(coalesce(days_90_180,0)) as days_90_180,\nsum(coalesce(days_gt_180,0)) as days_gt_180,\nsum(coalesce(days_30_60,0)+coalesce(days_60_90,0)+coalesce(days_90_180,0)+coalesce(days_gt_180,0)+coalesce(days_lt_30,0)) as total\nFROM crosstab($$select \nbusinessunit,\ncustomername,\ncontract,\ncase when due_days <= 30 then 'days_lt_30'\n when due_days <= 60 then 'days_30_60'\n when due_days <= 90 then 'days_60_90'\n when due_days <= 180 then 'days_90_180'\n else 'days_gt_180' end as due_bucket,\nsum(due_amount) as due_amount\n\nfrom mart_invoice \nwhere 1=1\n[[AND {{BU}}]]\n[[AND {{Customer}}]]\n[[AND {{Contract}}]]\ngroup by case when due_days <= 30 then 'days_lt_30'\n when due_days <= 60 then 'days_30_60'\n when due_days <= 90 then 'days_60_90'\n when due_days <= 180 then 'days_90_180'\n else 'days_gt_180' end,\n businessunit,\ncustomername,\ncontract $$, \n$$ Select distinct due_bucket\nfrom\n((select case when due_days <= 30 then 'days_lt_30'\n when due_days <= 60 then 'days_30_60'\n when due_days <= 90 then 'days_60_90'\n when due_days <= 180 then 'days_90_180'\n else 'days_gt_180' end as due_bucket,\nbusinessunit,\ncustomername,\ncontract,\n\nsum(due_amount) as due_amount\n\nfrom mart_invoice \n\ngroup by case when due_days <= 30 then 'days_lt_30'\n when due_days <= 60 then 'days_30_60'\n when due_days <= 90 then 'days_60_90'\n when due_days <= 180 then 'days_90_180'\n else 'days_gt_180' end,\n businessunit,\ncustomername,\ncontract\norder by due_bucket)\nunion all\n(select 'days_30_60' as due_bucket,NULL as businessunit,NULL as customername,NULL as contract, NULL as due_amount)\nunion all\n(select 'days_60_90' as due_bucket,NULL as businessunit,NULL as customername,NULL as contract, NULL as due_amount )\nunion all\n(select 'days_90_180' as due_bucket,NULL as businessunit,NULL as customername,NULL as contract, NULL as due_amount)\nunion all\n(select 'days_gt_180' as due_bucket,NULL as businessunit,NULL as customername,NULL as contract, NULL as due_amount)\nunion all\n(select 'days_lt_30' as due_bucket,NULL as businessunit,NULL as customername,NULL as contract, NULL as due_amount)\n )a\norder by due_bucket \n $$)\n AS ct(businessunit TEXT, customername TEXT, contract TEXT, days_30_60 NUMERIC,days_60_90 NUMERIC,days_90_180 NUMERIC,days_gt_180 NUMERIC ,days_lt_30 NUMERIC)\n group by businessunit\n having sum(coalesce(days_30_60,0)+coalesce(days_60_90,0)+coalesce(days_90_180,0)+coalesce(days_gt_180,0)+coalesce(days_lt_30,0))>0\norder by total desc"},
:database 3,
:parameters [{:type "category", :target ["dimension" ["template-tag" "BU"]], :value ["Consumer Goods"]}],
:async? true,
:cache-ttl nil},
:status :failed,
:class org.postgresql.util.PSQLException,
:stacktrace
["org.postgresql.core.v3.SimpleParameterList.bind(SimpleParameterList.java:69)"
"org.postgresql.core.v3.SimpleParameterList.setStringParameter(SimpleParameterList.java:132)"
"org.postgresql.jdbc.PgPreparedStatement.bindString(PgPreparedStatement.java:1060)"
"org.postgresql.jdbc.PgPreparedStatement.setString(PgPreparedStatement.java:356)"
"org.postgresql.jdbc.PgPreparedStatement.setString(PgPreparedStatement.java:342)"
"org.postgresql.jdbc.PgPreparedStatement.setObject(PgPreparedStatement.java:950)"
"com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.setObject(NewProxyPreparedStatement.java:1008)"
"--> driver.sql_jdbc.execute$set_object.invokeStatic(execute.clj:178)"
"driver.sql_jdbc.execute$set_object.invoke(execute.clj:175)"
"driver.sql_jdbc.execute$fn__77359.invokeStatic(execute.clj:187)"
"driver.sql_jdbc.execute$fn__77359.invoke(execute.clj:185)"
"driver.sql_jdbc.execute$set_parameters_BANG_$fn__77377.invoke(execute.clj:231)"
"driver.sql_jdbc.execute$set_parameters_BANG_.invokeStatic(execute.clj:227)"
"driver.sql_jdbc.execute$set_parameters_BANG_.invoke(execute.clj:223)"
"driver.sql_jdbc.execute$fn__77381.invokeStatic(execute.clj:245)"
"driver.sql_jdbc.execute$fn__77381.invoke(execute.clj:234)"
"driver.sql_jdbc.execute$prepared_statement_STAR_.invokeStatic(execute.clj:254)"
"driver.sql_jdbc.execute$prepared_statement_STAR_.invoke(execute.clj:251)"
"driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:387)"
"driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:374)"
"driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:383)"
"driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:374)"
"driver.sql_jdbc$fn__78967.invokeStatic(sql_jdbc.clj:54)"
"driver.sql_jdbc$fn__78967.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__46313.invoke(mbql_to_native.clj:25)"
"query_processor.middleware.check_features$check_features$fn__45589.invoke(check_features.clj:41)"
"query_processor.middleware.limit$limit$fn__46299.invoke(limit.clj:37)"
"query_processor.middleware.cache$maybe_return_cached_results$fn__45240.invoke(cache.clj:211)"
"query_processor.middleware.optimize_datetime_filters$optimize_datetime_filters$fn__46478.invoke(optimize_datetime_filters.clj:133)"
"query_processor.middleware.auto_parse_filter_values$auto_parse_filter_values$fn__44386.invoke(auto_parse_filter_values.clj:43)"
"query_processor.middleware.wrap_value_literals$wrap_value_literals$fn__40767.invoke(wrap_value_literals.clj:147)"
"query_processor.middleware.annotate$add_column_info$fn__40630.invoke(annotate.clj:582)"
"query_processor.middleware.permissions$check_query_permissions$fn__45464.invoke(permissions.clj:69)"
"query_processor.middleware.pre_alias_aggregations$pre_alias_aggregations$fn__47001.invoke(pre_alias_aggregations.clj:40)"
"query_processor.middleware.cumulative_aggregations$handle_cumulative_aggregations$fn__45662.invoke(cumulative_aggregations.clj:60)"
"query_processor.middleware.resolve_joined_fields$resolve_joined_fields$fn__47314.invoke(resolve_joined_fields.clj:94)"
"query_processor.middleware.resolve_joins$resolve_joins$fn__47619.invoke(resolve_joins.clj:178)"
"query_processor.middleware.add_implicit_joins$add_implicit_joins$fn__43976.invoke(add_implicit_joins.clj:181)"
"query_processor.middleware.large_int_id$convert_id_to_string$fn__46274.invoke(large_int_id.clj:44)"
"query_processor.middleware.format_rows$format_rows$fn__46254.invoke(format_rows.clj:74)"
"query_processor.middleware.desugar$desugar$fn__45728.invoke(desugar.clj:21)"
"query_processor.middleware.binning$update_binning_strategy$fn__44746.invoke(binning.clj:228)"
"query_processor.middleware.resolve_fields$resolve_fields$fn__45264.invoke(resolve_fields.clj:24)"
"query_processor.middleware.add_dimension_projections$add_remapping$fn__43606.invoke(add_dimension_projections.clj:314)"
"query_processor.middleware.add_implicit_clauses$add_implicit_clauses$fn__43837.invoke(add_implicit_clauses.clj:146)"
"query_processor.middleware.upgrade_field_literals$upgrade_field_literals$fn__48049.invoke(upgrade_field_literals.clj:45)"
"query_processor.middleware.add_source_metadata$add_source_metadata_for_source_queries$fn__44133.invoke(add_source_metadata.clj:124)"
"query_processor.middleware.reconcile_breakout_and_order_by_bucketing$reconcile_breakout_and_order_by_bucketing$fn__47198.invoke(reconcile_breakout_and_order_by_bucketing.clj:97)"
"query_processor.middleware.auto_bucket_datetimes$auto_bucket_datetimes$fn__44333.invoke(auto_bucket_datetimes.clj:139)"
"query_processor.middleware.resolve_source_table$resolve_source_tables$fn__45311.invoke(resolve_source_table.clj:45)"
"query_processor.middleware.parameters$substitute_parameters$fn__46983.invoke(parameters.clj:111)"
"query_processor.middleware.resolve_referenced$resolve_referenced_card_resources$fn__45363.invoke(resolve_referenced.clj:79)"
"query_processor.middleware.expand_macros$expand_macros$fn__45984.invoke(expand_macros.clj:155)"
"query_processor.middleware.add_timezone_info$add_timezone_info$fn__44142.invoke(add_timezone_info.clj:15)"
"query_processor.middleware.splice_params_in_response$splice_params_in_response$fn__47985.invoke(splice_params_in_response.clj:32)"
"query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__47209$fn__47213.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__47209.invoke(resolve_database_and_driver.clj:25)"
"query_processor.middleware.fetch_source_query$resolve_card_id_source_tables$fn__46202.invoke(fetch_source_query.clj:264)"
"query_processor.middleware.store$initialize_store$fn__47994$fn__47995.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__47994.invoke(store.clj:10)"
"query_processor.middleware.validate$validate_query$fn__48056.invoke(validate.clj:10)"
"query_processor.middleware.normalize_query$normalize$fn__46326.invoke(normalize_query.clj:22)"
"query_processor.middleware.add_rows_truncated$add_rows_truncated$fn__43994.invoke(add_rows_truncated.clj:35)"
"query_processor.middleware.results_metadata$record_and_return_metadata_BANG_$fn__47970.invoke(results_metadata.clj:147)"
"query_processor.middleware.constraints$add_default_userland_constraints$fn__45605.invoke(constraints.clj:42)"
"query_processor.middleware.process_userland_query$process_userland_query$fn__47072.invoke(process_userland_query.clj:135)"
"query_processor.middleware.catch_exceptions$catch_exceptions$fn__45548.invoke(catch_exceptions.clj:173)"
"query_processor.reducible$async_qp$qp_STAR___37408$thunk__37409.invoke(reducible.clj:103)"
"query_processor.reducible$async_qp$qp_STAR___37408$fn__37411.invoke(reducible.clj:108)"],
:context :question,
:error "The column index is out of range: 1, number of columns: 0.",
:row_count 0,
:running_time 0,
:data {:rows [], :cols []}}

himanshugupta9690 · May 7, 2021, 3:23pm

@flamber : anything you are able to decipher from these logs?

flamber · May 7, 2021, 3:48pm

@himanshugupta9690 I'm not sure what's going on. Have you checked the Postgres database logs to see the actual query it tries to run and what the return/error says there?
Does it work if you use two simple Date filters instead of a Field Filter?

himanshugupta9690 · May 7, 2021, 4:11pm

@flamber can you tell how to access Postgres database logs?

flamber · May 7, 2021, 4:31pm

@himanshugupta9690 If you don't control the Postgres, then you need the administrator to help you:
https://stackoverflow.com/questions/722221/how-to-log-postgresql-queries