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

          Tim Yates created issue -
          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
          Guillaume Delcroix made changes -
          Field Original Value New Value
          Fix Version/s 1.6.1 [ 14852 ]
          Fix Version/s 1.5.8 [ 14630 ]
          Fix Version/s 1.7-beta-1 [ 14014 ]
          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
          Tim Yates made changes -
          Attachment TestHelper.groovy [ 40111 ]
          Attachment diff.txt [ 40110 ]
          Guillaume Delcroix made changes -
          Fix Version/s 1.6.2 [ 15151 ]
          Fix Version/s 1.6.1 [ 14852 ]
          Guillaume Delcroix made changes -
          Fix Version/s 1.5.8 [ 14630 ]
          Guillaume Delcroix made changes -
          Fix Version/s 1.6.2 [ 15151 ]
          Fix Version/s 1.6.3 [ 15251 ]
          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.
          Paul King made changes -
          Assignee Paul King [ paulk_asert ]
          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
          Paul King made changes -
          Resolution Fixed [ 1 ]
          Status Open [ 1 ] Resolved [ 5 ]
          Paul King made changes -
          Link This issue is related to GROOVY-2782 [ GROOVY-2782 ]
          Paul King made changes -
          Status Resolved [ 5 ] Closed [ 6 ]
          Mark Thomas made changes -
          Project Import Sun Apr 05 13:32:57 UTC 2015 [ 1428240777691 ]
          Mark Thomas made changes -
          Workflow jira [ 12732316 ] Default workflow, editable Closed status [ 12744134 ]
          Mark Thomas made changes -
          Project Import Mon Apr 06 02:11:23 UTC 2015 [ 1428286283443 ]
          Mark Thomas made changes -
          Workflow jira [ 12970150 ] Default workflow, editable Closed status [ 12977926 ]
          Mark Thomas made changes -
          Assignee paulk_asert [ paulk_asert ] Paul King [ paulk ]
          Transition Time In Source Status Execution Times Last Executer Last Execution Date
          Open Open Resolved Resolved
          95d 1h 32m 1 Paul King 05/May/09 09:11
          Resolved Resolved Closed Closed
          9d 2h 35m 1 Paul King 14/May/09 11:47

            People

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

              Dates

              • Created:
                Updated:
                Resolved:

                Development