MySQL Forums
Forum List  »  Newbie

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

Options: ReplyQuote


Subject
Written By
Posted
October 09, 2018 07:26AM
Re: Reverting SQL Standard to SQL-92
November 15, 2018 08:50AM


Sorry, only registered users may post in this forum.

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.