| Bug #90002 | IS NULL with not null column works incorrectly with ROLLUP | ||
|---|---|---|---|
| Submitted: | 10 Mar 2018 17:48 | Modified: | 13 Mar 2018 6:49 |
| Reporter: | Victor Bazhenov | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.7.21, 8.0.0 | OS: | FreeBSD |
| Assigned to: | CPU Architecture: | x86 | |
| Tags: | IS NULL | ||
[12 Mar 2018 14:11]
MySQL Verification Team
Hi! Thank you for your bug report. However, it is not a bug. If you read chapter 12.19.2 of our Reference Manual, you will notice that NULL value in the composite output is the obligatory last value for the grand total rows. This is totally independent of whether the columns is nullable or not. Hence, our ROLLUP works as intended and according to SQL standard.
[13 Mar 2018 6:49]
Victor Bazhenov
Excuse me for importunity, i spoke inaccurately or misunderstood the answer.
I'm not confused by NULL value of `c` column in grand total line, i'm confused that IS NULL result in 0 value.
I.e., in grand total line i expect 1 value in second column ("c is null") of result table and "c is null" value in last column ("case when ...") of result table.
Such result is obtained when `c` is nullable.
ALTER TABLE t CHANGE COLUMN c c int;
SELECT
c,
c IS NULL,
c IS NOT NULL,
IFNULL(c, 'c is null'),
CASE WHEN c IS NULL THEN 'c is null' ELSE c END
FROM t GROUP BY c WITH ROLLUP;
+------+-----------+---------------+------------------------+-------------------------------------------------+
| c | c IS NULL | c IS NOT NULL | IFNULL(c, 'c is null') | CASE WHEN c IS NULL THEN 'c is null' ELSE c END |
+------+-----------+---------------+------------------------+-------------------------------------------------+
| 1 | 0 | 1 | 1 | 1 |
| NULL | 1 | 0 | c is null | c is null |
+------+-----------+---------------+------------------------+-------------------------------------------------+
It looks like IS NULL Optimization described at 8.2.1.12 of Reference Manual.
[13 Mar 2018 12:53]
MySQL Verification Team
Hi! You are confused by the results simply because you are not using ROLLUP for what it is designed to do, by SQL standards. With aggregating queries you need other fields in the result set to use aggregating functions, like SUM(), AVG() etc ..... Then, and only then, ROLLUP and other additions to the GROUP BY, will provide a meaningful results, like subtotals and totals.
Description: When IS NULL is used with not null column in the select list of a query with ROLLUP, unexpected result is appear in the extra rows added by ROLLUP. In those rows not null column becomes null, but IS NULL doesn't give true. How to repeat: CREATE TABLE t (c INT NOT NULL); INSERT INTO t VALUES(1); SELECT c, c IS NULL, c IS NOT NULL, IFNULL(c, 'c is null'), CASE WHEN c IS NULL THEN 'c is null' ELSE c END FROM t GROUP BY c WITH ROLLUP; +------+-----------+---------------+------------------------+-------------------------------------------------+ | c | c is null | c is not null | ifnull(c, 'c is null') | CASE WHEN c IS NULL THEN 'c is null' ELSE c END | +------+-----------+---------------+------------------------+-------------------------------------------------+ | 1 | 0 | 1 | 1 | 1 | | NULL | 0 | 0 | c is null | NULL | +------+-----------+---------------+------------------------+-------------------------------------------------+