diff options
| author | Alexander Barkov <bar@mariadb.org> | 2015-09-10 17:13:35 +0400 |
|---|---|---|
| committer | Alexander Barkov <bar@mariadb.org> | 2015-09-10 17:13:35 +0400 |
| commit | 4aebba3aeba2d413268455c3c8c7cbfd04e2f94f (patch) | |
| tree | 048d73a20b078c43c6f567a5957e5e7e53005160 /mysql-test/t/null.test | |
| parent | 8e553c455c4740a51d2a7d0e23c3c79863b5df22 (diff) | |
| download | mariadb-git-4aebba3aeba2d413268455c3c8c7cbfd04e2f94f.tar.gz | |
MDEV-8740 Wrong result for SELECT..WHERE year_field=10 AND NULLIF(year_field,2011.1)='2011'
MDEV-8754 Wrong result for SELECT..WHERE year_field=2020 AND NULLIF(year_field,2010)='2020'
Problems:
1. Item_func_nullif stored a copy of args[0] in a private member m_args0_copy,
which was invisible for the inherited Item_func menthods, like
update_used_tables(). As a result, after equal field propagation
things like Item_func_nullif::const_item() could return wrong result
and a non-constant NULLIF() was erroneously treated as a constant
at optimize_cond() time.
Solution: removing m_args0_copy and storing the return value item
in args[2] instead.
2. Equal field propagation did not work well for Item_fun_nullif.
Solution: using ANY_SUBST for args[0] and args[1], as they are in
comparison, and IDENTITY_SUBST for args[2], as it's not in comparison.
Diffstat (limited to 'mysql-test/t/null.test')
| -rw-r--r-- | mysql-test/t/null.test | 31 |
1 files changed, 31 insertions, 0 deletions
diff --git a/mysql-test/t/null.test b/mysql-test/t/null.test index 695c22f3bbd..5347a961c59 100644 --- a/mysql-test/t/null.test +++ b/mysql-test/t/null.test @@ -880,5 +880,36 @@ DROP TABLE t1; --echo # +--echo # MDEV-8740 Wrong result for SELECT..WHERE year_field=10 AND NULLIF(year_field,2011.1)='2011' +--echo # +CREATE TABLE t1 (a YEAR); +INSERT INTO t1 VALUES (2010),(2011); +SELECT a=10 AND NULLIF(a,2011.1)='2011' AS cond FROM t1; +SELECT * FROM t1 WHERE a=10; +SELECT * FROM t1 WHERE NULLIF(a,2011.1)='2011'; +SELECT * FROM t1 WHERE a=10 AND NULLIF(a,2011.1)='2011'; +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE a=10 AND NULLIF(a,2011.1)='2011'; +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE a=10 AND NULLIF(a,2011.1)=CONCAT('2011',RAND()); +DROP TABLE t1; + + +--echo # +--echo # MDEV-8754 Wrong result for SELECT..WHERE year_field=2020 AND NULLIF(year_field,2010)='2020' +--echo # +CREATE TABLE t1 (a YEAR); +INSERT INTO t1 VALUES (2010),(2020); +SELECT * FROM t1 WHERE a=2020; +SELECT * FROM t1 WHERE NULLIF(a,2010)='2020'; +SELECT * FROM t1 WHERE a=2020 AND NULLIF(a,2010)='2020'; +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE a=2020 AND NULLIF(a,2010)='2020'; +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE a=2020 AND NULLIF(a,2010)=CONCAT('2020',RAND()); +DROP TABLE t1; + + +--echo # --echo # End of 10.1 tests --echo # |