This project is archived and is in readonly mode.

#796 ✓committed
Michalis Polakis

A small modification at ActiveRecord's calculations.rb

Reported by Michalis Polakis | August 10th, 2008 @ 09:18 PM | in 2.x

Hello,

by adding at calculations.rb

line 208 : sql << ") AS #{aggregate_alias}_subquery" if use_workaround

More legitimate sql code is generated, and a bug for the newly developed and distributed MonetDB adapter is solved.

MonetDB prints this message :

!subquery table reference needs alias, use AS xxx in: "select count(*) as count_all from (select distinct "accounts".id from "accounts" left outer join "companies" on "companies".id = "accounts".firm_id and "companies"."type" = 'Firm' where (companies.id > 1) );"

Could this modification be added in the ActiveRecord code?

By the way, (this is not intended to be spam), I've developed an activerecord adapter for MonetDB. MonetDB is an open source, high-performance column-store database management system with automatic index management, flexible optimizer infrastructure, and programmable backend functionality. It comes with functional complete SQL- and XQuery- frontends.

To find out more about MonetDB visit monetdb.cwi.nl and to get the adapter visit http://rubyforge.org/projects/mo...

Thanks, Michalis Polakis

Comments and changes to this ticket

  • Michael Koziarski

    Michael Koziarski August 11th, 2008 @ 08:48 AM

    Hi There,

    Can you make the change and submit a patch:

    http://rails.lighthouseapp.com/p...

    If you need help with using git the folks in #rails-contrib will probably be able to help.

  • Tom Ward

    Tom Ward August 11th, 2008 @ 03:00 PM

    • Tag set to activerecord, patch, tiny

    I've created a patch with Michalis' change (for which he should get the credit), and tested against sqlite3 and mysql. As this fixes a problem in a non-core adapter, I can't see how best to add a test. Any ideas?

  • Repository

    Repository August 11th, 2008 @ 03:49 PM

    • State changed from “new” to “committed”

    (from [c7375d74d9fff3219d4ea389ba9e36a90afe9d33]) Alias subquery used in calculations, to provide better compatibility with databases such as MonetDB

    Signed-off-by: Michael Koziarski michael@koziarski.com Signed-off-by: Tom Ward tom@popdog.net [#796 state:committed] http://github.com/rails/rails/co...

  • Repository

    Repository August 25th, 2008 @ 09:29 AM

    (from [b6ad9a75ccf0e8734312357a7ac9dd7c47fd4cf1]) Alias subquery used in calculations, to provide better compatibility with databases such as MonetDB

    Signed-off-by: Michael Koziarski michael@koziarski.com Signed-off-by: Tom Ward tom@popdog.net [#796 state:committed] http://github.com/rails/rails/co...

  • Raimonds Simanovskis

    Raimonds Simanovskis September 8th, 2008 @ 04:06 PM

    This change in Rails 2.1.1 broke Oracle database compatibility :(

    See http://rails.lighthouseapp.com/p...

    At first, there is bug that instead of using single quotes in ### sql << ') AS #{aggregate_alias}subquery' if use_workaround ### you should use double quotes: ### sql << ") AS #{aggregate_alias}subquery" if use_workaround ###

    And second, this "AS table_alias" syntax is not supported by Oracle database - see latest Oracle syntax specification at http://download.oracle.com/docs/...

    So I think this change should be reverted and probably moved to database adapter where each adapter could specify correct way how to add table aliases in SQL queries.

  • Ed Lebert

    Ed Lebert October 2nd, 2008 @ 05:01 PM

    Raimonds,

    Do you have a suggested workaround for this in the mean time? My oracle rails app broke :(

  • Raimonds Simanovskis

    Raimonds Simanovskis October 3rd, 2008 @ 08:29 AM

    You can apply quick monkey patch from http://rails.lighthouseapp.com/p...

    I am going to include similar monkey patch in my next version of oracle_enhanced adapter.

    And I will also try to get this fixed for next release of Rails. Correct solution would be to remove "AS" keyword from line http://github.com/rails/rails/tr... as "AS" keyword for table alias is not supported by Oracle and is optional for other databases (also for MonetDB which caused this problem :( )

  • Michael Koziarski
  • Raimonds Simanovskis

    Raimonds Simanovskis October 3rd, 2008 @ 09:41 PM

    Yes, now it's fine for Oracle. Thanks Michael.

    Just one additional comment - Oracle allows max 30 characters in any identifier (table name, index name, column name, alias name etc.). Therefore for Oracle it would be better not to generate too long identifiers from Rails (like appending to column names long suffix "_subquery"). So if you can please use short prefixes and suffixes :)

  • john

    john October 22nd, 2008 @ 05:40 AM

    Raimonds, even though this issue is solved and the monkey patch helped I just had a question.

    When the changes are made from version to version, no unit tests are performed?

  • Raimonds Simanovskis

    Raimonds Simanovskis October 22nd, 2008 @ 11:50 AM

    John,

    As Oracle adapter is not anymore in core ActiveRecord, then ActiveRecord unit tests are not run on Oracle database during Rails continuous integration builds. Therefore such issues might appear when some changes are made which break compatibility with other non-core databases.

    I tried to run all ActiveRecord unit tests on Oracle database but currently quite a lot are failing - because of too-long identifiers, because of some Oracle specific limitations etc. Probably in the future I together with Rails core team will manage that all ActiveRecord unit tests will run on Oracle database :) But now it's not so easy to identify new changes which break compatibility.

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile »

<h2 style="font-size: 14px">Tickets have moved to Github</h2>

The new ticket tracker is available at <a href="https://github.com/rails/rails/issues">https://github.com/rails/rails/issues</a>

Attachments

Referenced by

Pages