[MDEV-3875] Wrong result (missing row) on a DISTINCT query with the same subquery in the SELECT list and GROUP BY Created: 2012-11-21  Updated: 2013-01-27  Resolved: 2013-01-27

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.0.0, 5.5.28, 5.3.10, 5.2.12, 5.1.62
Fix Version/s: 10.0.1, 5.5.29, 5.3.12

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Sergei Golubchik
Resolution: Fixed Votes: 0
Labels: upstream

Issue Links:
Relates

 Description   

The following test case

CREATE TABLE t1 (a INT) ENGINE=MyISAM;
 
INSERT INTO t1 VALUES (0),(7);
 
 
 
CREATE TABLE t2 (b INT) ENGINE=MyISAM;
 
INSERT INTO t2 VALUES (7),(0),(3);
 
 
 
CREATE ALGORITHM=TEMPTABLE VIEW v AS
 
SELECT DISTINCT    
( SELECT MAX(a) FROM t1 WHERE alias.b = a ) AS field1 
FROM t2 AS alias GROUP BY field1;
 
 
 
SELECT * FROM v;

returns two rows only:

field1
 
0
 
7

while the same query without the view returns 3 rows:

field1
 
NULL
 
0
 
7

EXPLAIN with the default optimizer_switch:

EXPLAIN EXTENDED
 
SELECT * FROM v;
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 
1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	3	100.00	
2	DERIVED	alias	ALL	NULL	NULL	NULL	NULL	3	100.00	Using temporary; Using filesort
 
4	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
 
3	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
 
Warnings:
 
Note	1276	Field or reference 'test.alias.b' of SELECT #3 was resolved in SELECT #2
 
Note	1276	Field or reference 'test.alias.b' of SELECT #4 was resolved in SELECT #2
 
Note	1003	select `v`.`field1` AS `field1` from `test`.`v`
 

branch: maria/5.5
 
bzr version-info
 
revision-id: wlad@montyprogram.com-20121120142439-zvx42vxhc8lurmnv
 
date: 2012-11-20 15:24:39 +0100
 
revno: 3576

Reproducible with the default optimizer_switch as well as with all OFF values, except for in_to_exists or materialization that have to be on to run the query.

Also reproducible on all current versions of MariaDB, and on MySQL 5.1 and 5.5; but not reproducible on MySQL 5.6 revno 4458.

 Comments   
Comment by Oleksandr Byelkin [ 2012-11-29 ]

The suspiciouse thing is DEPENDENT SUBQUERY #4 because here is only one subquery and it should be (and is) DEPENDENT SUBQUERY #3

Comment by Oleksandr Byelkin [ 2012-11-30 ]

The materialized temporary table of the view gets only two rows.

Comment by Oleksandr Byelkin [ 2013-01-23 ]

2 subqueries could be explained by show create view:
show create view v;
View Create View character_set_client collation_connection
v CREATE ALGORITHM=TEMPTABLE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select distinct (select max(`t1`.`a`) from `t1` where (`alias`.`b` = `t1`.`a`)) AS `field1` from `t2` `alias` group by (select max(`t1`.`a`) from `t1` where (`alias`.`b` = `t1`.`a`)) latin1 latin1_swedish_ci

Comment by Oleksandr Byelkin [ 2013-01-23 ]

The problem is not connected to view, equivalent query also return wrong result:
select distinct (select max(`t1`.`a`) from `t1` where (`alias`.`b` =
`t1`.`a`)) AS `field1` from `t2` `alias` group by (select max(`t1`.`a`) from
`t1` where (`alias`.`b` = `t1`.`a`));
field1
0
7

Comment by Oleksandr Byelkin [ 2013-01-23 ]

Fast workaround will be print names in ORDER BY/GROUP BY lists if they are present, but it will not fix the problem above.

Comment by Oleksandr Byelkin [ 2013-01-23 ]

A bit better formatted query for new test suite:

select distinct
(select max(a) from t1 where (alias.b = a)) AS field1
from t2 as alias
group by
(select max(a) from t1 where (alias.b = a));

Comment by Oleksandr Byelkin [ 2013-01-23 ]

the last variant is still reproducible on MySQL 5.6 (when view is working OK) which makes me thing that in 5.6 put just workaround

Comment by Oleksandr Byelkin [ 2013-01-23 ]

In MySQL they solved the problem with Item::print_for_order. It makes views a bit more optimized, but does not solve the problem in general.

Comment by Oleksandr Byelkin [ 2013-01-23 ]

As Serg mentioned it could be http://bugs.mysql.com/bug.php?id=66896

Comment by Oleksandr Byelkin [ 2013-01-23 ]

remove_duplicates (sql_select.cc) do not take into account null_ptr of fields, it uses for comparison only fields data without NULL flag.

Comment by Oleksandr Byelkin [ 2013-01-24 ]

Distinct index of temporary table to implement DISTINCT clause used only in case of absence of GROUP BY clause.

Comment by Oleksandr Byelkin [ 2013-01-24 ]

create table t1(i int, g int);

insert into t1
values (null, 1), (0, 2);

select distinct i from t1 group by g;

drop table t1;

Comment by Sergei Golubchik [ 2013-01-27 ]

pushed in 5.3

Generated at Thu Feb 08 06:51:57 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.