Bug 20391 - MySQL 3.23.27-beta gives incorrect results on RH 7.0 vs. RH 6.1
MySQL 3.23.27-beta gives incorrect results on RH 7.0 vs. RH 6.1
Status: CLOSED DUPLICATE of bug 18905
Product: Red Hat Linux
Classification: Retired
Component: mysql (Show other bugs)
7.0
i386 Linux
medium Severity high
: ---
: ---
Assigned To: Patrick Macdonald
David Lawrence
:
Depends On:
Blocks:
  Show dependency treegraph
 
Reported: 2000-11-05 15:05 EST by Need Real Name
Modified: 2007-04-18 12:29 EDT (History)
0 users

See Also:
Fixed In Version:
Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of:
Environment:
Last Closed: 2000-11-05 15:05:57 EST
Type: ---
Regression: ---
Mount Type: ---
Documentation: ---
CRM:
Verified Versions:
Category: ---
oVirt Team: ---
RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: ---


Attachments (Terms of Use)

  None (edit)
Description Need Real Name 2000-11-05 15:05:55 EST
Last Sunday, we migrated from a machine running Red Hat Linux 6.1 
to a new machine running Red Hat Linux 7.0. We have always used 
our own compiled version of MySQL, going all the way back to when 
we first ran it on early Slackware releases. I've never used the RPMs 
for MySQL - we use their full text indexing feature, and that is only 
available in a stable form in the versions that are newer than what 
Red Hat ships.

I'm classifying this as a gcc problem, since we are not using the RH 
bundled MySQL.

The two machines invoved are:

host7 - Red Hat Linux 6.1 - Dual P III 550 Mhz
host8 - Red Hat Linux 7.0 - Dual P III 800 Mhz

The only updates installed on the Red Hat machines, are the 
required security patches and updates. Everything about them is very 
generic.

The example query and tables are:

SELECT sum(ai.itemfee), a.sitename
FROM associateitems ai, associates a
WHERE ai.associatenum=a.associatenum AND 
a.cataloggroupnum=1
AND ai.wasreported='N'
GROUP BY ai.associatenum
ORDER BY a.sitename

CREATE TABLE associateitems (
  associatenum int(11) DEFAULT '0' NOT NULL,
  ordernum int(11) DEFAULT '0' NOT NULL,
  itemnum int(11) DEFAULT '0' NOT NULL,
  quantity int(11) DEFAULT '0' NOT NULL,
  itemcost int(11) DEFAULT '0' NOT NULL,
  itemfee int(11) DEFAULT '0' NOT NULL,
  wasdirect enum('N','Y') DEFAULT 'N' NOT NULL,
  timestamp timestamp(14),
  wasreported enum('N','Y') DEFAULT 'N' NOT NULL,
  dateordered int(8) unsigned DEFAULT '0' NOT NULL,
  inuse enum('N','Y') DEFAULT 'N' NOT NULL,
  KEY associatenum (associatenum),
  KEY ordernum (ordernum),
  KEY itemnum (itemnum)
);

INSERT INTO associateitems VALUES 
(1,32641,28536,1,2249,112,'N',20001105125709,'N',19990322,'N');
INSERT INTO associateitems VALUES 
(1,32643,4027,1,1500,75,'N',20001105125709,'N',19990322,'N');

CREATE TABLE associates (
  associatenum int(11) NOT NULL auto_increment,
  associateid varchar(24) DEFAULT '' NOT NULL,
  cataloggroupnum int(11) DEFAULT '0' NOT NULL,
  payeeaddresstype char(1) DEFAULT '' NOT NULL,
  contactaddresstype char(1) DEFAULT '' NOT NULL,
  timestamp timestamp(14),
  contactaddressnum int(11) DEFAULT '0' NOT NULL,
  payeeaddressnum int(11) DEFAULT '0' NOT NULL,
  storename varchar(128) DEFAULT '' NOT NULL,
  sendstatus enum('N','Y') DEFAULT 'N' NOT NULL,
  sendannouncements enum('N','Y') DEFAULT 'N' NOT NULL,
  sitedescription text DEFAULT '' NOT NULL,
  sitename varchar(128) DEFAULT '' NOT NULL,
  siteurl varchar(128) DEFAULT '' NOT NULL,
  disabled enum('N','Y') DEFAULT 'N' NOT NULL,
  shoppernum int(11) DEFAULT '0' NOT NULL,
  hidden enum('N','Y') DEFAULT 'N' NOT NULL,
  PRIMARY KEY (associatenum),
  KEY cataloggroupnum (cataloggroupnum)
);

INSERT INTO associates VALUES 
(1,'drgames',1,'D','D',19990322105056,2,1,'DarkRealms Game 
Design','Y','Y','A web site for an online RPG.','DarkRealms Game 
Design','http://www.darkrealms.com/~dwind','N',0,'N');

The full associateitems table has 9574 rows, associates has 424 
rows. The two tables are part of a much larger database, but I was 
able to reproduce it with just these two. If I trim the data down to just 
one associate and a couple associate items, as shown above, the 
problem doesn't show up either. Only when its run on a larger set of 
data.

The following are the results of the tests, to summaryize:

--- Red Hat 7.0, 3.23.27-beta, configure flags --with-raid

    Incorrect results (example result is 75).

--- Red Hat 6.1, 3.23.25-beta, configure flags --with-raid
--- Red Hat 6.1, 3.23.27-beta, configure flags --with-raid

    Correct results (example result is 187).

I've only included one row in the results, although all are slightly off on 
the amounts shown. Any suggestions are welcome - I do not think 
there are any errors in the SQL being used, at least, any that should 
cause different results on the same data. I can make the full data set 
available to help troubleshooting what the problem is, if need be.

-rh






--- Red Hat 7.0, 3.23.27-beta, configure flags --with-raid

[root@host8 /root]# uname -a
Linux host8.r5i.com 2.2.16-22smp #1 SMP Tue Aug 22 16:39:21 EDT 
2000 i686 unknown
[root@host8 tmp]# mysqladmin -p ver   
Enter password: 
mysqladmin  Ver 8.9 Distrib 3.23.27-beta, for pc-linux-gnu on i686
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX 
DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free 
software,
and you are welcome to modify and redistribute it under the GPL 
license

Server version          3.23.27-beta
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /tmp/mysql.sock
Uptime:                 6 days 16 hours 10 min 28 sec

Threads: 9  Questions: 63418199  Slow queries: 481  Opens: 
373022  Flush tables: 1  Open tables: 64 Queries per second avg: 
109.981

mysql> SELECT sum(ai.itemfee), a.sitename
    -> FROM associateitems ai, associates a
    -> WHERE ai.associatenum=a.associatenum AND 
a.cataloggroupnum=1
    -> AND ai.wasreported='N'
    -> GROUP BY ai.associatenum
    -> ORDER BY a.sitename
    -> ;
+-----------------+-------------------------------------------------------+
| sum(ai.itemfee) | sitename                                              |
+-----------------+-------------------------------------------------------+
[. cut .]
|              75 | DarkRealms Game Design                                |
[. cut .]
+-----------------+-------------------------------------------------------+
58 rows in set (0.02 sec)

--- Red Hat 6.1, 3.23.25-beta, configure flags --with-raid

[root@host7 /root]# uname -a
Linux host7.r5i.com 2.2.12-20smp #1 SMP Mon Sep 27 10:34:45 EDT 
1999 i686 unknown
[root@host7 /root]# mysqladmin -p ver 
Enter password: 
mysqladmin  Ver 8.9 Distrib 3.23.25-beta, for pc-linux-gnu on i686
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX 
DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free 
software,
and you are welcome to modify and redistribute it under the GPL 
license

Server version          3.23.25-beta
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /tmp/mysql.sock
Uptime:                 2 min 10 sec

Threads: 1  Questions: 10016  Slow queries: 0  Opens: 10  Flush 
tables: 1  Open tables: 2 Queries per second avg: 77.046

mysql> SELECT sum(ai.itemfee), a.sitename
    -> FROM associateitems ai, associates a
    -> WHERE ai.associatenum=a.associatenum AND 
a.cataloggroupnum=1
    -> AND ai.wasreported='N'
    -> GROUP BY ai.associatenum
    -> ORDER BY a.sitename
    -> ;
+-----------------+-------------------------------------------------------+
| sum(ai.itemfee) | sitename                                              |
+-----------------+-------------------------------------------------------+
[. cut .]
|             187 | DarkRealms Game Design                                |
[. cut .]
+-----------------+-------------------------------------------------------+
58 rows in set (0.03 sec)

--- Red Hat 6.1, 3.23.27-beta, configure flags --with-raid

[root@host7 mysql-3.23.27-beta]# uname -a
Linux host7.r5i.com 2.2.12-20smp #1 SMP Mon Sep 27 10:34:45 EDT 
1999 i686 unknown
[root@host7 mysql-3.23.27-beta]# mysqladmin -p ver
Enter password: 
mysqladmin  Ver 8.9 Distrib 3.23.27-beta, for pc-linux-gnu on i686
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX 
DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free 
software,
and you are welcome to modify and redistribute it under the GPL 
license

Server version          3.23.27-beta
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /tmp/mysql.sock
Uptime:                 1 min 36 sec

Threads: 1  Questions: 4  Slow queries: 0  Opens: 8  Flush tables: 1  
Open tables: 2 Queries per second avg: 0.042

mysql> SELECT sum(ai.itemfee), a.sitename
    -> FROM associateitems ai, associates a
    -> WHERE ai.associatenum=a.associatenum AND 
a.cataloggroupnum=1
    -> AND ai.wasreported='N'
    -> GROUP BY ai.associatenum
    -> ORDER BY a.sitename
    -> ;
+-----------------+-------------------------------------------------------+
| sum(ai.itemfee) | sitename                                              |
+-----------------+-------------------------------------------------------+
[. cut .]
|             187 | DarkRealms Game Design                                |
[. cut .]
+-----------------+-------------------------------------------------------+
58 rows in set (0.02 sec)
Comment 1 Jakub Jelinek 2000-11-05 15:22:07 EST
There are two bugs I found in MySQL. One is illegal type punning
on ia32, which causes it not to work properly when compiled
with -fstrict-aliasing (either apply the patch you can find from
bug 18905 or compile with -fno-strict-aliasing), the other is
breakage on sparc (incorrect assembly). If MySQL will still not
work after you apply the patch, please reopen the bug, but I believe
your problem should be fixed by that patch.

*** This bug has been marked as a duplicate of 18905 ***

Note You need to log in before you can comment on or make changes to this bug.