Re: "strange" EXPLAIN result of a subselect ?
am 18.11.2002 01:39:51 von Jocelyn FournierHi,
I've just hit another problem with your latest patch applied :
EXPLAIN SELECT numreponse FROM threadhardwarefr7 WHERE numeropost='1' AND
numreponse=(SELECT 1 FROM threadhardwarefr7 WHERE numeropost='1');
=> it freezes mysql monitor
(BTW no mailing list seems to work right now ?)
Regards,
Jocelyn
----- Original Message -----
From: "Sanja Byelkin"
To: "Jocelyn Fournier"
Cc:
Sent: Sunday, November 17, 2002 8:30 PM
Subject: Re: "strange" EXPLAIN result of a subselect ?
> Hi!
>
> On Sat, Nov 16, 2002 at 06:41:23PM -0000, Jocelyn Fournier wrote:
> > Hi,
> >
> > If you execute the select alone, you obtain :
> >
> > mysql> EXPLAIN SELECT MAX(numreponse) FROM threadhardwarefr7 WHERE
> > numeropost='1';
> >
+----+-------------+-------+------+---------------+------+-- -------+------+-
> > -----+------------------------------+
> > | id | select_type | table | type | possible_keys | key | key_len | ref
|
> > rows | Extra |
> >
+----+-------------+-------+------+---------------+------+-- -------+------+-
> > -----+------------------------------+
> > | 1 | SIMPLE | | | | | |
|
> > | Select tables optimized away |
> >
+----+-------------+-------+------+---------------+------+-- -------+------+-
> > -----+------------------------------+
> > 1 row in set (0.00 sec)
> >
> > However inside a subselect you obtain :
> >
> > mysql> EXPLAIN SELECT numreponse FROM threadhardwarefr7 WHERE
numeropost='1'
> > AND numreponse=(SELECT MAX(numreponse) FROM threadhardwarefr7 WHERE
> > numeropost='1');
> >
+----+-------------+-------------------+-------+------------ --------+-------
> > --+---------+-------------+------+----------------+
> > | id | select_type | table | type | possible_keys |
key
> > | key_len | ref | rows | Extra |
> >
+----+-------------+-------------------+-------+------------ --------+-------
> > --+---------+-------------+------+----------------+
> > | 1 | PRIMARY | threadhardwarefr7 | const | PRIMARY,numreponse |
> > PRIMARY | 7 | const,const | 1 | |
> > | 2 | SUBSELECT | | | |
> > | | | | No tables used |
> >
+----+-------------+-------------------+-------+------------ --------+-------
> > --+---------+-------------+------+----------------+
> > 2 rows in set (0.01 sec)
> >
> > I think "No tables used" here is not completely accurate.
>
> Thank you for good bugreport yet another time. I fix this bug. Here is
> patch:
>
> diff -Nrc a/sql/sql_select.cc b/sql/sql_select.cc
> *** a/sql/sql_select.cc Sun Nov 17 22:28:33 2002
> --- b/sql/sql_select.cc Sun Nov 17 22:28:33 2002
> ***************
> *** 395,407 ****
> zero_result_cause= "No matching min/max row";
> DBUG_RETURN(0);
> }
> ! if (select_options & SELECT_DESCRIBE)
> ! {
> ! select_describe(this, false, false, false,
> ! "Select tables optimized away");
> ! delete procedure;
> ! DBUG_RETURN(1);
> ! }
> tables_list= 0; // All tables resolved
> }
> }
> --- 395,401 ----
> zero_result_cause= "No matching min/max row";
> DBUG_RETURN(0);
> }
> ! zero_result_cause= "Select tables optimized away";
> tables_list= 0; // All tables resolved
> }
> }
> ***************
> *** 663,669 ****
> { // Only test of functions
> error=0;
> if (select_options & SELECT_DESCRIBE)
> ! select_describe(this, false, false, false, "No tables used");
> else
> {
> result->send_fields(fields_list,1);
> --- 657,664 ----
> { // Only test of functions
> error=0;
> if (select_options & SELECT_DESCRIBE)
> ! select_describe(this, false, false, false,
> ! (zero_result_cause?zero_result_cause:"No tables used"));
> else
> {
> result->send_fields(fields_list,1);
>
>
> [skip]
>
> --
> For technical support contracts, visit https://order.mysql.com/
> __ ___ ___ ____ __
> / |/ /_ __/ __/ __ \/ / Mr. Oleksandr Byelkin
> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
> /_/ /_/\_, /___/\___\_\___/ Lugansk, Ukraine
> <___/ 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-thread13011@lists.mysql.com
To unsubscribe, e-mail