Bug #32335 inconsistent int > null +1 behavior
Submitted: 13 Nov 2007 18:01 Modified: 14 Jan 2008 20:04
Reporter: Matt Lord Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.50 OS:Any
Assigned to: Gleb Shchepa
Tags: arithmetic, bfsm_2007_12_06, null
Triage: D2 (Serious)

[13 Nov 2007 18:01] Matt 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 18: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 19: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 19: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 8:15] Bugs System
Pushed into 5.0.54
[14 Dec 2007 8:19] Bugs System
Pushed into 5.1.23-rc
[14 Dec 2007 8:22] Bugs System
Pushed into 6.0.5-alpha
[14 Jan 2008 20: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).