В 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}");
--
С уважением
Юрий