Yes, I've also tested the LIKE 'abc%' but forgot to include it in my mail :)
CREATE TABLE `threadhardwarefr12` (
`numeropost` mediumint(8) unsigned NOT NULL default '0',
`numreponse` int(10) unsigned NOT NULL default '0',
`pseudo` varchar(35) NOT NULL default '',
`date` datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`pseudo`)
) TYPE=InnoDB
mysql> SELECT LOWER(pseudo),date,numreponse FROM threadhardwarefr12 WHERE
pseudo LIKE 'kyt%' ORDER BY pseudo;
+---------------+---------------------+------------+
| LOWER(pseudo) | date | numreponse |
+---------------+---------------------+------------+
| kytine | 2002-09-18 20:37:31 | 1360 |
| kytoune | 2002-09-18 20:42:20 | 1361 |
+---------------+---------------------+------------+
2 rows in set (0.00 sec)
Regards,
Jocelyn
----- Original Message -----
From: "Heikki Tuuri" <[EMAIL PROTECTED]>
To: "Jocelyn Fournier" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>;
<[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Thursday, September 26, 2002 9:28 PM
Subject: Re: InnoDB: Assertion failure in file ha_innodb.cc line 2180...
> Jocelyn,
>
> ----- Original Message -----
> From: "Jocelyn Fournier" <[EMAIL PROTECTED]>
> To: "Heikki Tuuri" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
> Cc: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>;
> <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
> Sent: Thursday, September 26, 2002 9:53 PM
> Subject: Re: InnoDB: Assertion failure in file ha_innodb.cc line 2180...
>
>
> > Heikki, Jeremy,
> >
> > FYI, I've just tested latest pull (including Monty's patch) without
Heikki
> > 1.1318 changeset (Remove the flag HA_NOT_READ_PREFIX_LAST because ORDER
BY
> > orders wrong then;) and now it works perfectly fine :
> >
> > mysql> SELECT LOWER(pseudo),date,numreponse FROM threadhardwarefr12
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.82 sec)
> >
> > mysql> SELECT LOWER(pseudo),date,numreponse FROM threadhardwarefr12
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.00 sec)
> >
> > So it seems ORDER BY + WHERE primarykey LIKE 'abc%' ORDER BY primarykey
> DESC
> > works fine now.
>
> it is the LIKE 'abc%' which does not work. Your test above does not have
> LIKE.
>
> I tested now that adding the flag
>
> HA_NOT_READ_PREFIX_LAST
>
> back to index_flags() in ha_innodb.h seems to fix also the LIKE problem.
> Thus people who compile themselves can fix also this last known bug
through
> the patch below. The patch probably does not make it to 4.0.4 because the
> 4.0.4 build may have been finished today. 4.0.3 does not have the DESC
bug.
>
> But I will add PAGE_CUR_LE_OR_EXTENDS to InnoDB-4.0.5 and remove the above
> flag. Having different search methods for InnoDB and MyISAM is too
> bug-prone, as we have seen.
>
> > Regards,
> > Jocelyn
>
> Regards,
>
> Heikki
>
> ChangeSet
> 1.1323 02/09/26 21:21:44 [EMAIL PROTECTED] +1 -0
> ha_innodb.h:
> Put the flag HA_NOT_READ_PREFIX_LAST back to index_flags: seems to fix
> also the LIKE ... DESC bug
>
> sql/ha_innodb.h
> 1.46 02/09/26 21:21:34 [EMAIL PROTECTED] +1 -5
> Put the flag HA_NOT_READ_PREFIX_LAST back to index_flags: seems to fix
> also the LIKE ... DESC bug
>
> # 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.45/sql/ha_innodb.h Wed Sep 25 20:35:01 2002
> +++ 1.46/sql/ha_innodb.h Thu Sep 26 21:21:34 2002
> @@ -81,10 +81,6 @@
> 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_TABLE_SCAN_ON_INDEX),
> last_dup_key((uint) -1),
> @@ -100,7 +96,7 @@
> ulong index_flags(uint idx) const
> {
> return (HA_READ_NEXT | HA_READ_PREV | HA_READ_ORDER |
> - HA_KEY_READ_ONLY);
> + HA_KEY_READ_ONLY | HA_NOT_READ_PREFIX_LAST);
> }
> uint max_record_length() const { return HA_MAX_REC_LENGTH; }
> uint max_keys() const { return MAX_KEY; }
>
>
>
> > ----- Original Message -----
> > From: "Heikki Tuuri" <[EMAIL PROTECTED]>
> > To: <[EMAIL PROTECTED]>
> > Cc: "Jocelyn Fournier" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>;
> > <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>;
> > <[EMAIL PROTECTED]>
> > Sent: Thursday, September 26, 2002 8:39 PM
> > Subject: Re: InnoDB: Assertion failure in file ha_innodb.cc line 2180...
> >
> >
> > > Jeremy,
> > >
> > > ----- Original Message -----
> > > From: "Jeremy Zawodny" <[EMAIL PROTECTED]>
> > > To: "Heikki Tuuri" <[EMAIL PROTECTED]>
> > > Cc: "Jocelyn Fournier" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>;
> > > <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>;
> > > <[EMAIL PROTECTED]>
> > > Sent: Thursday, September 26, 2002 8:25 PM
> > > Subject: Re: InnoDB: Assertion failure in file ha_innodb.cc line
2180...
> > >
> > >
> > > > On Wed, Sep 25, 2002 at 09:43:13PM +0300, Heikki Tuuri wrote:
> > > > > 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.
> > > >
> > > > FYI, my server is happy now too.
> > >
> > > thank you for testing the source tree! Now we found and fixed these
bugs
> > > before the actual release was made :).
> > >
> > > One bug remains: I tested yesterday that
> > >
> > > WHERE primarykey LIKE 'abc%' ORDER BY primarykey DESC;
> > >
> > > really works wrong in upcoming InnoDB-4.0.4. It only returns rows
where
> > > primarykey = 'abc'. But better fix that bug in 4.0.5 since it requires
a
> > > somewhat more fundamental change. Until that users beware!
> > >
> > > > I'll rebuild with Monty's latest patch and see that things work as
> > > > expected.
> > > >
> > > > Thanks!
> > > >
> > > > Jeremy
> > >
> > > Thank you,
> > >
> > > Heikki
> > > Innobase Oy
> > >
> > >
> > >
> > > > --
> > > > Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo!
> > > > <[EMAIL PROTECTED]> | http://jeremy.zawodny.com/
> > > >
> > > > MySQL 3.23.51: up 51 days, processed 1,083,975,227 queries (244/sec.
> > avg)
> > >
> > >
> > >
> > >
> > >
> >
>
>
>
>
>
---------------------------------------------------------------------
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
