Skip Menu |
 

This queue is for tickets about the DBIx-Class CPAN distribution.

Report information
The Basics
Id: 56791
Status: rejected
Priority: 0/
Queue: DBIx-Class

People
Owner: Nobody in particular
Requestors:
Cc:
AdminCc:

Bug Information
Severity: Important
Broken in: 0.08119
Fixed in: (no value)



Subject: raw sql ORDER_BY clause ignored
Download (untitled) / with headers
text/plain 1.4k
My code works in 0.08118, but is broken in 0.08119; the changelog leads me to suspect: - Add $rs->is_ordered to test for existing order_by on a resultset I first use raw SQL to define a specific ORDER BY sequence "myseq", in the following method "bynum": #---------------------------------------- sub bynum { my $self = shift; return $self->search( {}, { '+select' => [ \'(CASE WHEN num LIKE "U%" THEN 1 WHEN num LIKE "F%" THEN 2 when num LIKE "W%" THEN 3 ELSE 4 END) AS myseq' ], 'order_by' => ['myseq' ], '+as' => ['myseq' ], } ); } #---------------------------------------- When I use the above method (as follows), in 0.08119 DBIC throws an error about "column myseq": DBIx::Class::ResultSet::next(): DBI Exception: DBD::SQLite::db prepare_cached failed: no such column: myseq [for Statement "SELECT num, ... ORDER BY myseq, ... Here's the skeleton code, but if this needs more explanation, let me know. #---------------------------------------- package DB::Schema::ResultSet::Pub; use parent 'DBIx::Class::ResultSet'; sub foo { my $self = shift; return $self->search( ... )->bynum; # returns ordered rs } sub bar { my $self = shift; return $self ->foo # should be an ordered rs ->search( ... ); # error: "cant find column myseq" } #---------------------------------------- Then calling "->bar" gives the error msg. Thanks,
Download (untitled) / with headers
text/plain 1.7k
I believe a regression was introduced in 0.08119 that was fixed in 0.08121. Can you try 0.08121? On Wed Apr 21 14:51:32 2010, jdub wrote: Show quoted text
> My code works in 0.08118, but is broken in 0.08119; the changelog leads > me to suspect: > - Add $rs->is_ordered to test for existing order_by on a resultset > > > I first use raw SQL to define a specific ORDER BY sequence "myseq", in > the following method "bynum": > > #---------------------------------------- > sub bynum { > my $self = shift; > > return $self->search( > {}, > { '+select' => [ > \'(CASE WHEN num LIKE "U%" THEN 1 WHEN num LIKE "F%" THEN 2 > when num LIKE "W%" THEN 3 ELSE 4 END) AS myseq' > ], > 'order_by' => ['myseq' ], > '+as' => ['myseq' ], > } > ); > } > #---------------------------------------- > > > When I use the above method (as follows), in 0.08119 DBIC throws an > error about "column myseq": > DBIx::Class::ResultSet::next(): DBI Exception: DBD::SQLite::db > prepare_cached failed: > no such column: myseq [for Statement "SELECT num, ... ORDER BY > myseq, ... > > > Here's the skeleton code, but if this needs more explanation, let me know. > #---------------------------------------- > package DB::Schema::ResultSet::Pub; > use parent 'DBIx::Class::ResultSet'; > > sub foo { > my $self = shift; > return $self->search( ... )->bynum; # returns ordered rs > } > > > sub bar { > my $self = shift; > return $self > ->foo # should be an ordered rs > ->search( ... ); # error: "cant find column myseq" > } > #---------------------------------------- > > Then calling "->bar" gives the error msg. Thanks,
From: jdub
Download (untitled) / with headers
text/plain 346b
On Wed Apr 21 15:19:39 2010, arcanez wrote: Show quoted text
> I believe a regression was introduced in 0.08119 that was fixed in > 0.08121. Can you try > 0.08121?
It definitely doesn't work in 0.08121. I initially upgraded from 0.08115 to 0.08121 and then worked backwards 'til I found the version where it failed, thinking that approach might be more helpful.
Download (untitled) / with headers
text/plain 1.2k
On Wed Apr 21 14:51:32 2010, jdub wrote: Show quoted text
> My code works in 0.08118, but is broken in 0.08119; the changelog leads > me to suspect: > - Add $rs->is_ordered to test for existing order_by on a resultset > > > I first use raw SQL to define a specific ORDER BY sequence "myseq", in > the following method "bynum": > > #---------------------------------------- > sub bynum { > my $self = shift; > > return $self->search( > {}, > { '+select' => [ > \'(CASE WHEN num LIKE "U%" THEN 1 WHEN num LIKE "F%" THEN 2 > when num LIKE "W%" THEN 3 ELSE 4 END) AS myseq' > ], > 'order_by' => ['myseq' ], > '+as' => ['myseq' ], > } > ); > } > #----------------------------------------
Is this the full search() call or you edited parts you assumed were not relevant? Show quoted text
> When I use the above method (as follows), in 0.08119 DBIC throws an > error about "column myseq": > DBIx::Class::ResultSet::next(): DBI Exception: DBD::SQLite::db > prepare_cached failed: > no such column: myseq [for Statement "SELECT num, ... ORDER BY > myseq, ... >
I need the full error, with the full unaltered SQL statement. Show quoted text
> Here's the skeleton code, but if this needs more explanation, let me know.
Skeleton code is not very useful - give me full real exceprts.
Subject: raw sql ORDER_BY clause ignored - NOT!!
From: jdub
On Wed Apr 21 17:19:26 2010, RIBASUSHI wrote: Show quoted text
> I need the full error, with the full unaltered SQL statement. >
> > Here's the skeleton code, but if this needs more explanation, let me
know. Show quoted text
> > Skeleton code is not very useful - give me full real exceprts. >
Thank you for the help and the excellent advice about providing the full code. That turned out to be the solution (!) Here's what I thought didn't work, and the fix is now obvious. The 'select' and 'as' should have been '+select' and '+as'. Of course the code works fine once that was changed. I offer profuse and humble apologies. #_______________________________________________________________________ sub bar { my $self = shift; my $rs = $self->conf->search({ # conf returns ordered rs 'num' => { 'like' => 'U%' } }, { 'select' => [ 'num', \"\'conf\' AS category" ], 'as' => [ 'num', 'category' ], }); my @stuff; while ( my $row = $rs->next ) { push @stuff, $row->num; } return \@stuff; }
Closing due to PEBKAC :)


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.