1#include "catch.hpp"
2#include "test_helpers.hpp"
3
4using namespace duckdb;
5using namespace std;
6
7TEST_CASE("Test Row IDs", "[rowid]") {
8 unique_ptr<QueryResult> result;
9 DuckDB db(nullptr);
10 Connection con(db);
11 con.EnableQueryVerification();
12
13 REQUIRE_NO_FAIL(con.Query("create table a(i integer);"));
14 REQUIRE_NO_FAIL(con.Query("insert into a values (42), (44);"));
15
16 // we can query row ids
17 result = con.Query("SELECT rowid, * FROM a");
18 REQUIRE(CHECK_COLUMN(result, 0, {0, 1}));
19 REQUIRE(CHECK_COLUMN(result, 1, {42, 44}));
20 REQUIRE(result->types.size() == 2);
21
22 result = con.Query("SELECT rowid+1 FROM a WHERE CASE WHEN i=42 THEN rowid=0 ELSE rowid=1 END;");
23 REQUIRE(CHECK_COLUMN(result, 0, {1, 2}));
24
25 // rowid isn't expanded in *
26 result = con.Query("SELECT * FROM a");
27 REQUIRE(CHECK_COLUMN(result, 0, {42, 44}));
28 REQUIRE(result->types.size() == 1);
29
30 // we can't update rowids
31 REQUIRE_FAIL(con.Query("UPDATE a SET rowid=5"));
32 // we also can't insert with explicit row ids
33 REQUIRE_FAIL(con.Query("INSERT INTO a (rowid, i) VALUES (5, 6)"));
34
35 // we can use rowid as column name
36 REQUIRE_NO_FAIL(con.Query("create table b(rowid integer);"));
37 REQUIRE_NO_FAIL(con.Query("insert into b values (42), (22);"));
38
39 // this rowid is expanded
40 result = con.Query("SELECT * FROM b ORDER BY 1");
41 REQUIRE(CHECK_COLUMN(result, 0, {22, 42}));
42 REQUIRE(result->types.size() == 1);
43
44 // selecting rowid just selects the column
45 result = con.Query("SELECT rowid FROM b ORDER BY 1");
46 REQUIRE(CHECK_COLUMN(result, 0, {22, 42}));
47 REQUIRE(result->types.size() == 1);
48 // now we can update
49 REQUIRE_NO_FAIL(con.Query("UPDATE b SET rowid=5"));
50 // and insert
51 REQUIRE_NO_FAIL(con.Query("INSERT INTO b (rowid) VALUES (5)"));
52
53 result = con.Query("SELECT * FROM b");
54 REQUIRE(CHECK_COLUMN(result, 0, {5, 5, 5}));
55}
56
57TEST_CASE("Test Row IDs used in different types of operations", "[rowid]") {
58 unique_ptr<QueryResult> result;
59 DuckDB db(nullptr);
60 Connection con(db);
61 con.EnableQueryVerification();
62
63 // test row ids on different operations
64 // this is interesting because rowids are emitted as compressed vectors
65 // hence this is really a test of correct handling of compressed vectors in the execution engine
66 REQUIRE_NO_FAIL(con.Query("create table a(i integer);"));
67 REQUIRE_NO_FAIL(con.Query("insert into a values (42);"));
68
69 // arithmetic
70 result = con.Query("SELECT rowid + 1, rowid - 1, rowid + rowid, i + rowid FROM a");
71 REQUIRE(CHECK_COLUMN(result, 0, {1}));
72 REQUIRE(CHECK_COLUMN(result, 1, {-1}));
73 REQUIRE(CHECK_COLUMN(result, 2, {0}));
74 REQUIRE(CHECK_COLUMN(result, 3, {42}));
75
76 // unary ops
77 result = con.Query("SELECT -rowid, +rowid, abs(rowid) FROM a");
78 REQUIRE(CHECK_COLUMN(result, 0, {0}));
79 REQUIRE(CHECK_COLUMN(result, 1, {0}));
80 REQUIRE(CHECK_COLUMN(result, 2, {0}));
81
82 // ternary ops
83 result = con.Query("SELECT rowid BETWEEN -1 AND 1, 0 BETWEEN rowid AND 1, 1 BETWEEN -3 AND rowid FROM a");
84 REQUIRE(CHECK_COLUMN(result, 0, {true}));
85 REQUIRE(CHECK_COLUMN(result, 1, {true}));
86 REQUIRE(CHECK_COLUMN(result, 2, {false}));
87
88 // comparisons
89 result = con.Query("SELECT rowid < i, rowid = NULL, rowid = i, rowid <> 0 FROM a");
90 REQUIRE(CHECK_COLUMN(result, 0, {true}));
91 REQUIRE(CHECK_COLUMN(result, 1, {Value()}));
92 REQUIRE(CHECK_COLUMN(result, 2, {false}));
93 REQUIRE(CHECK_COLUMN(result, 3, {false}));
94
95 // simple (ungrouped) aggregates
96 result = con.Query("SELECT SUM(rowid), MIN(rowid), MAX(rowid), COUNT(rowid), FIRST(rowid) FROM a");
97 REQUIRE(CHECK_COLUMN(result, 0, {0}));
98 REQUIRE(CHECK_COLUMN(result, 1, {0}));
99 REQUIRE(CHECK_COLUMN(result, 2, {0}));
100 REQUIRE(CHECK_COLUMN(result, 3, {1}));
101 REQUIRE(CHECK_COLUMN(result, 4, {0}));
102
103 result = con.Query("SELECT COUNT(*) FROM a");
104 REQUIRE(CHECK_COLUMN(result, 0, {1}));
105
106 // grouped aggregates
107 result = con.Query("SELECT SUM(rowid), MIN(rowid), MAX(rowid), COUNT(rowid), FIRST(rowid) FROM a GROUP BY i");
108 REQUIRE(CHECK_COLUMN(result, 0, {0}));
109 REQUIRE(CHECK_COLUMN(result, 1, {0}));
110 REQUIRE(CHECK_COLUMN(result, 2, {0}));
111 REQUIRE(CHECK_COLUMN(result, 3, {1}));
112 REQUIRE(CHECK_COLUMN(result, 4, {0}));
113
114 // group by rowid
115 result = con.Query("SELECT SUM(i) FROM a GROUP BY rowid");
116 REQUIRE(CHECK_COLUMN(result, 0, {42}));
117
118 // joins
119 // equality
120 result = con.Query("SELECT * FROM a, a a2 WHERE a.rowid=a2.rowid");
121 REQUIRE(CHECK_COLUMN(result, 0, {42}));
122 // inequality
123 result = con.Query("SELECT * FROM a, a a2 WHERE a.rowid<>a2.rowid");
124 REQUIRE(CHECK_COLUMN(result, 0, {}));
125 // range
126 result = con.Query("SELECT * FROM a, a a2 WHERE a.rowid>=a2.rowid");
127 REQUIRE(CHECK_COLUMN(result, 0, {42}));
128
129 // order by
130 result = con.Query("SELECT * FROM a ORDER BY rowid");
131 REQUIRE(CHECK_COLUMN(result, 0, {42}));
132
133 // insert into table
134 REQUIRE_NO_FAIL(con.Query("INSERT INTO a SELECT rowid FROM a"));
135 result = con.Query("SELECT * FROM a ORDER BY 1");
136 REQUIRE(CHECK_COLUMN(result, 0, {0, 42}));
137
138 // update value
139 REQUIRE_NO_FAIL(con.Query("UPDATE a SET i=rowid"));
140 result = con.Query("SELECT * FROM a ORDER BY 1");
141 REQUIRE(CHECK_COLUMN(result, 0, {0, 1}));
142
143 // use rowid in filter
144 result = con.Query("SELECT * FROM a WHERE rowid=0");
145 REQUIRE(CHECK_COLUMN(result, 0, {0}));
146 result = con.Query("SELECT * FROM a WHERE rowid BETWEEN -100 AND 100 ORDER BY 1");
147 REQUIRE(CHECK_COLUMN(result, 0, {0, 1}));
148 result = con.Query("SELECT * FROM a WHERE rowid=0 OR rowid=1");
149 REQUIRE(CHECK_COLUMN(result, 0, {0, 1}));
150
151 // window expressions
152 result = con.Query("SELECT row_number() OVER (PARTITION BY rowid) FROM a ORDER BY rowid");
153 REQUIRE(CHECK_COLUMN(result, 0, {1, 1}));
154 result = con.Query("SELECT row_number() OVER (ORDER BY rowid) FROM a ORDER BY rowid");
155 REQUIRE(CHECK_COLUMN(result, 0, {1, 2}));
156 result = con.Query("SELECT row_number() OVER (ORDER BY rowid DESC) FROM a ORDER BY rowid");
157 REQUIRE(CHECK_COLUMN(result, 0, {2, 1}));
158
159 // uncorrelated subqueries
160 result = con.Query("SELECT (SELECT rowid FROM a LIMIT 1)");
161 REQUIRE(CHECK_COLUMN(result, 0, {0}));
162 result = con.Query("SELECT 0 IN (SELECT rowid FROM a)");
163 REQUIRE(CHECK_COLUMN(result, 0, {true}));
164 result = con.Query("SELECT EXISTS(SELECT rowid FROM a)");
165 REQUIRE(CHECK_COLUMN(result, 0, {true}));
166
167 // correlated subqueries
168 result = con.Query("SELECT (SELECT a2.rowid FROM a a2 WHERE a.rowid=a2.rowid) FROM a");
169 REQUIRE(CHECK_COLUMN(result, 0, {0, 1}));
170 result = con.Query("SELECT a.rowid IN (SELECT a2.rowid FROM a a2 WHERE a.rowid>=a2.rowid) FROM a");
171 REQUIRE(CHECK_COLUMN(result, 0, {true, true}));
172 result = con.Query("SELECT EXISTS(SELECT a2.rowid FROM a a2 WHERE a.rowid>=a2.rowid) FROM a");
173 REQUIRE(CHECK_COLUMN(result, 0, {true, true}));
174}
175