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 18:43	Modified:	30 Mar 2008 20:42
Reporter:	Allen Lakomiak	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S2 (Serious)
Version:	5.0.45	OS:	Linux
Assigned to:	Georgi Kodinov	CPU Architecture:	Any
Tags:	regression, wrong result, ZEROFILL

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`,`lastyear_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.