Queries with NOT IN clause

[brooly]

When I execute a query like:
SELECT * FROM T WHERE ID IN(?,....)
it works perfect...
But If I execute the same query in negative
SELECT * FROM T WHERE ID NOT IN(?,....)
I get the following error:
{
"code": "ER_WRONG_ARGUMENTS",
"sqlState": "#HY00"
}

I've checked the arguments and number of arguments and they're ok.

Thanks a lot for your support!

[sidorares]

Can you post full query text where error can be reproduced?

[sidorares]

also, what happens when you prepare & execute your query from the command line client? See examples how to use PREPARE here - http://dev.mysql.com/doc/refman/5.0/en/sql-syntax-prepared-statements.html

[brooly]

You can find attached my query, and what happens with it executing from the command line client. It works.

[sidorares]

create a gist please so I can copy&pasete text

[brooly]

I know that the problem is with NOT IN clause, because I've subdivied the query until get where is the error, and if I execute the simplified query:
SELECT D.ID_DATO FROM DATOS D WHERE ID_DATO NOT IN(?,?)
It fails, but the same query in positive works perfectly
SELECT D.ID_DATO FROM DATOS D WHERE ID_DATO IN(?,?)
Both of them works from the command line.

[brooly]

https://gist.github.com/brooly/6544575

[sidorares]

Thanks! On it :)

[sidorares]

Can't reproduce on a simple example. The ER_WRONG_ARGUMENTS indicates that number of arguments you are passing to prepared statement is different from what it actually expect. My example - https://gist.github.com/sidorares/6545549

[brooly]

You're right, your example works for me too, I don't know what's happening with my query because the number of arguments is right (If I remove the "NOT" it works). Anyway it's clear that is my fault, sorry for the inconvenience.

Thanks crack!

[sidorares]

if you set breakpoint (or dump with console.log) here - https://github.com/sidorares/node-mysql2/blob/master/lib/commands/execute.js#L48 - you'll be able to see expected number of parameters.

[sidorares]

or, even better - here - https://github.com/sidorares/node-mysql2/blob/master/lib/commands/execute.js#L60
See parameterDefinitions array with parameter types etc

[sidorares]

I'll probably add some way to access parameterDefinitions from response and error, same way you can access field definitions

[brooly]

I'll try, once I solve it I will tell you 👍