search

cube-js / cube

📊 Cube — The Semantic Layer for Building Data Applications
https://cube.dev
Other
17.95k stars 1.78k forks source link

[Metabase] Unable to sync fields on latest version on metabase v0.51.2 #8926

Open pauldheinrichs opened 1 week ago

pauldheinrichs commented 1 week ago

Describe the bug https://github.com/metabase/metabase/pull/48576/files - the following change to sync table fields is no longer supported from metabase v0.51.2 in cube and prevents metabase from syncing cubes fields to metabase resulting in continuous failures

Seemingly sourced from the macro FORMAT newly leveraged which is missing from cube.

The failure message from metabase


{"status":"failed","exception":"class clojure.lang.ExceptionInfo","message":"Error executing query: ERROR: Initial planning error: Error during planning: Invalid function 'format'\nQUERY: SELECT \"c\".\"column_name\" AS \"name\", \"c\".\"udt_name\" AS \"database-type\", \"c\".\"ordinal_position\" - 1 AS \"database-position\", \"c\".\"table_schema\" AS \"table-schema\", \"c\".\"table_name\" AS \"table-name\", \"pk\".\"column_name\" IS NOT NULL AS \"pk?\", COL_DESCRIPTION(__cube_regclass_cast(FORMAT('%I.%I', CAST(\"c\".\"table_schema\" AS TEXT), CAST(\"c\".\"table_name\" AS TEXT))), \"c\".\"ordinal_position\") AS \"field-comment\", ((\"column_default\" IS NULL) OR (LOWER(\"column_default\") = 'null')) AND (\"is_nullable\" = 'NO') AND NOT (((\"column_default\" IS NOT NULL) AND (\"column_default\" LIKE '%nextval(%')) OR (\"is_identity\" <> 'NO')) AS \"database-required\", ((\"column_default\" IS NOT NULL) AND (\"column_default\" LIKE '%nextval(%')) OR (\"is_identity\" <> 'NO') AS \"database-is-auto-increment\" FROM \"information_schema\".\"columns\" AS \"c\" LEFT JOIN (SELECT \"tc\".\"table_schema\", \"tc\".\"table_name\", \"kc\".\"column_name\" FROM \"information_schema\".\"table_constraints\" AS \"tc\" JOIN \"information_schema\".\"key_column_usage\" AS \"kc\" ON (\"tc\".\"constraint_name\" = \"kc\".\"constraint_name\") AND (\"tc\".\"table_schema\" = \"kc\".\"table_schema\") AND (\"tc\".\"table_name\" = \"kc\".\"table_name\") WHERE \"tc\".\"constraint_type\" = 'PRIMARY KEY') AS \"pk\" ON (\"c\".\"table_schema\" = \"pk\".\"table_schema\") AND (\"c\".\"table_name\" = \"pk\".\"table_name\") AND (\"c\".\"column_name\" = \"pk\".\"column_name\") WHERE c.table_schema !~ '^information_schema|catalog_history|pg_' AND (\"c\".\"table_schema\" IN ('replaced_placeholder')) ORDER BY \"table-schema\" ASC, \"table-name\" ASC, \"database-position\" ASC","stacktrace":["--> driver.sql_jdbc.execute$reducible_query$reify__86010$fn__86011$fn__86012.invoke(execute.clj:761)","driver.sql_jdbc.execute$reducible_query$reify__86010$fn__86011.invoke(execute.clj:757)","driver.sql_jdbc.execute$fn__85799$fn__85800.invoke(execute.clj:398)","driver.sql_jdbc.execute$do_with_resolved_connection85769__85770.invokeStatic(execute.clj:338)","driver.sql_jdbc.execute$do_with_resolved_connection85769__85770.invoke(execute.clj:321)","driver.sql_jdbc.execute$fn__85799.invokeStatic(execute.clj:392)","driver.sql_jdbc.execute$fn__85799.invoke(execute.clj:390)","driver.sql_jdbc.execute$reducible_query$reify__86010.reduce(execute.clj:751)","sync.sync_metadata.fields$sync_fields_BANG_89200__89201$fn__89202.invoke(fields.clj:82)","sync.util$do_with_error_handling.invokeStatic(util.clj:191)","sync.util$do_with_error_handling.invoke(util.clj:184)","sync.sync_metadata.fields$sync_fields_BANG_89200__89201.invokeStatic(fields.clj:76)","sync.sync_metadata.fields$sync_fields_BANG_89200__89201.invoke(fields.clj:71)","sync.util$run_step_with_metadata65837__65839$fn__65841$fn__65844.invoke(util.clj:495)","models.task_history$do_with_task_history65674__65675.invokeStatic(task_history.clj:121)","models.task_history$do_with_task_history65674__65675.invoke(task_history.clj:109)","sync.util$run_step_with_metadata65837__65839$fn__65841.doInvoke(util.clj:488)","sync.util$with_start_and_finish_logging_STAR_.invokeStatic(util.clj:132)","sync.util$with_start_and_finish_logging_STAR_.invoke(util.clj:126)","sync.util$do_with_start_and_finish_debug_logging.invokeStatic(util.clj:150)","sync.util$do_with_start_and_finish_debug_logging.invoke(util.clj:146)","sync.util$run_step_with_metadata65837__65839.invokeStatic(util.clj:482)","sync.util$run_step_with_metadata65837__65839.invoke(util.clj:477)","sync.util$run_sync_operation65887__65888$fn__65889$fn__65897.invoke(util.clj:568)","sync.util$run_sync_operation65887__65888$fn__65889.invoke(util.clj:566)","models.task_history$do_with_task_history65674__65675.invokeStatic(task_history.clj:121)","models.task_history$do_with_task_history65674__65675.invoke(task_history.clj:109)","sync.util$run_sync_operation65887__65888.invokeStatic(util.clj:563)","sync.util$run_sync_operation65887__65888.invoke(util.clj:558)","sync.sync_metadata$sync_db_metadata_BANG_89796__89797$fn__89798.invoke(sync_metadata.clj:70)","sync.util$do_with_error_handling.invokeStatic(util.clj:191)","sync.util$do_with_error_handling.invoke(util.clj:184)","driver$fn__58386.invokeStatic(driver.clj:892)","driver$fn__58386.invoke(driver.clj:892)","sync.util$sync_in_context$fn__65746.invoke(util.clj:167)","sync.util$with_db_logging_disabled$fn__65743.invoke(util.clj:159)","sync.util$with_start_and_finish_logging_STAR_.invokeStatic(util.clj:132)","sync.util$with_start_and_finish_logging_STAR_.invoke(util.clj:126)","sync.util$with_start_and_finish_logging$fn__65730.invoke(util.clj:144)","sync.util$with_sync_events65720__65721$fn__65725.invoke(util.clj:118)","sync.util$with_duplicate_ops_prevented$fn__65710.invoke(util.clj:90)","sync.util$do_sync_operation65761__65762.invokeStatic(util.clj:216)","sync.util$do_sync_operation65761__65762.invoke(util.clj:210)","sync.sync_metadata$sync_db_metadata_BANG_89796__89797.invokeStatic(sync_metadata.clj:68)","sync.sync_metadata$sync_db_metadata_BANG_89796__89797.invoke(sync_metadata.clj:65)","api.database$fn__104016$fn__104021.invoke(database.clj:1016)"],"ex-data":{"driver":"postgres","sql":["SELECT","  \"c\".\"column_name\" AS \"name\",","  \"c\".\"udt_name\" AS \"database-type\",","  \"c\".\"ordinal_position\" - 1 AS \"database-position\",","  \"c\".\"table_schema\" AS \"table-schema\",","  \"c\".\"table_name\" AS \"table-name\",","  \"pk\".\"column_name\" IS NOT NULL AS \"pk?\",","  COL_DESCRIPTION(","    CAST(","      CAST(","        FORMAT(","          '%I.%I',","          CAST(\"c\".\"table_schema\" AS TEXT),","          CAST(\"c\".\"table_name\" AS TEXT)","        ) AS REGCLASS","      ) AS OID","    ),","    \"c\".\"ordinal_position\"","  ) AS \"field-comment\",","  (","    (\"column_default\" IS NULL)","    OR (LOWER(\"column_default\") = 'null')","  )","  AND (\"is_nullable\" = 'NO')","  AND NOT (","    (","      (\"column_default\" IS NOT NULL)","      AND (\"column_default\" LIKE '%nextval(%')","    )","    OR (\"is_identity\" <> 'NO')","  ) AS \"database-required\",","  (","    (\"column_default\" IS NOT NULL)","    AND (\"column_default\" LIKE '%nextval(%')","  )","  OR (\"is_identity\" <> 'NO') AS \"database-is-auto-increment\"","FROM","  \"information_schema\".\"columns\" AS \"c\"","  LEFT JOIN (","    SELECT","      \"tc\".\"table_schema\",","      \"tc\".\"table_name\",","      \"kc\".\"column_name\"","    FROM","      \"information_schema\".\"table_constraints\" AS \"tc\"","      INNER JOIN \"information_schema\".\"key_column_usage\" AS \"kc\" ON (\"tc\".\"constraint_name\" = \"kc\".\"constraint_name\")","      AND (\"tc\".\"table_schema\" = \"kc\".\"table_schema\")","      AND (\"tc\".\"table_name\" = \"kc\".\"table_name\")","    WHERE","      \"tc\".\"constraint_type\" = 'PRIMARY KEY'","  ) AS \"pk\" ON (\"c\".\"table_schema\" = \"pk\".\"table_schema\")","  AND (\"c\".\"table_name\" = \"pk\".\"table_name\")","  AND (\"c\".\"column_name\" = \"pk\".\"column_name\")","WHERE","  c.table_schema !~ '^information_schema|catalog_history|pg_'","  AND (\"c\".\"table_schema\" IN (?))","ORDER BY","  \"table-schema\" ASC,","  \"table-name\" ASC,","  \"database-position\" ASC"],"params":["public"]},"original-info":null}

The converted queries

SELECT
  c.column_name AS name,
  c.udt_name AS database_type,
  c.ordinal_position - 1 AS database_position,
  c.table_schema AS table_schema,
  c.table_name AS table_name,
  pk.column_name IS NOT NULL AS pk,
  COL_DESCRIPTION(
    CAST(
      CAST(
        FORMAT(
          '%I.%I',
          CAST(c.table_schema AS TEXT),
          CAST(c.table_name AS TEXT)
        ) AS REGCLASS
      ) AS OID
    ),
    c.ordinal_position
  ) AS field_comment,
  (
    (column_default IS NULL)
    OR (LOWER(column_default) = 'null')
  )
  AND (is_nullable = 'NO')
  AND NOT (
    (
      (column_default IS NOT NULL)
      AND (column_default LIKE '%nextval(%')
    )
    OR (is_identity <> 'NO')
  ) AS database_required,
  (
    (column_default IS NOT NULL)
    AND (column_default LIKE '%nextval(%')
  )
  OR (is_identity <> 'NO') AS database_is_auto_increment
FROM
  information_schema.columns AS c
  LEFT JOIN (
    SELECT
      tc.table_schema,
      tc.table_name,
      kc.column_name
    FROM
      information_schema.table_constraints AS tc
      INNER JOIN information_schema.key_column_usage AS kc ON (tc.constraint_name = kc.constraint_name)
      AND (tc.table_schema = kc.table_schema)
      AND (tc.table_name = kc.table_name)
    WHERE
      tc.constraint_type = 'PRIMARY KEY'
  ) AS pk ON (c.table_schema = pk.table_schema)
  AND (c.table_name = pk.table_name)
  AND (c.column_name = pk.column_name)
WHERE
  c.table_schema !~ '^information_schema|catalog_history|pg_'
  AND (c.table_schema IN ('public'))
ORDER BY
  table_schema ASC,
  table_name ASC,
  database_position ASC;
SELECT
  c.column_name AS name,
  c.udt_name AS database_type,
  c.ordinal_position - 1 AS database_position,
  c.table_schema AS table_schema,
  c.table_name AS table_name,
  pk.column_name IS NOT NULL AS pk,
  COL_DESCRIPTION(
    __cube_regclass_cast(
      FORMAT('%I.%I', CAST(c.table_schema AS TEXT), CAST(c.table_name AS TEXT))
    ),
    c.ordinal_position
  ) AS field_comment,
  (
    (column_default IS NULL)
    OR (LOWER(column_default) = 'null')
  )
  AND (is_nullable = 'NO')
  AND NOT (
    (
      (column_default IS NOT NULL)
      AND (column_default LIKE '%nextval(%')
    )
    OR (is_identity <> 'NO')
  ) AS database_required,
  (
    (column_default IS NOT NULL)
    AND (column_default LIKE '%nextval(%')
  )
  OR (is_identity <> 'NO') AS database_is_auto_increment
FROM
  information_schema.columns AS c
  LEFT JOIN (
    SELECT
      tc.table_schema,
      tc.table_name,
      kc.column_name
    FROM
      information_schema.table_constraints AS tc
      JOIN information_schema.key_column_usage AS kc ON (tc.constraint_name = kc.constraint_name)
      AND (tc.table_schema = kc.table_schema)
      AND (tc.table_name = kc.table_name)
    WHERE
      tc.constraint_type = 'PRIMARY KEY'
  ) AS pk ON (c.table_schema = pk.table_schema)
  AND (c.table_name = pk.table_name)
  AND (c.column_name = pk.column_name)
WHERE
  c.table_schema !~ '^information_schema|catalog_history|pg_'
  AND (c.table_schema IN ('replaced_placeholder'))
ORDER BY
  table_schema ASC,
  table_name ASC,
  database_position ASC;
itestyoy commented 16 hours ago

Hi! Also jumping on this with the same issue.