| Subject: | -as option breaks a Select statement |
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 |
Message body not shown because it is not plain text.
| Subject: | as_bug.pl |
#!/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;