MySQL Bugs: #37891: " Column cannot be null " error with aggregate in a subquery

Bug #37891	" Column cannot be null " error with aggregate in a subquery
Submitted:	5 Jul 2008 8:21	Modified:	22 Nov 2010 0:38
Reporter:	Philip Stoev	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	6.0	OS:	Any
Assigned to:	Sergey Petrunya	CPU Architecture:	Any

Description:
When executing certain queries, both SELECT and EXPLAIN SELECT fail with error  1048: Column cannot be null. Some of the queries may make no sense, however SELECT should not fail, but rather return an empty set. EXPLAIN SELECT should not fail but rather report "impossible where" or whatever is appropriate.

How to repeat:
If you apply the patch from bug #36135, the following queries will start failing rather than crashing:

CREATE TABLE t1 (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `int_nokey` int(11) DEFAULT NULL,
  `int_key` int(11) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `int_key` (`int_key`)
) ENGINE=MyISAM;

INSERT INTO t1 VALUES (1,1,1);

CREATE TABLE t2 (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `int_key` int(11) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `int_key` (`int_key`)
) ENGINE=MyISAM;

# This is a perfectly valid query
SELECT * FROM t1 WHERE (int_key, int_nokey )
IN (SELECT pk, AVG(DISTINCT int_key) FROM t2);

# This one is kind of questionable, AVG DISTINCT on a primary key
SELECT * FROM t1
WHERE int_key IN (SELECT AVG(DISTINCT pk )
 FROM t1 WHERE `int_nokey` IS NULL
);

Suggested fix:
SELECT should not fail, but rather return an empty set. EXPLAIN SELECT should not fail but rather report "impossible where" or whatever is appropriate.

Here is another example, should work without the patch as well:

CREATE TABLE t1 (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `int_key` int(11) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `int_key` (`int_key`)
) ENGINE=MyISAM;

INSERT INTO t1 VALUES (1,10);

CREATE TABLE t2 (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `time_nokey` time DEFAULT NULL,
  `datetime_key` time DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `datetime_key` (`datetime_key`)
) ENGINE=MyISAM;

INSERT INTO t2 VALUES (1,'18:19:29',NOW());

SELECT * FROM t1 WHERE int_key IN ( SELECT SUM(DISTINCT pk) FROM t2 WHERE time_nokey = datetime_key );

Setting to Open so that the bug verification team can check previous releases.

The bug is a manifestation of two problems
1. The problem of BUG#36135 : crash (assertion failure) when we try to send eof after having sent an SQL  Error.
2. The SQL error.

As Philip wrote, the first part is addressed by fix for BUG#36135.

Re the second part: The problem doesn't show up on 5.x, and looking at error stack trace in 6.0 it looks like the problem was caused by Materialization strategy:

  #0  my_error (nr=1048, MyFlags=0) at my_error.c:75
  #1  0x0848f409 in set_field_to_null_with_conversions (field=0x9bf6210, no_conversions=false) at field_conv.cc:187
  #2  0x0826ad99 in Item::save_in_field (this=0x9bf9ee0, field=0x9bf6210, no_conversions=false) at item.cc:4877
  #3  0x0837e9c1 in fill_record (thd=0x9ba1f70, ptr=0x9bf6054, values=@0x9bf9aa4, ignore_errors=true) at sql_base.cc:7236
  #4  0x084d32ce in select_union::send_data (this=0x9c01eb8, values=@0x9bf9aa4) at sql_union.cc:60
  #5  0x083c90bf in return_zero_rows (join=0x9c02888, result=0x9c01eb8, tables=0x9bfa0c8, fields=@0x9bf9aa4, send_row=true, select_options=268435456, info=0x8aa1038 "Impossible WHERE noticed after reading const tables", having=0x0) at sql_select.cc:9032
  #6  0x083c96fc in JOIN::exec (this=0x9c02888) at sql_select.cc:2340
  #7  0x082dd10d in subselect_hash_sj_engine::exec (this=0x9c01e80) at item_subselect.cc:3213
  #8  0x082e2116 in Item_subselect::exec (this=0x9bfa748) at item_subselect.cc:285
  #9  0x082e228e in Item_in_subselect::exec (this=0x9bfa748) at item_subselect.cc:335
  #10 0x082daf74 in Item_in_subselect::val_bool (this=0x9bfa748) at item_subselect.cc:925
  #11 0x082729aa in Item::val_bool_result (this=0x9bfa748) at ../item.h:742
  #12 0x082a1a93 in Item_in_optimizer::val_int (this=0x9bfab68) at item_cmpfunc.cc:1595
  #13 0x083ae9b9 in do_select (join=0x9c008a0, fields=0x9ba3520, table=0x0, procedure=0x0) at sql_select.cc:13424
  #14 0x083cb25e in JOIN::exec (this=0x9c008a0) at sql_select.cc:2826
  #15 0x083c5f2f in mysql_select (thd=0x9ba1f70, rref_pointer_array=0x9ba3590, tables=0x9bf9530, wild_num=1, fields=@0x9ba3520, conds=0x9bfa748, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147764736, result=0x9bfa898, unit=0x9ba31f4, select_lex=0x9ba348c) at sql_select.cc:3016
  #16 0x083cb56e in handle_select (thd=0x9ba1f70, lex=0x9ba3198, result=0x9bfa898, setup_tables_done_option=0) at sql_select.cc:300
  #17 0x0833b0df in execute_sqlcom_select (thd=0x9ba1f70, all_tables=0x9bf9530) at sql_parse.cc:4875
  #18 0x0833ca89 in mysql_execute_command (thd=0x9ba1f70) at sql_parse.cc:2107
  #19 0x083457c7 in mysql_parse (thd=0x9ba1f70, inBuf=0x9bf9358 "SELECT * FROM t1 WHERE int_key IN ( SELECT SUM(DISTINCT pk) FROM t2 WHERE time_nokey = datetime_key )", length=101, found_semicolon=0xa914c270) at sql_parse.cc:5845

If we up the stack trace we see this:

(gdb) up
  #3  0x0837e9c1 in fill_record (thd=0x9ba1f70, ptr=0x9bf60bc, values=@0x9bf9b0c, ignore_errors=true) at sql_base.cc:7236
(gdb) p value
  $7 = (Item_sum_sum_distinct *) 0x9bf9f48
(gdb) p value->maybe_null
  $8 = 0 '\0'
(gdb) p value->is_null()
  $9 = true

Apparently Item_sum_sum_distinct has wrong attributes - it is NULL while it has maybe_null==FALSE.

I think an assertion needs to be placed for the condition

!value->maybe_null && value->is_null()

Philip,
I agree that this has to never be true. The problem with the assertion is that there's no single universal place to put it.  Item evaluation is invoked in too many places.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/53456

2687 Sergey Petrunia	2008-09-05
      BUG#37891: Column cannot be null error with aggregate in a subquery
      - Add Item_sum_distinct::fix_fields() which sets maybe_null to TRUE.
        This makes SUM(DISTINCT x) and AVG(DISTINCT x) be nullable, which
        is needed as they have NULL value when evaluated over empty resultset.

Pushed into 6.0.7-alpha  (revid:sergefp@mysql.com-20080905190146-1cht8gd34apcj4m0) (version source revid:sergefp@mysql.com-20080905190146-1cht8gd34apcj4m0) (pib:5)

Noted in 6.0.7 changelog.

SUM(DISTINCT) and AVG(DISTINCT) for an empty result set in a subquery
were not properly handled as being able to return NULL.

Pushed into mysql-next-mr (revid:alik@sun.com-20100816062819-bluwgdq8q4xysmlg) (version source revid:alik@sun.com-20100816062612-enatdwnv809iw3s9) (pib:20)

Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (merge vers: 5.6.99-m4) (pib:21)

Noted in 5.6.1 changelog.

Correction: No 5.6.1 changelog entry. Bug does not appear in any released 5.6.x version.