Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.3.12, 5.5, 10.0, 10.1
-
CentOS Linux on x86_64 CPU
-
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
Issue Links
- relates to
-
-
- Closed
-