MySQL Bugs: #5421: Subquery

Bug #5421	Subquery
Submitted:	5 Sep 2004 18:34	Modified:	5 Sep 2004 19:05
Reporter:	Bjoern Katla	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1	OS:	Windows (windows)
Assigned to:		CPU Architecture:	Any

Description:
When trying to run a subquery like:

select a, (select sum(b) from T2 where T2.x = T1.x)  as 'n'  from T1

one gets an error.  

Checking the documentation it seems that this syntax not seems to be supported.  

To my experience this is a rather common way to use subqueries. Perhaps more common than the ones you have implemented so far for sub-queries. 

Are there any plans to implement this? 

(and Yes - I know that I can write this simple query another way but in many cases this syntax is very nice to have and easy to use.   
(a) one does not have to list loads of columns in a group by clause and 
b) the need for complex joins are simpler when the number of subqueries increase..) 

How to repeat:
select a, (select sum(b) from T2 where T2.x = T1.x)  as 'n'  from T1

can't repeat - tested against 4.1.4 version: 
 
mysql> CREATE TABLE t1 (a int, x int); 
Query OK, 0 rows affected (0.17 sec) 
 
mysql> INSERT INTO t1 VALUES (1,1),(2,2),(3,1); 
Query OK, 3 rows affected (0.01 sec) 
Records: 3  Duplicates: 0  Warnings: 0 
 
mysql> CREATE TABLE t2 (b int, x int); 
Query OK, 0 rows affected (0.05 sec) 
 
mysql> INSERT INTO t2 VALUES (1,2),(3,1),(2,2); 
Query OK, 3 rows affected (0.00 sec) 
Records: 3  Duplicates: 0  Warnings: 0 
 
mysql> SELECT a, (SELECT sum(b) FROM t2 WHERE t2.x=t1.x) FROM t1; 
+------+-----------------------------------------+ 
| a    | (SELECT sum(b) FROM t2 WHERE t2.x=t1.x) | 
+------+-----------------------------------------+ 
|    1 |                                       3 | 
|    2 |                                       3 | 
|    3 |                                       3 | 
+------+-----------------------------------------+

Seems the upgrade I just did from 4.0.x to 4.1.. gamma did not go through well.  

When I finally made the upgrade, the subquery works FINE!.  Superb!!

You should however consider upgrading your documentation with regard to upgrading (at least from 4.0 to 4.1) .  

*It seems that doing an upgrade with the c:\MYSQL directory intact does not work like described in the chapters describing upgrading from 4.0 to 4.1.  
There is vital information in chapter 2.2.1.2 page 80 with regard to dropping the mysql directory before attempting a reinstall (and copying the DATA directory).  (The info in chapter 2.5.2 that points to chap 2.2.1.2 seems to be related only to alpga 4.1 installs that lacked an installer) 

Running the install program while there is a C:\mysql directory does not upgrade properly (this is however the "recommended" way which a mysql-novice like myself follows.. ).   
  
*There are also some erroneous info with regard to mysqld-opt which no longer seems to be in the bin directory and when it was installed as a service it took some time to find out WHY it did not start when net start mysql was issued.  I guess this also has to do with my mysql-novice status.