MySQL Bugs: #22228: Group by with join, view and concat gives wrong answer

Bug #22228	Group by with join, view and concat gives wrong answer
Submitted:	11 Sep 2006 5:23	Modified:	14 Sep 2006 12:28
Reporter:	Jan Lindström	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.x	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
If I have groub by with join, view and concat I would expect to get

+---+------+------+
| a | b    | c    |
+---+------+------+
| 2 | B    | B    |
| 3 | C    | C    |
| 5 | E    | E    |
+---+------+------+

Instead I will get

+---+------+------+
| a | b    | c    |
+---+------+------+
| 2 | C    | B    |
| 3 | E    | C    |
| 5 | E    | E    |
+---+------+------+

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.24a-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop view IF EXISTS v1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DROP TABLE IF EXISTS t1, t2;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql>
mysql> CREATE TABLE t1 (a int, b char(1), primary key (a)) engine=innodb;
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO t1 VALUES (1,'A'),(2,'B'),(3,'C'),(4,'D'),(5, 'E'),
    -> (11,'A'),(12,'B'),(13,'C'),(14,'D'),(15, 'E'),
    -> (21,'A'),(22,'B'),(23,'C'),(24,'D'),(25, 'E'),
    -> (31,'A'),(32,'B'),(33,'C'),(34,'D'),(35, 'E'),
    -> (41,'A'),(42,'B'),(43,'C'),(44,'D'),(45, 'E'),
    -> (51,'A'),(52,'B'),(53,'C'),(54,'D'),(55, 'E'),
    -> (61,'A'),(62,'B'),(63,'C'),(64,'D'),(65, 'E'),
    -> (71,'A'),(72,'B'),(73,'C'),(74,'D'),(75, 'E'),
    -> (81,'A'),(82,'B'),(83,'C'),(84,'D'),(85, 'E'),
    -> (91,'A'),(92,'B'),(93,'C'),(94,'D'),(95, 'E'),
    -> (100,'A'),(102,'B'),(103,'C'),(104,'D'),(105, 'E');
Query OK, 55 rows affected (0.04 sec)
Records: 55  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE t2 (a int, b int, key b (b)) engine=innodb;
Query OK, 0 rows affected (0.10 sec)

mysql> INSERT INTO t2 VALUES (0,0),(0,3),(0,2),(0,3),(2,3),(0,5);
Query OK, 6 rows affected (0.03 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> CREATE VIEW v1 AS select t1.a, concat(t1.b,'') AS b, t1.b as c from t1;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT v1.a, v1.b, v1.c from t2, v1 where t2.b=v1.a GROUP BY t2.b;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 2 | C    | B    |
| 3 | E    | C    |
| 5 | E    | E    |
+---+------+------+
3 rows in set (0.00 sec)

mysql>
mysql> drop view IF EXISTS v1;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE IF EXISTS t1, t2;
Query OK, 0 rows affected (0.03 sec)

mysql>
mysql> CREATE TABLE t1 (a int, b char(1), primary key (a)) engine=myisam;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO t1 VALUES (1,'A'),(2,'B'),(3,'C'),(4,'D'),(5, 'E'),
    -> (11,'A'),(12,'B'),(13,'C'),(14,'D'),(15, 'E'),
    -> (21,'A'),(22,'B'),(23,'C'),(24,'D'),(25, 'E'),
    -> (31,'A'),(32,'B'),(33,'C'),(34,'D'),(35, 'E'),
    -> (41,'A'),(42,'B'),(43,'C'),(44,'D'),(45, 'E'),
    -> (51,'A'),(52,'B'),(53,'C'),(54,'D'),(55, 'E'),
    -> (61,'A'),(62,'B'),(63,'C'),(64,'D'),(65, 'E'),
    -> (71,'A'),(72,'B'),(73,'C'),(74,'D'),(75, 'E'),
    -> (81,'A'),(82,'B'),(83,'C'),(84,'D'),(85, 'E'),
    -> (91,'A'),(92,'B'),(93,'C'),(94,'D'),(95, 'E'),
    -> (100,'A'),(102,'B'),(103,'C'),(104,'D'),(105, 'E');
Query OK, 55 rows affected (0.00 sec)
Records: 55  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE t2 (a int, b int, key b (b)) engine=myisam;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t2 VALUES (0,0),(0,3),(0,2),(0,3),(2,3),(0,5);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> CREATE VIEW v1 AS select t1.a, concat(t1.b,'') AS b, t1.b as c from t1;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT v1.a, v1.b, v1.c from t2, v1 where t2.b=v1.a GROUP BY t2.b;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 2 | C    | B    |
| 3 | E    | C    |
| 5 | E    | E    |
+---+------+------+
3 rows in set (0.00 sec)

mysql> explain  SELECT v1.a, v1.b, v1.c from t2, v1 where t2.b=v1.a GROUP BY t2.b;
+----+-------------+-------+--------+---------------+---------+---------+-----------+------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref       | rows | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+-----------+------+-------------+
|  1 | PRIMARY     | t2    | index  | b             | b       | 5       | NULL      |    6 | Using index |
|  1 | PRIMARY     | t1    | eq_ref | PRIMARY       | PRIMARY | 4       | test.t2.b |    1 |             |
+----+-------------+-------+--------+---------------+---------+---------+-----------+------+-------------+

Note that if you remove some rows you get correct output:

mysql> delete from t1 where a between 11 and 105;
Query OK, 50 rows affected (0.00 sec)

mysql> SELECT v1.a, v1.b, v1.c from t2, v1 where t2.b=v1.a GROUP BY t2.b;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 2 | B    | B    |
| 3 | C    | C    |
| 5 | E    | E    |
+---+------+------+
3 rows in set (0.01 sec)

mysql> explain SELECT v1.a, v1.b, v1.c from t2, v1 where t2.b=v1.a GROUP BY t2.b;
+----+-------------+-------+------+---------------+------+---------+-----------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref       | rows | Extra   |
+----+-------------+-------+------+---------------+------+---------+-----------+------+---------------------------------+
|  1 | PRIMARY     | t1    | ALL  | PRIMARY       | NULL | NULL    | NULL      |    5 | Using temporary; Using filesort |
|  1 | PRIMARY     | t2    | ref  | b             | b    | 5       | test.t1.a |    2 | Using where; Using index   |
+----+-------------+-------+------+---------------+------+---------+-----------+------+---------------------------------+
2 rows in set (0.01 sec)

Difference seems to be in the join order.

How to repeat:
drop view IF EXISTS v1;
DROP TABLE IF EXISTS t1, t2; 

CREATE TABLE t1 (a int, b char(1), primary key (a)) engine=innodb;
INSERT INTO t1 VALUES (1,'A'),(2,'B'),(3,'C'),(4,'D'),(5, 'E'),
(11,'A'),(12,'B'),(13,'C'),(14,'D'),(15, 'E'),
(21,'A'),(22,'B'),(23,'C'),(24,'D'),(25, 'E'),
(31,'A'),(32,'B'),(33,'C'),(34,'D'),(35, 'E'),
(41,'A'),(42,'B'),(43,'C'),(44,'D'),(45, 'E'),
(51,'A'),(52,'B'),(53,'C'),(54,'D'),(55, 'E'),
(61,'A'),(62,'B'),(63,'C'),(64,'D'),(65, 'E'),
(71,'A'),(72,'B'),(73,'C'),(74,'D'),(75, 'E'),
(81,'A'),(82,'B'),(83,'C'),(84,'D'),(85, 'E'),
(91,'A'),(92,'B'),(93,'C'),(94,'D'),(95, 'E'),
(100,'A'),(102,'B'),(103,'C'),(104,'D'),(105, 'E');
CREATE TABLE t2 (a int, b int, key b (b)) engine=innodb;
INSERT INTO t2 VALUES (0,0),(0,3),(0,2),(0,3),(2,3),(0,5);
CREATE VIEW v1 AS select t1.a, concat(t1.b,'') AS b, t1.b as c from t1;
SELECT v1.a, v1.b, v1.c from t2, v1 where t2.b=v1.a GROUP BY t2.b;

drop view IF EXISTS v1;
DROP TABLE IF EXISTS t1, t2; 

CREATE TABLE t1 (a int, b char(1), primary key (a)) engine=myisam;
INSERT INTO t1 VALUES (1,'A'),(2,'B'),(3,'C'),(4,'D'),(5, 'E'),
(11,'A'),(12,'B'),(13,'C'),(14,'D'),(15, 'E'),
(21,'A'),(22,'B'),(23,'C'),(24,'D'),(25, 'E'),
(31,'A'),(32,'B'),(33,'C'),(34,'D'),(35, 'E'),
(41,'A'),(42,'B'),(43,'C'),(44,'D'),(45, 'E'),
(51,'A'),(52,'B'),(53,'C'),(54,'D'),(55, 'E'),
(61,'A'),(62,'B'),(63,'C'),(64,'D'),(65, 'E'),
(71,'A'),(72,'B'),(73,'C'),(74,'D'),(75, 'E'),
(81,'A'),(82,'B'),(83,'C'),(84,'D'),(85, 'E'),
(91,'A'),(92,'B'),(93,'C'),(94,'D'),(95, 'E'),
(100,'A'),(102,'B'),(103,'C'),(104,'D'),(105, 'E');
CREATE TABLE t2 (a int, b int, key b (b)) engine=myisam;
INSERT INTO t2 VALUES (0,0),(0,3),(0,2),(0,3),(2,3),(0,5);
CREATE VIEW v1 AS select t1.a, concat(t1.b,'') AS b, t1.b as c from t1;
SELECT v1.a, v1.b, v1.c from t2, v1 where t2.b=v1.a GROUP BY t2.b;

Thank you for the report, but I can not repeat it using last BK sources:

-- <skip code to fill tables and create view>

mysql> SELECT v1.a, v1.b, v1.c from t2, v1 where t2.b=v1.a GROUP BY t2.b;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 2 | B    | B    |
| 3 | C    | C    |
| 5 | E    | E    |
+---+------+------+
3 rows in set (0.00 sec)

This bug is still on 5.0.24a i.e. will it be fixed on 5.0.25 ?

Yes, it should.

If necessary, you can access the source repository and build the latest available version. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html