[MDEV-7992] LEFT JOINed result of subquery with LEFT JOIN and WHERE IS NULL filter Returns unexpected result - JIRA

Details

Type: Bug
Status: Stalled
Priority: Major
Resolution: Unresolved
Affects Version/s: 5.3.12, 10.1, 10.0, 5.5
Fix Version/s: 10.1, 10.0, 5.5
Component/s: Data Manipulation - Subquery, Optimizer
Labels:
- innodb
- isnull
- join
- left
- verified
Environment:
CentOS Linux on x86_64 CPU

Sprint:
5.5.45

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.

Gliffy Diagrams

Attachments

Options
- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

Attachments

BugReport.txt
7 kB
2015-04-14 17:10
diff
1 kB
2015-07-30 10:58

Activity

Ascending order - Click to sort in descending order

Hide

Permalink

Elena Stepanova added a comment - 2015-04-15 19:24

Thanks for the report and the test case.
The workaround would be to set optimizer_switch='derived_merge=off'.

Test case (exactly the same as in the attachment, just cleaned up)

CREATE TABLE T1(
  K1 INT PRIMARY KEY,
  Name VARCHAR(15)
);

INSERT INTO T1 VALUES (1,'T1Row1');

CREATE TABLE T2(
  K2 INT PRIMARY KEY,
  K1r INT,
  rowTimestamp DATETIME,
  Event VARCHAR(15)
);

INSERT INTO T2 VALUES
  (1, 1, '2015-04-13 10:42:11' ,'T1Row1Event1'),
  (2, 1, '2015-04-13 10:42:12' ,'T1Row1Event2'),
  (3, 1, '2015-04-13 10:42:12' ,'T1Row1Event3');

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
;


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
;

DROP TABLE T1, T2;

Show

Elena Stepanova added a comment - 2015-04-15 19:24 Thanks for the report and the test case. The workaround would be to set optimizer_switch='derived_merge=off' . Test case (exactly the same as in the attachment, just cleaned up) CREATE TABLE T1( K1 INT PRIMARY KEY, Name VARCHAR (15) ); INSERT INTO T1 VALUES (1,'T1Row1'); CREATE TABLE T2( K2 INT PRIMARY KEY, K1r INT, rowTimestamp DATETIME, Event VARCHAR (15) ); INSERT INTO T2 VALUES (1, 1, '2015-04-13 10:42:11' ,'T1Row1Event1'), (2, 1, '2015-04-13 10:42:12' ,'T1Row1Event2'), (3, 1, '2015-04-13 10:42:12' ,'T1Row1Event3'); 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 ; 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 ; DROP TABLE T1, T2;

8 older comments

Hide

Permalink

Oleksandr Byelkin added a comment - 2015-07-30 11:41

It is probably incorrect choice of reference table for NULL detection (t1i).

Show

Oleksandr Byelkin added a comment - 2015-07-30 11:41 It is probably incorrect choice of reference table for NULL detection (t1i).

Hide

Permalink

Oleksandr Byelkin added a comment - 2015-07-30 18:06

Adding any expression "saves" execution, so it is problem of "second pointer":

SELECT – t2b.K2 IS NULL
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 + 1) IS NULL
;
K1 Name K2 K1r rowTimestamp Event K2B K1rB rowTimestampB EventB
1 T1Row1 3 1 2015-04-13 10:42:12 T1Row1Event3 NULL NULL NULL NULL

Show

Oleksandr Byelkin added a comment - 2015-07-30 18:06 Adding any expression "saves" execution, so it is problem of "second pointer": SELECT – t2b.K2 IS NULL 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 + 1) IS NULL ; K1 Name K2 K1r rowTimestamp Event K2B K1rB rowTimestampB EventB 1 T1Row1 3 1 2015-04-13 10:42:12 T1Row1Event3 NULL NULL NULL NULL

Hide

Permalink

Oleksandr Byelkin added a comment - 2015-07-30 19:29

the reference null table is the same in all cases of Item_direct_view_ref calls

Show

Oleksandr Byelkin added a comment - 2015-07-30 19:29 the reference null table is the same in all cases of Item_direct_view_ref calls

Hide

Permalink

Oleksandr Byelkin added a comment - 2015-08-02 23:36

Execution path (via sub_select()) and expression except obvious OR change looks also the same.

the question is why null_row set...

Show

Oleksandr Byelkin added a comment - 2015-08-02 23:36 Execution path (via sub_select()) and expression except obvious OR change looks also the same. the question is why null_row set...

Hide

Permalink

Oleksandr Byelkin added a comment - 2015-08-04 13:40

outer trigger is switched off so IS_NULL looks like TRUE

Item_func_trig_cond::val_int (this=0x7fffe8033398) at /home/bell/maria/git/server/sql/item_cmpfunc.h:486
(gdb) p *trig_var
$2 = false
(gdb) p dbug_print_item(args[0])
$3 = 0x1518cc0 <dbug_item_print_buf> "trigcond(isnull(`test`.`t2i`.`K2`))"
(gdb)

Show

Oleksandr Byelkin added a comment - 2015-08-04 13:40 outer trigger is switched off so IS_NULL looks like TRUE Item_func_trig_cond::val_int (this=0x7fffe8033398) at /home/bell/maria/git/server/sql/item_cmpfunc.h:486 (gdb) p *trig_var $2 = false (gdb) p dbug_print_item(args [0] ) $3 = 0x1518cc0 <dbug_item_print_buf> "trigcond(isnull(`test`.`t2i`.`K2`))" (gdb)

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

Details

Description

Gliffy Diagrams

Attachments

Attachments

Activity

People

Dates

Agile