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

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

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

PG Bug reporting form
The following bug has been logged on the website:

Bug reference:      16557
Logged by:          Adarshdeep Cheema
Email address:      [hidden email]
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

Reply | Threaded
Open this post in threaded view
|

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

Dave Cramer-7
This has been fixed in
Please upgrade the driver 
Dave Cramer
www.postgres.rocks


On Mon, 27 Jul 2020 at 13:47, PG Bug reporting form <[hidden email]> wrote:
The following bug has been logged on the website:

Bug reference:      16557
Logged by:          Adarshdeep Cheema
Email address:      [hidden email]
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

Reply | Threaded
Open this post in threaded view
|

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

Adarshdeep Cheema

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 <[hidden email]>
To: [hidden email], [hidden email]
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
Please upgrade the driver 
Dave Cramer
www.postgres.rocks
 
On Mon, 27 Jul 2020 at 13:47, PG Bug reporting form <[hidden email]> wrote:
The following bug has been logged on the website:

Bug reference:      16557
Logged by:          Adarshdeep Cheema
Email address:      [hidden email]
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
 
 

Reply | Threaded
Open this post in threaded view
|

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

Dave Cramer-7
On Tue, 1 Sep 2020 at 22:21, Adarshdeep Cheema <[hidden email]> 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 <[hidden email]>
To: [hidden email], [hidden email]
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
Please upgrade the driver 
Dave Cramer
www.postgres.rocks
 
On Mon, 27 Jul 2020 at 13:47, PG Bug reporting form <[hidden email]> wrote:
The following bug has been logged on the website:

Bug reference:      16557
Logged by:          Adarshdeep Cheema
Email address:      [hidden email]
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