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



MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
X-Mailer: MIME-tools 5.427 (Entity 5.427)
Content-Disposition: inline
Content-Transfer-Encoding: binary
X-RT-Original-Encoding: utf-8
Content-Length: 1052
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;
MIME-Version: 1.0
X-Mailer: MIME-tools 5.427 (Entity 5.427)
Content-Disposition: inline
Content-Type: text/plain; charset="UTF-8"
Message-ID: <rt-3.8.HEAD-26844-1298634865-915.66165-0-0 [...] rt.cpan.org>
Content-Transfer-Encoding: binary
X-RT-Original-Encoding: utf-8
Content-Length: 299
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.
MIME-Version: 1.0
In-Reply-To: <rt-3.8.HEAD-26844-1298634865-915.66165-0-0 [...] rt.cpan.org>
X-Mailer: MIME-tools 5.427 (Entity 5.427)
References: <rt-3.8.HEAD-26844-1298634865-915.66165-0-0 [...] rt.cpan.org>
Content-Type: multipart/mixed; boundary="----------=_1298637889-26835-23"
Message-ID: <rt-3.8.HEAD-26835-1298637889-710.66165-0-0 [...] rt.cpan.org>
X-RT-Original-Encoding: utf-8
Content-Length: 0
Content-Disposition: inline
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: binary
X-RT-Original-Encoding: utf-8
Content-Length: 22
Attached failing test.
MIME-Version: 1.0
Subject: count_complex_with_having_and_invalid_as.t
X-Mailer: MIME-tools 5.427 (Entity 5.427)
Content-Type: text/troff; name="count_complex_with_having_and_invalid_as.t"
Content-Disposition: inline; filename="count_complex_with_having_and_invalid_as.t"
Content-Transfer-Encoding: binary
Content-Length: 1014
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 $@; }
MIME-Version: 1.0
In-Reply-To: <rt-3.8.HEAD-26835-1298637889-710.66165-0-0 [...] rt.cpan.org>
X-Mailer: MIME-tools 5.427 (Entity 5.427)
Content-Disposition: inline
References: <rt-3.8.HEAD-26844-1298634865-915.66165-0-0 [...] rt.cpan.org> <rt-3.8.HEAD-26835-1298637889-710.66165-0-0 [...] rt.cpan.org>
Content-Type: text/plain; charset="UTF-8"
Message-ID: <rt-3.8.HEAD-26840-1300662943-1485.66165-0-0 [...] rt.cpan.org>
Content-Transfer-Encoding: binary
X-RT-Original-Encoding: utf-8
Content-Length: 1172
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!
MIME-Version: 1.0
X-Mailer: MIME-tools 5.427 (Entity 5.427)
Content-Disposition: inline
Content-Type: text/plain; charset="UTF-8"
Message-ID: <rt-3.8.HEAD-18805-1303823047-1879.66165-0-0 [...] rt.cpan.org>
Content-Transfer-Encoding: binary
X-RT-Original-Encoding: utf-8
Content-Length: 582
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?
MIME-Version: 1.0
X-Mailer: MIME-tools 5.427 (Entity 5.427)
Content-Disposition: inline
Content-Type: text/plain; charset="UTF-8"
Message-ID: <rt-3.8.HEAD-18805-1303823136-1394.66165-0-0 [...] rt.cpan.org>
Content-Transfer-Encoding: binary
X-RT-Original-Encoding: utf-8
Content-Length: 189
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.
From ribasushi [...] cpan.org Tue Apr 26 09: 41:45 2011
MIME-Version: 1.0
X-Spam-Status: No, score=-6.543 tagged_above=-99.9 required=10 tests=[AWL=0.357, BAYES_00=-1.9, RCVD_IN_DNSWL_HI=-5] autolearn=ham
In-Reply-To: <rt-3.8.HEAD-18805-1303823137-1484.66165-5-0 [...] rt.cpan.org>
X-Spam-Flag: NO
References: <RT-Ticket-66165 [...] rt.cpan.org> <rt-3.8.HEAD-18805-1303823137-1484.66165-5-0 [...] rt.cpan.org>
X-Virus-Scanned: Debian amavisd-new at bestpractical.com
Message-ID: <4DB6CB8A.7000500 [...] cpan.org>
Content-Type: text/plain; charset=UTF-8; format=flowed
X-RT-Original-Encoding: utf-8
X-Spam-Score: -6.543
Received: from localhost (localhost [127.0.0.1]) by hipster.bestpractical.com (Postfix) with ESMTP id 6835761E007 for <cpan-bug+DBIx-Class [...] hipster.bestpractical.com>; Tue, 26 Apr 2011 09:41:45 -0400 (EDT)
Received: from hipster.bestpractical.com ([127.0.0.1]) by localhost (hipster.bestpractical.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 78SZU5yVf8YU for <cpan-bug+DBIx-Class [...] hipster.bestpractical.com>; Tue, 26 Apr 2011 09:41:40 -0400 (EDT)
Received: from la.mx.develooper.com (x1.develooper.com [207.171.7.70]) by hipster.bestpractical.com (Postfix) with SMTP id 3DE9B61E003 for <bug-DBIx-Class [...] rt.cpan.org>; Tue, 26 Apr 2011 09:41:40 -0400 (EDT)
Received: (qmail 7102 invoked by uid 103); 26 Apr 2011 13:41:39 -0000
Received: from x16.dev (10.0.100.26) by x1.dev with QMQP; 26 Apr 2011 13:41:39 -0000
Received: from arx.rabbit.us (HELO arx.rabbit.us) (76.244.88.238) by 16.mx.develooper.com (qpsmtpd/0.80/v0.80-19-gf52d165) with ESMTP; Tue, 26 Apr 2011 06:41:36 -0700
Received: from [10.0.13.6] (unknown [10.0.13.6]) by arx.rabbit.us (Postfix) with ESMTP id 07452D815C for <bug-DBIx-Class [...] rt.cpan.org>; Tue, 26 Apr 2011 09:41:31 -0400 (EDT)
Delivered-To: cpan-bug+DBIx-Class [...] hipster.bestpractical.com
User-Agent: Mozilla-Thunderbird 2.0.0.24 (X11/20100328)
Subject: Re: [rt.cpan.org #66165]
Return-Path: <ribasushi [...] cpan.org>
X-Spam-Check-BY: 16.mx.develooper.com
X-Original-To: cpan-bug+DBIx-Class [...] hipster.bestpractical.com
X-RT-Mail-Extension: dbix-class
Date: Tue, 26 Apr 2011 15:41:30 +0200
X-Spam-Level:
To: bug-DBIx-Class [...] rt.cpan.org
Content-Transfer-Encoding: 7bit
From: Peter Rabbitson <ribasushi [...] cpan.org>
RT-Message-ID: <rt-3.8.HEAD-18812-1303825306-1374.66165-0-0 [...] rt.cpan.org>
Content-Length: 431
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 :)
MIME-Version: 1.0
Subject: DBIC does not guess literals in select/where/having/group/etc
In-Reply-To: <rt-3.8.HEAD-18805-1303823047-1879.66165-0-0 [...] rt.cpan.org>
X-Mailer: MIME-tools 5.427 (Entity 5.427)
Content-Disposition: inline
References: <rt-3.8.HEAD-18805-1303823047-1879.66165-0-0 [...] rt.cpan.org>
Content-Type: text/plain; charset="UTF-8"
Message-ID: <rt-3.8.HEAD-18805-1304151000-1350.66165-0-0 [...] rt.cpan.org>
Content-Transfer-Encoding: binary
X-RT-Original-Encoding: utf-8
Content-Length: 889
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(*)...
MIME-Version: 1.0
In-Reply-To: <rt-3.8.HEAD-18805-1304151000-1350.66165-0-0 [...] rt.cpan.org>
X-Mailer: MIME-tools 5.427 (Entity 5.427)
Content-Disposition: inline
References: <rt-3.8.HEAD-18805-1303823047-1879.66165-0-0 [...] rt.cpan.org> <rt-3.8.HEAD-18805-1304151000-1350.66165-0-0 [...] rt.cpan.org>
Content-Type: text/plain; charset="UTF-8"
Message-ID: <rt-3.8.HEAD-18810-1304771429-1258.66165-0-0 [...] rt.cpan.org>
Content-Transfer-Encoding: binary
X-RT-Original-Encoding: utf-8
Content-Length: 186
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.