[teiid-issues] [JBoss JIRA] Commented: (TEIID-818) Error returned from SQL Server: Incorrect syntax near: NULLS

[Steve Hawkins (JIRA)]

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

Steve Hawkins commented on TEIID-818:
-------------------------------------

The translator class change is fine (then Postgres/DB2 should also be marked as supporting explicit ordering), but the problem is with the orderbyitem logic in the sqlconversionvisitor.  It should be:

NullOrder nullOrder = this.translator.getDefaultNullOrder();

if (!this.translator.supportsExplicitNullOrdering() || nullOrder == NullOrder.LOW) {
     return;
}

if (obj.getDirection() == IOrderByItem.ASC) {
     if (nullOrder != NullOrder.FIRST) {
          buffer.append(" NULLS FIRST"); //$NON-NLS-1$
     }
} else if (nullOrder == NullOrder.FIRST) {
     buffer.append(" NULLS LAST"); //$NON-NLS-1$
}

Since we currently don't support user specification of nulls first/last we are simply trying to make the source give the same answer as us (we treat nulls as low), so it shouldn't try to put the null ordering clause if the source is also nulls low.

> Error returned from SQL Server: Incorrect syntax near: NULLS
> ------------------------------------------------------------
>
>                 Key: TEIID-818
>                 URL: https://jira.jboss.org/jira/browse/TEIID-818
>             Project: Teiid
>          Issue Type: Bug
>          Components: Query Engine
>    Affects Versions: 6.2.0
>         Environment: Fedora 10, build from 8/31/09, Java HotSpot(TM) Server VM (build 11.0-b16, mixed mode)
>            Reporter: Paul Nittel
>            Assignee: Steven Hawkins
>         Attachments: PartsDesc_Teiid-818.zip, teiid.log.zip, TPCR_defect.zip
>
>
> Running the TPCR queries has worked previously, I believe even with Teiid from a short while back. Now it's not. This particular query is detailed below.
> Transformation text:
> SELECT 
> 		S_NAME, COUNT(*) AS numwait 
> FROM 
> 		TPCR.SUPPLIER, TPCR.LINEITEM AS l1, TPCR.ORDERS, TPCR.NATION 
> WHERE 
> 		(S_SUPPKEY = L_SUPPKEY) AND (O_ORDERKEY = L_ORDERKEY) AND (O_ORDERSTATUS = 'F') AND (L_RECEIPTDATE > L_COMMITDATE) AND (EXISTS (SELECT 
> 						* 
> FROM 
> 						TPCR.LINEITEM AS l2 
> WHERE 
> 						(l2.L_ORDERKEY = l1.L_ORDERKEY) AND (l2.L_SUPPKEY <> l1.L_SUPPKEY))) AND (NOT (EXISTS (SELECT 
> 							* 
> FROM 
> 							TPCR.LINEITEM AS l3 
> WHERE 
> 							(l3.L_ORDERKEY = l1.L_ORDERKEY) AND (l3.L_SUPPKEY <> l1.L_SUPPKEY) AND (l3.L_RECEIPTDATE > l3.L_COMMITDATE)))) AND (S_NATIONKEY = N_NATIONKEY) AND (N_NAME = 'SAUDI ARABIA') 
> GROUP BY S_NAME
> Query I executed:
> select * from vTpcr.Q21 ORDER BY numwait desc, s_name LIMIT 100;
> Error I received:
> 2009-09-02 10:42:23,491 ERROR [Worker0_QueryProcessorQueue1] org.teiid.DQP - Unexpected exception for request 0.0
> [MetaMatrixComponentException] 170: Error Code:170 Message:Error Code:170 Message:Error occurred on connector TPCR Connector<2> - Error Code:170 Message:Line 1: Incorrect syntax near 'NULLS'.
> Executing statement:
>   [SQL: SELECT TOP 100 * FROM (SELECT g_0.S_NAME AS c_0, COUNT(*) AS c_1 FROM SUPPLIER g_0, LINEITEM g_1, ORDERS g_2, NATION g_3 WHERE (g_0.S_SUPPKEY = g_1.L_SUPPKEY) AND (g_2.O_ORDERKEY = g_1.L_ORDERKEY) AND (g_0.S_NATIONKEY = g_3.N_NATIONKEY) AND (g_1.L_RECEIPTDATE > g_1.L_COMMITDATE) AND (EXISTS (SELECT g_4.L_ORDERKEY, g_4.L_PARTKEY, g_4.L_SUPPKEY, g_4.L_LINENUMBER, g_4.L_QUANTITY, g_4.L_EXTENDEDPRICE, g_4.L_DISCOUNT, g_4.L_TAX, g_4.L_RETURNFLAG, g_4.L_LINESTATUS, g_4.L_SHIPDATE, g_4.L_COMMITDATE, g_4.L_RECEIPTDATE, g_4.L_SHIPINSTRUCT, g_4.L_SHIPMODE, g_4.L_COMMENT FROM LINEITEM g_4 WHERE (g_4.L_ORDERKEY = g_1.L_ORDERKEY) AND (g_4.L_SUPPKEY <> g_1.L_SUPPKEY))) AND (NOT (EXISTS (SELECT g_5.L_ORDERKEY, g_5.L_PARTKEY, g_5.L_SUPPKEY, g_5.L_LINENUMBER, g_5.L_QUANTITY, g_5.L_EXTENDEDPRICE, g_5.L_DISCOUNT, g_5.L_TAX, g_5.L_RETURNFLAG, g_5.L_LINESTATUS, g_5.L_SHIPDATE, g_5.L_COMMITDATE, g_5.L_RECEIPTDATE, g_5.L_SHIPINSTRUCT, g_5.L_SHIPMODE, g_5.L_COMMENT FROM LINEITEM g_5 WHERE (g_5.L_ORDERKEY = g_1.L_ORDERKEY) AND (g_5.L_SUPPKEY <> g_1.L_SUPPKEY) AND (g_5.L_RECEIPTDATE > g_5.L_COMMITDATE)))) AND (g_2.O_ORDERSTATUS = 'F') AND (g_3.N_NAME = 'SAUDI ARABIA') GROUP BY g_0.S_NAME) AS X ORDER BY c_1 DESC NULLS LAST, c_0]
> 1 [ConnectorException]Error Code:170 Message:Error occurred on connector TPCR Connector<2> - Error Code:170 Message:Line 1: Incorrect syntax near 'NULLS'.
> This failed both in the Designer (running the version from 8/31) and Teiid (9/1, running as a server). I'll attach the server log and project to this defect.

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