Groovy
  1. Groovy
  2. GROOVY-3320

SQL Column Aliases are ignored by groovy.sql with MySQL

    Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Minor Minor
    • Resolution: Fixed
    • Affects Version/s: 1.5.7
    • Fix Version/s: 1.6.3, 1.7-beta-1
    • Component/s: SQL processing
    • Labels:
      None
    • Environment:
      Connecting to MySQL 5 with Connector/J 5.1+

      Description

      I had a problem when joining two tables that shared a field name, ie:

      SELECT a.id, a.description, b.description FROM a JOIN b ON( a.b_fk = b.id )

      As both fields get put into the save field in the resultant row map.

      Trying to alias the rows doesn't help, ie;

      SELECT a.id, a.description AS a_desc, b.description AS b_desc FROM a JOIN b ON( a.b_fk = b.id )

      Gives the same.

      I found the reason for this ( http://jira.codehaus.org/browse/GROOVY-2782) which recommends adding the "useOldAliasMetadataBehavior=true" parameter to the JDBC url, and this does fix it, however in that issue, Ken Weiner asks:

      >> Should the rows method be calling ResultSetMetaData.getColumnLabel() instead?

      Which I thought was a good point, deserving of another issue.

      It's strange, and kinda unpredictable as it stands, as the following:

      SELECT 10, 10 AS ten

      Does give you two columns back (so the alias works) – but I guess this is a missed case on the original MySQL "fix"

      Hope this make sense... It's my first issue, and I've tried not to babble

      Cheers,

      Tim

      1. diff.txt
        2 kB
        Tim Yates
      2. TestHelper.groovy
        3 kB
        Tim Yates

        Issue Links

          Activity

          Hide
          Tim Yates added a comment -

          Sorry, my typing and proof reading is awful:

          >> As both fields get put into the save field in the resultant row map.

          Should be

          As both fields get put into the same field ('description') in the resultant row map.

          Show
          Tim Yates added a comment - Sorry, my typing and proof reading is awful: >> As both fields get put into the save field in the resultant row map. Should be As both fields get put into the same field ('description') in the resultant row map.
          Hide
          Tim Yates added a comment - - edited

          The line in question (in 1.6 RC-2) is org.codehaus.groovy.runtime.DefaultGroovyMethods:10750

          lhm.put(metadata.getColumnName(i), rs.getObject(i));
          
          Show
          Tim Yates added a comment - - edited The line in question (in 1.6 RC-2) is org.codehaus.groovy.runtime.DefaultGroovyMethods:10750 lhm.put(metadata.getColumnName(i), rs.getObject(i));
          Hide
          Jochen Theodorou added a comment -

          interested in writing a patch and test case?

          Show
          Jochen Theodorou added a comment - interested in writing a patch and test case?
          Hide
          Tim Yates added a comment -

          Yeah, but I have no idea how :-/

          I am also not sure (and cannot test) if this would regress something with Oracle, Postgres, etc

          I can't think how it would, but anecdotes and thoughts aren't tests

          Show
          Tim Yates added a comment - Yeah, but I have no idea how :-/ I am also not sure (and cannot test) if this would regress something with Oracle, Postgres, etc I can't think how it would, but anecdotes and thoughts aren't tests
          Hide
          Tim Yates added a comment -

          Right... done some reading over the weekend, and worked it out...

          I'll attach a patch to this issue as soon as I can for the issue, and the test case

          Show
          Tim Yates added a comment - Right... done some reading over the weekend, and worked it out... I'll attach a patch to this issue as soon as I can for the issue, and the test case
          Hide
          Tim Yates added a comment -

          Right...having no luck with this

          I wrote some tests, and some test data, and changed the line (to generate a patch), but aparently src/test/groovy/sql/TestHelper.groovy is of mime type application/octet-stream, so diff will not display it...

          Show
          Tim Yates added a comment - Right...having no luck with this I wrote some tests, and some test data, and changed the line (to generate a patch), but aparently src/test/groovy/sql/TestHelper.groovy is of mime type application/octet-stream, so diff will not display it...
          Hide
          Guillaume Delcroix added a comment -

          If a diff ain't work, attach the full blown files, we'll cope with them.
          That said, we'll have to properly fix the mime type of those files.

          Show
          Guillaume Delcroix added a comment - If a diff ain't work, attach the full blown files, we'll cope with them. That said, we'll have to properly fix the mime type of those files.
          Hide
          Tim Yates added a comment -

          Attached diff for my fix and the test code for it and test/groovy/sql/TestHelper.groovy (the file tagged as binary)

          Hope it's ok!

          And really sorry for the delay I missed your reply to my last message until now

          Please note, I had to add get_column_name=true to the hsqldb connect string to get it to follow what they claim are the defaults

          Tim

          Show
          Tim Yates added a comment - Attached diff for my fix and the test code for it and test/groovy/sql/TestHelper.groovy (the file tagged as binary) Hope it's ok! And really sorry for the delay I missed your reply to my last message until now Please note, I had to add get_column_name=true to the hsqldb connect string to get it to follow what they claim are the defaults Tim
          Hide
          Paul King added a comment - - edited

          Patch applied in trunk. Strangely enough I couldn't replicate the MySQL problem using HSQL 1.8.0.7 or 1.8.0.10 and they didn't need the URL qualifier either to work. HSQL 1.9.0-alpha2 did replicate the problem. No URL qualifier was needed there either. The patch then fixed the issue. Applying to 1_6_X shortly.

          Show
          Paul King added a comment - - edited Patch applied in trunk. Strangely enough I couldn't replicate the MySQL problem using HSQL 1.8.0.7 or 1.8.0.10 and they didn't need the URL qualifier either to work. HSQL 1.9.0-alpha2 did replicate the problem. No URL qualifier was needed there either. The patch then fixed the issue. Applying to 1_6_X shortly.
          Hide
          Paul King added a comment -

          Also now on 1_6_X branch

          Show
          Paul King added a comment - Also now on 1_6_X branch

            People

            • Assignee:
              Paul King
              Reporter:
              Tim Yates
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development