Bug #26206 Subselect having null value result noting

• Submitted: 9 Feb 2007 5:22
• Modified: 12 May 2007 15:42
• Reporter: Cuaap
• Status: No Feedback
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 5.0.27
• OS: Windows (Win server 2003)
• CPU Architecture: Any
• Tags: Subselect having null value result nothing

[9 Feb 2007 5:22] Cuaap

Description:
Hi, I am may be new in using mysql. Before mysql 5 released I am using mysql 3. In mysql 5 I try to use subselect with null value but result nothing. Here the diskription :
First I am create table1 with ddl:

CREATE TABLE `agus1` (`noreff` varchar(12) NOT NULL default '') ENGINE=InnoDB 
DEFAULT CHARSET=latin1

then fill the value :
noreff
  x
  y
  z
  p
  q

Second table named table2 have 2 column contain :
with ddl : 
CREATE TABLE `agus2` (`nomer` varchar(12) NOT NULL default '',
  `noreff` varchar(12) default NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1

Then fill the value :
 nomer | noreff
  a    | null (null value) 
  b    |  x
  c    |  y

then i am use query : select * from table1 where column1 not in (select column2 from table2). Result empty query.
Truely I expected the result :

 noreff
   x
   p
   q

In mysql 3 don't have subselect so I don't know is bug or not.

Other bug if i am filling value in table2 (agus2) but only column2 (noreff). I expect that column1 fill with '' like mysql 3. But in mysql 5 have error : column 'nomer' cannot be null.
In mysql 3 or 4.0 isn't not happen.

How to repeat:
First I am create table1 with ddl:

CREATE TABLE `agus1` (`noreff` varchar(12) NOT NULL default '') ENGINE=InnoDB 
DEFAULT CHARSET=latin1

then fill the value :
noreff
  x
  y
  z
  p
  q

Second table named table2 have 2 column contain :
with ddl : 
CREATE TABLE `agus2` (`nomer` varchar(12) NOT NULL default '',
  `noreff` varchar(12) default NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1

Then fill the value :
 nomer | noreff
  a    | null (null value) 
  b    |  x
  c    |  y

then i am use query : select * from table1 where column1 not in (select column2 from table2). Result empty query.
Truely I expected the result :

 noreff
   x
   p
   q

In mysql 3 don't have subselect so I don't know is bug or not.

Other bug if i am filling value in table2 (agus2) but only column2 (noreff). I expect that column1 fill with '' like mysql 3. But in mysql 5 have error : column 'nomer' cannot be null.
In mysql 3 or 4.0 isn't not happen

Suggested fix:
fix the null value and default value

[9 Feb 2007 7:47] Max Fischer

That's how a subquery works. If it finds a null, it voids the result.

[9 Feb 2007 7:50] Max Fischer

Respecting the second one, it is a bug.
When you explicitly send a null value to a non-null column, it is expected to have an error.
But if you left that column out of the field list in the insert statement, than it should fill with the default value.

[9 Feb 2007 10:57] Sveta Smirnova

Thank you for the report.

Please provide output of SELECT @@sql_mode;

[10 Mar 2007 0:00] Bugs System

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

[12 Apr 2007 15:42] Valeriy Kravchuk

Feedback is still needed.

[12 May 2007 23:00] Bugs System

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".