[teiid-issues] [JBoss JIRA] Commented: (TEIID-755) Oracle Connector is not honouring metadata when rewriting ORDER BY clause

[Steven Hawkins (JIRA)]

    [ https://jira.jboss.org/jira/browse/TEIID-755?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12479414#action_12479414 ] 

Steven Hawkins commented on TEIID-755:
--------------------------------------

If it goes through the processor/AliasGenerator all derived columns will have proper output names.  The order by clause then typically references these names, not the metadata.  i.e.:
SELECT INTKEY FROM BQT1.SMALLA UNION SELECT INTKEY FROM BQT1.SMALLB ORDER BY INTKEY
will actually become:
SELECT g_1.IntKey c_0 FROM SmallA g_1 UNION SELECT g_0.IntKey c_0 FROM SmallB g_0 ORDER BY c_0 NULLS FIRST

see TestOracleSQLConversionVisitor.testRowLimitWithUnionOrderBy

The only time when the metadata will be needed in the order by clause is with an unrelated column:
SELECT INTKEY FROM BQT1.SMALLA ORDER BY INTNUM
when executed through the engine should become:
SELECT g_0.IntKey c_0 FROM SmallB g_0 ORDER BY g_0.IntNum NULLS FIRST


> Oracle Connector is not honouring metadata when rewriting ORDER BY clause
> -------------------------------------------------------------------------
>
>                 Key: TEIID-755
>                 URL: https://jira.jboss.org/jira/browse/TEIID-755
>             Project: Teiid
>          Issue Type: Bug
>          Components: JDBC Connector
>    Affects Versions: 6.0.0, 6.1.0, 6.2.0
>            Reporter: Larry O'Leary
>            Assignee: Steven Hawkins
>            Priority: Minor
>             Fix For: 6.2.0
>
>   Original Estimate: 1 hour
>  Remaining Estimate: 1 hour
>
> When executing a query that contains an ORDER BY clause, the Oracle Connector is rewriting the query using the column name given by the MM query rather than the source name as specified in the source model.  What this means is that column name case is not being enforced.  
> For example:
> MM User Command:
> SELECT INTKEY FROM BQT1.SMALLA UNION SELECT INTKEY FROM BQT1.SMALLB ORDER BY INTKEY
> Model represents INTKEY as IntKey for source name.  Currently we rewrite as:
> SELECT SmallA.IntKey FROM SmallA UNION SELECT SmallB.IntKey FROM SmallB ORDER BY INTKEY
> This should actually be:
> SELECT SmallA.IntKey FROM SmallA UNION SELECT SmallB.IntKey FROM SmallB ORDER BY IntKey
> Although this does not trigger a failure with the current implementation, if the column names were qualified is SQL identifiers, the query would fail as Oracle is expecting a column name of "IntKey" and would not know what "INTKEY" was.

-- 
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