Details
Description
Hi,
I reported a bug on MySQL and MariaDB 5.5.39 is affected.
http://bugs.mysql.com/bug.php?id=73946
>>
When you join 2 tables with no resultset and put a min/max on child table, in some case Parent's fields is not null.
In the test-case bellow, PARENT_ID must be null, not "1"
Test case :
use test; |
|
drop table if exists _Parent; |
create table _Parent |
(
|
PARENT_ID int(10) unsigned NOT NULL AUTO_INCREMENT, |
PARENT_FIELD VARCHAR(10), |
PRIMARY KEY (PARENT_ID) |
) engine=innodb;
|
|
drop table if exists _Child; |
create table _Child |
(
|
CHILD_ID INT NOT NULL AUTO_INCREMENT, |
PARENT_ID INT NOT NULL, |
CHILD_FIELD varchar(10), |
PRIMARY KEY (CHILD_ID) |
) engine=innodb;
|
|
INSERT INTO _Parent (PARENT_FIELD) |
SELECT 'AAAA'; |
|
INSERT INTO _Child (PARENT_ID, CHILD_FIELD) |
SELECT 1, 'BBBB'; |
|
select |
_Parent.PARENT_ID,
|
min(CHILD_FIELD) |
from _Parent straight_join _Child |
where _Parent.PARENT_ID = 1 |
and _Parent.PARENT_ID = _Child.PARENT_ID |
and _Child.CHILD_FIELD = "ZZZZ"; |
+-----------+------------------+
|
| PARENT_ID | min(CHILD_FIELD) |
|
+-----------+------------------+
|
| 1 | NULL |
|
+-----------+------------------+
|
Attachments
Issue Links
- causes
-
MDEV-31962 Latest 10.11.5 major bug with INNER JOIN
- Closed
-
MDEV-32073 SELECT behavior has changed between 10.11.4 and 10.11.5
- Closed
- relates to
-
MDEV-25453 Wrong result upon JOIN with constant CSV table
- Open
- links to