Uploaded image for project: 'Teiid'
  1. Teiid
  2. TEIID-1870

Querying random number from DB2 v97 does not push down to source

    XMLWordPrintable

Details

    • Bug
    • Resolution: Done
    • Major
    • 7.7, 8.0
    • 7.6
    • Query Engine
    • None

    Description

      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@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@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@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@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."

      Attachments

        Activity

          People

            rhn-engineering-shawkins Steven Hawkins
            pnittel_jira Paul Nittel (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: