| 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> |
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