Thanks for clarifying, Jeremy. I like your idea of emulating this behavior, and I think I'd enjoy working on it. I'm not sure I understand what you have in mind by "an additional order term", but if you write a line or two demonstrating how you'd want the API to look, I'll get to work on it this week.
Best, Chris On Fri, Aug 31, 2018 at 3:42 PM Jeremy Evans <[email protected]> wrote: > 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. > -- 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.
