Bug in DELETE and SHOW CREATE TABLE with HEAP table and BTREE index in MySQL-4.1

am 15.11.2002 20:43:42 von Jocelyn Fournier

Hi,

How-to-repeat :

Download the table online1 here (11 KB) :

ftp://support.mysql.com/pub/mysql/secret/heap_btree.tar.gz

Then :

CREATE TABLE `online` (
`pseudo` char(35) character set latin1 NOT NULL default '',
`date` int(10) unsigned NOT NULL default '0',
`cat` tinyint(4) unsigned NOT NULL default '0',
`forum` tinyint(3) unsigned NOT NULL default '0',
PRIMARY KEY (`pseudo`),
KEY `forum` (`forum`),
KEY `date` USING BTREE (`date`)
) TYPE=HEAP CHARSET=latin1

INSERT INTO online SELECT * FROM online1

DELETE FROM online WHERE date < 1037388159;
Query OK, 0 rows affected (0.00 sec)

SELECT * FROM online WHERE date < 1037388159;
+--------------------+------------+-----+-------+
| pseudo | date | cat | forum |
+--------------------+------------+-----+-------+
| KevinTheProud | 1037388158 | 12 | 1 |

| debeman | 1037388139 | 0 | 1 |
+--------------------+------------+-----+-------+
34 rows in set (0.00 sec)

=> DELETE doesn't work properly

Bug #2 :

SHOW CREATE TABLE online;

| online | CREATE TABLE `online` (
`pseudo` char(35) character set latin1 NOT NULL default '',
`date` int(10) unsigned NOT NULL default '0',
`cat` tinyint(4) unsigned NOT NULL default '0',
`forum` tinyint(3) unsigned NOT NULL default '0',
PRIMARY KEY (`pseudo`),
KEY `forum` (`forum`),
KEY `date` (`date`)
) TYPE=HEAP CHARSET=latin1 |

=> The BTREE index is not mentionned.

Regards,
Jocelyn

------------------------------------------------------------ ---------
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 bugs-thread13008@lists.mysql.com
To unsubscribe, e-mail

Re: Bug in DELETE and SHOW CREATE TABLE with HEAP table and BTREE index in MySQL-4.1

am 18.11.2002 14:25:32 von Jocelyn Fournier

Hi,

It's probably the same bug, but just in case :

mysql> SELECT MAX(date) FROM online;
+------------+
| MAX(date) |
+------------+
| 1037388543 |
+------------+
1 row in set (0.00 sec)

mysql> SELECT date FROM online ORDER BY date DESC LIMIT 1;
+------------+
| date |
+------------+
| 1037388543 |
+------------+
1 row in set (0.00 sec)

mysql> SELECT date FROM online ORDER BY date DESC;
+------------+
| date |
+------------+
| 1037388549 |
| 1037388549 |
| 1037388549 |
| 1037388548 |
| 1037388548 |

Well obvious, it wasn't the max value ;)

Regards,
Jocelyn

----- Original Message -----
From: "Jocelyn Fournier"
To:
Sent: Friday, November 15, 2002 7:43 PM
Subject: Bug in DELETE and SHOW CREATE TABLE with HEAP table and BTREE index
in MySQL-4.1

> Hi,
>
> How-to-repeat :
>
> Download the table online1 here (11 KB) :
>
> ftp://support.mysql.com/pub/mysql/secret/heap_btree.tar.gz
>
> Then :
>
> CREATE TABLE `online` (
> `pseudo` char(35) character set latin1 NOT NULL default '',
> `date` int(10) unsigned NOT NULL default '0',
> `cat` tinyint(4) unsigned NOT NULL default '0',
> `forum` tinyint(3) unsigned NOT NULL default '0',
> PRIMARY KEY (`pseudo`),
> KEY `forum` (`forum`),
> KEY `date` USING BTREE (`date`)
> ) TYPE=HEAP CHARSET=latin1
>
> INSERT INTO online SELECT * FROM online1
>
> DELETE FROM online WHERE date < 1037388159;
> Query OK, 0 rows affected (0.00 sec)
>
> SELECT * FROM online WHERE date < 1037388159;
> +--------------------+------------+-----+-------+
> | pseudo | date | cat | forum |
> +--------------------+------------+-----+-------+
> | KevinTheProud | 1037388158 | 12 | 1 |
>
> | debeman | 1037388139 | 0 | 1 |
> +--------------------+------------+-----+-------+
> 34 rows in set (0.00 sec)
>
> => DELETE doesn't work properly
>
> Bug #2 :
>
> SHOW CREATE TABLE online;
>
> | online | CREATE TABLE `online` (
> `pseudo` char(35) character set latin1 NOT NULL default '',
> `date` int(10) unsigned NOT NULL default '0',
> `cat` tinyint(4) unsigned NOT NULL default '0',
> `forum` tinyint(3) unsigned NOT NULL default '0',
> PRIMARY KEY (`pseudo`),
> KEY `forum` (`forum`),
> KEY `date` (`date`)
> ) TYPE=HEAP CHARSET=latin1 |
>
>
> => The BTREE index is not mentionned.
>
> Regards,
> Jocelyn
>

------------------------------------------------------------ ---------
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 bugs-thread13019@lists.mysql.com
To unsubscribe, e-mail

Re: Bug in DELETE and SHOW CREATE TABLE with HEAP table and BTREE index in MySQL-4.1

am 18.11.2002 18:25:19 von Ramil Kalimullin

Hello Jocelyn,

Friday, November 15, 2002, 11:43:42 PM, you wrote:

JF> Hi,
JF> How-to-repeat :
JF> Download the table online1 here (11 KB) :
JF> ftp://support.mysql.com/pub/mysql/secret/heap_btree.tar.gz

Thank you for your bug report.
I found the cause of this bug today.
I'll fix it tomorrow.

--
Best regards,
Ramil mailto:ram@udm.net

------------------------------------------------------------ ---------
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 bugs-thread13025@lists.mysql.com
To unsubscribe, e-mail

Re[2]: Bug in DELETE and SHOW CREATE TABLE with HEAP table and BTREE index in MySQL-4.1

am 19.11.2002 16:06:03 von Ramil Kalimullin

Hello Jocelyn,

Monday, November 18, 2002, 5:25:32 PM, you wrote:

JF> It's probably the same bug, but just in case :

Thank you.
I just pushed fix.

Regards,
Ramil.
--
For technical support contracts, visit https://order.mysql.com/?ref=mrka
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Ramil Kalimullin
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Izhevsk, Russia
<___/ www.mysql.com

------------------------------------------------------------ ---------
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 bugs-thread13031@lists.mysql.com
To unsubscribe, e-mail

Re: Bug in DELETE and SHOW CREATE TABLE with HEAP table and BTREE index in MySQL-4.1

am 19.11.2002 17:08:35 von Ramil Kalimullin

Hello Jocelyn,

Friday, November 15, 2002, 11:43:42 PM, you wrote:
JF> Bug #2 :

JF> SHOW CREATE TABLE online;

JF> | online | CREATE TABLE `online` (
JF> `pseudo` char(35) character set latin1 NOT NULL default '',
JF> `date` int(10) unsigned NOT NULL default '0',
JF> `cat` tinyint(4) unsigned NOT NULL default '0',
JF> `forum` tinyint(3) unsigned NOT NULL default '0',
JF> PRIMARY KEY (`pseudo`),
JF> KEY `forum` (`forum`),
JF> KEY `date` (`date`)
JF> ) TYPE=HEAP CHARSET=latin1 |
=>> The BTREE index is not mentionned.

Thanks. Fixed. Pushed.

Regards,
Ramil.
--
For technical support contracts, visit https://order.mysql.com/?ref=mrka
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Ramil Kalimullin
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Izhevsk, Russia
<___/ www.mysql.com

------------------------------------------------------------ ---------
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 bugs-thread13034@lists.mysql.com
To unsubscribe, e-mail