[JBoss JIRA] (TEIID-4469) Insert with query expression does not apply source hint to target
by Johnathon Lee (JIRA)
[ https://issues.jboss.org/browse/TEIID-4469?page=com.atlassian.jira.plugin... ]
Johnathon Lee updated TEIID-4469:
---------------------------------
Fix Version/s: 8.12.8.6_3
> Insert with query expression does not apply source hint to target
> -----------------------------------------------------------------
>
> Key: TEIID-4469
> URL: https://issues.jboss.org/browse/TEIID-4469
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 8.7.1.6_2
> Reporter: Marc Shirley
> Assignee: Steven Hawkins
> Fix For: 9.1, 9.0.5, 8.7.10.6_2, 8.12.8.6_3
>
> Attachments: oracledba-vdb.xml
>
>
> "INSERT INTO ... SELECT ..." statements result in a ProjectIntoNode which seems to prevent any source hints from being passed down to the relevant source. These work correctly with an "INSERT INTO ... VALUES ..." statement. I've included example user and final query plan data below for the non-working [1] and working [2] insert statements.
> [1] source hint not applied to ProjectIntoNode:
> USER COMMAND:
> INSERT /*+sh test:'append' */ INTO test.TEST (ID, "VALUE") SELECT X.id AS ID, X."value" AS "VALUE" FROM (SELECT bqt.SMALLA.INTKEY AS id, bqt.SMALLA.INTNUM AS "value" FROM bqt.SMALLA WHERE bqt.SMALLA.INTKEY = 0) AS X
> ...
> OPTIMIZATION COMPLETE:
> PROCESSOR PLAN:
> ProjectIntoNode(0) output=[Count] test.TEST
> AccessNode(1) output=[bqt.SMALLA.INTKEY AS ID, bqt.SMALLA.INTNUM AS "VALUE"] SELECT /*+sh test:'append' */ g_0.INTKEY, g_0.INTNUM FROM bqt.SMALLA AS g_0 WHERE g_0.INTKEY = 0
> [2] source hint applied to AccessNode:
> USER COMMAND:
> INSERT /*+sh test:'append' */ INTO test.TEST (ID, "VALUE") VALUES ('-1', '-1')
> ...
> OPTIMIZATION COMPLETE:
> PROCESSOR PLAN:
> AccessNode(0) output=[Count] INSERT /*+sh test:'append' */ INTO test.TEST (ID, "VALUE") VALUES ('-1', '-1')
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
9 years, 4 months
[JBoss JIRA] (TEIID-4566) Use of $top without $filter in odata url query fails
by Paul Richardson (JIRA)
[ https://issues.jboss.org/browse/TEIID-4566?page=com.atlassian.jira.plugin... ]
Paul Richardson updated TEIID-4566:
-----------------------------------
Description:
Have built teiid dv 6.3 distribution from git (d2642e58) and installed into eap.
On deployment of vdb, odata url works correctly with most parameters. However, it returns
{code:xml}
<error>
<code>null</code>
<message>Assertion failed.</message>
</error>
{code}
for urls containing the $top=x parameter (where x is some number), eg.
https://localhost:8443/odata4/usstates.1/USStates/SvcView?$top=10
On the other hand, if I add a filter to the set of parameters, the top 10 is returned correctly, eg.
https://localhost:8443/odata4/usstates.1/USStates/SvcView?$top=10&$filter...
The stacktrace in the jb log can be found [here|https://gist.github.com/phantomjinx/b8fbc45329762daa9972b0fe77472f08].
was:
Have built teiid dv 6.3 distribution from git (d2642e58) and installed into eap.
On deployment of vdb, odata url works correctly with most parameters. However, it returns
{code:xml}
<error>
<code>null</code>
<message>Assertion failed.</message>
</error>
{code}
for urls only containing the $top=x parameter (where x is some number), eg.
https://localhost:8443/odata4/usstates.1/USStates/SvcView?$top=10
On the other hand, if I add a filter to the set of parameters, the top 10 is returned correctly, eg.
https://localhost:8443/odata4/usstates.1/USStates/SvcView?$top=10&$filter...
The stacktrace in the jb log can be found [here|https://gist.github.com/phantomjinx/b8fbc45329762daa9972b0fe77472f08].
> Use of $top without $filter in odata url query fails
> ----------------------------------------------------
>
> Key: TEIID-4566
> URL: https://issues.jboss.org/browse/TEIID-4566
> Project: Teiid
> Issue Type: Bug
> Components: OData
> Affects Versions: 8.12.7.6_3
> Reporter: Paul Richardson
> Assignee: Steven Hawkins
>
> Have built teiid dv 6.3 distribution from git (d2642e58) and installed into eap.
> On deployment of vdb, odata url works correctly with most parameters. However, it returns
> {code:xml}
> <error>
> <code>null</code>
> <message>Assertion failed.</message>
> </error>
> {code}
> for urls containing the $top=x parameter (where x is some number), eg.
> https://localhost:8443/odata4/usstates.1/USStates/SvcView?$top=10
> On the other hand, if I add a filter to the set of parameters, the top 10 is returned correctly, eg.
> https://localhost:8443/odata4/usstates.1/USStates/SvcView?$top=10&$filter...
> The stacktrace in the jb log can be found [here|https://gist.github.com/phantomjinx/b8fbc45329762daa9972b0fe77472f08].
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
9 years, 4 months
[JBoss JIRA] (TEIID-4565) Add objects table to system schema
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-4565?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-4565:
---------------------------------------
Note that properties are not objects by themselves, so e.uid refers to the object who has that property. Also do you mean for the view to contain all object types (not just table/column)? Can you provide a full example of how you would use this?
> Add objects table to system schema
> ----------------------------------
>
> Key: TEIID-4565
> URL: https://issues.jboss.org/browse/TEIID-4565
> Project: Teiid
> Issue Type: Feature Request
> Reporter: Tom Johnston
> Assignee: Steven Hawkins
>
> The addition of a objects table would make it simpler to join to when extensions are implemented.
> An example might be something similar to this query:
> select sysObjects.*
> from (
> select t.schemaName as Parent,t.name, t.uid ,'Table' as ObjectType
> from sys.tables t
> where schemaName not in (select distinct(schemaName) from "SYS"."Tables" where isSystem = true)
> UNION
> select c.tableName as Parent,c.name, c.uid,'Column' as ObjectType
> from sys.columns c
> where schemaName not in (select distinct(schemaName) from "SYS"."Tables" where isSystem = true)
> UNION
> select null as Parent,e.Value as Name,e.uid,'Property' as ObjectType
> from sys.properties as e) as SysObjects
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
9 years, 4 months
[JBoss JIRA] (TEIID-4565) Add objects table to system schema
by Tom Johnston (JIRA)
[ https://issues.jboss.org/browse/TEIID-4565?page=com.atlassian.jira.plugin... ]
Tom Johnston updated TEIID-4565:
--------------------------------
Description:
The addition of a objects table would make it simpler to join to when extensions are implemented.
An example might be something similar to this query:
select sysObjects.*
from (
select t.schemaName as Parent,t.name, t.uid ,'Table' as ObjectType
from sys.tables t
where schemaName not in (select distinct(schemaName) from "SYS"."Tables" where isSystem = true)
UNION
select c.tableName as Parent,c.name, c.uid,'Column' as ObjectType
from sys.columns c
where schemaName not in (select distinct(schemaName) from "SYS"."Tables" where isSystem = true)
UNION
select null as Parent,e.Value as Name,e.uid,'Property' as ObjectType
from sys.properties as e) as SysObjects
was:
The addition of a objects table would make it simpler to join to when extensions are implemented.
An example might be something similar to this query:
select sysObjects.*
from (
select t.schemaName as Parent,t.name, t.uid ,'Table' as ObjectType
from sys.tables t
where schemaName not in ('SYS','pg_catalog','SYSADMIN')
UNION
select c.tableName as Parent,c.name, c.uid,'Column' as ObjectType
from sys.columns c
where schemaName not in ('SYS','pg_catalog','SYSADMIN')
UNION
select null as Parent,e.Value as Name,e.uid,'Property' as ObjectType
from sys.properties as e) as SysObjects
> Add objects table to system schema
> ----------------------------------
>
> Key: TEIID-4565
> URL: https://issues.jboss.org/browse/TEIID-4565
> Project: Teiid
> Issue Type: Feature Request
> Reporter: Tom Johnston
> Assignee: Steven Hawkins
>
> The addition of a objects table would make it simpler to join to when extensions are implemented.
> An example might be something similar to this query:
> select sysObjects.*
> from (
> select t.schemaName as Parent,t.name, t.uid ,'Table' as ObjectType
> from sys.tables t
> where schemaName not in (select distinct(schemaName) from "SYS"."Tables" where isSystem = true)
> UNION
> select c.tableName as Parent,c.name, c.uid,'Column' as ObjectType
> from sys.columns c
> where schemaName not in (select distinct(schemaName) from "SYS"."Tables" where isSystem = true)
> UNION
> select null as Parent,e.Value as Name,e.uid,'Property' as ObjectType
> from sys.properties as e) as SysObjects
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
9 years, 4 months
[JBoss JIRA] (TEIID-4565) Add objects table to system schema
by Tom Johnston (JIRA)
Tom Johnston created TEIID-4565:
-----------------------------------
Summary: Add objects table to system schema
Key: TEIID-4565
URL: https://issues.jboss.org/browse/TEIID-4565
Project: Teiid
Issue Type: Feature Request
Reporter: Tom Johnston
Assignee: Steven Hawkins
The addition of a objects table would make it simpler to join to when extensions are implemented.
An example might be something similar to this query:
select sysObjects.*
from (
select t.schemaName as Parent,t.name, t.uid ,'Table' as ObjectType
from sys.tables t
where schemaName not in ('SYS','pg_catalog','SYSADMIN')
UNION
select c.tableName as Parent,c.name, c.uid,'Column' as ObjectType
from sys.columns c
where schemaName not in ('SYS','pg_catalog','SYSADMIN')
UNION
select null as Parent,e.Value as Name,e.uid,'Property' as ObjectType
from sys.properties as e) as SysObjects
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
9 years, 4 months