List:General Discussion« Previous MessageNext Message »
From:BAUMEISTER Alexandre Date:May 9 2001 10:03am
Subject:Wrong COUNT(*) with Innobase !?
View as plain text  
Bonjour,

  Mysql-3.23.37  with  latest patch (patch of the patch from Heikki :)
  ) under Solaris 2.8 (SPARC 64bit).

  I have a table :

CREATE TABLE mybuffer (
  id int(10) unsigned NOT NULL auto_increment,
  bourse char(3) NOT NULL default '',
  service varchar(10) NOT NULL default '',
  date datetime NOT NULL default '0000-00-00 00:00:00',
  data text,
  PRIMARY KEY  (id),
  KEY id_bourse (bourse),
  KEY id_date (date)
) TYPE=Innobase;

mysql> desc mybuffer;
+---------+------------------+------+-----+---------------------+----------------+
| Field   | Type             | Null | Key | Default             | Extra          |
+---------+------------------+------+-----+---------------------+----------------+
| id      | int(10) unsigned |      | PRI | NULL                | auto_increment |
| bourse  | char(3)          |      | MUL |                     |                |
| service | varchar(10)      |      |     |                     |                |
| date    | datetime         |      | MUL | 0000-00-00 00:00:00 |                |
| data    | text             | YES  |     | NULL                |                |
+---------+------------------+------+-----+---------------------+----------------+
5 rows in set (0.00 sec)

  If I look for distinct 'bourse' :

mysql> select distinct bourse from mybuffer;
+--------+
| bourse |
+--------+
| NAS    |
| PAR    |
+--------+
2 rows in set (0.75 sec)

  If I want to know the number of rows with 'bourse' equal to 'NAS' :

mysql> select count(*) from mybuffer where bourse='NAS';
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.01 sec)

  !!??

  If I remove the count() :

mysql> select * from mybuffer where bourse='NAS';
+--------+--------+---------+---------------------+------------------------------------------------+
| id     | bourse | service | date                | data                                  
        |
+--------+--------+---------+---------------------+------------------------------------------------+
| 122429 | NAS    | live    | 2001-04-26 18:46:34 | TCHBS?0138.95?4153600?7118:46:34?51300
        |
| 122430 | NAS    | live    | 2001-04-26 18:46:34 |
TASML?0126.55?41856100?7118:46:34?511000       |
| 122431 | NAS    | live    | 2001-04-26 18:46:34 |
TINTC?0129.8?431331600?7118:46:34?51100        |
| 122432 | NAS    | live    | 2001-04-26 18:46:34 |
TBEAS?0138.87?47440700?7118:46:34?51200        |
| 122433 | NAS    | live    | 2001-04-26 18:46:34 |
TINTC?0129.8?431332100?7118:46:34?51500        |
| 122434 | NAS    | live    | 2001-04-26 18:46:34 | TBEAS?0138.9?47440900?7118:46:34?51200
        |
| 122435 | NAS    | live    | 2001-04-26 18:46:34 | TMCSI?0117.8?4130800?7118:46:34?512500
        |
| 122436 | NAS    | live    | 2001-04-26 18:46:34 |
TMSFT?0170.54?432247800?7118:46:34?513100      |
| 122437 | NAS    | live    | 2001-04-26 18:46:34 |
TXOXO?014.89?425024700?7118:46:34?51800        |
| 122438 | NAS    | live    | 2001-04-26 18:46:34 |
TMSFT?0170.54?432248500?7118:46:34?51700       |
| 122439 | NAS    | live    | 2001-04-26 18:46:34 |
TMXIM?0147.95?41768500?7118:46:34?511000       |
....
| 123155 | NAS    | live    | 2001-04-26 18:46:45 | TMYGN?0147.85?4476900?7118:46:45?51100
        |
| 123156 | NAS    | live    | 2001-04-26 18:46:45 | TNITE?0118.4?41775300?7118:46:45?51100
        |
| 123157 | NAS    | live    | 2001-04-26 18:46:45 |
TSEBL?0144.05?48318200?7118:46:45?51200        |
| 123158 | NAS    | live    | 2001-04-26 18:46:45 | TNWAC?0125.18?4265600?7118:46:45?51100
        |
+--------+--------+---------+---------------------+------------------------------------------------+
730 rows in set (0.03 sec)

  Strange.

  I've not yet tried to find if this is only a bug on this table or if
  I  can repeat it on another table. But I can repeat these answers on
  this table.

  Regards,
  Alex.

Thread
weird indexing behavior in presense of LIMITAndrey Gubarev20 Apr
Re:weird indexing behavior in presense of LIMITHeikki Tuuri20 Apr
  • Wrong COUNT(*) with Innobase !?BAUMEISTER Alexandre9 May
Re: Wrong COUNT(*) with Innobase !?Heikki Tuuri9 May
  • Re[2]: Wrong COUNT(*) with Innobase !?BAUMEISTER Alexandre9 May