Ben Margolin <[EMAIL PROTECTED]> wrote:
>
> I am new to subselects, and what I really want is to do them in an update,
> but the following shows a simpler select, that also doesn't seem to work as I
> think it should. Advice? Do I just misunderstand how subselects are actually
> executed?
>
> (This is on mysql version 4.1.0-alpha-max-nt.)
>
> First, here's the tables in question:
>
> mysql> describe m;
> +-------+---------+-----------+------+-----+---------+-------+
> | Field | Type | Collation | Null | Key | Default | Extra |
> +-------+---------+-----------+------+-----+---------+-------+
> | toid | int(11) | binary | YES | | NULL | |
> | rd | int(11) | binary | YES | | NULL | |
> +-------+---------+-----------+------+-----+---------+-------+
>
> mysql> describe p;
> +---------+---------+-----------+------+-----+---------+-------+
> | Field | Type | Collation | Null | Key | Default | Extra |
> +---------+---------+-----------+------+-----+---------+-------+
> | userid | int(11) | binary | | PRI | 0 | |
> | pmnew | int(11) | binary | YES | | NULL | |
> | pmtotal | int(11) | binary | YES | | NULL | |
> +---------+---------+-----------+------+-----+---------+-------+
>
> and the data in the tables...
>
> mysql> select * from p;
> +--------+-------+---------+
> | userid | pmnew | pmtotal |
> +--------+-------+---------+
> | 1 | 0 | 0 |
> | 2 | 0 | 0 |
> +--------+-------+---------+
> 2 rows in set (0.00 sec)
>
> mysql> select * from m;
> +------+-------+
> | toid | rd |
> +------+-------+
> | 1 | 0 |
> | 1 | 0 |
> | 1 | 0 |
> | 1 | 12 |
> | 1 | 15 |
> | 1 | 123 |
> | 1 | 12312 |
> | 1 | 12312 |
> | 1 | 123 |
> | 2 | 0 |
> | 2 | 0 |
> | 2 | 1 |
> | 2 | 2 |
> +------+-------+
> 13 rows in set (0.00 sec)
>
> mysql> select userid,pmtotal,pmnew, (select count(rd) from m where
> toid=p.userid) calc_total, (select count(rd) from m where rd=0 and
> toid=p.userid) calc_new from p where userid in (select distinct toid from m);
> +--------+---------+-------+------------+----------+
> | userid | pmtotal | pmnew | calc_total | calc_new |
> +--------+---------+-------+------------+----------+
> | 1 | 0 | 0 | 9 | 3 |
> | 2 | 0 | 0 | NULL | NULL |
> +--------+---------+-------+------------+----------+
> Now, the first row has what I want and expect, in calc_total and calc_new...
> but the second row doesn't. Why? Shouldn't the subselects in the field
> selector part (not the where part) be "re-executed" for each value in the
> IN()? If so, I'd expect calc_total to be '4', and calc_new to be '2' for the
> second row. For example, if I manually fudge the WHERE ... IN, I get:
>
> mysql> select userid,pmtotal,pmnew, (select count(rd) from m where
> toid=p.userid) calc_total, (select count(rd) from m where rd=0 and
> toid=p.userid) calc_new from p where userid in (2);
> +--------+---------+-------+------------+----------+
> | userid | pmtotal | pmnew | calc_total | calc_new |
> +--------+---------+-------+------------+----------+
> | 2 | 0 | 0 | 4 | 2 |
> +--------+---------+-------+------------+----------+
>
> which is exactly what I want, but all at once :-)
>
> Ideas? Misunderstanding on my part? Bug?
>
> (By the way, what I eventually want to do is an update to set pmtotal and
> pmnew to be the calc_total and calc_new; in the real schema this is a
> simplified version of, they are essentially 'caches' of the new/total
> counts...)
Thanks for the report. I added your report to the MySQL bug database:
http://bugs.mysql.com/bug.php?id=860
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Victoria Reznichenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
<___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
