| Subject: | Problem with -group_by and raw SQL and bindings in -where |
| Date: | Fri, 12 Jul 2013 11:21:01 -0700 |
| To: | "bug-SQL-Abstract-More [...] rt.cpan.org" <bug-SQL-Abstract-More [...] rt.cpan.org> |
| From: | Allan Cochrane <Allan.Cochrane [...] arm.com> |
Hi,
If the -where clause contains raw SQL and that SQL contains an ORDER BY statement then the GROUP BY clause is inserted in that raw SQL and not the generated SQL from SQL::Abstract::More. This causes a problem for the SQL parser upstream.
I understand that in my example the ORDER BY is irrelevant but the raw SQL is what I have to work with and would prefer it not to be touched. My workaround is to append the GROUP BY clause by hand and not via S::A::M
Example:
[brodie@cam-flume1 merge_ng]$ cat ~/sqlabstractmore.pl
#! perl
use 5.18.0;
use SQL::Abstract::More;
my $sql_to_be_left_alone = "/* BEGIN */ SELECT revision_id FROM (SELECT * FROM t1 WHERE conds ORDER BY col1 LIMIT 300) rev_ids) /* END */";
my @bindings = (3, 1, 4, 1, 5);
my $rev = [$sql_to_be_left_alone,@bindings];
my $sql = SQL::Abstract::More->new(limit_dialect => 'LimitOffset');
my ($stmt,@new_bindings) = $sql->select(
-from => 't2',
-columns => 'COUNT(*),revision_fk',
-where => {revision_fk => {-in => \$rev}},
-group_by => 'revision_fk',
);
say "Cannot find original SQL in generated statement" if index($stmt,$sql_to_be_left_alone) == -1;
say " $sql_to_be_left_alone";
say "SQL = $stmt";
say "Bindings = @bindings";
Regards,
Allan
-- IMPORTANT NOTICE: The contents of this email and any attachments are confidential and may also be privileged. If you are not the intended recipient, please notify the sender immediately and do not disclose the contents to any other person, use it for any purpose, or store or copy the information in any medium. Thank you.