groovy
  1. groovy
  2. GROOVY-5170

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. bug.groovy
        5 kB
        Jeffrey Adamson

        Activity

        Hide
        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
        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
        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
        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
        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
        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
        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
        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
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: