Description:
MySQL version / commit
9.7.2 @ 008e09c (also present on 8.4)
Steps to reproduce
MTR case using the existing udf_example library, no source changes needed.
my_median() sets *is_null = 1 when it accumulated no value.
mysql-test/t/udf_aggregate_is_null-master.opt:
$UDF_EXAMPLE_LIB_OPT
mysql-test/t/udf_aggregate_is_null.test:
--source include/have_udf.inc
--replace_result $UDF_EXAMPLE_LIB UDF_EXAMPLE_LIB
eval CREATE AGGREGATE FUNCTION my_median
RETURNS INTEGER SONAME "$UDF_EXAMPLE_LIB";
CREATE TABLE t1 (grp INT, val INT);
SELECT my_median(val) AS v,
my_median(val) IS NULL AS is_null,
my_median(val) IS NOT NULL AS is_not_null,
my_median(val) <=> NULL AS null_safe_eq,
IFNULL(my_median(val), -1) AS ifnull_v,
COALESCE(my_median(val), -1) AS coalesce_v
FROM t1;
INSERT INTO t1 VALUES (1, NULL), (1, NULL), (2, 10), (2, 20), (2, 30);
SELECT grp, my_median(val) AS v
FROM t1
GROUP BY grp
HAVING my_median(val) IS NOT NULL
ORDER BY grp;
DROP TABLE t1;
DROP FUNCTION my_median;
The aggregated column is nullable on purpose, so that the aggregate item is
nullable and Item_func_isnull::resolve_type() does not fold IS NULL to a
constant before the item is consulted.
Expected vs actual result
Empty table. The value is NULL, so IS NULL must be 1:
v is_null is_not_null null_safe_eq ifnull_v coalesce_v
NULL 0 1 1 -1 -1
IS NULL answers 0 and IS NOT NULL answers 1 for a row whose value prints
as NULL. <=>, IFNULL and COALESCE all report NULL correctly, so the
result really is NULL; only IS NULL/IS NOT NULL disagree.
A group of only NULLs is likewise reported as not-NULL, and is not
distinguished from a group that has a value:
grp v is_null
1 NULL 0
2 20 0
The consequence is wrong rows, not only a wrong boolean. HAVING ... IS NOT NULL returns the NULL group:
grp v
1 NULL <-- should not be here
2 20
and HAVING ... IS NULL returns no rows at all.
Platform / compiler
macOS / AppleClang (not platform specific)
How to repeat:
MySQL version / commit
9.7.2 @ 008e09c (also present on 8.4)
Steps to reproduce
MTR case using the existing udf_example library, no source changes needed.
my_median() sets *is_null = 1 when it accumulated no value.
mysql-test/t/udf_aggregate_is_null-master.opt:
$UDF_EXAMPLE_LIB_OPT
mysql-test/t/udf_aggregate_is_null.test:
--source include/have_udf.inc
--replace_result $UDF_EXAMPLE_LIB UDF_EXAMPLE_LIB
eval CREATE AGGREGATE FUNCTION my_median
RETURNS INTEGER SONAME "$UDF_EXAMPLE_LIB";
CREATE TABLE t1 (grp INT, val INT);
SELECT my_median(val) AS v,
my_median(val) IS NULL AS is_null,
my_median(val) IS NOT NULL AS is_not_null,
my_median(val) <=> NULL AS null_safe_eq,
IFNULL(my_median(val), -1) AS ifnull_v,
COALESCE(my_median(val), -1) AS coalesce_v
FROM t1;
INSERT INTO t1 VALUES (1, NULL), (1, NULL), (2, 10), (2, 20), (2, 30);
SELECT grp, my_median(val) AS v
FROM t1
GROUP BY grp
HAVING my_median(val) IS NOT NULL
ORDER BY grp;
DROP TABLE t1;
DROP FUNCTION my_median;
The aggregated column is nullable on purpose, so that the aggregate item is
nullable and Item_func_isnull::resolve_type() does not fold IS NULL to a
constant before the item is consulted.
Expected vs actual result
Empty table. The value is NULL, so IS NULL must be 1:
v is_null is_not_null null_safe_eq ifnull_v coalesce_v
NULL 0 1 1 -1 -1
IS NULL answers 0 and IS NOT NULL answers 1 for a row whose value prints
as NULL. <=>, IFNULL and COALESCE all report NULL correctly, so the
result really is NULL; only IS NULL/IS NOT NULL disagree.
A group of only NULLs is likewise reported as not-NULL, and is not
distinguished from a group that has a value:
grp v is_null
1 NULL 0
2 20 0
The consequence is wrong rows, not only a wrong boolean. HAVING ... IS NOT NULL returns the NULL group:
grp v
1 NULL <-- should not be here
2 20
and HAVING ... IS NULL returns no rows at all.
Platform / compiler
macOS / AppleClang (not platform specific)
Description: MySQL version / commit 9.7.2 @ 008e09c (also present on 8.4) Steps to reproduce MTR case using the existing udf_example library, no source changes needed. my_median() sets *is_null = 1 when it accumulated no value. mysql-test/t/udf_aggregate_is_null-master.opt: $UDF_EXAMPLE_LIB_OPT mysql-test/t/udf_aggregate_is_null.test: --source include/have_udf.inc --replace_result $UDF_EXAMPLE_LIB UDF_EXAMPLE_LIB eval CREATE AGGREGATE FUNCTION my_median RETURNS INTEGER SONAME "$UDF_EXAMPLE_LIB"; CREATE TABLE t1 (grp INT, val INT); SELECT my_median(val) AS v, my_median(val) IS NULL AS is_null, my_median(val) IS NOT NULL AS is_not_null, my_median(val) <=> NULL AS null_safe_eq, IFNULL(my_median(val), -1) AS ifnull_v, COALESCE(my_median(val), -1) AS coalesce_v FROM t1; INSERT INTO t1 VALUES (1, NULL), (1, NULL), (2, 10), (2, 20), (2, 30); SELECT grp, my_median(val) AS v FROM t1 GROUP BY grp HAVING my_median(val) IS NOT NULL ORDER BY grp; DROP TABLE t1; DROP FUNCTION my_median; The aggregated column is nullable on purpose, so that the aggregate item is nullable and Item_func_isnull::resolve_type() does not fold IS NULL to a constant before the item is consulted. Expected vs actual result Empty table. The value is NULL, so IS NULL must be 1: v is_null is_not_null null_safe_eq ifnull_v coalesce_v NULL 0 1 1 -1 -1 IS NULL answers 0 and IS NOT NULL answers 1 for a row whose value prints as NULL. <=>, IFNULL and COALESCE all report NULL correctly, so the result really is NULL; only IS NULL/IS NOT NULL disagree. A group of only NULLs is likewise reported as not-NULL, and is not distinguished from a group that has a value: grp v is_null 1 NULL 0 2 20 0 The consequence is wrong rows, not only a wrong boolean. HAVING ... IS NOT NULL returns the NULL group: grp v 1 NULL <-- should not be here 2 20 and HAVING ... IS NULL returns no rows at all. Platform / compiler macOS / AppleClang (not platform specific) How to repeat: MySQL version / commit 9.7.2 @ 008e09c (also present on 8.4) Steps to reproduce MTR case using the existing udf_example library, no source changes needed. my_median() sets *is_null = 1 when it accumulated no value. mysql-test/t/udf_aggregate_is_null-master.opt: $UDF_EXAMPLE_LIB_OPT mysql-test/t/udf_aggregate_is_null.test: --source include/have_udf.inc --replace_result $UDF_EXAMPLE_LIB UDF_EXAMPLE_LIB eval CREATE AGGREGATE FUNCTION my_median RETURNS INTEGER SONAME "$UDF_EXAMPLE_LIB"; CREATE TABLE t1 (grp INT, val INT); SELECT my_median(val) AS v, my_median(val) IS NULL AS is_null, my_median(val) IS NOT NULL AS is_not_null, my_median(val) <=> NULL AS null_safe_eq, IFNULL(my_median(val), -1) AS ifnull_v, COALESCE(my_median(val), -1) AS coalesce_v FROM t1; INSERT INTO t1 VALUES (1, NULL), (1, NULL), (2, 10), (2, 20), (2, 30); SELECT grp, my_median(val) AS v FROM t1 GROUP BY grp HAVING my_median(val) IS NOT NULL ORDER BY grp; DROP TABLE t1; DROP FUNCTION my_median; The aggregated column is nullable on purpose, so that the aggregate item is nullable and Item_func_isnull::resolve_type() does not fold IS NULL to a constant before the item is consulted. Expected vs actual result Empty table. The value is NULL, so IS NULL must be 1: v is_null is_not_null null_safe_eq ifnull_v coalesce_v NULL 0 1 1 -1 -1 IS NULL answers 0 and IS NOT NULL answers 1 for a row whose value prints as NULL. <=>, IFNULL and COALESCE all report NULL correctly, so the result really is NULL; only IS NULL/IS NOT NULL disagree. A group of only NULLs is likewise reported as not-NULL, and is not distinguished from a group that has a value: grp v is_null 1 NULL 0 2 20 0 The consequence is wrong rows, not only a wrong boolean. HAVING ... IS NOT NULL returns the NULL group: grp v 1 NULL <-- should not be here 2 20 and HAVING ... IS NULL returns no rows at all. Platform / compiler macOS / AppleClang (not platform specific)