8000 [Test] Adjust index tests to expected behavior by taniabogatsch · Pull Request #16062 · duckdb/duckdb · GitHub
[go: up one dir, main page]
More Web Proxy on the site http://driver.im/
Skip to content

[Test] Adjust index tests to expected behavior #16062

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Merged
merged 2 commits into from
Feb 4, 2025
Merged

[Test] Adjust index tests to expected behavior #16062

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
2 commits
Select commit Hold shift + click to select a range
fd5e46b
adjust tests to expected behavior
taniabogatsch Feb 4, 2025
4908b38
Merge branch 'v1.2-histrionicus' into index-fixes
taniabogatsch Feb 4, 2025
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
17 changes: 7 additions & 10 deletions test/sql/constraints/primarykey/test_pk_updel_multi_column.test
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -2,9 +2,6 @@
# description: PRIMARY KEY and update/delete on multiple columns
# group: [primarykey]

# See test/sql/index/art/constraints/test_art_tx_returning.test.
require vector_size 2048

statement ok
PRAGMA enable_verification;

Expand All @@ -25,17 +22,17 @@ SELECT * FROM test ORDER BY ALL;
13 pandas

statement ok
UPDATE test SET a = a + 1;
UPDATE test SET a = a + 3;

query IT
SELECT * FROM test ORDER BY ALL;
----
12 pandas
13 pandas
14 pandas
15 pandas
16 pandas

statement error
UPDATE test SET a = 13 WHERE a = 12;
UPDATE test SET a = 15 WHERE a = 14;
----
<REGEX>:Constraint Error.*violates primary key constraint.*

Expand All @@ -47,12 +44,12 @@ UPDATE test SET a = 4;
query IT
SELECT * FROM test ORDER BY a;
----
12 pandas
13 pandas
14 pandas
15 pandas
16 pandas

statement ok
UPDATE test SET a = a - 1;
UPDATE test SET a = a - 3;

query IT
SELECT * FROM test ORDER BY ALL;
Expand Down
31 changes: 19 additions & 12 deletions test/sql/index/art/constraints/test_art_tx_returning.test
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -2,13 +2,6 @@
# description: Test updates on the primary key containing RETURNING.
# group: [constraints]

# For each incoming chunk, we add the row IDs to the delete index.
# For standard_vector_size = 2, we delete [0, 1], and then try to insert value [1, 2].
# This is expected to throw a constraint violation.
# The value 2 is not yet in the delete index, as the chunk that would add that value hasn't been processed, yet.
# This scenario is a known limitation (also in postgres).
require vector_size 2048

statement ok
PRAGMA enable_verification

Expand All @@ -22,10 +15,24 @@ statement ok
INSERT INTO tbl_list SELECT range, [range || ' payload'] FROM range(5);

query II
UPDATE tbl_list SET id = id + 5 RETURNING id, payload;
----
5 [0 payload]
6 [1 payload]
7 [2 payload]
8 [3 payload]
9 [4 payload]

statement ok
INSERT INTO tbl_list SELECT range + 10, [(range + 10) || ' payload'] FROM range(3000);

# For each incoming chunk, we add the row IDs to the delete index.
# For standard_vector_size = 2048, we delete the first chunk, and then try to insert the incremented values.
# This is expected to throw a constraint violation.
# The value going into the next chunk is not yet in the delete index, as the chunk that would add that value hasn't been processed, yet.
# This scenario is a known limitation (also in postgres).

statement error
UPDATE tbl_list SET id = id + 1 RETURNING id, payload;
----
1 [0 payload]
2 [1 payload]
3 [2 payload]
4 [3 payload]
5 [4 payload]
<REGEX>:Constraint Error.*violates primary key constraint.*
104 changes: 42 additions & 62 deletions test/sql/upsert/test_big_insert.test
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -3,121 +3,101 @@
# group: [upsert]

statement ok
pragma enable_verification;
PRAGMA enable_verification;

statement ok
SET preserve_insertion_order=false;
SET preserve_insertion_order = false;

# big insert
statement ok
CREATE TABLE integers(
i INTEGER unique,
j INTEGER DEFAULT 0,
k INTEGER DEFAULT 0
i INT UNIQUE,
j INT DEFAULT 0,
k INT DEFAULT 0
);

statement ok
INSERT INTO integers(i) SELECT i from range(5000) tbl(i);
INSERT INTO integers(i) SELECT i FROM range(5000) tbl(i);

query I
SELECT COUNT(*) FROM integers
----
5000

# All tuples hit a conflict - Do nothing
# All tuples hit a conflict - DO NOTHING.
statement ok
INSERT INTO integers SELECT * FROM integers on conflict do nothing;
INSERT INTO integers SELECT * FROM integers ON CONFLICT DO NOTHING;

# All tuples hit a conflict - Do Update
# All tuples hit a conflict - DO UPDATE.
statement ok
INSERT INTO integers SELECT * FROM integers on conflict do update set j = 10;
INSERT INTO integers SELECT * FROM integers ON CONFLICT DO UPDATE SET j = 10;

# All 'j' entries are changed to 10
# All 'j' entries are changed to 10.
query I
SELECT COUNT(*) FILTER (WHERE j = 10) FROM integers
----
5000

# All insert tuples cause a conflict on the same row
statement error
INSERT INTO integers(i,j) select
i%5,
i
from range(5000) tbl(i) on conflict do update set
j = excluded.j,
k = excluded.i;
----
Invalid Input Error: ON CONFLICT DO UPDATE can not update the same row twice in the same command. Ensure that no rows proposed for insertion within the same command have duplicate constrained values

statement ok
INSERT INTO integers(i,j) select i%5,i from range(4995, 5000) tbl(i) on conflict do update set j = excluded.j, k = excluded.i;
INSERT INTO integers(i, j) SELECT i % 5, i FROM range(4995, 5000) tbl(i) ON CONFLICT DO UPDATE SET j = excluded.j, k = excluded.i;

# This is what we might expect the previous result to look like as well
query I
select j from integers limit 5;
SELECT j FROM integers LIMIT 5;
----
4995
4996
4997
4998
4999

# This is the worst conflicting rowid pattern we could have
# Every odd-indexed insert tuple conflicts with a row at the start of the existing tuples
# And every even-indexed insert tuple conflicts with a row at the end of the existing tuples
# This is the worst conflicting rowid pattern we could have.
# Every odd-indexed insert tuple conflicts with a row at the start of the existing tuples.
# And every even-indexed insert tuple conflicts with a row at the end of the existing tuples.
statement ok
insert into integers(i,j)
select
CASE WHEN i % 2 = 0
THEN
4999 - (i//2)
ELSE
i - ((i//2)+1)
END,
i
from range(5000) tbl(i)
on conflict do update set j = excluded.j;

# This shows that the odd-indexed insert tuples conflicted with the first rows
INSERT INTO integers(i, j)
SELECT CASE WHEN i % 2 = 0
THEN 4999 - (i // 2)
ELSE i - ((i // 2) + 1)
END, i
FROM range(5000) tbl(i)
ON CONFLICT DO UPDATE SET j = excluded.j;

# This shows that the odd-indexed insert tuples conflicted with the first rows.
query I
select j from integers limit 5;
SELECT j FROM integers LIMIT 5;
----
1
3
5
7
9

# This shows that the even-indexed insert tuples conflicted with the last rows
# This shows that the even-indexed insert tuples conflicted with the last rows.
query I
select j from integers limit 5 offset 4995;
SELECT j FROM integers LIMIT 5 OFFSET 4995;
----
8
6
4
2
0

# Reset j
# Reset j.
statement ok
update integers set j = 0;
UPDATE integers SET j = 0;

# Only set j if both the existing tuple and the insert tuple are even
# Only set j if both the existing tuple and the insert tuple are even.
statement ok
insert into integers(i,j)
select
CASE WHEN i % 2 = 0
THEN
4999 - (i//2)
ELSE
i - ((i//2)+1)
END,
i
from range(5000) tbl(i)
on conflict do update set j = excluded.j where i % 2 = 0 AND excluded.j % 2 = 0;

# The DO UPDATE where clause is only true for a quarter of the cases
INSERT INTO integers(i, j)
SELECT CASE WHEN i % 2 = 0
THEN 4999 - (i // 2)
ELSE i - ((i // 2) + 1)
END, i
FROM range(5000) tbl(i)
ON CONFLICT DO UPDATE SET j = excluded.j
WHERE i % 2 = 0 AND excluded.j % 2 = 0;

# The DO UPDATE WHERE clause is only true for a quarter of the cases.
query I
select COUNT(j) filter (where j != 0) from integers;
SELECT COUNT(j) FILTER (WHERE j != 0) FROM integers;
----
1250
32 changes: 32 additions & 0 deletions test/sql/upsert/test_big_insert_no_vector_verification.test
View file
Open in desktop
5F72
Original file line number Diff line number Diff line change
@@ -0,0 +1,32 @@
# name: test/sql/upsert/test_big_insert_no_vector_verification.test
# description: Test ON CONFLICT statement on the same conflicting row.
# group: [upsert]

# The constant operator verification ensures th 1E79 at we have only one row per data chunk.
# Thus, the below insert succeeds, as we no longer see the same row within a chunk.
require no_vector_verification

statement ok
PRAGMA enable_verification;

statement ok
SET preserve_insertion_order = false;

statement ok
CREATE TABLE integers(
i INT UNIQUE,
j INT DEFAULT 0,
k INT DEFAULT 0
);

statement ok
INSERT INTO integers(i) SELECT i FROM range(5000) tbl(i);

statement error
INSERT INTO integers(i, j)
SELECT i % 5, i
FROM range(5000) tbl(i) ON CONFLICT DO UPDATE SET
j = excluded.j,
k = excluded.i;
----
<REGEX>:Invalid Input Error:.*ON CONFLICT DO UPDATE can not update the same row twice in the same command.*
Loading
0