Alas! Poor old MySql version 4.0.2 and the IN Clause


#1

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.


#2

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
removed_email_address@domain.invalid