Bug #32335 inconsistent int > null +1 behavior
Submitted: 13 Nov 2007 19:01 Modified: 14 Jan 21:04
Reporter: Matthew Lord
Status: Closed
Category:Server Severity:S3 (Non-critical)
Version:5.0.50 OS:Any
Assigned to: Gleb Shchepa Target Version:
Tags: arithmetic, null, bfsm_2007_12_06
Triage: D2 (Serious)

[13 Nov 2007 19:01] Matthew Lord 
Description:
see how to repeat

How to repeat:
/* Set up data */
create table bug_test (int_col integer not null, bigint_col bigint not null)
engine=MyISAM;

insert into bug_test (int_col,bigint_col) values (1,1);

/* This one works fine */
select * from bug_test where int_col > null + 1;

/* This one produces an error */
select * from bug_test where bigint_col > null + 1;

I'm not sure which one behaves "properly" but they should at least be consistent.
[17 Nov 2007 19:56] 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/37995

ChangeSet@1.2566, 2007-11-17 23:01:35+04:00, gshchepa@gleb.loc +3 -0
  Fixed bug #32335.
  Comparison of a BIGINT NOT NULL column with a constant arithmetical
  expression containing NULL leaded to the error 1048: Column '...'
  cannot be null.
  
  Order of the constant expression evaluation during query preparation
  has been modified to check an expression result for NULL value _before_
  conversion to the target column type, because that type may be declared
  as NOT NULL.
[17 Nov 2007 20:12] 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/37996

ChangeSet@1.2566, 2007-11-17 23:17:53+04:00, gshchepa@gleb.loc +3 -0
  Fixed bug #32335.
  Comparison of a BIGINT NOT NULL column with a constant arithmetical
  expression containing NULL leaded to the error 1048: Column '...'
  cannot be null.
  
  Order of the constant expression evaluation during query preparation
  has been modified to check an expression result for NULL value _before_
  conversion to the target column type, because that type may be declared
  as NOT NULL.
[17 Nov 2007 20:57] 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/37997

ChangeSet@1.2566, 2007-11-18 00:02:55+04:00, gshchepa@gleb.loc +3 -0
  Fixed bug #32335.
  Comparison of a BIGINT NOT NULL column with a constant arithmetic
  expression that evaluates to NULL caused error 1048: "Column '...'
  cannot be null".
  
  Made convert_constant_item() check if the constant expression is NULL
  before attempting to store it in a field. Attempts to store NULL in a
  NOT NULL field caused query errors.
[14 Dec 2007 9:15] Bugs System 
Pushed into 5.0.54
[14 Dec 2007 9:19] Bugs System 
Pushed into 5.1.23-rc
[14 Dec 2007 9:22] Bugs System 
Pushed into 6.0.5-alpha
[14 Jan 21:04] Jon Stephens 
Documented bugfix in 5.0.54, 5.1.23, and 6.0.5 changelogs as follows:

        Comparison of a BIGINT NOT NULL column with a
        constant arithmetic expression that evaluated to NULL mistakenly
        caused the error Column '...' cannot be
          null (error 1048).