Skip Menu |
 

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

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

People
Owner: Nobody in particular
Requestors: icestar [...] inbox.ru
Cc:
AdminCc:

Bug Information
Severity: Critical
Broken in: 0.08123
Fixed in: 0.08125



Subject: -as option breaks a Select statement
Download (untitled) / with headers
text/plain 2.3k
Hello. I've found the bug related with -as option. Please, consider the next result set example. my $stat_rs = $schema->resultset('Statistics')->search( { 'me.targetLineId' => '554BA6E0-B410-11DF-BF0D-ED7CFF98A82D', }, { select => [ 'me.sourceLineId' ], as => [qw/ line_id /], } ); $stat_rs = $stat_rs->search(undef, { '+select' => [ { SUM => 'me.impressions', -as => 'impressions' }, { SUM => 'me.clicks', -as => 'clicks' }, { SUM => 'me.leads_reach', -as => 'leads_reach' }, { SUM => 'me.leads', -as => 'leads_uniq' }, { SUM => 'me.leads_nu', -as => 'leads_nu' }, ], '+as' => [qw/ impressions clicks leads_reach leads_uniq leads_nu /], } ); $stat_rs = $stat_rs->search(undef, { '+select' => ['me.profileId', 'me.bannerId'], '+as' => [qw/profile_id banner_id/], group_by => ['me.sourceLineId'], having => [ '-and' => { impressions => { '!=', 0 }}, { clicks => { '!=', 0 }}, { leads_reach => { '!=', 0 }}, { leads_uniq => { '!=', 0 }}, { leads_nu => { '!=', 0 }}, ], } ); I'm using -as option in the first modification of start_rs result set. If I try to generate Select statement after that it will be correct. (SELECT me.sourceLineId, SUM( me.impressions ) AS impressions, SUM( me.clicks ) AS clicks, SUM( me.leads_reach ) AS leads_reach, SUM( me.leads ) AS leads_uniq, SUM( me.leads_nu ) AS leads_nu FROM Statistics me WHERE ( me.targetLineId = ? ) But if I want to do another modification to add some more columns to the result set via +select & +as options I will get this: SELECT me.sourceLineId, ( ) AS impressions, SUM( me.impressions ), ( ) AS clicks, SUM( me.clicks ), ( ) AS leads_reach, SUM( me.leads_reach ), ( ) AS leads_uniq, SUM( me.leads ), ( ) AS leads_nu, SUM( me.leads_nu ), me.profileId, me.bannerId FROM Statistics me WHERE ( me.targetLineId = ? ) GROUP BY me.sourceLineId HAVING ( impressions != ? OR clicks != ? OR leads_reach != ? OR leads_uniq != ? OR leads_nu != ? ) And the exception "You have an error in your SQL syntax". In the attachments you can find the test schema and test script file to reproduce this bug.
Subject: TestSchema.tgz
Download TestSchema.tgz
application/x-compressed-tar 578b

Message body not shown because it is not plain text.

Subject: as_bug.pl
Download as_bug.pl
text/x-perl 1.6k
#!/usr/bin/perl use strict; use warnings; use Test::Schema; use Data::Dumper; my $db_file = 'test.db'; my $dsn = "dbi:SQLite:dbname=$db_file"; my $user = 'test'; my $pass = 'test'; unlink $db_file if -e $db_file; my $schema = Test::Schema->connect($dsn, $user, $pass, {AutoCommit => 1}); $schema->deploy(); $schema->storage->debug(1); my $stat_rs = $schema->resultset('Statistics')->search( { 'me.targetLineId' => '554BA6E0-B410-11DF-BF0D-ED7CFF98A82D', }, { select => [ 'me.sourceLineId' ], as => [qw/ line_id /], } ); $stat_rs = $stat_rs->search(undef, { '+select' => [ { SUM => 'me.impressions', -as => 'impressions' }, { SUM => 'me.clicks', -as => 'clicks' }, { SUM => 'me.leads_reach', -as => 'leads_reach' }, { SUM => 'me.leads', -as => 'leads_uniq' }, { SUM => 'me.leads_nu', -as => 'leads_nu' }, ], '+as' => [qw/ impressions clicks leads_reach leads_uniq leads_nu /], } ); print "Correct SQL: '", Dumper($stat_rs->as_query), "'\n"; $stat_rs = $stat_rs->search(undef, { '+select' => ['me.profileId', 'me.bannerId'], '+as' => [qw/profile_id banner_id/], group_by => ['me.sourceLineId'], having => [ '-and' => { impressions => { '!=', 0 }}, { clicks => { '!=', 0 }}, { leads_reach => { '!=', 0 }}, { leads_uniq => { '!=', 0 }}, { leads_nu => { '!=', 0 }}, ], } ); print "Wrong SQL: '", Dumper($stat_rs->as_query), "'\n"; exit 0;
Download (untitled) / with headers
text/plain 737b
On Fri Sep 10 05:32:13 2010, Alien wrote: Show quoted text
> Hello. > I've found the bug related with -as option. > Please, consider the next result set example.
Show quoted text
> SELECT me.sourceLineId, ( ) AS impressions, SUM( me.impressions ), ( ) > AS clicks, SUM( me.clicks ), ( ) AS leads_reach, SUM( me.leads_reach ), > ( ) AS leads_uniq, SUM( me.leads ), ( ) AS leads_nu, SUM( me.leads_nu > ), me.profileId, me.bannerId FROM Statistics me WHERE ( me.targetLineId > = ? ) GROUP BY me.sourceLineId HAVING ( impressions != ? OR clicks != ? > OR leads_reach != ? OR leads_uniq != ? OR leads_nu != ? ) > > And the exception "You have an error in your SQL syntax".
Interesting... This one I'll try to poke tomorrow, looks like a simple omission somewhere.
Download (untitled) / with headers
text/plain 214b
This pickle is finally fixed. The size of the patch should explain the delay. Cheers! http://git.shadowcat.co.uk/gitweb/gitweb.cgi?p=dbsrgits/DBIx-Class.git;a=commitdiff;h=a3a173775d6f40c2e1b78d63e8fd0961b11fa6c2


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.