Skip Menu |
 

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

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

People
Owner: Nobody in particular
Requestors: sargie [...] cpan.org
Cc: paulm [...] paulm.com
AdminCc:

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



CC: paulm [...] paulm.com
Subject: prefetch and group_by not playing nicely together
Download (untitled) / with headers
text/plain 3.6k
Here is the Perl data structure of the query I would like: join => [qw/publication_story/], select => [{ count => 'publication_story.uid' }, qw/me.uid me.name me.short_name me.url me.description me.publication_type_uid me.region_uid me.channel_type_uid me.circulation me.ave me.comments/,], as => [qw/story_count uid name short_name url description publication_type_uid region_uid channel_type_uid circulation ave comments/], group_by => [qw/me.uid/], prefetch => [qw/publication_type region/], If I comment-out the 'group_by', I get the sensible: SELECT COUNT( publication_story.uid ), me.uid, me.name, me.short_name, me.url, me.description, me.publication_type_uid, me.region_uid, me.channel_type_uid, me.circulation, me.ave, me.comments, publication_type.uid, publication_type.parent_uid, publication_type.value, publication_type.hide, publication_type.alias, publication_type.order_priority, publication_type.show_in_lists, publication_type.data_type, publication_type.cascade_data_type, publication_type.description, region.uid, region.parent_uid, region.value, region.hide, region.alias, region.order_priority, region.show_in_lists, region.data_type, region.cascade_data_type, region.description FROM publication me LEFT JOIN story publication_story ON publication_story.publication_uid = me.uid LEFT JOIN code_tree publication_type ON publication_type.uid = me.publication_type_uid LEFT JOIN code_tree region ON region.uid = me.region_uid WHERE ( ( name LIKE '%ZDNet%' OR short_name LIKE '%ZDNet%' ) ) ORDER BY name; This doesn't work though, as the count requires a GROUP BY clause. If I manually add that GROUP BY clause to the end ("GROUP BY me.uid") it works fine. However, if I ask DBIx::Class to do it, it gets all messed up: SELECT me.story_count, me.uid, me.name, me.short_name, me.url, me.description, me.publication_type_uid, me.region_uid, me.channel_type_uid, me.circulation, me.ave, me.comments, publication_type.uid, publication_type.parent_uid, publication_type.value, publication_type.hide, publication_type.alias, publication_type.order_priority, publication_type.show_in_lists, publication_type.data_type, publication_type.cascade_data_type, publication_type.description, region.uid, region.parent_uid, region.value, region.hide, region.alias, region.order_priority, region.show_in_lists, region.data_type, region.cascade_data_type, region.description FROM (SELECT COUNT( publication_story.uid ) AS story_count, me.uid, me.name, me.short_name, me.url, me.description, me.publication_type_uid, me.region_uid, me.channel_type_uid, me.circulation, me.ave, me.comments FROM publication me LEFT JOIN story publication_story ON publication_story.publication_uid = me.uid WHERE ( ( name LIKE '%ZDNet%' OR short_name LIKE '%ZDNet%' ) ) GROUP BY me.uid ORDER BY name) me LEFT JOIN story publication_story ON publication_story.publication_uid = me.uid LEFT JOIN code_tree publication_type ON publication_type.uid = me.publication_type_uid LEFT JOIN code_tree region ON region.uid = me.region_uid WHERE ( ( name LIKE '%ZDNet%' OR short_name LIKE '%ZDNet%' ) ) ORDER BY name; The work-around I'm using at the moment is to remove the 'pre-fetch', which gives the following SQL: SELECT COUNT( publication_story.uid ), me.uid, me.name, me.short_name, me.url, me.description, me.publication_type_uid, me.region_uid, me.channel_type_uid, me.circulation, me.ave, me.comments FROM publication me LEFT JOIN story publication_story ON publication_story.publication_uid = me.uid WHERE ( ( name LIKE '%ZDNet%' OR short_name LIKE '%ZDNet%' ) ) GROUP BY me.uid ORDER BY name; Please let me know if you'd like any more details.
Download (untitled) / with headers
text/plain 892b
On Fri Mar 26 20:38:17 2010, SARGIE wrote: Show quoted text
> > Here is the Perl data structure of the query I would like: > > join => [qw/publication_story/], > select => [{ count => 'publication_story.uid' }, qw/me.uid me.name > me.short_name > me.url me.description me.publication_type_uid me.region_uid > me.channel_type_uid > me.circulation me.ave me.comments/,], > as => [qw/story_count uid name short_name url description > publication_type_uid > region_uid channel_type_uid circulation ave comments/], > group_by => [qw/me.uid/], > prefetch => [qw/publication_type region/], > > if I ask DBIx::Class to do it, it gets all messed up: > > Please let me know if you'd like any more details.
Yes, what is the actual problem? The query looks exactly the way it was designed to look. is there a DBD error, do you get incorrect results back, do you just find the query overly complicated...?
Download (untitled) / with headers
text/plain 532b
Show quoted text
> Yes, what is the actual problem? The query looks exactly the way it was > designed to look. is there a DBD error, do you get incorrect results > back, do you just find the query overly complicated...?
Sorry, I thought the repeat of the whole query would be red-flag enough. The actual problem in practice is the second query doesn't work: it returns a row for each of the rows it's meant to be grouping - publication_story, in this case. In practice that means the return of 2,562 rows instead of 8 for this particular query.
On Sun Mar 28 03:11:09 2010, SARGIE wrote: Show quoted text
> > Yes, what is the actual problem? The query looks exactly the way it
> was
> > designed to look. is there a DBD error, do you get incorrect results > > back, do you just find the query overly complicated...?
> > Sorry, I thought the repeat of the whole query would be red-flag > enough. The actual problem in > practice is the second query doesn't work: it returns a row for each > of the rows it's meant to be > grouping - publication_story, in this case. In practice that means the > return of 2,562 rows > instead of 8 for this particular query.
And how would prefetch of a has_many work on 8 rows? where is it going to pull the info for all the children from? I stoll dpn't understand what is the *ACTUAL* problem. Do you get an incorrect ->count ? Does the amount of objects from ->all/->next not meet your expectations of 8? If it is just the "repeated" query - please use some sql formatter and read the query carefully. You'll see where/how the prefetch in fact takes place.
Download (untitled) / with headers
text/plain 106b
As per IRC conversation, rejecting until a failing test case is supplied. Feel free to reopen at any time.


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.