Description:
MySQL 9.7.0: POW(x, NULL) returns 1 and reports NOT NULL, instead of NULL. Only a NULL exponent is affected -- POW(NULL, y) correctly returns NULL, and sibling functions EXP/LOG/MOD/ROUND/TRUNCATE/ATAN with a NULL argument all correctly return NULL on 9.7.0.
Impact: any boolean context admits a phantom row -- "SELECT 1 WHERE POW(2,NULL)" returns a row -- and COALESCE(POW(x,NULL), y) returns 1 instead of y. This silently changes WHERE/HAVING row sets and computed values.
This is a regression: the result is correct (NULL) on MySQL 9.6.0.
Root cause: sql/item_func.cc, Item_func_pow::val_real(). After evaluating the exponent via args[1]->val_real(), the second NULL check re-tests args[0] instead of args[1]:
if ((null_value = args[0]->null_value)) return 0.0; // arg0 (the value)
const double val2 = args[1]->val_real(); // the exponent
if ((null_value = args[0]->null_value)) return 0.0; // BUG: should test args[1]
A NULL exponent yields val2 = 0.0 with null_value left false, so pow(value, 0.0) = 1 is returned. The current development/trunk source still has the correct combined check "null_value = (args[0]->null_value || args[1]->null_value)". Fix: test args[1]->null_value on the second guard.
How to repeat:
SELECT VERSION(); -- 9.7.0
SELECT POW(2, NULL); -- returns 1, expected NULL
SELECT POW(2, NULL) IS NULL; -- returns 0, expected 1
SELECT 1 WHERE POW(2, NULL); -- returns a row, expected no rows
SELECT COALESCE(POW(2, NULL), -99); -- returns 1, expected -99
-- On MySQL 9.6.0 the same statements correctly give: NULL, 1, (no row), -99
-- Sibling functions are correct on 9.7.0 (all return NULL):
SELECT EXP(NULL), LOG(2, NULL), MOD(5, NULL), ROUND(2, NULL), POW(NULL, 2);
Suggested fix:
In sql/item_func.cc, Item_func_pow::val_real(), change the second NULL guard (after evaluating the exponent args[1]->val_real()) from "if ((null_value = args[0]->null_value)) return 0.0;" to test args[1]->null_value. Equivalently, restore the single combined check used on trunk: null_value = (args[0]->null_value || args[1]->null_value).
Description: MySQL 9.7.0: POW(x, NULL) returns 1 and reports NOT NULL, instead of NULL. Only a NULL exponent is affected -- POW(NULL, y) correctly returns NULL, and sibling functions EXP/LOG/MOD/ROUND/TRUNCATE/ATAN with a NULL argument all correctly return NULL on 9.7.0. Impact: any boolean context admits a phantom row -- "SELECT 1 WHERE POW(2,NULL)" returns a row -- and COALESCE(POW(x,NULL), y) returns 1 instead of y. This silently changes WHERE/HAVING row sets and computed values. This is a regression: the result is correct (NULL) on MySQL 9.6.0. Root cause: sql/item_func.cc, Item_func_pow::val_real(). After evaluating the exponent via args[1]->val_real(), the second NULL check re-tests args[0] instead of args[1]: if ((null_value = args[0]->null_value)) return 0.0; // arg0 (the value) const double val2 = args[1]->val_real(); // the exponent if ((null_value = args[0]->null_value)) return 0.0; // BUG: should test args[1] A NULL exponent yields val2 = 0.0 with null_value left false, so pow(value, 0.0) = 1 is returned. The current development/trunk source still has the correct combined check "null_value = (args[0]->null_value || args[1]->null_value)". Fix: test args[1]->null_value on the second guard. How to repeat: SELECT VERSION(); -- 9.7.0 SELECT POW(2, NULL); -- returns 1, expected NULL SELECT POW(2, NULL) IS NULL; -- returns 0, expected 1 SELECT 1 WHERE POW(2, NULL); -- returns a row, expected no rows SELECT COALESCE(POW(2, NULL), -99); -- returns 1, expected -99 -- On MySQL 9.6.0 the same statements correctly give: NULL, 1, (no row), -99 -- Sibling functions are correct on 9.7.0 (all return NULL): SELECT EXP(NULL), LOG(2, NULL), MOD(5, NULL), ROUND(2, NULL), POW(NULL, 2); Suggested fix: In sql/item_func.cc, Item_func_pow::val_real(), change the second NULL guard (after evaluating the exponent args[1]->val_real()) from "if ((null_value = args[0]->null_value)) return 0.0;" to test args[1]->null_value. Equivalently, restore the single combined check used on trunk: null_value = (args[0]->null_value || args[1]->null_value).