On Friday, August 31, 2018 at 11:50:40 AM UTC-7, [email protected] wrote: > > Hi Jeremy, > > Thanks for such an amazing library. Sequel and Roda are two huge reasons I > keep writing web apps in Ruby. > > The documentation re: Sequel.desc and Sequel.asc -- > https://sequel.jeremyevans.net/rdoc/classes/Sequel/SQL/Builders.html#method-i-desc > > -- doesn't mention those methods being adapter-specific, but calling them > reliably raises an exception in SQLite when using the `:nulls` option. > Here's a quick example: > > ``` > # sqlite_nulls_example.rb > > require 'sequel' > DB = Sequel.connect 'sqlite:/' > DB.create_table(:examples) { primary_key :id; String :name } > DB[:examples].import([:name], [['this'], ['that'], [nil]]) > DB[:examples].order(Sequel.asc(:name, nulls: :last)).all > > ``` > > Running that script on my Mac (v 10.13.6) produces the following traceback: > > Traceback (most recent call last): > 16: from > /Users/chrisfrank/.rbenv/versions/2.5.0/lib/ruby/gems/2.5.0/gems/sequel-5.5.0/lib/sequel/dataset/actions.rb:1002:in > > `_all' > 15: from > /Users/chrisfrank/.rbenv/versions/2.5.0/lib/ruby/gems/2.5.0/gems/sequel-5.5.0/lib/sequel/dataset/actions.rb:51:in > > `block in all' > 14: from > /Users/chrisfrank/.rbenv/versions/2.5.0/lib/ruby/gems/2.5.0/gems/sequel-5.5.0/lib/sequel/dataset/actions.rb:151:in > > `each' > 13: from > /Users/chrisfrank/.rbenv/versions/2.5.0/lib/ruby/gems/2.5.0/gems/sequel-5.5.0/lib/sequel/adapters/sqlite.rb:320:in > > `fetch_rows' > 12: from > /Users/chrisfrank/.rbenv/versions/2.5.0/lib/ruby/gems/2.5.0/gems/sequel-5.5.0/lib/sequel/dataset/actions.rb:1085:in > > `execute' > 11: from > /Users/chrisfrank/.rbenv/versions/2.5.0/lib/ruby/gems/2.5.0/gems/sequel-5.5.0/lib/sequel/adapters/sqlite.rb:126:in > > `execute' > 10: from > /Users/chrisfrank/.rbenv/versions/2.5.0/lib/ruby/gems/2.5.0/gems/sequel-5.5.0/lib/sequel/adapters/sqlite.rb:180:in > > `_execute' > 9: from > /Users/chrisfrank/.rbenv/versions/2.5.0/lib/ruby/gems/2.5.0/gems/sequel-5.5.0/lib/sequel/database/connecting.rb:269:in > > `synchronize' > 8: from > /Users/chrisfrank/.rbenv/versions/2.5.0/lib/ruby/gems/2.5.0/gems/sequel-5.5.0/lib/sequel/connection_pool/threaded.rb:91:in > > `hold' > 7: from > /Users/chrisfrank/.rbenv/versions/2.5.0/lib/ruby/gems/2.5.0/gems/sequel-5.5.0/lib/sequel/adapters/sqlite.rb:187:in > > `block in _execute' > 6: from > /Users/chrisfrank/.rbenv/versions/2.5.0/lib/ruby/gems/2.5.0/gems/sequel-5.5.0/lib/sequel/database/logging.rb:38:in > > `log_connection_yield' > 5: from > /Users/chrisfrank/.rbenv/versions/2.5.0/lib/ruby/gems/2.5.0/gems/sequel-5.5.0/lib/sequel/adapters/sqlite.rb:187:in > > `block (2 levels) in _execute' > 4: from > /Users/chrisfrank/.rbenv/versions/2.5.0/lib/ruby/gems/2.5.0/gems/sqlite3-1.3.13/lib/sqlite3/database.rb:264:in > > `query' > 3: from > /Users/chrisfrank/.rbenv/versions/2.5.0/lib/ruby/gems/2.5.0/gems/sqlite3-1.3.13/lib/sqlite3/database.rb:91:in > > `prepare' > 2: from > /Users/chrisfrank/.rbenv/versions/2.5.0/lib/ruby/gems/2.5.0/gems/sqlite3-1.3.13/lib/sqlite3/database.rb:91:in > > `new' > 1: from > /Users/chrisfrank/.rbenv/versions/2.5.0/lib/ruby/gems/2.5.0/gems/sqlite3-1.3.13/lib/sqlite3/database.rb:91:in > > `initialize' > Sequel::DatabaseError (SQLite3::SQLException: near "NULLS": syntax error) > > Is this the expected behavior under SQLite? If so, would you accept a PR > that amends the documentation to say so? >
Yes, this is expected, as SQLite doesn't support the concept (see https://www.sqlite.org/syntax/ordering-term.html). I don't think a PR is needed. In general with Sequel, if you attempt to use SQL features the database doesn't support, an exception will be raised. Updating the documentation to state that explicitly in every case where it could apply would probably make the documentation harder to use. An alternative approach would be to emulate NULLS FIRST/LAST behavior on databases that do not natively support it. That could probably be done by adding an additional order term for the null handling before the order term using :nulls. I'd accept a PR for that with the usual provisos (reasonable implementation, specs, and integration tests). Thanks, Jeremy -- You received this message because you are subscribed to the Google Groups "sequel-talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sequel-talk. For more options, visit https://groups.google.com/d/optout.
