[LINQ2SQL BUG] Nullable BIT(1) columns have their null-value rows always evaluated to true for 'COLUMN_NAME ?? false'

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for MySQL
Post Reply
ksidirop
Posts: 1
Joined: Wed 28 Mar 2018 10:30

[LINQ2SQL BUG] Nullable BIT(1) columns have their null-value rows always evaluated to true for 'COLUMN_NAME ?? false'

Post by ksidirop » Wed 28 Mar 2018 10:59

Libs employed:

Devart.Data.dll => 5.0.1878.0
Devart.Data.MySql.dll => 8.10.1086.0
Devart.Data.MySql.Design.dll => 8.10.1086.0
Devart.Data.MySql.Entity.EF6.dll => 8.10.1086.0
EntityFramework => 6.2.0

We are targeting .net4.7.1 from an ASP.NET MVC5 project. Our repositories are build on top of an EF .edmx (db-first approach). Our queries look like so:

var results = _db.NB_FILTERS
.Select(x => new ReportFiltersDTO { IsHiddenSubFilter = x.NFS_SUBFILTER_YN ?? false })
.ToList();

NFS_SUBFILTER_YN is declared as a boolean in our .edmx aka:

<Property Name="NFS_SUBFILTER_YN" Type="boolean" />

The NB_FILTERS ddl is like so:

CREATE TABLE NB_FILTERS (
[...]
NFS_SUBFILTER_YN BIT(1) NULL,
[...]
)

In such a scenario if certain rows are null the given linq expression returns 'true' for all of them instead of 'false'. The culprit appears to be in the auto-generated sql:

actual => CASE WHEN Extent1.NFS_SUBFILTER_YN IS NULL THEN 0 ELSE Extent1.NFS_SUBFILTER_YN END
corrected => CASE WHEN Extent1.NFS_SUBFILTER_YN IS NULL THEN 1 ELSE CAST(Extent1.NFS_SUBFILTER_YN AS SIGNED) END

Sidenote:

Interestingly enough such queries work when the columns involved are nullable decimal(x,y) columns [the auto-generated sql correctly employs CAST(... AS SIGNED)]. It appears that somehow bit(x) was omitted from the list of types which are eligible for such handling.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: [LINQ2SQL BUG] Nullable BIT(1) columns have their null-value rows always evaluated to true for 'COLUMN_NAME ?? false

Post by Shalex » Fri 30 Mar 2018 13:04

Thank you for your report. We will notify you when the issue is fixed.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: [LINQ2SQL BUG] Nullable BIT(1) columns have their null-value rows always evaluated to true for 'COLUMN_NAME ?? false

Post by Shalex » Fri 27 Apr 2018 10:52

The bug with using BIT(1) column in the CASE clause in EF1/EF4/EF5/EF6 is fixed: viewtopic.php?f=2&t=37092.

Post Reply