| Subject: | Error parsing Postgres tstzrange |
| Date: | Mon, 13 Jul 2020 08:53:12 +0200 |
| To: | bug-SQL-Abstract [...] rt.cpan.org |
| From: | Cameron Daniel <cam.daniel [...] gmail.com> |
SQL::Abstract throws an error parsing queries containing the Postgres
tstzrange function (and possibly range types bracketed data format in
general).
A simple reproduction case, the comment at the top is a link to a
prepared fiddle to see the SQL working. I think it's the mismatched
brace types in the range that's throwing out the parser
#!/usr/bin/env perl
# https://www.db-fiddle.com/f/qJQZrWF2CGJKiPFVSYMHUh/0
use feature ':5.20';
use strict;
use warnings;
use SQL::Abstract;
use SQL::Abstract::Tree;
say "SQL::Abstract version: $SQL::Abstract::VERSION";
my $sql = <<'SQL';
SELECT
me.foo_id,
me.name,
related.name
FROM foo me
LEFT JOIN foo_bar ON (
foo_bar.active <@ tstzrange(now(), NULL, '(]')
AND foo_bar.foo_id = me.foo_id
)
LEFT JOIN bar related ON related.bar_id = foo_bar.bar_id
WHERE
related.value > 1;
SQL
my $sqlat = SQL::Abstract::Tree->new({profile => 'console_monochrome',
fill_in_placeholders => 0});
say $sqlat->parse($sql);
The output from this script is:
SQLAT version: 1.86
missing closing ')' around block foo_bar.active <@ tstzrange( now( ),
NULL, '( ]' ) AND foo_bar.foo_id = me.foo_id )
LEFT JOIN bar related
ON related.bar_id = foo_bar.bar_id
WHERE related.value > 1; at test.pl line 30.