Bug #3551 Incorect row count on query

• Submitted: 23 Apr 2004 10:54
• Modified: 28 Apr 2004 12:10
• Reporter: Matthew Taylor
• Status: Can't repeat
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 5.0.0-alpha
• OS: Linux (Linux)
• CPU Architecture: Any

[23 Apr 2004 10:54] Matthew Taylor

Description:
 This query

SELECT  DISTINCT contribution.contribution_id FROM contribution WHERE ((contribution.site_id = 2) AND (contribution.local_id > 130316613));
gives
Empty set (0.00 sec)
in mysql

In phpmyadmin it gives a full page of results but reports 0 rows.

There is no problem with
SELECT   contribution.contribution_id FROM contribution WHERE ((contribution.site_id = 2) AND (contribution.local_id > 130316613));

or

SELECT DISTINCT * FROM contribution WHERE ((contribution.site_id = 2) AND (contribution.local_id > 130316613));

The table ddl is

CREATE TABLE contribution (
  contribution_id varchar(12) NOT NULL default '',
  group_code varchar(64) NOT NULL default '',
  object_id varchar(12) default NULL,
  object_class varchar(128) default NULL,
  user_id varchar(12) default NULL,
  contribution_source text,
  contribution_date datetime NOT NULL default '0000-00-00 00:00:00',
  contribution_comment varchar(64) default NULL,
  status int(11) default NULL,
  is_queried int(11) default NULL,
  version varchar(64) NOT NULL default '',
  local_id int(11) default NULL,
  site_id int(11) default NULL,
  PRIMARY KEY  (contribution_id),
  UNIQUE KEY repeats (object_id,object_class(42),version),
  KEY max (site_id,local_id),
  KEY object_class (object_class(42)),
  KEY contribution_date (contribution_date),
  KEY group_code (group_code),
  KEY object_id (object_id),
  KEY status (status),
  KEY version (version)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

How to repeat:
See above

[28 Apr 2004 12:10] Alexander Keremidarski

Not enough information was provided for us to be able
to handle this bug. Please re-read the instructions at
http://bugs.mysql.com/how-to-report.php

If you can provide more information, feel free to add it
to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

Additional info:

mysql> insert into contribution (contribution_id, site_id, local_id) values(1, 2, 130316614);
Query OK, 1 row affected (0.02 sec)
 
mysql> SELECT  DISTINCT contribution.contribution_id FROM contribution WHERE ((contribution.site_id = 2) AND (contribution.local_id > 130316613));
+-----------------+
| contribution_id |
+-----------------+
| 1               |
+-----------------+
1 row in set (0.01 sec)