[CONPY-54] Wrong substitution Created: 2020-04-06  Updated: 2020-04-06  Resolved: 2020-04-06

Status: Closed
Project: MariaDB Connector/Python
Component/s: DBAPI 2.0
Affects Version/s: None
Fix Version/s: N/A

Type: Bug Priority: Critical
Reporter: Luciano Barcaro Assignee: Georg Richter
Resolution: Won't Fix Votes: 0
Labels: None


 Description   

This query gives me a SQL error if I use parameter substitution:

db = mariadb.connector(...)
 
qr = db.cursor(buffered=True)
 
qr.execute('select if((3 is ?), ?, ?)', (None, 'a', 'b'))

This should be translated to:

select if((3 is null), 'a', 'b')

Is there a way to see/get formatted query just before execution?

 Comments   
Comment by Georg Richter [ 2020-04-06 ]

If a statement contains placeholders, MariaDB Connector/Python will use the binary protocol - these is no substitution done in Connector/Python or Connector/C.

if is ? is not supported in binary protocol. It would also end up in a syntax error if a non NULL value would be passed, if = ? is supported but will fail if a NULL value would be passed. Since you already know that you want to compare against NULL, the correct syntax would be if(IS_NULL( ? ), ?, ?)

We decided to use the binary protocol in Connector/Python due to performance reasons: When connecting to MariaDB 10.2 or newer execute_many using delete or update/replace statements is up to 20 times faster compared to other connectors since it uses MariaDB's bulk feature of the binary protocol. For insert statements it's less, since e.g. MySQL Connector/Python rewrites the statement using INSERT with multiple value syntax.

Generated at Thu Feb 08 03:29:54 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.