[JBoss JIRA] (TEIID-3818) Microsoft's Power BI desktop using ODBC uses unsupported name type
by Dave Nicodemus (JIRA)
Dave Nicodemus created TEIID-3818:
-------------------------------------
Summary: Microsoft's Power BI desktop using ODBC uses unsupported name type
Key: TEIID-3818
URL: https://issues.jboss.org/browse/TEIID-3818
Project: Teiid
Issue Type: Feature Request
Components: ODBC
Affects Versions: 8.11.5
Environment: Most likely not OS / hardware dependent.
Reporter: Dave Nicodemus
Assignee: Steven Hawkins
Full history in this thread https://developer.jboss.org/message/945727#945727
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}
Apparently due to:
{quote}
Our pg layer isn't currently handling the name type. It's not a full pg emulation, rather it's targeted at supporting as much of Teiid SQL as possible. So this would need to be captured as an issue.
{quote}
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
10 years, 5 months
[JBoss JIRA] (TEIID-3813) Informix translator - convert function in definition of view is not pushed down
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3813?page=com.atlassian.jira.plugin... ]
Steven Hawkins edited comment on TEIID-3813 at 11/10/15 3:36 PM:
-----------------------------------------------------------------
Updated the informix translator to support cast using their relevant type names and standard cast syntax - more than like this is valid for only informix 9 or later. Version 8 may need to use the older pg :: style cast - and version 7 does not appear to support casting.
was (Author: shawkins):
Updated the informix translator to support cast using their relevant type names and standard cast syntax - more than like this is valid for only informix 9.
> Informix translator - convert function in definition of view is not pushed down
> -------------------------------------------------------------------------------
>
> Key: TEIID-3813
> URL: https://issues.jboss.org/browse/TEIID-3813
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Connector
> Affects Versions: 8.7.1.6_2
> Reporter: Juraj Duráni
> Assignee: Steven Hawkins
> Fix For: 8.12.2, 8.13
>
>
> I defined a view (see definition below). When I try to select all rows from the view, teiid/informix jdbc driver throws an exception.
> Teiid DDL:
> {code:sql}
> CREATE VIEW U6 (StringCol string,
> IntCol integer)
> AS
> SELECT CONVERT(BQT1.SmallA.IntNum, string) AS StringCol, BQT1.SmallA.IntNum AS IntCol
> FROM BQT1.SmallA
> UNION ALL
> SELECT BQT1.SmallB.StringNum, CONVERT(BQT1.SmallB.StringNum, integer)
> FROM BQT1.SmallB;
> }
> {code}
> Query:
> {code:sql}
> SELECT * FROM VQT.U6
> {code}
> Source-specific query
> {code:sql}
> SELECT g_1.intnum AS c_0, g_1.intnum AS c_1 FROM smalla AS g_1 UNION ALL SELECT g_0.stringnum AS c_0, g_0.stringnum AS c_1 FROM smallb AS g_0
> {code}
> Actual exception:
> {code:text}
> Caused by: java.sql.SQLException: The statement failed because corresponding column data types must be compatible for each UNION, INTERSECT, or MINUS query.
> at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:408)
> at com.informix.jdbc.IfxSqli.addException(IfxSqli.java:3178)
> ...
> {code}
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
10 years, 5 months
[JBoss JIRA] (TEIID-3813) Informix translator - convert function in definition of view is not pushed down
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3813?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-3813.
-----------------------------------
Fix Version/s: 8.12.2
8.13
Resolution: Done
Updated the informix translator to support cast using their relevant type names and standard cast syntax - more than like this is valid for only informix 9.
> Informix translator - convert function in definition of view is not pushed down
> -------------------------------------------------------------------------------
>
> Key: TEIID-3813
> URL: https://issues.jboss.org/browse/TEIID-3813
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Connector
> Affects Versions: 8.7.1.6_2
> Reporter: Juraj Duráni
> Assignee: Steven Hawkins
> Fix For: 8.12.2, 8.13
>
>
> I defined a view (see definition below). When I try to select all rows from the view, teiid/informix jdbc driver throws an exception.
> Teiid DDL:
> {code:sql}
> CREATE VIEW U6 (StringCol string,
> IntCol integer)
> AS
> SELECT CONVERT(BQT1.SmallA.IntNum, string) AS StringCol, BQT1.SmallA.IntNum AS IntCol
> FROM BQT1.SmallA
> UNION ALL
> SELECT BQT1.SmallB.StringNum, CONVERT(BQT1.SmallB.StringNum, integer)
> FROM BQT1.SmallB;
> }
> {code}
> Query:
> {code:sql}
> SELECT * FROM VQT.U6
> {code}
> Source-specific query
> {code:sql}
> SELECT g_1.intnum AS c_0, g_1.intnum AS c_1 FROM smalla AS g_1 UNION ALL SELECT g_0.stringnum AS c_0, g_0.stringnum AS c_1 FROM smallb AS g_0
> {code}
> Actual exception:
> {code:text}
> Caused by: java.sql.SQLException: The statement failed because corresponding column data types must be compatible for each UNION, INTERSECT, or MINUS query.
> at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:408)
> at com.informix.jdbc.IfxSqli.addException(IfxSqli.java:3178)
> ...
> {code}
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
10 years, 5 months
[JBoss JIRA] (TEIID-3813) Informix translator - convert function in definition of view is not pushed down
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3813?page=com.atlassian.jira.plugin... ]
Steven Hawkins updated TEIID-3813:
----------------------------------
Component/s: JDBC Connector
> Informix translator - convert function in definition of view is not pushed down
> -------------------------------------------------------------------------------
>
> Key: TEIID-3813
> URL: https://issues.jboss.org/browse/TEIID-3813
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Connector
> Affects Versions: 8.7.1.6_2
> Reporter: Juraj Duráni
> Assignee: Steven Hawkins
>
> I defined a view (see definition below). When I try to select all rows from the view, teiid/informix jdbc driver throws an exception.
> Teiid DDL:
> {code:sql}
> CREATE VIEW U6 (StringCol string,
> IntCol integer)
> AS
> SELECT CONVERT(BQT1.SmallA.IntNum, string) AS StringCol, BQT1.SmallA.IntNum AS IntCol
> FROM BQT1.SmallA
> UNION ALL
> SELECT BQT1.SmallB.StringNum, CONVERT(BQT1.SmallB.StringNum, integer)
> FROM BQT1.SmallB;
> }
> {code}
> Query:
> {code:sql}
> SELECT * FROM VQT.U6
> {code}
> Source-specific query
> {code:sql}
> SELECT g_1.intnum AS c_0, g_1.intnum AS c_1 FROM smalla AS g_1 UNION ALL SELECT g_0.stringnum AS c_0, g_0.stringnum AS c_1 FROM smallb AS g_0
> {code}
> Actual exception:
> {code:text}
> Caused by: java.sql.SQLException: The statement failed because corresponding column data types must be compatible for each UNION, INTERSECT, or MINUS query.
> at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:408)
> at com.informix.jdbc.IfxSqli.addException(IfxSqli.java:3178)
> ...
> {code}
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
10 years, 5 months
[JBoss JIRA] (TEIID-3812) HANA MIN and MAX functions not applicable on boolean
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3812?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-3812.
-----------------------------------
Fix Version/s: 8.12.2
8.13
Resolution: Done
Changed to compute the min/max by casting to tinyint and then converting the expression back to boolean.
> HANA MIN and MAX functions not applicable on boolean
> ----------------------------------------------------
>
> Key: TEIID-3812
> URL: https://issues.jboss.org/browse/TEIID-3812
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Affects Versions: 8.7.1.6_2
> Reporter: Jan Stastny
> Assignee: Steven Hawkins
> Fix For: 8.12.2, 8.13
>
>
> SAP HANA doesn't support MIN and MAX functions for BOOLEAN type.
> Compared to postgresql, which also has BOOLEAN data type, it seems inconsistent, as postgresql translator mimics MIN and MAX functions by translating them using PostgreSQL specific constructs:
> min(BooleanValue) as bool_and(BooleanValue)
> max(BooleanValue) as bool_or(BooleanValue)
> Similar issue is with CEILING and FLOOR functions.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
10 years, 5 months
[JBoss JIRA] (TEIID-3802) HANA translator modifies boolean to tinyint in type conversion
by RH Bugzilla Integration (JIRA)
[ https://issues.jboss.org/browse/TEIID-3802?page=com.atlassian.jira.plugin... ]
RH Bugzilla Integration commented on TEIID-3802:
------------------------------------------------
Van Halbert <vhalbert(a)redhat.com> changed the Status of [bug 1278409|https://bugzilla.redhat.com/show_bug.cgi?id=1278409] from NEW to MODIFIED
> HANA translator modifies boolean to tinyint in type conversion
> --------------------------------------------------------------
>
> Key: TEIID-3802
> URL: https://issues.jboss.org/browse/TEIID-3802
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Affects Versions: 8.7.1.6_2
> Reporter: Jan Stastny
> Assignee: Steven Hawkins
> Fix For: 8.12.2, 8.13
>
>
> Teiid modifies boolean data type to tinyint before pushing the query down to the HANA instance.
> This brings following issue while having a query similar to this one:
> {code:sql}
> SELECT BQT1.SMallA.BooleanValue,cast(BQT2.SmallB.CharValue as boolean) FROM BQT1.SmallA,BQT2.SmallB WHERE BQT1.SmallA.BooleanValue = cast(BQT2.SmallB.CharValue as boolean)
> {code}
> which Teiid modifies to:
> {code:sql}
> SELECT g_0."BOOLEANVALUE", cast(g_1."CHARVALUE" AS tinyint) FROM "BQT1"."SMALLA" AS g_0, "BQT2"."SMALLB" AS g_1 WHERE g_0."BOOLEANVALUE" = cast(g_1."CHARVALUE" AS tinyint)
> {code}
> But the problem is not present for:
> {code:sql}
> SELECT BQT1.SMallA.BooleanValue FROM BQT1.SmallA WHERE BQT1.SmallA.BooleanValue = cast(BQT1.SmallA.CharValue as boolean)
> {code}
> It seems that the Cartesian product and comparing values from different sources are the cause of the issue. But HANA has internal type BOOLEAN, so there might be no point at modifying the type to TINYINT after all. I also checked, that the pushed query (which causes the issue) with the casting altered to BOOLEAN runs well on HANA. The following query returns expected results when pushed directly to HANA instance:
> {code:sql}
> SELECT g_0."BOOLEANVALUE", cast(g_1."CHARVALUE" AS boolean) FROM "BQT1"."SMALLA" AS g_0, "BQT2"."SMALLB" AS g_1 WHERE g_0."BOOLEANVALUE" = cast(g_1."CHARVALUE" AS boolean)
> {code}
> NOTE: The CharValue columns are modelled as VARCHAR/string in fact. Thus the cast is a valid operation.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
10 years, 5 months
[JBoss JIRA] (TEIID-3802) HANA translator modifies boolean to tinyint in type conversion
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3802?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-3802.
-----------------------------------
Fix Version/s: 8.12.2
8.13
Assignee: Steven Hawkins (was: Ted Jones)
Resolution: Done
Opted to just cast to boolean - if we need to support an older version of hana without a boolean type, then we can restrict the pushdown at that time.
> HANA translator modifies boolean to tinyint in type conversion
> --------------------------------------------------------------
>
> Key: TEIID-3802
> URL: https://issues.jboss.org/browse/TEIID-3802
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Affects Versions: 8.7.1.6_2
> Reporter: Jan Stastny
> Assignee: Steven Hawkins
> Fix For: 8.12.2, 8.13
>
>
> Teiid modifies boolean data type to tinyint before pushing the query down to the HANA instance.
> This brings following issue while having a query similar to this one:
> {code:sql}
> SELECT BQT1.SMallA.BooleanValue,cast(BQT2.SmallB.CharValue as boolean) FROM BQT1.SmallA,BQT2.SmallB WHERE BQT1.SmallA.BooleanValue = cast(BQT2.SmallB.CharValue as boolean)
> {code}
> which Teiid modifies to:
> {code:sql}
> SELECT g_0."BOOLEANVALUE", cast(g_1."CHARVALUE" AS tinyint) FROM "BQT1"."SMALLA" AS g_0, "BQT2"."SMALLB" AS g_1 WHERE g_0."BOOLEANVALUE" = cast(g_1."CHARVALUE" AS tinyint)
> {code}
> But the problem is not present for:
> {code:sql}
> SELECT BQT1.SMallA.BooleanValue FROM BQT1.SmallA WHERE BQT1.SmallA.BooleanValue = cast(BQT1.SmallA.CharValue as boolean)
> {code}
> It seems that the Cartesian product and comparing values from different sources are the cause of the issue. But HANA has internal type BOOLEAN, so there might be no point at modifying the type to TINYINT after all. I also checked, that the pushed query (which causes the issue) with the casting altered to BOOLEAN runs well on HANA. The following query returns expected results when pushed directly to HANA instance:
> {code:sql}
> SELECT g_0."BOOLEANVALUE", cast(g_1."CHARVALUE" AS boolean) FROM "BQT1"."SMALLA" AS g_0, "BQT2"."SMALLB" AS g_1 WHERE g_0."BOOLEANVALUE" = cast(g_1."CHARVALUE" AS boolean)
> {code}
> NOTE: The CharValue columns are modelled as VARCHAR/string in fact. Thus the cast is a valid operation.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
10 years, 5 months
[JBoss JIRA] (TEIID-3817) HANA FLOOR and CELING functions not applicable to boolean
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3817?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-3817.
-----------------------------------
Fix Version/s: 8.12.2
8.13
Resolution: Done
Added a general cast to tinyint to prevent issues with non-integral types.
> HANA FLOOR and CELING functions not applicable to boolean
> ---------------------------------------------------------
>
> Key: TEIID-3817
> URL: https://issues.jboss.org/browse/TEIID-3817
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Affects Versions: 8.7.1.6_2
> Reporter: Jan Stastny
> Assignee: Steven Hawkins
> Fix For: 8.12.2, 8.13
>
>
> SAP HANA doesn't support FLOOR and CEILING functions for BOOLEAN type.
> Compared to postgresql, which also has BOOLEAN data type, it seems inconsistent.
> When querying postgres directly:
> {code:sql}
> SELECT CEILING(BooleanValue) FROM BQT1.SmallA
> {code}
> is not valid query.
> But through Teiid, it returns double values corresponding to the boolean value provided:
> 0.0 for CEILING(false)
> 1.0 for CELING(true)
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
10 years, 5 months