sidorares / sidorares/node-mysql2

Problem using `execute` and boolean parameters

Open
#446 9 comments 0 reactions 0 assignees View on GitHub

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

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.