Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-7992

LEFT JOINed result of subquery with LEFT JOIN and WHERE IS NULL filter Returns unexpected result

    XMLWordPrintable

Details

    • 5.5.45, Compatibility-18, 5.5.54

    Description

      A table T1 is inner joined to a table T2 containing multiple timestamped rows with a FK reference to T1. Left joined to this is a subquery returning rows of T2 referenceing the same row of T1 but with newer timestamps or with identical timestamps and larger integer primary keys.

      Example results:

      +----+--------+----+------+---------------------+------+------+---------------------+
      | K1 | Name   | K2 | K1r  | rowTimestamp        | K2B  | K1rB | rowTimestampB       |
      +----+--------+----+------+---------------------+------+------+---------------------+
      |  1 | T1Row1 |  1 |    1 | 2015-04-13 10:42:11 |    2 |    1 | 2015-04-13 10:42:12 |
      |  1 | T1Row1 |  1 |    1 | 2015-04-13 10:42:11 |    3 |    1 | 2015-04-13 10:42:12 |
      |  1 | T1Row1 |  2 |    1 | 2015-04-13 10:42:12 |    3 |    1 | 2015-04-13 10:42:12 |
      |  1 | T1Row1 |  3 |    1 | 2015-04-13 10:42:12 | NULL | NULL | NULL                |
      +----+--------+----+------+---------------------+------+------+---------------------+

      Now a filter is added to the where clause:
      and K2B IS NULL
      and instead of returning just the last row of the above result set the results are as follows:

      +----+--------+----+------+---------------------+------+------+---------------+
      | K1 | Name   | K2 | K1r  | rowTimestamp        | K2B  | K1rB | rowTimestampB |
      +----+--------+----+------+---------------------+------+------+---------------+
      |  1 | T1Row1 |  1 |    1 | 2015-04-13 10:42:11 | NULL | NULL | NULL          |
      |  1 | T1Row1 |  2 |    1 | 2015-04-13 10:42:12 | NULL | NULL | NULL          |
      |  1 | T1Row1 |  3 |    1 | 2015-04-13 10:42:12 | NULL | NULL | NULL          |
      +----+--------+----+------+---------------------+------+------+---------------+

      as though new rows had been created in the joins.

      This behavior is not exhibited by MySql 5.5 on the same test data and queries.

      A detailed procedure for reproducing the issue is attached.

      The actual query is:

      SELECT
         t1a.*,
         t2a.*,
         t2b.K2 as K2B, 
         t2b.K1r as K1rB, 
         t2b.rowTimestamp as rowTimestampB, 
         t2b.Event as EventB
      FROM
         T1 as t1a
         JOIN T2 as t2a 
         ON t2a.K1r = t1a.K1
         LEFT JOIN (
            SELECT
              t2i.*
            FROM
               T1 as t1i
               LEFT JOIN T2 as t2i
               ON t2i.K1r = t1i.K1
            WHERE 
              t1i.K1 = 1
      	and t2i.K2 IS NOT NULL
         ) as t2b
         ON t2b.K1r = t1a.K1
            AND t2b.rowTimestamp > t2a.rowTimestamp
            OR (t2b.rowTimestamp = t2a.rowTimestamp AND t2b.K2 > t2a.K2)
      WHERE      
        t1a.K1 = 1
        and t2b.K2 IS NULL 

      The subquery used does appear peculiar to me:

           SELECT
              t2i.*
            FROM
               T1 as t1i
               LEFT JOIN T2 as t2i
               ON t2i.K1r = t1i.K1
            WHERE 
              t1i.K1 = 1
      	and t2i.K2 IS NOT NULL

      in that it uses a left join and a subsequent filter to verify non-null joined data. A more natural form might be:

            SELECT
              t2i.*
            FROM
               T1 as t1i
               JOIN T2 as t2i
               ON t2i.K1r = t1i.K1
            WHERE 
              t1i.K1 = 1

      I mention this because, in fact, using the second form of the subquery eliminates the unexpected behavior, a fact which may help in the diagnosis. However, I am testing MariaDB as a "drop-in replacement" for an existing MySql deployment and I don't have the option of modifying the SQL produced by the application.

      I apologize for the complexity of the test query. It was the simplest form I could find that demonstrated the behavior exhibited by the problematic application queries.

      Attachments

        1. diff
          1 kB
        2. BugReport.txt
          7 kB

        Issue Links

          Activity

            People

              igor Igor Babaev
              pmcgee Phil McGee
              Votes:
              0 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.