[JBoss JIRA] (TEIID-1776) Ingres as source - Source Error Codes 2118, 2501, and 263071 being returned
by Steven Hawkins (Resolved) (JIRA)
[ https://issues.jboss.org/browse/TEIID-1776?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-1776.
-----------------------------------
Fix Version/s: 7.6
Resolution: Done
time and timestamp are now used as the time/timestamp cast arguments. Also changing the translator to use implicit widening numeric conversions fixed 4. Note that 7.4.1/7.5 will have issues with Ingres time types until patched.
I'll link this issue to the existing subquery issue for if/when we want to address it. Since it's erroring and not returning wrong data, it's not a high priority to fix.
> Ingres as source - Source Error Codes 2118, 2501, and 263071 being returned
> ---------------------------------------------------------------------------
>
> Key: TEIID-1776
> URL: https://issues.jboss.org/browse/TEIID-1776
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Affects Versions: 7.4.1
> Reporter: Warren Gibson
> Assignee: Steven Hawkins
> Fix For: 7.6
>
>
> EDS Query Testing is getting unexpected results on 4 queries. The queries are
> listed below:
> SELECT TIMEVALUE, BYTENUM, (SELECT B.FLOATNUM FROM BQT1.SMALLA AS B WHERE (B.INTKEY = (SELECT A.INTKEY FROM BQT1.SMALLA AS A WHERE (A.STRINGNUM > 10) AND (A.INTKEY = B.INTKEY))) AND (B.INTKEY = A.INTKEY)) FROM bqt1.smalla AS a
> Error Code:2118 Message:Remote org.teiid.core.TeiidProcessingException: Error Code:2118 Message:Source: Error Code:2118 Message:'Invalid qualifier 'g_1'. 'g_1' must be declared in the FROM clause or equivalent. If used in a target list, 'g_1' must be declared at the current scope.' error executing statement(s): [Prepared Values: [] SQL: SELECT g_0.timevalue, cast(g_0.bytenum AS tinyint), (SELECT g_1.floatnum FROM smalla AS g_1 WHERE g_1.intkey = g_0.intkey AND g_1.intkey = (SELECT g_2.intkey FROM smalla AS g_2 WHERE g_2.stringnum > '10' AND g_2.intkey = g_1.intkey)) FROM smalla AS g_0]
> ********************************************************
> SELECT BQT1.SmallA.TimeValue, BQT2.SmallB.TimestampValue FROM BQT1.SmallA, BQT2.SmallB WHERE BQT1.SmallA.TimeValue = convert(BQT2.SmallB.TimestampValue, time) AND BQT1.SmallA.IntKey >= 0 AND BQT2.SmallB.IntKey >= 0 ORDER BY BQT1.SmallA.TimeValue
> Error Code:2501 Message:Remote org.teiid.core.TeiidProcessingException: Error Code:2501 Message:Source: Error Code:2501 Message:'line 1, Syntax error on 'with time'. The correct syntax is: SELECT [ALL|DISTINCT] target_list FROM table(s) [WHERE search_cond] [GROUP BY col(s)] [HAVING search_cond] [UNION subselect] [ORDER BY col(s)]' error executing statement(s): [Prepared Values: [] SQL: SELECT g_0.timevalue AS c_0, g_1.timestampvalue AS c_1 FROM smalla AS g_0, smallb AS g_1 WHERE g_0.timevalue = cast(g_1.timestampvalue AS time with time zone) AND g_0.intkey >= 0 AND g_1.intkey >= 0 ORDER BY c_0]
> ********************************************************
> SELECT BQT1.SmallA.TimeValue, BQT1.SmallB.TimestampValue FROM BQT1.SmallA, BQT1.SmallB WHERE BQT1.SmallA.TimeValue = convert(BQT1.SmallB.TimestampValue, time) AND BQT1.SmallA.IntKey >= 0 AND BQT1.SmallB.IntKey >= 0 ORDER BY BQT1.SmallA.TimeValue
> Error Code:2501 Message:Remote org.teiid.core.TeiidProcessingException: Error Code:2501 Message:Source: Error Code:2501 Message:'line 1, Syntax error on 'with time'. The correct syntax is: SELECT [ALL|DISTINCT] target_list FROM table(s) [WHERE search_cond] [GROUP BY col(s)] [HAVING search_cond] [UNION subselect] [ORDER BY col(s)]' error executing statement(s): [Prepared Values: [] SQL: SELECT g_0.timevalue AS c_0, g_1.timestampvalue AS c_1 FROM smalla AS g_0, smallb AS g_1 WHERE g_0.timevalue = cast(g_1.timestampvalue AS time with time zone) AND g_0.intkey >= 0 AND g_1.intkey >= 0 ORDER BY c_0]
> *******************************************************
> SELECT intkey, stringkey, floatnum, bytenum, (SELECT bytenum FROM bqt1.smalla AS b WHERE (bytenum = a.longnum) AND (intkey = '10')) AS longnum FROM bqt1.smalla AS a
> Error Code:263071 Message:Remote org.teiid.core.TeiidProcessingException: Error Code:263071 Message:Source: Error Code:263071 Message:'consistency check - all elements of boolean factor do not have same join id' error executing statement(s): [Prepared Values: [] SQL: SELECT g_0.intkey, g_0.stringkey, g_0.floatnum, cast(g_0.bytenum AS tinyint), (SELECT cast(g_1.bytenum AS tinyint) FROM smalla AS g_1 WHERE cast(cast(g_1.bytenum AS tinyint) AS bigint) = g_0.longnum AND g_1.intkey = 10) FROM smalla AS g_0]
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.jboss.org/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
12 years, 8 months
[JBoss JIRA] (TEIID-1774) Ingres as source - ORDER BY on TimeValue not properly ordering results as expected
by Warren Gibson (Commented) (JIRA)
[ https://issues.jboss.org/browse/TEIID-1774?page=com.atlassian.jira.plugin... ]
Warren Gibson commented on TEIID-1774:
--------------------------------------
I built the timetest (x time) sample also. All the metadata for the BQT TimeValue column is the same as the timetest (x time) example. The standard BQT test data contains 00:00:00 in some rows. After testing further I find the order gets confused and starts sequencing again following a 00:00:00 TimeValue. Not sure why.
Example:
22 22:00:00
46 22:00:00
47 23:00:00
48 00:00:00
24 00:00:00
0 00:00:00
1 01:00:00
49 01:00:00
25 01:00:00
2 02:00:00
26 02:00:00
3 03:00:00
> Ingres as source - ORDER BY on TimeValue not properly ordering results as expected
> ----------------------------------------------------------------------------------
>
> Key: TEIID-1774
> URL: https://issues.jboss.org/browse/TEIID-1774
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Affects Versions: 7.4.1
> Reporter: Warren Gibson
> Assignee: Steven Hawkins
>
> EDS Query Testing is getting unexpected results on 68 queries involving ORDER BY on TimeValue. When an ORDER BY is placed on TimeValue the returned results are not ordered as expected.
> An example query is: SELECT BQT1.SmallA.TimeValue FROM BQT1.SmallA ORDER BY TimeValue
> The returned results are:
> TimeValue
> 05:00:00
> 05:00:00
> 06:00:00
> 06:00:00
> 07:00:00
> 08:00:00
> 08:00:00
> 09:00:00
> 09:00:00
> 10:00:00
> 10:00:00
> 11:00:00
> 11:00:00
> 12:00:00
> 12:00:00
> 13:00:00
> 13:00:00
> 14:00:00
> 14:00:00
> 15:00:00
> 16:00:00
> 16:00:00
> 17:00:00
> 17:00:00
> 18:00:00
> 18:00:00
> 19:00:00
> 19:00:00
> 20:00:00
> 20:00:00
> 21:00:00
> 21:00:00
> 22:00:00
> 22:00:00
> 23:00:00
> 00:00:00
> 00:00:00
> 00:00:00
> 01:00:00
> 01:00:00
> 01:00:00
> 02:00:00
> 02:00:00
> 03:00:00
> 03:00:00
> 04:00:00
> 04:00:00
> <null>
> <null>
> <null>
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.jboss.org/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
12 years, 8 months
[JBoss JIRA] (TEIID-1775) Ingres as source - WHERE CLAUSE is not returning expected results in some cases
by Steven Hawkins (Resolved) (JIRA)
[ https://issues.jboss.org/browse/TEIID-1775?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-1775.
-----------------------------------
Resolution: Won't Fix
See the resolution of TEIID-1774. Freshly created time values do not exhibit the same behavior as the bqt queries. The workaround would be to model the column as timestamp and cast to time. If this becomes a customer issue then a fix can be investigated based upon the actual native type.
As for the string form of the floating point values, there is already a KI covering that.
> Ingres as source - WHERE CLAUSE is not returning expected results in some cases
> -------------------------------------------------------------------------------
>
> Key: TEIID-1775
> URL: https://issues.jboss.org/browse/TEIID-1775
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Affects Versions: 7.4.1
> Reporter: Warren Gibson
> Assignee: Steven Hawkins
>
> EDS Query Testing is getting unexpected results on 24 queries involving WHERE CLAUSE. A few examples queries are given below:
> SELECT BQT1.SmallA.TimeValue FROM BQT1.SmallA WHERE BQT1.SmallA.TimeValue > '17:00:00' ORDER BY TimeValue (Expected 11 records but received 23)
> SELECT INTKEY, FLOATNUM FROM BQT1.SmallA WHERE RIGHT(FLOATNUM, 1) <> 0 ORDER BY INTKEY (Expected 43 records but received only 0)
> SELECT BQT1.SmallA.StringKey, BQT2.SmallB.FloatNum FROM BQT1.SmallA, BQT2.SmallB WHERE BQT1.SmallA.StringKey = convert(BQT2.SmallB.FloatNum, string) AND BQT1.SmallA.IntKey >= 0 AND BQT2.SmallB.IntKey >= 0 ORDER BY BQT1.SmallA.StringKey (Expected 25 records but received only 0)
> SELECT IntKey, TimeValue FROM (SELECT IntKey, TimeValue FROM BQT2.SmallA WHERE IntKey > 5) AS x WHERE TimeValue >= '17:00:00' ( Expected 13 records but received 20)
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.jboss.org/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
12 years, 8 months
[JBoss JIRA] (TEIID-1774) Ingres as source - ORDER BY on TimeValue not properly ordering results as expected
by Steven Hawkins (Resolved) (JIRA)
[ https://issues.jboss.org/browse/TEIID-1774?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-1774.
-----------------------------------
Resolution: Won't Fix
Actually if I create an entirely new table, e.g.
"create table timetest (x time)"
any values inserted, e.g.
"insert into timetest (x) values ('05:00:00')"
are ordered correctly by an order by. so the root cause here is either with how the table was defined or with how values were entered.
the workaround in any case would be to model the column as a timestamp and cast to time.
> Ingres as source - ORDER BY on TimeValue not properly ordering results as expected
> ----------------------------------------------------------------------------------
>
> Key: TEIID-1774
> URL: https://issues.jboss.org/browse/TEIID-1774
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Affects Versions: 7.4.1
> Reporter: Warren Gibson
> Assignee: Steven Hawkins
>
> EDS Query Testing is getting unexpected results on 68 queries involving ORDER BY on TimeValue. When an ORDER BY is placed on TimeValue the returned results are not ordered as expected.
> An example query is: SELECT BQT1.SmallA.TimeValue FROM BQT1.SmallA ORDER BY TimeValue
> The returned results are:
> TimeValue
> 05:00:00
> 05:00:00
> 06:00:00
> 06:00:00
> 07:00:00
> 08:00:00
> 08:00:00
> 09:00:00
> 09:00:00
> 10:00:00
> 10:00:00
> 11:00:00
> 11:00:00
> 12:00:00
> 12:00:00
> 13:00:00
> 13:00:00
> 14:00:00
> 14:00:00
> 15:00:00
> 16:00:00
> 16:00:00
> 17:00:00
> 17:00:00
> 18:00:00
> 18:00:00
> 19:00:00
> 19:00:00
> 20:00:00
> 20:00:00
> 21:00:00
> 21:00:00
> 22:00:00
> 22:00:00
> 23:00:00
> 00:00:00
> 00:00:00
> 00:00:00
> 01:00:00
> 01:00:00
> 01:00:00
> 02:00:00
> 02:00:00
> 03:00:00
> 03:00:00
> 04:00:00
> 04:00:00
> <null>
> <null>
> <null>
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.jboss.org/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
12 years, 8 months
[JBoss JIRA] (TEIID-1774) Ingres as source - ORDER BY on TimeValue not properly ordering results as expected
by Steven Hawkins (Commented) (JIRA)
[ https://issues.jboss.org/browse/TEIID-1774?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-1774:
---------------------------------------
It is unclear why that is the return order. If I use a query such as
"select cast(timevalue as time) as t from smalla order by t"
then I get the expected result.
I'm inclined to KI. Our workaround that works at least on ingres10 is to do the cast on the order by column "order by cast(timevalue as time)"
> Ingres as source - ORDER BY on TimeValue not properly ordering results as expected
> ----------------------------------------------------------------------------------
>
> Key: TEIID-1774
> URL: https://issues.jboss.org/browse/TEIID-1774
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Affects Versions: 7.4.1
> Reporter: Warren Gibson
> Assignee: Steven Hawkins
>
> EDS Query Testing is getting unexpected results on 68 queries involving ORDER BY on TimeValue. When an ORDER BY is placed on TimeValue the returned results are not ordered as expected.
> An example query is: SELECT BQT1.SmallA.TimeValue FROM BQT1.SmallA ORDER BY TimeValue
> The returned results are:
> TimeValue
> 05:00:00
> 05:00:00
> 06:00:00
> 06:00:00
> 07:00:00
> 08:00:00
> 08:00:00
> 09:00:00
> 09:00:00
> 10:00:00
> 10:00:00
> 11:00:00
> 11:00:00
> 12:00:00
> 12:00:00
> 13:00:00
> 13:00:00
> 14:00:00
> 14:00:00
> 15:00:00
> 16:00:00
> 16:00:00
> 17:00:00
> 17:00:00
> 18:00:00
> 18:00:00
> 19:00:00
> 19:00:00
> 20:00:00
> 20:00:00
> 21:00:00
> 21:00:00
> 22:00:00
> 22:00:00
> 23:00:00
> 00:00:00
> 00:00:00
> 00:00:00
> 01:00:00
> 01:00:00
> 01:00:00
> 02:00:00
> 02:00:00
> 03:00:00
> 03:00:00
> 04:00:00
> 04:00:00
> <null>
> <null>
> <null>
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.jboss.org/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
12 years, 8 months
[JBoss JIRA] (TEIID-1777) Ingres as source - 3 VQT Queries using COUNT(*) returning unexpected results
by Steven Hawkins (Resolved) (JIRA)
[ https://issues.jboss.org/browse/TEIID-1777?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-1777.
-----------------------------------
Resolution: Deferred
Ah I misread. Ingres does indeed return the wrong result. They cannot handle nested aggregates. A new capability would be needed to workaround their limitation.
Starting with Teiid 7.5 I believe that we'll detect that query 2 must return a count of 1 regardless.
Given that these are uncommon scenarios, at this point I'll resolve as deferred, but we can workaround by inhibiting pushdown if a customer needs it.
> Ingres as source - 3 VQT Queries using COUNT(*) returning unexpected results
> ----------------------------------------------------------------------------
>
> Key: TEIID-1777
> URL: https://issues.jboss.org/browse/TEIID-1777
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Affects Versions: 7.4.1
> Reporter: Warren Gibson
> Assignee: Steven Hawkins
>
> EDS Query Testing is getting unexpected results on the following queries:
> 3 queries involving COUNT(*)
>
> QUERY: SELECT COUNT(*) FROM VQT.Agg1
> COMMENT: Expected count of 47 rows but got count of 1.
> Agg1 transformation: SELECT BQT1.SmallA.StringNum FROM BQT1.SmallA GROUP BY BQT1.SmallA.StringNum
> QUERY: SELECT COUNT(*) FROM VQT.Agg3
> COMMENT: Expected count of 1 but got count of 50.
> Agg3 transformation: SELECT COUNT(*) FROM BQT1.SmallA
> QUERY: SELECT COUNT(*) FROM VQT.Agg4
> COMMENT: Expected count of 47 but got count of 1.
> Agg4 transformation: SELECT MIN(BQT1.SmallA.StringNum), SUM(BQT1.SmallA.IntKey), AVG(BQT1.SmallA.IntKey) FROM BQT1.SmallA GROUP BY BQT1.SmallA.StringNum
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.jboss.org/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
12 years, 8 months
[JBoss JIRA] (TEIID-1777) Ingres as source - 3 VQT Queries using COUNT(*) returning unexpected results
by Warren Gibson (Commented) (JIRA)
[ https://issues.jboss.org/browse/TEIID-1777?page=com.atlassian.jira.plugin... ]
Warren Gibson commented on TEIID-1777:
--------------------------------------
Pushdown query for 2 is: Source-specific command: SELECT COUNT(*) FROM (SELECT COUNT(*) AS c_0 FROM smalla AS g_0) AS v_0
> Ingres as source - 3 VQT Queries using COUNT(*) returning unexpected results
> ----------------------------------------------------------------------------
>
> Key: TEIID-1777
> URL: https://issues.jboss.org/browse/TEIID-1777
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Affects Versions: 7.4.1
> Reporter: Warren Gibson
> Assignee: Steven Hawkins
>
> EDS Query Testing is getting unexpected results on the following queries:
> 3 queries involving COUNT(*)
>
> QUERY: SELECT COUNT(*) FROM VQT.Agg1
> COMMENT: Expected count of 47 rows but got count of 1.
> Agg1 transformation: SELECT BQT1.SmallA.StringNum FROM BQT1.SmallA GROUP BY BQT1.SmallA.StringNum
> QUERY: SELECT COUNT(*) FROM VQT.Agg3
> COMMENT: Expected count of 1 but got count of 50.
> Agg3 transformation: SELECT COUNT(*) FROM BQT1.SmallA
> QUERY: SELECT COUNT(*) FROM VQT.Agg4
> COMMENT: Expected count of 47 but got count of 1.
> Agg4 transformation: SELECT MIN(BQT1.SmallA.StringNum), SUM(BQT1.SmallA.IntKey), AVG(BQT1.SmallA.IntKey) FROM BQT1.SmallA GROUP BY BQT1.SmallA.StringNum
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.jboss.org/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
12 years, 8 months