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

[2 Feb 2006 15:27] Magnus Blåudd
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;
[
[2 Feb 2006 15:29] Magnus Blåudd
Another example:
select 10 in (1,NULL,3);
=>  
1048: Column '10 in (1,NULL,3)' cannot be null
[11 Feb 2006 21:40] Brian Aker
Kostja believe the bug to be in the decimal type.
[10 Nov 2006 10:53] Magnus Blåudd
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'
[10 Nov 2006 11:35] Konstantin Osipov
Hi Magnus,
yes, likely, thanks.
[13 Nov 2006 19:44] Bugs System
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.
[16 Nov 2006 10:15] Tomash Brechko
Problem with IN() is also in 4.1.
[16 Nov 2006 10:24] Bugs System
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.
[16 Nov 2006 15:40] Tomash Brechko
Queued to 4.1-runtime, 5.0-runtime, 5.1-runtime.
[30 Nov 2006 1:31] Konstantin Osipov
Fixed in 4.1.23, 5.0.32 and 5.1.13
[30 Nov 2006 20:17] Paul DuBois
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.
[18 Dec 2006 14:35] Vladimir Shebordaev
See bug #5929