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
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
|
# name: test/sql/binder/test_case_insensitive_binding.test
# description: Test case insensitive binding of columns
# group: [binder]
statement ok
PRAGMA enable_verification
# we can bind case insensitive column names
statement ok
CREATE TABLE test ("HeLlO" INTEGER)
statement ok
INSERT INTO test VALUES (1)
# lowercase names are aliased
statement ok
SELECT HeLlO FROM test
statement ok
SELECT hello FROM test
statement ok
SELECT "HeLlO" FROM test
statement ok
SELECT "HELLO" FROM test
statement ok
SELECT "HELLo" FROM test
# verify that the column name of the original column is returned
query I
SELECT alias(HeLlO) FROM test
----
HeLlO
query I
SELECT alias(hello) FROM test
----
HeLlO
# verify that an alias here still works
query I
SELECT alias(x) FROM (SELECT HeLlO as x FROM test) tbl;
----
x
# verify that it also works when we specify the table-name explicitly
statement ok
SELECT test.HeLlO FROM test
statement ok
SELECT test.hello FROM test
statement ok
SELECT test."HeLlO" FROM test
statement ok
SELECT test."HELLO" FROM test
statement ok
SELECT test."HELLo" FROM test
statement ok
UPDATE test SET hello=3
statement ok
UPDATE test SET HeLlO=3
statement ok
DROP TABLE test
# this counts as a duplicate column error
statement error
CREATE TABLE test("HeLlO" INTEGER, "HELLO" INTEGER)
----
# conflicts can come from different sources!
statement ok
CREATE TABLE test1("HeLlO" INTEGER)
statement ok
CREATE TABLE test2("HELLO" INTEGER)
statement error
SELECT HeLlO FROM test1, test2
----
statement error
SELECT hello FROM test1, test2
----
statement error
SELECT "HeLlO" FROM test1, test2
----
statement error
SELECT "HELLO" FROM test1, test2
----
statement error
SELECT "HELLo" FROM test1, test2
----
# in this case we can eliminate the conflict by specifically selecting the source
statement ok
SELECT test1.HeLlO FROM test1, test2
statement ok
SELECT test1.hello FROM test1, test2
statement ok
SELECT test1."HeLlO" FROM test1, test2
statement ok
SELECT test1."HELLO" FROM test1, test2
statement ok
SELECT test1."HELLo" FROM test1, test2
statement ok
SELECT test2.HeLlO FROM test1, test2
statement ok
SELECT test2.hello FROM test1, test2
statement ok
SELECT test2."HeLlO" FROM test1, test2
statement ok
SELECT test2."HELLO" FROM test1, test2
statement ok
SELECT test2."HELLo" FROM test1, test2
statement ok
SELECT * FROM test1 JOIN test2 USING (hello)
query I
SELECT hello FROM (SELECT 42) tbl("HeLlO")
----
42
|
