January 2010 - teiid-issues - Jboss List Archives

[JBoss JIRA] Created: (TEIID-700) Adding ORDER BY to a specific query (see details) gets ORA-00904: "C_0": invalid identifier

by Paul Nittel (JIRA)

Adding ORDER BY to a specific query (see details) gets ORA-00904: "C_0": invalid identifier ------------------------------------------------------------------------------------------- Key: TEIID-700 URL: https://jira.jboss.org/jira/browse/TEIID-700 Project: Teiid Issue Type: Bug Components: Query Engine Affects Versions: 6.1.0 Environment: Fedora 10, OpenJDK 1.6.0, Teiid 6.1.0 Reporter: Paul Nittel Assignee: Steven Hawkins I created a query which worked fine until I added the ORDER BY clause: (SELECT INTKEY, 'A' AS WHEREFROM FROM BQT1.SMALLA LIMIT 6) UNION ALL (SELECT INTKEY, 'B' AS WHEREFROM FROM BQT2.SMALLB LIMIT 5) ORDER BY INTKEY, WHEREFROM LIMIT 10 Now, when executed, it generates this: Jul 01, 2009 10:37:09.727 [Worker5_QueryProcessorQueue47|0] ERROR <DQP|0> Unexpected exception for request 0.16 [MetaMatrixComponentException] 904: Error Code:904 Message:Error Code:904 Message:Error occurred on connector BQT1 Connector<8> - Error Code:904 Message:ORA-00904: "C_0": invalid identifier Executing statement: [SQL: SELECT * FROM (SELECT g_1.IntKey AS c_0, 'A' AS c_1 FROM SmallA g_1) WHERE ROWNUM <= 6 UNION ALL SELECT * FROM (SELECT g_0.IntKey AS c_0, 'B' AS c_1 FROM SmallB g_0) WHERE ROWNUM <= 5 ORDER BY c_0, c_1] 1 [ConnectorException]Error Code:904 Message:Error occurred on connector BQT1 Connector<8> - Error Code:904 Message:ORA-00904: "C_0": invalid identifier Executing statement: [SQL: SELECT * FROM (SELECT g_1.IntKey AS c_0, 'A' AS c_1 FROM SmallA g_1) WHERE ROWNUM <= 6 UNION ALL SELECT * FROM (SELECT g_0.IntKey AS c_0, 'B' AS c_1 FROM SmallB g_0) WHERE ROWNUM <= 5 ORDER BY c_0, c_1] 2 [JDBCExecutionException]Error Code:904 Message:ORA-00904: "C_0": invalid identifier Executing statement: [SQL: SELECT * FROM (SELECT g_1.IntKey AS c_0, 'A' AS c_1 FROM SmallA g_1) WHERE ROWNUM <= 6 UNION ALL SELECT * FROM (SELECT g_0.IntKey AS c_0, 'B' AS c_1 FROM SmallB g_0) WHERE ROWNUM <= 5 ORDER BY c_0, c_1] at org.teiid.dqp.internal.process.DataTierTupleSource.switchBatch(DataTierTupleSource.java:128) at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:89) at com.metamatrix.query.processor.relational.AccessNode.nextBatchDirect(AccessNode.java:136) at com.metamatrix.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:257) at com.metamatrix.query.processor.relational.LimitNode.nextBatchDirect(LimitNode.java:81) at com.metamatrix.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:257) at com.metamatrix.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:110) at com.metamatrix.query.processor.QueryProcessor.process(QueryProcessor.java:160) at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:264) at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:210) at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:44) at com.metamatrix.common.queue.WorkerPoolFactory$StatsCapturingSharedThreadPoolExecutor$1.run(WorkerPoolFactory.java:211) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603) at java.lang.Thread.run(Thread.java:636) Caused by: org.teiid.connector.api.ConnectorException: Error Code:904 Message:Error occurred on connector BQT1 Connector<8> - Error Code:904 Message:ORA-00904: "C_0": invalid identifier Executing statement: [SQL: SELECT * FROM (SELECT g_1.IntKey AS c_0, 'A' AS c_1 FROM SmallA g_1) WHERE ROWNUM <= 6 UNION ALL SELECT * FROM (SELECT g_0.IntKey AS c_0, 'B' AS c_1 FROM SmallB g_0) WHERE ROWNUM <= 5 ORDER BY c_0, c_1] at org.teiid.dqp.internal.datamgr.impl.ConnectorWorkItem.handleError(ConnectorWorkItem.java:235) at org.teiid.dqp.internal.datamgr.impl.ConnectorWorkItem.process(ConnectorWorkItem.java:174) at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:44) at org.teiid.dqp.internal.datamgr.impl.SynchConnectorWorkItem.run(SynchConnectorWorkItem.java:69) ... 4 more Caused by: org.teiid.connector.jdbc.JDBCExecutionException: Error Code:904 Message:ORA-00904: "C_0": invalid identifier Executing statement: [SQL: SELECT * FROM (SELECT g_1.IntKey AS c_0, 'A' AS c_1 FROM SmallA g_1) WHERE ROWNUM <= 6 UNION ALL SELECT * FROM (SELECT g_0.IntKey AS c_0, 'B' AS c_1 FROM SmallB g_0) WHERE ROWNUM <= 5 ORDER BY c_0, c_1] at org.teiid.connector.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:103) at org.teiid.dqp.internal.datamgr.impl.ConnectorWorkItem.processNewRequest(ConnectorWorkItem.java:277) at org.teiid.dqp.internal.datamgr.impl.ConnectorWorkItem.process(ConnectorWorkItem.java:157) ... 6 more Steve Hawkins suspects the OracleTranslator code. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: https://jira.jboss.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira

14 years, 9 months

2
5
0 / 0

[JBoss JIRA] Created: (TEIID-710) DB2 does not support pushdown of null keyword as a select expression

by Steven Hawkins (JIRA)

DB2 does not support pushdown of null keyword as a select expression -------------------------------------------------------------------- Key: TEIID-710 URL: https://jira.jboss.org/jira/browse/TEIID-710 Project: Teiid Issue Type: Bug Components: JDBC Connector Affects Versions: 6.1.0 Reporter: Steven Hawkins Assignee: Steven Hawkins Priority: Minor Fix For: 6.2.0 For example SELECT SmallA.IntKey, NULL FROM SmallA will fail. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: https://jira.jboss.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira

14 years, 9 months

1
2
0 / 0

[JBoss JIRA] Created: (TEIID-360) SQLServer SQLTranslator issues

by Steven Hawkins (JIRA)

SQLServer SQLTranslator issues ------------------------------ Key: TEIID-360 URL: https://jira.jboss.org/jira/browse/TEIID-360 Project: Teiid Issue Type: Bug Components: JDBC Connector Affects Versions: 6.0.0 Reporter: Steven Hawkins Assignee: Steven Hawkins Fix For: 6.0.0 The time literal for Access/SQLServer is incorrect - should use 1970 not 1900 Limit for a set op needs to use a subquery Support for repeat should be aliased to replicate -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: https://jira.jboss.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira

14 years, 9 months

1
7
0 / 0

[JBoss JIRA] Created: (TEIID-455) Config edits should use optimistic locking

by Steven Hawkins (JIRA)

Config edits should use optimistic locking ------------------------------------------ Key: TEIID-455 URL: https://jira.jboss.org/jira/browse/TEIID-455 Project: Teiid Issue Type: Bug Affects Versions: 6.1.0 Reporter: Steven Hawkins Assignee: Steven Hawkins Fix For: 6.x Config edits are atomic, but do not enforce consistency - the last one always wins. Optimistic locking should be used to properly ensure consistency. Also versions should be restorable. Currently it's not expected that multiple admins will be making changes concurrently, so this is not a critical issue. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: https://jira.jboss.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira

14 years, 9 months

1
3
0 / 0

[JBoss JIRA] Commented: (TEIID-168) Remove resultset caching

by Steven Hawkins (JIRA)

[ https://jira.jboss.org/jira/browse/TEIID-168?page=com.atlassian.jira.plug... ] Steven Hawkins commented on TEIID-168: -------------------------------------- re-added result set caching backed by the buffermanager. Since this is no longer a memory cache, it's enabled by default with a size of 1024. It also means that xquery and document model documents are cachable as well (since they are already backed by disk stores). Result set caching can still be used through the cache execution property, but a cache hint can also be used e.g. /* cache */ select .... The hint must be located at the start of the query and can only appear next to a select or exec/execute. No considerations are currently made for transactional state though. > Remove resultset caching > ------------------------ > > Key: TEIID-168 > URL: https://jira.jboss.org/jira/browse/TEIID-168 > Project: Teiid > Issue Type: Feature Request > Components: JDBC Connector, Query Engine > Affects Versions: 7.0 > Reporter: Steven Hawkins > Assignee: Steven Hawkins > Fix For: 7.0 > > > Resultset caching should be replaced with relation and procedure caching. This would initially be backed by the buffermanager, based upon hints/costing, and be utilized by the planner rather than the resolver. It would be good if we also detected the level of determinism. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: https://jira.jboss.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira

14 years, 11 months

1
0
0 / 0

[JBoss JIRA] Updated: (TEIID-168) Refine caching behavior

by Steven Hawkins (JIRA)

[ https://jira.jboss.org/jira/browse/TEIID-168?page=com.atlassian.jira.plug... ] Steven Hawkins updated TEIID-168: --------------------------------- Summary: Refine caching behavior (was: Remove resultset caching) Affects Version/s: 7.0 (was: 8.x) Description: Connector resultset caching should be replaced with relation and procedure caching. This would initially be backed by the buffermanager, based upon hints/costing, and be utilized by the planner rather than the resolver. It would be good if we also detected the level of determinism. Query resultset caching should also be backed by the buffermanager. was:Resultset caching should be replaced with relation and procedure caching. This would initially be backed by the buffermanager, based upon hints/costing, and be utilized by the planner rather than the resolver. It would be good if we also detected the level of determinism. Affects: [Documentation (Ref Guide, User Guide, etc.), Compatibility/Configuration, Release Notes] (was: [Documentation (Ref Guide, User Guide, etc.), Release Notes]) > Refine caching behavior > ----------------------- > > Key: TEIID-168 > URL: https://jira.jboss.org/jira/browse/TEIID-168 > Project: Teiid > Issue Type: Feature Request > Components: JDBC Connector, Query Engine > Affects Versions: 7.0 > Reporter: Steven Hawkins > Assignee: Steven Hawkins > Fix For: 7.0 > > > Connector resultset caching should be replaced with relation and procedure caching. This would initially be backed by the buffermanager, based upon hints/costing, and be utilized by the planner rather than the resolver. It would be good if we also detected the level of determinism. > Query resultset caching should also be backed by the buffermanager. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: https://jira.jboss.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira

14 years, 11 months

1
0
0 / 0

[JBoss JIRA] Updated: (TEIID-7) Control of BigDecimal precision and scale

by Steven Hawkins (JIRA)

[ https://jira.jboss.org/jira/browse/TEIID-7?page=com.atlassian.jira.plugin... ] Steven Hawkins updated TEIID-7: ------------------------------- Fix Version/s: 7.1 (was: 7.0) Pushing to 7.1 > Control of BigDecimal precision and scale > ----------------------------------------- > > Key: TEIID-7 > URL: https://jira.jboss.org/jira/browse/TEIID-7 > Project: Teiid > Issue Type: Bug > Components: Query Engine > Affects Versions: 8.x > Reporter: Jerry Helbling > Priority: Minor > Fix For: 7.1 > > > Problem 1: The parser defaults to parsing literals that look like bigdecimal as doubles, which have no scale. > Workaround (which is on devcentral): You can avoid the above conversions by wrapping the values in a convert in the query (example: "convert('123.00', BigDecimal )"). > A better fix is update the parser to look for loss of scale information and to use the bigdecimal type instead. > Problem 2: default division behavior JBEDSP-305 > Problem 3: No explicit control via the parser > It would be good to provide explicit precision and scale via "convert(xxx, BigDecimal(10, 2))" - side note bigdecimal is a bad name for this type. it should really just be decimal > Reference: This is related to Issue Tracker Issue 175485. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: https://jira.jboss.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira

14 years, 11 months

1
0
0 / 0

[JBoss JIRA] Updated: (TEIID-65) Precision of BigInteger and BigDecimal need to be limited

by Steven Hawkins (JIRA)

[ https://jira.jboss.org/jira/browse/TEIID-65?page=com.atlassian.jira.plugi... ] Steven Hawkins updated TEIID-65: -------------------------------- Fix Version/s: 7.1 (was: 7.0) Pushing to 7.1 > Precision of BigInteger and BigDecimal need to be limited > --------------------------------------------------------- > > Key: TEIID-65 > URL: https://jira.jboss.org/jira/browse/TEIID-65 > Project: Teiid > Issue Type: Sub-task > Components: Common > Affects Versions: 8.x > Reporter: Steven Hawkins > Fix For: 7.1 > > > To ensure database and string conversion compatibility BigInteger and BigDecimal should be limited to 1000 digits of precision (taken from PostGres). -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: https://jira.jboss.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira

14 years, 11 months

1
0
0 / 0

[JBoss JIRA] Created: (TEIID-935) Teiid is making column names upper case, even when specified as lower

by Paul Nittel (JIRA)

Teiid is making column names upper case, even when specified as lower --------------------------------------------------------------------- Key: TEIID-935 URL: https://jira.jboss.org/jira/browse/TEIID-935 Project: Teiid Issue Type: Bug Components: Query Engine Affects Versions: 7.0 Environment: Fedora 12, OpenJDK Server VM (build 14.0-b16, mixed mode), Teiid build 1/19/2010 Reporter: Paul Nittel Assignee: Steven Hawkins I've got a transformation that defines two columns like this: SELECT c_count, COUNT(*) AS custdist FROM (SELECT C_CUSTKEY, COUNT(O_ORDERKEY) AS c_count FROM TPCR.CUSTOMER LEFT OUTER JOIN TPCR.ORDERS ON C_CUSTKEY = O_CUSTKEY AND O_COMMENT NOT LIKE '%special%requests%' GROUP BY C_CUSTKEY) AS c_orders GROUP BY c_count In the past, when queried (SELECT * FROM ...), the columns were spelled c_count and custdist. Now they're spelled C_COUNT and custdist. Do we now upcase column names that are not specified as an alias (or do we only preserve case on aliases)? I noticed this in AdminShell, but see the Designer is also doing it, so I'm thinking it's the query engine, not the tool. (Steve H. agreed it should still be lower case when queried.) -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: https://jira.jboss.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira

14 years, 11 months

2
3
0 / 0

[JBoss JIRA] Updated: (TEIID-93) when a fairly complex query is pushed down to the XML-HTTP Connector, it fails

by Steven Hawkins (JIRA)

[ https://jira.jboss.org/jira/browse/TEIID-93?page=com.atlassian.jira.plugi... ] Steven Hawkins updated TEIID-93: -------------------------------- Fix Version/s: 7.1 (was: 7.0) pushing to 7.1 > when a fairly complex query is pushed down to the XML-HTTP Connector, it fails > ------------------------------------------------------------------------------ > > Key: TEIID-93 > URL: https://jira.jboss.org/jira/browse/TEIID-93 > Project: Teiid > Issue Type: Bug > Components: XML Connector > Affects Versions: 8.x > Reporter: Steven Hawkins > Fix For: 7.1 > > > Defect Tracker #25059: when a fairly complex query is pushed down to the XML-HTTP > Connector, it fails > Need a lot more information. > OK, here's how to reproduce this issue (which we hit at Citi). Use the attached model project set in 5.5.1RC2 Designer, and use the response.xml attached with your standard xmltool.war tester. Then issue the following query from Designer: > >> > >> select H_RelationshipID, H_RelationshipName, H_HHID, H_TeamLeadName, H_TeamLeadID, H_MemberFirstName, H_MemberLastName, H_MemberAcctID, H_MemberFCName, H_MemberFCNum, H_HHLeadInd, H_MemberType, H_MemberRole, H_clientId, A_ADDR_TYPE, A_ADDR_SUB_TYPE, A_ADDR_SUB_NAME, A_ADDR_LINE1, A_ADDR_LINE2, A_ADDR_LINE3, A_ADDR_LINE4, A_ADDR_LINE5, A_ADDR_LINE6, A_ADDR_FLAG1, A_ADDR_FLAG2, A_ADDR_FLAG3, A_ADDR_FLAG4, A_ADDR_FLAG5, A_ADDR_FLAG6, A_CITY, A_ZIP, A_STATE_CODE, A_POSTAL_CODE, A_COUNTRY, A_ALTA_TYPE, A_EFF_DATE, A_EXP_DATE from "RPXMLTest"."CustRelnDataWS.WS.RetrieveCustRelnData.relnDataSingleTable" where A_ADDR_TYPE IN ('BA','AA') AND ((A_ADDR_TYPE IN('AA')) AND(NVL(A_ALTA_TYPE,'') = '')) > >> > >> This plans to the following source query being pushed to the connector: > >> > >> SELECT A_ALTA_TYPE, H_RelationshipID, H_RelationshipName, H_HHID, H_TeamLeadName, H_TeamLeadID, H_MemberFirstName, H_MemberLastName, H_MemberAcctID, H_MemberFCName, H_MemberFCNum, H_HHLeadInd, H_MemberType, H_MemberRole, H_clientId, A_ADDR_TYPE, A_ADDR_SUB_TYPE, A_ADDR_SUB_NAME, A_ADDR_LINE1, A_ADDR_LINE2, A_ADDR_LINE3, A_ADDR_LINE4, A_ADDR_LINE5, A_ADDR_LINE6, A_ADDR_FLAG1, A_ADDR_FLAG2, A_ADDR_FLAG3, A_ADDR_FLAG4, A_ADDR_FLAG5, A_ADDR_FLAG6, A_CITY, A_ZIP, A_STATE_CODE, A_POSTAL_CODE, A_COUNTRY, A_EFF_DATE, A_EXP_DATE FROM RPXMLTest.CustRelnDataWS.WS.RetrieveCustRelnData.relnDataSingleTable WHERE (A_ADDR_TYPE IN ('BA', 'AA')) AND (A_ADDR_TYPE = 'AA') > >> > >> Now, the criteria here says that A_ADDR_TYPE needs to be in the set of 'AA', 'BA', AND needs to be equal to 'AA'. So really only those "rows" where A_ADDR_TYPE='AA' should be returned. But instead the connector returns all rows where A_ADDR_TYPE is 'AA' or 'BA'. > >> > > > > This seems like it could be a defect in the way we hande IN criteria. Was there ever a case or defect on this? -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: https://jira.jboss.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira

14 years, 11 months

1
0
0 / 0

2024

2023

2022

2021

2020

2019

2018

2017

2016

2015

2014

2013

2012

2011

2010

2009

teiid-issues January 2010