balderdashy / balderdashy/sails
Wrong NOT NULL criteria on MYSQL
- 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
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