Ruby on Rails | Screencasts | Download | Documentation | Weblog | Community | Source

Ticket #1335 (closed defect: untested)

Opened 3 years ago

Last modified 2 years ago

[PATCH] non-numeric primary keys quoted too many times in SQL for HABTM join table

Reported by: davelee Assigned to: Jeremy Kemper <rails@bitsweat.net>
Priority: high Milestone: 1.2
Component: ActiveRecord Version: 0.13.1
Severity: major Keywords: sqlserver mssql mysql postgresql oracle oci needs_review
Cc:

Description (Last modified by bitsweat)

Background: I am running MS SQL server with a legacy DB schema overlaid by AR-friendly views; the only thing odd about the views themselves is that the id in one table is nvarchar, not integer (it's used as the foreign key by all the other legacy tables).

When adding a has_and_belongs_to_many relationship which uses this varchar key, the SQL statement generated has three quotes on either side of the id , eg INSERT INTO bears_frogs ([bear_id], [frog_id]) VALUES (TEDDY_13,'1');

The quotes are generated in the code which creates the SQL itself (I verified this by playing around extensively with the console). It seems to be line #128-129 of the file C:\ruby\lib\ruby\gems\1.8\gems\activerecord-1.10.1\lib\active_record\associations\has_and_belongs_to_many_association.rb which causes the problem.

I only observed this with my wacko legacy schema running on MS SQL.

Change History

05/23/05 01:55:14 changed by anonymous

  • summary changed from non-numeric primary keys quoted too many times in HABTM join table to non-numeric primary keys quoted too many times in SQL for HABTM join table.

05/23/05 01:56:41 changed by davelee

you may need to de-wikify the SQL query above to see what's going on .. there are three single quotes being generated on either side of TEDDY_13

- D

05/23/05 03:08:47 changed by bitsweat

  • keywords set to sqlserver mssql.
  • milestone set to 1.0.

That's calling quote on the model (see base.rb) which calls quote on the db adapter (see connection_adapters/sqlserver_adapter.rb) which escapes ' (single quote) with (two single quotes) then wraps the string in single quotes. Maybe it is too aggressive or is somehow double-quoting?

05/23/05 03:09:24 changed by bitsweat

Seems Trac is a bit aggressive with the WikiFormatting :/

06/06/05 07:07:00 changed by Alex Thomson

  • keywords changed from sqlserver mssql to sqlserver mssql mysql.

It seems that this behaviour is not limited to SQL server. I'm seeing similar behaviour with varchar keys using mysql.

06/14/05 20:28:29 changed by Sam Pohlenz

An interim fix for this is to change line 117 from 

  attributes[column.name] = @owner.quoted_id

to 

  if @owner.id.class == String
    attributes[column.name] = @owner.id
  else
    attributes[column.name] = @owner.quoted_id
  end

and line 119 from 

  attributes[column.name] = record.quoted_id

to 

  if record.id.class == String
    attributes[column.name] = record.id
  else
    attributes[column.name] = record.quoted_id
  end

That seems to have fixed it for me. I'm sure a Ruby expert could make a tad more elegant patch than me though.

07/11/05 11:54:26 changed by chris@chrisbrinker.com

  • keywords changed from sqlserver mssql mysql to sqlserver mssql mysql postgresql.

I am experiencing the same behavior in Postgresql as well. Applying the temporary patch works nicely now.

shortened up the patch a bit: 

attributes[column.name] = (@owner.id.class == String) ? @owner.id : @owner.quoted_id

attributes[column.name] = (record.id.class == String) ? record.id : record.quoted_id

07/22/05 09:09:28 changed by Nathaniel Talbott <nathaniel@talbott.ws>

  • keywords changed from sqlserver mssql mysql postgresql to sqlserver mssql mysql postgresql oracle oci.
  • version changed from 0.12.1 to 0.13.1.

Updating to indicate that this is also broken for Oracle, and the problem still exists in 0.13.1.

11/15/05 10:50:57 changed by bitsweat

  • owner changed from David to bitsweat.
  • priority changed from low to normal.
  • summary changed from non-numeric primary keys quoted too many times in SQL for HABTM join table to [PATCH] non-numeric primary keys quoted too many times in SQL for HABTM join table.
  • milestone changed from 1.0 to 1.1.

Thanks for the patches, folks. How about some unit tests to verify the correct behavior?

02/09/06 21:56:21 changed by bitsweat

  • priority changed from normal to high.
  • description changed.
  • severity changed from normal to major.

Could someone please update the patch with unit tests? Thank you.

04/05/06 16:25:55 changed by anonymous

  • keywords changed from sqlserver mssql mysql postgresql oracle oci to sqlserver mssql mysql postgresql oracle oci needs_review.

Is this still valid in latest revision?

09/03/06 21:50:35 changed by bitsweat

  • status changed from new to closed.
  • resolution set to untested.