Bug in MySQL 4.0.3-beta with DISTINCT

How-to-Repeat:

I've supplied a reproducible bug in the mysql-bug.tar.gz file in
support.mysql.com/pub/mysql/secret directory. The relevant files are:

mysql/auto/*.MYI

and

development-bin.001
development-bin.002
development-bin.003

The server actually crashes based on whether or not DISTINCT is in the
query, so at least that much has been determined as the factor. I tested
this problem on two different boxes, both of them Intel architecture
running pentiums, running Redhat 6.2 and Redhat 7.3. The tables
originally came from a MySQL 3.23 server which I updated on our
development box to MySQL 4.0.3 using RPM. The query that follows was
issued manually using the 'mysql' client that comes as an RPM as well.
The query issued is:

SELECT distinct a.vehicleid, a.vin, a.year, a.price, a.saleprice,
a.miles, a.doors, a.descr, a.adtext, a.status, a.stocknum, a.views,
a.thumb AS thumbnail, a.image AS outside, a.color, a.descr, a.engine,
b.descr as make, c.descr as model, c.clas, d.dealerid, d.dealername,
d.logo, d.thumb, d.path, d.address1, d.address2, d.city, d.state, d.zip
FROM auto_vehicle a LEFT JOIN auto_vehicle_options USING(vehicleid),
auto_make b, auto_model c, auto_dealer d WHERE a.dealerid = '150083' AND

a.vehicleid = '119248' AND b.makeid = a.makeid AND c.modelid = a.modelid

AND d.dealerid = a.dealerid AND d.enabled = 'y' AND d.parentid = 150035
ORDER BY d.priority desc LIMIT 0,10;

That query actually executes properly on MySQL 3.23.x branch, and has
been in use for a while now. In addition, some of the tables were
updated from ISAM tables to MyISAM. The tables that were upgraded from
their ISAM counterparts are:

auto_dealer_dealer.ISD
auto_dealer_dealerloc.ISD
auto_dealerloc.ISD
auto_dimages.ISD
auto_feature.ISD
auto_make.ISD
auto_model.ISD
auto_options.ISD
auto_vehicle.ISD
auto_vehicle_options.ISD
auto_vimages.ISD

All the other tables were listed as .MYI tables, so they were in a
compatible format for use with OPTIMIZE and ANALYZE. Again, I've
converted all these tables to the latest MyISAM format, using ALTER
TABLE table TYPE = MYISAM, followed by a myisamchk -s table, followed by
a myisamchk --check --analyze table.

If you have questions, comments, or need additional information about
the subject of this message, feel free to contact us via phone or
e-mail.

Regards,

Patrick O'Lone
------------------------------
TOWNNEWS.COM/INN
Internet Software Engineer
Phone: 309-743-0809
Toll: 800-579-6397
Fax: 309-743-0830
Email: polone@townnews.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-thread12514@lists.mysql.com
To unsubscribe, e-mail

Bug in MySQL 4.0.3-beta with DISTINCT

Hi!

>>>>> "Patrick" == Patrick O'Lone writes:

Patrick> How-to-Repeat:
Patrick> I've supplied a reproducible bug in the mysql-bug.tar.gz file in
Patrick> support.mysql.com/pub/mysql/secret directory. The relevant files are:

Patrick> mysql/auto/*.MYI

Patrick> and

Patrick> development-bin.001
Patrick> development-bin.002
Patrick> development-bin.003



Is this bug already fixed ?
(I think Sergei fixed it but I am not 100 % sure)

If this is, please send a message to bugs@list.mysql.com about this.

Regards,
Monty

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

Re: Bug in MySQL 4.0.3-beta with DISTINCT

Hi!

On Sep 09, Patrick O'Lone wrote:
> How-to-Repeat:
>
> I've supplied a reproducible bug in the mysql-bug.tar.gz file in
> support.mysql.com/pub/mysql/secret directory. The relevant files are:
>
> mysql/auto/*.MYI
>
> and
>
> development-bin.001
> development-bin.002
> development-bin.003
>
> The server actually crashes based on whether or not DISTINCT is in the
> query, so at least that much has been determined as the factor. I tested
> this problem on two different boxes, both of them Intel architecture
> running pentiums, running Redhat 6.2 and Redhat 7.3. The tables
> originally came from a MySQL 3.23 server which I updated on our
> development box to MySQL 4.0.3 using RPM. The query that follows was
> issued manually using the 'mysql' client that comes as an RPM as well.
> The query issued is:
>
> SELECT distinct a.vehicleid, a.vin, a.year, a.price, a.saleprice,
> a.miles, a.doors, a.descr, a.adtext, a.status, a.stocknum, a.views,
> a.thumb AS thumbnail, a.image AS outside, a.color, a.descr, a.engine,
> b.descr as make, c.descr as model, c.clas, d.dealerid, d.dealername,
> d.logo, d.thumb, d.path, d.address1, d.address2, d.city, d.state, d.zip
> FROM auto_vehicle a LEFT JOIN auto_vehicle_options USING(vehicleid),
> auto_make b, auto_model c, auto_dealer d WHERE a.dealerid = '150083' AND
> a.vehicleid = '119248' AND b.makeid = a.makeid AND c.modelid = a.modelid
> AND d.dealerid = a.dealerid AND d.enabled = 'y' AND d.parentid = 150035
> ORDER BY d.priority desc LIMIT 0,10;

The bug is fixed, thank you for a test case.

Still, I'd like to note, that your query is overcomplicated.
Table auto_vehicle_options is not used anywhere in the query, so it can
be removed from FROM clause. All other tables are referenced by their
PRIMARY KEYs, so neither DISTINCT, nor ORDER BY, nor LIMIT are necessary,
as this query is guaranteed to return no more than one row.

Regards,
Sergei

--
MySQL Development Team
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
<___/

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