MySQL Bugs: #35724: Non-found left join data passed as a parameter to a function in WHERE

Bug #35724	Non-found left join data passed as a parameter to a function in WHERE
Submitted:	31 Mar 2008 22:24	Modified:	4 Mar 2009 18:42
Reporter:	Tomas Valenta	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	MySQL 5.0.51a-community-nt, 5.1, 6.0 BK	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Contribution, functions, join, null, Parameter, stored procedures, where

Description:
Hello,
I found an interesting bug in processing WHERE clause when you use a stored function. If you pass a NULL value which comes from not-found LEFT JOIN table column to the function as a parameter, it is always evaluated as FALSE or maybe NULL. See ``how to repeat'' section. The two results should be the same in my opinion; the second one is right. You can see a workaround in the example I posted.
Tested on WinXP SP2.
Cheers
     Vali

How to repeat:
mysql> CREATE TABLE t1 (c1 INT, c2 CHAR(1));
Query OK, 0 rows affected (0.20 sec)

mysql> CREATE TABLE t2 (d1 INT, d2 CHAR(1));
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO t1 VALUES (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd');
Query OK, 4 rows affected (0.03 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t2 VALUES (1, 'a'), (2, 'b'), (3, NULL);
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> DELIMITER %%
mysql> CREATE FUNCTION f(p CHAR(1)) RETURNS INT
    -> BEGIN
    ->   RETURN TRUE;
    -> END %%
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> SELECT t1.*, t2.* FROM t1 LEFT JOIN t2 ON (t1.c1 = t2.d1)
    -> WHERE f(t2.d2);
+------+------+------+------+
| c1   | c2   | d1   | d2   |
+------+------+------+------+
|    1 | a    |    1 | a    |
|    2 | b    |    2 | b    |
|    3 | c    |    3 | NULL |
+------+------+------+------+
3 rows in set (0.00 sec)

mysql> SELECT t1.*, t2.* FROM t1 LEFT JOIN t2 ON (t1.c1 = t2.d1)
    -> WHERE f(COALESCE(t2.d2, NULL));
+------+------+------+------+
| c1   | c2   | d1   | d2   |
+------+------+------+------+
|    1 | a    |    1 | a    |
|    2 | b    |    2 | b    |
|    3 | c    |    3 | NULL |
|    4 | d    | NULL | NULL |
+------+------+------+------+
4 rows in set (0.00 sec)

mysql>

Thank you for the report.

Verified as described.

Setting the right lead.

Feb. 4, 2009

I believe that the following is happening :
During the left joining of tables, the function, f() is called with "no argument" since there is no row in table t2 that matches the condition t1.c1 = t2.d1 when t1.c1 = 4 which causes an error return from the function and results in "where" to fail.
Whereas in case 2, function coalesce() gets "no argument" from table2 and the null argument for a total of 1 argument which results in a null return value which feeds into the function f() which now does get an argument and produces no error return and provides expected "True" output.

Hiromichi Watari

This example is showing what is most confusing in this bug:
Try this: 
mysql> SELECT t1.*, t2.*,f(t2.d2) FROM t1 LEFT JOIN t2 ON (t1.c1 = t2.d1);
+------+------+------+------+----------+
| c1   | c2   | d1   | d2   | f(t2.d2) |
+------+------+------+------+----------+
|    1 | a    |    1 | a    |        1 |
|    2 | b    |    2 | b    |        1 |
|    3 | c    |    3 | NULL |        1 |
|    4 | d    | NULL | NULL |        1 |
+------+------+------+------+----------+
4 rows in set (0.03 sec)
The function is working OK.

But when you add condition it does not work anymore:

mysql> SELECT t1.*, t2.*,f(t2.d2) FROM t1 LEFT JOIN t2 ON (t1.c1 = t2.d1) where
f(t2.d2)=1;
+------+------+------+------+----------+
| c1   | c2   | d1   | d2   | f(t2.d2) |
+------+------+------+------+----------+
|    1 | a    |    1 | a    |        1 |
|    2 | b    |    2 | b    |        1 |
|    3 | c    |    3 | NULL |        1 |
+------+------+------+------+----------+
3 rows in set (0.01 sec)

Bug #35724 Patch(1 of 8)

Attachment: mysql_system_tables.sql (text/x-sql), 20.45 KiB.

Bug #35724 Patch(2 of 8)

Attachment: lex.h (text/x-chdr), 22.74 KiB.

Bug #35724 Patch(3 of 8)

Attachment: sql_lex.h (text/x-chdr), 58.58 KiB.

Bug #35724 Patch(4 of 8)

Attachment: sql_yacc.yy (application/octet-stream, text), 417.93 KiB.

Bug #35724 Patch(5 of 8)

Attachment: item_func.h (text/x-chdr), 48.22 KiB.

Bug #35724 Patch(6 of 8)

Attachment: item_func.cc (text/x-c++src), 155.41 KiB.

Bug #35724 Patch(7 of 8)

Attachment: sp.h (text/x-chdr), 3.88 KiB.

Bug #35724 Patch(8 of 8)

Attachment: sp.cc (text/x-c++src), 61.27 KiB.

March 4, 2009

First of all, the hypothesis I made regarding this bug on Feb. 4, 2009 turned out to be false, oops, hate when that happens.

The problem is caused by the optimizer converting the outer join to an inner join since it determined that the "where" condition will reject null rows of the inner table, alas incorrectly in this particular instance.  All SP(Stored Procedure) functions are considered to be "null rejecting"(A null argument implies a logical false or equivalent return value.) by the server which is not always the case and as a matter of fact, some native functions(and operators) specifically deal with this situation by overriding their not_null_tables() method (coalesce() is one of them).  As one can see that this problem afflicts not only SP functions but also native functions as well, UDFs(User Defined Functions) don't suffer from the same fate since their not_null_tables() has been overridden but at a cost of no outer to inner join optimization.

The proposed solution will not require any database to be recreated.

1) SP functions
  Add a new characteristic "[NOT] REJECT NULL" (default: REJECT NULL) to SPs and this characteristic will modify the behavior of not_null_tables() method in the class Item_func from which Item_func_sp inherits.

2) UDFs
  No problem hence no solution however a decision was made to modify not_null_tables() method in Item_func as opposed to Item_func_sp because some day UDFs could adopt a similar mechanism for null rejection.(Initid structure to contain "reject null" status set by a function initializer.)

3) Native functions
  Override the not_null_tables() method.

Usage
  After a new server is installed and before doing anything else, modify the "proc" table first.

  mysql>use mysql;
  mysql>alter table proc add not_reject_null enum('YES','NO') DEFAULT 'NO';
  mysql>use my_database;
  mysql>alter function f NOT REJECT NULL;

  Of course you can set it back to the default as well.

  mysql>alter function f REJECT NULL;

Modifications made on version 6.0 source(latest pull as of March 3, 2009)

Files modified:   

  The following file creates system tables with the modified "proc" table and for a new installation only.
                    mysql_system_tables.sql

  The following files add "[NOT] REJECT NULL" characteristic to SPs and modify CREATE/ALTER FUNCTION commands to accept the new characteristic.
                    lex.h
                    sql_lex.h
                    sql_yacc.yy

  The following files add "not_reject_null" attribute(default: not_reject_null=false) to the class Item_func and modify its not_null_tables() method and fix_fields() method of Item_func_sp.
                    item_func.h
                    item_func.cc

  The following files modify create/update SP function routines.
                    sp.h
                    sp.cc

Classes modified:   Item_func
                    Item_func_sp

Methods modified:   Item_func::Item_func() (Initializer list of all 8 constructors)
                    Item_func::not_null_tables()
                    Item_func_sp::fix_fields()

Attribute added:    Item_func::not_reject_null

Functions modified: db_find_routine()
                    sp_create_routine()
                    sp_update_routine()
                    create_string()

All patched files have been uploaded.

Hiromichi Watari

Thank you for creating the patch for the bug. I can see Target version attribute set to 6.0. Does this mean that this issue will not be fixed in 5.0.x or 5.1.x branches?

Revised Patch (6 of 8), please discard the previous patch (6 of 8).

Attachment: item_func.cc (text/x-c++src), 155.43 KiB.

>[4 Mar 19:42] Tomas Valenta
>
>Thank you for creating the patch for the bug. I can see Target version attribute set to
>6.0. Does this mean that this issue will not be fixed in 5.0.x or 5.1.x branches?

I am not in the loop of making those sorts of decisions, so I really don't know.