mysql-ruby and subqueries

[Andrew Muro]

I'm trying to use the Ruby MySQL module to do a subquery, but it won't work. The query is:

SELECT family_id FROM cluster.protein_family WHERE analysis_id=1
AND species_id=14 GROUP BY family_id HAVING family_id IN
(SELECT family_id FROM cluster.protein_family WHERE species_id=3
AND analysis_id=1 GROUP BY family_id HAVING COUNT(*)=1)

This query works without a problem if I do it through the MySQL client, but not using the ruby API. This is what happens:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND species_id=14 GROUP BY family_id HAVING family_id IN (SELECT family_id FROM ' at line 1 (Mysql::Error)

I'm using MySQL-Ruby 2.7. Does anyone have an idea of what is going on here? Thanks.

Andrew