Bug #43966 Canonical query does not treat \N as NULL

• Submitted: 30 Mar 2009 17:26
• Modified: 22 May 2017 8:11
• Reporter: Diego Medina
• Status: Won't fix
• Category: MySQL Enterprise Monitor: Agent
• Severity: S3 (Non-critical)
• Version: 2.1.0.1019
• OS: Any
• Assigned to: Assigned Account
• CPU Architecture: Any

[30 Mar 2009 17: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 2009 17: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")