[Dbix-class] problem with dbix-class fetch from mysql vs sqlite

[Zbigniew Lukasiak]

Hello,

On Thu, May 15, 2008 at 10:15 PM, Jean-Francois Lucier
<Jean-Francois.Lucier at usherbrooke.ca> wrote:
> Hello,
>
> I have build an API using DBIx::Class that executes many queries on either
> SQLite or mysql (same database schema). I have put traces in my script.
>
> When I execute on mysql, everything goes well and my traces output the
> following:
>
> trace1 selfstart=45622542 selfaligmentstrand=1
> me.location_id : 2884872
> SELECT me.location_id, me.start, me.end, me.accession, me.aligment_id,
> aligment.aligment_id, aligment.strand, aligment.reference_sequence_id,
> aligment.score, aligment.distant_id, aligment.distant_table FROM location
> me  JOIN aligment aligment ON ( aligment.aligment_id = me.aligment_id )
> WHERE ( ( ( start*strand < ? ) AND ( me.aligment_id = ? ) ) ) ORDER BY
> start*strand DESC LIMIT 1: '45622542', '507781'
> me.location_id : 2884873
>
> for SQlite I get:
>
> trace1 selfstart=45622542 selfaligmentstrand=1
> me.location_id : 2884872
> SELECT me.location_id, me.start, me.end, me.accession, me.aligment_id,
> aligment.aligment_id, aligment.strand, aligment.reference_sequence_id,
> aligment.score, aligment.distant_id, aligment.distant_table FROM location
> me  JOIN aligment aligment ON ( aligment.aligment_id = me.aligment_id )
> WHERE ( ( ( start*strand < ? ) AND ( me.aligment_id = ? ) ) ) ORDER BY
> start*strand DESC LIMIT 1: '45622542', '507781'
> me.location_id : 2884872
>
> If I use directly DBI and execute the same query, evrything goes ok and I
> get:
>
> the following query will be executed: SELECT me.location_id, me.start,
> me.end, me.accession, me.aligment_id, aligment.aligment_id, aligment.strand,
> aligment.reference_sequence_id, aligment.score, aligment.distant_id,
> aligment.distant_table FROM location me  JOIN aligment aligment ON (
> aligment.aligment_id = me.aligment_id ) WHERE ( ( ( start*strand < 45622542
> ) AND ( me.aligment_id = 507781 ) ) ) ORDER BY start*strand DESC LIMIT 1
> reference_sequence_id=27
> score=1
> location_id=2884873
> distant_id=742163
> aligment_id=507781
> accession=
> end=45620997
> strand=1
> distant_table=transcript
> start=45620932
>
> This is the code that executes the query inside DBIx::Class along with the
> traces:
>
> sub prev_location {    #only for stored locations
>     my ($self) = @_;
>     warn("I am not sure this can work") unless ($self->in_storage);
>     print STDERR "trace1 selfstart=".$self->start."
> selfaligmentstrand=".$self->aligment->strand."\n";
>     print STDERR "me.location_id : ".$self->location_id."\n";
>     my $x = $self->aligment->search_related_rs(
>         'locations',
>         {'start*strand' => {'<' => $self->start * $self->aligment->strand}},
>         {order_by => ['start*strand DESC']}
>     )->slice(0, 0)->single;
>     print STDERR "me.location_id : ".$x->location_id."\n";
>     return $x;
> }

Did you try to remove things from that query?  For example remove:
'->slice(0, 0)->single' (the query will then return an array - but
that's OK - you just need to verify the SQL query after all),
'{order_by => ['start*strand DESC']}', maybe other parts as well.  Try
to clean it a bit in general.

Then what would be also helpful is your ResultSource definitions.

-- 
Zbigniew Lukasiak
http://brudnopis.blogspot.com/