Error ORA-00972: identifier is too long Question Custom

thales.dias · January 7, 2021, 2:50pm

Hi!
I would like to report a bug when trying to ask custom questions using join.
I have received the error message ORA-00972: identifier is too long.
Analyzing the log, I realized that the tool is passing the nickname of the table and columns in the join instead of the correct nomenclature.
can you help me?

flamber · January 7, 2021, 3:18pm

Hi @thales.dias
Please post “Diagnostic Info” from Admin > Troubleshooting, and which version of ojdbc8.jar you are using
The actual query would be helpful to understand the problem too.
Sounds like it’s something like this: https://stackoverflow.com/questions/3085562/ora-00972-identifier-is-too-long-alias-column-name

thales.dias · January 7, 2021, 3:46pm

{
“browser-info”: {
“language”: “pt-BR”,
“platform”: “Win32”,
“userAgent”: “Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/87.0.4280.88 Safari/537.36”,
“vendor”: “Google Inc.”
},
“system-info”: {
“file.encoding”: “UTF-8”,
“java.runtime.name”: “Java™ SE Runtime Environment”,
“java.runtime.version”: “1.8.0_212-b10”,
“java.vendor”: “Oracle Corporation”,
“java.vendor.url”: “http://java.oracle.com/”,
“java.version”: “1.8.0_212”,
“java.vm.name”: “Java HotSpot™ 64-Bit Server VM”,
“java.vm.version”: “25.212-b10”,
“os.name”: “Linux”,
“os.version”: “3.10.0-1160.6.1.el7.x86_64”,
“user.language”: “en”,
“user.timezone”: “America/Sao_Paulo”
},
“metabase-info”: {
“databases”: [
“oracle”
],
“hosting-env”: “unknown”,
“application-database”: “mysql”,
“application-database-details”: {
“database”: {
“name”: “MariaDB”,
“version”: “10.5.8-MariaDB”
},
“jdbc-driver”: {
“name”: “MariaDB Connector/J”,
“version”: “2.6.2”
}
},
“run-mode”: “prod”,
“version”: {
“tag”: “v0.37.4”,
“date”: “2020-12-17”,
“branch”: “release-x.37.x”,
“hash”: “e0d5287”
},
“settings”: {
“report-timezone”: null
}
}
}

thales.dias · January 7, 2021, 3:49pm

Hi!
The query I cannot post, it is outside the company’s rules, but basically the table is named DIM_TEMPO and the alias of DIM TEMPO.
When I make a query the metabase is considering DIM TEMPO and not DIM_TEMPO.

This means that the alias I entered in the metabase is considered in the column and not the real name. The alias is large and therefore the error occurs.

flamber · January 7, 2021, 3:51pm

@thales.dias Then create a sample schema, which you can share, and with steps-to-reproduce.
It’s really difficult to help without much information.

thales.dias · January 7, 2021, 6:38pm

Hi!
This is an example of a query. The time dimension he interprets with the wrong name

SELECT *
SELECT *
FROM (SELECT
“BI_SCHEMA”.“FAT_RMN”.“VLR_RMN_BRUTA” AS “VLR_RMN_BRUTA”,
“BI_SCHEMA”.“FAT_RMN”.“VLR_RMN_LIQUIDA” AS “VLR_RMN_LIQUIDA”,
“DIMENSÃO TEMPO - CÓDIGO IDENTIFICADOR DA DATA DO PAGAMENTO”.“SEQ_DIM_TEMPO” AS “SEQ_DIM_TEMPO”,
“DIMENSÃO TEMPO - CÓDIGO IDENTIFICADOR DA DATA DO PAGAMENTO”.“DAT_DATA” AS “DAT_DATA”,
“DIMENSÃO TEMPO - CÓDIGO IDENTIFICADOR DA DATA DO PAGAMENTO”.“NUM_ANO” AS “NUM_ANO”,
FROM “BI_SCHEMA”.“FAT_RMN”
LEFT JOIN “BI_SCHEMA”.“DIM_TEMPO” “DIMENSÃO TEMPO - CÓDIGO IDENTIFICADOR DA DATA DO PAGAMENTO”
ON “BI_SCHEMA”.“FAT_RMN”.“SEQ_DAT_PAGAMENTO” =
“DIMENSÃO TEMPO - CÓDIGO IDENTIFICADOR DA DATA DO PAGAMENTO”.“SEQ_DIM_TEMPO”
WHERE (“DIMENSÃO TEMPO - CÓDIGO IDENTIFICADOR DA DATA DO PAGAMENTO”.“NUM_ANO” = 2020 AND
“DIMENSÃO TEMPO - CÓDIGO IDENTIFICADOR DA DATA DO PAGAMENTO”.“NUM_MES” = 11))
WHERE ROWNUM <= 2000

flamber · January 7, 2021, 7:12pm

@thales.dias I don’t understand what the problem is, but it looks like you are hitting a limitation on Oracle, because you have created very long names for your tables in Metabase. Change the names to something shorter in Admin > Data Model.

thales.dias · January 8, 2021, 11:26am

Hi!
I believe this is not the problem, when I reverse the order of the joins in some cases it works.
It seems that METABASE is not accepting that the table of values is the first table selected in the custom question.
Anyway, I will reduce the names to try to solve the problem.
Thanks!

thales.dias · January 8, 2021, 11:48am

Hi!
I will add the images here for easy understanding.
When I reverse the order of the join, METABASE understands without giving the error mentioned.
I'm finding it strange for that.

First attempt using the table of values as the starting table.

thales.dias · January 8, 2021, 11:50am

Second attempt using the organ registration table as the initial table.

flamber · January 8, 2021, 12:29pm

@thales.dias

Which version of Oracle are you using?
Which version of ojdbc8.jar are you using?
Please post the query of the “second attempt”, where it works

thales.dias · January 8, 2021, 12:31pm

I identified that the problem appears to be in the friendly name.
Even disabled, it continues passing the friendly name in the query execution.
3D49A7F8

flamber · January 8, 2021, 12:44pm

@thales.dias When you disable Friendly Names, then it does a resync of all your data, which might take a while (see Admin > Troubleshooting > Logs) and it might require that you refresh your browser too, so you see the update names.
I don’t understand what the problem is, since you are not providing the queries that work vs the ones that doesn’t work for comparison.

Francois_data · January 19, 2021, 9:24am

Hi, i’m facing the same issue. Given the join order, the issue pops ups or not.

Query 1 : fails

SELECT “HELIOS”.“SWINTRANS_V_F_OT”.“CODE_EXPEDITEUR” AS “CODE_EXPEDITEUR”, count(*) AS “count”
FROM “HELIOS”.“SWINTRANS_V_F_OT”
LEFT JOIN “HELIOS”.“SWINTRANS_V_F_ELTS_FACT_VENTE” “Swi Ntr Ans V F El Ts Fact Vente” ON “HELIOS”.“SWINTRANS_V_F_OT”.“NO_LIGNE_COMMANDE” = “Swi Ntr Ans V F El Ts Fact Vente”.“NO_LIGNE_COMMANDE”
GROUP BY “HELIOS”.“SWINTRANS_V_F_OT”.“CODE_EXPEDITEUR”

Error : "ORA-00972: identifier is too long"

Query 2 : succeeds

SELECT “SWINTRANS_V_F_OT”.“CODE_EXPEDITEUR” AS “CODE_EXPEDITEUR”, count(*) AS “count”
FROM “HELIOS”.“SWINTRANS_V_F_ELTS_FACT_VENTE”
LEFT JOIN “HELIOS”.“SWINTRANS_V_F_OT” “SWINTRANS_V_F_OT” ON “HELIOS”.“SWINTRANS_V_F_ELTS_FACT_VENTE”.“NO_LIGNE_COMMANDE” = “SWINTRANS_V_F_OT”.“NO_LIGNE_COMMANDE”
GROUP BY “SWINTRANS_V_F_OT”.“CODE_EXPEDITEUR”

Any idea of the cause or any workaround ?

==[ Environment ]===========================

{
“browser-info”: {
“language”: “fr”,
“platform”: “MacIntel”,
“userAgent”: “Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/87.0.4280.141 Safari/537.36”,
“vendor”: “Google Inc.”
},
“system-info”: {
“file.encoding”: “Cp1252”,
“java.runtime.name”: “Java™ SE Runtime Environment”,
“java.runtime.version”: “1.8.0_65-b17”,
“java.vendor”: “Oracle Corporation”,
“java.vendor.url”: “http://java.oracle.com/”,
“java.version”: “1.8.0_65”,
“java.vm.name”: “Java HotSpot™ 64-Bit Server VM”,
“java.vm.version”: “25.65-b01”,
“os.name”: “Windows Server 2012 R2”,
“os.version”: “6.3”,
“user.language”: “fr”,
“user.timezone”: “Europe/Paris”
},
“metabase-info”: {
“databases”: [
“h2”,
“oracle”
],
“hosting-env”: “unknown”,
“application-database”: “h2”,
“application-database-details”: {
“database”: {
“name”: “H2”,
“version”: “1.4.197 (2018-03-18)”
},
“jdbc-driver”: {
“name”: “H2 JDBC Driver”,
“version”: “1.4.197 (2018-03-18)”
}
},
“run-mode”: “prod”,
“version”: {
“tag”: “v0.37.5”,
“date”: “2021-01-05”,
“branch”: “release-x.37.x”,
“hash”: “be537ee”
},
“settings”: {
“report-timezone”: null
}
}
}

flamber · January 19, 2021, 11:23am

@Francois_data This is a limitation of Oracle < 12.2. You can disable Friendly Name:
https://www.metabase.com/docs/latest/administration-guide/08-configuration-settings.html#friendly-table-and-field-names
You should migrate away from H2 if you’re using Metabase in production:
https://www.metabase.com/docs/latest/operations-guide/migrating-from-h2.html
And you should update Java:
https://www.metabase.com/docs/latest/operations-guide/java-versions.html

thales.dias · February 16, 2021, 12:39pm

Hi!
Disabling friendly names did not solve my problem. Neither does updating java.

flamber · February 16, 2021, 2:24pm

@thales.dias
This is a limitation of Oracle < 12.2
Post “Diagnostic Info” from Admin > Troubleshooting.
And the version of ojdbc8.jar

thales.dias · February 18, 2021, 7:10pm

Hi!

I am still facing a problem in METABASE with customized questions.
When I use join in custom questions, METABASE cannot understand that the real name of the table and field should be given to the query and not the friendly name.
METABASE diagnostic information data is:

{
"browser-info": {
"language": "pt-BR",
"platform": "Win32",
"userAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/88.0.4324.150 Safari/537.36",
"vendor": "Google Inc."
},
"system-info": {
"file.encoding": "UTF-8",
"java.runtime.name": "Java(TM) SE Runtime Environment",
"java.runtime.version": "1.8.0_212-b10",
"java.vendor": "Oracle Corporation",
"java.vendor.url": "http://java.oracle.com/",
"java.version": "1.8.0_212",
"java.vm.name": "Java HotSpot(TM) 64-Bit Server VM",
"java.vm.version": "25.212-b10",
"os.name": "Linux",
"os.version": "3.10.0-1160.15.2.el7.x86_64",
"user.language": "en",
"user.timezone": "America/Sao_Paulo"
},
"metabase-info": {
"databases": [
"oracle"
],
"hosting-env": "unknown",
"application-database": "mysql",
"application-database-details": {
"database": {
"name": "MariaDB",
"version": "10.5.8-MariaDB"
},
"jdbc-driver": {
"name": "MariaDB Connector/J",
"version": "2.6.2"
}
},
"run-mode": "prod",
"version": {
"tag": "v0.38.0",
"date": "2021-02-16",
"branch": "release-x.38.x",
"hash": "471aa51"
},
"settings": {
"report-timezone": null
}
}
}

When I join the error returned is:

Analyzing the log file, the query is being executed as follows:

SELECT *
FROM (SELECT BI_SICOM.TBLAUXEMP.CODACP AS CODACP,
BI_SICOM.TBLAUXEMP.CODMUN AS CODMUN,
BI_SICOM.TBLAUXEMP.CODORG AS CODORG,
BI_SICOM.TBLAUXEMP.DATCRG AS DATCRG,
BI_SICOM.TBLAUXEMP.NUMANOMESREF AS NUMANOMESREF,
BI_SICOM.TBLAUXEMP.NUMANOREF AS NUMANOREF,
BI_SICOM.TBLAUXEMP.NUMMESREF AS NUMMESREF,
BI_SICOM.TBLAUXEMP.SEQEMP AS SEQEMP,
BI_SICOM.TBLAUXEMP.SEQORG AS SEQORG,
BI_SICOM.TBLAUXEMP.VLRANLEMPFNTRER AS VLRANLEMPFNTRER,
BI_SICOM.TBLAUXEMP.VLRANLLQDEMPFNTRER AS VLRANLLQDEMPFNTRER,
BI_SICOM.TBLAUXEMP.VLRANLOTRBXAEMPFNTRER AS VLRANLOTRBXAEMPFNTRER,
BI_SICOM.TBLAUXEMP.VLRANLPAGEMPFNTRER AS VLRANLPAGEMPFNTRER,
BI_SICOM.TBLAUXEMP.VLREMPFNTRER AS VLREMPFNTRER,
BI_SICOM.TBLAUXEMP.VLRLQDEMPFNTRER AS VLRLQDEMPFNTRER,
BI_SICOM.TBLAUXEMP.VLROTRBXAEMPFNTRER AS VLROTRBXAEMPFNTRER,
BI_SICOM.TBLAUXEMP.VLRPAGEMPFNTRER AS VLRPAGEMPFNTRER,
BI_SICOM.TBLAUXEMP.VLRRFCEMPFNTRER AS VLRRFCEMPFNTRER,
TABELA ÓRGÃO - CÓDIGO IDENTIFICADOR DO ÓRGÃO.SEQORG AS identifier_wyquqxrxv,
TABELA ÓRGÃO - CÓDIGO IDENTIFICADOR DO ÓRGÃO.DATATL AS identifier_nruzyqvxtus,
TABELA ÓRGÃO - CÓDIGO IDENTIFICADOR DO ÓRGÃO.DATCRA AS identifier_rzxrvuvvtt,
TABELA ÓRGÃO - CÓDIGO IDENTIFICADOR DO ÓRGÃO.DATEXO AS identifier_nrutqxwxswt,
TABELA ÓRGÃO - CÓDIGO IDENTIFICADOR DO ÓRGÃO.DATINI AS identifier_nsqxxwssvvy,
TABELA ÓRGÃO - CÓDIGO IDENTIFICADOR DO ÓRGÃO.DATPUBEXO AS identifier_nrwwsytuszs,
TABELA ÓRGÃO - CÓDIGO IDENTIFICADOR DO ÓRGÃO.CODMUN AS identifier_uxwxqqxut,
TABELA ÓRGÃO - CÓDIGO IDENTIFICADOR DO ÓRGÃO.CODMUNSIC AS identifier_nxqquxyqty,
TABELA ÓRGÃO - CÓDIGO IDENTIFICADOR DO ÓRGÃO.CODORG AS identifier_rvquzwwrsq,
TABELA ÓRGÃO - CÓDIGO IDENTIFICADOR DO ÓRGÃO.CODTPOORG AS identifier_wrwxtqzts,
TABELA ÓRGÃO - CÓDIGO IDENTIFICADOR DO ÓRGÃO.DSCEML AS identifier_nvqzvwxuqq,
TABELA ÓRGÃO - CÓDIGO IDENTIFICADOR DO ÓRGÃO.DSCTPOORG AS identifier_nrruxzsrwux,
TABELA ÓRGÃO - CÓDIGO IDENTIFICADOR DO ÓRGÃO.NOMORG AS identifier_tuuqwywtq,
TABELA ÓRGÃO - CÓDIGO IDENTIFICADOR DO ÓRGÃO.NUMCGCORG AS identifier_nsxxytxwss,
TABELA ÓRGÃO - CÓDIGO IDENTIFICADOR DO ÓRGÃO.NUMLEICRA AS identifier_yztvtttvs,
TABELA ÓRGÃO - CÓDIGO IDENTIFICADOR DO ÓRGÃO.NUMLEIEXO AS identifier_usrqwvyru,
TABELA ÓRGÃO - CÓDIGO IDENTIFICADOR DO ÓRGÃO.NUMTEL AS identifier_nryxszvxqxr
FROM BI_SICOM.TBLAUXEMP
LEFT JOIN BI_SICOM.ODSORG identifier_ntwuvvtytv ON BI_SICOM.TBLAUXEMP.SEQORG = TABELA ÓRGÃO - CÓDIGO IDENTIFICADOR DO ÓRGÃO.SEQORG
)
WHERE rownum <= 2000;

The ÓRGÃO table is being interpreted by the friendly name together with the name of your entity key and not by the real name.
Even disabling friendly names, the problem still persists.
I wonder if anyone else is experiencing this problem and if they have already found the solution.
Thanks!

flamber · February 18, 2021, 7:22pm

@thales.dias

Which version of ojdbc8.jar are you using?
Which version of Oracle Server are you using?
You should upgrade your Java version: https://www.metabase.com/docs/latest/operations-guide/java-versions.html

I don’t understand what you mean. If you have disabled Friendly Names, then perhaps you have given the table a custom name in Admin > Data Model.

flamber · May 9, 2021, 1:11pm

That took a while to figure out what was going on - I have created an issue about this:
https://github.com/metabase/metabase/issues/15978 - upvote by clicking on the first post