Skip Menu |

This queue is for tickets about the DBD-Pg CPAN distribution.

Report information
The Basics
Id: 123187
Status: resolved
Priority: 0/
Queue: DBD-Pg

People
Owner: greg [...] turnstep.com
Requestors: ya [...] mtw.ru
Cc:
AdminCc:

Bug Information
Severity: (no value)
Broken in:
  • 3.5.0
  • 3.5.0_1
  • 3.5.0_2
  • 3.5.1
  • 3.5.2
  • 3.5.3
  • 3.5.9_1
  • 3.6.0
  • 3.6.1
  • 3.6.2
  • 3.6.9_1
  • 3.6.9_2
  • 3.7.0
Fixed in: 3.7.1



Subject: What the problem?
Date: Wed, 4 Oct 2017 10:46:21 +0300
To: bug-DBD-Pg [...] rt.cpan.org
From: "Yuri I. Averiyanov" <ya [...] mtw.ru>
CREATE TABLE "TBL" ( "NODE" INTEGER NOT NULL, "TREE" INTEGER[], PRIMARY KEY "NODE" ); INSERT ... SELECT * FROM "TBL" WHERE "NODE" = ANY (ARRAY[(SELECT "TREE" FROM "TBL" WHERE "NODE" = $node)]); Field "TREE" is list of all parenthes in hierarhy. This query worked in PgAdmin3, psql and perl-Pg, but not worked in DBD::Pg. Not worked with $DBH->prepare($query). Answered "Syntax error". Other interfaces/handlers to subroutines in libpg worked. Not worked only DBD::Pg. -- С уважением Юрий
Hi, can you find the exact syntax error from the Postgres log files? My first instinct was to think it has to do with the dollar placeholders. I assume you have verified that the placeholder is not being interpolated in the string? I.e. It's in a single-quoted string?
Subject: Re: [rt.cpan.org #123187] What the problem?
Date: Wed, 4 Oct 2017 15:32:44 +0300
To: bug-DBD-Pg [...] rt.cpan.org
From: "Yuri I. Averiyanov" <ya [...] mtw.ru>
В Wed, 4 Oct 2017 08:00:54 -0400 "David Christensen via RT" <bug-DBD-Pg@rt.cpan.org> пишет: Show quoted text
> <URL: https://rt.cpan.org/Ticket/Display.html?id=123187 > > > Hi, can you find the exact syntax error from the Postgres log files? > My first instinct was to think it has to do with the dollar > placeholders. I assume you have verified that the placeholder is not > being interpolated in the string? I.e. It's in a single-quoted string?
No errors in PgSQL log file. This error generated before PgSQL ONLY by $DBH->prepare($query) and ONLY in perl! I think, that is bug in 'prepare' function. Other APIs for libpg don't generate errors. -- С уважением Юрий
Subject: Re: [rt.cpan.org #123187] What the problem?
Date: Wed, 4 Oct 2017 15:50:12 +0300
To: bug-DBD-Pg [...] rt.cpan.org
From: "Yuri I. Averiyanov" <ya [...] mtw.ru>
В Wed, 4 Oct 2017 08:00:54 -0400 "David Christensen via RT" <bug-DBD-Pg@rt.cpan.org> пишет: Show quoted text
> Hi, can you find the exact syntax error from the Postgres log files? > My first instinct was to think it has to do with the dollar > placeholders. I assume you have verified that the placeholder is not > being interpolated in the string? I.e. It's in a single-quoted string?
Oh,sorry. I change control of logs and now have error. Perl code: my $req = $DBH->prepare(q{SELECT * FROM "TBL" WHERE "NODE" = ANY (ARRAY[(SELECT "TREE" FROM "TBL" WHERE "NODE" = ? )]);}); $req->execute(100); PgSQL log: STATEMENT: SELECT * FROM "TBL" WHERE "NODE" = ANY (ARRAY[(SELECT "TREE" FROM "TBL" WHERE "NODE" = ? )]); ERROR: syntax error at or near "ARRAY" at character 41 STATEMENT: SELECT * FROM "NODE" WHERE "NODE" = ANY ARRAY[ ? ]; This query work in PgAdmin3, in psql, in Pg. But don't work ONLY in DBD::Pg. In PgAdmin3, psql, Pg I see 5 records without any errors. -- С уважением Юрий
Subject: Re: [rt.cpan.org #123187] What the problem?
Date: Wed, 4 Oct 2017 16:17:47 +0300
To: bug-DBD-Pg [...] rt.cpan.org
From: "Yuri I. Averiyanov" <ya [...] mtw.ru>
В Wed, 4 Oct 2017 08:00:54 -0400 "David Christensen via RT" <bug-DBD-Pg@rt.cpan.org> пишет: Show quoted text
> Hi, can you find the exact syntax error from the Postgres log files? > My first instinct was to think it has to do with the dollar > placeholders. I assume you have verified that the placeholder is not > being interpolated in the string? I.e. It's in a single-quoted string?
As I see from log, $DBH->prepare($query) lost a subquery that returns an array of the links from the field "TREE". -- С уважением Юрий
Show quoted text
> This query work in PgAdmin3, in psql, in Pg. But don't work ONLY in > DBD::Pg. In PgAdmin3, psql, Pg I see 5 records without any errors.
That code works for me with DBD::Pg. Can you please provide a standalone, complete script that shows how it fails for you (and have it do print $DBD::Pg::VERSION and $dbh->{pg_server_version} please)
Subject: Re: [rt.cpan.org #123187] What the problem?
Date: Sun, 8 Oct 2017 21:13:51 +0300
To: bug-DBD-Pg [...] rt.cpan.org
From: "Yuri I. Averiyanov" <ya [...] mtw.ru>
В Sun, 8 Oct 2017 10:58:15 -0400 "Greg Sabino Mullane via RT" <bug-DBD-Pg@rt.cpan.org> пишет: Show quoted text
> > This query work in PgAdmin3, in psql, in Pg. But don't work ONLY in > > DBD::Pg. In PgAdmin3, psql, Pg I see 5 records without any errors.
> > That code works for me with DBD::Pg. Can you please provide a > standalone, complete script that shows how it fails for you (and have > it do print $DBD::Pg::VERSION and $dbh->{pg_server_version} please)
$DBD::Pg::VERSION=3.5.3 $dbh->{pg_server_version}=90603 server PgSQL work under FreeBSD 11.1-RELEASE i386. client PgSQL work under Fedora-25 x86_64 Not work perl-DBD-Pg.x86_64-3.5.3-3.fc25 I work in Unix from 1983 and in Postgres from 1993 and perl-4. Before this time, all worked without problem. This code was writen in 2005. Of cause, I can run this script under FreeBSD, but script will work long time (more than 24h) because work with great number of data. On HDD gzip archive used more than 300G. Until 2017, I used Pg. But in 2017 I tried to use DBD::Pg, that DBI allows to prepare requests and store their handlers in variables. The speed tests showed that it works about equally fast. However, using the handlers as variables makes the code more readable. And here I was faced with this problem. The database contains information on the ownership of the records to any groups. Groups can be hierarchical. And when I tried to use a massive type in one request. To get a set of record IDs in a single query using an ARRAY. And here I was faced with the problem that the subquery somehow lost in DBD::Pg. Other implementations of the API to libpg in other programs in Linux work fine. As I understand it, DBI somehow loses the subquery if it is placed in brackets within the subquery have '?'. Other sub-queries without parentheses and '?' are worked correctly. May be this effect due to the fact that I'm using 'use strict' in the beginning of code? Now I rollback the old 2012 version of this query that performed a search of the records in the hierarchy step by step. Of cause, I can myself find the bug, but sorry, now I don't have time for it. May be after New Year only. Sorry my English. I'm Russian. -- С уважением Юрий
Again, it would help to see the actual code. The only way I can reproduce the error shown is to force a question mark to be accepted by the server literally like so: $SQL = 'SELECT * FROM "NODE" WHERE "NODE" = ANY ARRAY[ ? ]'; $dbh->{pg_placeholder_dollaronly} = 1; $sth = $dbh->prepare($SQL); $req->execute();
Subject: Re: [rt.cpan.org #123187] What the problem?
Date: Mon, 29 Jan 2018 13:33:33 +0300
To: bug-DBD-Pg [...] rt.cpan.org
From: "Yuri I. Averiyanov" <ya [...] mtw.ru>
В Sat, 27 Jan 2018 16:57:01 -0500 "Greg Sabino Mullane via RT" <bug-DBD-Pg@rt.cpan.org> пишет: Show quoted text
> Again, it would help to see the actual code. The only way I can > reproduce the error shown is to force a question mark to be accepted > by the server literally like so: > > $SQL = 'SELECT * FROM "NODE" WHERE "NODE" = ANY ARRAY[ ? ]'; > $dbh->{pg_placeholder_dollaronly} = 1; > $sth = $dbh->prepare($SQL); > $req->execute();
Source SQL code: CREATE TABLE "TBL" ( "NODE" INTEGER NOT NULL, "TREE" INTEGER[], PRIMARY KEY "NODE" ); INSERT ... SELECT * FROM "TBL" WHERE "NODE" = ANY (ARRAY[(SELECT "TREE" FROM "TBL" WHERE "NODE" = 100)]); Perl code: my $req = $DBH->prepare(q{SELECT * FROM "TBL" WHERE "NODE" = ANY (ARRAY[(SELECT "TREE" FROM "TBL" WHERE "NODE" = ? )]);}); $req->execute(100); PgSQL log: STATEMENT: SELECT * FROM "TBL" WHERE "NODE" = ANY (ARRAY[( SELECT "TREE" FROM "TBL" WHERE "NODE" = ? )]); ERROR: syntax error at or near "ARRAY" at character 41 STATEMENT: SELECT * FROM "NODE" WHERE "NODE" = ANY ARRAY[ ? ]; I don't use Windows. This query work in PgAdmin3, in psql, in Pg in any UNIX OS. But don't work ONLY in DBD::Pg. In PgAdmin3, psql, Pg I see 5 records without any errors. I think, that $DBH->prepare($SQL) have bug when parse subquery in query. Prepare ONLY query wihout subquery don't generate error. In other programs, worked with libpg, which don't prepared query, don't make error and result have 5 records. Parser query in postgresql work right. ONLY in DBD::Pg this SQL query generated error. Next code, equivalent of original code, worked all time and anywhere: my $q1 = $DBH->prepare(q{SELECT "TREE" FROM "TBL" WHERE "NODE" = ? ;}); my $q2 = $DBH->prepare(q{SELECT * FROM "TBL" WHERE "NODE" = ANY (ARRAY[ ? ]);}); my $r1 = $q1->execute(100); my $row = $r1->fetchrow_hashref; my $r2 = $q2->execute("$row->{TREE}"); -- С уважением Юрий
Subject: Re: [rt.cpan.org #123187] What the problem?
Date: Tue, 30 Jan 2018 18:20:35 +0300
To: bug-DBD-Pg [...] rt.cpan.org
From: "Yuri I. Averiyanov" <ya [...] mtw.ru>
В Sat, 27 Jan 2018 16:57:01 -0500 "Greg Sabino Mullane via RT" <bug-DBD-Pg@rt.cpan.org> пишет: I understand where is the error. If the SQL query contains a subquery, there is no substitution of the argument instead of character '?'if the character '?'located in the subquery. This is evidenced by the string: PgSQL log: STATEMENT: SELECT * FROM "TBL" WHERE "NODE" = ANY (ARRAY[( SELECT "TREE" FROM "TBL" WHERE "NODE" = ? )]); Must be SELECT * FROM "TBL" WHERE "NODE" = ANY (ARRAY[( SELECT "TREE" FROM "TBL" WHERE "NODE" = 100 )]); -- С уважением Юрий
Okay, I was able to duplicate and have a fix, in 22c134d850e857e1f9d0b67e2830a44c34ca623d. Please see if that clears up the error for you too. Thank you.
Assuming fixed, marking as patched.