Finding all subqueries in sql using regex-CodePudding

Following regex \(\s*select([^()]*)\) giving all subqueries but it ignores when subquery contains any sql functions like max(), count(), ....etc regex works for below

SELECT student.name, teacher.name, sub.subject_name
FROM student student
JOIN teacher teacher on teacher.id=student.teacher_id
JOIN (select subject.name subject_name, subject.id id from subject) sub on sub.id=teacher.subject_id 
where student.id=1

it finds subquery as select subject.name subject_name, subject.id id from subject but not for

SELECT student.name, teacher.name, sub.subject_name
FROM student student
JOIN teacher teacher on teacher.id=student.teacher_id
JOIN (select group_concat(subject.name) subject_names from subject)
where student.id=1

it doesn't find match as select group_concat(subject.name) subject_names from subject how can I get match...?

CodePudding user response：

You can try regex /\((?:[^()]|\((?:[^()]|\([^()]*\))*\))*\)/mg;

CodePudding user response：

Challenge accepted. See if this leads to a viable solution:

mysql> explain select (select 'a' from t) x,
                      (select 'b' from t) y,
                      "select 'c'" z
                    from t;
 ---- ------------- ------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ------- 
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
 ---- ------------- ------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ------- 
|  1 | PRIMARY     | t     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4243 |   100.00 | NULL  |
|  3 | SUBQUERY    | t     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4243 |   100.00 | NULL  |
|  2 | SUBQUERY    | t     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4243 |   100.00 | NULL  |
 ---- ------------- ------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ------- 
3 rows in set, 1 warning (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select 
        (/* select#2 */ select 'a' from `covid`.`t`) AS `x`,
        (/* select#3 */ select 'b' from `covid`.`t`) AS `y`,
        'select \'c\'' AS `z`
     from `db`.`t`
1 row in set (0.00 sec)

Then parse the EXPLAIN by using the /../ comments and balancing parens.

(I edited whitespace for effect.)