Potentially incorrect query results - Beta Foreign Keys

[gnotisauton]

I just noticed a surprisingly straightforward case of incorrect query results.
I'm using the Beta Foreign Keys feature, so that might be a possible cause.

Reproduction steps

Steps I have taken to ensure that this is not caused by other factors than the database:

• ran queries using MySQL Workbench and SQLectron to eliminate IDE issue
• ran queries on a copy of the database running MSSQL to eliminate query issue

Steps I have not yet been able to take:

• run queries on another (non-Planetscale) MySQL instance to eliminate generic MySQL issue

Steps I will not be able to take:

• run queries on another (non-Planetscale) Vertex instance to eliminate generic Vertex issue

Data Model:

hosted on dbdiagram.io

Query:

SELECT DISTINCT
        p.Id,
        p.Firstname,
        p.Surname,
        prg.Title

FROM    Cohorts AS c1

    INNER JOIN
        CohortModules AS cm
    ON  c1.Id = cm.CohortId
    INNER JOIN
        Modules AS m
    ON  cm.ModuleId = m.Id
    INNER JOIN
        ModuleSubscriptions AS ms
    ON  m.Id = ms.ModuleId
    INNER JOIN
        Participants AS p
    ON  ms.ParticipantId = p.Id

    INNER JOIN
        CohortEnrollments AS ce
    ON  p.Id = ce.ParticipantId
    AND c1.Id <> ce.CohortId

    INNER JOIN
        Cohorts AS c2
    ON  ce.CohortId = c2.Id
    INNER JOIN
        Programmes AS prg
    ON  c2.ProgrammeId = prg.Id

WHERE   c1.Abbreviation = 'FTMSc210'
    AND p.Id = 1022744
--    AND prg.Title = 'Full-Time MSc in Business Administration'

Output

Produces the following result:

Id	Firstname	Surname	Title
1022744	Marcelo	Chen	Full-Time MSc in Business Administration
1022744	Marcelo	Chen	Preparation Course Full-Time Masters

From the previous, it seems to me that removing the comment should produce the following result:

Id	Firstname	Surname	Title
1022744	Marcelo	Chen	Full-Time MSc in Business Administration

However, removing the comment produces an empty table (no rows).

What did I miss?
I'd be happy to share login credentials to the database, just @-mention me or comment. (Not so happy to just paste them here ;) )

[orware]

Hello @gnotisauton,

I would be happy to work more closely with you via our https://support.planetscale.com/ site as a case like will likely require some additional review and back and forth to solve successfully and it would be easier to share further details with us through there.

One immediate thought that does come to mind however that would be good to rule out, especially if the Title values are entered manually into your Programmes table is whether it is possible that there is a trailing space, or extra character of some sort that would make it so that when you add the filter on the Title field that it actually doesn't match the values leading to the empty result set?

That's definitely been something that's occurred before and can be hard to catch and you might be able to even quickly verify if this may be the case by modifying the filter to something like AND prg.Title LIKE 'Full-Time%' and see if that returns a result. If it does, then the possibility of there being an extra unexpected character within the Full-Time MSc in Business Administration name is probably likely.

[gnotisauton]

Hi @orware,

🤦🏼‍♂️ you're right, the cause is indeed a trailing space. Thank you for your very patient and respectful reply.
This database and the above code was recently migrated from MS SQL to Planetscale, and the trailing space never caused an issue because MS SQL ignores trailing spaces in equality operations.

Cheers,
Laurens