zfr9527 commented on code in PR #60260: URL: https://github.com/apache/doris/pull/60260#discussion_r2731537001
########## regression-test/suites/nereids_function_p0/gen_function/unnest_join_list_test.groovy: ########## @@ -0,0 +1,276 @@ +// Licensed to the Apache Software Foundation (ASF) under one +// or more contributor license agreements. See the NOTICE file +// distributed with this work for additional information +// regarding copyright ownership. The ASF licenses this file +// to you under the Apache License, Version 2.0 (the +// "License"); you may not use this file except in compliance +// with the License. You may obtain a copy of the License at +// +// http://www.apache.org/licenses/LICENSE-2.0 +// +// Unless required by applicable law or agreed to in writing, +// software distributed under the License is distributed on an +// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY +// KIND, either express or implied. See the License for the +// specific language governing permissions and limitations +// under the License. + +suite("unnest_join_list_test", "unnest") { + + String prefix_str = "unnest_join_list_" + def tb_name1 = prefix_str + "table1" + def tb_name2 = prefix_str + "table2" + def tb_name3 = prefix_str + "table3" + def tb_name4 = prefix_str + "table4" + def tb_name5 = prefix_str + "table5" + def tb_name6 = prefix_str + "table6" + + sql """drop table if exists ${tb_name1}""" + sql """ + CREATE TABLE IF NOT EXISTS ${tb_name1} ( + u_id INT, + u_name VARCHAR(50), + u_tags ARRAY<STRING>, + u_logins BITMAP, + u_ext MAP<STRING, STRING> + ) + DUPLICATE KEY(u_id) + DISTRIBUTED BY HASH(u_id) BUCKETS 1 + PROPERTIES ("replication_num" = "1");""" + + sql """ + INSERT INTO ${tb_name1} VALUES + (1, 'Alice', ['work', 'gym'], bitmap_from_string("101, 102"), {'city': 'shanghai'}), + (2, 'Bob', ['game'], bitmap_empty(), {'city': 'beijing'}), + (3, 'Charlie', [], to_bitmap(103), {}), + (101, 'Alice', ['work', 'gym'], bitmap_from_string("101, 102"), {'city': 'shanghai'}), + (102, 'Bob', ['game'], bitmap_empty(), {'city': 'beijing'}), + (103, 'Charlie', [], to_bitmap(103), {});""" + + sql """drop table if exists ${tb_name2}""" + sql """ + CREATE TABLE IF NOT EXISTS ${tb_name2} ( + tag_name VARCHAR(50), + tag_group VARCHAR(20) + ) + DISTRIBUTED BY HASH(tag_name) BUCKETS 1 + PROPERTIES ("replication_num" = "1");""" + + sql """INSERT INTO ${tb_name2} VALUES ('work', 'Business'), ('gym', 'Health'), ('game', 'Entertain');""" + + sql """drop table if exists ${tb_name3}""" + sql """ + CREATE TABLE IF NOT EXISTS ${tb_name3} ( + category_name VARCHAR(20), + manager_name VARCHAR(50), + office_location VARCHAR(50) + ) + DISTRIBUTED BY HASH(category_name) BUCKETS 1 + PROPERTIES ("replication_num" = "1");""" + + sql """ + INSERT INTO ${tb_name3} VALUES + ('Business', 'David', 'Tower A'), + ('Health', 'Eve', 'Tower B'), + ('Entertain', 'Frank', 'Tower C');""" + + // lateral join + // Test a lateral join using `JOIN UNNEST` with an ON true condition. + qt_lateral_join_unnest """SELECT u.u_name, t.tag, t.pos FROM ${tb_name1} u JOIN UNNEST(u.u_tags) WITH ORDINALITY AS t(pos, tag) ON true ORDER BY u.u_name, t.pos;""" + + // on filter + // Test a lateral join with a filter on the unnested table in the ON clause. + qt_lateral_join_unnest_with_on_filter """SELECT u.u_name, t.tag FROM ${tb_name1} u JOIN UNNEST(u.u_tags) WITH ORDINALITY AS t(pos, tag) ON t.pos = 1 ORDER BY u.u_name, t.tag;""" + + // more table join + // Test a chain of joins following an UNNEST operation. + qt_chain_join_after_unnest """SELECT u.u_name, t.tag, d.tag_group FROM ${tb_name1} u JOIN UNNEST(u.u_tags) WITH ORDINALITY AS t(pos, tag) ON true JOIN ${tb_name2} d ON t.tag = d.tag_name ORDER BY u.u_name, t.tag, d.tag_group;""" + + // bitmap + // Test unnesting a bitmap and then joining the results back to the same table. + qt_unnest_bitmap_and_join_back """ + SELECT u.u_name AS owner, b.login_id, u2.u_name AS visitor + FROM ${tb_name1} u + JOIN UNNEST(u.u_logins) AS b(login_id) ON true + JOIN ${tb_name1} u2 ON b.login_id = u2.u_id + ORDER BY owner, login_id, visitor;""" + + test { + // Test that using UNNEST directly within a JOIN condition is not allowed and fails as expected. + sql """SELECT u.u_name AS owner, b.login_id, u2.u_name AS visitor + FROM ${tb_name1} u + JOIN UNNEST(u.u_logins) AS b(login_id) ON true + JOIN ${tb_name1} u2 ON b.login_id = unnest(u2.u_logins); + """ + exception "table generating function is not allowed in LOGICAL_PROJECT" + } + + // map + // Test joining with an unnested map and applying a filter in the ON clause. + qt_join_unnest_map_with_filter """ + SELECT u.u_name, m.entry + FROM ${tb_name1} u + JOIN UNNEST(u.u_ext) AS m(entry) ON m.entry LIKE '%city%' + ORDER BY u.u_name, m.entry;""" + + // left join + // Test a LEFT JOIN with UNNEST, preserving left-side rows for empty arrays. + qt_left_join_unnest_array """ + SELECT u.u_name, t.tag, t.pos + FROM ${tb_name1} u + LEFT JOIN UNNEST(u.u_tags) WITH ORDINALITY AS t(tag, pos) ON true + ORDER BY u.u_name, t.pos;""" + Review Comment: Swap places -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
