7.1 update: strange serialize behavior

[Bjoernsen]

Currently, I try to update my app from rails 7.0 to 7.1.

One thing that I recognized it the behavior of searching for DB entries where serialize is used for a column to store a Hash

Let's say, I have the model:

class SomeModel < ApplicationRecord
  serialize :something, type: Hash, coder: YAML
end

I would like to get all entries where something is empty/nil/null/...

Using Rails 7.0:

SomeModel.where('something is NULL').count
# --> "SELECT \"SOME_MODELS\".* FROM \"SOME_MODELS\" WHERE (something is NULL)"
# result is 100

SomeModel.where(something: nil).count
# --> "SELECT \"SOME_MODELS\".* FROM \"SOME_MODELS\" WHERE \"SOME_MODELS\".\"SOMETHING\" IS NULL"
# result is 100

SomeModel.where(something: {}).count
# --> "SELECT \"SOME_MODELS\".* FROM \"SOME_MODELS\" WHERE \"SOME_MODELS\".\"SOMETHING\" IS NULL"
# result is 100

I always get the same result.

When I do the same with Rails 7.1

SomeModel.where('something is NULL').count
# --> "SELECT \"SOME_MODELS\".* FROM \"SOME_MODELS\" WHERE (something is NULL)"
# result is 100

SomeModel.where(something: nil).count
# -->"SELECT \"SOME_MODELS\".* FROM \"SOME_MODELS\" WHERE DBMS_LOB.COMPARE(\"SOME_MODELS\".\"SOMETHING\", NULL) = 0"
# result is 0

SomeModel.where(something: {}).count
# -->"SELECT \"SOME_MODELS\".* FROM \"SOME_MODELS\" WHERE DBMS_LOB.COMPARE(\"SOME_MODELS\".\"SOMETHING\", NULL) = 0"
# result is 0

I only get the correct result for SomeModel.where('something is NULL').count. The other queries do not work.

Is this a new Rails 7.1 behavior, or is this related to the Oracle adapter? I would not be a fan of switching from something: nil to 'something is NULL' because I would need an additional where if I would further filter the entries, and rubocop would complain anyway (Use where(something: nil) instead of manually constructing SQL. (convention:Rails/WhereEquals)).

System configuration

Rails version: 7.1.4

Oracle enhanced adapter version: 7.1.0

Ruby version: 3.3.4

Oracle Database version: 19.22 (19c)

[brianthoman]

I don't think this is strictly related to serialization. I'm seeing issues with CLOB/BLOB columns, even before getting serialization involved.

I think the source of the nil comparison problem is here. I got the nil comparisons working by adding return super if o.right.nil?, but this is a somewhat naively implemented on my part. There may be more to it.

The empty hash comparison I'm not sure about yet. I'll take a look next time I get a chance.

[brianthoman]

I think I got my wires crossed earlier - I was testing the empty hashes with a non-serialized column. After sorting things out and testing again, the guard clause also seems to fix the empty hash queries. I've submitted a PR.

[pandu-49]

This problem is because of considering text/binary columns as CLOB in https://github.com/rsim/oracle-enhanced/pull/2258

[akostadinov]

That's a good catch! I reviewed brianthoman's PR (although I am not in any kind of authority in the project). Hopefully it will get merged soon.