Jocelyn,
below the latest patch which puts the code as it was in 4.0.3. Some LIKE
'abc%' ... DESC queries may return wrong results, but this is the best we
can get to 4.0.4.
I have to ask Monty about the use of HA_READ_PREFIX_LAST. Other bugs may be
lurking in the use/non-use of that search flag.
----- Original Message -----
From: "Jocelyn Fournier" <[EMAIL PROTECTED]>
To: "Heikki Tuuri" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>;
<[EMAIL PROTECTED]>
Sent: Wednesday, September 25, 2002 9:04 PM
Subject: Re: InnoDB: Assertion failure in file ha_innodb.cc line 2180...
> Hi Heikki,
>
> The query doesn't crash anymore, but the ORDER BY doesn't work at all (I
> tested it on a table with data).
>
> How-to-repeat :
>
> INSERT INTO threadhardwarefr13 (pseudo,date,numreponse) VALUES
> ('kytine','2002-09-18 20:37:31','1360'),('joce','2002-09-18
> 20:42:20','1361'),('kytine','2002-09-18 20:46:32','1362');
>
>
> mysql> SELECT LOWER(pseudo),date,numreponse FROM threadhardwarefr13 WHERE
> numeropost='0' ORDER BY numreponse DESC LIMIT 0,3;
> +---------------+---------------------+------------+
> | LOWER(pseudo) | date | numreponse |
> +---------------+---------------------+------------+
> | kytine | 2002-09-18 20:37:31 | 1360 |
> | joce | 2002-09-18 20:42:20 | 1361 |
> | kytine | 2002-09-18 20:46:32 | 1362 |
> +---------------+---------------------+------------+
> 3 rows in set (0.00 sec)
>
> mysql> SELECT LOWER(pseudo),date,numreponse FROM threadhardwarefr13 WHERE
> numeropost='0' ORDER BY numreponse ASC LIMIT 0,3;
> +---------------+---------------------+------------+
> | LOWER(pseudo) | date | numreponse |
> +---------------+---------------------+------------+
> | kytine | 2002-09-18 20:37:31 | 1360 |
> | joce | 2002-09-18 20:42:20 | 1361 |
> | kytine | 2002-09-18 20:46:32 | 1362 |
> +---------------+---------------------+------------+
> 3 rows in set (0.00 sec)
Now this works:
mysql> INSERT INTO threadhardwarefr13 (pseudo,date,numreponse) VALUES
-> ('kytine','2002-09-18 20:37:31','1360'),('joce','2002-09-18
'> 20:42:20','1361'),('kytine','2002-09-18 20:46:32','1362');
Query OK, 3 rows affected (0.04 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT LOWER(pseudo),date,numreponse FROM threadhardwarefr13 WHERE
-> numeropost='0' ORDER BY numreponse DESC LIMIT 0,3;
+---------------+---------------------+------------+
| LOWER(pseudo) | date | numreponse |
+---------------+---------------------+------------+
| kytine | 2002-09-18 20:46:32 | 1362 |
| joce | 2002-09-18 20:42:20 | 1361 |
| kytine | 2002-09-18 20:37:31 | 1360 |
+---------------+---------------------+------------+
3 rows in set (0.01 sec)
mysql>
ChangeSet
1.1318 02/09/25 20:35:12 [EMAIL PROTECTED] +2 -0
ha_innodb.h, ha_innodb.cc:
Remove the flag HA_NOT_READ_PREFIX_LAST because ORDER BY orders wrong
then;
also remove the diagnostic print to avoid cluttering the error log
sql/ha_innodb.h
1.45 02/09/25 20:35:01 [EMAIL PROTECTED] +4 -1
Remove the flag HA_NOT_READ_PREFIX_LAST because ORDER BY orders wrong
then;
also remove the diagnostic print to avoid cluttering the error log
sql/ha_innodb.cc
1.111 02/09/25 20:35:01 [EMAIL PROTECTED] +2 -2
Remove the flag HA_NOT_READ_PREFIX_LAST because ORDER BY orders wrong
then;
also remove the diagnostic print to avoid cluttering the error log
# This is a BitKeeper patch. What follows are the unified diffs for the
# set of deltas contained in the patch. The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User: heikki
# Host: hundin.mysql.fi
# Root: /home/heikki/mysql-4.0
--- 1.110/sql/ha_innodb.cc Wed Sep 25 19:02:06 2002
+++ 1.111/sql/ha_innodb.cc Wed Sep 25 20:35:01 2002
@@ -2180,9 +2180,9 @@
case HA_READ_BEFORE_KEY: return(PAGE_CUR_L);
case HA_READ_PREFIX: return(PAGE_CUR_GE);
case HA_READ_PREFIX_LAST:
- ut_print_timestamp(stderr);
+ /* ut_print_timestamp(stderr);
fprintf(stderr,
- " InnoDB: Warning: Using HA_READ_PREFIX_LAST\n");
+ " InnoDB: Warning: Using HA_READ_PREFIX_LAST\n"); */
return(PAGE_CUR_LE);
/* InnoDB does not yet support ..PREFIX_LAST!
--- 1.44/sql/ha_innodb.h Wed Sep 25 19:02:03 2002
+++ 1.45/sql/ha_innodb.h Wed Sep 25 20:35:01 2002
@@ -81,8 +81,11 @@
HA_NO_WRITE_DELAYED |
HA_PRIMARY_KEY_IN_READ_INDEX |
HA_DROP_BEFORE_CREATE |
+ /* We should also list HA_NOT_READ_PREFIX_LAST
+ here but it currently seems to break ORDER BY;
+ until release 4.0.5 some LIKE 'abc%' ... DESC
+ queries will not work correctly */
HA_NO_PREFIX_CHAR_KEYS |
- HA_NOT_READ_PREFIX_LAST |
HA_TABLE_SCAN_ON_INDEX),
last_dup_key((uint) -1),
start_of_scan(0)
---------------------------------------------------------------------
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
