Bug #11235 Expression with IFNULL yields 18 where 1 is correct
Submitted: 10 Jun 2005 10:24 Modified: 17 Jun 2005 3:06
Reporter: Joerg Bruehe Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.7 OS:Sol 10 x86
Assigned to: Ramil Kalimullin CPU Architecture:Any

[10 Jun 2005 10:24] Joerg Bruehe
Description:
Build of 5.0.7, based on ChangeSet
  1.1950 05/06/09 16:53:08 kent@mysql.com +12 -0
  Many files:
    More yaSSL changes in Visual Studio 6 project files

Observed only on sol10-x86:

-------------------------------------------------------
*** r/null.result       Thu Jun  9 18:11:40 2005
--- r/null.reject       Thu Jun  9 19:23:09 2005
***************
*** 12,18 ****
  NULL  NULL    NULL    NULL
  select NULL=NULL,NULL<>NULL,IFNULL(NULL,1.1)+0,IFNULL(NULL,1) | 0;
  NULL=NULL     NULL<>NULL      IFNULL(NULL,1.1)+0      IFNULL(NULL,1) | 0
! NULL  NULL    1.1     1
  select strcmp("a",NULL),(1<NULL)+0.0,NULL regexp "a",null like "a%","a%" like null;
  strcmp("a",NULL)      (1<NULL)+0.0    NULL regexp "a" null like "a%"  "a%" like null
  NULL  NULL    NULL    NULL    NULL
--- 12,18 ----
  NULL  NULL    NULL    NULL
  select NULL=NULL,NULL<>NULL,IFNULL(NULL,1.1)+0,IFNULL(NULL,1) | 0;
  NULL=NULL     NULL<>NULL      IFNULL(NULL,1.1)+0      IFNULL(NULL,1) | 0
! NULL  NULL    1.1     18
  select strcmp("a",NULL),(1<NULL)+0.0,NULL regexp "a",null like "a%","a%" like null;
  strcmp("a",NULL)      (1<NULL)+0.0    NULL regexp "a" null like "a%"  "a%" like null
  NULL  NULL    NULL    NULL    NULL
-------------------------------------------------------

How to repeat:
Test suite.
[13 Jun 2005 14:45] Lenz Grimmer
This happens on SLES9/x86_64 as well, but with a slightly different value:

TEST                            RESULT
-------------------------------------------------------
null                           [ fail ]

Errors are (from /home/mysqldev/bug-11235/mysql-test/var/log/mysqltest-time) :
/home/mysqldev/bug-11235/client/mysqltest: At line 192: Result length mismatch
(the last lines may be the most important ones)
Below are the diffs between actual and expected results:
-------------------------------------------------------
*** r/null.result       2005-06-11 04:39:40.000000000 +0300
--- r/null.reject       2005-06-13 18:45:19.883209241 +0300
***************
*** 12,18 ****
  NULL  NULL    NULL    NULL
  select NULL=NULL,NULL<>NULL,IFNULL(NULL,1.1)+0,IFNULL(NULL,1) | 0;
  NULL=NULL     NULL<>NULL      IFNULL(NULL,1.1)+0      IFNULL(NULL,1) | 0
! NULL  NULL    1.1     1
  select strcmp("a",NULL),(1<NULL)+0.0,NULL regexp "a",null like "a%","a%" like null;
  strcmp("a",NULL)      (1<NULL)+0.0    NULL regexp "a" null like "a%"  "a%" like null
  NULL  NULL    NULL    NULL    NULL
--- 12,18 ----
  NULL  NULL    NULL    NULL
  select NULL=NULL,NULL<>NULL,IFNULL(NULL,1.1)+0,IFNULL(NULL,1) | 0;
  NULL=NULL     NULL<>NULL      IFNULL(NULL,1.1)+0      IFNULL(NULL,1) | 0
! NULL  NULL    1.1     16
  select strcmp("a",NULL),(1<NULL)+0.0,NULL regexp "a",null like "a%","a%" like null;
  strcmp("a",NULL)      (1<NULL)+0.0    NULL regexp "a" null like "a%"  "a%" like null
  NULL  NULL    NULL    NULL    NULL
-------------------------------------------------------
[13 Jun 2005 14:48] Lenz Grimmer
master.trace of the failing "null" test

Attachment: master.trace.bz2 (application/x-bzip2, text), 165.92 KiB.

[13 Jun 2005 14:50] Lenz Grimmer
A trace file of the test failure has been attached.
[14 Jun 2005 8:55] Ramil Kalimullin
fixed in 5.0.8
[17 Jun 2005 3:06] Paul Dubois
Noted in 5.0.8 changelog.