Alas! Poor old MySql version 4.0.2 and the IN Clause

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.

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,