string::lower() comparison with an array fails

[renatolond]

Describe the bug

When using string:lower() in a where statement, if the comparison is with an array, the comparison fails.

To Reproduce

Assuming a table "table" with a string field "field", in any repository do:

root.where { string::lower(field).is([“value1”, “value2”]) }

Fails with

Caused by PG::UndefinedFunction: ERROR: operator does not exist: text = record
LINE 1: …" FROM “table” WHERE (LOWER(“table”.“field”) = ('value1…
HINT: No operator matches the given name and argument types. You might need to add explicit type casts.

Expected behavior

The comparison is expected to generate a query with (LOWER("table"."field") in ('value1', 'value2')) and the query should work

Your environment

• Affects my production application: NO
• Ruby version: 2.6.6
• OS: Linux

[solnic]

For the record, this is what we should support:

root.where { lower(field).in(["value1", "value2"]) }

Return types should not be required in where blocks after all.

[flash-gordon]

@solnic I disagree, return type is required to determine the list of available functions/operators. It's not required for common operators such as IN or =

[solnic]

@flash-gordon not sure if I understand - do you mean that we should stop handling function building via method_missing and go the explicit route with a list of available functions?

[flash-gordon]

@solnic the list of available operators is determined by the expression type: https://github.com/rom-rb/rom-sql/blob/f3ef5817462c4915f37d0523ea2a2ba95c36a9de/lib/rom/sql/type_extensions.rb For instance json_returning_function(field).json_operator(args) will be a method missing (or mb some other) error whereas json::json_returning_function(field).json_operator(args) will work just fine. It's consistent and I think we should keep it that way.

[solnic]

@flash-gordon OK fair enough 😄 so it seems like this bug is caused by the lack of in method defined on ROM::SQL::Function

[renatolond]

Just to add a little comment, the method that seems to support either = or in in some other parts seem to be is, is it intentional to split it into .is and .in ?
(see

rom-sql/spec/unit/function_spec.rb

Lines 74 to 75 in 4611a76

	expect(func.case(users[:id].is([1, 2]) => 'first', else: 'last').sql_literal(ds)).
	to eql(%((CASE WHEN ("users"."id" IN (1, 2)) THEN 'first' ELSE 'last' END)))

)

[solnic]

@renatolond actually, it's not intentional - ROM::SQL::Attribute#is uses Sequels =~ operator which supports array arg too (just like in).

This should be unified somehow, because ROM::SQL::Function#is doesn't do the same thing 🙈