vapor/fluent-mysql-driver

.filter(.field == nil) not working anymore

proggeramlug opened this issue · 6 comments

proggeramlug commented

When using something like:

.filter(\field == nil)

the MySQL Output becomes "field == null", which really needs to be "field IS NULL".

tanner0101 commented

@proggeramlug are you sure that PR fixed the issue? That == operator is not the one used in .filter(<keyPath> == <value>). This is the operator used, and it already includes that check.

proggeramlug commented

@tanner0101 Try it yourself, the one you referenced (https://github.com/vapor/fluent/blob/master/Sources/Fluent/QueryBuilder/QueryBuilder%2BOperators.swift#L142) is not working for me, like it's not the one used here.

proggeramlug commented

Also checkout this one, where the same problem is observed: vapor/sql-kit#13

proggeramlug commented

Okay, I just created a test project that illustrates the case: https://github.com/proggeramlug/vapor-niltest

I also have a big hint for the fix (if you have no time to do this any time soon let me know and I'll gladly dig deeper).

So in the TodoController.swift is an import Fluent statement as well as an import FluentMySQL statement. If you remove the import FluentMySQL statement it works as expected:

$ vapor build --run --verbose
Building Project ...
Building Project [Done]
Running NilTest ...
[ INFO ] Migrating 'mysql' database (MigrationConfig.swift:69)
[mysql] [2018-06-21 21:09:36 +0000] SELECT COUNT(*) AS `fluentAggregate` FROM `fluent` []
[mysql] [2018-06-21 21:09:36 +0000] SELECT * FROM `fluent` ORDER BY `fluent`.`batch` DESC LIMIT 1 OFFSET 0 []
[mysql] [2018-06-21 21:09:36 +0000] SELECT * FROM `fluent` WHERE `fluent`.`name` = (?) LIMIT 1 OFFSET 0 [string("Todo")]
[ INFO ] Migrations complete (MigrationConfig.swift:73)
Running default command: .build/debug/Run serve
Server starting on http://localhost:8080
[mysql] [2018-06-21 21:09:46 +0000] SELECT * FROM `Todo` WHERE `Todo`.`id` = (?) AND `Todo`.`testField` IS NULL [integer8(1)]

However, if you leave the FluentMySQL in there it will be wrong:

$ vapor build --run --verbose
Building Project ...
Compile Swift Module 'App' (6 sources)
Linking ./.build/x86_64-apple-macosx10.10/debug/Run
Building Project [Done]
Running NilTest ...
[ INFO ] Migrating 'mysql' database (MigrationConfig.swift:69)
[mysql] [2018-06-21 21:11:09 +0000] SELECT COUNT(*) AS `fluentAggregate` FROM `fluent` []
[mysql] [2018-06-21 21:11:10 +0000] SELECT * FROM `fluent` ORDER BY `fluent`.`batch` DESC LIMIT 1 OFFSET 0 []
[mysql] [2018-06-21 21:11:10 +0000] SELECT * FROM `fluent` WHERE `fluent`.`name` = (?) LIMIT 1 OFFSET 0 [string("Todo")]
[ INFO ] Migrations complete (MigrationConfig.swift:73)
Running default command: .build/debug/Run serve
Server starting on http://localhost:8080
[mysql] [2018-06-21 21:11:12 +0000] SELECT * FROM `Todo` WHERE `Todo`.`id` = ? AND `Todo`.`testField` = ? [integer8(1), null]

So it is somehow tied to the FluentMySQL dependency. My PR mentioned above fixes it but the problem may be way before that one even kicks in.

tanner0101 commented

Oh, I see now. Great find. There is an overload on filter(...) that accepts the DB's generic filter type. For MySQL, that is MySQLExpression. Since MySQL also offers an == that generates a MySQLExpression, that one is being chosen instead.

Very interesting problem. I think that the filter overload that accepts the raw filter type should probably be prefixed with raw: to be clear.

Like:

filter(raw: \.foo == bar)

That way there's no ambiguity. With that said, you are correct and vapor/sql-kit#13 should be merged :)