Bug #13851 Make selects possible where columns are found

• Submitted: 7 Oct 2005 18:32
• Modified: 15 Dec 2010 11:05
• Reporter: Andre Timmer
• Status: Can't repeat
• Category: MySQL Server
• Severity: S4 (Feature request)
• Version: MySQL 5
• OS: Any (any)
• CPU Architecture: Any

[7 Oct 2005 18:32] Andre Timmer

Description:
It would be nice to have:

select
        col1
,       col2
,       (subselect, correlating and non correlating)
from  ...

How to repeat:
.

Suggested fix:
Examples for inspiration:

select
        col1
,       col2
,       (select max(id) from other_tab)
from  tab

select
        col1
,       col2
,       (select count(*) from other_tab aa where aa.col1 = bb.col1)
from  tab bb

select
        col1
,       col2
from  tab aa
,       tab bb
where
       ..join..
and  col1 = (select max(id) from other_tab)

[15 Dec 2010 11:05] Valeriy Kravchuk

Queries like that works in 5.1.x at least. Look:

mysql> select 1, (select 2 from mysql.user limit 1) as a;
+---+------+
| 1 | a    |
+---+------+
| 1 |    2 |
+---+------+
1 row in set (0.03 sec)

mysql> select 1, (select max(host) from mysql.user limit 1) as a;
+---+-----------+
| 1 | a         |
+---+-----------+
| 1 | localhost |
+---+-----------+
1 row in set (0.02 sec)

mysql> select version();
+------------------+
| version()        |
+------------------+
| 5.1.53-community |
+------------------+
1 row in set (0.01 sec)

mysql> select 1, (select max(host) from mysql.user where host=u.host) as a from
mysql.user u;
+---+-----------+
| 1 | a         |
+---+-----------+
| 1 | %         |
| 1 | %         |
| 1 | localhost |
| 1 | localhost |
| 1 | localhost |
| 1 | localhost |
+---+-----------+
6 rows in set (0.03 sec)

mysql> select user, host from mysql.user where host= (select max(host) from mysq
l.user);
+-----------+-----------+
| user      | host      |
+-----------+-----------+
| myuser    | localhost |
| root      | localhost |
| trig_user | localhost |
| usert     | localhost |
+-----------+-----------+
4 rows in set (0.00 sec)