Moor: Unexpected warning: Expected a colon (:) followed by a query

[johrpan]

For this query in my .moor file:

worksByComposer:
SELECT DISTINCT A.* FROM works A, works B ON A.id = B.part_of
    WHERE A.composer = :id OR B.composer = :id;

I get the following unexpected warning from the builder:

[WARNING] moor_generator:moor_generator on lib/database.dart:
line 45, column 46: Expected a colon (:) followed by a query
   ╷
45 │ SELECT DISTINCT A.* FROM works A, works B ON A.id = B.part_of
   │                                              ^
   ╵

If this warning is intended here, I would like to know what I'm doing wrong. Thanks a lot!

EDIT: I just noticed that the query is also not working as expected. It generates the following function which omits the intended parameter :id:

Selectable<Work> worksByComposer() {
    return customSelectQuery('SELECT DISTINCT A.* FROM works A, works B',
        variables: [], readsFrom: {works}).map(_rowToWork);
  }

[simolus3]

In my understanding the query is invalid indeed. You use a join constraint (ON A.id = B.part_of), but those only work with join operators, not when selecting multiple tables with a comma. So something like FROM works A LEFT JOIN works B ON ... should work.

When we hit a parsing error (like in this case), we try to make the best out of it. If we just failed as soon as we hit any error, working on a moor file with multiple syntax errors is very annoying - so we try to report all of them. This means that moor will do some lax parsing in case of an error. In this case, it saw that the part until the ON is a valid query on its own, and decided that what follows has to be another query. So, it interprets the ON as a query name and then complains about a missing colon.

In this case, the parser recovery resulted in an unfortunate error message that made things more confusing. In general this is tricky to get right and the errors only indicate a rough position, but this seems like an easy case to improve.

[simolus3]

This will be fixed in the next moor version - thanks again for the report.

[johrpan]

Thanks for fixing the warning! I got this confusing syntax from the w3schools SQL tutorial. I guess, Google brought me there. Afterwards I slipped the ON in and the confusion was complete. The funny thing is, that this query works just fine and as expected in SQLite, otherwise I hadn't filed an issue. Of course I'll rewrite it anyway. Thanks for the explanation!

[simolus3]

this query works just fine and as expected in SQLite

You're right, good catch! This is a parser bug indeed, I think I've misread the syntax diagram for selects. This should be fixed with 1a2d3bdee75d6dc4defeef480283c38db5fba1fb, so in the next moor version that query should be parsed correctly. Until then, you can manually specify the join operator as a workaround.