Bug #4275 SELECT : Constant values are affecting other columns
Submitted: 24 Jun 2004 17:36 Modified: 28 Jun 2004 19:47
Reporter: Sébastien Buysse Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.0.15 OS:Linux (Mandrake 9.2)
Assigned to: Matthew Lord CPU Architecture:Any

[24 Jun 2004 17:36] Sébastien Buysse
Description:

When I run a query with constant values they are showing correctly most of the time, but with special values such as 0, 1 or a number small enough to be the index of a resulting row, the corresponding row is overrided with the constant.

How to repeat:
Here is my query, you see clearly BillId twice

  SELECT BillId, BillId, (BillId + 100), BillDate, 0, BillIp, "C", BillTotal, "OGONE"
    FROM AKR_BILL
    WHERE (BillActive = 'Y')

result:

 Full Texts
BillId 	BillId 	( BillId +100 ) 	BillDate 	0 	BillIp 	C 	BillTotal 	OGONE
0 	6 	106 	2002-04-27 08:11:39 	0 	193.250.234.101 	C 	29.99 	OGONE
0 	12 	112 	2002-06-01 23:48:57 	0 	65.29.164.94 	C 	29.99 	OGONE
0 	15 	115 	2002-06-22 21:01:21 	0 	213.98.94.87 	C 	29.99 	OGONE

If I use 1 instead of 0, the second row is full of 1 and BillId is ok in the first row... This is not really logical.

With this query (notice the 1-1), the result is ok.

  SELECT BillId, BillId, (BillId + 100), BillDate, 1-1, BillIp, "C", BillTotal, "OGONE"
    FROM AKR_BILL
    WHERE (BillActive = 'Y')

 Full Texts
BillId 	BillId 	( BillId +100 ) 	BillDate 	1 -1 	BillIp 	C 	BillTotal 	OGONE
6 	6 	106 	2002-04-27 08:11:39 	0 	193.250.234.101 	C 	29.99 	OGONE
12 	12 	112 	2002-06-01 23:48:57 	0 	65.29.164.94 	C 	29.99 	OGONE
15 	15 	115 	2002-06-22 21:01:21 	0 	213.98.94.87 	C 	29.99 	OGONE
[25 Jun 2004 1:35] Matthew Lord
Hi Seb,

I was unable to reproduce this on 4.0.20.  If you can could you provide me with a dump of the table 
that you are using so that I can examine it?

Best Regards
[25 Jun 2004 11:58] Sébastien Buysse
Here is a dump of the first rows of the table.

CREATE TABLE `AKR_BILL` (
  `BILLID` int(10) unsigned NOT NULL auto_increment,
  `BILLNUMBER` int(10) unsigned default '0',
  `BILLPROMO` int(10) unsigned default NULL,
  `BILLUSER` int(10) unsigned NOT NULL default '0',
  `BILLCOMPANY` varchar(200) default NULL,
  `BILLDATE` datetime NOT NULL default '0000-00-00 00:00:00',
  `BILLTOTAL` float NOT NULL default '0',
  `BILLIP` varchar(200) NOT NULL default '',
  `BILLADRESS` varchar(200) NOT NULL default '',
  `BILLPOSTAL` varchar(100) NOT NULL default '',
  `BILLCITY` varchar(100) NOT NULL default '',
  `BILLCOUNTRY` varchar(100) NOT NULL default '',
  `BILLCANCELLED` datetime default NULL,
  `BILLVAT` varchar(200) default NULL,
  `BILLREGCOM` varchar(200) default NULL,
  `BILLACTIVE` char(1) NOT NULL default '',
  PRIMARY KEY  (`BILLID`),
  UNIQUE KEY `KKAPBILL` (`BILLID`)
) TYPE=ISAM AUTO_INCREMENT=1038 ;

#
# Dumping data for table `AKR_BILL`
#

INSERT INTO `AKR_BILL` VALUES (1, 0, NULL, 2, 'hjk', '2002-04-15 21:27:08', '29.99', '213.224.83.118', 'hj', 'jh', 'hjhj', 'Jamaica', NULL, 'hjhj', 'jh', 'N');
INSERT INTO `AKR_BILL` VALUES (2, 0, NULL, 3, '', '2002-04-19 03:36:02', '29.99', '207.35.39.42', '35 Capel Road', 'WD19 4FE', 'Oxhey Village', 'United Kingdom', NULL, '', '', 'N');
INSERT INTO `AKR_BILL` VALUES (3, 0, NULL, 4, '', '2002-04-19 03:39:09', '29.99', '207.35.39.42', '16 Jesmond Terrace', 'NE26 2JF', 'Newcastle', 'United Kingdom', NULL, '', '', 'N');
INSERT INTO `AKR_BILL` VALUES (4, 0, NULL, 5, '', '2002-04-26 12:28:00', '29.99', '200.242.49.83', '2 Garland Way', 'HP22 5QW', 'Aylesbury', 'United Kingdom', NULL, '', '', 'N');
INSERT INTO `AKR_BILL` VALUES (5, 0, NULL, 6, '', '2002-04-26 12:31:38', '29.99', '200.242.49.83', '35 Capel Road', 'WD19 4FE', 'Oxhey Village', 'United Kingdom', NULL, '', '', 'N');
INSERT INTO `AKR_BILL` VALUES (6, 1, NULL, 7, 'N/A', '2002-04-27 08:11:39', '29.99', '193.250.234.101', 'Villa Ramuntxo Quartier Gaharduo ,       OSSES.', 'PA . 64780', 'N/A', 'France', NULL, '', '', 'Y');
INSERT INTO `AKR_BILL` VALUES (7, 0, NULL, 8, '', '2002-05-02 02:04:09', '29.99', '', '522 s pearl st', '43215', 'columbus', 'United States', NULL, '', '', 'N');
INSERT INTO `AKR_BILL` VALUES (8, 0, NULL, 9, '', '2002-05-19 14:24:34', '29.99', '217.128.243.145', '56 Rue du Ballon', '59800', 'LILLE', 'France', NULL, '', '', 'N');
[28 Jun 2004 19:47] Matthew Lord
Hi Sebastien,

I could not repeat in 4.0.20.  This bug looks like it has been fixed since 4.0.15.

Best Regards