MariaDB

  1. Bug #967242
  2. Comment #2

Comment 2 for bug 967242

Revision history for this message
Oleksandr "Sanja" Byelkin (sanja-byelkin) wrote : 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;