Support `UNNEST` as a table factor #493

sivchari · 2022-05-10T13:26:32Z

This PR adds support UNNEST for BigQuery.

from_item:
    {
      table_name [ as_alias ] [ FOR SYSTEM_TIME AS OF timestamp_expression ]
      | { join_operation | ( join_operation ) }
      | ( query_expr ) [ as_alias ]
      | field_path
      | unnest_operator
      | cte_name [ as_alias ]
    }

unnest_operator:
    {
      [UNNEST](https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#unnest)( array_expression )
      | UNNEST( array_path )
      | array_path
    }
    [ as_alias ]
    [ WITH OFFSET [ as_alias ] ]

https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#unnest_operator

alamb

Thank you @sivchari -- sorry for the delay in review

I think this is a nice follow on from the IN UNNEST added in #426 by @komukomo

I wonder if they could be unified somehow 🤔

alamb · 2022-05-10T14:27:13Z

src/parser.rs

@@ -3545,6 +3546,27 @@ impl<'a> Parser<'a> {
                // appearing alone in parentheses (e.g. `FROM (mytable)`)
                self.expected("joined table", self.peek_token())
            }
+        } else if self.parse_keyword(Keyword::UNNEST) {


Can we please conditionalize parsing for only GenericDialect and BigQueryDialect (added in #490)?

alamb · 2022-05-10T14:28:31Z

tests/sqlparser_common.rs

@@ -2706,6 +2706,26 @@ fn parse_table_function() {
    );
 }

+#[test]
+fn parse_unnest() {


can we also please add some tests for the following cases:

no alias

no WITH OFFSET clause

neither alias nor WITH OFFSET clause

Thanks!

coveralls · 2022-05-22T19:28:32Z

Pull Request Test Coverage Report for Build 2390833123

52 of 57 (91.23%) changed or added relevant lines in 3 files are covered.
500 unchanged lines in 6 files lost coverage.
Overall coverage decreased (-0.1%) to 89.69%

Changes Missing Coverage	Covered Lines	Changed/Added Lines	%
tests/sqlparser_common.rs	33	34	97.06%
src/ast/query.rs	9	11	81.82%
src/parser.rs	10	12	83.33%

Files with Coverage Reduction	New Missed Lines	%
tests/sqlparser_postgres.rs	1	97.71%
src/ast/query.rs	12	86.44%
src/ast/value.rs	13	83.33%
tests/sqlparser_common.rs	61	97.06%
src/tokenizer.rs	76	88.97%
src/parser.rs	337	83.1%

Totals
Change from base Build 2378120823:	-0.1%
Covered Lines:	8473
Relevant Lines:	9447

💛 - Coveralls

I updated condition. This changes conditionalize parsing for only BigQueryDialect or GenericDialect.

sivchari · 2022-05-24T17:33:20Z

Hi, @alamb.
I fixed parsing condition and add some tests. Thanks.

alamb

Looks good -- thanks @sivchari

alamb · 2022-05-25T20:05:22Z

Hi @sivchari the tests appear to be failing on CI

sivchari · 2022-05-26T13:11:13Z

Hi @alamb . Sorry, I fixed it :)

sivchari · 2022-05-26T13:11:52Z

$ cargo test --all-features                                                                                                                                                                                                                      1812ms  Thu May 26 22:10:13 2022
   Compiling sqlparser v0.17.0 (/Users/sivchari/workspace/sqlparser-rs)
    Finished test [unoptimized + debuginfo] target(s) in 4.31s
     Running unittests (target/debug/deps/sqlparser-0441e57738596040)

running 33 tests
test ast::tests::test_window_frame_default ... ok
test ast::tests::test_grouping_sets_display ... ok
test ast::tests::test_cube_display ... ok
test tokenizer::tests::tokenize_bitwise_op ... ok
test ast::tests::test_rollup_display ... ok
test parser::tests::test_prev_index ... ok
test tokenizer::tests::tokenize_comment ... ok
test tokenizer::tests::tokenize_comment_at_eof ... ok
test tokenizer::tests::tokenize_invalid_string ... ok
test tokenizer::tests::tokenize_explain_select ... ok
test dialect::tests::test_is_dialect ... ok
test tokenizer::tests::tokenize_explain_analyze_select ... ok
test tokenizer::tests::tokenize_invalid_string_cols ... ok
test tokenizer::tests::tokenize_is_null ... ok
test tokenizer::tests::tokenize_mismatched_quotes ... ok
test tokenizer::tests::tokenize_mssql_top ... ok
test tokenizer::tests::tokenize_multiline_comment ... ok
test tokenizer::tests::tokenize_multiline_comment_with_even_asterisks ... ok
test tokenizer::tests::tokenize_newline_in_string_literal ... ok
test tokenizer::tests::tokenize_logical_xor ... ok
test tokenizer::tests::tokenize_newlines ... ok
test tokenizer::tests::tokenize_pg_regex_match ... ok
test tokenizer::tests::tokenize_quoted_identifier ... ok
test tokenizer::tests::tokenize_scalar_function ... ok
test tokenizer::tests::tokenize_right_arrow ... ok
test tokenizer::tests::tokenize_select_1 ... ok
test tokenizer::tests::tokenize_select_float ... ok
test tokenizer::tests::tokenize_string_string_concat ... ok
test tokenizer::tests::tokenize_simple_select ... ok
test tokenizer::tests::tokenize_string_predicate ... ok
test tokenizer::tests::tokenize_unicode_whitespace ... ok
test tokenizer::tests::tokenize_unterminated_string_literal ... ok
test tokenizer::tests::tokenizer_error_impl ... ok

test result: ok. 33 passed; 0 failed; 0 ignored; 0 measured; 0 filtered out; finished in 0.00s

     Running tests/sqlparser_bigquery.rs (target/debug/deps/sqlparser_bigquery-5447e66c9e7cb53d)

running 1 test
test parse_table_identifiers ... ok

test result: ok. 1 passed; 0 failed; 0 ignored; 0 measured; 0 filtered out; finished in 0.00s

     Running tests/sqlparser_common.rs (target/debug/deps/sqlparser_common-2cee36b23b08cc1c)

running 170 tests
test all_keywords_sorted ... ok
test ensure_multiple_dialects_are_tested - should panic ... ok
test parse_alter_table_alter_column_type ... ok
test parse_aggregate_with_group_by ... ok
test parse_alter_table_drop_constraint ... ok
test parse_assert ... ok
test parse_alter_table_drop_column ... ok
test parse_assert_message ... ok
test parse_alter_table ... ok
test parse_bad_constraint ... ok
test parse_between ... ok
test parse_binary_all ... ok
test lateral_derived ... ok
test parse_between_with_expr ... ok
test parse_binary_any ... ok
test parse_alter_table_alter_column ... ok
test parse_bitwise_ops ... ok
test parse_collate ... ok
test parse_column_aliases ... ok
test parse_commit ... ok
test parse_create_database ... ok
test parse_count_wildcard ... ok
test parse_compound_expr_1 ... ok
test parse_create_database_ine ... ok
test parse_compound_expr_2 ... ok
test parse_complex_join ... ok
test parse_create_index ... ok
test parse_alter_table_constraints ... ok
test parse_create_or_replace_materialized_view ... ok
test parse_create_materialized_view ... ok
test parse_create_external_table ... ok
test parse_create_schema ... ok
test parse_create_external_table_lowercase ... ok
test parse_create_or_replace_view ... ok
test parse_create_or_replace_external_table ... ok
test parse_create_table_trailing_comma ... ok
test parse_create_table_hive_array ... ok
test parse_cast ... ok
test parse_create_table_as ... ok
test parse_create_table_with_multiple_on_delete_in_constraint_fails ... ok
test parse_create_or_replace_table ... ok
test parse_create_view_with_columns ... ok
test parse_create_table_with_multiple_on_delete_fails ... ok
test parse_create_view ... ok
test parse_create_table_with_on_delete_on_update_2in_any_order ... ok
test parse_create_table_with_options ... ok
test parse_cross_join ... ok
test parse_create_view_with_options ... ok
test parse_delete_statement ... ok
test parse_cte_renamed_columns ... ok
test parse_drop_index ... ok
test parse_delimited_identifiers ... ok
test parse_drop_schema ... ok
test parse_drop_table ... ok
test parse_discard ... ok
test parse_create_table ... ok
test parse_drop_view ... ok
test parse_derived_tables ... ok
test parse_explain_table ... ok
test parse_escaped_single_quote_string_predicate ... ok
test parse_ctes ... ok
test parse_from_advanced ... ok
test parse_explain_analyze_with_simple_select ... ok
test parse_exists_subquery ... ok
test parse_ilike ... ok
test parse_in_error ... ok
test parse_implicit_join ... ok
test parse_grant ... ok
test parse_fetch_variations ... ok
test parse_insert_sqlite ... ok
test parse_in_list ... ok
test parse_in_subquery ... ok
test parse_invalid_infix_not ... ok
test parse_invalid_subquery_without_parens ... ok
test parse_invalid_table_name ... ok
test parse_extract ... ok
test parse_in_unnest ... ok
test parse_is_not_distinct_from ... ok
test parse_is_not_null ... ok
test parse_is_null ... ok
test parse_is_distinct_from ... ok
test parse_is_boolean ... ok
test parse_fetch ... ok
test parse_like ... ok
test parse_insert_values ... ok
test parse_join_nesting ... ok
test parse_limit_accepts_all ... ok
test parse_limit_my_sql_syntax ... ok
test parse_literal_date ... ok
test parse_limit_is_not_an_alias ... ok
test parse_join_syntax_variants ... ok
test parse_literal_decimal ... ok
test parse_literal_string ... ok
test parse_literal_timestamp ... ok
test parse_literal_time ... ok
test parse_joins_using ... ok
test parse_joins_on ... ok
test parse_listagg ... ok
test parse_logical_xor ... ok
test parse_no_table_name ... ok
test parse_named_argument_function ... ok
test parse_null_in_select ... ok
test parse_number ... ok
test parse_not ... ok
test parse_not_precedence ... ok
test parse_natural_join ... ok
test parse_position ... ok
test parse_parens ... ok
test parse_projection_nested_type ... ok
test parse_position_negative ... ok
test parse_offset_and_limit ... ok
test parse_offset ... ok
test parse_scalar_function_in_projection ... ok
test parse_recursive_cte ... ok
test parse_rollback ... ok
test parse_literal_interval ... ok
test parse_scalar_subqueries ... ok
test parse_select_all_distinct ... ok
test parse_searched_case_expr ... ok
test parse_merge ... ok
test parse_select_distinct ... ok
test parse_select_all ... ok
test parse_select_count_wildcard ... ok
test parse_select_count_distinct ... ok
test parse_select_distinct_missing_paren ... ok
test parse_select_distinct_tuple ... ok
test parse_select_distinct_two_fields ... ok
test parse_select_group_by_grouping_sets ... ok
test parse_select_group_by_rollup ... ok
test parse_select_group_by_cube ... ok
test parse_select_group_by ... ok
test parse_select_order_by_limit ... ok
test parse_select_having ... ok
test parse_select_qualify ... ok
test parse_select_into ... ok
test parse_select_order_by_nulls_order ... ok
test parse_set_transaction ... ok
test parse_select_string_predicate ... ok
test parse_select_with_date_column_name ... ok
test parse_simple_case_expr ... ok
test parse_select_wildcard ... ok
test parse_select_order_by ... ok
test parse_simple_math_expr_plus ... ok
test parse_multiple_statements ... ok
test parse_simple_math_expr_minus ... ok
test parse_simple_select ... ok
test parse_string_agg ... ok
test parse_table_function ... ok
test parse_substring ... ok
test parse_top_level ... ok
test parse_trim ... ok
test parse_time_functions ... ok
test parse_tuples ... ok
test parse_unary_math ... ok
test parse_tuple_invalid ... ok
test parse_start_transaction ... ok
test parse_try_cast ... ok
test parse_unnest ... ok
test parse_where_delete_statement ... ok
test parse_update_with_table_alias ... ok
test parse_update ... ok
test parse_values ... ok
test test_eof_after_as ... ok
test parse_window_functions ... ok
test test_no_infix_error ... ok
test test_lock ... ok
test test_merge_into_using_table ... ok
test test_revoke ... ok
test test_placeholder ... ok
test parse_union ... ok

test result: ok. 170 passed; 0 failed; 0 ignored; 0 measured; 0 filtered out; finished in 0.06s

     Running tests/sqlparser_hive.rs (target/debug/deps/sqlparser_hive-52c63cb92e40f970)

running 30 tests
test drop_table_purge ... ok
test create_table_like ... ok
test columns_after_partition ... ok
test create_local_directory ... ok
test from_cte ... ok
test lateral_view ... ok
test decimal_precision ... ok
test create_temp_table ... ok
test no_join_condition ... ok
test map_access ... ok
test parse_analyze ... ok
test parse_analyze_for_columns ... ok
test parse_msck ... ok
test parse_set ... ok
test parse_insert_overwrite ... ok
test long_numerics ... ok
test parse_with_cte ... ok
test rename_table ... ok
test sort_by ... ok
test set_statement_with_minus ... ok
test parse_table_create ... ok
test test_add_partition ... ok
test test_cluster_by ... ok
test test_alter_partition ... ok
test test_distribute_by ... ok
test test_drop_if_exists ... ok
test test_spaceship ... ok
test test_drop_partition ... ok
test test_truncate ... ok
test test_identifier ... ok

test result: ok. 30 passed; 0 failed; 0 ignored; 0 measured; 0 filtered out; finished in 0.00s

     Running tests/sqlparser_mssql.rs (target/debug/deps/sqlparser_mssql-d20b6aadb75ad26f)

running 10 tests
test parse_mssql_delimited_identifiers ... ok
test parse_mssql_top_paren ... ok
test parse_mssql_bin_literal ... ok
test parse_mssql_identifiers ... ok
test parse_mssql_top ... ok
test parse_mssql_single_quoted_aliases ... ok
test parse_mssql_top_percent ... ok
test parse_mssql_top_percent_with_ties ... ok
test parse_mssql_top_with_ties ... ok
test parse_mssql_apply_join ... ok

test result: ok. 10 passed; 0 failed; 0 ignored; 0 measured; 0 filtered out; finished in 0.00s

     Running tests/sqlparser_mysql.rs (target/debug/deps/sqlparser_mysql-09402a987d1b01fc)

running 18 tests
test parse_create_table_auto_increment ... ok
test parse_create_table_collate ... ok
test parse_create_table_set_enum ... ok
test parse_create_table_comment_character_set ... ok
test parse_create_table_engine_default_charset ... ok
test parse_create_table_with_minimum_display_width ... ok
test parse_create_table_unsigned ... ok
test parse_alter_table_change_column ... ok
test parse_escaped_string ... ok
test parse_quote_identifiers ... ok
test parse_quote_identifiers_2 ... ok
test parse_identifiers ... ok
test parse_kill ... ok
test parse_show_create ... ok
test parse_insert_with_on_duplicate_update ... ok
test parse_unterminated_escape ... ok
test parse_update_with_joins ... ok
test parse_show_columns ... ok

test result: ok. 18 passed; 0 failed; 0 ignored; 0 measured; 0 filtered out; finished in 0.00s

     Running tests/sqlparser_postgres.rs (target/debug/deps/sqlparser_postgres-742d6ae4ab9c06aa)

running 38 tests
test parse_copy_from_before_v9_0 ... ok
test parse_alter_table_alter_column ... ok
test parse_alter_table_constraints_rename ... ok
test parse_comments ... ok
test parse_copy_from ... ok
test parse_bad_if_not_exists ... ok
test parse_create_table_empty ... ok
test parse_copy_to ... ok
test parse_array_index_expr ... ok
test parse_copy_to_before_v9_0 ... ok
test parse_create_schema_if_not_exists ... ok
test parse_create_table_constraints_only ... ok
test parse_create_table_from_pg_dump ... ok
test parse_copy_from_stdin ... ok
test parse_create_table_if_not_exists ... ok
test parse_execute ... ok
test parse_local_and_global ... ok
test parse_deallocate ... ok
test parse_drop_schema_if_exists ... ok
test parse_pg_bitwise_binary_ops ... ok
test parse_pg_postfix_factorial ... ok
test parse_create_table_with_inherit ... ok
test parse_on_commit ... ok
test parse_pg_regex_match_ops ... ok
test parse_pg_unary_ops ... ok
test parse_quoted_identifier ... ok
test parse_set_role ... ok
test parse_show ... ok
test parse_prepare ... ok
test parse_create_table_with_defaults ... ok
test test_composite_value ... ok
test parse_update_set_from ... ok
test test_copy_from ... ok
test parse_set ... ok
test test_copy_to ... ok
test test_savepoint ... ok
test test_transaction_statement ... ok
test test_json ... ok

test result: ok. 38 passed; 0 failed; 0 ignored; 0 measured; 0 filtered out; finished in 0.01s

     Running tests/sqlparser_redshift.rs (target/debug/deps/sqlparser_redshift-15c76e47098e1921)

running 4 tests
test brackets_over_db_schema_table_name_with_whites_paces ... ok
test test_sharp ... ok
test test_square_brackets_over_db_schema_table_name ... ok
test test_double_quotes_over_db_schema_table_name ... ok

test result: ok. 4 passed; 0 failed; 0 ignored; 0 measured; 0 filtered out; finished in 0.00s

     Running tests/sqlparser_regression.rs (target/debug/deps/sqlparser_regression-f194480519530e78)

running 22 tests
test tpch_13 ... ok
test tpch_16 ... ok
test tpch_11 ... ok
test tpch_10 ... ok
test tpch_12 ... ok
test tpch_15 ... ok
test tpch_14 ... ok
test tpch_1 ... ok
test tpch_17 ... ok
test tpch_18 ... ok
test tpch_2 ... ok
test tpch_20 ... ok
test tpch_19 ... ok
test tpch_4 ... ok
test tpch_3 ... ok
test tpch_21 ... ok
test tpch_22 ... ok
test tpch_5 ... ok
test tpch_7 ... ok
test tpch_8 ... ok
test tpch_6 ... ok
test tpch_9 ... ok

test result: ok. 22 passed; 0 failed; 0 ignored; 0 measured; 0 filtered out; finished in 0.01s

     Running tests/sqlparser_snowflake.rs (target/debug/deps/sqlparser_snowflake-3c3044b791712d10)

running 5 tests
test test_snowflake_single_line_tokenize ... ok
test test_snowflake_create_table ... ok
test test_sf_derived_table_in_parenthesis ... ok
test test_single_table_in_parenthesis ... ok
test test_single_table_in_parenthesis_with_alias ... ok

test result: ok. 5 passed; 0 failed; 0 ignored; 0 measured; 0 filtered out; finished in 0.01s

     Running tests/sqlparser_sqlite.rs (target/debug/deps/sqlparser_sqlite-573c2f404e72ef32)

running 4 tests
test parse_create_table_without_rowid ... ok
test parse_create_table_auto_increment ... ok
test parse_create_sqlite_quote ... ok
test parse_create_virtual_table ... ok

test result: ok. 4 passed; 0 failed; 0 ignored; 0 measured; 0 filtered out; finished in 0.00s

     Running tests/sqpparser_clickhouse.rs (target/debug/deps/sqpparser_clickhouse-0fbaf7f32411a926)

running 3 tests
test parse_kill ... ok
test parse_array_expr ... ok
test parse_map_access_expr ... ok

test result: ok. 3 passed; 0 failed; 0 ignored; 0 measured; 0 filtered out; finished in 0.00s

   Doc-tests sqlparser

running 1 test
test src/lib.rs - (line 20) ... ok

test result: ok. 1 passed; 0 failed; 0 ignored; 0 measured; 0 filtered out; finished in 0.41s

alamb · 2022-05-27T10:27:54Z

Thanks again @sivchari !

alamb changed the title ~~Support unnest~~ Support unnest as a table factor May 22, 2022

alamb reviewed May 22, 2022

View reviewed changes

alamb changed the title ~~Support unnest as a table factor~~ Support UNNEST as a table factor May 22, 2022

sivchari added 5 commits May 25, 2022 01:48

support unnest

ea3313c

add test

0311f0b

fix ast

25b878f

Update condition for BigQueryDialect or GenericDialect

4c5021f

I updated condition. This changes conditionalize parsing for only BigQueryDialect or GenericDialect.

Add some tests

515463f

sivchari force-pushed the support-unnest branch from 4f0b340 to 515463f Compare May 24, 2022 17:32

alamb approved these changes May 25, 2022

View reviewed changes

fix test

2e84c8b

alamb merged commit aa46e93 into sqlparser-rs:main May 27, 2022

sivchari mentioned this pull request Jun 29, 2022

feat: Add WITH OFFSET Alias #528

Merged

hexedpackets mentioned this pull request Sep 13, 2023

Support UNNEST as a table factor for PostgreSQL #968

Merged

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Support `UNNEST` as a table factor #493

Support `UNNEST` as a table factor #493

sivchari commented May 10, 2022

alamb left a comment

alamb May 10, 2022

alamb May 10, 2022

coveralls commented May 22, 2022 •

edited

sivchari commented May 24, 2022 •

edited

alamb left a comment

alamb commented May 25, 2022

sivchari commented May 26, 2022

sivchari commented May 26, 2022

alamb commented May 27, 2022

Support UNNEST as a table factor #493

Support UNNEST as a table factor #493

Conversation

sivchari commented May 10, 2022

alamb left a comment

Choose a reason for hiding this comment

alamb May 10, 2022

Choose a reason for hiding this comment

alamb May 10, 2022

Choose a reason for hiding this comment

coveralls commented May 22, 2022 • edited

Pull Request Test Coverage Report for Build 2390833123

💛 - Coveralls

sivchari commented May 24, 2022 • edited

alamb left a comment

Choose a reason for hiding this comment

alamb commented May 25, 2022

sivchari commented May 26, 2022

sivchari commented May 26, 2022

alamb commented May 27, 2022

Support `UNNEST` as a table factor #493

Support `UNNEST` as a table factor #493

coveralls commented May 22, 2022 •

edited

sivchari commented May 24, 2022 •

edited