1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
|
# name: test/sql/projection/test_columns_replace_using.test
# description: Test COLUMNS REPLACE with NATURAL JOIN and JOIN USING
# group: [projection]
statement ok
PRAGMA enable_verification
# Test COLUMNS(* REPLACE) with NATURAL JOIN
# Issue: COLUMNS(* REPLACE a AS a) was generating duplicate columns for USING columns
# Expected: Should produce the same result as SELECT *
query I
SELECT COLUMNS(* REPLACE a AS a) FROM range(1) t1(a) NATURAL JOIN range(1) t2(a)
----
0
query I
SELECT * FROM range(1) t1(a) NATURAL JOIN range(1) t2(a)
----
0
# Test with JOIN USING
query I
SELECT COLUMNS(* REPLACE a AS a) FROM range(1) t1(a) JOIN range(1) t2(a) USING (a)
----
0
# Test with an actual replacement expression
query I
SELECT COLUMNS(* REPLACE (a + 1 AS a)) FROM range(1) t1(a) NATURAL JOIN range(1) t2(a)
----
1
# Test with multiple rows
query I
SELECT COLUMNS(* REPLACE (a * 2 AS a)) FROM range(3) t1(a) NATURAL JOIN range(3) t2(a)
----
0
2
4
# Test with multiple columns where one is a USING column
query III
SELECT COLUMNS(* REPLACE (a + 100 AS a)) FROM (SELECT 1 as a, 2 as b) t1 NATURAL JOIN (SELECT 1 as a, 3 as c) t2
----
101 2 3
# Test replacing a non-USING column
query III
SELECT COLUMNS(* REPLACE (b + 10 AS b)) FROM (SELECT 1 as a, 2 as b) t1 NATURAL JOIN (SELECT 1 as a, 3 as c) t2
----
1 12 3
# Test with three-way NATURAL JOIN
query I
SELECT COLUMNS(* REPLACE (a AS a)) FROM range(1) t1(a) NATURAL JOIN range(1) t2(a) NATURAL JOIN range(1) t3(a)
----
0
# Test with three-way NATURAL JOIN with complex expression
query I
SELECT COLUMNS(* REPLACE (a + 1 AS a)) FROM range(2) t1(a) NATURAL JOIN range(2) t2(a) NATURAL JOIN range(2) t3(a)
----
1
2
# Test COLUMNS(* RENAME) still works correctly with NATURAL JOIN
query I
SELECT COLUMNS(* RENAME a AS b) FROM range(1) t1(a) NATURAL JOIN range(1) t2(a)
----
0
# Test multiple replacements with NATURAL JOIN
statement ok
CREATE TABLE test_t1 AS SELECT 1 as a, 2 as b, 3 as c
statement ok
CREATE TABLE test_t2 AS SELECT 1 as a, 4 as d, 5 as e
query IIIII
SELECT COLUMNS(* REPLACE (a + 10 AS a, b + 20 AS b)) FROM test_t1 NATURAL JOIN test_t2
----
11 22 3 4 5
# Test REPLACE with complex expression on USING column
query IIIII
SELECT COLUMNS(* REPLACE (concat(a::VARCHAR, '_joined') AS a)) FROM test_t1 NATURAL JOIN test_t2
----
1_joined 2 3 4 5
# Test that EXCLUDE with qualified names still works correctly
statement ok
CREATE TABLE integers(i INTEGER, j INTEGER, k INTEGER)
statement ok
INSERT INTO integers VALUES (1, 2, 3)
query IIII
SELECT * EXCLUDE (i1.i, i2.i) FROM integers i1 JOIN integers i2 USING (i)
----
2 3 2 3
# Clean up
statement ok
DROP TABLE test_t1
statement ok
DROP TABLE test_t2
statement ok
DROP TABLE integers
|
