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

Join subquery containing ifnull in join condition

    XMLWordPrintable

    Details

    • Type: Feature Request
    • Status: Closed (View Workflow)
    • Priority: Critical
    • Resolution: Won't Fix
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None
    • Labels:
      None

      Description

      I have join subquery linked to main query via 2 fields in the query below.
      To be able to join null values between main query and subquery, I have added ifnull to join fields with a random value.
      If I run my query directly over mysql, the field CalculatedField1 return values whenever the field RESOLUTIONDATE is null in the main query.
      But when executed in Teiid, the query returns always null for the field CalculatedField1.
      When looking at the jdbc queries executed by Teiid, I found that ifnull is used for the main query fields but not for the subquery fields, which may be causing a link problem between main query and subquery.
      I have tried to put the ifnull into the subquery but still getting jdbc subquery fields without the ifnull function.

      SELECT DISTINCT
      jiraissue.RESOLUTIONDATE AS jiraissue_RESOLUTIONDATE,
      jiraissue.CREATED AS jiraissue_CREATED,
      CalculatedField1_Sub.CalculatedField1 AS CalculatedField1
      FROM tjiradb_D1_VDB_DSModel.tjiradb.jiraissue jiraissue
      LEFT JOIN
      (SELECT DISTINCT
      TIMESTAMPDIFF(
      SQL_TSI_MINUTE,
      jiraissue_sub.CREATED,
      PARSETIMESTAMP('2014-10-03 00:00:00',
      'yyyy-MM-dd HH:mm:ss'))
      AS CalculatedField1,
      jiraissue_sub.RESOLUTIONDATE AS jiraissue_RESOLUTIONDATE,
      jiraissue_sub.CREATED AS jiraissue_CREATED
      FROM tjiradb_D1_VDB_DSModel.tjiradb.jiraissue jiraissue_sub
      WHERE ((((jiraissue_sub.RESOLUTIONDATE IS NULL)))))
      CalculatedField1_Sub
      ON (IFNULL(jiraissue.RESOLUTIONDATE, -2147483648) =
      IFNULL(CalculatedField1_Sub.jiraissue_RESOLUTIONDATE,
      -2147483648))
      AND (IFNULL(jiraissue.CREATED, -2147483648) =
      IFNULL(CalculatedField1_Sub.jiraissue_CREATED,
      -2147483648))

      Teiid JDBC queries:

      SELECT DISTINCT g_0.`RESOLUTIONDATE` AS c_0, g_0.`CREATED` AS c_1, ifnull(date_format(g_0.`RESOLUTIONDATE`, '%Y-%m-%d %H:%i:%S.%f'), '2147483648') AS c_2, ifnull(date_format(g_0.`CREATED`, '%Y%m-%d %H:%i:%S.%f'), '-2147483648') AS c_3 FROM `tjiradb`.`jiraissue` AS g_0 ORDER BY c_2, c_3

      SELECT g_0.`CREATED`, g_0.`RESOLUTIONDATE` FROM `tjiradb`.`jiraissue` AS g_0 WHERE g_0.`RESOLUTIONDATE` IS NULL

      Teiid Execution plan

      <?xml version='1.0' encoding='UTF-8'?><node name="LimitNode"><property name="Output Columns"><value>jiraissue_RESOLUTIONDATE (timestamp)</value><value>jiraissue_CREATED (timestamp)</value><value>CalculatedField1 (long)</value></property><property name="Statistics"><value>Node Output Rows: 10</value><value>Node Next Batch Process Time: 7</value><value>Node Cumulative Next Batch Process Time: 43</value><value>Node Cumulative Process Time: 49</value><value>Node Next Batch Calls: 4</value><value>Node Blocks: 3</value></property><property name="Cost Estimates"><value>Estimated Node Cardinality: 10.0</value></property><property name="Child 0"><node name="SortNode"><property name="Output Columns"><value>jiraissue_RESOLUTIONDATE (timestamp)</value><value>jiraissue_CREATED (timestamp)</value><value>CalculatedField1 (long)</value></property><property name="Statistics"><value>Node Output Rows: 10</value><value>Node Next Batch Process Time: 13</value><value>Node Cumulative Next Batch Process Time: 42</value><value>Node Cumulative Process Time: 48</value><value>Node Next Batch Calls: 4</value><value>Node Blocks: 3</value></property><property name="Cost Estimates"><value>Estimated Node Cardinality: -1.0</value></property><property name="Child 0"><node name="ProjectNode"><property name="Output Columns"><value>jiraissue_RESOLUTIONDATE (timestamp)</value><value>jiraissue_CREATED (timestamp)</value><value>CalculatedField1 (long)</value></property><property name="Statistics"><value>Node Output Rows: 2048</value><value>Node Next Batch Process Time: 0</value><value>Node Cumulative Next Batch Process Time: 35</value><value>Node Cumulative Process Time: 0</value><value>Node Next Batch Calls: 5</value><value>Node Blocks: 4</value></property><property name="Cost Estimates"><value>Estimated Node Cardinality: -1.0</value></property><property name="Child 0"><node name="JoinNode"><property name="Output Columns"><value>RESOLUTIONDATE (timestamp)</value><value>CREATED (timestamp)</value><value>CalculatedField1 (long)</value></property><property name="Statistics"><value>Node Output Rows: 2048</value><value>Node Next Batch Process Time: 0</value><value>Node Cumulative Next Batch Process Time: 35</value><value>Node Cumulative Process Time: 0</value><value>Node Next Batch Calls: 5</value><value>Node Blocks: 4</value></property><property name="Cost Estimates"><value>Estimated Node Cardinality: -1.0</value></property><property name="Child 0"><node name="AccessNode"><property name="Output Columns"><value>RESOLUTIONDATE (timestamp)</value><value>CREATED (timestamp)</value><value>expr (string)</value><value>expr (string)</value></property><property name="Statistics"><value>Node Output Rows: 2048</value><value>Node Next Batch Process Time: 0</value><value>Node Cumulative Next Batch Process Time: 1</value><value>Node Cumulative Process Time: 0</value><value>Node Next Batch Calls: 10</value><value>Node Blocks: 6</value></property><property name="Cost Estimates"><value>Estimated Node Cardinality: -1.0</value></property><property name="Query"><value>SELECT DISTINCT g_0.RESOLUTIONDATE AS c_0, g_0.CREATED AS c_1, IFNULL(convert(g_0.RESOLUTIONDATE, string), '-2147483648') AS c_2, IFNULL(convert(g_0.CREATED, string), '-2147483648') AS c_3 FROM tjiradb_D1_VDB_DSModel.tjiradb.jiraissue AS g_0 ORDER BY c_2, c_3</value></property><property name="Model Name"><value>tjiradb_D1_VDB_DSModel</value></property></node></property><property name="Child 1"><node name="ProjectNode"><property name="Output Columns"><value>CalculatedField1 (long)</value><value>jiraissue_RESOLUTIONDATE (timestamp)</value><value>jiraissue_CREATED (timestamp)</value><value>CalculatedField11 (long)</value><value>expr (string)</value><value>expr (string)</value></property><property name="Statistics"><value>Node Output Rows: 1320</value><value>Node Next Batch Process Time: 22</value><value>Node Cumulative Next Batch Process Time: 18</value><value>Node Cumulative Process Time: 25</value><value>Node Next Batch Calls: 4</value><value>Node Blocks: 1</value></property><property name="Child 0"><node name="ProjectNode"><property name="Output Columns"><value>CalculatedField1 (long)</value><value>jiraissue_RESOLUTIONDATE (timestamp)</value><value>jiraissue_CREATED (timestamp)</value><value>CalculatedField11 (long)</value></property><property name="Statistics"><value>Node Output Rows: 1320</value><value>Node Next Batch Process Time: 9</value><value>Node Cumulative Next Batch Process Time: 3</value><value>Node Cumulative Process Time: 10</value><value>Node Next Batch Calls: 2</value><value>Node Blocks: 1</value></property><property name="Cost Estimates"><value>Estimated Node Cardinality: -1.0</value></property><property name="Child 0"><node name="AccessNode"><property name="Output Columns"><value>CREATED (timestamp)</value><value>RESOLUTIONDATE (timestamp)</value></property><property name="Statistics"><value>Node Output Rows: 1320</value><value>Node Next Batch Process Time: 8</value><value>Node Cumulative Next Batch Process Time: 1</value><value>Node Cumulative Process Time: 8</value><value>Node Next Batch Calls: 2</value><value>Node Blocks: 1</value></property><property name="Cost Estimates"><value>Estimated Node Cardinality: -1.0</value></property><property name="Query"><value>SELECT g_0.CREATED, g_0.RESOLUTIONDATE FROM tjiradb_D1_VDB_DSModel.tjiradb.jiraissue AS g_0 WHERE g_0.RESOLUTIONDATE IS NULL</value></property><property name="Model Name"><value>tjiradb_D1_VDB_DSModel</value></property></node></property><property name="Select Columns"><value>TIMESTAMPDIFF(SQL_TSI_MINUTE, jiraissue_sub.CREATED,

      {ts'2014-10-03 00:00:00.0'}

      ) AS CalculatedField1</value><value>jiraissue_sub.RESOLUTIONDATE AS jiraissue_RESOLUTIONDATE</value><value>jiraissue_sub.CREATED AS jiraissue_CREATED</value><value>TIMESTAMPDIFF(SQL_TSI_MINUTE, jiraissue_sub.CREATED,

      {ts'2014-10-03 00:00:00.0'}

      ) AS CalculatedField11</value></property></node></property><property name="Select Columns"><value>CalculatedField1_Sub.CalculatedField1</value><value>CalculatedField1_Sub.jiraissue_RESOLUTIONDATE</value><value>CalculatedField1_Sub.jiraissue_CREATED</value><value>CalculatedField1_Sub.CalculatedField11</value><value>IFNULL(convert(CalculatedField1_Sub.jiraissue_RESOLUTIONDATE, string), '-2147483648')</value><value>IFNULL(convert(CalculatedField1_Sub.jiraissue_CREATED, string), '-2147483648')</value></property></node></property><property name="Join Strategy"><value>MERGE JOIN (ALREADY_SORTED/SORT_DISTINCT)</value></property><property name="Join Type"><value>LEFT OUTER JOIN</value></property><property name="Join Criteria"><value>IFNULL(convert(jiraissue.RESOLUTIONDATE, string), '-2147483648')=IFNULL(convert(CalculatedField1_Sub.jiraissue_RESOLUTIONDATE, string), '-2147483648')</value><value>IFNULL(convert(jiraissue.CREATED, string), '-2147483648')=IFNULL(convert(CalculatedField1_Sub.jiraissue_CREATED, string), '-2147483648')</value></property></node></property><property name="Select Columns"><value>jiraissue.RESOLUTIONDATE AS jiraissue_RESOLUTIONDATE</value><value>jiraissue.CREATED AS jiraissue_CREATED</value><value>CalculatedField1_Sub.CalculatedField1 AS CalculatedField1</value></property></node></property><property name="Sort Mode"><value>DUP_REMOVE</value></property></node></property><property name="Row Offset"><value>null</value></property><property name="Row Limit"><value>10</value></property></node>

        Gliffy Diagrams

          Attachments

            Activity

              People

              • Assignee:
                shawkins Steven Hawkins
                Reporter:
                mtawk Mark Tawk
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: