getIndexInfo() returns COLUMN_NAME as quoted string about pgjdbc

[davecramer]

To be honest I don't know the answer to this question.

Dana ?

Dave Cramer

On Wed, Feb 5, 2014 at 4:10 AM, snotling [email protected] wrote:

In the process of updating the Pg JDBC driver used in GeoServer [1], I've
run across a difference of behavior in the latest version:
AbstractJdbc2DatabaseMetaData.getIndexInfo() returns COLUMN_NAME as a
quoted string.
I don't know if the use of a quoted string is correct but I've dig up this
old changelog on this topic:

Version 8.3-dev601 (2007-07-31):

• fix Don't return quotes around identifiers in the results of DatabaseMetaData.getIndexInfo even if they would require quoting in SQL. Committed by jurka. Thanks to Andrei Badea.

The corresponding commit is: 4db623chttps://github.com/pgjdbc/pgjdbc/commit/4db623c3fd15e4675d48ad2fdf91ab3dd07465ad
That past behavior has been reverted in commit 1d516031d51603,
through the use of pg_catalog.pg_get_indexdef() instead of
pg_catalog.pg_attribute.attname previously.
What's strange is that the commit message mentions a change justified by
Pg 9.0, whereas the code uses connection.haveMinimumServerVersion("8.3").

So is it a regression? What does the JDBC spec says in regard to columns
as quoted strings?

[1] https://jira.codehaus.org/browse/GEOT-4636

Reply to this email directly or view it on GitHubhttps://github.com//issues/117
.

[ringerc]

PgJDBC needs to return, and consume, quoted identifier strings everywhere, or nowhere. Answering this, in the absence of explicit guidance from the spec, means we need to look at where we're inconsistent and why.

JDBC is pretty pathetic about dealing with quoting and naming, it seems to largely assume you'll take care of this yourself. It doesn't expose a standard java.sql.Connection.quoteIdentifier or anything so useful as that.

So either way, applications feel some pain - they need their own code for escaping identifiers for use in SQL (if we return unquoted identifiers everywhere), or de-escaping them (for reporting to users, etc, if we return quoted identifiers).

Personally my inclination, in terms of least-surprise, is that we should be dealing exclusively with _un_quoted identifiers except in SQL text.

[davecramer]

On Sun, Feb 9, 2014 at 8:15 AM, Craig Ringer [email protected]:

PgJDBC needs to return, and consume, quoted identifier strings everywhere,
or nowhere. Answering this, in the absence of explicit guidance from the
spec, means we need to look at where we're inconsistent and why.

JDBC is pretty pathetic about dealing with quoting and naming, it seems to
largely assume you'll take care of this yourself. It doesn't expose a
standard java.sql.Connection.quoteIdentifier or anything so useful as
that.

So either way, applications feel some pain - they need their own code for
escaping identifiers for use in SQL (if we return unquoted identifiers
everywhere), or de-escaping them (for reporting to users, etc, if we return
quoted identifiers).

Personally my inclination, in terms of least-surprise, is that we should
be dealing exclusively with _un_quoted identifiers except in SQL text.

+! on returning unquoted identifires

Reply to this email directly or view it on GitHubhttps://github.com//issues/117#issuecomment-34573429
.

[aaime]

Hi, to share some perspective, in GetTools/Geoserver we have unified code gathering database metadata from many other databases with spatial extensions (Oracle, MySql, SQLServer, DB2, Sqlite, H2, Teradata), to the best of my knowledge the Postgresql JDBC driver is the only one returning quoted identifiers

[aaime]

Hi, was there any progress on this one?

[davecramer]

Unfortunately no, but I'll see if I can get to it

[aaime]

Thanks a lot!

[davecramer]

OK, I just tested the current code and it does not return quoted identifiers

The following groovy test

public static void main(String []args)
{
def sql =
Sql.newInstance('jdbc:postgresql:test','test','','org.postgresql.Driver')
ResultSet dbMetaData = sql.getConnection().metaData.getIndexInfo(null,
null,'sample',false,true)
ResultSetMetaData metaData = dbMetaData.metaData
int colCount = metaData.columnCount
def columnNames = []
for ( int i=1; i<= colCount;i++)
{
columnNames += metaData.getColumnName(i)
}

while (dbMetaData.next())
{

  columnNames.each { columnName ->
    println "$columnName = ${dbMetaData.getObject(columnName)}"
  }
}

}

returns

table_cat = null
table_schem = public
table_name = sample
non_unique = false
index_qualifier = null
index_name = pk_sample
type = 3
ordinal_position = 1
column_name = id
asc_or_desc = A
cardinality = 0.0
pages = 1
filter_condition = null
table_cat = null
table_schem = public
table_name = sample
non_unique = true
index_qualifier = null
index_name = name_idx
type = 3
ordinal_position = 1
column_name = name
asc_or_desc = A
cardinality = 0.0
pages = 1
filter_condition = null

for a table defined as

test=> \d sample
Table "public.sample"
Column | Type | Modifiers
--------+------------------------+-----------
id | integer | not null
name | character varying(100) | not null
Indexes:
"pk_sample" PRIMARY KEY, btree (id)
"name_idx" btree (name)

Can you provide a simple test program which replicates the desired result ?

Thanks,

Dave Cramer

On 23 April 2014 06:34, Andrea Aime [email protected] wrote:

Thanks a lot!

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/117#issuecomment-41146501
.

[aaime]

I'll have a look, but as a quick hint, try with a column name that needs quotes in postgresql (but of course not in Java), such as "MyColumn", e.g.:

create table mytable (
  id int primary key,
  "MyColumn" varchar
);

[davecramer]

Ok, that works, thanks.

Interestingly enough it returns the table name quoted and the index name
unquoted!!!!

Dave Cramer

On 26 April 2014 09:45, Andrea Aime [email protected] wrote:

I'll have a look, but as a quick hint, try with a column name that needs
quotes in postgresql (but of course not in Java), such as "MyColumn", e.g.:

create table mytable (
id int primary key,
"MyColumn" varchar
);

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/117#issuecomment-41469076
.

[davecramer]

I committed something in master, can you review

[aaime]

Hi Dave, just built the driver and run it through the GeoTools tests, it works for all minus one.
And this one might be unrelated, but I'm not 100% sure about that.
We have a test that uses a table like this:

                                   Table "public.ft1"
     Column     |         Type         |                    Modifiers                     
----------------+----------------------+--------------------------------------------------
 id             | integer              | not null default nextval('ft1_id_seq'::regclass)
 geometry       | geometry(Point,4326) | 
 intProperty    | integer              | 
 doubleProperty | double precision     | 
 stringProperty | character varying    | 
Indexes:
    "ft1_pkey" PRIMARY KEY, btree (id)
    "ft1_geometry_index" gist (geometry)

With simple contents:

id |                      geometry                      | intProperty | doubleProperty | stringProperty 
----+----------------------------------------------------+-------------+----------------+----------------
  0 | 0101000020E610000000000000000000000000000000000000 |           0 |              0 | zero
  1 | 0101000020E6100000000000000000F03F000000000000F03F |           1 |            1.1 | one
  2 | 0101000020E610000000000000000000400000000000000040 |           2 |            2.2 | two

And then we run a query that does a few type casts, which, when run in psql, returns three records:

SELECT "id",encode(ST_AsEWKB("geometry"),'base64') as "geometry","intProperty","doubleProperty","stringProperty" FROM "public"."ft1" WHERE  ("doubleProperty"::text LIKE ('%' || "intProperty"::text)) = true
;
 id |               geometry               | intProperty | doubleProperty | stringProperty 
----+--------------------------------------+-------------+----------------+----------------
  0 | AQEAACDmEAAAAAAAAAAAAAAAAAAAAAAAAA== |           0 |              0 | zero
  1 | AQEAACDmEAAAAAAAAAAA8D8AAAAAAADwPw== |           1 |            1.1 | one
  2 | AQEAACDmEAAAAAAAAAAAAEAAAAAAAAAAQA== |           2 |            2.2 | two

However, when I run it through this driver, I only get 1 record back, the first. Weird?
I'll have a look at creating a stand along example, but does this ring any bell?

[davecramer]

Andrea,

Sorry doesn't ring a bell. If you can provide the standalone test that
would be awesome!!!

FWIW, I avoid case sensitive columns in postgres wherever possible.

Dave Cramer

On 1 May 2014 09:42, Andrea Aime [email protected] wrote:

Hi Dave, just built the driver and run it through the GeoTools tests, it
works for all minus one.
And this one might be unrelated, but I'm not 100% sure about that.
We have a test that uses a table like this:

                               Table "public.ft1"
 Column     |         Type         |                    Modifiers

----------------+----------------------+--------------------------------------------------
id | integer | not null default nextval('ft1_id_seq'::regclass)
geometry | geometry(Point,4326) |
intProperty | integer |
doubleProperty | double precision |
stringProperty | character varying |
Indexes:
"ft1_pkey" PRIMARY KEY, btree (id)
"ft1_geometry_index" gist (geometry)

With simple contents:

id | geometry | intProperty | doubleProperty | stringProperty
----+----------------------------------------------------+-------------+----------------+----------------
0 | 0101000020E610000000000000000000000000000000000000 | 0 | 0 | zero
1 | 0101000020E6100000000000000000F03F000000000000F03F | 1 | 1.1 | one
2 | 0101000020E610000000000000000000400000000000000040 | 2 | 2.2 | two

And then we run a query that does a few type casts, which, when run in
psql, returns three records:

SELECT "id",encode(ST_AsEWKB("geometry"),'base64') as "geometry","intProperty","doubleProperty","stringProperty" FROM "public"."ft1" WHERE ("doubleProperty"::text LIKE ('%' || "intProperty"::text)) = true
;
id | geometry | intProperty | doubleProperty | stringProperty
----+--------------------------------------+-------------+----------------+----------------
0 | AQEAACDmEAAAAAAAAAAAAAAAAAAAAAAAAA== | 0 | 0 | zero
1 | AQEAACDmEAAAAAAAAAAA8D8AAAAAAADwPw== | 1 | 1.1 | one
2 | AQEAACDmEAAAAAAAAAAAAEAAAAAAAAAAQA== | 2 | 2.2 | two

However, when I run it through this driver, I only get 1 record back, the
first. Weird?
I'll have a look at creating a stand along example, but does this ring any
bell?

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/117#issuecomment-41910609
.

[aaime]

Sorry, all our code is build to work with case sensitive columns in all databases (well, except with Oracle of course, as Oracle spatial cannot deal with them). I'll have a look at putting toghether a stand alone example.

[aaime]

Hi, sorry it took so long, here is the stand-alone test:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class LikeTest {

    public static void main(String[] args) throws Exception {
        // grab a database connection
        Class.forName("org.postgresql.Driver");
        try (Connection connection = DriverManager.getConnection(
                "jdbc:postgresql://localhost/gttest", "cite", "cite");
                Statement st = connection.createStatement();) {

            // clean up the table if present, and populate it
            st.execute("DROP TABLE IF EXISTS LIKE_TEST");
            st.execute("CREATE TABLE LIKE_TEST (id serial, intProperty int, doubleProperty float)");
            st.execute("INSERT INTO LIKE_TEST(intProperty, doubleProperty) VALUES(0, 0)");
            st.execute("INSERT INTO LIKE_TEST(intProperty, doubleProperty) VALUES(1, 1.1)");
            st.execute("INSERT INTO LIKE_TEST(intProperty, doubleProperty) VALUES(2, 2.2)");

            // show the results of the various expressions
            try (ResultSet rs = st
                    .executeQuery("select id, doubleProperty::text, intProperty::text, doubleProperty::text LIKE ('%' || intProperty::text) from LIKE_TEST")) {
                while (rs.next()) {
                    System.out.println(rs.getString(1) + ", " + rs.getString(2) + ", "
                            + rs.getString(3) + ", " + rs.getString(4));
                }
            }

            // run the actual query now
            int size = 0;
            try (ResultSet rs = st
                    .executeQuery("select * from LIKE_TEST where doubleProperty::text LIKE ('%' || intProperty::text)")) {
                while (rs.next()) {
                    size++;
                }
            }

            if (size != 3) {
                throw new RuntimeException("Should have gotten a count of 3, but it was : " + size);
            }

        }
    }
}

Now, as you can see I made two queries, one to print the various bits of the expression, and one to do the same counting our test does.

This is where it gets weird, if I run the first query from pgsql I get the following result:

gttest=# select id, doubleProperty::text, intProperty::text, doubleProperty::text LIKE ('%' || intProperty::text) from LIKE_TEST;
 id | doubleproperty | intproperty | ?column? 
----+----------------+-------------+----------
  1 | 0              | 0           | t
  2 | 1.1            | 1           | t
  3 | 2.2            | 2           | t
(3 rows)

But the printout of my class looks as follows:

1, 0, 0, t
2, 1.10000000000000009, 1, f
3, 2.20000000000000018, 2, f

So, it seems doubleProperty::text behaves differently in the two cases, and I'm not sure why.

[davecramer]

I have to agree this is very strange.... Debugging the driver shows false
coming back for the last two in the driver.

FYI, there are subtle differences between psql and the driver. The driver
uses unnamed prepared statements however I tried a prepared statement and
that didn't actually change anything.

Dave Cramer

On 22 February 2015 at 05:11, Andrea Aime [email protected] wrote:

Hi, sorry it took so long, here is the stand-alone test:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class LikeTest {

public static void main(String[] args) throws Exception {
    // grab a database connection
    Class.forName("org.postgresql.Driver");
    try (Connection connection = DriverManager.getConnection(
            "jdbc:postgresql://localhost/gttest", "cite", "cite");
            Statement st = connection.createStatement();) {

        // clean up the table if present, and populate it
        st.execute("DROP TABLE IF EXISTS LIKE_TEST");
        st.execute("CREATE TABLE LIKE_TEST (id serial, intProperty int, doubleProperty float)");
        st.execute("INSERT INTO LIKE_TEST(intProperty, doubleProperty) VALUES(0, 0)");
        st.execute("INSERT INTO LIKE_TEST(intProperty, doubleProperty) VALUES(1, 1.1)");
        st.execute("INSERT INTO LIKE_TEST(intProperty, doubleProperty) VALUES(2, 2.2)");

        // show the results of the various expressions
        try (ResultSet rs = st
                .executeQuery("select id, doubleProperty::text, intProperty::text, doubleProperty::text LIKE ('%' || intProperty::text) from LIKE_TEST")) {
            while (rs.next()) {
                System.out.println(rs.getString(1) + ", " + rs.getString(2) + ", "
                        + rs.getString(3) + ", " + rs.getString(4));
            }
        }

        // run the actual query now
        int size = 0;
        try (ResultSet rs = st
                .executeQuery("select * from LIKE_TEST where doubleProperty::text LIKE ('%' || intProperty::text)")) {
            while (rs.next()) {
                size++;
            }
        }

        if (size != 3) {
            throw new RuntimeException("Should have gotten a count of 3, but it was : " + size);
        }

    }
}

}

Now, as you can see I made two queries, one to print the various bits of
the expression, and one to do the same counting our test does.

This is where it gets weird, if I run the first query from pgsql I get the
following result:

gttest=# select id, doubleProperty::text, intProperty::text, doubleProperty::text LIKE ('%' || intProperty::text) from LIKE_TEST;
id | doubleproperty | intproperty | ?column?
----+----------------+-------------+----------
1 | 0 | 0 | t
2 | 1.1 | 1 | t
3 | 2.2 | 2 | t
(3 rows)

But the printout of my class looks as follows:

1, 0, 0, t
2, 1.10000000000000009, 1, f
3, 2.20000000000000018, 2, f

So, it seems doubleProperty::text behaves differently in the two cases,
and I'm not sure why.

—
Reply to this email directly or view it on GitHub
#117 (comment).

[davecramer]

I asked Tom Lane off list and his response was:

I bet the difference is extra_float_digits.

regression=# select id, doubleProperty::text,
intProperty::text, doubleProperty::text LIKE ('%' ||
intProperty::text) from LIKE_TEST;
id | doubleproperty | intproperty | ?column?
----+----------------+-------------+----------
1 | 0 | 0 | t
2 | 1.1 | 1 | t
3 | 2.2 | 2 | t
(3 rows)

regression=# set extra_float_digits = 3;
SET
regression=# select id, doubleProperty::text,
intProperty::text, doubleProperty::text LIKE ('%' ||
intProperty::text) from LIKE_TEST;
id | doubleproperty | intproperty | ?column?
----+---------------------+-------------+----------
1 | 0 | 0 | t
2 | 1.10000000000000009 | 1 | f
3 | 2.20000000000000018 | 2 | f
(3 rows)

Dave Cramer

On 22 February 2015 at 05:11, Andrea Aime [email protected] wrote:

Hi, sorry it took so long, here is the stand-alone test:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class LikeTest {

public static void main(String[] args) throws Exception {
    // grab a database connection
    Class.forName("org.postgresql.Driver");
    try (Connection connection = DriverManager.getConnection(
            "jdbc:postgresql://localhost/gttest", "cite", "cite");
            Statement st = connection.createStatement();) {

        // clean up the table if present, and populate it
        st.execute("DROP TABLE IF EXISTS LIKE_TEST");
        st.execute("CREATE TABLE LIKE_TEST (id serial, intProperty int, doubleProperty float)");
        st.execute("INSERT INTO LIKE_TEST(intProperty, doubleProperty) VALUES(0, 0)");
        st.execute("INSERT INTO LIKE_TEST(intProperty, doubleProperty) VALUES(1, 1.1)");
        st.execute("INSERT INTO LIKE_TEST(intProperty, doubleProperty) VALUES(2, 2.2)");

        // show the results of the various expressions
        try (ResultSet rs = st
                .executeQuery("select id, doubleProperty::text, intProperty::text, doubleProperty::text LIKE ('%' || intProperty::text) from LIKE_TEST")) {
            while (rs.next()) {
                System.out.println(rs.getString(1) + ", " + rs.getString(2) + ", "
                        + rs.getString(3) + ", " + rs.getString(4));
            }
        }

        // run the actual query now
        int size = 0;
        try (ResultSet rs = st
                .executeQuery("select * from LIKE_TEST where doubleProperty::text LIKE ('%' || intProperty::text)")) {
            while (rs.next()) {
                size++;
            }
        }

        if (size != 3) {
            throw new RuntimeException("Should have gotten a count of 3, but it was : " + size);
        }

    }
}

}

Now, as you can see I made two queries, one to print the various bits of
the expression, and one to do the same counting our test does.

This is where it gets weird, if I run the first query from pgsql I get the
following result:

gttest=# select id, doubleProperty::text, intProperty::text, doubleProperty::text LIKE ('%' || intProperty::text) from LIKE_TEST;
id | doubleproperty | intproperty | ?column?
----+----------------+-------------+----------
1 | 0 | 0 | t
2 | 1.1 | 1 | t
3 | 2.2 | 2 | t
(3 rows)

But the printout of my class looks as follows:

1, 0, 0, t
2, 1.10000000000000009, 1, f
3, 2.20000000000000018, 2, f

So, it seems doubleProperty::text behaves differently in the two cases,
and I'm not sure why.

—
Reply to this email directly or view it on GitHub
#117 (comment).

[aaime]

Found related reports, it seems the driver is indeed setting that property: https://bugzilla.mozilla.org/show_bug.cgi?id=730322

[aaime]

Soo... what could be done about it? I guess the driver needs that setting for other bits of its functionality? Or could it be a leftover of past code requirements?

[aaime]

Switching my brain on, the old driver we are using (a 8.4-701.jdbc3 version) did not behave this way, so I guess that setting has been added later

[davecramer]

you should be able to set the property to whatever you want once you get
the connection.

Dave Cramer

On 1 March 2015 at 04:22, Andrea Aime [email protected] wrote:

Switching my brain on, the old driver we are using (a 8.4-701.jdbc3
version) did not behave this way, so I guess that setting has been added
later

—
Reply to this email directly or view it on GitHub
#117 (comment).

[aaime]

That is good to know, however... I guess it was set for a reason? In other words, what else is going to regress when I set it? ;-)

[davecramer]

You will get some rounding errors on floats

http://www.postgresql.org/docs/9.3/static/runtime-config-client.html

Dave Cramer

On 1 March 2015 at 08:42, Andrea Aime [email protected] wrote:

That is good to know, however... I guess it was set for a reason? In other
words, what else is going to regress when I set it? ;-)

—
Reply to this email directly or view it on GitHub
#117 (comment).