sidorares / sidorares/node-mysql2
Problem using `execute` and boolean parameters
Nobody has claimed this yet.
- Dominant language
- TypeScript
- Stars
- 4.4k
- Forks
- 680
- Avg merge
- 9h 7m
- Merged PRs (30d)
- 59
Description
First of all, great job with this module! It was a really smooth transition from the mysql module.
Now to the problem...
I have a table with a isAddon TINYINT(1) NOT NULL column.
Such a column is converted to a boolean when selecting from the table, but when using a boolean parameter to filter the table only works with the query() method and not execute(). I get the same result no matter if I pass true or false as a parameter, see example below.
This does not work:
// running a query where I want: isAddon=0
db.execute('select count(*) from MyTable where isAddon = ?', [false]).then(console.log)
Promise { <pending> }
> [ [ BinaryRow { 'count(*)': 680 } ],
[ { catalog: 'def',
schema: '',
name: 'count(*)',
orgName: '',
table: '',
orgTable: '',
characterSet: 63,
columnLength: 21,
columnType: 8,
flags: 129,
decimals: 0 } ] ]
// running a query where I want: isAddon=1
> db.execute('select count(*) from MyTable where isAddon = ?', [true]).then(console.log)
Promise { <pending> }
> [ [ BinaryRow { 'count(*)': 680 } ],
[ { catalog: 'def',
schema: '',
name: 'count(*)',
orgName: '',
table: '',
orgTable: '',
characterSet: 63,
columnLength: 21,
columnType: 8,
flags: 129,
decimals: 0 } ] ]
As you can see the results are the same, I get 680 number of rows for both queries.
Using query() works:
// running the query where I want: isAddon=0
> db.query('select count(*) from MyTable where isAddon = ?', [false]).then(console.log)
Promise { <pending> }
> [ [ TextRow { 'count(*)': 680 } ],
[ { catalog: 'def',
schema: '',
name: 'count(*)',
orgName: '',
table: '',
orgTable: '',
characterSet: 63,
columnLength: 21,
columnType: 8,
flags: 129,
decimals: 0 } ] ]
// running the query where I want: isAddon=1
> db.query('select count(*) from MyTable where isAddon = ?', [true]).then(console.log)
Promise { <pending> }
> [ [ TextRow { 'count(*)': 7 } ],
[ { catalog: 'def',
schema: '',
name: 'count(*)',
orgName: '',
table: '',
orgTable: '',
characterSet: 63,
columnLength: 21,
columnType: 8,
flags: 129,
decimals: 0 } ] ]
As you can see I get different results, which are correct, 680 number of rows where isAddon=0 and 7 rows where isAddon=1.
Converting boolean's to number's also work with execute():
> db.execute('select count(*) from MyTable where isAddon = ?', [Number(false)]).then(console.log)
Promise { <pending> }
> [ [ BinaryRow { 'count(*)': 680 } ],
[ { catalog: 'def',
schema: '',
name: 'count(*)',
orgName: '',
table: '',
orgTable: '',
characterSet: 63,
columnLength: 21,
columnType: 8,
flags: 129,
decimals: 0 } ] ]
> db.execute('select count(*) from MyTable where isAddon = ?', [Number(true)]).then(console.log)
Promise { <pending> }
> [ [ BinaryRow { 'count(*)': 7 } ],
[ { catalog: 'def',
schema: '',
name: 'count(*)',
orgName: '',
table: '',
orgTable: '',
characterSet: 63,
columnLength: 21,
columnType: 8,
flags: 129,
decimals: 0 } ] ]
NodeJS version: v6.7.0
mysql2 version: 1.1.1
Contributor guide
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Research direction
Reproduce the difference between the execute() and query() entry points with boolean and numeric parameters against a TINYINT(1) column. Trace parameter handling for execute(), then add coverage that distinguishes true from false and confirms the existing numeric behavior.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- javascript, mysql, node.js
- Domain
- backend, databases
- Issue type
- Bug
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 48/100