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

Error on Subquery with union and view join in union

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Critical
    • Resolution: Fixed
    • Affects Version/s: 5.5.33a
    • Fix Version/s: 5.5.34
    • Component/s: None
    • Labels:
      None
    • Environment:
      Fedora 19

      Description

      ########### PREFACE ################################
       
      CREATE OR REPLACE VIEW invoiced_trades_view AS
      (
      SELECT il.trade_origin,
             il.trade_id,
             il.trade_line_id
        FROM invoice_head ih,
             invoice_line il
       WHERE ih.id = il.invoice_id
         AND ih.creditnote_id IS NULL
      );
       
      select * from invoiced_trades_view where 
          ->   ( trade_id = 16123 and trade_line_id = 52350 )
          -> or ( trade_id = 16129 and trade_line_id = 52370 )
          -> or ( trade_id = 16977 and trade_line_id = 55162 )
          -> or ( trade_id = 16984 and trade_line_id = 55185 );
      +--------------+----------+---------------+
      | trade_origin | trade_id | trade_line_id |
      +--------------+----------+---------------+
      | IWBMARKET    |    16123 |         52350 |
      | IWBMARKET    |    16129 |         52370 |
      | IWBMARKET    |    16984 |         55185 |
      | IWBMARKET    |    16977 |         55162 |
      +--------------+----------+---------------+

      ============ FAILING QUERY ==========================
       
      SELECT *
      FROM
      (
      (
      SELECT 'IWBMARKET-1' trade_origin,
             it.trade_origin trade_origin_it,
             th.id trade_id,
             tl.id trade_line_id,
             tl.trader_id trader_id
        FROM trade_head th
             JOIN trade_line tl ON th.id = tl.trade_id
        LEFT JOIN invoiced_trades_view it ON it.trade_origin = 'IWBMARKET'
                                         AND it.trade_id = th.id
                                         AND it.trade_line_id = tl.id
       WHERE th.is_closed = 'Y'
         AND th.is_deleted = 'N'
         AND tl.is_deleted = 'N'
         AND tl.is_billable = 'Y'
         AND it.trade_origin IS NULL
      )
      UNION ALL
      (
      SELECT 'IWBMARKET-2' trade_origin,
             it.trade_origin trade_origin_it,
             th.id trade_id,
             tl.id trade_line_id,
             tl.trader_id trader_id
        FROM trade_head th
             JOIN trade_line tl ON tl.trade_id = th.id
        LEFT JOIN invoiced_trades_view it ON it.trade_origin = 'IWBMARKET'
                                         AND it.trade_id = th.id
                                         AND it.trade_line_id = tl.id
       WHERE th.is_closed = 'Y'
         AND th.is_deleted = 'N'
         AND tl.is_deleted = 'N'
         AND tl.is_billable = 'Y'
         AND it.trade_origin IS NULL 
      )
      ) t
      where trader_id = 1488
      ;

      >>>>>>>> EXPECTED RESULT <<<<<<<<<<
       
      no columns selected

      >>>>>>>> MariaDB 5.5.33a <<<<<<<<<<
       
      +--------------+-----------------+----------+---------------+-----------+
      | trade_origin | trade_origin_it | trade_id | trade_line_id | trader_id |
      +--------------+-----------------+----------+---------------+-----------+
      | IWBMARKET-1  | NULL            |    16977 |         55162 |      1488 |
      | IWBMARKET-1  | NULL            |    16984 |         55185 |      1488 |
      | IWBMARKET-2  | NULL            |    16977 |         55162 |      1488 |
      | IWBMARKET-2  | NULL            |    16984 |         55185 |      1488 |
      +--------------+-----------------+----------+---------------+-----------+

      >>>>>>>>>>> MySQL 5.5.32 <<<<<<<<<<<<<<<
       
      Empty set (0.65 sec)

      >>>>>>>>>>> MariaDB 5.5.33a explain <<<<<<<<<<<<<<<<<<<
       
      +------+--------------+------------+--------+----------------------+---------+---------+-------------------------------------------------------+-------+-------------+
      | id   | select_type  | table      | type   | possible_keys        | key     | key_len | ref                                                   | rows  | Extra       |
      +------+--------------+------------+--------+----------------------+---------+---------+-------------------------------------------------------+-------+-------------+
      |    1 | PRIMARY      | <derived2> | ALL    | NULL                 | NULL    | NULL    | NULL                                                  | 32686 | Using where |
      |    2 | DERIVED      | th         | ALL    | PRIMARY              | NULL    | NULL    | NULL                                                  | 16343 | Using where |
      |    2 | DERIVED      | tl         | ref    | idx1                 | idx1    | 4       | iwbmarket_test.th.id                                  |     1 | Using where |
      |    2 | DERIVED      | il         | ref    | invoice_id,idx1,idx2 | idx1    | 70      | const,iwbmarket_test.th.id,iwbmarket_test.tl.id       |     1 | Using where |
      |    2 | DERIVED      | ih         | eq_ref | PRIMARY              | PRIMARY | 4       | iwbmarket_test.il.invoice_id                          |     1 | Using where |
      |    3 | UNION        | th         | ALL    | PRIMARY              | NULL    | NULL    | NULL                                                  | 16343 | Using where |
      |    3 | UNION        | tl         | ref    | idx1                 | idx1    | 4       | iwbmarket_test.th.id                                  |     1 | Using where |
      |    3 | UNION        | il         | ref    | invoice_id,idx1,idx2 | idx1    | 70      | const,iwbmarket_test.tl.trade_id,iwbmarket_test.tl.id |     1 | Using where |
      |    3 | UNION        | ih         | eq_ref | PRIMARY              | PRIMARY | 4       | iwbmarket_test.il.invoice_id                          |     1 | Using where |
      | NULL | UNION RESULT | <union2,3> | ALL    | NULL                 | NULL    | NULL    | NULL                                                  |  NULL |             |
      +------+--------------+------------+--------+----------------------+---------+---------+-------------------------------------------------------+-------+-------------+
      10 rows in set (0.00 sec)

      >>>>>>>>>>> MySQL 5.5.32 explain <<<<<<<<<<<<<<<<<<
       
      (i had to rename the database from iwbmarket_test to rudy)
       
      +----+--------------+------------+--------+----------------------+---------+---------+-----------------------------+-------+-------------+
      | id | select_type  | table      | type   | possible_keys        | key     | key_len | ref                         | rows  | Extra       |
      +----+--------------+------------+--------+----------------------+---------+---------+-----------------------------+-------+-------------+
      |  1 | PRIMARY      | <derived2> | ALL    | NULL                 | NULL    | NULL    | NULL                        |  1734 | Using where |
      |  2 | DERIVED      | th         | ALL    | PRIMARY              | NULL    | NULL    | NULL                        | 16574 | Using where |
      |  2 | DERIVED      | tl         | ref    | idx1                 | idx1    | 4       | rudy.th.id                  |     1 | Using where |
      |  2 | DERIVED      | il         | ref    | invoice_id,idx1,idx2 | idx1    | 70      | rudy.th.id,rudy.tl.id       |     1 | Using where |
      |  2 | DERIVED      | ih         | eq_ref | PRIMARY              | PRIMARY | 4       | rudy.il.invoice_id          |     1 |             |
      |  3 | UNION        | th         | ALL    | PRIMARY              | NULL    | NULL    | NULL                        | 16574 | Using where |
      |  3 | UNION        | tl         | ref    | idx1                 | idx1    | 4       | rudy.th.id                  |     1 | Using where |
      |  3 | UNION        | il         | ref    | invoice_id,idx1,idx2 | idx1    | 70      | rudy.tl.trade_id,rudy.tl.id |     1 | Using where |
      |  3 | UNION        | ih         | eq_ref | PRIMARY              | PRIMARY | 4       | rudy.il.invoice_id          |     1 |             |
      | NULL | UNION RESULT | <union2,3> | ALL    | NULL                 | NULL    | NULL    | NULL                        |  NULL |             |
      +----+--------------+------------+--------+----------------------+---------+---------+-----------------------------+-------+-------------+
      10 rows in set (0.65 sec)

      If I remove the "AND it.trade_origin IS NULL" restriction, this happens:

      SELECT *
      FROM
      (
      (
      SELECT 'IWBMARKET-1' trade_origin,
             it.trade_origin trade_origin_it,
             th.id trade_id,
             tl.id trade_line_id,
             tl.trader_id trader_id
        FROM trade_head th
             JOIN trade_line tl ON th.id = tl.trade_id
        LEFT JOIN invoiced_trades_view it ON it.trade_origin = 'IWBMARKET'
                                         AND it.trade_id = th.id
                                         AND it.trade_line_id = tl.id
       WHERE th.is_closed = 'Y'
         AND th.is_deleted = 'N'
         AND tl.is_deleted = 'N'
         AND tl.is_billable = 'Y'
      )
      UNION ALL
      (
      SELECT 'IWBMARKET-2' trade_origin,
             it.trade_origin trade_origin_it,
             th.id trade_id,
             tl.id trade_line_id,
             tl.trader_id trader_id
        FROM trade_head th
             JOIN trade_line tl ON tl.trade_id = th.id
        LEFT JOIN invoiced_trades_view it ON it.trade_origin = 'IWBMARKET'
                                         AND it.trade_id = th.id
                                         AND it.trade_line_id = tl.id
       WHERE th.is_closed = 'Y'
         AND th.is_deleted = 'N'
         AND tl.is_deleted = 'N'
         AND tl.is_billable = 'Y'
      )
      ) t
      where trader_id = 1488
      ;
       
      ;+--------------+-----------------+----------+---------------+-----------+
      | trade_origin | trade_origin_it | trade_id | trade_line_id | trader_id |
      +--------------+-----------------+----------+---------------+-----------+
      | IWBMARKET-1  | IWBMARKET       |    16123 |         52350 |      1488 |
      | IWBMARKET-1  | IWBMARKET       |    16129 |         52370 |      1488 |
      | IWBMARKET-1  | NULL            |    16977 |         55162 |      1488 |
      | IWBMARKET-1  | NULL            |    16984 |         55185 |      1488 |
      | IWBMARKET-2  | IWBMARKET       |    16123 |         52350 |      1488 |
      | IWBMARKET-2  | IWBMARKET       |    16129 |         52370 |      1488 |
      | IWBMARKET-2  | NULL            |    16977 |         55162 |      1488 |
      | IWBMARKET-2  | NULL            |    16984 |         55185 |      1488 |
      +--------------+-----------------+----------+---------------+-----------+
      8 rows in set (0.86 sec)

      And if I add a join to the primary key of another table, the rows (of the original query) double.
      I am joining to company here, where company.id is the unique primary key.

      SELECT *
      FROM
      (
      (
      SELECT 'IWBMARKET-1' trade_origin,
             it.trade_origin trade_origin_it,
             th.id trade_id,
             tl.id trade_line_id,
             tl.trader_id trader_id
        FROM trade_head th
             JOIN trade_line tl ON th.id = tl.trade_id
        LEFT JOIN invoiced_trades_view it ON it.trade_origin = 'IWBMARKET'
                                         AND it.trade_id = th.id
                                         AND it.trade_line_id = tl.id
             JOIN company c ON c.id = tl.company_id
       WHERE th.is_closed = 'Y'
         AND th.is_deleted = 'N'
         AND tl.is_deleted = 'N'
         AND tl.is_billable = 'Y'
         AND it.trade_origin IS NULL
      )
      UNION ALL
      (
      SELECT 'IWBMARKET-2' trade_origin,
             it.trade_origin trade_origin_it,
             th.id trade_id,
             tl.id trade_line_id,
             tl.trader_id trader_id
        FROM trade_head th
             JOIN trade_line tl ON tl.trade_id = th.id
        LEFT JOIN invoiced_trades_view it ON it.trade_origin = 'IWBMARKET'
                                         AND it.trade_id = th.id
                                         AND it.trade_line_id = tl.id
       WHERE th.is_closed = 'Y'
         AND th.is_deleted = 'N'
         AND tl.is_deleted = 'N'
         AND tl.is_billable = 'Y'
         AND it.trade_origin IS NULL
      )
      ) t
      where trader_id = 1488
      ;
       
      +--------------+-----------------+----------+---------------+-----------+
      | trade_origin | trade_origin_it | trade_id | trade_line_id | trader_id |
      +--------------+-----------------+----------+---------------+-----------+
      | IWBMARKET-1  | NULL            |    16123 |         52350 |      1488 |
      | IWBMARKET-1  | NULL            |    16129 |         52370 |      1488 |
      | IWBMARKET-1  | NULL            |    16977 |         55162 |      1488 |
      | IWBMARKET-1  | NULL            |    16984 |         55185 |      1488 |
      | IWBMARKET-2  | NULL            |    16977 |         55162 |      1488 |
      | IWBMARKET-2  | NULL            |    16984 |         55185 |      1488 |
      +--------------+-----------------+----------+---------------+-----------+
      6 rows in set (0.77 sec)

      If you need more information, please let me know. I am sorry that I cannot provide you the full table dumps, as this is production data.

        Attachments

          Activity

            People

            Assignee:
            Unassigned
            Reporter:
            rudy Rudy Metzger
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: