Many-to-many populates with limit broken

[devinivy]

I'm experiencing an issue populating with limit. The underlying issue is that when joining, you can of course obtain duplicate records, meaning the limit wont work correctly. The code here does the following...

Say users and files are in a many-to-many relationship, joined on their ids. I want to get 10 of user #1's files. Here's roughly how the sequel looks:

SELECT files.* FROM files
    INNER JOIN user_has_file ON user_has_file.fileId=files.id
    WHERE files.id IN
        (SELECT user_has_file.fileId FROM user_has_file
            WHERE user_has_file.userId = 1)
    LIMIT 10;

I think the issue is that the subquery may return file ids for files that belong to other users. Thus, after the join, some records might have duplicate file ids (since one row may be for user 1 and another row for user 2, who shares the same file). By the time the join is done and is being used, fewer than 10 unique files were found even though the user has more than 10 files.

I'm thinking this might be the right idea:

SELECT files.* FROM files
    INNER JOIN user_has_file ON user_has_file.fileId=files.id
    WHERE files.id IN
        (SELECT user_has_file.fileId FROM user_has_file
            WHERE user_has_file.userId = 1)
        AND user_has_file.userId = 1
    LIMIT 10;

Example rows returned when selecting the file id and joined userId. Notice files having id of 1 and 2 are duplicated, thus only 8 unique records are returned.

id	userId
14	1
1	1
3	1
4	1
2	1
32	1
42	1
10	1
1	40
2	40

[devinivy]

But why not,

SELECT files.* FROM files
    INNER JOIN user_has_file ON user_has_file.fileId=files.id
    WHERE user_has_file.userId = 1
    LIMIT 10;

I realize this stuff is complicated, so I may be missing something.

@RWOverdijk any idea?

[devinivy]

Failing test: balderdashy/waterline-adapter-tests#83
I'd call this bug fairly hot!

CC @tjwebb @dmarcelino

[dmarcelino]

Agreed, it's a serious bug.

[devinivy]

Proposed change: #54. Makes me sweat!

[particlebanana]

Published 0.5.0 with included fix

[dmarcelino]

nice 👍