[teiid-issues] [JBoss JIRA] (TEIID-3818) Issues with ODBC metadata foreign key queries

Issues with ODBC metadata foreign key queries --------------------------------------------- Key: TEIID-3818 URL: https://issues.jboss.org/browse/TEIID-3818 Project: Teiid Issue Type: Bug Components: ODBC Affects Versions: 8.4 Environment: Most likely not OS / hardware dependent. Reporter: Dave Nicodemus Assignee: Steven Hawkins Priority: Critical Fix For: 8.7.2.6_2, 8.12.2, 8.13, 8.7.6 The current foreign key handling should return imported key, but instead returns exported keys. When exported keys are asked for an exception is thrown: While attempting to use Client Microsoft's Power BI desktop using ODBC connecting to Teiid Embedded server using VDB with metadata tags. Received the following errors : {code} [36.864]ERROR from backend during send_query: 'SERROR' [36.865]ERROR from backend during send_query: 'C50000' [36.869]ERROR from backend during send_query: 'MTEIID31100 Parsing error: Encountered "('ScottTestMart' AS [*]name[*]) as" at line 1, column 33. Was expecting: "string" | "varbinary" | "varchar" | "boolean" | "byte" | "tinyint" | "short" | "smallint" | "char" | "integer" ...' [36.875]ERROR from backend during send_query: 'Dorg.teiid.jdbc.TeiidSQLException: TEIID31100 Parsing error: Encountered "('ScottTestMart' AS [*]name[*]) as" at line 1, column 33. Was expecting: "string" | "varbinary" | "varchar" | "boolean" | "byte" | "tinyint" | "short" | "smallint" | "char" | "integer" ...' [37.397]STATEMENT ERROR: func=PGAPI_ForeignKeys, desc='', errnum=1, errmsg='PGAPI_ForeignKeys query error' {code} Due to this query being generated by the BI tool : {code} 'select 'ScottTestMart'::name as PKTABLE_CAT, n2.nspname as PKTABLE_SCHEM, c2.relname as PKTABLE_NAME, a2.attname as PKCOLUMN_NAME, 'ScottTestMart'::name as FKTABLE_CAT, n1.nspname as FKTABLE_SCHEM, c1.relname as FKTABLE_NAME, a1.attname as FKCOLUMN_NAME, i::int2 as KEY_SEQ, case ref.confupdtype when 'c' then 0::int2 when 'n' then 2::int2 when 'd' then 4::int2 when 'r' then 1::int2 else 3::int2 end as UPDATE_RULE, case ref.confdeltype when 'c' then 0::int2 when 'n' then 2::int2 when 'd' then 4::int2 when 'r' then 1::int2 else 3::int2 end as DELETE_RULE, ref.conname as FK_NAME, cn.conname as PK_NAME, case when ref.condeferrable then case when ref.condeferred then 5::int2 else 6::int2 end else 7::int2 end as DEFERRABLITY from ((((((( (select cn.oid, conrelid, conkey, confrelid, confkey, generate_series(array_lower(conkey, 1), array_upper(conkey, 1)) as i, confupdtype, confdeltype, conname, condeferrable, condeferred from pg_catalog.pg_constraint cn, pg_catalog.pg_class c, pg_catalog.pg_namespace n where contype = 'f' and confrelid = c.oid and relname = 'Categories' and n.oid = c.relnamespace and n.nspname = 'ScottTestMart' ) ref inner join pg_catalog.pg_class c1 on c1.oid = ref.conrelid) inner join pg_catalog.pg_namespace n1 on n1.oid = c1.relnamespace) inner join pg_catalog.pg_attribute a1 on a1.attrelid = c1.oid and a1.attnum = conkey[i]) inner join pg_catalog.pg_class c2 on c2.oid = ref.confrelid) inner join pg_catalog.pg_namespace n2 on n2.oid = c2.relnamespace) inner join pg_catalog.pg_attribute a2 on a2.attrelid = c2.oid and a2.attnum = confkey[i]) left outer join pg_catalog.pg_constraint cn on cn.conrelid = ref.confrelid and cn.contype = 'p') order by ref.oid, ref.i' {code}