Description:
Given the following query:
mysql> CREATE TABLE v0(c1 INT);
Query OK, 0 rows affected (0.46 sec)
mysql> CREATE VIEW v2(c3) AS SELECT SUM(c1) FROM v0;
Query OK, 0 rows affected (0.02 sec)
mysql> SET @XML = NULL;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @XML;
+------------+
| @XML |
+------------+
| NULL |
+------------+
1 row in set (0.00 sec)
mysql> SELECT extractValue ( @xml, '/a' );
+----------------------------------------------------------+
| extractValue ( @xml, '/a' ) |
+----------------------------------------------------------+
| NULL |
+----------------------------------------------------------+
1 row in set (0.00 sec)
-- Expected. Because @xml is NULL.
mysql> SELECT * FROM v2 WHERE extractValue ( @xml, '/a' );
+------+
| c1 |
+------+
| NULL |
+------+
1 row in set (0.00 sec)
-- Expected, since extractValue returns NULL.
mysql> SELECT COUNT( * ) FROM v2 WHERE extractValue ( @xml, '/a' );
+------------+
| COUNT( * ) |
+------------+
| 1 |
+------------+
1 row in set (0.00 sec)
-- Expected 0 because only NULL is returned. Get 1 instead.
----------------------------------------------------------
In the query above, we create table v0 with INT column c1. We then create VIEW v2 that shows the SUM of c1 from v0 as c3. We then access the newly create view v2 using the SELECT statements. Since the user defined variable @xml is NULL, we expect the function extractValue ( @xml, '/a' ) to always return NULL. In the third SELECT statements, the WHERE clause contains this extractValue function, the output of the query is NULL as expected. However, if we add COUNT(*) in the SELECT clause (the fourth SELECT stmt), because value NULL should never be counted inside the COUNT function, we expect the result to be 0, but 1 is returned.
We observe that by removing the aggregate function inside the CREATE VIEW statement, i.e.,
using statement: CREATE VIEW v2 (c3) AS SELECT c1 FROM v0;
can fix the problem and the fourth SELECT statement will output 0 as expected. And this behavior CANNOT be reproduce if we use functions other than extractValue() that returns NULL. Only extractValue() can trigger this problem in the SELECT COUNT(*) statement. We are not sure whether this is a bug or an expected behavior.
How to repeat:
In the mysql server version 8.0.27, use the following query commands:
mysql> CREATE TABLE v0(c1 INT);
Query OK, 0 rows affected (0.46 sec)
mysql> CREATE VIEW v2(c3) AS SELECT SUM(c1) FROM v0;
Query OK, 0 rows affected (0.02 sec)
mysql> SET @XML = NULL;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @XML;
+------------+
| @XML |
+------------+
| NULL |
+------------+
1 row in set (0.00 sec)
mysql> SELECT extractValue ( @xml, '/a' );
+----------------------------------------------------------+
| extractValue ( @xml, '/a' ) |
+----------------------------------------------------------+
| NULL |
+----------------------------------------------------------+
1 row in set (0.00 sec)
-- Expected. Because @xml is NULL.
mysql> SELECT * FROM v2 WHERE extractValue ( @xml, '/a' );
+------+
| c1 |
+------+
| NULL |
+------+
1 row in set (0.00 sec)
-- Expected, since extractValue returns NULL.
mysql> SELECT COUNT( * ) FROM v2 WHERE extractValue ( @xml, '/a' );
+------------+
| COUNT( * ) |
+------------+
| 1 |
+------------+
1 row in set (0.00 sec)
-- Expected 0 because only NULL is returned. Get 1 instead.
----------------------------------------------------------
Suggested fix:
mysql> CREATE TABLE v0(c1 INT);
mysql> CREATE VIEW v2(c3) AS SELECT SUM(c1) FROM v0;
mysql> SET @XML = NULL;
mysql> SELECT @XML; -- Returns NULL
mysql> SELECT extractValue ( @xml, '/a' ); -- Returns NULL
-- Expected. Because @xml is NULL.
mysql> SELECT * FROM v2 WHERE extractValue ( @xml, '/a' ); -- Return NULL
mysql> SELECT COUNT( * ) FROM v2 WHERE extractValue ( @xml, '/a' ); -- Should not return 1, should be 0 instead.
