subselect question... shouldn't this work?

[Ben Margolin]

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...)

Any comments appreciated.

Ben Margolin
=====
[ Ben Margolin -- be*********@yahoo.com -- be*@prince.org ]

__________________________________
Do you Yahoo!?
SBC Yahoo! DSL - Now only $29.95 per month!
http://sbc.yahoo.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/my***********...ie.nctu.edu.tw