Skip Menu |
 

This queue is for tickets about the SQL-Abstract CPAN distribution.

Report information
The Basics
Id: 117234
Status: open
Priority: 0/
Queue: SQL-Abstract

People
Owner: Nobody in particular
Requestors: gjtunley [...] gmail.com
Cc:
AdminCc:

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



Subject: Issues with generated SQL from SQL::Abstract::Tree
Date: Fri, 26 Aug 2016 09:21:01 +0100
To: bug-SQL-Abstract [...] rt.cpan.org
From: Gareth Tunley <gjtunley [...] gmail.com>
Download (untitled) / with headers
text/plain 2.2k
Hi, I am using SQL::Abstract::Tree to "beautify" SQL for outputting to a query log for debugging purposes (pull out individual queries for re-execution/tuning), this is being called in a subclass of DBIx::Class::Storage::Statistics invoked as a debug object. sub query_start { my ($self, $sql, @params) = @_; my $tree = SQL::Abstract::Tree->new( fill_in_placeholders => 1, indent_amount => 8, indentmap => { 'from' => 0, 'left join' => 0, 'join' => 0, 'on' => 1, }, indent_string => ' ', placeholder_surround => [], profile => 'console_monochrome', ); $log->debug('Executing: ' . $tree->format($sql, \@params)); return; } The result source which is generating the SQL that has issues is a virtual result source: package My::Schema::SearchableJob; use strict; use warnings; use utf8; # Include common stuff like inflate datetime use base 'My::Schema::SchemaBase'; __PACKAGE__->table_class('DBIx::Class::ResultSource::View'); __PACKAGE__->table('jobs'); __PACKAGE__->result_source_instance->is_virtual(1); __PACKAGE__->result_source_instance->view_definition( "SELECT j.*, CONCAT_WS('_', j.CustomerID, j.CustomerDepartmentID) AS CombinedID, " . "CONCAT_WS(' - ', c.CustomerName, d.CustomerDepartmentName) AS CombinedName, " . 'FROM jobs j ' . 'JOIN customers c ON c.CustomerID = j.CustomerID ' . 'LEFT JOIN departments d ON d.CustomerDepartmentID = j.CustomerDepartmentID' ); When the SQL is generated for the above it outputs as: ( SELECT j.*, CONCAT_WS '_', j.CustomerID, j.CustomerDepartmentID AS CombinedID, CONCAT_WS ' - ', c.CustomerName, d.CustomerDepartmentName AS CombinedName FROM jobs j JOIN customers c ON c.CustomerID = c.CustomerID LEFT JOIN departments d ON d.CustomerDepartmentID = j.CustomerDepartmentID ) me Where the CONCAT_WS has lost it's brackets. Additionally a number of: Use of uninitialized value $left in concatenation (.) or string at /usr/local/share/perl/5.18.2/SQL/Abstract/Tree.pm line 573. Use of uninitialized value $right in concatenation (.) or string at /usr/local/share/perl/5.18.2/SQL/Abstract/Tree.pm line 573. are outputted to STDERR. Thanks Gareth -- Gareth Tunley - gjtunley@gmail.com
Subject: Re: [rt.cpan.org #117234] Issues with generated SQL from SQL::Abstract::Tree
Date: Fri, 26 Aug 2016 11:16:35 +0200
To: bug-SQL-Abstract [...] rt.cpan.org
From: Peter Rabbitson <ribasushi [...] cpan.org>
Download (untitled) / with headers
text/plain 937b
On 08/26/2016 10:21 AM, Gareth Tunley via RT wrote: Show quoted text
> Fri Aug 26 04:21:13 2016: Request 117234 was acted upon. > Transaction: Ticket created by gjtunley@gmail.com > Queue: SQL-Abstract > Subject: Issues with generated SQL from SQL::Abstract::Tree
Bug confirmed, with a simpler repro case as follows (patch against SQLA master) diff --git a/t/14roundtrippin.t b/t/14roundtrippin.t index 2dd026b..ce7d672 100644 --- a/t/14roundtrippin.t +++ b/t/14roundtrippin.t @@ -30,2 +30,3 @@ my @sql = ( "SELECT [me].[id], ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS [rno__row__index] FROM ( SELECT [me].[id] FROM [LogParents] [me]) [me]", + q|SELECT j.*, CONCAT_WS('_', j.CustomerID, j.CustomerDepartmentID) AS CombinedID|, # deliberate batshit insanity I have a round of fixes to the parser subsystem scheduled around the 1st week of Sept. Will get to this particular issue then if nobody beats me to it earlier. Cheers
Subject: Re: [rt.cpan.org #117234] Issues with generated SQL from SQL::Abstract::Tree
Date: Mon, 5 Sep 2016 22:01:30 +0100
To: bug-SQL-Abstract [...] rt.cpan.org
From: Gareth Tunley <gjtunley [...] gmail.com>
Download (untitled) / with headers
text/plain 1.3k
Thanks Peter - I really appreciate it. Thank you also for all your work on DBIx::Class - I know in OSS it's so often unappreciated so I wanted to make sure I said thank you for your efforts. On 26 August 2016 at 10:17, Peter Rabbitson via RT < bug-SQL-Abstract@rt.cpan.org> wrote: Show quoted text
> <URL: https://rt.cpan.org/Ticket/Display.html?id=117234 > > > On 08/26/2016 10:21 AM, Gareth Tunley via RT wrote:
> > Fri Aug 26 04:21:13 2016: Request 117234 was acted upon. > > Transaction: Ticket created by gjtunley@gmail.com > > Queue: SQL-Abstract > > Subject: Issues with generated SQL from SQL::Abstract::Tree
> > Bug confirmed, with a simpler repro case as follows (patch against SQLA > master) > > diff --git a/t/14roundtrippin.t b/t/14roundtrippin.t > index 2dd026b..ce7d672 100644 > --- a/t/14roundtrippin.t > +++ b/t/14roundtrippin.t > @@ -30,2 +30,3 @@ my @sql = ( > "SELECT [me].[id], ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS > [rno__row__index] FROM ( SELECT [me].[id] FROM [LogParents] [me]) [me]", > + q|SELECT j.*, CONCAT_WS('_', j.CustomerID, j.CustomerDepartmentID) AS > CombinedID|, > # deliberate batshit insanity > > I have a round of fixes to the parser subsystem scheduled around the 1st > week of Sept. Will get to this particular issue then if nobody beats me > to it earlier. > > Cheers > > >
-- Gareth Tunley - gjtunley@gmail.com


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.