Question 21300 in Mysql Question posted in
The official MySQL documentation can be found here.

Finding all subqueries in sql using regex – Mysql

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…?

Tags:

2

Answers


  1. 0

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

    regex_subquery

    Login or Signup to reply.
  2. 0

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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search