[JBoss JIRA] (TEIID-3808) Informix translator - date/time/timestamp values are adjusted differently according to timezone
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3808?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-3808.
-----------------------------------
Resolution: Deferred
Added a KI to the 8.12.x release notes and updated the docs.
> Informix translator - date/time/timestamp values are adjusted differently according to timezone
> -----------------------------------------------------------------------------------------------
>
> Key: TEIID-3808
> URL: https://issues.jboss.org/browse/TEIID-3808
> Project: Teiid
> Issue Type: Quality Risk
> Affects Versions: 8.7.1.6_2
> Reporter: Juraj Duráni
> Assignee: Steven Hawkins
>
> The Informix translator adjusts only time value according to server's timezone. Timestamp values are adjusted only if 'DatabaseTimeZone' property is set. Date values are not adjusted at all.
> I am not sure what is Teiid's logic in this case. Is the difference between server's timezone and database's timezone taken into account? Or only the timezone of the database?
> -------------------
> Server's timezone - GMT+1
> DatabaseTimeZone property - not overridden
> Result:
> |time|date|timestamp|
> |01:00:00|2000-01-01|2000-01-01 00:00:00.0|
> Server's timezone - GMT+5
> DatabaseTimeZone property - not overridden
> Result:
> |time|date|timestamp|
> |05:00:00|2000-01-01|2000-01-01 00:00:00.0|
> Server's timezone - GMT+5
> DatabaseTimeZone property - GMT+1
> Result:
> |time|date|timestamp|
> |01:00:00|2000-01-01|2000-01-01 04:00:00.0|
> Server's timezone - GMT+5
> DatabaseTimeZone property - GMT-1
> Result (int this case
> |time|date|timestamp|
> |23:00:00|2000-01-01|2000-01-01 06:00:00.0|
> Informix:
> {code:sql}
> ...
> datevalue date,
> timevalue datetime hour to fraction(1),
> timestampvalue datetime year to fraction(1),
> ...
> {code}
> VDB - foreign table:
> {code:sql}
> ...
> DateValue date OPTIONS (NATIVE_TYPE 'date', NAMEINSOURCE 'datevalue'),
> TimeValue timestamp OPTIONS (NATIVE_TYPE 'datetime hour to fraction(1)', NAMEINSOURCE 'timevalue'),
> TimestampValue timestamp OPTIONS (NATIVE_TYPE 'datetime year to fraction(1)', NAMEINSOURCE 'timestampvalue'),
> ...
> {code}
> VDB - view:
> {code:sql}
> ...
> DateValue date,
> TimeValue time,
> TimestampValue timestamp,
> ...
> AS ... DateValue, convert(TimeValue, time), TimestampValue, ...
> {code}
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 1 month
[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 commented on TEIID-3813:
---------------------------------------
The informix translator currently is nothing more than ansi-jdbc with convert/cast marked as supported - however there is no additional logic to actually implement the casts. I'll look more into why it's like that.
> 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
> 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)
9 years, 1 month
[JBoss JIRA] (TEIID-3815) Oracle translator - OFFSET function does not work
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3815?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-3815.
-----------------------------------
Fix Version/s: 8.13
Resolution: Done
Updated the logic to check for max int and to prevent overflow.
> Oracle translator - OFFSET function does not work
> -------------------------------------------------
>
> Key: TEIID-3815
> URL: https://issues.jboss.org/browse/TEIID-3815
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 8.7.2.6_2
> Reporter: Juraj Duráni
> Assignee: Steven Hawkins
> Fix For: 8.12.2, 8.13
>
>
> Oracle translator translates OFFSET function as:
> {code:sql}
> SELECT c_0 FROM (SELECT VIEW_FOR_LIMIT.*, ROWNUM ROWNUM_ FROM (***Query***) VIEW_FOR_LIMIT WHERE ROWNUM <= -2147483604) WHERE ROWNUM_ > 45
> {code}
> This always returns an empty result because of *ROWNUM <= -2147483604*.
> Example:
> Query:
> {code:sql}
> SELECT INTKEY FROM bqt1.smalla ORDER BY INTKEY OFFSET 45 ROWS
> {code}
> Source-specific command:
> {code:sql}
> SELECT c_0 FROM (SELECT VIEW_FOR_LIMIT.*, ROWNUM ROWNUM_ FROM (SELECT trunc(g_0."INTKEY") AS c_0 FROM "DV"."SMALLA" g_0 ORDER BY c_0) VIEW_FOR_LIMIT WHERE ROWNUM <= -2147483604) WHERE ROWNUM_ > 45
> {code}
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 1 month
[JBoss JIRA] (TEIID-3815) Oracle translator - OFFSET function does not work
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3815?page=com.atlassian.jira.plugin... ]
Steven Hawkins updated TEIID-3815:
----------------------------------
Fix Version/s: 8.12.2
The limit is set to max int, but that is not being checked by the translator so it produces an overflow value with the offset.
> Oracle translator - OFFSET function does not work
> -------------------------------------------------
>
> Key: TEIID-3815
> URL: https://issues.jboss.org/browse/TEIID-3815
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 8.7.2.6_2
> Reporter: Juraj Duráni
> Assignee: Steven Hawkins
> Fix For: 8.12.2
>
>
> Oracle translator translates OFFSET function as:
> {code:sql}
> SELECT c_0 FROM (SELECT VIEW_FOR_LIMIT.*, ROWNUM ROWNUM_ FROM (***Query***) VIEW_FOR_LIMIT WHERE ROWNUM <= -2147483604) WHERE ROWNUM_ > 45
> {code}
> This always returns an empty result because of *ROWNUM <= -2147483604*.
> Example:
> Query:
> {code:sql}
> SELECT INTKEY FROM bqt1.smalla ORDER BY INTKEY OFFSET 45 ROWS
> {code}
> Source-specific command:
> {code:sql}
> SELECT c_0 FROM (SELECT VIEW_FOR_LIMIT.*, ROWNUM ROWNUM_ FROM (SELECT trunc(g_0."INTKEY") AS c_0 FROM "DV"."SMALLA" g_0 ORDER BY c_0) VIEW_FOR_LIMIT WHERE ROWNUM <= -2147483604) WHERE ROWNUM_ > 45
> {code}
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 1 month
[JBoss JIRA] (TEIID-3775) HBase - GROUP BY clause returns duplicates results
by RH Bugzilla Integration (JIRA)
[ https://issues.jboss.org/browse/TEIID-3775?page=com.atlassian.jira.plugin... ]
RH Bugzilla Integration commented on TEIID-3775:
------------------------------------------------
Van Halbert <vhalbert(a)redhat.com> changed the Status of [bug 1272969|https://bugzilla.redhat.com/show_bug.cgi?id=1272969] from NEW to CLOSED
> HBase - GROUP BY clause returns duplicates results
> --------------------------------------------------
>
> Key: TEIID-3775
> URL: https://issues.jboss.org/browse/TEIID-3775
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 8.7.1.6_2
> Reporter: Juraj Duráni
> Assignee: Steven Hawkins
>
> I have a view defined as follows:
> {code:sql}
> CREATE VIEW U8 (IntKey integer,
> StringKey string,
> IntNum integer,
> StringNum string,
> FloatNum float,
> LongNum long,
> DoubleNum double,
> ByteNum byte,
> DateValue date,
> TimeValue time,
> TimestampValue timestamp,
> BooleanValue boolean,
> CharValue char,
> ShortValue short,
> BigIntegerValue biginteger,
> BigDecimalValue bigdecimal)
> AS
> SELECT BQT1.SmallA.IntKey, BQT1.SmallA.StringKey, BQT1.SmallA.IntNum, BQT1.SmallA.StringNum,
> BQT1.SmallA.FloatNum, BQT1.SmallA.LongNum, BQT1.SmallA.DoubleNum, BQT1.SmallA.ByteNum,
> BQT1.SmallA.DateValue, BQT1.SmallA.TimeValue, BQT1.SmallA.TimestampValue, BQT1.SmallA.BooleanValue,
> BQT1.SmallA.CharValue, BQT1.SmallA.ShortValue, BQT1.SmallA.BigIntegerValue, BQT1.SmallA.BigDecimalValue
> FROM BQT1.SmallA
> UNION
> SELECT BQT1.SmallB.IntKey, BQT1.SmallB.StringKey, BQT1.SmallB.IntNum, BQT1.SmallB.StringNum,
> BQT1.SmallB.FloatNum, BQT1.SmallB.LongNum, BQT1.SmallB.DoubleNum, BQT1.SmallB.ByteNum,
> BQT1.SmallB.DateValue, BQT1.SmallB.TimeValue, BQT1.SmallB.TimestampValue, BQT1.SmallB.BooleanValue,
> BQT1.SmallB.CharValue, BQT1.SmallB.ShortValue, BQT1.SmallB.BigIntegerValue, BQT1.SmallB.BigDecimalValue
> FROM BQT1.SmallB ORDER BY IntKey;
> {code}
> Query *SELECT StringNum FROM VQT.U8 GROUP BY StringNum* returns duplicate results (multiple null).
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 1 month
[JBoss JIRA] (TEIID-3816) Informix translator - <> ALL is translated as NOT IN - this statement seem to not work in Infromix
by RH Bugzilla Integration (JIRA)
[ https://issues.jboss.org/browse/TEIID-3816?page=com.atlassian.jira.plugin... ]
RH Bugzilla Integration updated TEIID-3816:
-------------------------------------------
Bugzilla References: https://bugzilla.redhat.com/show_bug.cgi?id=1279473
Bugzilla Update: Perform
> Informix translator - <> ALL is translated as NOT IN - this statement seem to not work in Infromix
> --------------------------------------------------------------------------------------------------
>
> Key: TEIID-3816
> URL: https://issues.jboss.org/browse/TEIID-3816
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 8.7.2.6_2
> Reporter: Juraj Duráni
> Assignee: Steven Hawkins
>
> Query:
> {code:sql}
> SELECT INTKEY, BOOLEANVALUE FROM BQT1.SMALLA WHERE BOOLEANVALUE <> ALL (SELECT BOOLEANVALUE FROM BQT1.SMALLA WHERE INTKEY = 40)
> {code}
> Source-specific command:
> {code:sql}
> SELECT g_0.IntKey, g_0.BooleanValue FROM Source.SmallA AS g_0 WHERE g_0.BooleanValue NOT IN (SELECT g_1.BooleanValue FROM Source.SmallA AS g_1 WHERE g_1.IntKey = 40)
> {code}
> *Note: boolean value for row where Intkey = 40 is false.*
> Expected result:
> |intkey|booleanvalue|
> |1|true|
> |...|...|
> Actual result:
> |intkey|booleanvalue|
> |1|false|
> |...|...|
> This seems to be an Informix issue as neither '<> ALL' nor 'NOT IN' works.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 1 month
[JBoss JIRA] (TEIID-3816) Informix translator - <> ALL is translated as NOT IN - this statement seem to not work in Infromix
by Juraj Duráni (JIRA)
Juraj Duráni created TEIID-3816:
-----------------------------------
Summary: Informix translator - <> ALL is translated as NOT IN - this statement seem to not work in Infromix
Key: TEIID-3816
URL: https://issues.jboss.org/browse/TEIID-3816
Project: Teiid
Issue Type: Bug
Affects Versions: 8.7.2.6_2
Reporter: Juraj Duráni
Assignee: Steven Hawkins
Query:
{code:sql}
SELECT INTKEY, BOOLEANVALUE FROM BQT1.SMALLA WHERE BOOLEANVALUE <> ALL (SELECT BOOLEANVALUE FROM BQT1.SMALLA WHERE INTKEY = 40)
{code}
Source-specific command:
{code:sql}
SELECT g_0.IntKey, g_0.BooleanValue FROM Source.SmallA AS g_0 WHERE g_0.BooleanValue NOT IN (SELECT g_1.BooleanValue FROM Source.SmallA AS g_1 WHERE g_1.IntKey = 40)
{code}
*Note: boolean value for row where Intkey = 40 is false.*
Expected result:
|intkey|booleanvalue|
|1|true|
|...|...|
Actual result:
|intkey|booleanvalue|
|1|false|
|...|...|
This seems to be an Informix issue as neither '<> ALL' nor 'NOT IN' works.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 1 month
[JBoss JIRA] (TEIID-3813) Informix translator - convert function in definition of view is not pushed down
by Juraj Duráni (JIRA)
[ https://issues.jboss.org/browse/TEIID-3813?page=com.atlassian.jira.plugin... ]
Juraj Duráni commented on TEIID-3813:
-------------------------------------
Another examples:
1. '+'/'-'/'*'/'/' operations are not allowed on boolean values
- Query
{code:sql}
SELECT BooleanValue, (convert(BooleanValue, float)+2) AS BooleanValuePlus2, (convert(BooleanValue, float) - 2) AS BooleanValueMinus2, (convert(BooleanValue, float)*2) AS BooleanValueTimes2, (convert(BooleanValue, float)/2) AS BooleanValueDiv2 FROM BQT1.SmallA WHERE IntKey >=0 AND IntKey <=5 ORDER BY BooleanValue
{code}
- Source-specific command
{code:sql}
SELECT g_0.booleanvalue AS c_0, (g_0.booleanvalue + 2.0) AS c_1, (g_0.booleanvalue - 2.0) AS c_2, (g_0.booleanvalue * 2.0) AS c_3, (g_0.booleanvalue / 2.0) AS c_4 FROM smalla AS g_0 WHERE g_0.intkey >= 0 AND g_0.intkey <= 5 ORDER BY c_0
{code}
However, infromix doe not support conversion from boolean to numeric type. But in my opinion it would be better to get exception concerning casting rather than "Routine (equal/plus/minut/...) can not be resolved."
> 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
> 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)
9 years, 1 month
[JBoss JIRA] (TEIID-3815) Oracle translator - OFFSET function does not work
by RH Bugzilla Integration (JIRA)
[ https://issues.jboss.org/browse/TEIID-3815?page=com.atlassian.jira.plugin... ]
RH Bugzilla Integration updated TEIID-3815:
-------------------------------------------
Bugzilla References: https://bugzilla.redhat.com/show_bug.cgi?id=1279460
Bugzilla Update: Perform
> Oracle translator - OFFSET function does not work
> -------------------------------------------------
>
> Key: TEIID-3815
> URL: https://issues.jboss.org/browse/TEIID-3815
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 8.7.2.6_2
> Reporter: Juraj Duráni
> Assignee: Steven Hawkins
>
> Oracle translator translates OFFSET function as:
> {code:sql}
> SELECT c_0 FROM (SELECT VIEW_FOR_LIMIT.*, ROWNUM ROWNUM_ FROM (***Query***) VIEW_FOR_LIMIT WHERE ROWNUM <= -2147483604) WHERE ROWNUM_ > 45
> {code}
> This always returns an empty result because of *ROWNUM <= -2147483604*.
> Example:
> Query:
> {code:sql}
> SELECT INTKEY FROM bqt1.smalla ORDER BY INTKEY OFFSET 45 ROWS
> {code}
> Source-specific command:
> {code:sql}
> SELECT c_0 FROM (SELECT VIEW_FOR_LIMIT.*, ROWNUM ROWNUM_ FROM (SELECT trunc(g_0."INTKEY") AS c_0 FROM "DV"."SMALLA" g_0 ORDER BY c_0) VIEW_FOR_LIMIT WHERE ROWNUM <= -2147483604) WHERE ROWNUM_ > 45
> {code}
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 1 month
[JBoss JIRA] (TEIID-3815) Oracle translator - OFFSET function does not work
by Juraj Duráni (JIRA)
Juraj Duráni created TEIID-3815:
-----------------------------------
Summary: Oracle translator - OFFSET function does not work
Key: TEIID-3815
URL: https://issues.jboss.org/browse/TEIID-3815
Project: Teiid
Issue Type: Bug
Affects Versions: 8.7.2.6_2
Reporter: Juraj Duráni
Assignee: Steven Hawkins
Oracle translator translates OFFSET function as:
{code:sql}
SELECT c_0 FROM (SELECT VIEW_FOR_LIMIT.*, ROWNUM ROWNUM_ FROM (***Query***) VIEW_FOR_LIMIT WHERE ROWNUM <= -2147483604) WHERE ROWNUM_ > 45
{code}
This always returns an empty result because of *ROWNUM <= -2147483604*.
Example:
Query:
{code:sql}
SELECT INTKEY FROM bqt1.smalla ORDER BY INTKEY OFFSET 45 ROWS
{code}
Source-specific command:
{code:sql}
SELECT c_0 FROM (SELECT VIEW_FOR_LIMIT.*, ROWNUM ROWNUM_ FROM (SELECT trunc(g_0."INTKEY") AS c_0 FROM "DV"."SMALLA" g_0 ORDER BY c_0) VIEW_FOR_LIMIT WHERE ROWNUM <= -2147483604) WHERE ROWNUM_ > 45
{code}
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 1 month