| Subject: | Select with Subquery and Join -> Illegal parameter number exception |
Hello.
I've just upgraded to the last version of DBIx::Class and got an
exception (bind_param failed: Illegal parameter number) in the test
which worked before. I tried to reproduce this bug in a small test
script and found the regularity:
I get this bug only when I write Select statement with sub queries and
join.
Query:
my $subquery = $schema->resultset('Line')->search(
{
'line.type' => 'sitePixel',
'line.mediaplanId' => { '=' => \'me.id' },
},
{ alias => 'line' }
);
my $query = $schema->resultset('Mediaplan')->search(
{
'me.title' => 'Test Mediaplan'
},
{
join => [qw/advertiser/],
select => [
'me.title',
'me.id',
'advertiser.title',
$subquery->get_column('startDate')->min_rs->as_query,
$subquery->get_column('stopDate')->max_rs->as_query,
],
as => [qw/
title
id
advertiser
min_date
max_date
/],
}
);
The result Select statement is:
SELECT me.title, me.id, advertiser.title, (SELECT MIN( line.startDate )
FROM Line line WHERE ( ( line.mediaplanId = me.id AND line.type
= ? ) )), (SELECT MAX( line.stopDate ) FROM Line line WHERE
( ( line.mediaplanId = me.id AND line.type = ? ) )) FROM Mediaplan me
INNER JOIN Advertiser advertiser ON advertiser.id = me.advertiserId
WHERE ( me.title = ? ): 'sitePixel', 'sitePixel', 'sitePixel',
'sitePixel', 'Test Mediaplan'
I get 2 extra 'sitePixel' parameters and the mentioned exception.
If I remove join the result query will be:
SELECT me.title, me.id, (SELECT MIN( line.startDate ) FROM Line line
WHERE ( ( line.mediaplanId = me.id AND line.type = ? ) )), (SELECT MAX
( line.stopDate ) FROM Line line WHERE ( ( line.mediaplanId = me.id AND
line.type = ? ) )) FROM Mediaplan me WHERE ( me.title = ? ):
'sitePixel', 'sitePixel', 'Test Mediaplan'
Everything is right here.
Sorry, but I can not say in which version this bug appeared and which
version I had before upgrading.
I've attached the test schema and the script to the report to reproduce
this bug by yourself.
| Subject: | subquery_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 $count = $schema->resultset('Mediaplan')->count;
print "The number of rows is '$count'\n";
my $line_rs = $schema->resultset('Line')->search(
{
'line.type' => 'sitePixel',
'line.mediaplanId' => { '=' => \'me.id' },
},
{ alias => 'line' }
);
my $subquery = $line_rs->get_column('startDate')->min_rs->as_query;
warn "SUBQUERY: '".Dumper($subquery)."'\n";
my $mediaplan_rs = $schema->resultset('Mediaplan')->search(
{
'me.title' => 'Test Mediaplan'
},
{
join => [qw/advertiser/],
select => [
'me.title',
'me.id',
'advertiser.title',
$line_rs->get_column('startDate')->min_rs->as_query,
$line_rs->get_column('stopDate')->max_rs->as_query,
],
as => [qw/
title
id
advertiser
min_date
max_date
/],
}
);
my $result = [ map +{ $_->get_columns() }, $mediaplan_rs->all];
print Dumper($result);
exit 0;
| Subject: | TestSchema.tgz |
Message body not shown because it is not plain text.