MySQL Bugs: #17047: CHAR() and IN() can return NULL without signaling NULL result

Bug #17047	CHAR() and IN() can return NULL without signaling NULL result
Submitted:	2 Feb 2006 15:27	Modified:	30 Nov 2006 20:17
Reporter:	Magnus Blåudd	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Prepared statements	Severity:	S3 (Non-critical)
Version:	4.1, 5.0.30	OS:	Linux (Linux)
Assigned to:	Tomash Brechko	CPU Architecture:	Any

Description:
When executing a select that (unexpectedy) returns NULL via server side cursor the error " Column '%s' cannot be null" will be returned.

How to repeat:
set sql_mode=traditional;
select char(0xff,0x8f using utf8);

Would normally yield the result:
> set sql_mode=traditional;
> select char(0xff,0x8f using utf8);
> char(0xff,0x8f using utf8)
> NULL
> Warnings:
> Error 1300    Invalid utf8 character string: 'FF8F'

But when executed as a server side cursor it will return:
>mysqltest: At line 2: query 'select char(0xff,0x8f using utf8)' failed: 1048: Column 'char(0xff,0x8f using utf8)' cannot be null

This is because the temporary table created on the server does not allow the NULL value to be inserted.

Suggested fix:
Make all columns in the server side cursors temptable always allow NULL values.

sql_cursor.cc >>
--- 662,678 ----
  bool Select_materialize::send_fields(List<Item> &list, uint flags)
  {
    DBUG_ASSERT(table == 0);
+
+   /*
+     Make it possible for the result table to store NULL values in all fields
+   */
+   List_iterator_fast<Item> it(*column_types);
+   Item *item;
+   while ((item= it++))
+   {
+     item->maybe_null= 1;
+   }
+
    if (create_result_table(unit->thd, unit->get_unit_column_types(),
                            FALSE, thd->options | TMP_TABLE_ALL_COLUMNS, ""))
      return TRUE;
[

Another example:
select 10 in (1,NULL,3);
=>  
1048: Column '10 in (1,NULL,3)' cannot be null

Kostja believe the bug to be in the decimal type.

Still exists. I still think the cursor result table need to be able to store NULL in any case - else it is a problem with "char" not setting maybe_null flag when sql_mode is traditional.

As in the example below, char might actually return NULL.
set sql_mode=traditional;
select char(0xff,0x8f using utf8);
char(0xff,0x8f using utf8)
NULL
Warnings:
Error 1300    Invalid utf8 character string: 'FF8F'

Hi Magnus,
yes, likely, thanks.

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/15269

ChangeSet@1.2284, 2006-11-13 22:42:00+03:00, kroki@moonlight.intranet +7 -0
  BUG#17047: select returning NULL fails when executed via cursor
  
  The bug has nothing to do with cursors (but requires them for one of the
  test cases).  The problem was that some functions (namely IN() and
  CHAR()) were returning NULL in certain conditions, while they didn't set
  their maybe_null flag.
  
  The fix is to set maybe_null correctly.

Problem with IN() is also in 4.1.

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/15399

ChangeSet@1.2540, 2006-11-16 13:21:38+03:00, kroki@moonlight.intranet +3 -0
  BUG#17047: CHAR() and IN() can return NULL without signaling NULL result
  
  The problem was that some functions (namely IN() starting with 4.1, and
  CHAR() starting with 5.0) were returning NULL in certain conditions,
  while they didn't set their maybe_null flag.  Because of that there could
  be some problems with 'IS NULL' check, and statements that depend on the
  function value domain, like CREATE TABLE t1 SELECT 1 IN (2, NULL);.
  
  The fix is to set maybe_null correctly.

Queued to 4.1-runtime, 5.0-runtime, 5.1-runtime.

Fixed in 4.1.23, 5.0.32 and 5.1.13

Noted in 4.1.23, 5.0.32, 5.1.14 changelogs.

IN() and CHAR() can return NULL, but did not signal that to the query
processor, causing incorrect results for IS NULL operations.

See bug #5929