[teiid-issues] [JBoss JIRA] (TEIID-1870) Querying random number from DB2 v97 does not push down to source

[Paul Nittel (Updated) (JIRA)]

     [ https://issues.jboss.org/browse/TEIID-1870?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Paul Nittel updated TEIID-1870:
-------------------------------

    Attachment: QT_db2v97ds_Push.vdb
                QT_db2v97ds_Push-ds.xml
                db2jcc.jar


VDB, driver, and data source definition
                
> Querying random number from DB2 v97 does not push down to source
> ----------------------------------------------------------------
>
>                 Key: TEIID-1870
>                 URL: https://issues.jboss.org/browse/TEIID-1870
>             Project: Teiid
>          Issue Type: Bug
>          Components: Query Engine
>    Affects Versions: 7.6
>            Reporter: Paul Nittel
>            Assignee: Steven Hawkins
>         Attachments: db2jcc.jar, QT_db2v97ds_Push-ds.xml, QT_db2v97ds_Push.vdb
>
>
> When I query SELECT RAND(), IntKey from SmallA using DB2 directly, I get back:
> 1,INTKEY
> 0.00125,0
> 0.56359,1
> 0.1933,2
> 0.80874,3
> Using the equivalent SQL through Teiid to DB2, I get:
> expr,IntKey
> 0.86867,0
> 0.86867,1
> 0.86867,2
> 0.86867,3
> The command log entry for the Teiid query looks like this:
> 2011-12-14 07:50:22,093 DEBUG [org.teiid.COMMAND_LOG] (New I/O server worker #1-2         START USER COMMAND:        startTime=2011-12-14 07:50:22.093        requestID=KURKVzpMf/MI.12        txID=null        sessionID=KURKVzpMf/MI        applicationName=JDBC        principal=user at teiid-security        vdbName=QT_db2v97ds_Push        vdbVersion=1        sql=select rand(), curtime(), intkey from bqt1.smalla limit 5
> 2011-12-14 07:50:22,099 DEBUG [org.teiid.COMMAND_LOG] (Worker6_QueryProcessorQueue1394         START DATA SRC COMMAND:        startTime=2011-12-14 07:50:22.098        requestID=KURKVzpMf/MI.12        sourceCommandID=0        txID=null        modelName=Source        connectorBindingName=db2        sessionID=KURKVzpMf/MI        principal=user at teiid-security        sql=SELECT convert(g_0.INTKEY, integer) AS c_2 FROM Source.SMALLA AS g_0 LIMIT 5
> 2011-12-14 07:50:22,394 DEBUG [org.teiid.COMMAND_LOG] (Worker6_QueryProcessorQueue1396         END SRC COMMAND:        endTime=2011-12-14 07:50:22.394        requestID=KURKVzpMf/MI.12        sourceCommandID=0        txID=null        modelName=Source        connectorBindingName=db2        sessionID=KURKVzpMf/MI        principal=user at teiid-security        finalRowCount=5
> 2011-12-14 07:50:22,427 DEBUG [org.teiid.COMMAND_LOG] (Worker5_QueryProcessorQueue1397         END USER COMMAND:        endTime=2011-12-14 07:50:22.427        requestID=KURKVzpMf/MI.12        txID=null        sessionID=KURKVzpMf/MI        principal=user at teiid-security        vdbName=QT_db2v97ds_Push        vdbVersion=1        finalRowCount=0
> The source command appears to be lacking the rand() function call. 
> In response, dev says, "So the issue here is that the final rewrite of the pushdown query is pre-evaluating the non-deterministic pushdown function.  If the function were not pushed-down you would get something more like you would expect."

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