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
-
-
-
- Closed
-
- relates to
-
-
- Open
-
- links to
Activity
--source include/have_innodb.inc
|
|
|
CREATE TABLE _parent ( parent_id int unsigned NOT NULL auto_increment, parent_field varchar(10), |
PRIMARY KEY (parent_id))engine=innodb; |
|
|
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"; |
|
|
drop table _child,_parent; |
The problem here is that item->no_rows_in_result() does not work for Item_field.
There is also several bugs around no_rows_in_result, for example:
- join->no_rows_in_result_called is used but never set
- Item_func::restore_to_before_no_rows_in_result() calls to wrong function.
MDEV-6768 Wrong result with aggregate with join with no result set
When a query does implicit grouping and join operation produces an empty
result set, a NULL-complemented row combination is generated.
However, constant table fields still show non-NULL values.
What happens in the is that end_send_group() is called with a
const row but without any rows matching the WHERE clause.
This last part is shown by 'join->first_record' not being set.
This causes item->no_rows_in_result() to be called for all items to reset
all sum functions to their initial state. However fields are not set
to NULL.
The used fix is to produce NULL-complemented records for constant tables
as well. Also, reset the constant table's records back in case we're
in a subquery which may get re-executed.
An alternative fix would have item->no_rows_in_result() also work
with Item_field objects.
Marc
Thank you for the report.
sanja
The problem is reproducible on InnoDB tables, but not on MyISAM! If you put the provided test case into MTR, make sure it uses InnoDB for the tables.
Please also note that it's an upstream bug; if you think it's appropriate, we can wait and see if it gets fixed in MySQL.