Status: Closed (View Workflow)
Affects Version/s: 5.3.12, 5.5, 10.1, 10.0
Fix Version/s: 5.5.55
Environment:CentOS Linux on x86_64 CPU
Sprint:5.5.45, Compatibility-18, 5.5.54
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.
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:
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:
The subquery used does appear peculiar to me:
in that it uses a left join and a subsequent filter to verify non-null joined data. A more natural form might be:
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.