BUG #16557: getProcedureColumns() function returns columns, when there is no existing Stored Procedure in the DB

This has been fixed in
https://github.com/pgjdbc/pgjdbc/pull/1723
Please upgrade the driver
Dave Cramer
www.postgres.rocks

On Mon, 27 Jul 2020 at 13:47, PG Bug reporting form <noreply@postgresql.org>
wrote:

<div class="socmaildefaultfont" dir="ltr" style="font-family:Arial, Helvetica, sans-serif;font-size:10pt" ><div dir="ltr" ><br>Hi Dave,<br>&nbsp;
<div>We are using 42.2.11 and the latest JDBC driver. We also tried 42.2.16 JDBC drivers as well.</div><br>please note that getProcedures() returns nothing, which is expected as we do not have any Stored procedure.<br><br>but when we use getProcedureColumns(catalog, schema,null,null) then it returns the columns from Functions, which is unexpected and is a bug.<br><br>Thanks&nbsp;<br>Adarshdeep<br><br><br>----- Original message -----<br>From: Dave Cramer &lt;davecramer@postgres.rocks&gt;<br>To: adarshdeep.cheema@ibm.com, pgsql-bugs@lists.postgresql.org<br>Cc:<br>Subject: [EXTERNAL] Re: BUG #16557: getProcedureColumns() function returns columns, when there is no existing Stored Procedure in the DB<br>Date: Tue, Sep 1, 2020 6:36 PM<br>&nbsp;
<div dir="ltr" >This has been fixed in
<div><a href="https://github.com/pgjdbc/pgjdbc/pull/1723&quot; target="_blank">https://github.com/pgjdbc/pgjdbc/pull/1723&lt;/a&gt;&lt;/div&gt;
<div>Please upgrade the driver&nbsp;
<div><div data-smartmail="gmail_signature" dir="ltr" ><div dir="ltr" >Dave Cramer
<div>www.postgres.rocks</div></div></div></div></div></div>&nbsp;

<div><div dir="ltr" >On Mon, 27 Jul 2020 at 13:47, PG Bug reporting form &lt;<a href="mailto:noreply@postgresql.org" target="_blank">noreply@postgresql.org</a>&gt; wrote:</div>
<blockquote style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex" >The following bug has been logged on the website:<br><br>Bug reference:&nbsp; &nbsp; &nbsp; 16557<br>Logged by:&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Adarshdeep Cheema<br>Email address:&nbsp; &nbsp; &nbsp; <a href="mailto:adarshdeep.cheema@ibm.com" target="_blank">adarshdeep.cheema@ibm.com</a><br>PostgreSQL version: 12.2<br>Operating system:&nbsp; &nbsp;Windows<br>Description:&nbsp; &nbsp; &nbsp; &nbsp;<br><br>Create any user defined function using Postgres 12.3 Server, we are using<br>postgresql-42.2.11 JDBC driver<br><br>DDLs:<br>CREATE FUNCTION findec(pdec numeric) RETURNS numeric<br>&nbsp; &nbsp; LANGUAGE plpgsql<br>&nbsp; &nbsp; AS $$<br>declare r decimal(7,2);<br>begin<br>&nbsp; &nbsp; &nbsp; &nbsp; r = PDEC;<br>&nbsp; &nbsp; &nbsp; &nbsp; return r;<br>end;<br>$$;<br><br><br>a) When we use the following Java code, then we get nothing as we do no have<br>any Stored Procedure defined, which is expected<br>&nbsp; resultSet= metadata.getProcedures(null , "public", null); ,<br>&nbsp; while (resultSet.next()){<br><br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; System.out.print(resultSet.getString(1)+"&nbsp; &nbsp; ");<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; System.out.print(resultSet.getString(2)+"&nbsp; &nbsp; ");<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; System.out.print(resultSet.getString(3)+"&nbsp; &nbsp; ");<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; System.out.print(resultSet.getString(4)+"&nbsp; &nbsp; ");<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; System.out.print(resultSet.getString(5)+"&nbsp; &nbsp; ");<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; System.out.println(resultSet.getString(6)+"&nbsp; &nbsp; ");<br>&nbsp; &nbsp; &nbsp; &nbsp; }<br><br>b) Now change the JAVA code to the following and you will get two columns in<br>the resultSet, which is a bug as there is no StoredProcedure Defined in the<br>database<br><br>&nbsp;resultSet= metadata.getProcedureColumns(null , "public", "findec", null);<br>,<br>&nbsp; while (resultSet.next()){<br><br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; System.out.print(resultSet.getString(1)+"&nbsp; &nbsp; ");<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; System.out.print(resultSet.getString(2)+"&nbsp; &nbsp; ");<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; System.out.print(resultSet.getString(3)+"&nbsp; &nbsp; ");<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; System.out.print(resultSet.getString(4)+"&nbsp; &nbsp; ");<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; System.out.print(resultSet.getString(5)+"&nbsp; &nbsp; ");<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; System.out.println(resultSet.getString(6)+"&nbsp; &nbsp; ");<br>&nbsp; &nbsp; &nbsp; &nbsp; }<br><br><br>OUTPUT:<br>null&nbsp; &nbsp; public&nbsp; &nbsp; findec&nbsp; &nbsp; returnValue&nbsp; &nbsp; 5&nbsp; &nbsp; 2&nbsp; &nbsp;<br>null&nbsp; &nbsp; public&nbsp; &nbsp; findec&nbsp; &nbsp; pdec&nbsp; &nbsp; 1&nbsp; &nbsp; 2<br>&nbsp;</blockquote></div></div>
<div dir="ltr" >&nbsp;</div></div><BR>

On Tue, 1 Sep 2020 at 22:21, Adarshdeep Cheema <Adarshdeep.Cheema@ibm.com>
wrote:

Hi Dave,

We are using 42.2.11 and the latest JDBC driver. We also tried 42.2.16
JDBC drivers as well.

please note that getProcedures() returns nothing, which is expected as we
do not have any Stored procedure.

but when we use getProcedureColumns(catalog, schema,null,null) then it
returns the columns from Functions, which is unexpected and is a bug.

Thanks
Adarshdeep

----- Original message -----
From: Dave Cramer <davecramer@postgres.rocks>
To: adarshdeep.cheema@ibm.com, pgsql-bugs@lists.postgresql.org
Cc:
Subject: [EXTERNAL] Re: BUG #16557: getProcedureColumns() function returns
columns, when there is no existing Stored Procedure in the DB
Date: Tue, Sep 1, 2020 6:36 PM

This has been fixed in
https://github.com/pgjdbc/pgjdbc/pull/1723
Please upgrade the driver
Dave Cramer
www.postgres.rocks

On Mon, 27 Jul 2020 at 13:47, PG Bug reporting form <
noreply@postgresql.org> wrote:

The following bug has been logged on the website:

Bug reference: 16557
Logged by: Adarshdeep Cheema
Email address: adarshdeep.cheema@ibm.com
PostgreSQL version: 12.2
Operating system: Windows
Description:

Create any user defined function using Postgres 12.3 Server, we are using
postgresql-42.2.11 JDBC driver

DDLs:
CREATE FUNCTION findec(pdec numeric) RETURNS numeric
LANGUAGE plpgsql
AS $$
declare r decimal(7,2);
begin
r = PDEC;
return r;
end;
$$;

a) When we use the following Java code, then we get nothing as we do no
have
any Stored Procedure defined, which is expected
resultSet= metadata.getProcedures(null , "public", null); ,
while (resultSet.next()){

System.out.print(resultSet.getString(1)+" ");
System.out.print(resultSet.getString(2)+" ");
System.out.print(resultSet.getString(3)+" ");
System.out.print(resultSet.getString(4)+" ");
System.out.print(resultSet.getString(5)+" ");
System.out.println(resultSet.getString(6)+" ");
}

b) Now change the JAVA code to the following and you will get two columns
in
the resultSet, which is a bug as there is no StoredProcedure Defined in the
database

resultSet= metadata.getProcedureColumns(null , "public", "findec", null);
,
while (resultSet.next()){

System.out.print(resultSet.getString(1)+" ");
System.out.print(resultSet.getString(2)+" ");
System.out.print(resultSet.getString(3)+" ");
System.out.print(resultSet.getString(4)+" ");
System.out.print(resultSet.getString(5)+" ");
System.out.println(resultSet.getString(6)+" ");
}

OUTPUT:
null public findec returnValue 5 2
null public findec pdec 1 2

I just tried this:

@Test
public void testGetProcuresWithSchema() throws SQLException {
try {
conn.createStatement().execute("CREATE FUNCTION findec(pdec
numeric) RETURNS numeric\n" +
" LANGUAGE plpgsql\n" +
" AS $$\n" +
"declare r decimal(7,2);\n" +
"begin\n" +
" r = PDEC;\n" +
" return r;\n" +
"end;\n" +
"$$;");
DatabaseMetaData dbmd = conn.getMetaData();
ResultSet rs = dbmd.getProcedures(null, "public", "findec");
if (rs.next()) {
fail();
}
}finally {
conn.createStatement().execute("DROP FUNCTION findec");
}

}

And it does not find the function ?

Dave Cramer
www.postgres.rocks