Skip Menu |
 

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

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

People
Owner: Nobody in particular
Requestors: korjavin [...] gmail.com
Cc:
AdminCc:

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



Subject: Problem "Result collapse not possible - selection from a has_many source redirected to the main" after update 0.08196-3 => 0.08250-1
Date: Tue, 24 Sep 2013 10:58:42 +0600
To: bug-DBIx-Class [...] rt.cpan.org
From: Ivan Korjavin <korjavin [...] gmail.com>
Schema is: CREATE TABLE providers ( pid integer primary key autoincrement, name varchar(50), ); CREATE TABLE orders ( oid integer primary key autoincrement, pid integer references providers (pid), ); CREATE TABLE orderstatus( osid integer primary key autoincrement, oid integer references orders (oid), regdt timestamp default current_timestamp ); Code is: my $order = $schema->resultset('Order'); my $r = $order->search( { 'orderstatuses.sid' => 1, 'orderstatuses.regdt' => { '>=', $first } }, { prefetch=>'pid',join => 'orderstatuses','+select'=> 'orderstatuses.regdt', '+as' => 'regdt', order_by => { -desc => 'me.oid' } } ); DBIC_TRACE=1 show the query: SELECT me.oid, me.pid, orderstatuses.regdt, pid.pid, pid.name FROM orders me LEFT JOIN orderstatus orderstatuses ON orderstatuses.oid = me.oid LEFT JOIN providers pid ON pid.pid = me.pid WHERE ( ( orderstatuses.regdt >= ? AND orderstatuses.sid = ? ) ) ORDER BY me.oid DESC Its works nice with libdbix-class-perl = 0.08196-3 but in libdbix-class-perl = 0.08250-1 show the error: Result collapse not possible - selection from a has_many source redirected to the main object at /usr/share/perl5/DBIx/Class/Schema.pm line 1080. DBIx::Class::Schema::throw_exception('a::Model=HASH(0x1931de0)', 'Result collapse not possible - selection from a has_many sour...') called at /usr/share/perl5/DBIx/Class/ResultSource.pm line 1952 DBIx::Class::ResultSource::throw_exception('DBIx::Class::ResultSource::Table=HASH(0x193b958)', 'Result collapse not possible - selection from a has_many sour...') called at /usr/share/perl5/DBIx/Class/ResultSet.pm line 3894 DBIx::Class::ResultSet::throw_exception('DBIx::Class::ResultSet=HASH(0x2d65f08)', 'Result collapse not possible - selection from a has_many sour...') called at /usr/share/perl5/DBIx/Class/ResultSet.pm line 1378 DBIx::Class::ResultSet::_construct_results('DBIx::Class::ResultSet=HASH(0x2d65f08)') called at /usr/share/perl5/DBIx/Class/ResultSet.pm line 1259 DBIx::Class::ResultSet::next('DBIx::Class::ResultSet=HASH(0x2d65f08)') called at lib/Report.pl line 27
Download (untitled) / with headers
text/plain 1.2k
On Tue Sep 24 00:58:58 2013, korjavin@gmail.com wrote: Show quoted text
> Code is: > > my $order = $schema->resultset('Order'); > > my $r = $order->search( > { 'orderstatuses.sid' => 1, > 'orderstatuses.regdt' => { '>=', $first } > }, > { prefetch=>'pid',join => 'orderstatuses','+select'=> > 'orderstatuses.regdt', '+as' => 'regdt', order_by => { -desc => > 'me.oid' } > } > ); > > Its works nice with libdbix-class-perl = 0.08196-3 but > in libdbix-class-perl = 0.08250-1 > > show the error: > > Result collapse not possible - selection from a has_many source > redirected > to the main object ...
Everything is correct. Under 0.08196 this was a silent bug. While your query may return *multiple* orderstatuses per order object, you are asking to take *one* value of regdt and assign it to the main order object. Before DBIC would ranomly use a value (usually the first one that came off the cursor), but this was never correct to begin with. Now it throws an exception informing you that you are about to lose data. You need to rewrite this particular query, it can not produce a sensible result as specified. Let me know if this clarifies things or you still have some questions. For the time being I will mark the ticket as rejected, since eerything is working by design.
Subject: Re: [rt.cpan.org #88923] Problem "Result collapse not possible - selection from a has_many source redirected to the main" after update 0.08196-3 => 0.08250-1
Date: Tue, 24 Sep 2013 15:29:36 +0600
To: bug-DBIx-Class [...] rt.cpan.org
From: Ivan Korjavin <korjavin [...] gmail.com>
Download (untitled) / with headers
text/plain 178b
Thank you!! Now I understood where is problem. I was confused that tested code stops working after update. Still don't know how to fix that query, but anyway I got your point.
Subject: Re: [rt.cpan.org #88923] Problem "Result collapse not possible - selection from a has_many source redirected to the main" after update 0.08196-3 => 0.08250-1
Date: Tue, 24 Sep 2013 09:53:09 +0000
To: "korjavin [...] gmail.com via RT" <bug-DBIx-Class [...] rt.cpan.org>
From: Peter Rabbitson <ribasushi [...] cpan.org>
Download (untitled) / with headers
text/plain 708b
On Tue, Sep 24, 2013 at 05:29:52AM -0400, korjavin@gmail.com via RT wrote: Show quoted text
> Queue: DBIx-Class > Ticket <URL: https://rt.cpan.org/Ticket/Display.html?id=88923 > > > Thank you!! > Now I understood where is problem. > I was confused that tested code stops working after update. > > Still don't know how to fix that query, but anyway I got your point.
What you most likely want is to prefetch orderstatuses in addition to pid, OR get an aggregate of regdt (a MIN or a MAX). It all depends on what you are doing with this data afterwards. What usually helps is to word the question in your spoken language, as opposed to Perl. Then all of a sudden things start making much more sense. Cheers
Subject: Re: [rt.cpan.org #88923] Problem "Result collapse not possible - selection from a has_many source redirected to the main" after update 0.08196-3 => 0.08250-1
Date: Tue, 24 Sep 2013 18:03:58 +0600
To: bug-DBIx-Class [...] rt.cpan.org
From: Ivan Korjavin <korjavin [...] gmail.com>
Download (untitled) / with headers
text/plain 1.1k
My english, probably, worse then Perl, so I'll try the sql: I want something like this: select min(os.regdt),o.oid,p.name from orders o inner join orderstatus os on o.oid=os.oid left join providers p on p.pid=o.pid where os.sid=1 and os.regdt>'...' group by o.oid; 2013/9/24 Peter Rabbitson via RT <bug-DBIx-Class@rt.cpan.org> Show quoted text
> <URL: https://rt.cpan.org/Ticket/Display.html?id=88923 > > > On Tue, Sep 24, 2013 at 05:29:52AM -0400, korjavin@gmail.com via RT wrote:
> > Queue: DBIx-Class > > Ticket <URL: https://rt.cpan.org/Ticket/Display.html?id=88923 > > > > > Thank you!! > > Now I understood where is problem. > > I was confused that tested code stops working after update. > > > > Still don't know how to fix that query, but anyway I got your point.
> > What you most likely want is to prefetch orderstatuses in addition to > pid, OR get an aggregate of regdt (a MIN or a MAX). It all depends on > what you are doing with this data afterwards. > > What usually helps is to word the question in your spoken language, as > opposed to Perl. Then all of a sudden things start making much more > sense. > > Cheers > > > >
Download (untitled) / with headers
text/plain 511b
On Tue Sep 24 08:04:16 2013, korjavin@gmail.com wrote: Show quoted text
> I want something like this: > > select min(os.regdt)...
Then do so :) Instead of your original: $order->search( {...}, { order_by => { -desc => 'me.oid' } }, prefetch=>'pid', join => 'orderstatuses', '+select'=> 'orderstatuses.regdt', '+as' => 'regdt', }); do $order->search( {...}, { order_by => { -desc => 'me.oid' } }, prefetch=>'pid', join => 'orderstatuses', '+columns' => { regdt => { min => 'orderstatuses.regdt' } }, });
Subject: Re: [rt.cpan.org #88923] Problem "Result collapse not possible - selection from a has_many source redirected to the main" after update 0.08196-3 => 0.08250-1
Date: Tue, 24 Sep 2013 18:42:39 +0600
To: bug-DBIx-Class [...] rt.cpan.org
From: Ivan Korjavin <korjavin [...] gmail.com>
Download (untitled) / with headers
text/plain 762b
Yeaah!! This and group_by => 'me.oid ' , and it works perfect! Thank you! 2013/9/24 Peter Rabbitson via RT <bug-DBIx-Class@rt.cpan.org> Show quoted text
> <URL: https://rt.cpan.org/Ticket/Display.html?id=88923 > > > On Tue Sep 24 08:04:16 2013, korjavin@gmail.com wrote: >
> > I want something like this: > > > > select min(os.regdt)...
> > Then do so :) > > Instead of your original: > > $order->search( {...}, { > order_by => { -desc => 'me.oid' } }, > prefetch=>'pid', > join => 'orderstatuses', > '+select'=> 'orderstatuses.regdt', > '+as' => 'regdt', > }); > > do > > $order->search( {...}, { > order_by => { -desc => 'me.oid' } }, > prefetch=>'pid', > join => 'orderstatuses', > '+columns' => { regdt => { min => 'orderstatuses.regdt' } }, > }); > >


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.