SUBSELECT printed as DEPENDENT SUBSELECT with MySQL-4.1 ?

Hi,

I'm wondering if the following is not a bug, since I don't see any
dependence in the subselect (unless I missed something) :

How-to-repeat :

mysql> EXPLAIN SELECT a FROM (SELECT 1 as a) b WHERE a IN (SELECT 1 FROM
(SELECT 1) c);
+----+---------------------+------------+--------+---------- -----+------+---
------+------+------+----------------+
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows | Extra |
+----+---------------------+------------+--------+---------- -----+------+---
------+------+------+----------------+
| 1 | PRIMARY | | system | NULL | NULL |
NULL | NULL | 1 | |
| 3 | DEPENDENT SUBSELECT | | system | NULL | NULL |
NULL | NULL | 1 | Using where |
| 4 | DERIVED | NULL | NULL | NULL | NULL |
NULL | NULL | NULL | No tables used |
| 2 | DERIVED | NULL | NULL | NULL | NULL |
NULL | NULL | NULL | No tables used |
+----+---------------------+------------+--------+---------- -----+------+---
------+------+------+----------------+
4 rows in set (0.00 sec)

Or perhaps it's linked to the fact (SELECT 1 FROM (SELECT 1) c) is a
candidate to be reduced during optimisation ?
In this case what is the rule to be a candidate for optimisation ? :)

If I change IN to =, there's no more DEPENDENT SUBSELECT :

mysql> EXPLAIN SELECT a FROM (SELECT 1 as a) b WHERE a=(SELECT 1 FROM
(SELECT 1) c);
+----+-------------+------------+--------+---------------+-- ----+---------+-
-----+------+----------------+
| id | select_type | table | type | possible_keys | key | key_len |
ref | rows | Extra |
+----+-------------+------------+--------+---------------+-- ----+---------+-
-----+------+----------------+
| 1 | PRIMARY | | system | NULL | NULL | NULL |
NULL | 1 | |
| 3 | SUBSELECT | | system | NULL | NULL | NULL |
NULL | 1 | |
| 4 | DERIVED | NULL | NULL | NULL | NULL | NULL |
NULL | NULL | No tables used |
| 2 | DERIVED | NULL | NULL | NULL | NULL | NULL |
NULL | NULL | No tables used |
+----+-------------+------------+--------+---------------+-- ----+---------+-
-----+------+----------------+
4 rows in set (0.00 sec)

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

Re: SUBSELECT printed as DEPENDENT SUBSELECT with MySQL-4.1 ?

On Sun, Dec 15, 2002 at 03:02:57PM -0000, Jocelyn Fournier wrote:
> Hi,
>
> I'm wondering if the following is not a bug, since I don't see any
> dependence in the subselect (unless I missed something) :
>
> How-to-repeat :
>
> mysql> EXPLAIN SELECT a FROM (SELECT 1 as a) b WHERE a IN (SELECT 1 FROM
> (SELECT 1) c);
> +----+---------------------+------------+--------+---------- -----+------+---
> ------+------+------+----------------+
> | id | select_type | table | type | possible_keys | key |
> key_len | ref | rows | Extra |
> +----+---------------------+------------+--------+---------- -----+------+---
> ------+------+------+----------------+
> | 1 | PRIMARY | | system | NULL | NULL |
> NULL | NULL | 1 | |
> | 3 | DEPENDENT SUBSELECT | | system | NULL | NULL |
> NULL | NULL | 1 | Using where |
> | 4 | DERIVED | NULL | NULL | NULL | NULL |
> NULL | NULL | NULL | No tables used |
> | 2 | DERIVED | NULL | NULL | NULL | NULL |
> NULL | NULL | NULL | No tables used |
> +----+---------------------+------------+--------+---------- -----+------+---
> ------+------+------+----------------+
> 4 rows in set (0.00 sec)
>
> Or perhaps it's linked to the fact (SELECT 1 FROM (SELECT 1) c) is a
> candidate to be reduced during optimisation ?
> In this case what is the rule to be a candidate for optimisation ? :)

'a' is dependence, ('a' belongs to subquery in this case).

> If I change IN to =, there's no more DEPENDENT SUBSELECT :
>
> mysql> EXPLAIN SELECT a FROM (SELECT 1 as a) b WHERE a=(SELECT 1 FROM
> (SELECT 1) c);
> +----+-------------+------------+--------+---------------+-- ----+---------+-
> -----+------+----------------+
> | id | select_type | table | type | possible_keys | key | key_len |
> ref | rows | Extra |
> +----+-------------+------------+--------+---------------+-- ----+---------+-
> -----+------+----------------+
> | 1 | PRIMARY | | system | NULL | NULL | NULL |
> NULL | 1 | |
> | 3 | SUBSELECT | | system | NULL | NULL | NULL |
> NULL | 1 | |
> | 4 | DERIVED | NULL | NULL | NULL | NULL | NULL |
> NULL | NULL | No tables used |
> | 2 | DERIVED | NULL | NULL | NULL | NULL | NULL |
> NULL | NULL | No tables used |
> +----+-------------+------------+--------+---------------+-- ----+---------+-
> -----+------+----------------+
> 4 rows in set (0.00 sec)

There are difference between scalar and table subselect.
I think it is possible to test how many records in table if no condition
applied, but how many tables with 1 record you face in real life, and how
many queries without condition? And now which probability of facing both of
they in same query we have?

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

Re: SUBSELECT printed as DEPENDENT SUBSELECT with MySQL-4.1 ?

Hi,

I agree with you.
I was originally thinking about this kind of query :
SELECT field_list FROM table1 WHERE field IN (SELECT field2 FROM table2
WHERE condition_on_table2) ORDER BY order_by_cond LIMIT #num,#offset;

and I wasn't thinking about the fact that 'field' was belonging to the
subquery.

Thanks for the clarification !

Regards,
Jocelyn


----- Original Message -----
From: "Sanja Byelkin"
To: "Jocelyn Fournier"
Cc:
Sent: Sunday, December 15, 2002 6:10 PM
Subject: Re: SUBSELECT printed as DEPENDENT SUBSELECT with MySQL-4.1 ?


> On Sun, Dec 15, 2002 at 03:02:57PM -0000, Jocelyn Fournier wrote:
> > Hi,
> >
> > I'm wondering if the following is not a bug, since I don't see any
> > dependence in the subselect (unless I missed something) :
> >
> > How-to-repeat :
> >
> > mysql> EXPLAIN SELECT a FROM (SELECT 1 as a) b WHERE a IN (SELECT 1 FROM
> > (SELECT 1) c);
> >
+----+---------------------+------------+--------+---------- -----+------+---
> > ------+------+------+----------------+
> > | id | select_type | table | type | possible_keys | key
|
> > key_len | ref | rows | Extra |
> >
+----+---------------------+------------+--------+---------- -----+------+---
> > ------+------+------+----------------+
> > | 1 | PRIMARY | | system | NULL | NULL
|
> > NULL | NULL | 1 | |
> > | 3 | DEPENDENT SUBSELECT | | system | NULL | NULL
|
> > NULL | NULL | 1 | Using where |
> > | 4 | DERIVED | NULL | NULL | NULL | NULL
|
> > NULL | NULL | NULL | No tables used |
> > | 2 | DERIVED | NULL | NULL | NULL | NULL
|
> > NULL | NULL | NULL | No tables used |
> >
+----+---------------------+------------+--------+---------- -----+------+---
> > ------+------+------+----------------+
> > 4 rows in set (0.00 sec)
> >
> > Or perhaps it's linked to the fact (SELECT 1 FROM (SELECT 1) c) is a
> > candidate to be reduced during optimisation ?
> > In this case what is the rule to be a candidate for optimisation ? :)
>
> 'a' is dependence, ('a' belongs to subquery in this case).
>
> > If I change IN to =, there's no more DEPENDENT SUBSELECT :
> >
> > mysql> EXPLAIN SELECT a FROM (SELECT 1 as a) b WHERE a=(SELECT 1 FROM
> > (SELECT 1) c);
> >
+----+-------------+------------+--------+---------------+-- ----+---------+-
> > -----+------+----------------+
> > | id | select_type | table | type | possible_keys | key |
key_len |
> > ref | rows | Extra |
> >
+----+-------------+------------+--------+---------------+-- ----+---------+-
> > -----+------+----------------+
> > | 1 | PRIMARY | | system | NULL | NULL |
NULL |
> > NULL | 1 | |
> > | 3 | SUBSELECT | | system | NULL | NULL |
NULL |
> > NULL | 1 | |
> > | 4 | DERIVED | NULL | NULL | NULL | NULL |
NULL |
> > NULL | NULL | No tables used |
> > | 2 | DERIVED | NULL | NULL | NULL | NULL |
NULL |
> > NULL | NULL | No tables used |
> >
+----+-------------+------------+--------+---------------+-- ----+---------+-
> > -----+------+----------------+
> > 4 rows in set (0.00 sec)
>
> There are difference between scalar and table subselect.
> I think it is possible to test how many records in table if no condition
> applied, but how many tables with 1 record you face in real life, and how
> many queries without condition? And now which probability of facing both
of
> they in same query we have?
>
> --
> 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-thread13291@lists.mysql.com
> To unsubscribe, e-mail
>
>
>
>


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

Re: SUBSELECT printed as DEPENDENT SUBSELECT with MySQL-4.1 ?

Hi!

On Sun, Dec 15, 2002 at 06:31:06PM -0000, Jocelyn Fournier wrote:
[skip]
> I agree with you.
> I was originally thinking about this kind of query :
> SELECT field_list FROM table1 WHERE field IN (SELECT field2 FROM table2
> WHERE condition_on_table2) ORDER BY order_by_cond LIMIT #num,#offset;
>
> and I wasn't thinking about the fact that 'field' was belonging to the
> subquery.
>
> Thanks for the clarification !

Strictly speaking it do not belong to query. It was my incorrect phrase, but
subquery depend of it (in current implementation). May be in future we made
optimization with using of temporary table, but now this query will be
rewritten with using exists-like (it can return NULL) subquery, in which will
be used references to field 'field', consequently subquery is depended.

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