groovy
  1. groovy
  2. GROOVY-3320

SQL Column Aliases are ignored by groovy.sql with MySQL

    Details

    • Type: Improvement Improvement
    • Status: Closed 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+
    • Number of attachments :
      2

      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 -

          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 Laforge 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 Laforge 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: