Matching for equality of CLOB fields fails

[akostadinov]

Steps to reproduce

# just a minimal project `rails new --database=oracle
git clone https://github.com/akostadinov/rails-ora-02014.git
cd rails-ora-02014
vi config/database.yml # set your DB connection details
rails c
> a = Article.create!(title: "test", body: "test body")
> Article.where(body: "test body").take

Expected behavior

  Article Load (31.3ms)  SELECT "ARTICLES".* FROM "ARTICLES" WHERE DBMS_LOB.COMPARE("ARTICLES"."BODY", :a1) = 0 FETCH FIRST :a2 ROWS ONLY  [["body", #<OCI8::CLOB:0x0000000004035328>], ["LIMIT", 1]]                       
=>                                                          
#<Article:0x000000000404a188                                
 id: 1,
 title: "test",
 body: "test body",
 created_at: Fri, 07 Jan 2022 16:46:23.902340000 UTC +00:00,
 updated_at: Fri, 07 Jan 2022 16:46:23.902340000 UTC +00:00>

Actual behavior

  Article Load (2.7ms)  SELECT "ARTICLES".* FROM "ARTICLES" WHERE "ARTICLES"."BODY" = :a1 FETCH FIRST :a2 ROWS ONLY  [["body", #<OCI8::CLOB:0x00007fed8cd6fea8>], ["LIMIT", 1]]                                                                 
Traceback (most recent call last):                                              
stmt.c:265:in oci8lib_270.so: ORA-00932: inconsistent datatypes: expected - got CLOB (OCIError)                                                                 
stmt.c:265:in oci8lib_270.so: OCIError: ORA-00932: inconsistent datatypes: expected - got CLOB (ActiveRecord::StatementInvalid)

Possible fix using a function

This is how we monkey patch the issue. I will probably create a pull request later but wanted to dump what I have here first so I don't forget.

    Arel::Visitors::Oracle12.class_eval do # same goes for Oracle
      def visit_Arel_Nodes_Equality(o, collector)
        case (left = o.left)
        when Arel::Attributes::Attribute
          table = left.relation.table_name
          schema_cache = @connection.schema_cache

          return super unless schema_cache.data_source_exists?(table)

          column = schema_cache.columns_hash(table)[left.name.to_s]

          case column.type
          when :text, :binary
            # https://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_lob.htm#i1016668
            # returns 0 when the comparison succeeds
            comparator = Arel::Nodes::NamedFunction.new('DBMS_LOB.COMPARE', [left, o.right])
            collector = visit comparator, collector
            collector << ' = 0'
            collector
          else
            super
          end
        else
          super
        end
      end
    end

System configuration

Oracle Enhanced Adapter version 7.0.0

Rails version: Rails 7.0.1

Ruby version: ruby 2.7.3p183 (2021-04-05 revision 6847ee089d) [x86_64-linux]

Oracle version: 19.3.0