Skip Menu |
 

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

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

People
Owner: Nobody in particular
Requestors: aholland [...] ecstuning.com
Cc:
AdminCc:

Bug Information
Severity: Normal
Broken in: 0.082810
Fixed in: (no value)



Subject: Invalid SQL from paging total
Download (untitled) / with headers
text/plain 1.2k
I am running a query like this with some aggregate functions and paging: my $rs = $schema->resultset("items")->search([ {}, { 'join' => [ "counts" ], '+select' => [ { 'sum' => "counts.qty" }, ], '+as' => [ "expected", ], 'group_by' => [ "me.countID" ], 'having' => { 'finalCount' => { '-ident' => "expected" }, }, 'rows' => 100, 'page' => 1, }, ]); This generates SQL like so: SELECT ... FROM ( SELECT me.itemID, ..., me.finalCount, SUM( counts.qty ) AS expected FROM item me LEFT JOIN count counts ON counts.itemID = me.itemID GROUP BY me.itemID HAVING finalCount = expected LIMIT 100 ) me LEFT JOIN count counts ON counts.itemID = me.itemID; However, when I try to get the total number of items (to calculate the total # of pages available), using: my $itemCount = $rs->pager->total_entries; It generates this SQL, which results in "Unknown column 'expected' in 'having clause'": SELECT COUNT( * ) FROM ( SELECT me.itemID, me.finalCount FROM items LEFT JOIN count counts ON counts.itemID = me.itemID GROUP BY me.itemID HAVING finalCount = expected ) me LEFT JOIN count counts ON counts.itemID = me.itemID
CC: undisclosed-recipients:;
Subject: Re: [rt.cpan.org #100949] Invalid SQL from paging total
Date: Mon, 29 Dec 2014 14:30:37 +0100
To: bug-DBIx-Class [...] rt.cpan.org
From: ilmari [...] ilmari.org (Dagfinn Ilmari Mannsåker)
Download (untitled) / with headers
text/plain 1.4k
"Andrew Holland via RT" <bug-DBIx-Class@rt.cpan.org> writes: Show quoted text
> I am running a query like this with some aggregate functions and paging: > > my $rs = $schema->resultset("items")->search([ > {}, > { > 'join' => [ "counts" ], > '+select' => [ > { 'sum' => "counts.qty" }, > ], > '+as' => [ > "expected", > ],
This form of +select/+as is not guaranteed to alias the actual expression in the SQL, for that you need: '+select' => [ { sum => "counts.qty", -as => "expected" } ] In general, it's even better to use +columns instead, since that keeps the SQL expression together with the DBIC-side column alias. '+columns' => { expected => { sum => "counts.qty", -as => "expected" }, } The -as clause will also make DBIC copy the select clause into the COUNT subquery. Show quoted text
> 'group_by' => [ "me.countID" ], > 'having' => { > 'finalCount' => { '-ident' => "expected" }, > },
Just FYI, referencing an alias from the 'select' list in the 'having' clause is not portable. E.g. PostgreSQL doesn't allow this, you have to repeat the entire expression in 'having'. -- - Twitter seems more influential [than blogs] in the 'gets reported in the mainstream press' sense at least. - Matt McLeod - That'd be because the content of a tweet is easier to condense down to a mainstream media article. - Calle Dybedahl
Subject: Re: [rt.cpan.org #100949] Invalid SQL from paging total
Date: Mon, 29 Dec 2014 14:47:56 +0100
To: bug-DBIx-Class [...] rt.cpan.org
From: Peter Rabbitson <ribasushi [...] cpan.org>
Download (untitled) / with headers
text/plain 734b
On 12/29/2014 02:30 PM, (Dagfinn Ilmari Mannsåker) via RT wrote: Show quoted text
> Just FYI, referencing an alias from the 'select' list in the 'having' > clause is not portable. E.g. PostgreSQL doesn't allow this, you have > to repeat the entire expression in 'having'.
This. In general you always want to repeat the entire clause in "having", especially since the RDBMS query parser will do so anyway (luckily with an SQL generator like DBIC this is trivial). The original query working is an implementation detail (DBIC internally knows it needs to AS a snippet, and it uses the first available alias, which happens to be what you supplied in +as) Let us know if this answers your question fully, and whether the ticket may be closed.
From: aholland [...] ecstuning.com
Download (untitled) / with headers
text/plain 1007b
On Mon Dec 29 08:48:11 2014, RIBASUSHI wrote: Show quoted text
> On 12/29/2014 02:30 PM, (Dagfinn Ilmari Mannsåker) via RT wrote:
> > Just FYI, referencing an alias from the 'select' list in the 'having' > > clause is not portable. E.g. PostgreSQL doesn't allow this, you have > > to repeat the entire expression in 'having'.
> > This. In general you always want to repeat the entire clause in > "having", especially since the RDBMS query parser will do so anyway > (luckily with an SQL generator like DBIC this is trivial). > > The original query working is an implementation detail (DBIC internally > knows it needs to AS a snippet, and it uses the first available alias, > which happens to be what you supplied in +as) > > Let us know if this answers your question fully, and whether the ticket > may be closed.
Worked around the issue by moving `-ident => "SUM(counts.qty)"` into the HAVING clause. Didn't realize that aliases in the HAVING clause was unique to certain implementations. You can close this.
CC: undisclosed-recipients:;
Subject: Re: [rt.cpan.org #100949] Invalid SQL from paging total
Date: Mon, 12 Jan 2015 14:14:45 +0100
To: bug-DBIx-Class [...] rt.cpan.org
From: ilmari [...] ilmari.org (Dagfinn Ilmari Mannsåker)
Download (untitled) / with headers
text/plain 980b
"Andrew Holland via RT" <bug-DBIx-Class@rt.cpan.org> writes: Show quoted text
> Worked around the issue by moving `-ident => "SUM(counts.qty)"` into > the HAVING clause. Didn't realize that aliases in the HAVING clause > was unique to certain implementations. You can close this.
This will break if you turn on quote_names, since it'll quote the whole SUM() expression as a single identifier. For SQL that can't be represented in SQL::Abstract syntax, use the \[$sql, @bind] form: https://metacpan.org/pod/SQL::Abstract#Literal-SQL-with-placeholders-and-bind-values-subqueries https://metacpan.org/pod/DBIx::Class::ResultSet#DBIC-BIND-VALUES For example: having => \['SUM(counts.qty) = ?', $expected], -- - Twitter seems more influential [than blogs] in the 'gets reported in the mainstream press' sense at least. - Matt McLeod - That'd be because the content of a tweet is easier to condense down to a mainstream media article. - Calle Dybedahl
Not a DBIC bug, marking as rejected.


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.