# Alas! Poor old MySql version 4.0.2 and the IN Clause

**URL:** <https://www.ruby-forum.com/t/alas-poor-old-mysql-version-4-0-2-and-the-in-clause/168196>\
**Category:** Rails\
**Created:** [May 18, 2009, 6:31am UTC](https://www.ruby-forum.com/t/alas-poor-old-mysql-version-4-0-2-and-the-in-clause/168196 "2009-05-18T06:31:11Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![adeel\_bm](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/47e85d/32.png) [@adeel\_bm](https://www.ruby-forum.com/u/adeel_bm)\
**Post date:** [May 18, 2009, 6:31am UTC](https://www.ruby-forum.com/t/alas-poor-old-mysql-version-4-0-2-and-the-in-clause/168196/1 "2009-05-18T06:31:11Z")

</div>

While using IN clause in find\_by\_sql’s parametrized query, I got the  
error message from MySQl.  
The idea is to put a join on number of tables describe as:

contacts -\> id, status\_id  
profiles -\> id, contact\_id, first\_name, last\_name  
subprofiles -\> id, profile\_id, title, level\_id, company\_id  
companies -\> id, company\_name  
levels -\> id, name

contacts has one profile  
contacts\_users has many subprofiles

The ruby statements looks like:

if params[:company].blank? && params[:title].blank? && checkempty &&  
params[:last\_name].blank? && params[:first\_name].blank?  
return (flash[:notice] = “Enter atleast one search parameter!”)  
end  
query\_builder = "SELECT \* FROM `contacts` WHERE 1=1 AND  
`contacts`.`status_id` = 1 "

```
if !params[:company].blank? || !params[:title].blank? || !checkempty

```

|| !params[:first\_name].blank? || !params[:last\_name].blank?  
query\_builder += " AND `contacts`.`id` IN  
(  
SELECT `contact_id` FROM  
`profiles` WHERE 1=1"  
if !params[:company].blank? || !checkempty || !params[:title].blank?  
query\_builder += " AND `profiles`.`id` IN  
(  
SELECT `profile_id` FROM  
`subprofiles` WHERE 1=1 "  
if !params[:company].blank?  
query\_builder += " AND `subprofiles`.`company_id` IN  
(  
SELECT `id` FROM  
`companies` WHERE `company_name` like ‘%#{params[:company]}%’  
)"  
end  
if !checkempty  
query\_builder += " AND  
`subprofiles`.`level_id` IN  
(  
SELECT `id` FROM  
`levels` WHERE `levels`.`name` IN #{get\_level\_values}  
)"

```
   end
    if !params[:title].blank?
      query_builder += " AND `subprofiles`.`title` like

```

‘%#{params[:title]}%’"  
end  
query\_builder+=")"  
end  
unless params[:first\_name].blank?  
query\_builder += " AND `profiles`.`first_name` like  
‘%#{params[:first\_name]}%’"  
end  
unless params[:last\_name].blank?  
query\_builder += " AND `profiles`.`last_name` like  
‘%#{params[:last\_name]}%’"  
end  
query\_builder += “)”  
end

```
puts

```

“---------------------------------------------#{query\_builder}---------------------------------------------”  
@records = Contact.find\_by\_sql(query\_builder)

Providing all the parameters, the resultant Query looks like:  
SELECT \* FROM `contacts` WHERE 1=1 AND `contacts`.`status_id` = 1 AND  
`contacts`.`id` IN  
(  
SELECT `contact_id` FROM  
`profiles` WHERE 1=1 AND `profiles`.`id` IN  
(  
SELECT `profile_id` FROM  
`subprofiles` WHERE 1=1 AND `subprofiles`.`company_id` IN  
(  
SELECT `id` FROM  
`companies` WHERE `company_name` like ‘%sadd%’  
) AND `subprofiles`.`title`  
like ‘%sad%’) AND `profiles`.`first_name` like ‘%sad%’ AND  
`profiles`.`last_name` like ‘%sad%’)

The statement runs on mysql v. “5.1.30-community-log” (comes with wamp)  
perfectly fine, but the hosting guys r still sticked with v  
“4.0.27-standard”. Now when I run the script there, mysql return error  
saying:  
“Mysql::Error: 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 'SELECT `contact_id` FROM `profiles` WHERE 1=1 AND  
`profiles`…”

Seems like the subquery is returning NULL and in version 5.1, it returns  
an empty string ‘’ so that in bigger picture the outer queries are not  
affected, while in older v 4.0.2 it returns NOTHING so that it returns  
this error. Please, remedy the problem by giving the subsitute in orm or  
improving the query so that it could run on older version aswell, owing  
to the fact, it would be nearly imposible for the hosting guys to update  
the mysql version.

---

<div class="post-metadata">

**Author:** ![adeel\_bm](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/47e85d/32.png) [@adeel\_bm](https://www.ruby-forum.com/u/adeel_bm)\
**Post date:** [May 18, 2009, 4:49pm UTC](https://www.ruby-forum.com/t/alas-poor-old-mysql-version-4-0-2-and-the-in-clause/168196/2 "2009-05-18T16:49:18Z")

</div>

Abm Abm wrote:

> Please, remedy the problem by giving the subsitute in orm or  
> improving the query so that it could run on older version aswell, owing  
> to the fact, it would be nearly imposible for the hosting guys to update  
> the mysql version.

Find a new host. Seriously. MySQL 5 has been out for a long time, and  
if your host can’t support it, then they are not doing their job.

MySQL 5 is bad enough as a DB server; mySQL 4.0 is obsolete, owing to  
the lack of essential features such as subqueries. Your host should at  
least be able to upgrade to mySQL 4.1, which does support subqueries.

Better yet, forget about mySQL entirely and use PostgreSQL. It’s a  
_much_ better database server, and you’ll have fewer headaches.

## Best,

Marnen Laibow-Koser  
[http://www.marnen.org](http://www.marnen.org)  
removed\_email\_address@domain.invalid