MySQL Bugs: #74980: "where" condition after "group by" statement does not work/give any warnings

Bug #74980	"where" condition after "group by" statement does not work/give any warnings
Submitted:	24 Nov 2014 13:01	Modified:	9 Jan 2015 6:54
Reporter:	isa ölmez	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.5, 5.6	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
select * from mytable where id is not null group by id and id > 100;

This query does not give any warning and it does not apply "and id > 100" part of the query. This is somewhat misleading.

How to repeat:
Create a table with 1 column and populate. Adjust second condition so that it should filter out some rows. However you will see that it will not filter out these rows. 

select * from mytable where id is not null group by id and id > 100;

I tried a similar query on 5.6.12 (our live server) 

SELECT * FROM `order_details` WHERE order_id IS NOT NULL GROUP BY order_id AND order_id = 44778; 

With this statement I get 2 rows returned with order_id "1" and "44778" respectively. Looks weird!

-- Peter
-- not a MySQL/Oracle person

It looks like it gets parsed and executed as 

SELECT * FROM `order_details` WHERE (order_id IS NOT NULL GROUP BY order_id) AND order_id = 44778;

Please ignore my last comment.

According to all SQL standards from SQL-99 to SQL-2011, grouping elements can be only columns and NEVER expressions. There are possibilities for other types of grouping elements, but all of these have some special keywords that further define grouping.

Expressions, such as the ones found in this bug report, are not allowed, so the server must return the error.

Hence, this is a fully verified bug.

[29 Dec 2014 0:33] Chaithra Gopalareddy

The above mentioned behavior is a well documented feature in mysql

12.18.3 MySQL Handling of GROUP BY
 Standard SQL does not permit expressions in GROUP BY clauses, so a statement
such as this is invalid:

SELECT id, FLOOR(value/100)
  FROM tbl_name
  GROUP BY id, FLOOR(value/100);

MySQL extends standard SQL to permit expressions in GROUP BY clauses and
considers the preceding statement valid.

Standard SQL also does not permit aliases in GROUP BY clauses. MySQL extends
standard SQL to permit aliases, so another way to write the query is as
follows:

SELECT id, FLOOR(value/100) AS val
  FROM tbl_name
  GROUP BY id, val;

So w.r.t to the bug raised it becomes that when query has group by of the
form group by a and a >100, mysql treats it as a boolean expression and
returns one group which satisfies the condition and another group that does
not satisfy the condition. Of course this is an invalid query in
ONLY_FULL_GROUP_BY mode unless aggregated.
Consider the following example.

mysql> select * from t1;
+------+------+
| i    | j    |
+------+------+
|    1 |    2 |
|    1 |    3 |
|    1 |    4 |
|    5 |    4 |
|    5 |    3 |
|    5 |    2 |
| NULL | NULL |
+------+------+

mysql> select j from t1 group by j;
+------+
| j    |
+------+
| NULL |
|    2 |
|    3 |
|    4 |
+------+

mysql> select j from t1 group by j and j > 3;
+------+
| j    |
+------+
| NULL |
|    2 |
|    4 |
+------+
3 rows in set (0.00 sec)

For the above query, we have 2 in the output because, the boolean operation 2
and 2> 3 would result in false which is a group with a value of 0, similarly
for 3 and 3>3. But only one tuple shows up because the first row is always
given out when not in ONLY_FULL_GROUP_BY.
For j=4, 4 and  4> 3 becomes true and hence a different group and one group
for NULL.

mysql> select sum(j) from t1 group by j and j > 3;
+--------+
| sum(j) |
+--------+
|   NULL |
|     10 |
|      8 |
+--------+
3 rows in set (0.00 sec)

We can see it more clearly in the above query. When aggregated, the two value
sets 2 and 3 with two rows each have been aggregated to 10 and value set 4
has been aggregated to 8.

So according to us this is not a bug.