Multiplay Labs

tech hits and tips from Multiplay

Invalid SQL Generated by Mixed Relative / Qualified Conditions in ActiveRecord

without comments

If you mix relative and fully qualified conditions in ActiveRecord, your in for random results with the potential for invalid SQL.

The following is an example of code which may or may not work.

User.find( :all, { :name => 'wibble', 'email.valid' => true }, :join => [ :email ] )

The problem lies in the base method sanitize_sql_hash_for_conditions that generates the fully qualified SQL statements. It takes a “default” table name that’s used for unqualified conditions, however it overwrites this variable if it comes across an fully qualified condition. This means that the generation of the SQL depends on the internal order of the hash resulting in unpredictable output.

In our case code which had been working for months suddenly stopped generating valid SQL without any changes, we even had a dev instance and a live instance running identical code but generating different SQL its that unpredictable. It seems something totally unrelated had effected the internal hash order and hence the validity of the generated SQL.

The fix for this is simply to ensure that fully qualified conditions don’t overwrite the default for relative conditions, the following patch achieves this.

Index: vendor/rails/activerecord/lib/active_record/base.rb
===================================================================
--- vendor/rails/activerecord/lib/active_record/base.rb (revision 1332)
+++ vendor/rails/activerecord/lib/active_record/base.rb (working copy)
@@ -2308,11 +2308,13 @@
 
               # Extract table name from qualified attribute names.
               if attr.include?('.')
-                table_name, attr = attr.split('.', 2)
-                table_name = connection.quote_table_name(table_name)
+                tn, attr = attr.split('.', 2)
+                tn = connection.quote_table_name(tn)
+                attribute_condition("#{tn}.#{connection.quote_column_name(attr)}", value)
+              else
+                attribute_condition("#{table_name}.#{connection.quote_column_name(attr)}", value)
               end
 
-              attribute_condition("#{table_name}.#{connection.quote_column_name(attr)}", value)
             else
               sanitize_sql_hash_for_conditions(value, connection.quote_table_name(attr.to_s))
             end

Written by Dilbert

October 9th, 2009 at 10:33 am

Posted in Code,Hackery,Rails

Leave a Reply

You must be logged in to post a comment.