Skip Menu |
 

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

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

People
Owner: Nobody in particular
Requestors: JHTHORSEN [...] cpan.org
Cc:
AdminCc:

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

Attachments
count_complex_with_having_and_invalid_as.t



I got an issue, when doing count() in both SQLite and Oracle database on a rather complex query. The part where it fails is when my select looks something like this: SELECT COUNT( * ) FROM ( SELECT me.colA, MAX(CASE kv.attr_name WHEN 'FOO' THEN upper(kv.attr_value) ELSE '' END) AS MAX(CASE kv__attr_name WHEN 'FOO' THEN upper(kv.attr_value) ELSE '' END) FROM table me LEFT JOIN kv kv ON kv.foo_id = me.foo_id WHERE ... ) So the issue is the "string" after "AS". The diff below fixes the problem. diff --git a/lib/DBIx/Class/ResultSet.pm b/lib/DBIx/Class/ResultSet.pm index 02bceb7..c684a64 100644 --- a/lib/DBIx/Class/ResultSet.pm +++ b/lib/DBIx/Class/ResultSet.pm @@ -1525,6 +1525,7 @@ sub _count_subq_rs { if ($colpiece =~ /\./ && $colpiece !~ /^$attrs->{alias}\./) { my $as = $colpiece; $as =~ s/\./__/; + $as =~ s/\W/_/g; $colpiece = \ sprintf ('%s AS %s', map { $sql_maker->_quote ($_) } ($colpiece, $as) ); } push @{$sub_attrs->{select}}, $colpiece;
Download (untitled) / with headers
text/plain 299b
... AS MAX(CASE kv__attr_name WHEN 'FOO' THEN upper(kv.attr_value) ... ^^ The column alias is not valid in Oracle, nor SQLite. Reason for this is that (qw/ ( ' . /, " ") and friends are not valid when specifying the alias. I think it should be safe to just have alphanumeric characters in the alias.
Attached failing test.
Subject: count_complex_with_having_and_invalid_as.t
use strict; use warnings; use lib qw(t/lib); use Test::More; use DBICTest; use DBIC::SqlMakerTest; use DBIC::DebugObj; my $schema = DBICTest->init_schema(); { my $aggregates = { foo => "MAX(CASE tracks.position WHEN 'foo' THEN tracks.position ELSE '' END)", bar => "MAX(CASE tracks.title WHEN 'foo' THEN tracks.title ELSE '' END)", }; my $rs = $schema->resultset("CD")->search(undef, { 'join' => 'tracks', '+select' => [ map { \ "$_"} values %$aggregates, ], '+as' => [keys %$aggregates], 'group_by' => 'me.artist', 'having' => { "MAX(CASE tracks.title WHEN 'foo' THEN upper(tracks.title) ELSE '' END)" => 'foo' }, 'page' => 1, 'rows' => 10, } ); ok(defined eval { $rs->count }, 'Complex query with " AS ..." did not die') or diag $@; }
Download (untitled) / with headers
text/plain 1.1k
Not sure if we ever spoke about this on irc - but this bugreport is invalid. You are in essence supplying a HAVING clause that reads: HAVING $table.$column = ? where ? == 'foo' $table == "MAX(CASE tracks" $columns == "title WHEN 'foo' THEN upper(tracks.title) ELSE '' END)" With the following modification your test works fine (and fires the proper sql): --- a/t/count/count_complex_with_having_and_invalid_as.t +++ b/t/count/count_complex_with_having_and_invalid_as.t @@ -22,7 +22,9 @@ my $schema = DBICTest->init_schema(); '+select' => [ map { \ "$_"} values %$aggregates, ], '+as' => [keys %$aggregates], 'group_by' => 'me.artist', - 'having' => \"MAX(CASE tracks.title WHEN 'foo' THEN upper(tracks.title) ELSE '' END) = 'foo'", + 'having' => { + "MAX(CASE tracks.title WHEN 'foo' THEN upper(tracks.title) ELSE '' END)" => 'foo' + }, 'page' => 1, 'rows' => 10, } Please feel free to reopen this ticket if you feel there is more to it than this. Cheers!
Download (untitled) / with headers
text/plain 582b
I don't get your diff. Is it reversed? Looks like "+" is what I got in the example above... Anyway, I tried both and it works nicely for all() and next(), but not for count(). Which version of DBIC do you have? Also, what about placeholders? The data comes from a web-form so I'm not too eager to put it directly inside a \"scalar ref". (Afraid of Bobby Drop-Tables, you know;)) What I see on the count() query is that it adds some strange columns to the "SELECT ...". Why does count() do anything else than just count(*)? Why does it need to have all those columns in the query?
Download (untitled) / with headers
text/plain 189b
And yes: I'm the guy from IRC. Sadly I've been stuck in Windows and didn't bother to find a proper IRC client. I'll hopefully be back in Ubuntu tomorrow and also on irc.perl.org#dbix-class.
Subject: Re: [rt.cpan.org #66165]
Date: Tue, 26 Apr 2011 15:41:30 +0200
To: bug-DBIx-Class [...] rt.cpan.org
From: Peter Rabbitson <ribasushi [...] cpan.org>
Download (untitled) / with headers
text/plain 431b
Jan Henning Thorsen via RT wrote: Show quoted text
> Queue: DBIx-Class > Ticket <URL: https://rt.cpan.org/Ticket/Display.html?id=66165 > > > And yes: I'm the guy from IRC. Sadly I've been stuck in Windows and > didn't bother to find a proper IRC client. I'll hopefully be back in > Ubuntu tomorrow and also on irc.perl.org#dbix-class.
http://search.cpan.org/~abraxxa/DBIx-Class-0.08127/lib/DBIx/Class.pm#GETTING_HELP/SUPPORT 2nd link :)
Subject: DBIC does not guess literals in select/where/having/group/etc
Download (untitled) / with headers
text/plain 889b
On Tue Apr 26 09:04:08 2011, JHTHORSEN wrote: Show quoted text
> I don't get your diff. Is it reversed? Looks like "+" is what I got in > the example above...
It's not reversed, I fucked up the having clause :) Show quoted text
> Anyway, I tried both and it works nicely for all() and next(), but not > for count(). Which version of DBIC do you have?
Didn't work how? Show quoted text
> Also, what about placeholders? The data comes from a web-form so I'm not > too eager to put it directly inside a \"scalar ref". (Afraid of Bobby > Drop-Tables, you know;))
For placeholders one has this \[ $sql_with_placeholders, [ opt1 => val1 ], [ opt2 => val2 ] ... ] Show quoted text
> What I see on the count() query is that it adds some strange columns to > the "SELECT ...". Why does count() do anything else than just count(*)? > Why does it need to have all those columns in the query?
I am not sure I am reading the right thing - I only see count(*)...
Download (untitled) / with headers
text/plain 186b
I am closing this ticket as a "wontfix" reject, as all the information so far indicates abuse of the API, and not an actual bug. Feel free to reopen this ticket with more info/questions.


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.