groovy

GroovyRowResult and GroovyResultSet are Inconsistent with each other when using a postgres driver

Details

  • Type: Bug Bug
  • Status: Closed Closed
  • Priority: Major Major
  • Resolution: Fixed
  • Affects Version/s: 1.8.4
  • Fix Version/s: 1.8.6, 2.0-beta-3
  • Component/s: None
  • Labels:
    None
  • Environment:
    Groovy with a postgres database
  • Number of attachments :
    1

Description

Basically, the root problem seams to be that the postgres (driver?) folds unquoted object identifiers to lower case. This behavior is transparent to the api when using sql.eachRow, but not when sql.firstRow. This appears to be related to how eachRow uses a GroovyResultSet which delegates the getObject(property) to the driver, but firstRow uses a GroovyRowResult which does not.

String query = "SELECT * FROM $table";
sql.eachRow(query)

{ row -> def eValue = row[column] println "each row found $eValue"; }

def fValue = sql.firstRow(query)[column]
println "firstRow found $fValue"

I saw code in GroovyRowResult#getProperty(String) currently checks the exact case, then tries folding the property name to upper-case (as is more common). This is not adequate for postgres behavior which folds to lower-case.

Attached is a script which demonstrates this, an example invocation would be
`bug.groovy --url jdbc:postgresql://example.com:5432/catalog --user postgres --pass postgres --schema public --driver org.postgresql.Driver TABLE ID`
It requires a postgres database with a table and column (create using unquoted and/or lowercase names) exist with at least one row.

  1. File
    bug.groovy
    05/Dec/11 3:21 PM
    5 kB
    Jeffrey Adamson

Activity

Ascending order - Click to sort in descending order
Hide
Permalink
Jeffrey Adamson added a comment -

I posted this to "GroovyDev" <dev@groovy.codehaus.org> for feedback first, but now I realize I probably wasn't sending from a registered address. So please forgive me if this a duplicate or "works as expected".

Show
Jeffrey Adamson added a comment - I posted this to "GroovyDev" <dev@groovy.codehaus.org> for feedback first, but now I realize I probably wasn't sending from a registered address. So please forgive me if this a duplicate or "works as expected".
Hide
Permalink
John Wagenleitner added a comment -

Just wanted to add a note that the same problem can also occur with MySQL since the driver returns column labels in the same case used in the create table statement. The ResultSet seems to handle the names case insensitively which as pointed out is not the case for the GroovyRowResult.

In Oracle, Hsqldb and H2 the column names are in uppercase so they don't have this problem.

import groovy.sql.*

def sql = Sql.newInstance('jdbc:mysql://localhost/groovy', 'groovy', 'foo', 'com.mysql.jdbc.Driver')

try {
    sql.execute('''DROP TABLE IF EXISTS foo''')
    sql.execute('''create table foo (name varchar(30), Title varchar(30))''')
    sql.execute('''insert into foo (name, title) values ('foo', 'bar')''') 
    
    sql.eachRow('select * from foo') {
        assert 'foo' == it.nAmE
        assert 'foo' == it.NAME
        assert 'bar' == it.title
        assert 'bar' == it.TITLE 
    }

    def row = sql.firstRow('select * from foo')
    // These will fail
    assert 'foo' == row.NAME
    assert 'bar' == row.title
    assert 'bar' == row.TITLE
} finally {
    sql.close()
}
Show
John Wagenleitner added a comment - Just wanted to add a note that the same problem can also occur with MySQL since the driver returns column labels in the same case used in the create table statement. The ResultSet seems to handle the names case insensitively which as pointed out is not the case for the GroovyRowResult . In Oracle, Hsqldb and H2 the column names are in uppercase so they don't have this problem. import groovy.sql.* def sql = Sql.newInstance('jdbc:mysql: //localhost/groovy', 'groovy', 'foo', 'com.mysql.jdbc.Driver') try { sql.execute('''DROP TABLE IF EXISTS foo''') sql.execute('''create table foo (name varchar(30), Title varchar(30))''') sql.execute('''insert into foo (name, title) values ('foo', 'bar')''') sql.eachRow('select * from foo') { assert 'foo' == it.nAmE assert 'foo' == it.NAME assert 'bar' == it.title assert 'bar' == it.TITLE } def row = sql.firstRow('select * from foo') // These will fail assert 'foo' == row.NAME assert 'bar' == row.title assert 'bar' == row.TITLE } finally { sql.close() }
Hide
Permalink
John Wagenleitner added a comment -

I have submitted a github pull request (https://github.com/groovy/groovy-core/pull/21) for this issue.

Show
John Wagenleitner added a comment - I have submitted a github pull request ( https://github.com/groovy/groovy-core/pull/21 ) for this issue.
Hide
Permalink
blackdrag blackdrag added a comment -

Javadoc of ResultSet says this: "Column names used as input to getter methods are case insensitive. When a getter method is called with a column name and several columns have the same name, the value of the first matching column will be returned. "
Going by that it should be ok to do this change

Show
blackdrag blackdrag added a comment - Javadoc of ResultSet says this: "Column names used as input to getter methods are case insensitive. When a getter method is called with a column name and several columns have the same name, the value of the first matching column will be returned. " Going by that it should be ok to do this change
Hide
Permalink
Paul King added a comment -

John, I tried to apply your pull request but received this error:

Applying: GROOVY-5170: GroovyRowResult and GroovyResultSet are Inconsistent
error: patch failed: src/test/groovy/sql/GroovyRowResultTest.groovy:1
error: src/test/groovy/sql/GroovyRowResultTest.groovy: patch does not apply
Patch failed at 0001 GROOVY-5170: GroovyRowResult and GroovyResultSet are Inconsistent

I haven't had time to investigate further but applied the patch with some tweaks manually.

John and Jeffrey, if you could test the latest snapshots, that would be great.

Thanks, Paul.

Show
Paul King added a comment - John, I tried to apply your pull request but received this error: Applying: GROOVY-5170: GroovyRowResult and GroovyResultSet are Inconsistent error: patch failed: src/test/groovy/sql/GroovyRowResultTest.groovy:1 error: src/test/groovy/sql/GroovyRowResultTest.groovy: patch does not apply Patch failed at 0001 GROOVY-5170: GroovyRowResult and GroovyResultSet are Inconsistent I haven't had time to investigate further but applied the patch with some tweaks manually. John and Jeffrey, if you could test the latest snapshots, that would be great. Thanks, Paul.
Hide
Permalink
John Wagenleitner added a comment -

I tested using the latest snapshot with a project I have that uses MySQL and it's working great (am grateful to no longer have to remember the exact mixed casing of the column names).

I also did a quick test with Postgresql and that also seems to work fine (though I'm not as familiar with Postresql).

Show
John Wagenleitner added a comment - I tested using the latest snapshot with a project I have that uses MySQL and it's working great (am grateful to no longer have to remember the exact mixed casing of the column names). I also did a quick test with Postgresql and that also seems to work fine (though I'm not as familiar with Postresql).
Hide
Permalink
Paul King added a comment -

Thanks John. Resolving for now. Jeffrey, if you spot any problems please reopen or create a new issue.

Show
Paul King added a comment - Thanks John. Resolving for now. Jeffrey, if you spot any problems please reopen or create a new issue.
Hide
Permalink
Jeffrey Adamson added a comment -

My basic test work great using. Thanks.

( using local groovy build from f51879debc47c1a0088f2bbbc6ec9062406045b9 of GROOVY_1_8_6_RC )

Show
Jeffrey Adamson added a comment - My basic test work great using. Thanks. ( using local groovy build from f51879debc47c1a0088f2bbbc6ec9062406045b9 of GROOVY_1_8_6_RC )

People

  • Assignee:
    Paul King
    Reporter:
    Jeffrey Adamson
Vote (0)
Watch (0)

Dates

  • Created:
    Updated:
    Resolved: