[JBoss JIRA] (TEIID-3654) Exception with system table subquery
by Steven Hawkins (JIRA)
Steven Hawkins created TEIID-3654:
-------------------------------------
Summary: Exception with system table subquery
Key: TEIID-3654
URL: https://issues.jboss.org/browse/TEIID-3654
Project: Teiid
Issue Type: Bug
Components: Query Engine
Affects Versions: 8.7
Reporter: Steven Hawkins
Assignee: Steven Hawkins
Fix For: 8.12
Issuing a subquery against a system table such as:
SELECT t.Name FROM SYS.Tables as t, (SELECT DISTINCT c.TableName FROM SYS.Columns AS c) AS X__1 WHERE t.Name = X__1.TableName
Results in the exception: TEIID30328 Unable to evaluate X__2.TableName: No value was available
This is due to the project not being pushed and the project columns not being updated appropriately.
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
10 years, 7 months
[JBoss JIRA] (TEIID-3635) Auto configuration of buffermanager values can be incorrect
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3635?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-3635.
-----------------------------------
Fix Version/s: 8.11.3
Resolution: Done
Updated the auto configuration to more conservatively allocate processor max kb as for small max active plans there was too much overhead, reduce the overall max reserve, make the allocation more balanced between the memory buffer and the reserve space (with the overall memory buffer size still approximately the same given the overall reduction of the max reserve). Also updated the docs based upon this and expanded more on the expectations / interplay of the settings.
For Teiid 9 we'll look into normalizing the setting names and units as we've accumulated some complexity over the years from effectively 3 major revisions.
> Auto configuration of buffermanager values can be incorrect
> -----------------------------------------------------------
>
> Key: TEIID-3635
> URL: https://issues.jboss.org/browse/TEIID-3635
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 8.0
> Reporter: Steven Hawkins
> Assignee: Steven Hawkins
> Priority: Critical
> Fix For: 8.12, 8.11.3
>
>
> If the heap size is significantly larger than 2 gb, such that the auto computed value for the max reserve is larger than 2 gb and the memory buffer size is also auto calculated, then the computed values for max reserve and max memory buffer can be significantly different than expected. At worst this results in the max memory buffer size of 0, which just default the buffer to being accessible by a single thread.
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
10 years, 7 months
[JBoss JIRA] (TEIID-3490) WITH [TABLENAME] AS clause being sent to source query
by RH Bugzilla Integration (JIRA)
[ https://issues.jboss.org/browse/TEIID-3490?page=com.atlassian.jira.plugin... ]
RH Bugzilla Integration commented on TEIID-3490:
------------------------------------------------
Jan Stastny <jstastny(a)redhat.com> changed the Status of [bug 1224377|https://bugzilla.redhat.com/show_bug.cgi?id=1224377] from ON_QA to VERIFIED
> WITH [TABLENAME] AS clause being sent to source query
> -----------------------------------------------------
>
> Key: TEIID-3490
> URL: https://issues.jboss.org/browse/TEIID-3490
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 8.7
> Reporter: Debbie Steigner
> Assignee: Steven Hawkins
> Fix For: 8.7.1.6_2, 8.11, 8.7.4
>
>
> The transformation for that table is:
> WITH MONTHEND AS (SELECT CONVERT(SQLBqt.tinya.DateValue, DATE) AS REFERENCE_VALUE FROM SQLBqt.tinya WHERE SQLBqt.tinya.IntKey = 2)
> SELECT
> RATES.INTKEY, MAX(CASE WHEN RATES.INTKEY = 20 THEN RATES.STRINGKEY END) AS GBP_RATE, MAX(CASE WHEN RATES.INTKEY = 25 THEN RATES.STRINGKEY END) AS EUR_RATE, MAX(CASE WHEN RATES.INTKEY = 30 THEN RATES.STRINGKEY END) AS USD_RATE
> FROM
> Oraclebqt.dssmalla2 AS RATES
> WHERE
> (RATES.INTKEY IN (20, 25, 30)) AND (RATES.DATEVALUE = (SELECT MONTHEND.REFERENCE_VALUE FROM MONTHEND))
> GROUP BY RATES.INTKEY
> source queries are showing the With clause being pushed down to Oracle and they shouldn't be.
> 11:11:54,283 DEBUG [org.teiid.CONNECTOR] (Worker3_QueryProcessorQueue30) Vw26e70yOBmw.6.3.2 Processing NEW request: WITH MONTHEND (REFERENCE_VALUE) AS (SELECT CONVERT(g_0.DateValue, DATE) FROM SQLBqt.tinya AS g_0 WHERE g_0.IntKey = 2) SELECT g_0.INTKEY, MAX(CASE WHEN g_0.INTKEY = 20 THEN g_0.STRINGKEY END), MAX(CASE WHEN g_0.INTKEY = 25 THEN g_0.STRINGKEY END), MAX(CASE WHEN g_0.INTKEY = 30 THEN g_0.STRINGKEY END) FROM Oraclebqt.DSSMALLA2 AS g_0 WHERE (g_0.INTKEY IN (20, 25, 30)) AND (g_0.DATEVALUE = {d'2000-01-03'}) GROUP BY g_0.INTKEY
> 11:11:54,283 DEBUG [org.jboss.jca.core.connectionmanager.pool.strategy.OnePool] (Worker3_QueryProcessorQueue30) Oraclebqt: getConnection(null, WrappedConnectionRequestInfo@2efc4e0e[userName=bqt2]) [0/20]
> 11:11:54,283 INFO [org.teiid.CONNECTOR] (Worker3_QueryProcessorQueue30) OracleExecutionFactory Commit=true;DatabaseProductName=Oracle;DatabaseProductVersion=Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
> With the Partitioning, OLAP, Data Mining and Real Application Testing options;DriverMajorVersion=11;DriverMajorVersion=2;DriverName=Oracle JDBC driver;DriverVersion=11.2.0.1.0;IsolationLevel=2
> 11:11:54,283 DEBUG [org.teiid.CONNECTOR] (Worker3_QueryProcessorQueue30) Vw26e70yOBmw.6.3.2 Obtained execution
> 11:11:54,283 DEBUG [org.teiid.COMMAND_LOG] (Worker3_QueryProcessorQueue30) START DATA SRC COMMAND: startTime=2015-05-22 11:11:54.283 requestID=Vw26e70yOBmw.6 sourceCommandID=3 executionID=2 txID=null modelName=Oraclebqt translatorName=oracle sessionID=Vw26e70yOBmw principal=teiiduser@teiid-security sql=WITH MONTHEND (REFERENCE_VALUE) AS (SELECT CONVERT(g_0.DateValue, DATE) FROM SQLBqt.tinya AS g_0 WHERE g_0.IntKey = 2) SELECT g_0.INTKEY, MAX(CASE WHEN g_0.INTKEY = 20 THEN g_0.STRINGKEY END), MAX(CASE WHEN g_0.INTKEY = 25 THEN g_0.STRINGKEY END), MAX(CASE WHEN g_0.INTKEY = 30 THEN g_0.STRINGKEY END) FROM Oraclebqt.DSSMALLA2 AS g_0 WHERE (g_0.INTKEY IN (20, 25, 30)) AND (g_0.DATEVALUE = {d'2000-01-03'}) GROUP BY g_0.INTKEY
> 11:11:54,285 DEBUG [org.teiid.CONNECTOR] (Worker3_QueryProcessorQueue30) Source-specific command: WITH MONTHEND AS (SELECT trunc(cast(g_0."DateValue" AS date)) AS REFERENCE_VALUE FROM "bqt2"."dbo"."tinya" g_0 WHERE g_0."IntKey" = 2) SELECT g_0."INTKEY", MAX(CASE WHEN g_0."INTKEY" = 20 THEN g_0."STRINGKEY" END), MAX(CASE WHEN g_0."INTKEY" = 25 THEN g_0."STRINGKEY" END), MAX(CASE WHEN g_0."INTKEY" = 30 THEN g_0."STRINGKEY" END) FROM "BQT2"."DSSMALLA2" g_0 WHERE g_0."INTKEY" IN (20, 25, 30) AND g_0."DATEVALUE" = {d '2000-01-03'} GROUP BY g_0."INTKEY"
> 11:11:54,285 TRACE [org.teiid.CONNECTOR] (Worker3_QueryProcessorQueue30) Source sql WITH MONTHEND AS (SELECT trunc(cast(g_0."DateValue" AS date)) AS REFERENCE_VALUE FROM "bqt2"."dbo"."tinya" g_0 WHERE g_0."IntKey" = 2) SELECT g_0."INTKEY", MAX(CASE WHEN g_0."INTKEY" = 20 THEN g_0."STRINGKEY" END), MAX(CASE WHEN g_0."INTKEY" = 25 THEN g_0."STRINGKEY" END), MAX(CASE WHEN g_0."INTKEY" = 30 THEN g_0."STRINGKEY" END) FROM "BQT2"."DSSMALLA2" g_0 WHERE g_0."INTKEY" IN (20, 25, 30) AND g_0."DATEVALUE" = {d '2000-01-03'} GROUP BY g_0."INTKEY"
> 11:11:55,514 DEBUG [org.teiid.COMMAND_LOG] (Worker3_QueryProcessorQueue30) ERROR SRC COMMAND: endTime=2015-05-22 11:11:55.514 requestID=Vw26e70yOBmw.6 sourceCommandID=3 executionID=2 txID=null modelName=Oraclebqt translatorName=oracle sessionID=Vw26e70yOBmw principal=teiiduser@teiid-security finalRowCount=null
> 11:11:55,514 WARN [org.teiid.CONNECTOR] (Worker3_QueryProcessorQueue30) Connector worker process failed for atomic-request=Vw26e70yOBmw.6.3.2: org.teiid.translator.jdbc.JDBCExecutionException: 907 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: WITH MONTHEND AS (SELECT trunc(cast(g_0."DateValue" AS date)) AS REFERENCE_VALUE FROM "bqt2"."dbo"."tinya" g_0 WHERE g_0."IntKey" = 2) SELECT g_0."INTKEY", MAX(CASE WHEN g_0."INTKEY" = 20 THEN g_0."STRINGKEY" END), MAX(CASE WHEN g_0."INTKEY" = 25 THEN g_0."STRINGKEY" END), MAX(CASE WHEN g_0."INTKEY" = 30 THEN g_0."STRINGKEY" END) FROM "BQT2"."DSSMALLA2" g_0 WHERE g_0."INTKEY" IN (20, 25, 30) AND g_0."DATEVALUE" = {d '2000-01-03'} GROUP BY g_0."INTKEY"]
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
10 years, 7 months
[JBoss JIRA] (TEIID-3490) WITH [TABLENAME] AS clause being sent to source query
by RH Bugzilla Integration (JIRA)
[ https://issues.jboss.org/browse/TEIID-3490?page=com.atlassian.jira.plugin... ]
RH Bugzilla Integration commented on TEIID-3490:
------------------------------------------------
Van Halbert <vhalbert(a)redhat.com> changed the Status of [bug 1224377|https://bugzilla.redhat.com/show_bug.cgi?id=1224377] from ASSIGNED to ON_QA
> WITH [TABLENAME] AS clause being sent to source query
> -----------------------------------------------------
>
> Key: TEIID-3490
> URL: https://issues.jboss.org/browse/TEIID-3490
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 8.7
> Reporter: Debbie Steigner
> Assignee: Steven Hawkins
> Fix For: 8.7.1.6_2, 8.11, 8.7.4
>
>
> The transformation for that table is:
> WITH MONTHEND AS (SELECT CONVERT(SQLBqt.tinya.DateValue, DATE) AS REFERENCE_VALUE FROM SQLBqt.tinya WHERE SQLBqt.tinya.IntKey = 2)
> SELECT
> RATES.INTKEY, MAX(CASE WHEN RATES.INTKEY = 20 THEN RATES.STRINGKEY END) AS GBP_RATE, MAX(CASE WHEN RATES.INTKEY = 25 THEN RATES.STRINGKEY END) AS EUR_RATE, MAX(CASE WHEN RATES.INTKEY = 30 THEN RATES.STRINGKEY END) AS USD_RATE
> FROM
> Oraclebqt.dssmalla2 AS RATES
> WHERE
> (RATES.INTKEY IN (20, 25, 30)) AND (RATES.DATEVALUE = (SELECT MONTHEND.REFERENCE_VALUE FROM MONTHEND))
> GROUP BY RATES.INTKEY
> source queries are showing the With clause being pushed down to Oracle and they shouldn't be.
> 11:11:54,283 DEBUG [org.teiid.CONNECTOR] (Worker3_QueryProcessorQueue30) Vw26e70yOBmw.6.3.2 Processing NEW request: WITH MONTHEND (REFERENCE_VALUE) AS (SELECT CONVERT(g_0.DateValue, DATE) FROM SQLBqt.tinya AS g_0 WHERE g_0.IntKey = 2) SELECT g_0.INTKEY, MAX(CASE WHEN g_0.INTKEY = 20 THEN g_0.STRINGKEY END), MAX(CASE WHEN g_0.INTKEY = 25 THEN g_0.STRINGKEY END), MAX(CASE WHEN g_0.INTKEY = 30 THEN g_0.STRINGKEY END) FROM Oraclebqt.DSSMALLA2 AS g_0 WHERE (g_0.INTKEY IN (20, 25, 30)) AND (g_0.DATEVALUE = {d'2000-01-03'}) GROUP BY g_0.INTKEY
> 11:11:54,283 DEBUG [org.jboss.jca.core.connectionmanager.pool.strategy.OnePool] (Worker3_QueryProcessorQueue30) Oraclebqt: getConnection(null, WrappedConnectionRequestInfo@2efc4e0e[userName=bqt2]) [0/20]
> 11:11:54,283 INFO [org.teiid.CONNECTOR] (Worker3_QueryProcessorQueue30) OracleExecutionFactory Commit=true;DatabaseProductName=Oracle;DatabaseProductVersion=Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
> With the Partitioning, OLAP, Data Mining and Real Application Testing options;DriverMajorVersion=11;DriverMajorVersion=2;DriverName=Oracle JDBC driver;DriverVersion=11.2.0.1.0;IsolationLevel=2
> 11:11:54,283 DEBUG [org.teiid.CONNECTOR] (Worker3_QueryProcessorQueue30) Vw26e70yOBmw.6.3.2 Obtained execution
> 11:11:54,283 DEBUG [org.teiid.COMMAND_LOG] (Worker3_QueryProcessorQueue30) START DATA SRC COMMAND: startTime=2015-05-22 11:11:54.283 requestID=Vw26e70yOBmw.6 sourceCommandID=3 executionID=2 txID=null modelName=Oraclebqt translatorName=oracle sessionID=Vw26e70yOBmw principal=teiiduser@teiid-security sql=WITH MONTHEND (REFERENCE_VALUE) AS (SELECT CONVERT(g_0.DateValue, DATE) FROM SQLBqt.tinya AS g_0 WHERE g_0.IntKey = 2) SELECT g_0.INTKEY, MAX(CASE WHEN g_0.INTKEY = 20 THEN g_0.STRINGKEY END), MAX(CASE WHEN g_0.INTKEY = 25 THEN g_0.STRINGKEY END), MAX(CASE WHEN g_0.INTKEY = 30 THEN g_0.STRINGKEY END) FROM Oraclebqt.DSSMALLA2 AS g_0 WHERE (g_0.INTKEY IN (20, 25, 30)) AND (g_0.DATEVALUE = {d'2000-01-03'}) GROUP BY g_0.INTKEY
> 11:11:54,285 DEBUG [org.teiid.CONNECTOR] (Worker3_QueryProcessorQueue30) Source-specific command: WITH MONTHEND AS (SELECT trunc(cast(g_0."DateValue" AS date)) AS REFERENCE_VALUE FROM "bqt2"."dbo"."tinya" g_0 WHERE g_0."IntKey" = 2) SELECT g_0."INTKEY", MAX(CASE WHEN g_0."INTKEY" = 20 THEN g_0."STRINGKEY" END), MAX(CASE WHEN g_0."INTKEY" = 25 THEN g_0."STRINGKEY" END), MAX(CASE WHEN g_0."INTKEY" = 30 THEN g_0."STRINGKEY" END) FROM "BQT2"."DSSMALLA2" g_0 WHERE g_0."INTKEY" IN (20, 25, 30) AND g_0."DATEVALUE" = {d '2000-01-03'} GROUP BY g_0."INTKEY"
> 11:11:54,285 TRACE [org.teiid.CONNECTOR] (Worker3_QueryProcessorQueue30) Source sql WITH MONTHEND AS (SELECT trunc(cast(g_0."DateValue" AS date)) AS REFERENCE_VALUE FROM "bqt2"."dbo"."tinya" g_0 WHERE g_0."IntKey" = 2) SELECT g_0."INTKEY", MAX(CASE WHEN g_0."INTKEY" = 20 THEN g_0."STRINGKEY" END), MAX(CASE WHEN g_0."INTKEY" = 25 THEN g_0."STRINGKEY" END), MAX(CASE WHEN g_0."INTKEY" = 30 THEN g_0."STRINGKEY" END) FROM "BQT2"."DSSMALLA2" g_0 WHERE g_0."INTKEY" IN (20, 25, 30) AND g_0."DATEVALUE" = {d '2000-01-03'} GROUP BY g_0."INTKEY"
> 11:11:55,514 DEBUG [org.teiid.COMMAND_LOG] (Worker3_QueryProcessorQueue30) ERROR SRC COMMAND: endTime=2015-05-22 11:11:55.514 requestID=Vw26e70yOBmw.6 sourceCommandID=3 executionID=2 txID=null modelName=Oraclebqt translatorName=oracle sessionID=Vw26e70yOBmw principal=teiiduser@teiid-security finalRowCount=null
> 11:11:55,514 WARN [org.teiid.CONNECTOR] (Worker3_QueryProcessorQueue30) Connector worker process failed for atomic-request=Vw26e70yOBmw.6.3.2: org.teiid.translator.jdbc.JDBCExecutionException: 907 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: WITH MONTHEND AS (SELECT trunc(cast(g_0."DateValue" AS date)) AS REFERENCE_VALUE FROM "bqt2"."dbo"."tinya" g_0 WHERE g_0."IntKey" = 2) SELECT g_0."INTKEY", MAX(CASE WHEN g_0."INTKEY" = 20 THEN g_0."STRINGKEY" END), MAX(CASE WHEN g_0."INTKEY" = 25 THEN g_0."STRINGKEY" END), MAX(CASE WHEN g_0."INTKEY" = 30 THEN g_0."STRINGKEY" END) FROM "BQT2"."DSSMALLA2" g_0 WHERE g_0."INTKEY" IN (20, 25, 30) AND g_0."DATEVALUE" = {d '2000-01-03'} GROUP BY g_0."INTKEY"]
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
10 years, 7 months
[JBoss JIRA] (TEIID-3652) Teiid could push some outer joins to the source database, but does not
by Mike Higgins (JIRA)
Mike Higgins created TEIID-3652:
-----------------------------------
Summary: Teiid could push some outer joins to the source database, but does not
Key: TEIID-3652
URL: https://issues.jboss.org/browse/TEIID-3652
Project: Teiid
Issue Type: Bug
Components: Query Engine
Affects Versions: 8.11.2
Reporter: Mike Higgins
Assignee: Steven Hawkins
In the following SQL:
select *
from s1.t1 t1
left outer join s2.t2 t2 on (t1.id = t2.id)
left outer join s2.t3 t3 on (t2.id2 = t3.id2)
where t1.name = 'x'
option makedep s2.t2
where t1 is in one database and t2 and t3 are in a different database,
Teiid could push the second left outer join to the source database. Currently, it does not and performs the join itself. Some outer joins cannot be pushed, of course, but this one and others like it could be.
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
10 years, 7 months