Skip Menu |
 

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

Report information
The Basics
Id: 68093
Status: resolved
Priority: 0/
Queue: DBIx-Class

People
Owner: Nobody in particular
Requestors: bphillips [...] cpan.org
Cc:
AdminCc:

Bug Information
Severity: Important
Broken in:
  • 0.08191
  • 0.08192
Fixed in: 0.08193



Subject: paginated resultset broken after
Download (untitled) / with headers
text/plain 2.7k
The following resultset works on $rs->all but fails on $rs->search({},{rows=>5})->all It looks like the sub-select is getting completely borked. Rumor has it (on IRC) that the bug was introduced by: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?p=dbsrgits/DBIx- Class.git;a=commitdiff;h=fcb7fcbb6bde5f9a211c62011b3110f07828caec;hp=ee53ca0f8e39882bc30fee5cb6db9e0318d09d59 Thanks! ## DBIC code $rs = $rs->search( { -exists => $other_rs->search( { 'inner_order_items.vendor' => $v->id, 'inner_order_items.orderid' => { -ident => 'me.orderid' } }, { alias => 'inner_order_items', select => ['orderid'] } )->search( { "inner_order_items.stamp_insert" => { -between => [ $begin, $end ] } } ) } ); -- working query in v0.0891 ($rs->all) SELECT 1 AS fake -- columns snipped for clarity and proprietary schema info FROM orders me WHERE EXISTS( SELECT inner_order_items.orderid FROM order_items inner_order_items WHERE inner_order_items.stamp_insert BETWEEN '2011-05-05 00:00:00' AND '2011-05-06 00:00:00' AND inner_order_items.orderid = me.orderid AND inner_order_items.vendor = '3639' ) --------------------------------------------- -- broken query in v0.0891 ($rs->search({}, {rows => 5}) SELECT 1 AS fake -- columns snipped for clarity and proprietary schema info FROM( SELECT 1 AS fake -- columns snipped for clarity and proprietary schema info FROM order_items inner_order_items WHERE inner_order_items.stamp_insert BETWEEN '2011-05-05 00:00:00' AND '2011-05-06 00:00:00' AND inner_order_items.orderid = me.orderid AND inner_order_items.vendor = '3639' ) ) ) ) me WHERE ROWNUM <= '5' --------------------------------------------- -- previously working query in v0.08127 ($rs->search({}, {rows => 5}) SELECT 1 AS fake -- columns snipped for clarity and proprietary schema info FROM ( SELECT 1 AS fake -- columns snipped for clarity and proprietary schema info FROM orders me WHERE EXISTS( SELECT inner_order_items.orderid FROM order_items inner_order_items WHERE inner_order_items.stamp_insert BETWEEN '2011-05-05 00:00:00' AND '2011-05-06 00:00:00' AND inner_order_items.orderid = me.orderid AND inner_order_items.vendor = '3639' ) ) me WHERE ROWNUM <= 5 Oracle error: DBI Exception: DBD::Oracle::db prepare_cached failed: ORA-00933: SQL command not properly ended (DBD ERROR: error possibly near <*> indicator at char 4296 in ' SELECT 1 AS fake -- columns snipped for clarity and proprietary schema info FROM ( SELECT 1 AS fake -- columns snipped for clarity and proprietary schema info FROM order_items inner_order_items WHERE ( ( ( inner_order_items.stamp_insert BETWEEN :p1 AND :p2 ) AND ( inner_order_items.orderid = me.orderid AND inner_order_items.vendor = :p3 ) ) ) )<*>) ) ) me WHERE ROWNUM <= :p4
Subject: Re: [rt.cpan.org #68093] paginated resultset broken after
Date: Tue, 10 May 2011 16:03:42 -0400
To: Brian Phillips via RT <bug-DBIx-Class [...] rt.cpan.org>
From: Peter Rabbitson <ribasushi [...] cpan.org>
Download (untitled) / with headers
text/plain 1.1k
On Tue, May 10, 2011 at 03:58:54PM -0400, Brian Phillips via RT wrote: Show quoted text
> Tue May 10 15:58:51 2011: Request 68093 was acted upon. > Transaction: Ticket created by bphillips > Queue: DBIx-Class > Subject: paginated resultset broken after > Broken in: 0.08191, 0.08192 > Severity: Important > Owner: Nobody > Requestors: bphillips@cpan.org > Status: new > Ticket <URL: https://rt.cpan.org/Ticket/Display.html?id=68093 > > > > The following resultset works on $rs->all but fails on $rs->search({},{rows=>5})->all > > It looks like the sub-select is getting completely borked. > > ## DBIC code > $rs = $rs->search( > { > -exists => $other_rs->search( > { 'inner_order_items.vendor' => $v->id, 'inner_order_items.orderid' => { -ident => 'me.orderid' } }, > { alias => 'inner_order_items', select => ['orderid'] } > )->search( { "inner_order_items.stamp_insert" => { -between => [ $begin, $end ] } } ) > } > ); >
Is this real code? Because I do not see a subselect here. -exists => $rs has no meaning -exists => $rs->as_query does Are you claiming the thing as written "worked" for you before?
Download (untitled) / with headers
text/plain 702b
Show quoted text
> Is this real code? Because I do not see a subselect here.
Sorry, I was gathering code that was spread through several functions into something I could no-paste. Show quoted text
> > -exists => $rs > has no meaning > > -exists => $rs->as_query > does > > Are you claiming the thing as written "worked" for you before?
Yes, the original code does in fact have -exists => $rs->as_query $rs = $rs->search( { -exists => $other_rs->search( { 'inner_order_items.vendor' => $v->id, 'inner_order_items.orderid' => { -ident => 'me.orderid' } }, { alias => 'inner_order_items', select => ['orderid'] } )->search( { "inner_order_items.stamp_insert" => { - between => [ $begin, $end ] } } )->as_query } );
Subject: Re: [rt.cpan.org #68093] paginated resultset broken after
Date: Tue, 10 May 2011 16:18:38 -0400
To: Brian Phillips via RT <bug-DBIx-Class [...] rt.cpan.org>
From: Peter Rabbitson <ribasushi [...] cpan.org>
Download (untitled) / with headers
text/plain 1.2k
On Tue, May 10, 2011 at 04:13:43PM -0400, Brian Phillips via RT wrote: Show quoted text
> Queue: DBIx-Class > Ticket <URL: https://rt.cpan.org/Ticket/Display.html?id=68093 > >
> > Is this real code? Because I do not see a subselect here.
> > Sorry, I was gathering code that was spread through several functions > into something I could no-paste. >
> > > > -exists => $rs > > has no meaning > > > > -exists => $rs->as_query > > does > > > > Are you claiming the thing as written "worked" for you before?
> > Yes, the original code does in fact have -exists => $rs->as_query > > $rs = $rs->search( > { > -exists => $other_rs->search( > { 'inner_order_items.vendor' => $v->id, > 'inner_order_items.orderid' => { -ident => 'me.orderid' } }, > { alias => 'inner_order_items', select => > ['orderid'] } > )->search( { "inner_order_items.stamp_insert" => { - > between => [ $begin, $end ] } } )->as_query > } > );
It is highly unlikely that the commit in question is causing you problems (it deals with an unrelated codepath). Since you are not in a position to provide actual code (the snipped stuff is crucial to debugging this), I need you to conduct a git-bisect to figure out which commit exactly broke your code. Ask on #dbix-class for help with bisection if you are unsure how to do it Cheers
Subject: Re: [rt.cpan.org #68093] paginated resultset broken after
Date: Tue, 10 May 2011 16:31:22 -0400
To: Brian Phillips via RT <bug-DBIx-Class [...] rt.cpan.org>
From: Peter Rabbitson <ribasushi [...] cpan.org>
Download (untitled) / with headers
text/plain 862b
On Tue, May 10, 2011 at 03:58:54PM -0400, Brian Phillips via RT wrote: Show quoted text
> --------------------------------------------- > -- broken query in v0.0891 ($rs->search({}, {rows => 5}) > SELECT 1 AS fake -- columns snipped for clarity and proprietary schema info > FROM( > SELECT 1 AS fake -- columns snipped for clarity and proprietary schema info > FROM order_items inner_order_items > WHERE inner_order_items.stamp_insert BETWEEN '2011-05-05 00:00:00' AND '2011-05-06 00:00:00' AND inner_order_items.orderid = me.orderid > AND inner_order_items.vendor = '3639' > ) ) ) ) me > WHERE ROWNUM <= '5'
Also is this snippet formatted by you or byt DBIx::Class' DBIC_TRACE_PROFILE? Please provide the exact single-line SQL, as seen by DBI with a minimal amount of pieces edited out and *without* adding your own made up 'as fake' pieces. Cheers
Download (untitled) / with headers
text/plain 729b
The result of git bisect resulted in this commit being the culprit: 69d3c2708f5564ce38d5878fa694b04f6740cde0 is the first bad commit commit 69d3c2708f5564ce38d5878fa694b04f6740cde0 Author: Arthur Axel 'fREW' Schmidt <frioux@gmail.com> Date: Fri Apr 8 11:02:43 2011 -0500 Fix complex limits with subqueries in selectors (fix both incorrect splitting of the selector list and duplication of binds) :100644 100644 789ce720b154140427d0c084e74f34b3c308480d 0648becf6eed1b1e972e55b3e97c8883175ed6ab M Changes :040000 040000 800b8279ae02b1fb4a148ebe1c57e8e78cc9e20e 5952006f3697679233a8acd99fa79e1fb57d9711 M lib :040000 040000 8497aa7f0f654bad0e0c79ccd6f16da51d95b747 8ba565c9c8c360bda061dd55edb391ba95acd510 M t
Subject: Re: [rt.cpan.org #68093] paginated resultset broken after
Date: Tue, 10 May 2011 16:48:55 -0400
To: Brian Phillips via RT <bug-DBIx-Class [...] rt.cpan.org>
From: Peter Rabbitson <ribasushi [...] cpan.org>
Download (untitled) / with headers
text/plain 1.3k
On Tue, May 10, 2011 at 04:33:31PM -0400, Brian Phillips via RT wrote: Show quoted text
> Queue: DBIx-Class > Ticket <URL: https://rt.cpan.org/Ticket/Display.html?id=68093 > > > The result of git bisect resulted in this commit being the culprit: > > 69d3c2708f5564ce38d5878fa694b04f6740cde0 is the first bad commit > commit 69d3c2708f5564ce38d5878fa694b04f6740cde0 > Author: Arthur Axel 'fREW' Schmidt <frioux@gmail.com> > Date: Fri Apr 8 11:02:43 2011 -0500 > > Fix complex limits with subqueries in selectors
Fuck. This, kids, is what we get for parsing SQL with regexes :) http://git.shadowcat.co.uk/gitweb/gitweb.cgi?p=dbsrgits/DBIx-Class.git;a=blob;f=lib/DBIx/Class/SQLMaker/LimitDialects.pm;h=e3da121881522ea7409080440d4d7c0226c768ed;hb=HEAD#l626 ^^ The line above is intended to take a pre-made SQL query, and cut away *only* the first SELECTor (so the stuff SELECT ... FROM) even if the selected columns involve other subselects. However it goes on to cut everything until the FROM in your WHERE (the exists), due to the .+ It can't be changed to a .+? because then it will not cut out subselects in the main SELECT. I will have to think about this for a bit. I have a solution in mind, but it makes baby Jesus cry enough to die from dehydration, so I need to see if we can do better than this bullshit. In the meantime you can try to tweak the regex in question to fix your immediate issue... Cheers
Subject: Re: [rt.cpan.org #68093] paginated resultset broken after
Date: Tue, 10 May 2011 17:24:58 -0400
To: Brian Phillips via RT <bug-DBIx-Class [...] rt.cpan.org>
From: Peter Rabbitson <ribasushi [...] cpan.org>
Download (untitled) / with headers
text/plain 130b
Can you try this please: http://paste.scsys.co.uk/102982 It's a crappy solution but it should be a relatively foolproof stopgap.
Download (untitled) / with headers
text/plain 211b
On Tue May 10 17:25:08 2011, RIBASUSHI wrote: Show quoted text
> Can you try this please: http://paste.scsys.co.uk/102982 > > It's a crappy solution but it should be a relatively foolproof stopgap.
Yes, that fixes the problem
Subject: Re: [rt.cpan.org #68093] paginated resultset broken after
Date: Wed, 11 May 2011 09:04:00 -0400
To: Brian Phillips via RT <bug-DBIx-Class [...] rt.cpan.org>
From: Peter Rabbitson <ribasushi [...] cpan.org>
Download (untitled) / with headers
text/plain 775b
On Wed, May 11, 2011 at 08:54:53AM -0400, Brian Phillips via RT wrote: Show quoted text
> Queue: DBIx-Class > Ticket <URL: https://rt.cpan.org/Ticket/Display.html?id=68093 > > > On Tue May 10 17:25:08 2011, RIBASUSHI wrote:
> > Can you try this please: http://paste.scsys.co.uk/102982 > > > > It's a crappy solution but it should be a relatively foolproof stopgap.
> > Yes, that fixes the problem
Can you please augment the three tests added by 69d3c270[1], to include a subselect in the WHERE part *in addition* to the selector, and wrap my fix as a complete git commit (with a Changes entry), and have someone on #dbic shove it into mainstream? Thank you for your help! Cheers [1] http://git.shadowcat.co.uk/gitweb/gitweb.cgi?p=dbsrgits/DBIx-Class.git;a=commitdiff;h=69d3c270
Fibnally fixed, sorry it took so long to release, $life intervened :)


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.