MySQL Bugs: #43558: failed to detect bad syntax

Bug #43558	failed to detect bad syntax
Submitted:	11 Mar 2009 9:59	Modified:	11 Mar 2009 13:15
Reporter:	Roger U	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S3 (Non-critical)
Version:	5.0.41	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	bad syntax, detect, failed

Description:
select w.id, w.sCode, count(i.id) as NI from webusers as w 
   left join webuseritems as i on w.id = i.WebuserId 
   group by i.webuserid 

this syntax is bad... Even Microsoft Access says  {...] does not include the specified expression 'Id' as part of an aggregate function

however MySQL 5.0.41 runs it and returns a resultset with rows "missing" - missing in that i was expecting something else 

I have since rewritten the query correctly.. and it works!

How to repeat:
create tabes webusers  fields  Id  sCode
                                1   aa
                                2   bb
                                3   cc
                                4   dd
                                5   ee

         webuseritems  fields  Id WebuserId       
                                1   1
                                2   1
                                3   3
                                4   3
                                5   3

the resultset will show webusers 1 & 3 as having webuseritems rows
but only one of webusers 2,4,5 as having NULL matches

Had you tried to use ONLY_FULL_GROUP_BY SQL mode? See http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html for details.

[11 Mar 11:06] Valeriy Kravchuk
Had you tried to use ONLY_FULL_GROUP_BY SQL mode?

i am using it on a shared server (at FastHosts) - though i could try it out locally.  But this isn't the point..  the point is MySQL should either reject the sql as bad,  or produce a complete resultset.  It accepts the sql and produces an incomplete resultset

This is not a bug. Read http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-columns.html also (about columns that are non-constant in the group etc).

Look:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3308 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 25
Server version: 5.0.74-enterprise-gpl-nt-log MySQL Enterprise Server - Pro Editi
on (GPL)

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

mysql> create table webusers (id int, sCode char(10));
Query OK, 0 rows affected (0.39 sec)

mysql> insert into webusers values(1, 'aa'), (2, 'bb'), (3, 'cc'), (4, 'dd'), (5
, 'ee');
Query OK, 5 rows affected (0.13 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> create table webuseritems (id int, WebuserId int);
Query OK, 0 rows affected (0.13 sec)

mysql> insert into webuseritems values( 1,1), (2,1), (3,3), (4,3), (5,3);
Query OK, 5 rows affected (0.06 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select w.id, w.sCode, count(i.id) as NI from webusers as w
    ->    left join webuseritems as i on w.id = i.WebuserId
    ->    group by i.webuserid ;
+------+-------+----+
| id   | sCode | NI |
+------+-------+----+
|    2 | bb    |  0 |
|    1 | aa    |  2 |
|    3 | cc    |  3 |
+------+-------+----+
3 rows in set (0.14 sec)

mysql> set session sql_mode='ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected (0.00 sec)

mysql> select w.id, w.sCode, count(i.id) as NI from webusers as w
    ->    left join webuseritems as i on w.id = i.WebuserId
    ->    group by i.webuserid ;
ERROR 1055 (42000): 'test.w.id' isn't in GROUP BY

Set sql_mode as I told you if you want to prevent "unexpected" results.

ok but...

"Set sql_mode as I told you if you want to prevent "unexpected" results."

may not be a bug - but i do think that anything that can generate "unexpected results"  should not be a default.  That is why the full SQL spec is written the way it is!