balderdashy / balderdashy/sails

Wrong NOT NULL criteria on MYSQL

Open
#6,803 4 comments 0 reactions 0 assignees View on GitHub
helpful info or workaround mysql orm
Dominant language
JavaScript
Stars
22.8k
Forks
1.9k
PR merge metrics
No merged PRs in 30d

Description

**Node version**: 10.4.1
**Sails version** _(sails)_: 1.1.0
**ORM hook version** _(sails-hook-orm)_: 2.1.1
**DB adapter & version** _(sails-mysql)_: 1.0.1



Hi, i'm trying execute a simple find on MySQL 5.7.25 looking for records where a field is not null.
Waterline ORM should use "IS NOT NULL" instead of "!=" to compare NULLS in the generated SQL query.
This is the controller's relevant portion of code:

```javascript
let someRecord = await User.find({
where: {
and: [
{USER_ID: req.param('USER_ID')},
{
or: [
{END_DATE: {'!=': null}},
{END_SESSION_DATE: {'!=': null}},
]
}
],
},
limit: 3
});
```

This is the generated SQL query:

```sql
select *
from USER
where USER_ID = 1
and (END_DATE != NULL or END_SESSION_DATE != NULL)
limit 3
```
If you try to use {FIELD:null} as criteria is correctly translated to IS NULL instead of "="
Is this a regression of some kind ? it seems a pretty bad bug to me

Contributor guide

Open the contributing guide

Research direction

No source file or test is named. Start by reproducing the controller criteria against MySQL 5.7.25 and tracing the generated query through Waterline and the sails-mysql adapter. Done means the equivalent non-null criteria generate IS NOT NULL SQL and return the expected records without changing the existing IS NULL behavior.

Written by the indexing model from the issue text.

Assessment

Tech stack
javascript, mysql
Domain
backend, databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Clearly specified
Newbie friendliness
42/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.