PL/SQL: subquery_operation_part in atom

[n9]

PL/SQL atom currently allows subquery to be followed by subquery_operation_part after ):

https://github.com/antlr/grammars-v4/blob/3f8074f7730c272ddfd2d77df0ae83b46422fd98/sql/plsql/PlSqlParser.g4#L6509

Is this correct? If so, where is it documented?

This "addition" comes from this commit: https://github.com/porcelli/plsql-parser/commit/1b9a348df07c05cbb4927b928b7b0d522e19e5e1. And the test file included in that commit passes the current grammar without the "extra" subquery_operation_part.

[kaby76]

Yes, that is also a source of ambiguity. For union10.sql, the ambiguity trees are:

subquery.sql.d=2441.a=5: (sql_script (unit_statement (data_manipulation_language_statements (select_statement (select_only_statement (subquery (subquery_basic_elements (query_block (SELECT "select") (selected_list (select_list_elements (expression (logical_expression (unary_logical_expression (multiset_expression (relational_expression (compound_expression (concatenation (model_expression (unary_expression (atom (LEFT_PAREN "(") (subquery (subquery_basic_elements (LEFT_PAREN "(") (subquery (subquery_basic_elements (query_block (SELECT "select") (selected_list (select_list_elements (expression (logical_expression (unary_logical_expression (multiset_expression (relational_expression (compound_expression (concatenation (model_expression (unary_expression (atom (constant (quoted_string (CHAR_STRING "'y'"))))))))))))))) (from_clause (FROM "from") (table_ref_list (table_ref (table_ref_aux (table_ref_aux_internal (dml_table_expression_clause (tableview_name (identifier (id_expression (regular_id (REGULAR_ID "dual"))))))))))) (where_clause (WHERE "where") (condition (expression (logical_expression (unary_logical_expression (multiset_expression (relational_expression (compound_expression (concatenation (model_expression (unary_expression (quantified_expression (EXISTS "exists") (LEFT_PAREN "(") (select_only_statement (subquery (subquery_basic_elements (query_block (SELECT "select") (selected_list (select_list_elements (expression (logical_expression (unary_logical_expression (multiset_expression (relational_expression (compound_expression (concatenation (model_expression (unary_expression (atom (constant (numeric (UNSIGNED_INTEGER "1"))))))))))))))) (from_clause (FROM "from") (table_ref_list (table_ref (table_ref_aux (table_ref_aux_internal (dml_table_expression_clause (tableview_name (identifier (id_expression (regular_id (REGULAR_ID "dual"))))))))))) (where_clause (WHERE "where") (condition (expression (logical_expression (unary_logical_expression (multiset_expression (relational_expression (relational_expression (compound_expression (concatenation (model_expression (unary_expression (atom (constant (numeric (UNSIGNED_INTEGER "1"))))))))) (relational_operator (EQUALS_OP "=")) (relational_expression (compound_expression (concatenation (model_expression (unary_expression (atom (constant (numeric (UNSIGNED_INTEGER "0")))))))))))))))))))) (RIGHT_PAREN ")")))))))))))))))) (RIGHT_PAREN ")")) (subquery_operation_part (UNION "union") (subquery_basic_elements (LEFT_PAREN "(") (subquery (subquery_basic_elements (query_block (SELECT "select") (selected_list (select_list_elements (expression (logical_expression (unary_logical_expression (multiset_expression (relational_expression (compound_expression (concatenation (model_expression (unary_expression (atom (constant (quoted_string (CHAR_STRING "'n'"))))))))))))))) (from_clause (FROM "from") (table_ref_list (table_ref (table_ref_aux (table_ref_aux_internal (dml_table_expression_clause (tableview_name (identifier (id_expression (regular_id (REGULAR_ID "dual"))))))))))) (where_clause (WHERE "where") (condition (expression (logical_expression (unary_logical_expression (NOT "not") (multiset_expression (relational_expression (compound_expression (concatenation (model_expression (unary_expression (quantified_expression (EXISTS "exists") (LEFT_PAREN "(") (select_only_statement (subquery (subquery_basic_elements (query_block (SELECT "select") (selected_list (select_list_elements (expression (logical_expression (unary_logical_expression (multiset_expression (relational_expression (compound_expression (concatenation (model_expression (unary_expression (atom (constant (numeric (UNSIGNED_INTEGER "1"))))))))))))))) (from_clause (FROM "from") (table_ref_list (table_ref (table_ref_aux (table_ref_aux_internal (dml_table_expression_clause (tableview_name (identifier (id_expression (regular_id (REGULAR_ID "dual"))))))))))) (where_clause (WHERE "where") (condition (expression (logical_expression (unary_logical_expression (multiset_expression (relational_expression (relational_expression (compound_expression (concatenation (model_expression (unary_expression (atom (constant (numeric (UNSIGNED_INTEGER "1"))))))))) (relational_operator (EQUALS_OP "=")) (relational_expression (compound_expression (concatenation (model_expression (unary_expression (atom (constant (numeric (UNSIGNED_INTEGER "0")))))))))))))))))))) (RIGHT_PAREN ")")))))))))))))))) (RIGHT_PAREN ")")))) (RIGHT_PAREN ")"))))))))))) (column_alias (AS "as") (identifier (id_expression (regular_id (REGULAR_ID "yes_no"))))))) (from_clause (FROM "from") (table_ref_list (table_ref (table_ref_aux (table_ref_aux_internal (dml_table_expression_clause (tableview_name (identifier (id_expression (regular_id (REGULAR_ID "dual")))))))))))))))))) (EOF ""))
subquery.sql.d=2441.a=6: (sql_script (unit_statement (data_manipulation_language_statements (select_statement (select_only_statement (subquery (subquery_basic_elements (query_block (SELECT "select") (selected_list (select_list_elements (expression (logical_expression (unary_logical_expression (multiset_expression (relational_expression (compound_expression (concatenation (model_expression (unary_expression (atom (LEFT_PAREN "(") (expressions (expression (logical_expression (unary_logical_expression (multiset_expression (relational_expression (compound_expression (concatenation (model_expression (unary_expression (atom (LEFT_PAREN "(") (subquery (subquery_basic_elements (query_block (SELECT "select") (selected_list (select_list_elements (expression (logical_expression (unary_logical_expression (multiset_expression (relational_expression (compound_expression (concatenation (model_expression (unary_expression (atom (constant (quoted_string (CHAR_STRING "'y'"))))))))))))))) (from_clause (FROM "from") (table_ref_list (table_ref (table_ref_aux (table_ref_aux_internal (dml_table_expression_clause (tableview_name (identifier (id_expression (regular_id (REGULAR_ID "dual"))))))))))) (where_clause (WHERE "where") (condition (expression (logical_expression (unary_logical_expression (multiset_expression (relational_expression (compound_expression (concatenation (model_expression (unary_expression (quantified_expression (EXISTS "exists") (LEFT_PAREN "(") (select_only_statement (subquery (subquery_basic_elements (query_block (SELECT "select") (selected_list (select_list_elements (expression (logical_expression (unary_logical_expression (multiset_expression (relational_expression (compound_expression (concatenation (model_expression (unary_expression (atom (constant (numeric (UNSIGNED_INTEGER "1"))))))))))))))) (from_clause (FROM "from") (table_ref_list (table_ref (table_ref_aux (table_ref_aux_internal (dml_table_expression_clause (tableview_name (identifier (id_expression (regular_id (REGULAR_ID "dual"))))))))))) (where_clause (WHERE "where") (condition (expression (logical_expression (unary_logical_expression (multiset_expression (relational_expression (relational_expression (compound_expression (concatenation (model_expression (unary_expression (atom (constant (numeric (UNSIGNED_INTEGER "1"))))))))) (relational_operator (EQUALS_OP "=")) (relational_expression (compound_expression (concatenation (model_expression (unary_expression (atom (constant (numeric (UNSIGNED_INTEGER "0")))))))))))))))))))) (RIGHT_PAREN ")")))))))))))))))) (RIGHT_PAREN ")") (subquery_operation_part (UNION "union") (subquery_basic_elements (LEFT_PAREN "(") (subquery (subquery_basic_elements (query_block (SELECT "select") (selected_list (select_list_elements (expression (logical_expression (unary_logical_expression (multiset_expression (relational_expression (compound_expression (concatenation (model_expression (unary_expression (atom (constant (quoted_string (CHAR_STRING "'n'"))))))))))))))) (from_clause (FROM "from") (table_ref_list (table_ref (table_ref_aux (table_ref_aux_internal (dml_table_expression_clause (tableview_name (identifier (id_expression (regular_id (REGULAR_ID "dual"))))))))))) (where_clause (WHERE "where") (condition (expression (logical_expression (unary_logical_expression (NOT "not") (multiset_expression (relational_expression (compound_expression (concatenation (model_expression (unary_expression (quantified_expression (EXISTS "exists") (LEFT_PAREN "(") (select_only_statement (subquery (subquery_basic_elements (query_block (SELECT "select") (selected_list (select_list_elements (expression (logical_expression (unary_logical_expression (multiset_expression (relational_expression (compound_expression (concatenation (model_expression (unary_expression (atom (constant (numeric (UNSIGNED_INTEGER "1"))))))))))))))) (from_clause (FROM "from") (table_ref_list (table_ref (table_ref_aux (table_ref_aux_internal (dml_table_expression_clause (tableview_name (identifier (id_expression (regular_id (REGULAR_ID "dual"))))))))))) (where_clause (WHERE "where") (condition (expression (logical_expression (unary_logical_expression (multiset_expression (relational_expression (relational_expression (compound_expression (concatenation (model_expression (unary_expression (atom (constant (numeric (UNSIGNED_INTEGER "1"))))))))) (relational_operator (EQUALS_OP "=")) (relational_expression (compound_expression (concatenation (model_expression (unary_expression (atom (constant (numeric (UNSIGNED_INTEGER "0")))))))))))))))))))) (RIGHT_PAREN ")")))))))))))))))) (RIGHT_PAREN ")")))))))))))))) (RIGHT_PAREN ")"))))))))))) (column_alias (AS "as") (identifier (id_expression (regular_id (REGULAR_ID "yes_no"))))))) (from_clause (FROM "from") (table_ref_list (table_ref (table_ref_aux (table_ref_aux_internal (dml_table_expression_clause (tableview_name (identifier (id_expression (regular_id (REGULAR_ID "dual")))))))))))))))))) (EOF ""))

Without the "extra" subquery_operation_part*, there's no ambiguity for the test case. And the entire test suite passes. With the change, performance improves as well. With the change, the test suite takes ~20s as opposed to ~24s with the "extra".