[Dbix-class] Find with literal SQL and bind not satisfying constraint

[Christian Lackas]

Hi Everybody,

we used to do something like this:

    my $proj = $projects->find( { projectpath => \'= ? COLLATE utf8_bin' }, 
        { key => 'ProjectPath', bind => [ $p ] } );

to find a project by its unique path in a case-sensitive way.
Unfortunately, after an upgrade of DBIx::Class (from 0.08204 to
0.082820) we now get an error message:

    DBIx::Class::ResultSource::_minimal_valueset_satisfying_constraint():
    Unable to satisfy requested constraint 'ProjectPath', missing values for
    column(s): 'projectpath'

although there clearly is a projectpath defined.

I tracked the issue down to DBIx::Class::Storage::DBIHacks::_extract_fixed_condition_columns
which does not cover the case where the value is a scalar reference.

What is the correct way to use literal SQL now?
Unfortunately this does not seem to work with find():
https://metacpan.org/pod/DBIx::Class::Manual::Cookbook#Using-SQL-functions-on-the-left-hand-side-of-a-comparison

    $projects->find( \[ 'projectpath = ?' , $p ] );

with the same error message shown above.

And also the reference to SQL::Abstract does not really help, e.g. this
also does not work:

    $projects->find( { projectpath => \[ '= ? COLLATE utf8_bin', $p ] } 
        { key => 'ProjectPath' } );

however, the slightly different version does work:

    $projects->find( { projectpath => \[ '= ? COLLATE utf8_bin', $p ] } );

There was a reason why we had to explicitly set the key manually here,
though, and I am not sure if simply removing it will not cause other
issues.


Both these things still work:

    $projects->find( { projectpath => $p }, { key => 'ProjectPath' } ); # just is case insensitive

or

    $projects->search( { projectpath => \'= ? COLLATE utf8_bin' },
              { key => 'ProjectPath', bind => [ $p ] } )->single;

Any idea why the original code stopped working?

Christian

-- 
Dr. Christian Lackas, Managing Partner
inviCRO, LLC -- In Imaging Yours
http://www.invicro.com/  http://www.spect-ct.com/