[1.0.0] Exception on 'SELECT *, MAX(...) FROM ...' query

[proninyaroslav]

It's easier to understand this from an example. I tried to make a simplified version of what is used in my project. Let's say we have the following null-safety code:

// foo.dart

@entity
class Foo {
  @primaryKey
  final int id;

  Foo({required this.id});
}

// foo_dao.dart

@dao
abstract class FooDao {
  @Query('SELECT *, MAX(id) FROM Foo')
  Future<List<Foo>> getMax();
}

database.g.dart will generate code like this for the getMax() method:

// database.g.dart

  @override
  Future<List<Foo>> getMax() async {
    return _queryAdapter.queryList('SELECT *, MAX(id) FROM Foo',
        mapper: (Map<String, Object?> row) => Foo(id: row['id'] as int));
  }

But there will be an exception when calling the method if the DB is empty. SELECT *, MAX(id) FROM Foo query is an important condition for reproducing the error. If you write just SELECT * FROM Foo, there will be no error.

type 'Null' is not a subtype of type 'int' in type cast
package:example/database.g.dart 110:65              _$FooDao.getMax.<fn>
package:floor/src/adapter/query_adapter.dart 40:36  QueryAdapter.queryList.<fn>
dart:_internal                                      ListIterable.toList
package:floor/src/adapter/query_adapter.dart 40:43  QueryAdapter.queryList

[proninyaroslav]

I modified the example to make it even easier.

[proninyaroslav]

@vitusortner
By the way, it's likely that this code didn't work even before the migration to null safety. Unfortunately I didn't write a test for it, and in the old version of floor it silently set all entity fields to null. But with the advent of null safety, such assignment is denied for non-null types. Quite helpful :D

[vitusortner]

I started investigating this issue. Does it only happen when the database is empty?

[proninyaroslav]

Yes.

[proninyaroslav]

I modified the example. The database is not empty:

test('Get max', () async {
    await taskDao.insertTask(Task(null, 'test'));
    print(await taskDao.getMax());
  });

Output:

[Task{id: 1, message: test}]
✓ Get max

The database is empty:

test('Get max', () async {
    print(await taskDao.getMax());
  });

Output:

type 'Null' is not a subtype of type 'String' in type cast
package:example/database.g.dart 152:52              _$TaskDao.getMax.<fn>
package:floor/src/adapter/query_adapter.dart 40:36  QueryAdapter.queryList.<fn>
dart:_internal                                      ListIterable.toList
package:floor/src/adapter/query_adapter.dart 40:43  QueryAdapter.queryList

[proninyaroslav]

@vitusortner
1.0.1 version still has this problem.

[proninyaroslav]

@vitusortner
Hi. Any ETA for issue resolving?

[mqus]

I looked at it briefly and I'm not sure what you're expecting the query to return, purely from an sql viewpoint. Afaik, on an empty database, this query will normally return no rows, but due to the max() function will return a single (aggregated) row, where all other columns are set to null. If the returned type (in this case Foo) can't set its values to null, thats when you get the error. As this is specified behaviour by SQL itself, I don't see the bug here.

[proninyaroslav]

@mqus
So, what to do in this situation? I can’t give up the list here, just like I can’t make all entity fields as nullable (this is at least silly)

[mqus]

I don't think this would work either way because the ´MAX(id)´ will not get mapped either way(as you can see in the generated code). Where should it even be mapped to?

Maybe you could explain your situation(what do you have and what do you want to achieve) from a higher level and we could start from there.

I want to add that we sadly do not yet support arbitrary return values for queries, so returning the maximum might require workarounds.

[proninyaroslav]

@mqus
The situation is to get all entities with the highest priority, the priority can be duplicated (therefore, a list is needed). It's probably easier to solve this problem on the Dart side by doing sorting or searching through the list.

[mqus]

What you are trying to do should be easy with floor and needs no workarounds. The query you could try is
SELECT * FROM mytable WHERE prio = (SELECT MAX(prio) FROM mytable).

(You'll have to replace prio and mytable with your actual priority column and the name of your entity)

(Updated: I have tried this in sqlite and it works as expected: no output in an empty table and only the rows with the maximum prio if entries are there).

[proninyaroslav]

Yes, it works.

[proninyaroslav]

Alternate query, that works:

SELECT *, MAX(id) FROM Foo GROUP BY id

[mqus]

I don't think this query does what you think it does. It is semantically completely different.

[mqus]

Issue seems to be solved, so I'm closing this. Reopen if you disagree :)