SQL query (Postgres) works normally, but not through ActiveRecord

Question

I've got a SQL query in Postgres that works fine from a SQL console/tool, but not through Rails' ActiveRecord (ActiveRecord::Base.connection.execute/select_all). I've tried a bunch of stuff like escaping quotes, calling ActiveRecord::Base.quote/sanitize to no avail - ActiveRecord returns an empty set where I've verified a tuple gets returned with this query.

SELECT
      ... blah blah
      FROM
        ... joins joins joins
        inner join core.pat_assignments assignment on assignment.correspondent_alias_id = out_alias.id
        inner join core.pats patent on patent.id = assignment.pat_id and (select regexp_matches(patent.us_class_current, '(\w+)\/')) = '{D02}'
      where
        in_alias.id in (1987, 5004)

The funny thing is, it returns something if I take out the last inner join line, specifically the regex match. So there's something with:

(select regexp_matches(patent.us_class_current, '(\w+)\/')) = '{D02}'

that's making it throw up, but I just can't figure out why... any suggestions would be greatly appreciated!

Look in the development.log file; you'll see the actual SQL that results from the variants. If it's not clear how they are different from that, post the differences (real ones, no "blah, blah" :-) and someone can probably help 'splain the differences. — Tom Harrison
– Tom Harrison, Commented Mar 27, 2012 at 1:58

mu is too short · Accepted Answer · 2012-03-27 06:15:34Z

You need to double that \ to get \w down to the regex engine and then you have to double each of those to get them past Ruby's string literal handling. And you should use E'' to avoid a warning. Also, you don't need that extra SELECT, you can compare the regexp_matches return value directly. So, something like this should work:

inner join ... and regexp_matches(patent.us_class_current, E'(\\\\w+)/') = array['D02']

There's no need to escape a slash in a PostgreSQL regex so I took that out too. Embedding a language (regex) inside a language (PostgreSQL's SQL) inside another language (Ruby) tends to get a bit messy when they all want to use the same escape character.

For example, in psql these things happen:

psql=> select regexp_matches('D03/pancakes', E'(\w+)/');
 regexp_matches 
----------------
(0 rows)

psql=> select regexp_matches('D03/pancakes', E'(\\w+)/');
 regexp_matches 
----------------
 {D03}
(1 row)

psql=> select regexp_matches('D03/pancakes', E'(\\w+)/') = array['D03'];
 ?column? 
----------
 t
(1 row)

And then from the Rails console:

> ActiveRecord::Base.connection.select_rows(%q{select regexp_matches('D03/pancakes', E'(\w+)/')})
   (0.5ms)  select regexp_matches('D03/pancakes', E'(\w+)/')
 => [] 
> ActiveRecord::Base.connection.select_rows(%q{select regexp_matches('D03/pancakes', E'(\\w+)/')})
   (1.9ms)  select regexp_matches('D03/pancakes', E'(\w+)/')
 => [] 
> ActiveRecord::Base.connection.select_rows(%q{select regexp_matches('D03/pancakes', E'(\\\\w+)/')})
   (0.4ms)  select regexp_matches('D03/pancakes', E'(\\w+)/')
 => [["{D03}"]] 
> ActiveRecord::Base.connection.select_rows(%q{select regexp_matches('D03/pancakes', E'(\\\\w+)/') = array['D03']})
   (1.4ms)  select regexp_matches('D03/pancakes', E'(\\w+)/') = array['D03']
 => [["t"]]

Collectives™ on Stack Overflow

SQL query (Postgres) works normally, but not through ActiveRecord

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related