search

Faveod / arel-extensions

Extending Arel
MIT License
142 stars 24 forks source link

arel-extensions clobbers order clauses in sqlite #24

Closed dvandersluis closed 4 years ago

dvandersluis commented 4 years ago

I'm not sure how this has never come up, but it seems like order clauses are clobbered when arel-extensions is activated. I've created a test that replicates this.

This behaviour seems limited to having arel-extensions along with sqlite. If you remove gem "arel_extensions", "2.0.8" from the test, or use mysql instead of sqlite, the test passes.

I've run this on ruby 2.6. Rails 6 with every 2.x version fails. I've also tried using rails 5 and arel_extensions 1.2.12 and it fails there as well.

dvandersluis commented 4 years ago

The strange thing is User.order(:score).to_sql does in fact show the correct statement, but when the query executes, the order clause is gone.

dvandersluis commented 4 years ago

Seems to be related to https://github.com/Faveod/arel-extensions/blob/ad9e1be64c055e20b986c0e5764abc968f0c3f19/lib/arel_extensions/visitors/sqlite.rb#L370-L373

Changing the query to User.order(:score).limit(5) will pass the test. I'm not exactly sure I'm understanding why the orders are cleared here?

jdelporte commented 4 years ago

Hi Daniel, Thanks you for this report. This code has been added to remove order in subqueries when there was no limit. Otherwise the query breaks in most DBMS. In other DBMS this code is triggered only on subqueries but apparently not in Sqlite visitor (maybe because of the adapter) in triggered always. Next gem will correct that.