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
Triage: D2 (Serious)

[26 Oct 2007 18:43] Allen Lakomiak
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
[27 Oct 2007 7:25] Valeriy Kravchuk
Thank you for a problem report. Please, send the exact test case, with CREATE TABLE and data.
[29 Oct 2007 11:47] Allen Lakomiak
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);
[29 Oct 2007 14:10] Hartmut Holzgraefe
4 result rows with 4.1, 0 with 5.0 and 5.1, 4.1 result looks right

will attach test case
[29 Oct 2007 14:30] Hartmut Holzgraefe
mysqltest test case

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

[29 Oct 2007 14:30] Hartmut Holzgraefe
probably zerofill related
[29 Oct 2007 14:37] Hartmut Holzgraefe
seems as if the ZEROFILL attribute is the culpirt here ...
[27 Nov 2007 17:45] 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/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.
[28 Nov 2007 0:16] Sergey Petrunya
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.
[28 Nov 2007 14:04] 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/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.
[15 Feb 2008 13:47] 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/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.
[13 Mar 2008 19:28] Bugs System
Pushed into 6.0.5-alpha
[13 Mar 2008 19:35] Bugs System
Pushed into 5.1.24-rc
[13 Mar 2008 19:42] Bugs System
Pushed into 5.0.60
[30 Mar 2008 20:42] Jon Stephens
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.