[MDEV-2514] LP:967242 - Wrong result (extra rows, not grouped) with JOIN, AND in ON condition, multi-part key, GROUP BY, OR in WHERE, MyISAM or Aria Created: 2012-03-28  Updated: 2015-02-02  Resolved: 2012-10-04

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Minor
Reporter: Elena Stepanova Assignee: Oleksandr Byelkin
Resolution: Fixed Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug967242.xml    

 Description   

The following query

SELECT t2_1.b
FROM t1 JOIN t2 AS t2_1 JOIN t2 AS t2_2
ON (t2_2.b = t2_1.b ) AND (t2_2.c = t2_1.c )
WHERE
( SELECT COUNT FROM t2 ) IS NOT NULL
OR a = t2_1.c
GROUP BY t2_1.b;

on the test data returns multiple rows for some values of t2_1.b, which should not be happening because of GROUP BY.

bzr version-info
revision-id: <email address hidden>
date: 2012-03-28 13:58:14 +0300
build-date: 2012-03-28 19:36:15 +0400
revno: 3481

Notes:
Could not move ON condition under WHERE, the problem goes away.
In the test data t1 contains one row, could not add more – the problem goes away.
Reproducible with the provided test case on MyISAM or Aria tables, but not InnoDB.
Reproducible with standard optimizer_switch as well as with all OFFs (except for in_to_exists or materialization, as one of them has to be ON to get the query executed).
Reproducible on MariaDB 5.3.5, MariaDB 5.5 revno 3353.
Not reproducible on MariaDB 5.2 revno 3126.
Not reproducible on MySQL 5.1.60, MySQL 5.5 revno 3737, MySQL trunk revno 3706.

EXPLAIN with minimal optimizer switch – all OFFs except for in_to_exists (wrong result):

id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00
1 PRIMARY t2_1 index c c 9 NULL 3 100.00 Using where; Using index
1 PRIMARY t2_2 ref c c 9 test.t2_1.c,test.t2_1.b 2 100.00Using index
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
Warnings:
Note 1003 select `test`.`t2_1`.`b` AS `b` from `test`.`t1` join `test`.`t2` `t2_1` join `test`.`t2` `t2_2` where ((`test`.`t2_2`.`c` = `test`.`t2_1`.`c`) and (`test`.`t2_2`.`b` = `test`.`t2_1`.`b`) and (((select count(0) from `test`.`t2`) is not null) or (`test`.`t2_1`.`c` = 'x'))) group by `test`.`t2_1`.`b`

Minimal optimizer_switch: in_to_exists=on
Full optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on

Test case:

SET optimizer_switch = 'in_to_exists=on';

CREATE TABLE t1 ( a VARCHAR(1) ) ENGINE=MyISAM;
INSERT INTO t1 VALUES ('x');

CREATE TABLE t2 ( b INT, c VARCHAR(1), KEY (c, b) ) ENGINE=MyISAM;
INSERT INTO t2 VALUES
(4, 'd'),(8, 'g'),(3, 'x'),(3, 'f'),
(0, 'p'),(3, 'j'),(8, 'c');

SELECT t2_1.b
FROM t1 JOIN t2 AS t2_1 JOIN t2 AS t2_2
ON (t2_2.b = t2_1.b ) AND (t2_2.c = t2_1.c )
WHERE
( SELECT COUNT FROM t2 ) IS NOT NULL
OR a = t2_1.c
GROUP BY t2_1.b;

  1. End of test case
  1. Expected result:
  2. b
  3. –
  4. 0
  5. 3
  6. 4
  7. 8
  1. Actual result:
  2. b
  3. –
  4. 8
  5. 4
  6. 3
  7. 8
  8. 3
  9. 0
  10. 3

 Comments   
Comment by Elena Stepanova [ 2012-03-28 ]

Re: Wrong result (extra rows, not grouped) with JOIN, AND in ON condition, multi-part key, GROUP BY, subquery and OR in WHERE, MyISAM or Aria
Setting importance to medium for now, due to t1 being 1-row table, looks like an edge case. Please adjust if further analysis shows otherwise.

Comment by Oleksandr Byelkin [ 2012-04-09 ]

Re: Wrong result (extra rows, not grouped) with JOIN, AND in ON condition, multi-part key, GROUP BY, subquery and OR in WHERE, MyISAM or Aria
Subquery is not important:

CREATE TABLE t1 ( a VARCHAR(1) ) ENGINE=MyISAM;
INSERT INTO t1 VALUES ('x');
CREATE TABLE t2 ( b INT, c VARCHAR(1), KEY (c, b) ) ENGINE=MyISAM;
INSERT INTO t2 VALUES
(4, 'd'),(8, 'g'),(3, 'x'),(3, 'f'),
(0, 'p'),(3, 'j'),(8, 'c');
SELECT t2_1.b as zzz
FROM t1 JOIN t2 AS t2_1 JOIN t2 AS t2_2
ON (t2_2.b = t2_1.b ) AND (t2_2.c = t2_1.c )
WHERE
rand() + 1 > 0 OR
a = t2_1.c
GROUP BY zzz;
zzz
8
4
3
8
3
0
3
drop table t1, t2;

Comment by Oleksandr Byelkin [ 2012-04-09 ]

Re: Wrong result (extra rows, not grouped) with JOIN, AND in ON condition, multi-part key, GROUP BY, OR in WHERE, MyISAM or Aria
It is repeatable on 5.1

Comment by Oleksandr Byelkin [ 2012-04-09 ]

Re: Wrong result (extra rows, not grouped) with JOIN, AND in ON condition, multi-part key, GROUP BY, OR in WHERE, MyISAM or Aria
fixed in maria 5.5

Comment by Oleksandr Byelkin [ 2012-04-09 ]

Re: Wrong result (extra rows, not grouped) with JOIN, AND in ON condition, multi-part key, GROUP BY, OR in WHERE, MyISAM or Aria
Ok, the original test suite still repeatable in 5.5 so fix of query with rand should be modified for subselects.

Comment by Oleksandr Byelkin [ 2012-04-29 ]

Re: Wrong result (extra rows, not grouped) with JOIN, AND in ON condition, multi-part key, GROUP BY, OR in WHERE, MyISAM or Aria
It even do not need random function:

SELECT t2_1.b as zzz
FROM t1 JOIN t2 AS t2_1 JOIN t2 AS t2_2
ON (t2_2.b = t2_1.b ) AND (t2_2.c = t2_1.c )
WHERE
t2_1.b + 1 > 0 OR
a = t2_1.c
GROUP BY zzz;
zzz
8
4
3
8
3
0
3

Comment by Oleksandr Byelkin [ 2012-04-29 ]

Re: Wrong result (extra rows, not grouped) with JOIN, AND in ON condition, multi-part key, GROUP BY, OR in WHERE, MyISAM or Aria
All incorrect results has key length in t2_1 is 3 in correct result it is 7 (possible key is the same - c)

Comment by Oleksandr Byelkin [ 2012-04-29 ]

Re: Wrong result (extra rows, not grouped) with JOIN, AND in ON condition, multi-part key, GROUP BY, OR in WHERE, MyISAM or Aria
oops 3 and 7 is estimation of rows

Comment by Oleksandr Byelkin [ 2012-04-29 ]

Re: Wrong result (extra rows, not grouped) with JOIN, AND in ON condition, multi-part key, GROUP BY, OR in WHERE, MyISAM or Aria
Correct execution uses temporary table.

Comment by Oleksandr Byelkin [ 2012-04-29 ]

Re: Wrong result (extra rows, not grouped) with JOIN, AND in ON condition, multi-part key, GROUP BY, OR in WHERE, MyISAM or Aria
It correct case test_if_skip_sort_order() decide to use filesort in incorrect one: "Keep current (ordered) select->quick" (which should not be true).

Comment by Oleksandr Byelkin [ 2012-04-29 ]

Re: Wrong result (extra rows, not grouped) with JOIN, AND in ON condition, multi-part key, GROUP BY, OR in WHERE, MyISAM or Aria
test_if_order_by_key() thinks that part 't2_1.c' (first part) of the index is constant so it could skip it and then part 't2_1.b' (second part) is appropriate for making sorting. But 't2_1.c' is not constant (because condition in WHERE connected with OR).

Comment by Oleksandr Byelkin [ 2012-04-29 ]

Re: Wrong result (extra rows, not grouped) with JOIN, AND in ON condition, multi-part key, GROUP BY, OR in WHERE, MyISAM or Aria
It is "multiple equal('x', `test`.`t2_2`.`c`, `test`.`t2_1`.`c`)" which trigger assigning key part constant 'x' is value of `test`.`t1`.`a`.

Here is visible how we get this triple equality:

WHERE:(original) ((((`test`.`t2_1`.`b` + 1) > 0) or (`test`.`t1`.`a` = `test`.`t2_1`.`c`)) and (`test`.`t2_2`.`b` = `test`.`t2_1`.`b`) and (`test`.`t2_2`.`c` = `test`.`t2_1`.`c`))

WHERE:(after equal_items) ((((`test`.`t2_1`.`b` + 1) > 0) or multiple equal(`test`.`t2_2`.`c`, `test`.`t2_1`.`c`, `test`.`t1`.`a`)) and multiple equal(`test`.`t2_2`.`b`, `test`.`t2_1`.`b`) and multiple equal(`test`.`t2_2`.`c`, `test`.`t2_1`.`c`))

WHERE:(after const change) ((((`test`.`t2_1`.`b` + 1) > 0) or multiple equal(`test`.`t2_2`.`c`, `test`.`t2_1`.`c`, `test`.`t1`.`a`)) and multiple equal(`test`.`t2_2`.`b`, `test`.`t2_1`.`b`) and multiple equal(`test`.`t2_2`.`c`, `test`.`t2_1`.`c`))

WHERE:(after remove) ((((`test`.`t2_1`.`b` + 1) > 0) or multiple equal(`test`.`t2_2`.`c`, `test`.`t2_1`.`c`, `test`.`t1`.`a`)) and multiple equal(`test`.`t2_2`.`b`, `test`.`t2_1`.`b`) and multiple equal(`test`.`t2_2`.`c`, `test`.`t2_1`.`c`))

Comment by Oleksandr Byelkin [ 2012-05-15 ]

Re: Wrong result (extra rows, not grouped) with JOIN, AND in ON condition, multi-part key, GROUP BY, OR in WHERE, MyISAM or Aria
fixed and merged to 5.3 with the original test suite.

Comment by Rasmus Johansson (Inactive) [ 2012-05-15 ]

Launchpad bug id: 967242

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