Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP

Loading…

Limiting dataset with clobs raises error when using Oracle #724

Closed
sdeming opened this Issue · 2 comments

2 participants

@sdeming

Using the limit method against a dataset that includes clob values raises an exception when running against Oracle.

As an example, assume there is a simple table named "notes" and in this table are three columns: id, title, content; each column is a Numeric, Varchar2, and Clob datatype respectively.

The following code will fail:

db[:notes].select_all.limit(0...10).all

With the following exception:

Sequel::DatabaseError: Java::JavaSql::SQLSyntaxErrorException: ORA-00932: inconsistent datatypes: expected - got CLOB

The reason for this is that in absence of an order, the limit code for Oracle, using the EmulateOffsetWithRowNumber module, imposes a mandatory order by all columns yielding the following SQL:

SELECT * FROM (SELECT "ID", "TITLE", "CONTENT" FROM (SELECT "NOTES".*, ROW_NUMBER() OVER (ORDER BY "ID", "TITLE", "CONTENT") "X_SEQUEL_ROW_NUMBER_X" FROM "NOTES") "T1" WHERE ("X_SEQUEL_ROW_NUMBER_X" > 0) ORDER BY "X_SEQUEL_ROW_NUMBER_X") "T1" WHERE (ROWNUM <= 10)

The problem area is the ORDER BY "ID", "TITLE", "CONTENT" since "CONTENT" cannot be a part of an order by.

The simple workaround is to add an order to the statement:

db[:notes].select_all.order(:id).limit(0...10)

But that isn't always possible. Some of the code I'm working with is doing some pretty gnarly work with putting together very large dynamic queries. Sequel has made that a whole lot easier than trying to generate SQL directly and up to and including version 3.27.0, it worked great. Now I'm trying to get us on the most recent release and this is the last part of that journey.

At this point I'm pretty stuck unless I want to rework how limit() functions for us, but I'd much rather find a more complete solution.

For completeness, here is my stack trace:

SELECT * FROM (SELECT "ID", "TITLE", "CONTENT" FROM (SELECT "NOTES".*, ROW_NUMBER() OVER (ORDER BY "ID", "TITLE", "CONTENT") "X_SEQUEL_ROW_NUMBER_X" FROM "NOTES") "T1" WHERE ("X_SEQUEL_ROW_NUMBER_X" > 0) ORDER BY "X_SEQUEL_ROW_NUMBER_X") "T1" WHERE (ROWNUM <= 10)
Sequel::DatabaseError: Java::JavaSql::SQLSyntaxErrorException: ORA-00932: inconsistent datatypes: expected - got CLOB

  raise_error at C:/Users/Scott/CODE/sequel_test/vendor/jruby/1.9/gems/sequel-4.3.0/lib/sequel/database/misc.rb:402
    statement at C:/Users/Scott/CODE/sequel_test/vendor/jruby/1.9/gems/sequel-4.3.0/lib/sequel/adapters/jdbc.rb:604
      execute at C:/Users/Scott/CODE/sequel_test/vendor/jruby/1.9/gems/sequel-4.3.0/lib/sequel/adapters/jdbc.rb:259
         hold at C:/Users/Scott/CODE/sequel_test/vendor/jruby/1.9/gems/sequel-4.3.0/lib/sequel/connection_pool/threaded.rb:104
  synchronize at C:/Users/Scott/CODE/sequel_test/vendor/jruby/1.9/gems/sequel-4.3.0/lib/sequel/database/connecting.rb:234
      execute at C:/Users/Scott/CODE/sequel_test/vendor/jruby/1.9/gems/sequel-4.3.0/lib/sequel/adapters/jdbc.rb:258
      execute at C:/Users/Scott/CODE/sequel_test/vendor/jruby/1.9/gems/sequel-4.3.0/lib/sequel/dataset/actions.rb:793
   fetch_rows at C:/Users/Scott/CODE/sequel_test/vendor/jruby/1.9/gems/sequel-4.3.0/lib/sequel/adapters/jdbc.rb:671
         each at C:/Users/Scott/CODE/sequel_test/vendor/jruby/1.9/gems/sequel-4.3.0/lib/sequel/dataset/actions.rb:143
          all at C:/Users/Scott/CODE/sequel_test/vendor/jruby/1.9/gems/sequel-4.3.0/lib/sequel/dataset/actions.rb:46
       (root) at clobs_with_limit.rb:10

And I've created a repository for specifically testing these kinds of things myself here:
https://github.com/sdeming/sequel_test

@jeremyevans
Owner

This isn't an issue with the limit emulation, but rather with the generic offset emulation (which is shared by MSSQL, DB2, and Oracle). If you just do limit(10) instead of limit(0...10), an order is not forced.

The Sequel <=3.28.0 code did not work with IN/NOT with subselects with offsets, which is why it was changed (see df3a5a4). However, it looks like instead of using the ROW_NUMBER window function, it may be able to use the ROWNUM expression, which I believe does not require an order. Alternatively, maybe it can leave the subselect ordered and just use ROW_NUMBER without an order (not sure if Oracle supports that, I'd have to test).

I think the reason for the current code is that it is shared by MSSQL, which doesn't allow ordered subselects unless limits are used for the subselect. Maybe it would be better to change the default emulation to not mess with or force an order, and just have specific emulation on MSSQL. It would be best if the generic emulation didn't require an order. I just have to make sure that the result works on MSSQL, DB2, and Oracle.

If you could submit a pull request with a failing oracle adapter test, that would be helpful. I can probably test this on Monday or Tuesday.

@sdeming

Thanks Jeremy for your help here. If there is anything I can add to this please let me know and I'll be happy to do so.

This is the first time I've set up an environment where I could actually run the sequel adapter specs. It wasn't as difficult as I'd imagined!

@jeremyevans jeremyevans closed this issue from a commit
@jeremyevans Make offset emulation on Oracle work when using columns that can't be…
… ordered (Fixes #724)

This changes Oracle from using the generic ROW_NUMBER window
function emulation to using a specific ROWNUM emulation. The
generic ROW_NUMBER window function can add orders on all columns,
and Oracle doesn't allow you to order on some column types.

Fix the oracle spec added to test this, since it tests for
ordered output without specifying an order, and that's not safe,
as without an order specified, the database is free to return
rows in any order.
4a58685
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.