trouble with automatically generated sql query

Question

I am having trouble with an automatically generated SQL query, that is based on several smaller queries stored in a table, and combined with what the user inputs.

I will show the inputs for a query that works, and then the query itself.

I will then show my inputs, and the query that fails.

If someone could point me what input I have used that is causing my query to fail, I would be most grateful.

fromclause:

((`clients` INNER JOIN `addresstorecord` 
    ON `clients`.`uuid` = `addresstorecord`.`recordid` 
    AND `addresstorecord`.`tabledefid`='tbld:6d290174-8b73-e199-fe6c-bcf3d4b61083' 
    AND addresstorecord.primary='1') 
INNER JOIN `addresses` ON `addresstorecord`.`addressid` = `addresses`.`uuid`)

displayfield:

IF(clients.company != '', 
   CONCAT(clients.company,
          IF(clients.lastname != '' OR clients.firstname != '', 
             CONCAT(' (',
                    IF(clients.lastname != '', clients.lastname, '{blank}'),
                    ', ',
                    IF(clients.firstname != '', clients.firstname, '{blank}'),
                    ')'),
             '')
          ),
   IF(clients.lastname != '' OR clients.firstname != '',
      CONCAT(IF(clients.lastname != '', clients.lastname, '{blank}'),
             ', ',
             IF(clients.firstname != '', clients.firstname, '{blank}')),
      ''))
)

secondaryfield:

IF(addresses.city != '' OR addresses.state !='' OR addresses.postalcode != '',
  CONCAT(IF(addresses.city != '', addresses.city, ''),
         ', ',
         IF(addresses.state != '', addresses.state, ''),
         ' ',
         IF(addresses.postalcode != '', addresses.postalcode, '')),
  'unspecified location')

classfield:

clients.type

searchfields:

clients.company, clients.firstname, clients.lastname

filterclause:

clients.inactive=0

The query which works:

SELECT
                COUNT(IF(clients.company != '', CONCAT(clients.company,IF(clients.lastname != '' OR clients.firstname != '', CONCAT(' (',if(clients.lastname != '', clients.lastname, '{blank}'),', ',if(clients.firstname != '', clients.firstname, '{blank}'),')'), '')), IF(clients.lastname != '' OR clients.firstname != '', CONCAT(if(clients.lastname != '', clients.lastname, '{blank}'),', ',if(clients.firstname != '', clients.firstname, '{blank}')), ''))) AS thecount
            FROM
                ((`clients` INNER JOIN `addresstorecord` ON `clients`.`uuid` = `addresstorecord`.`recordid` AND `addresstorecord`.`tabledefid`='tbld:6d290174-8b73-e199-fe6c-bcf3d4b61083' AND addresstorecord.primary='1') INNER JOIN `addresses` ON  `addresstorecord`.`addressid` = `addresses`.`uuid`)
            WHERE
                (clients.inactive=0)

                AND ( (clients.company LIKE 'g%'
OR clients.firstname LIKE 'g%'
OR clients.lastname LIKE 'g%'
) OR ((clients.company LIKE 'g%' OR clients.company LIKE '% g%'
OR clients.firstname LIKE 'g%' OR clients.firstname LIKE '% g%'
OR clients.lastname LIKE 'g%' OR clients.lastname LIKE '% g%'
)))
           SELECT DISTINCT
                IF(clients.company != '', CONCAT(clients.company,IF(clients.lastname != '' OR clients.firstname != '', CONCAT(' (',if(clients.lastname != '', clients.lastname, '{blank}'),', ',if(clients.firstname != '', clients.firstname, '{blank}'),')'), '')), IF(clients.lastname != '' OR clients.firstname != '', CONCAT(if(clients.lastname != '', clients.lastname, '{blank}'),', ',if(clients.firstname != '', clients.firstname, '{blank}')), '')) AS display,
                `clients`.`uuid` AS value,
                IF(addresses.city != '' OR addresses.state !='' OR addresses.postalcode != '', CONCAT(IF(addresses.city != '',addresses.city,''),', ',IF(addresses.state != '', addresses.state, ''),' ',IF(addresses.postalcode != '', addresses.postalcode, '')),'unspecified location') AS secondary,
                clients.type AS classname
            FROM
                ((`clients` INNER JOIN `addresstorecord` ON `clients`.`uuid` = `addresstorecord`.`recordid` AND `addresstorecord`.`tabledefid`='tbld:6d290174-8b73-e199-fe6c-bcf3d4b61083' AND addresstorecord.primary='1') INNER JOIN `addresses` ON  `addresstorecord`.`addressid` = `addresses`.`uuid`)
            WHERE
                (clients.inactive=0)

                AND ( (clients.company LIKE 'g%'
OR clients.firstname LIKE 'g%'
OR clients.lastname LIKE 'g%'
) OR ((clients.company LIKE 'g%' OR clients.company LIKE '% g%'
OR clients.firstname LIKE 'g%' OR clients.firstname LIKE '% g%'
OR clients.lastname LIKE 'g%' OR clients.lastname LIKE '% g%'
)))
            ORDER BY
                IF(clients.company != '', CONCAT(clients.company,IF(clients.lastname != '' OR clients.firstname != '', CONCAT(' (',if(clients.lastname != '', clients.lastname, '{blank}'),', ',if(clients.firstname != '', clients.firstname, '{blank}'),')'), '')), IF(clients.lastname != '' OR clients.firstname != '', CONCAT(if(clients.lastname != '', clients.lastname, '{blank}'),', ',if(clients.firstname != '', clients.firstname, '{blank}')), ''))
            LIMIT 0, 8

My inputs

fromclause.

guests

displayfield:

IF(guests.passport != '', CONCAT(guests.passport ,IF(guests.lastname != '' OR guests.passport!= '', CONCAT(' (',if(guests.lastname != '', guests.lastname, '{blank}'),', ',if(guests.firstname != '', guests.passport, '{blank}'),')'), '')), IF(guests.lastname != '' OR guests.passport!= '', CONCAT(if(guests.lastname != '', guests.lastname, '{blank}'),', ',if(guests.passport!= '', guests.passport, '{blank}')), ''))

secondaryfield:

classfield:

guests.id

searchfields:

guests.firstname, guests.lastname, guests.passport

filterclause:

guests.id=0

My query which fails:

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 'AS secondary,
                guests.id AS classname
            FROM
                guests
            WHERE
                (guests' at line 4

Statement: 
            SELECT DISTINCT
                IF(guests.passport != '', CONCAT(guests.passport ,IF(guests.lastname != '' OR guests.passport!= '', CONCAT(' (',if(guests.lastname != '', guests.lastname, '{blank}'),', ',if(guests.firstname != '', guests.passport, '{blank}'),')'), '')), IF(guests.lastname != '' OR guests.passport!= '', CONCAT(if(guests.lastname != '', guests.lastname, '{blank}'),', ',if(guests.passport!= '', guests.passport, '{blank}')), '')) AS display,
                `guests`.`uuid` AS value,
                 AS secondary,
                guests.id AS classname
            FROM
                guests
            WHERE
                (guests.id=0)

                AND ( (guests.firstname LIKE 'b%'
OR guests.lastname LIKE 'b%'
OR guests.passport LIKE 'b%'
) OR ((guests.firstname LIKE 'b%' OR guests.firstname LIKE '% b%'
OR guests.lastname LIKE 'b%' OR guests.lastname LIKE '% b%'
OR guests.passport LIKE 'b%' OR guests.passport LIKE '% b%'
)))
            ORDER BY
                IF(guests.passport != '', CONCAT(guests.passport ,IF(guests.lastname != '' OR guests.passport!= '', CONCAT(' (',if(guests.lastname != '', guests.lastname, '{blank}'),', ',if(guests.firstname != '', guests.passport, '{blank}'),')'), '')), IF(guests.lastname != '' OR guests.passport!= '', CONCAT(if(guests.lastname != '', guests.lastname, '{blank}'),', ',if(guests.passport!= '', guests.passport, '{blank}')), ''))
            LIMIT 0, 8

Answer 1

you don't have a field name selected with the secondary alias, should be something like

myField as secondary

replacing myField with the field from the table

Answer 2

In your parameters for the failing query, "secondaryfield" is empty. That is what is being substituted just before "AS secondary", so the result isn't valid SQL.