Re: Reverting SQL Standard to SQL-92
Posted by:
Arun Kumar
Date: November 15, 2018 08:50AM
mysql> create table NAME( sno int primary key, name varchar(20) );
Query OK, 0 rows affected (0.03 sec)
mysql> create table LOCATION( sno int, location varchar(20) );
Query OK, 0 rows affected (0.02 sec)
mysql> insert into NAME values(1,'foo');
Query OK, 1 row affected (0.01 sec)
mysql> insert into LOCATION values(1,'gooo');
Query OK, 1 row affected (0.01 sec)
mysql> set sql_mode='ansi';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select @@sql_mode;
+--------------------------------------------------------------------------------+
| @@sql_mode |
+--------------------------------------------------------------------------------+
| REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,ANSI |
+--------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT @@sql_mode, a.sno,b.sno,a.name,b.location
-> FROM NAME AS a
-> JOIN LOCATION AS b ON a.sno=b.sno
-> GROUP BY a.sno,b.location ;
+--------------------------------------------------------------------------------+-----+------+------+----------+
| @@sql_mode | sno | sno | name | location |
+--------------------------------------------------------------------------------+-----+------+------+----------+
| REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,ANSI | 1 | 1 | foo | gooo |
+--------------------------------------------------------------------------------+-----+------+------+----------+
1 row in set (0.00 sec)
mysql> SELECT @@sql_mode, a.sno,b.sno,a.name,b.location FROM NAME AS a JOIN LOCATION AS b ON a.sno=b.sno GROUP BY a.sno;
ERROR 1055 (42000): Expression #5 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.b.location' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by