[MDEV-6768] Wrong result with aggregate with join with no result set Created: 2014-09-23  Updated: 2023-09-01  Resolved: 2023-05-03

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 5.1.67, 5.2.14, 5.3.12, 5.5.39, 10.0.14, 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10
Fix Version/s: 10.4.31, 10.5.22, 10.6.15, 10.9.8, 10.10.6, 10.11.5

Type: Bug Priority: Critical
Reporter: Marc T. Assignee: Michael Widenius
Resolution: Fixed Votes: 0
Labels: 11.0-sel, upstream

Issue Links:
Problem/Incident
causes MDEV-31962 Latest 10.11.5 major bug with INNER JOIN Closed
causes MDEV-32073 SELECT behavior has changed between 1... Closed
Relates
relates to MDEV-25453 Wrong result upon JOIN with constant ... Open

 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             |
 
+-----------+------------------+



 Comments   
Comment by Elena Stepanova [ 2014-09-24 ]

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.

Comment by Alice Sherepa [ 2022-11-24 ] 

--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;

Comment by Michael Widenius [ 2023-03-31 ]

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.
Comment by Michael Widenius [ 2023-05-02 ]

Backporting patch from 10.5 to 10.4

Comment by Michael Widenius [ 2023-05-03 ]

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.

Comment by Marc T. [ 2023-05-03 ]

Many thanks

Generated at Thu Feb 08 07:14:26 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.