MySQL Bugs: #31887: DML Select statement not returning same results when executed in version 5

Bug #31887	DML Select statement not returning same results when executed in version 5
Submitted:	26 Oct 2007 20:43	Modified:	30 Mar 22:42
Reporter:	Allen Lakomiak
Status:	Closed
Category:	Server: Types	Severity:	S2 (Serious)
Version:	5.0.45	OS:	Linux
Assigned to:	Georgi Kodinov	Target Version:	5.0+
Tags:	ZEROFILL, regression, wrong result
Triage:	D2 (Serious)

Description:
I am executing the following sql on mysql version 5.0.45 
"SELECT campus_nbr from fin_facutil_buildings_test WHERE campus_nbr = 006 and
concat(campus_nbr,bldg_nbr,bldg_nbr_suffix) = '006102'"  I expected this query to return 4
rows back.  When executing this very same query in version 5.0.45, it returns zero rows. 
The table structures and data sets are the same in both environments, campus_nbr (int(3))
bldg_nbr (int(3)) bldg_nbr_suffix(char(1)).  It appears that if I have the query evaluate
campus_nbr as a number first and then ask to concatenate it with a char, it does not
recognize it.  If I were to change this query and enclose campus_nbr in quotes it then
works properly (campus_nbr = '006')

Any ideas why this has changed from version 4 to version 5?

How to repeat:
Create a table with 2 columns with int as datatype and one with char as datatype.  Execute
a query similar to the one above selecting your three columns.  Execute the query in mysql
4 and then in mysql 5

Thank you for a problem report. Please, send the exact test case, with CREATE TABLE and
data.

TABLE CREATE STATEMENT:

DROP TABLE IF EXISTS `dave`.`fin_facutil_buildings_test`;
CREATE TABLE  `dave`.`fin_facutil_buildings_test` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `term` int(5) default NULL,
  `group_nbr` int(2) default NULL,
  `campus_nbr` int(3) unsigned zerofill default NULL,
  `bldg_nbr` int(3) unsigned zerofill default NULL,
  `bldg_nbr_suffix` char(1) default NULL,
  `bldg_name` varchar(20) default NULL,
  `res_class` int(11) default NULL,
  `ownership_status` int(11) default NULL,
  `year_constructed` int(11) default NULL,
  `est_replacement_cost` int(10) unsigned zerofill default NULL,
  `cost_last_renovation` int(10) unsigned zerofill default NULL,
  `year_last_renovation` smallint(6) default NULL,
  `condition` int(11) default NULL,
  `ac_status` int(11) default NULL,
  `gross_area` int(11) default NULL,
  `orig_bldg_cost` int(10) unsigned zerofill default NULL,
  `nbr_floors` int(10) unsigned zerofill default NULL,
  `lastyear_update` smallint(6) default NULL,
  `record_type` int(11) default NULL,
  `update_username` varchar(20) default NULL,
  `update_timestamp` datetime default NULL,
  `building_status` varchar(30) default NULL,
  `cycle` int(5) unsigned default NULL,
  PRIMARY KEY  (`id`),
  KEY `bld1_indx` (`campus_nbr`,`bldg_nbr`,`bldg_nbr_suffix`,`cycle`)
) ENGINE=MyISAM AUTO_INCREMENT=26511 DEFAULT CHARSET=latin1;

DATA SQL:

INSERT INTO `dave`.`fin_facutil_buildings_test`
(`id`,`term`,`group_nbr`,`campus_nbr`,`bldg_nbr`,`bldg_nbr_suffix`,`bldg_name`,`res_class`
,`ownership_status`,`year_constructed`,`est_replacement_cost`,`cost_last_renovation`,`year
_last_renovation`,`condition`,`ac_status`,`gross_area`,`orig_bldg_cost`,`nbr_floors`,`last
year_update`,`record_type`,`update_username`,`update_timestamp`,`building_status`,`cycle`)
VALUES 
 (26029,20066,7,006,103,'','DUNCAN
HALL',1,1,1965,0010326374,0000120000,1988,2,3,81887,0001175000,0000000003,2000,2,'lakomiak
','2007-09-19 00:00:00','IN PROGRESS',20066),
 (26028,20066,7,006,102,'','WILSON
HALL',1,1,1938,0008411980,0000900000,1994,3,6,41690,0001197500,0000000003,2000,2,'lakomiak
','2007-09-19 00:00:00','IN PROGRESS',20066),
 (16927,20056,7,006,102,'','WILSON
HALL',1,1,1938,0007703278,0000900000,1994,3,6,41690,0001197500,0000000003,2000,2,NULL,NULL
,'CLOSED',20056),
 (14902,20046,7,006,102,'','WILSON
HALL',1,1,1938,0007015736,0000900000,1994,3,6,41690,0001197500,0000000003,2000,2,NULL,NULL
,'CLOSED',20046),
 (9442,20036,7,006,102,'','WILSON
HALL',1,1,1938,0006230671,0000900000,1994,3,6,41690,0001197500,0000000003,2000,2,NULL,NULL
,'CLOSED',20036);

4 result rows with 4.1, 0 with 5.0 and 5.1, 4.1 result looks right

will attach test case

mysqltest test case

Attachment: bug31887.tgz (application/x-gtar, text), 1.04 KiB.

probably zerofill related

seems as if the ZEROFILL attribute is the culpirt here ...

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/38644

ChangeSet@1.2588, 2007-11-27 19:45:05+02:00, gkodinov@magare.gmz +3 -0
  Bug #31887: DML Select statement not returning same results 
  when executed in version 5
  
  Zero fill is a field attribute only. So we can't propagate 
  constants for zerofill fields : the values and expression
  results don't have that flag.
  Fixed by disabling const propagation for fields with 
  ZEROFILL flag.

The problem with the approach taken in the provided fix is that it will make a rather
broad set of query plans to be no longer considered by the optimizer. 

From user point of view, the GA version will start using slower query plans.

Georgi, we should check if we can make a less intrusive fix.

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/38699

ChangeSet@1.2588, 2007-11-28 16:04:31+02:00, gkodinov@magare.gmz +4 -0
  Bug #31887: DML Select statement not returning same results 
  when executed in version 5
  
  Zero fill is a field attribute only. So we can't always 
  propagate  constants for zerofill fields : the values and 
  expression results don't have that flag.
  
  Fixed by converting the const value to a string and 
  using that in const propagation when the context allows it. 
  Disable const propagation for fields with ZEROFILL flag in
  all the other cases.

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/42355

ChangeSet@1.2588, 2008-02-15 15:47:32+02:00, gkodinov@magare.gmz +4 -0
  Bug #31887: DML Select statement not returning same results 
  when executed in version 5
  
  Zero fill is a field attribute only. So we can't always 
  propagate  constants for zerofill fields : the values and 
  expression results don't have that flag.
  
  Fixed by converting the const value to a string and 
  using that in const propagation when the context allows it. 
  Disable const propagation for fields with ZEROFILL flag in
  all the other cases.

Pushed into 6.0.5-alpha

Pushed into 5.1.24-rc

Pushed into 5.0.60

Documented bugfix in the 5.0.60, 5.1.23-ndb-6.3.11, 5.1.24, and 6.0.5 changelogs as
follows:

        Queries testing numeric constants containing leading zeroes against 
        ZEROFILL columns were not evaluated correctly.