I have found what appears to be a bug within FQL when using the union clause with subqueries.

 

The query below gives the expected result

 

SELECT d_OrganisationName

FROM ORGANISATION

WHERE a__kp_t_ORGANISATION NOT IN

  (

    SELECT a_kf_t_Organisation

    FROM ORGANISATION_CONTACT_LINK

    WHERE a_kf_t_Contact = '" & CONTACT::a__kp_t_CONTACT & "'

  )

 

which is a list all organisations that the contact isn't currently linked to

 

However, I also want to combine this result with another result, for example some default organisation:

 

SELECT d_OrganisationName

FROM ORGANISATION

WHERE d_OrganisationName = 'Default Organisation'

 

On it's own, the above query also gives the expected result, which is just 'Default Organisation'

 

However, when I put a union clause between the two queries like so

 

SELECT d_OrganisationName

FROM ORGANISATION

WHERE a__kp_t_ORGANISATION NOT IN

  (

    SELECT a_kf_t_Organisation

    FROM ORGANISATION_CONTACT_LINK

    WHERE a_kf_t_Contact = '" & CONTACT::a__kp_t_CONTACT & "'

  )

 

UNION

 

SELECT d_OrganisationName

FROM ORGANISATION

WHERE d_OrganisationName = 'Default Organisation'

 

The results include the combination of the two queries PLUS the result of the subquery, which is an id, i.e.

 

Some Org

Some Org

Default Org

OrgId1FromSubquery

OrgId2FromSubquery

 

It seems like FileMaker is seeing three result sets to UNION instead of two: the first query, the second query AND the subquery in the first query.

 

Has anyone else experienced this or have any suggestions for correcting it?

 

Thanks,

 

Alec

Could it be an order of precedence issue where the UNION throws together the SELECT for the IN plus the last SELECT and not what you expect (the result of the first SELECT plus the result of the 3rd SELECT)?  Experiment a bit with parentheses and see if that solves anything

Many thanks for the suggestion Wim. I did try some parenthesis experiments but they didn't seem to alter the result. I think the next step is to recreate this using a MySQL database to make sure I'm not just misusing the UNION keyword. I'll report back once I've given it a go.

Just done the same test with a MySQL database and the UNION keyword performs as expected, so this does seem to be a FileMaker-specific issue.

An update to this: The bug seems to be connected to the IN clause. I recently replaced an equals with an IN and the results from the sub-query appeared in the result set again. Shame =ANY doesn't work in FileMaker for a comparison.

 

I really should get this one reported.