Generate nullable field from subqery

[minia68]

Hi, with this kind of query in .moor file: queryWithSubqeury: select t.column1, (select column2 from table2 where table1_id = t.id limit 1) table2_column2 from table1 t, moor generate result class with non nullable field for table2_column2(from subqery). If table2 have 0 rows, table2_column2 will be null and i got type cast error.

[simolus3]

Good catch, thanks. I've considered just making everything coming from a subquery nullable, but I'm afraid that may break existing code (especially code using COUNT(*) in a subquery where a non-nullable type would be more appropriate).

In the meantime, you could either use NULLIF(column2, NULL) which is a no-op and convinces moor that a column is nullable. Alternatively, you could convert the subquery to a join which should get analyzed correctly:

select t.column1, t2.column2
  from table1 t
  left outer join (select * from table2 where table1_id  = t.id limit 1) t2

[matehat]

Hello!

With moor_generator 4.3.1, and following schema and query:

CREATE TABLE inboxes (
    id TEXT PRIMARY KEY NOT NULL,
    group_id TEXT NOT NULL
);

CREATE TABLE assignable_users (
    user_id TEXT NOT NULL,
    inbox_id TEXT NOT NULL
);

inboxesForGroup:    SELECT *
                    FROM inboxes as i
                    LEFT JOIN assignable_users as au
                    ON au.inbox_id = i.id
                    WHERE group_id = :group_id;

The class generated for the query declares userId to be a non-nullable String. I'm pretty sure it should nullable.

[simolus3]

@matehat Very good catch, thanks! I've fixed this in 5d7cb7dbba58ea6e235275ebf09614d46994859d.

[matehat]

Awesome, thanks!

[knaeckeKami]

do you have any ETA for releasing these fixes?

[simolus3]

I'll try to release a new moor version this weekend. The fixes required pretty substantial changes to how queries are analyzed, so I couldn't push a small hotfix release.