Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

ORDER BY clause is not in SELECT list #25924

Closed
ball-hayden opened this issue Jul 22, 2016 · 6 comments

Comments

Projects
None yet
8 participants
@ball-hayden
Copy link

commented Jul 22, 2016

Steps to reproduce

  1. Create a new Rails 5 Application with two models:
rails new OrderPluckDemo
rails g model Author name:string
rails g model Book name:string author:references rating:integer
  1. Try to retrieve the ids of authors, sorted by the book rating (i.e. Authors with the top rated books first)
Author.includes(:books).order('books.rating').uniq.ids

Expected behavior

An array of ids should be returned, sorted by top rating books.
This happens with Rails 4.2 and Arel 6.0.3

Actual behavior

(under Rails 5.0 and Arel 7.0.0 / 7.1.0)

ActiveRecord::StatementInvalid: Mysql2::Error: Expression #1 of ORDER BY clause is not in SELECT list, references column 'SparkSeat_development.books.rating' which is not in SELECT list; this is incompatible with DISTINCT: SELECT DISTINCT `authors`.`id` FROM `authors` LEFT OUTER JOIN `books` ON `books`.`author_id` = `authors`.`id` ORDER BY books.rating

Other information

Query executed is identical between versions:

SELECT DISTINCT `authors`.`id` FROM `authors` LEFT OUTER JOIN `books` ON `books`.`author_id` = `authors`.`id` ORDER BY books.rating

System configuration

Rails version:
5.0.0

Ruby version:
2.2.4p230

Database:
MySQL 5.7.13-0ubuntu0.16.04.2 (using mysql2 gem)

@kamipo

This comment has been minimized.

Copy link
Member

commented Jul 23, 2016

In MySQL 5.7.5 and up, ONLY_FULL_GROUP_BY affects handling of queries
that use DISTINCT and ORDER BY. It requires the ORDER BY columns in the
select list for distinct queries, and requires that the ORDER BY include
the distinct column.

Related #22241.

And Rails 5.0 keeps the default SQL mode. #24167
If you using MySQL 5.7.13, the default SQL mode includes ONLY_FULL_GROUP_BY.

Originally this query not always return expected sorted results.

Example:

root@localhost [test] > select id, rating from authors;
+------+--------+
| id   | rating |
+------+--------+
|    1 |      4 |
|    1 |      1 |
|    2 |      2 |
|    2 |      3 |
|    3 |      3 |
+------+--------+
5 rows in set (0.00 sec)

root@localhost [test] > select id, rating from authors order by rating;
+------+--------+
| id   | rating |
+------+--------+
|    1 |      1 |
|    2 |      2 |
|    2 |      3 |
|    3 |      3 |
|    1 |      4 |
+------+--------+
5 rows in set (0.00 sec)

root@localhost [test] > select distinct id from authors order by rating;
+------+
| id   |
+------+
|    2 |
|    3 |
|    1 |
+------+
3 rows in set (0.00 sec)

If you want to expect MySQL 5.6's behavior (without ONLY_FULL_GROUP_BY), set sql_mode explicitly.

# database.yml
production:
  adapter: mysql2
  database: foo_prod
  user: foo
  variables:
    sql_mode: TRADITIONAL
@ball-hayden

This comment has been minimized.

Copy link
Author

commented Jul 24, 2016

Ah. I see.
Thank you for your help.
Would it be worth adding something to the upgrade guide, since this seems to be a breaking change between 4.2 and 5.0?

@jaredbeck

This comment has been minimized.

Copy link
Contributor

commented Jul 29, 2016

Thanks @kamipo, sql_mode: TRADITIONAL worked for me.

After I changed the sql_mode in my config, I confirmed the change in the rails console with:

ActiveRecord::Base.connection.execute("show variables like 'sql_mode'").to_a
#=> [["sql_mode", "STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"]]

Note that ONLY_FULL_GROUP_BY, specifically, is not in the list.

PS: ONLY_FULL_GROUP_BY seems like a good idea, and I hope to use it in the future.

@olegantonyan

This comment has been minimized.

Copy link

commented May 9, 2017

Hello
Any news on supporting ONLY_FULL_GROUP_BY?

ochaochaocha3 added a commit to cre-ne-jp/log-archiver that referenced this issue Oct 6, 2018

MySQLのSQLモードをTRADITIONALに変更する
MySQL 5.7.5以降では、SELECT DISTINCTとORDER BYを組み合わせるとき
ORDER BYで指定する列をSELECTにも含めなければならなくなった。
その結果、ログ表示画面で年月の一覧を取得するときにエラーが発生する。

データベースの設定でMySQLのSQLモードをTRADITIONALに設定することで
MySQL 5.6以前と同じ挙動になるようにする。

参考文献:
1. https://blog.kamipo.net/entry/2015/12/14/171838
2. rails/rails#24167
3. rails/rails#25924 (comment)

@fr-shamano fr-shamano referenced this issue Nov 27, 2018

Open

fssss #2

Qambar added a commit to bbc/testmine that referenced this issue Feb 8, 2019

Fix `Order By` clause is not in SELECT list
I couldn't open the page http://192.168.99.100:3002/tests/28383?target=tv-platform-integration-test
and was getting the SQL statement ORDER BY error.

Found the following solution which worked:

See rails/rails#25924

Qambar added a commit to bbc/testmine that referenced this issue Feb 8, 2019

Fix `Order By` clause is not in SELECT list
I couldn't open the page http://192.168.99.100:3002/tests/28383?target=tv-platform-integration-test
and was getting the SQL statement ORDER BY error.

Found the following solution which worked:

See rails/rails#25924
@Backoo

This comment has been minimized.

Copy link

commented Apr 11, 2019

The @kamipo's solution

# database.yml
production:
  ...
  variables:
    sql_mode: TRADITIONAL

worked also for me, but I didn't understand if this is a fix (that will be definitively solved in future) or something else...

BTW By using mysql2 gem, Rails version 5.2.2 and Ruby version 2.4.5, I get the error only in production with MySQL version 14.14 Distrib 5.7.25 for Linux (x86_64). In development I'm using MySQL version 5.7.25-0ubuntu0.18.04.2 (Ubuntu) without problems.

@leonkielstra

This comment has been minimized.

Copy link

commented May 15, 2019

@Backoo

Does the SQL mode of your Ubuntu mysql install include ONLY_FULL_GROUP_BY? This is what was missing on my development install when I tried to reproduce it.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.