You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
Please answer these questions before submitting your issue. Thanks!
What did you do?
In TiDB:
mysql> create table tbl(a int, b int);
Query OK, 0 rows affected (0.01 sec)
mysql> select t1.a, t1.b, (select max(t2.b) from tbl t2 where t2.a in (select t3.b from tbl t3 where t3.a = t1.a)) as agg_col
-> from tbl t1 WHERE t1.b = 2;
ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'a'; this is incompatible with sql_mode=only_full_group_by
mysql> select @@sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
What did you expect to see?
In MySQL:
mysql> select t1.a, t1.b, (select max(t2.b) from tbl t2 where t2.a in (select t3.b from tbl t3 where t3.a = t1.a)) as agg_col
-> from tbl t1 WHERE t1.b = 2;
Empty set (0.00 sec)
mysql> select @@sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
What did you see instead?
TiDB has inconsistent behavior with MySQL.
What version of TiDB are you using (tidb-server -V or run select tidb_version(); on TiDB)?
Git Commit Hash: 4f232e5c8d3f65c5b21c71761613c06e1bc2538a
Git Branch: master
UTC Build Time: 2019-03-01 06:04:07
GoVersion: go version go1.11.1 darwin/amd64
Race Enabled: false
TiKV Min Version: 2.1.0-alpha.1-ff3dd160846b7d1aed9079c389fc188f7f5ea13e
Check Table Before Drop: false
The text was updated successfully, but these errors were encountered:
Bug Report
Please answer these questions before submitting your issue. Thanks!
In TiDB:
In MySQL:
TiDB has inconsistent behavior with MySQL.
tidb-server -V
or runselect tidb_version();
on TiDB)?The text was updated successfully, but these errors were encountered: