IBM Support

Denodo returns no rows when a query includes correlated subqueries

Troubleshooting


Problem

A SQL statement executed by Denodo returns no rows when the SQL statement includes a correlated subquery.

The following SQL statement uses a subquery to select rows in the tsuppliers table based on value of the rows in the tsupply table.
SELECT
    "sno" AS "SNO", 
    "pno" AS "PNO", 
    "qty" AS "QTY"
FROM
    "tsupply" "sp1" 
WHERE 
    "qty" > (
    SELECT
        AVG(CAST('double', "qty"))
    FROM
        "tsupply" "sp2" 
    WHERE 
        "sp2"."jno" = "sp1"."jno" AND
        "sp2"."sno" IN ( 
            SELECT
                "sno"
            FROM
                "tsuppliers" "s" 
            WHERE 
                "s"."sno" = "sp1"."sno" )
    )
ORDER BY 
    "SNO" ASC, 
    "PNO" ASC

Symptom

Denodo server version 7 and 8 incorrectly returns a result set with no rows.

Cause

 Denodo defect #83598.

 

Resolving The Problem

Modify the query to avoid using correlated subqueries when possible.

Document Location

Worldwide

[{"Type":"MASTER","Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSTSF6","label":"IBM Cognos Analytics"},"ARM Category":[{"code":"a8m0z0000001jkbAAA","label":"Reporting-\u003EPerformance"}],"ARM Case Number":"","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF016","label":"Linux"},{"code":"PF033","label":"Windows"}],"Version":"All Versions"}]

Document Information

Modified date:
14 February 2024

UID

ibm17086155

Page Feedback