MySQL Bugs: #16232: GROUP BY on a view with a WHERE restriction gives incorrect results

Bug #16232	GROUP BY on a view with a WHERE restriction gives incorrect results
Submitted:	5 Jan 2006 17:11	Modified:	31 Aug 2006 16:26
Reporter:	Tim Martin	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S3 (Non-critical)
Version:	5.0.17/5.0.19 BK	OS:	Linux (Linux 2.6)
Assigned to:	Igor Babaev	CPU Architecture:	Any

Description:
I have a view on a table, details below. If I query the view with 

SELECT * FROM myview;

I get
a  b     new_c
1  12345  197530
1  23456  175308
1  34567  153086

as expected.

If I change the query to 

SELECT * FROM myview WHERE a=1 GROUP BY b;

I would expect it not to affect the output (since b is a unique column, and a=1 for all rows anyway), but in fact I get:

a   b        new_c
1  12345  175308
1  23456  153086
1  34567  153086

How to repeat:
-- Schema ----------------

CREATE TABLE mytable
(
	a INT NOT NULL,
	b INT NOT NULL,
	c INT NOT NULL,

	PRIMARY KEY (a, b)	
);

CREATE VIEW myview AS
   SELECT mytable.a,
	mytable.b,

	(mytable.c * 2)
           AS new_c
    FROM mytable;

INSERT INTO mytable (a, b, c) 
VALUES (1, 12345, 98765),
(1, 23456, 87654),
(1, 34567, 76543);

-- Queries: -------------
SELECT * FROM myview;
SELECT * FROM myview WHERE a=1 GROUP BY b;

Suggested fix:
No fix found.

Querying against a table with the same content as the view works as expected, as does removing the calculation from the view (i.e. just CREATE VIEW AS SELECT a, b, c FROM mytable) - presumably this is optimised out.

We have a variation of this, but it looks more complex.  We did notice that changing the where clause to a range (i.e. where a >= 1 and a <= 1) causes the query to work correctly.

We have also observed this on 5.0.15-nt and 5.0.18 under Linux.

I can't reapeat it with the current bk tree:

mysql> SELECT VERSION();
+--------------+
| VERSION()    |
+--------------+
| 5.0.25-debug |
+--------------+
1 row in set (0.01 sec)

mysql> SELECT * FROM myview WHERE a=1 GROUP BY b;
+---+-------+--------+
| a | b     | new_c  |
+---+-------+--------+
| 1 | 12345 | 197530 |
| 1 | 23456 | 175308 |
| 1 | 34567 | 153086 |
+---+-------+--------+
3 rows in set (0.00 sec)