[JBoss JIRA] (TEIID-1791) Only the first 1000 items in the IN criteria are being pushed down in the query
by Van Halbert (Moved) (JIRA)
[ https://issues.jboss.org/browse/TEIID-1791?page=com.atlassian.jira.plugin... ]
Van Halbert moved SOA-3512 to TEIID-1791:
-----------------------------------------
Project: Teiid (was: JBoss Enterprise SOA Platform)
Key: TEIID-1791 (was: SOA-3512)
Affects Version/s: 7.4.1
(was: 5.2.0.ER3)
Component/s: Query Engine
(was: EDS)
Security: (was: JBoss Internal)
> Only the first 1000 items in the IN criteria are being pushed down in the query
> -------------------------------------------------------------------------------
>
> Key: TEIID-1791
> URL: https://issues.jboss.org/browse/TEIID-1791
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 7.4.1
> Reporter: Debbie Steigner
> Attachments: 1000_cmd_log.txt, 1000_problem.txt, TSL_POC.vdb
>
>
> In the below query, we are expecting a large number of book codes (46987), to be passed from ledgerbookid in t_sdm_ledgerbook into the TSL_FINANCIAL_ACCOUNT_ITEMS_T2 table. We can see from the query plan that it got the correct number of rows.
> I understand that Oracle can only take 1000 items in an IN() construct, so EDS will split the criteria into multiple sets of 1000 IN criteria. I have seen this work previously.
> It seems that for some reason in this query, possibly the other dependent join or hints, EDS pushes exactly 1000 criteria. Looking at the source specific SQL command in the EDS debug log, which I have attached, I counted the number of question marks in that IN() and it comes to 1000. We have checked that all of these book codes are unique, by doing a select distinct to the source model.
> select cc.costcentreid, cc.description , ac.accountdescription, sum(func_amount)
> from TSL_FINANCIAL_ACCOUNT_ITEMS_T2 tsl
> join t_sdm_glaccount ac on tsl.account_code = ac.accountid
> join /*+ MAKEIND */t_sdm_ledgerbook lb on tsl.book_code = lb.ledgerbookid
> join /*+ MAKEIND */t_sdm_costcentre cc on lb.parentcostcentreid = cc.costcentreid
> join /*+ MAKEIND */t_sdm_desk dsk on cc.parentdeskid = dsk.deskid
> join /*+ MAKEIND */t_sdm_businessarea ba on dsk.parentbusinessareaid = ba.businessareaid
> join /*+ MAKEIND */t_sdm_division div on ba.parentdivisionid = div.divisionid
> where tsl.business_date = '2011-06-29'
> and div.divisionid = 'GBM'
> and ac.classificationid in (3,4)
> group by cc.costcentreid, cc.description, ac.accountdescription;
--
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, 11 months
[JBoss JIRA] Created: (TEIID-1755) NPE using matviews with default profile
by Steven Hawkins (JIRA)
NPE using matviews with default profile
---------------------------------------
Key: TEIID-1755
URL: https://issues.jboss.org/browse/TEIID-1755
Project: Teiid
Issue Type: Bug
Components: Query Engine
Affects Versions: 7.4.1
Reporter: Steven Hawkins
Assignee: Steven Hawkins
Priority: Blocker
Fix For: 7.4.1, 7.6
Since we are not explicitly requiring the all profile the code should be safe to use in the default profile.
If distributed caching is not enabled we get the following:
2011-09-14 16:59:33,369 ERROR [org.teiid.PROCESSOR] (Worker3_QueryProcessorQueue45) Unexpected exception for request acP+ZR0GKpav.12
java.lang.NullPointerException
at org.teiid.query.tempdata.TempTableDataManager.touchTable(TempTableDataManager.java:639)
at org.teiid.query.tempdata.TempTableDataManager.handleSystemProcedures(TempTableDataManager.java:341)
at org.teiid.query.tempdata.TempTableDataManager.registerRequest(TempTableDataManager.java:194)
at org.teiid.query.tempdata.TempTableDataManager.registerRequest(TempTableDataManager.java:176)
at org.teiid.query.processor.relational.AccessNode.registerRequest(AccessNode.java:358)
at org.teiid.query.processor.relational.AccessNode.open(AccessNode.java:156)
at org.teiid.query.processor.relational.RelationalNode.open(RelationalNode.java:251)
at org.teiid.query.processor.relational.RelationalPlan.open(RelationalPlan.java:153)
...
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
12 years, 11 months
[JBoss JIRA] (TEIID-1657) CLONE - EDS - refreshMatView does not work correctly for an internal materialized view when clustering is enabled
by Paul Nittel (Closed) (JIRA)
[ https://issues.jboss.org/browse/TEIID-1657?page=com.atlassian.jira.plugin... ]
Paul Nittel closed TEIID-1657.
------------------------------
Tested and closed.
> CLONE - EDS - refreshMatView does not work correctly for an internal materialized view when clustering is enabled
> -----------------------------------------------------------------------------------------------------------------
>
> Key: TEIID-1657
> URL: https://issues.jboss.org/browse/TEIID-1657
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 7.1
> Reporter: Steven Hawkins
> Assignee: Steven Hawkins
> Fix For: 7.4.1, 7.5
>
>
> The refreshMatView works as expected in a single node environment with 'teiid-cache-manager-jboss-beans-rename-me.xml'. Still in a single node environment, that function no longer refreshes the data with "teiid-cache-manager-jboss-beans.xml" enabled.
> To recreate:
> 1. Start up an EDS instance with a vdb deployed which has an internal materialized view (IMV).
> 2. Query SYSADMIN.MATVIEWS and verify LoadState = "NEEDS_LOADING"
> 3. Query the IMV to populate the cache.
> 4. Query SYSADMIN.MATVIEWS and verify LoadState = "LOADED". Note the value of the 'Updated' column
> 5. run "CALL SYSADMIN.refreshMatView('view name',false)"
> 6. Query SYSADMIN.MATVIEWS and verify LoadState = "LOADED". Note the value of the 'Updated' column
> 7. Query the IMV to see if it pulls from the source or cache
> Expected results: Steps 3 and 5 should trigger queries to the source. Step 7 should pull from the cache without going back to the source.
--
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, 11 months
[JBoss JIRA] (TEIID-1778) Procedure returning incorrect result set; right number of rows, wrong data
by Paul Nittel (Created) (JIRA)
Procedure returning incorrect result set; right number of rows, wrong data
--------------------------------------------------------------------------
Key: TEIID-1778
URL: https://issues.jboss.org/browse/TEIID-1778
Project: Teiid
Issue Type: Bug
Components: Query Engine
Affects Versions: 7.4.1
Environment: JBDS 4.1.1 M2, SOA-P 5.2 ER4
Reporter: Paul Nittel
Assignee: Steven Hawkins
Testing virtual procedures uses the PartsProject, and its PartsVirtual.SupplierInfo table, as a starting point. This transformation is defined as:
SELECT PartsSourceA.SUPPLIER_PARTS.SUPPLIER_ID, PartsSourceA.SUPPLIER_PARTS.PART_ID, PartsSourceA.SUPPLIER_PARTS.QUANTITY, PartsSourceA.SUPPLIER_PARTS.SHIPPER_ID, PartsSourceB.SUPPLIER.SUPPLIER_NAME, PartsSourceB.SUPPLIER.SUPPLIER_STATUS, PartsSourceB.SUPPLIER.SUPPLIER_CITY, PartsSourceB.SUPPLIER.SUPPLIER_STATE
FROM PartsSourceA.SUPPLIER_PARTS, PartsSourceB.SUPPLIER
WHERE PartsSourceA.SUPPLIER_PARTS.SUPPLIER_ID = PartsSourceB.SUPPLIER.SUPPLIER_ID
(Fundamentally, this is a SELECT *, but excluding duplicate columns makes it appear more complicated.)
One procedure (MMSP07), is defined by this transformation:
CREATE VIRTUAL PROCEDURE
BEGIN
DECLARE string VARIABLES.x;
SELECT PartsVirtual.SupplierInfo.SUPPLIER_ID INTO #tempSupplierInfo FROM PartsVirtual.SupplierInfo;
LOOP ON (SELECT #tempSupplierInfo.SUPPLIER_ID FROM #tempSupplierInfo) AS supplier_idCursor
BEGIN
VARIABLES.x = supplier_idCursor.SUPPLIER_ID;
END
SELECT PartsVirtual.SupplierInfo.SUPPLIER_ID FROM PartsVirtual.SupplierInfo WHERE PartsVirtual.SupplierInfo.SUPPLIER_ID = VARIABLES.x;
END
The results should be 16 rows of "S115", but instead it returns 16 rows of the number 1.
Attached are the query plan and model project set.
--
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, 11 months
[JBoss JIRA] (TEIID-1786) Cannot invoke stored function that returns resultset on Oracle
by Steven Hawkins (Resolved) (JIRA)
[ https://issues.jboss.org/browse/TEIID-1786?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-1786.
-----------------------------------
Resolution: Duplicate Issue
This is a dup of TEIID-832. This has always been the case with Oracle native (datadirect supports this case without any special effort). This is a KI and I don't believe should be a blocker.
> Cannot invoke stored function that returns resultset on Oracle
> ---------------------------------------------------------------
>
> Key: TEIID-1786
> URL: https://issues.jboss.org/browse/TEIID-1786
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Connector
> Affects Versions: 7.4.1
> Reporter: Filip Nguyen
> Assignee: Steven Hawkins
> Priority: Blocker
>
> My goal was to return result set from stored procedure using Teiid on Oracle. This should be only possible by stored function.
> I created stored function in the DB
> {code:title=The stored function|borderStyle=solid}
> create or replace FUNCTION getEmployeeSurnames RETURN sys_refcursor AS
> resultset sys_refcursor;
> BEGIN
> OPEN RESULTSET FOR SELECT surname FROM employees;
> RETURN RESULTSET;
> END;
> {code}
> In Teiid designer I imported the function (VDB in attachment). After deploying the VDB I tried to call the function
> {code:title=JDBC query|borderStyle=solid}
> List<String> surnameList = new ArrayList<String>();
>
> String[] connectionProps = useCache ?
> new String[] {"resultSetCacheMode=true"} :
> new String[] {};
> String query = "{CALL getEmployeeSurnames()}";
> Connection conn = getTeiidConnection(VDBNAME, connectionProps);
> Statement cs = conn.createStatement();
>
> cs.execute(query);
> ResultSet rs = cs.executeQuery(query);
> while (rs.next()) {
> surnameList.add(rs.getString(1));
> }
> conn.close();
> {code}
> The exception on the server is
> {code:title=server.log|borderStyle=solid}
> 10:57:57,713 WARN [org.teiid.CONNECTOR] Connector worker process failed for atomic-request=YI3UxN+ZHXZU.0.2.79
> [TranslatorException] 65000: Error Code:65000 Message:'{ call GETEMPLOYEESURNAMES()}' error executing statement(s): {1}
> 1 [SQLException]ORA-06550: line 1, column 7:
> PLS-00221: 'GETEMPLOYEESURNAMES' is not a procedure or is undefined
> ORA-06550: line 1, column 7:
> PL/SQL: Statement ignored
> at org.teiid.translator.jdbc.JDBCProcedureExecution.execute(JDBCProcedureExecution.java:70)
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:264)
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:338)
> at org.teiid.dqp.internal.process.DataTierTupleSource.access$000(DataTierTupleSource.java:80)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:138)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:135)
> at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
> at java.util.concurrent.FutureTask.run(FutureTask.java:138)
> at org.teiid.dqp.internal.process.DQPCore$FutureWork.run(DQPCore.java:121)
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:194)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:118)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:288)
> at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
> at java.lang.Thread.run(Thread.java:662)
> Caused by: java.sql.SQLException: ORA-06550: line 1, column 7:
> PLS-00221: 'GETEMPLOYEESURNAMES' is not a procedure or is undefined
> ORA-06550: line 1, column 7:
> PL/SQL: Statement ignored
> at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440)
> at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
> at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:837)
> at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:445)
> at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191)
> at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523)
> at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:204)
> at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:1007)
> at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1315)
> at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3576)
> at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3677)
> at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4714)
> at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1374)
> at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.execute(WrappedPreparedStatement.java:299)
> at org.teiid.translator.jdbc.JDBCExecutionFactory.executeStoredProcedure(JDBCExecutionFactory.java:774)
> at org.teiid.translator.jdbc.JDBCProcedureExecution.execute(JDBCProcedureExecution.java:67)
> ... 14 more
> {code}
> This is solely problem on Oracle. I tried tested stored procedures/functions that return resultsets on other DB engines without problems (Postgres, MSSQL, MySQL, DB2).
> Additional info. In SOA platform there is native oracle driver:
> {code:title=ojdbc6.jar MANIFEST.MF|borderStyle=solid}
> Manifest-Version: 1.0
> Ant-Version: Apache Ant 1.6.5
> Created-By: 1.5.0_24-rev-b08 (Sun Microsystems Inc.)
> Implementation-Vendor: Oracle Corporation
> Implementation-Title: JDBC
> Implementation-Version: 11.2.0.2.0
> Repository-Id: JAVAVM_11.2.0.2.0_LINUX_100812.1
> Specification-Vendor: Sun Microsystems Inc.
> Specification-Title: JDBC
> Specification-Version: 4.0
> Main-Class: oracle.jdbc.OracleDriver
> sealed: true
> Name: oracle/sql/converter/
> Sealed: false
> Name: oracle/sql/
> Sealed: false
> Name: oracle/sql/converter_xcharset/
> Sealed: false
> {code}
--
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, 11 months
[JBoss JIRA] (TEIID-1786) Cannot invoke stored function that returns resultset on Oracle
by Van Halbert (Moved) (JIRA)
[ https://issues.jboss.org/browse/TEIID-1786?page=com.atlassian.jira.plugin... ]
Van Halbert moved SOA-3500 to TEIID-1786:
-----------------------------------------
Project: Teiid (was: JBoss Enterprise SOA Platform)
Key: TEIID-1786 (was: SOA-3500)
Affects Version/s: 7.4.1
(was: 5.2.0.ER5)
Component/s: JDBC Connector
(was: EDS)
Security: (was: Public)
> Cannot invoke stored function that returns resultset on Oracle
> ---------------------------------------------------------------
>
> Key: TEIID-1786
> URL: https://issues.jboss.org/browse/TEIID-1786
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Connector
> Affects Versions: 7.4.1
> Reporter: Filip Nguyen
> Assignee: Van Halbert
> Priority: Blocker
>
> My goal was to return result set from stored procedure using Teiid on Oracle. This should be only possible by stored function.
> I created stored function in the DB
> {code:title=The stored function|borderStyle=solid}
> create or replace FUNCTION getEmployeeSurnames RETURN sys_refcursor AS
> resultset sys_refcursor;
> BEGIN
> OPEN RESULTSET FOR SELECT surname FROM employees;
> RETURN RESULTSET;
> END;
> {code}
> In Teiid designer I imported the function (VDB in attachment). After deploying the VDB I tried to call the function
> {code:title=JDBC query|borderStyle=solid}
> List<String> surnameList = new ArrayList<String>();
>
> String[] connectionProps = useCache ?
> new String[] {"resultSetCacheMode=true"} :
> new String[] {};
> String query = "{CALL getEmployeeSurnames()}";
> Connection conn = getTeiidConnection(VDBNAME, connectionProps);
> Statement cs = conn.createStatement();
>
> cs.execute(query);
> ResultSet rs = cs.executeQuery(query);
> while (rs.next()) {
> surnameList.add(rs.getString(1));
> }
> conn.close();
> {code}
> The exception on the server is
> {code:title=server.log|borderStyle=solid}
> 10:57:57,713 WARN [org.teiid.CONNECTOR] Connector worker process failed for atomic-request=YI3UxN+ZHXZU.0.2.79
> [TranslatorException] 65000: Error Code:65000 Message:'{ call GETEMPLOYEESURNAMES()}' error executing statement(s): {1}
> 1 [SQLException]ORA-06550: line 1, column 7:
> PLS-00221: 'GETEMPLOYEESURNAMES' is not a procedure or is undefined
> ORA-06550: line 1, column 7:
> PL/SQL: Statement ignored
> at org.teiid.translator.jdbc.JDBCProcedureExecution.execute(JDBCProcedureExecution.java:70)
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:264)
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:338)
> at org.teiid.dqp.internal.process.DataTierTupleSource.access$000(DataTierTupleSource.java:80)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:138)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:135)
> at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
> at java.util.concurrent.FutureTask.run(FutureTask.java:138)
> at org.teiid.dqp.internal.process.DQPCore$FutureWork.run(DQPCore.java:121)
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:194)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:118)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:288)
> at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
> at java.lang.Thread.run(Thread.java:662)
> Caused by: java.sql.SQLException: ORA-06550: line 1, column 7:
> PLS-00221: 'GETEMPLOYEESURNAMES' is not a procedure or is undefined
> ORA-06550: line 1, column 7:
> PL/SQL: Statement ignored
> at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440)
> at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
> at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:837)
> at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:445)
> at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191)
> at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523)
> at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:204)
> at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:1007)
> at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1315)
> at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3576)
> at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3677)
> at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4714)
> at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1374)
> at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.execute(WrappedPreparedStatement.java:299)
> at org.teiid.translator.jdbc.JDBCExecutionFactory.executeStoredProcedure(JDBCExecutionFactory.java:774)
> at org.teiid.translator.jdbc.JDBCProcedureExecution.execute(JDBCProcedureExecution.java:67)
> ... 14 more
> {code}
> This is solely problem on Oracle. I tried tested stored procedures/functions that return resultsets on other DB engines without problems (Postgres, MSSQL, MySQL, DB2).
> Additional info. In SOA platform there is native oracle driver:
> {code:title=ojdbc6.jar MANIFEST.MF|borderStyle=solid}
> Manifest-Version: 1.0
> Ant-Version: Apache Ant 1.6.5
> Created-By: 1.5.0_24-rev-b08 (Sun Microsystems Inc.)
> Implementation-Vendor: Oracle Corporation
> Implementation-Title: JDBC
> Implementation-Version: 11.2.0.2.0
> Repository-Id: JAVAVM_11.2.0.2.0_LINUX_100812.1
> Specification-Vendor: Sun Microsystems Inc.
> Specification-Title: JDBC
> Specification-Version: 4.0
> Main-Class: oracle.jdbc.OracleDriver
> sealed: true
> Name: oracle/sql/converter/
> Sealed: false
> Name: oracle/sql/
> Sealed: false
> Name: oracle/sql/converter_xcharset/
> Sealed: false
> {code}
--
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, 11 months