Oracle fails queries on joins with tables with long display names #15978

flamber · 2021-05-09T13:07:56Z

Describe the bug
On Oracle, when making a join to a table with a long display name (which combined with the FK field display name is >30 bytes), then the query fails since 0.38.0

Possible workaround: Change table and column names to something shorter in Admin > Data Model.
Also recommended to disable or underscore "Friendly Table and Field Names" in Admin > Settings > General.

To Reproduce

Admin > Data Model > (Oracle with Sample) > Products > change name to Products-veeerylong-veeerylong-veeerylong
Custom question > (Oracle with Sample) > Reviews - join the Products table
Oracle 12.2+ fails with ORA-00904: "Products-veeerylong-veeerylong-veeerylong"."ID": invalid identifier because it's incorrectly using display name instead of alias in the join-clause.
Oracle pre-12.2 fails with ORA-00972: identifier is too long, since it doesn't support table/column/alias with more than 30 bytes, which the fix in 0.38.0 likely tried to solve, but caused breaking long table joins on all versions of Oracle.

Full stacktrace

2021-05-09 14:50:54,124 ERROR middleware.catch-exceptions :: Error processing query: null
{:database_id 48,
 :started_at #t "2021-05-09T14:50:53.045846+02:00[Europe/Copenhagen]",
 :via
 [{:status :failed,
   :class java.sql.SQLSyntaxErrorException,
   :error "ORA-00904: \"Products-veeerylong-veeerylong-veeerylong\".\"ID\": invalid identifier\n",
   :stacktrace
   ["oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:509)"
    "oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:461)"
    "oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1104)"
    "oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:553)"
    "oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:269)"
    "oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:655)"
    "oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:270)"
    "oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:91)"
    "oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:807)"
    "oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:983)"
    "oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1168)"
    "oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3666)"
    "oracle.jdbc.driver.T4CPreparedStatement.executeInternal(T4CPreparedStatement.java:1426)"
    "oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3713)"
    "oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1167)"
    "com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:431)"
    "--> driver.sql_jdbc.execute$fn__77454.invokeStatic(execute.clj:264)"
    "driver.sql_jdbc.execute$fn__77454.invoke(execute.clj:262)"
    "driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:389)"
    "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)"
    "driver.oracle$eval840$fn__841.invoke(oracle.clj:294)"
    "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:316)"
    "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.invoke(reducible.clj:109)"
    "query_processor.reducible$sync_qp$qp_STAR___37417$fn__37420.invoke(reducible.clj:135)"
    "query_processor.reducible$sync_qp$qp_STAR___37417.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__48102$process_query_and_save_execution_BANG___48111$fn__48114.invoke(query_processor.clj:249)"
    "query_processor$fn__48102$process_query_and_save_execution_BANG___48111.invoke(query_processor.clj:241)"
    "query_processor$fn__48146$process_query_and_save_with_max_results_constraints_BANG___48155$fn__48158.invoke(query_processor.clj:261)"
    "query_processor$fn__48146$process_query_and_save_with_max_results_constraints_BANG___48155.invoke(query_processor.clj:254)"
    "api.dataset$fn__54256$fn__54259.invoke(dataset.clj:55)"
    "query_processor.streaming$streaming_response_STAR_$fn__54237$fn__54238.invoke(streaming.clj:72)"
    "query_processor.streaming$streaming_response_STAR_$fn__54237.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__16055.invoke(streaming_response.clj:84)"],
   :state "42000"}],
 :json_query
 {:type "query",
  :query
  {:source-table 6347,
   :joins
   [{:fields "all",
     :source-table 6366,
     :condition ["=" ["field-id" 36480] ["joined-field" "Products-veeerylong-veeerylong-veeerylong" ["field-id" 36609]]],
     :alias "Products-veeerylong-veeerylong-veeerylong"}]},
  :database 48,
  :parameters [],
  :middleware {:js-int-to-string? true, :add-default-userland-constraints? true}},
 :native
 {:query
  "SELECT * FROM (SELECT \"SAMPLE_DATASET\".\"REVIEWS\".\"ID\" AS \"ID\", \"SAMPLE_DATASET\".\"REVIEWS\".\"BODY\" AS \"BODY\", \"SAMPLE_DATASET\".\"REVIEWS\".\"CREATED_AT\" AS \"CREATED_AT\", \"SAMPLE_DATASET\".\"REVIEWS\".\"PRODUCT_ID\" AS \"PRODUCT_ID\", \"SAMPLE_DATASET\".\"REVIEWS\".\"RATING\" AS \"RATING\", \"SAMPLE_DATASET\".\"REVIEWS\".\"REVIEWER\" AS \"REVIEWER\", \"Products-veeerylong-veeerylong-veeerylong\".\"ID\" AS \"identifier_nrxrxrruxuu\", \"Products-veeerylong-veeerylong-veeerylong\".\"CATEGORY\" AS \"identifier_nrwyyrqqtxy\", \"Products-veeerylong-veeerylong-veeerylong\".\"CREATED_AT\" AS \"identifier_rxwvtuwxqr\", \"Products-veeerylong-veeerylong-veeerylong\".\"EAN\" AS \"identifier_nrqtzqxwszr\", \"Products-veeerylong-veeerylong-veeerylong\".\"PRICE\" AS \"identifier_rzrwqtuuxq\", \"Products-veeerylong-veeerylong-veeerylong\".\"RATING\" AS \"identifier_ruzqtrzwux\", \"Products-veeerylong-veeerylong-veeerylong\".\"TITLE\" AS \"identifier_nrxxxtvqxtw\", \"Products-veeerylong-veeerylong-veeerylong\".\"VENDOR\" AS \"identifier_nyvwqvvtur\" FROM \"SAMPLE_DATASET\".\"REVIEWS\" LEFT JOIN \"SAMPLE_DATASET\".\"PRODUCTS\" \"identifier_nyurvuxrux\" ON \"SAMPLE_DATASET\".\"REVIEWS\".\"PRODUCT_ID\" = \"Products-veeerylong-veeerylong-veeerylong\".\"ID\") WHERE rownum <= 2000",
  :params nil},
 :status :failed,
 :class oracle.jdbc.OracleDatabaseException,
 :stacktrace
 ["oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:513)"
  "oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:461)"
  "oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1104)"
  "oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:553)"
  "oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:269)"
  "oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:655)"
  "oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:270)"
  "oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:91)"
  "oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:807)"
  "oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:983)"
  "oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1168)"
  "oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3666)"
  "oracle.jdbc.driver.T4CPreparedStatement.executeInternal(T4CPreparedStatement.java:1426)"
  "oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3713)"
  "oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1167)"
  "com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:431)"
  "--> driver.sql_jdbc.execute$fn__77454.invokeStatic(execute.clj:264)"
  "driver.sql_jdbc.execute$fn__77454.invoke(execute.clj:262)"
  "driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:389)"
  "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)"
  "driver.oracle$eval840$fn__841.invoke(oracle.clj:294)"
  "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:316)"
  "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.invoke(reducible.clj:109)"
  "query_processor.reducible$sync_qp$qp_STAR___37417$fn__37420.invoke(reducible.clj:135)"
  "query_processor.reducible$sync_qp$qp_STAR___37417.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__48102$process_query_and_save_execution_BANG___48111$fn__48114.invoke(query_processor.clj:249)"
  "query_processor$fn__48102$process_query_and_save_execution_BANG___48111.invoke(query_processor.clj:241)"
  "query_processor$fn__48146$process_query_and_save_with_max_results_constraints_BANG___48155$fn__48158.invoke(query_processor.clj:261)"
  "query_processor$fn__48146$process_query_and_save_with_max_results_constraints_BANG___48155.invoke(query_processor.clj:254)"
  "api.dataset$fn__54256$fn__54259.invoke(dataset.clj:55)"
  "query_processor.streaming$streaming_response_STAR_$fn__54237$fn__54238.invoke(streaming.clj:72)"
  "query_processor.streaming$streaming_response_STAR_$fn__54237.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__16055.invoke(streaming_response.clj:84)"],
 :context :ad-hoc,
 :error "ORA-00904: \"Products-veeerylong-veeerylong-veeerylong\".\"ID\": invalid identifier\n",
 :row_count 0,
 :running_time 0,
 :preprocessed
 {:type :query,
  :query
  {:source-table 6347,
   :fields
   [[:field-id 36482]
    [:field-id 36485]
    [:datetime-field [:field-id 36483] :default]
    [:field-id 36480]
    [:field-id 36481]
    [:field-id 36484]
    [:joined-field "Products-veeerylong-veeerylong-veeerylong" [:field-id 36609]]
    [:joined-field "Products-veeerylong-veeerylong-veeerylong" [:field-id 36615]]
    [:datetime-field [:joined-field "Products-veeerylong-veeerylong-veeerylong" [:field-id 36610]] :default]
    [:joined-field "Products-veeerylong-veeerylong-veeerylong" [:field-id 36613]]
    [:joined-field "Products-veeerylong-veeerylong-veeerylong" [:field-id 36614]]
    [:joined-field "Products-veeerylong-veeerylong-veeerylong" [:field-id 36608]]
    [:joined-field "Products-veeerylong-veeerylong-veeerylong" [:field-id 36611]]
    [:joined-field "Products-veeerylong-veeerylong-veeerylong" [:field-id 36612]]],
   :joins
   [{:strategy :left-join,
     :source-table 6366,
     :condition [:= [:field-id 36480] [:joined-field "Products-veeerylong-veeerylong-veeerylong" [:field-id 36609]]],
     :alias "Products-veeerylong-veeerylong-veeerylong"}],
   :limit 2000},
  :database 48,
  :middleware {:js-int-to-string? true, :add-default-userland-constraints? true},
  :info
  {:executed-by 1,
   :context :ad-hoc,
   :nested? false,
   :query-hash [17, -8, 20, -94, 86, -57, 88, -49, 120, -86, -35, 109, 32, 3, -6, 41, -4, 88, -75, -88, -122, 104, 15, -82, -35, 26, 117, 49, 39, -21, -76, -14]},
  :constraints {:max-results 10000, :max-results-bare-rows 2000}},
 :data {:rows [], :cols []}}

2021-05-09 14:50:54,194 DEBUG middleware.log :: POST /api/dataset 202 [ASYNC: completed] 1.2 s (19 DB calls) App DB connections: 0/7 Jetty threads: 3/50 (5 idle, 0 queued) (70 total active threads) Queries in flight: 1 (0 queued)

Information about your Metabase Installation:
Tested 0.37.8 thru 0.39.1 - works on 0.37.8 (for Oracle 12.2+, would always fail pre-12.2), regression since 0.38.0

Oracle has finally "officially" released Docker images a couple of days ago: https://hub.docker.com/r/gvenzl/oracle-xe
As an alternative to the ageing 11g that most have used thanks to: https://hub.docker.com/r/wnameless/oracle-xe-11g-r2

⬇️ Please click the 👍 reaction instead of leaving a +1 or update? comment

The text was updated successfully, but these errors were encountered:

muir-christopher · 2021-08-17T19:18:47Z

Thanks for logging this one, gave it a 👍 .

Our use case has users two types of users; power users who are already familiar with the oracle table names, and those that need things translated into plain English. To fit that need, we create an alias the data model with the pattern "English Name (ORACLE_NAME)" for each table, therefore run into this issue somewhat frequently. We do have the "Friendly Table and Field Names" setting disabled as well.

We are running 0.37.9 and Oracle 12c Standard Edition Release 12.1.0.2.0.

{
  "browser-info": {
    "language": "en-US",
    "platform": "Win32",
    "userAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:91.0) Gecko/20100101 Firefox/91.0",
    "vendor": ""
  },
  "system-info": {
    "file.encoding": "UTF-8",
    "java.runtime.name": "OpenJDK Runtime Environment",
    "java.runtime.version": "1.8.0_302-b08",
    "java.vendor": "Red Hat, Inc.",
    "java.vendor.url": "https://www.redhat.com/",
    "java.version": "1.8.0_302",
    "java.vm.name": "OpenJDK 64-Bit Server VM",
    "java.vm.version": "25.302-b08",
    "os.name": "Linux",
    "os.version": "3.10.0-1160.36.2.el7.x86_64",
    "user.language": "en",
    "user.timezone": "America/New_York"
  },
  "metabase-info": {
    "databases": [
      "postgres",
      "oracle"
    ],
    "hosting-env": "unknown",
    "application-database": "postgres",
    "application-database-details": {
      "database": {
        "name": "PostgreSQL",
        "version": "10.15"
      },
      "jdbc-driver": {
        "name": "PostgreSQL JDBC Driver",
        "version": "42.2.8"
      }
    },
    "run-mode": "prod",
    "version": {
      "tag": "v0.37.9",
      "date": "2021-02-11",
      "branch": "release-x.37.x",
      "hash": "a9a7fc8"
    },
    "settings": {
      "report-timezone": "US/Eastern"
    }
  }
}

camsaul · 2021-10-21T00:59:23Z

This should be easy to fix thanks to the new alias escaping stuff @jeff303 recently added in 0.41.0

emreyavuz369 · 2021-12-20T12:52:12Z

i still have same problem - java 11 oracle 12.2
Error : "ORA-00972: identifier is too long"

{ "browser-info": { "language": "tr-TR", "platform": "Linux x86_64", "userAgent": "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/93.0.4577.63 Safari/537.36", "vendor": "Google Inc." }, "system-info": { "file.encoding": "UTF-8", "java.runtime.name": "OpenJDK Runtime Environment", "java.runtime.version": "11.0.13+8", "java.vendor": "Eclipse Adoptium", "java.vendor.url": "https://adoptium.net/", "java.version": "11.0.13", "java.vm.name": "OpenJDK 64-Bit Server VM", "java.vm.version": "11.0.13+8", "os.name": "Linux", "os.version": "4.15.0-106-generic", "user.language": "en", "user.timezone": "GMT" }, "metabase-info": { "databases": [ "oracle" ], "hosting-env": "unknown", "application-database": "postgres", "application-database-details": { "database": { "name": "PostgreSQL", "version": "12.4 (Debian 12.4-1.pgdg100+1)" }, "jdbc-driver": { "name": "PostgreSQL JDBC Driver", "version": "42.2.23" } }, "run-mode": "prod", "version": { "date": "2021-12-16", "tag": "v0.41.5", "branch": "release-x.41.x", "hash": "fbfffc6" }, "settings": { "report-timezone": null } } }

…19659) * Add failing test for #15978 * Improved test * Add new metabase.driver.query-processor.escape-join-aliases QP middleware * Test fix 🔧 * Add reference to #20307 * Add some extra dox * Test fixes for BigQuery drivers * revert unneeded change * Fix :bigquery and :bigquery-cloud-sdk mixup * Test fixes 🔧 * Test fix 🔧 * Remove comment I meant to remove

…tifiers (#19659) (#20349) * Add logic to truncate and uniquely-suffix column alias identifiers (#19659) * Add failing test for #15978 * Improved test * Add new metabase.driver.query-processor.escape-join-aliases QP middleware * Test fix 🔧 * Add reference to #20307 * Add some extra dox * Test fixes for BigQuery drivers * revert unneeded change * Fix :bigquery and :bigquery-cloud-sdk mixup * Test fixes 🔧 * Test fix 🔧 * Remove comment I meant to remove * Backport changes to legacy :bigquery driver * Add 0.42.0 version of middleware for new namespace * Fix test failures

flamber added Type:Bug Product defects Priority:P2 Average run of the mill bug Querying/Processor Database/Oracle .Regression Bugs that were previously fixed and/or bugs unintentionally shipped with new features. labels May 9, 2021

jeff303 assigned camsaul Jul 22, 2021

emreyavuz369 mentioned this issue Dec 20, 2021

Updating log4j to 2.15.0 (v0.37.9) #19432

Closed

kaklakariada mentioned this issue Dec 22, 2021

Fix tests with self-referencing foreign key exasol/metabase-driver#12

Closed

camsaul mentioned this issue Jan 19, 2022

Add logic to truncate and uniquely-suffix column alias identifiers #19659

Merged

flamber mentioned this issue Jan 19, 2022

With columnames lager than 30 characters, metabase generates invalid identiefiers in querries (oracle) #19773

Closed

camsaul mentioned this issue Jan 24, 2022

Nested Query SQL Generation Fixes #18580

Closed

13 tasks

camsaul added a commit that referenced this issue Feb 8, 2022

Add failing test for #15978

a34cd3f

camsaul mentioned this issue Feb 8, 2022

QP should escape-alias join aliases #20303

Closed

camsaul closed this as completed in #19659 Feb 14, 2022

camsaul added this to the 0.42.1 milestone Feb 14, 2022

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Oracle fails queries on joins with tables with long display names #15978

Oracle fails queries on joins with tables with long display names #15978

flamber commented May 9, 2021 •

edited

muir-christopher commented Aug 17, 2021

camsaul commented Oct 21, 2021

emreyavuz369 commented Dec 20, 2021

Oracle fails queries on joins with tables with long display names #15978

Oracle fails queries on joins with tables with long display names #15978

Comments

flamber commented May 9, 2021 • edited

muir-christopher commented Aug 17, 2021

camsaul commented Oct 21, 2021

emreyavuz369 commented Dec 20, 2021

flamber commented May 9, 2021 •

edited