Alex,
can you print the EXPLAIN SELECT ... for the count(*) and the other query?
Regards,
Heikki
At 12:03 PM 5/9/01 +0200, you wrote:
>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.
>
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
