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

[5 Jan 2006 17:11] Tim Martin
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.
[10 Mar 2006 21:12] Richard Wesley
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.
[10 Mar 2006 21:13] Richard Wesley
We have also observed this on 5.0.15-nt and 5.0.18 under Linux.
[31 Aug 2006 16:26] Igor Babaev
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)

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.050 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.