| Subject: | Queries containing intervals of format "INTERVAL 'X period'" fail when pg_server_prepare is enabled |
Discovered an unusual bug with DBI after recently doing some updates. Basically a common query just completely fails. It's not immediately clear why, but it's easy to reproduce.
We use Postgres (9.4.5), running under FreeBSD (9.3), with recent versions of nearly every library that is part of our stack, DBI included (1.634). The issue occurs both when a database is either in UTF8 or SQL_ASCII.
The failing queries use the "INTERVAL 'X period'" format, along the lines of:
"SELECT now() > now() - INTERVAL '1 day'"
Upon updating our system we started receiving syntax errors like:
"ERROR: syntax error at or near "$1"
LINE 1: SELECT now() > now() - INTERVAL $1
^"
I found this to be very unusual, as the query executes fine from the Postgres CLI, and the alternative formats such as "::interval" and "CAST(X as INTERVAL)" both work as well. Only the format above fails.
I've since rewritten our failing queries to use an alternative format, but ideally we can get this fixed. It's not clear what sort of materials are necessary to resolve a bug like this, please let me know and I can provide those details.
In addition I have attached a basic script which reproduces the issue.
| Subject: | dbi.pl |
#!/usr/bin/perl
use warnings;
use strict;
use DBI;
my $username = '';
my $password = '';
my $host = '';
my $dbname = '';
my $datasource = "dbi:Pg:dbname=$dbname;host=$host;port=5432;";
my $dbh = DBI->connect($datasource, $username, $password, { PrintError => 0 })
or die 'dbi connect failure: ' . $DBI::errstr;
my $sth0 = $dbh->prepare('SELECT now() > now() - ?::interval');
$sth0->execute('1 day') or die $dbh->errstr();
print "Result of 'SELECT now() > now() - ?::interval' = " . $sth0->fetchrow() . "\n";
my $sth1 = $dbh->prepare('SELECT now() > now() - INTERVAL ?');
$sth1->execute('1 day') or die $dbh->errstr();
print "Result of 'SELECT now() > now() - ?::interval' = " . $sth1->fetchrow() . "\n";