postgresql_adapter.rb ignores NULLS while creating distincts in 3.2.7 #7200

Closed
matrol26 opened this Issue Jul 30, 2012 · 5 comments

Projects

None yet

4 participants

@matrol26

postgresql_adapter.rb ignores NULLS while creating distincts and lets search fail:

ActiveRecord::StatementInvalid (PG::Error: ERROR: syntax error at or near "FIRST"
LINE 1: ...LECT DISTINCT "permissions".id, groups.nameNULLS FIRST AS a...
^
: SELECT DISTINCT "permissions".id, groups.nameNULLS FIRST AS alias_0, vendors.nameNULLS FIRST AS alias_1, products.nameNULLS FIRST AS alias_2, media.nameNULLS FIRST AS alias_3 FROM "permissions" LEFT OUTER JOIN "groups" ON "groups"."id" = "permissions"."group_id" LEFT OUTER JOIN "products" ON "products"."id" = "permissions"."product_id" LEFT OUTER JOIN "vendors" ON "vendors"."id" = "permissions"."vendor_id" LEFT OUTER JOIN "media" ON "media"."id" = "permissions"."medium_id" LEFT OUTER JOIN "media_systems" ON "media_systems"."medium_id" = "media"."id" LEFT OUTER JOIN "systems" ON "systems"."id" = "media_systems"."system_id" LEFT OUTER JOIN "languages_media" ON "languages_media"."medium_id" = "media"."id" LEFT OUTER JOIN "languages" ON "languages"."id" = "languages_media"."language_id" ORDER BY groups.name asc NULLS FIRST, vendors.name asc NULLS FIRST, products.name asc NULLS FIRST, media.name asc NULLS FIRST LIMIT 25 OFFSET 0):

solution: change line 1111 in activerecord-3.2.7/lib/active_record/connection_adapters/postgresql_adapter.rb
from: order_columns = orders.collect { |s| s.gsub(/\s+(ASC|DESC)\s_/i, '') }
to: order_columns = orders.collect { |s| s.gsub(/\s+(ASC|DESC)\s_(NULLS\s+(FIRST|LAST)\s*)?/i, '') }

@rrmartins

Hello, @matrol26

How is the call you doing?

I have a system that is query works 100%.

Att.
@rrmartins

@matrol26

I have a table wheres columns could be null. I added an order method which sort the columns alphabetical and NULL values should before the others. The combination order by and nulls first|last is in postgres allowed: http://www.postgresql.org/docs/9.1/interactive/sql-select.html#SQL-ORDERBY

I made a small error in the "change-to" line (forgot a plus)
old: order_columns = orders.collect { |s| s.gsub(/\s+(ASC|DESC)\s(NULLS\s+(FIRST|LAST)\s_)?/i, '') }
new: order_columns = orders.collect { |s| s.gsub(/\s+(ASC|DESC)\s+(NULLS\s+(FIRST|LAST)\s_)?/i, '') }

@kennyj
kennyj commented Aug 1, 2012

In master, I guess we already fixed this .

1341         order_columns = orders.collect do |s|
1342           s = s.to_sql unless s.is_a?(String)
1343           s.gsub(/\s+(ASC|DESC)\s*(NULLS\s+(FIRST|LAST)\s*)?/i, '')
1344         end
@kennyj
kennyj commented Aug 1, 2012

oops. according to #5168, I fixed this issue to master, and you were original reporter...

I backport #5168 to 3-2-stable. How about kennyj@08afa95 ?

@matrol26
matrol26 commented Aug 1, 2012

Looks good.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment