MySQL Bugs: #43965: Canonical query does not replace NULL for literal ?

Bug #43965	Canonical query does not replace NULL for literal ?
Submitted:	30 Mar 2009 17:21	Modified:	22 May 2017 8:13
Reporter:	Diego Medina	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Enterprise Monitor: Agent	Severity:	S3 (Non-critical)
Version:	2.1.0.1019	OS:	Any
Assigned to:	Jan Kneschke	CPU Architecture:	Any

Description:
SELECT NULL should show up as SELECT ? on the Query Analyzer tab

But SELECT 1 IS NULL should be SELECT ? IS NULL

How to repeat:
1- Install and start the service manager and agent
2- Send this query through the agent

SELECT NULL;

and then

SELECT 1;

3- Wait until they appear on the Query Analyzer tab

Suggested fix:
Treat SELECT NULL as SELECT 1

=== modified file 'tests/unit/lua/quan.lua'
--- tests/unit/lua/quan.lua     2009-03-04 19:00:08 +0000
+++ tests/unit/lua/quan.lua     2009-03-30 17:22:24 +0000
@@ -141,3 +141,25 @@
 com_query_normalize("/*! */SELECT 1",
        "/*! */ SELECT ? ", "", "SELECT")
 
+--[[
+Bug #43965 - Canonical query does not replace NULL for literal ?
+
+Special case for NULL as literal or keyword
+
+--]]
+com_query_normalize("SELECT NULL",
+    "SELECT ? ", "", "SELECT")
+com_query_normalize("SELECT -NULL",
+    "SELECT ? ", "", "SELECT")
+com_query_normalize("SELECT - NULL",
+    "SELECT ? ", "", "SELECT")
+com_query_normalize("SELECT foo FROM tbl WHERE fld IN ( NULL , 1 )",
+    "SELECT foo FROM tbl WHERE bar IN ( ? /* , ... */ ) ", "", "SELECT")    
+com_query_normalize("INSERT INTO tbl VALUES (NULL, 1)",
+    "INSERT INTO tbl VALUES( ? ) ", "", "INSERT")
+com_query_normalize("INSERT INTO tbl VALUES ( NULL ), ( 1 )",
+    "INSERT INTO tbl VALUES( ? /* , ... */ ) ", "", "INSERT")
+com_query_normalize("SELECT 1 IS NULL",
+    "SELECT ? IS NULL", "", "SELECT")
+com_query_normalize("SELECT 1 IS NOT NULL",
+    "SELECT ? IS NOT NULL", "", "SELECT")

We either need a full-blown parser to distinguish "IS [NOT] NULL" from "`foo` = NULL", or (hack, hack) post-process the normalized query and replace "IS [NOT] ?" with "IS [NOT] NULL".

Eric Herman writes: 
Even without a full blown parser, we're able to address at least some of these issues in a non-cheesy-hack way.
As of today's pushes we can deal with "IN ( 1, NULL, 3 )" as "IN ( ? , ? , ? )" and VALUES ( 1, NULL, 3 ) as  "VALUES ( ? , ? , ? )"
However the solution for the general case remains hard or ugly.

For instance this works:
com_query_normalize("update foo set bar=0 where foo_id=7",
        "UPDATE foo SET bar = ? WHERE foo_id = ? ", "", "UPDATE")

But this does not:
com_query_normalize("update foo set bar=NULL where foo_id=7",
        "UPDATE foo SET bar = ? WHERE foo_id = ? ", "", "UPDATE")

Perhaps we can address more of the cases without a full blown parser or a cheesy hack.