Bug #43966 Canonical query does not treat \N as NULL
Submitted: 30 Mar 19:26
Reporter: Diego Medina
Status: Verified
Category:Monitoring: Agent Severity:S3 (Non-critical)
Version:2.1.0.1019 OS:Any
Assigned to: MEM Agent Devs Target Version:2.0 maint release

[30 Mar 19:26] Diego Medina 
Description:
\N is the same as NULL

so SELECT NULL should be the same as SELECT \N when displayed on the Query Analyzer tab

Right now SELECT \N is shown as 

SELECT \ `N`

Similar to http://bugs.mysql.com/bug.php?id=43965

How to repeat:
1- Install and start the service manager and an agent
2- Send these queries through the agent

SELECT \N;

SELECT NULL;

3- Refresh the Query Analyzer tab a few times until you see both queries there
[30 Mar 19:29] Diego Medina 
=== 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:27:33 +0000
@@ -141,3 +141,48 @@
 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")
+
+--[[
+Bug #43966
+
+Special case for \N == NULL
+
+--]]
+com_query_normalize("SELECT \N",
+    "SELECT ? ", "", "SELECT")
+com_query_normalize("SELECT -\N",
+    "SELECT ? ", "", "SELECT")
+com_query_normalize("SELECT - \N",
+    "SELECT ? ", "", "SELECT")
+com_query_normalize("SELECT foo FROM tbl WHERE fld IN ( \N , 1 )",
+    "SELECT foo FROM tbl WHERE bar IN ( ? /* , ... */ ) ", "", "SELECT")    
+com_query_normalize("INSERT INTO tbl VALUES (\N, 1)",
+    "INSERT INTO tbl VALUES( ? ) ", "", "INSERT")
+com_query_normalize("INSERT INTO tbl VALUES ( \N ), ( 1 )",
+    "INSERT INTO tbl VALUES( ? /* , ... */ ) ", "", "INSERT")
+com_query_normalize("SELECT 1 IS \N",
+    "SELECT ? IS NULL", "", "SELECT")
+com_query_normalize("SELECT 1 IS NOT \N",
+    "SELECT ? IS NOT NULL", "", "SELECT")