Null variables in functions handled incorrectly in WHERE clause (?)
"jds" <[email protected]>
| Newsgroups | gmane.comp.db.mysql.bugs |
|---|---|
| Message-ID | <002501c3799f$0524e1d0$010aa8c0@hpcomputer> |
This behavior does not seem correct and I found no references to it in help or in the bug database...
When I use an undefined variable as an argument to a function (e.g. PASSWORD() or UPPER()) in a WHERE clause, rows are returned when it appears they should not be. If NULL is passed directly, no rows are returned as expected. When a null variable is used, however, rows are returned erroneously:
CREATE TABLE test.temp_table (
name VARCHAR(50) NOT NULL PRIMARY KEY,
pw VARCHAR(16) NOT NULL);
INSERT INTO test.temp_table (name, pw)
VALUES ('tom', PASSWORD('my_pw'));
SET @pass='my_pw';
SET @wrong='incorrect';
# returns 1 row (OK)
SELECT name FROM test.temp_table
WHERE name='tom' AND pw=PASSWORD(@pass);
# returns 0 rows (OK)
SELECT name FROM test.temp_table
WHERE name='tom' AND pw=PASSWORD(@wrong);
# returns 1 row (WRONG)
SELECT name FROM test.temp_table
WHERE name='tom' AND pw=PASSWORD(@undefined);
# returns 1 row (WRONG)
SELECT name FROM test.temp_table
WHERE pw=PASSWORD(@undefined) AND name='tom';
# returns 1 row (WRONG)
SELECT name FROM test.temp_table
WHERE pw=PASSWORD(@undefined);
# returns 1 row (WRONG)
SELECT name FROM test.temp_table
WHERE name='tom' AND pw=UPPER(@undefined);
# returns 0 rows (OK)
SELECT name FROM test.temp_table
WHERE name='tom' AND pw=PASSWORD(Null);
Am I overlooking something, or is this a bug? The uninitialized variable should evaluate to NULL, the function should then evaluate to NULL, and the WHERE clause should match no records (the same as if NULL is used directly). This could easily happen if a variable is accidently uninitialized before calling a script that uses it. In this case, a user could be validated when clearly none should be.
This happens in version 4.0.13 under both Win XP (mysqld-nt) and Win 98SE (mysqld), as well as in 4.0.11-gamma under Linux (i586).
Thanks.
Jerry