aboutsummaryrefslogtreecommitdiff
path: root/src
diff options
context:
space:
mode:
Diffstat (limited to 'src')
-rw-r--r--src/test/regress/expected/create_index_spgist.out54
-rw-r--r--src/test/regress/sql/create_index_spgist.sql54
2 files changed, 48 insertions, 60 deletions
diff --git a/src/test/regress/expected/create_index_spgist.out b/src/test/regress/expected/create_index_spgist.out
index b422e928c0d..81b4a67c390 100644
--- a/src/test/regress/expected/create_index_spgist.out
+++ b/src/test/regress/expected/create_index_spgist.out
@@ -81,13 +81,13 @@ SELECT count(*) FROM quad_point_tbl WHERE p ~= '(4585, 365)';
(1 row)
CREATE TEMP TABLE quad_point_tbl_ord_seq1 AS
-SELECT rank() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
+SELECT row_number() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
FROM quad_point_tbl;
CREATE TEMP TABLE quad_point_tbl_ord_seq2 AS
-SELECT rank() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
+SELECT row_number() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
FROM quad_point_tbl WHERE p <@ box '(200,200,1000,1000)';
CREATE TEMP TABLE quad_point_tbl_ord_seq3 AS
-SELECT rank() OVER (ORDER BY p <-> '333,400') n, p <-> '333,400' dist, p
+SELECT row_number() OVER (ORDER BY p <-> '333,400') n, p <-> '333,400' dist, p
FROM quad_point_tbl WHERE p IS NOT NULL;
SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdef';
count
@@ -327,7 +327,7 @@ SELECT count(*) FROM quad_point_tbl WHERE p ~= '(4585, 365)';
(1 row)
EXPLAIN (COSTS OFF)
-SELECT rank() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
+SELECT row_number() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
FROM quad_point_tbl;
QUERY PLAN
-----------------------------------------------------------
@@ -337,18 +337,17 @@ FROM quad_point_tbl;
(3 rows)
CREATE TEMP TABLE quad_point_tbl_ord_idx1 AS
-SELECT rank() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
+SELECT row_number() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
FROM quad_point_tbl;
SELECT * FROM quad_point_tbl_ord_seq1 seq FULL JOIN quad_point_tbl_ord_idx1 idx
ON seq.n = idx.n
-AND (seq.dist = idx.dist AND seq.p ~= idx.p OR seq.p IS NULL AND idx.p IS NULL)
-WHERE seq.n IS NULL OR idx.n IS NULL;
+WHERE seq.dist IS DISTINCT FROM idx.dist;
n | dist | p | n | dist | p
---+------+---+---+------+---
(0 rows)
EXPLAIN (COSTS OFF)
-SELECT rank() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
+SELECT row_number() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
FROM quad_point_tbl WHERE p <@ box '(200,200,1000,1000)';
QUERY PLAN
-----------------------------------------------------------
@@ -359,18 +358,17 @@ FROM quad_point_tbl WHERE p <@ box '(200,200,1000,1000)';
(4 rows)
CREATE TEMP TABLE quad_point_tbl_ord_idx2 AS
-SELECT rank() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
+SELECT row_number() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
FROM quad_point_tbl WHERE p <@ box '(200,200,1000,1000)';
SELECT * FROM quad_point_tbl_ord_seq2 seq FULL JOIN quad_point_tbl_ord_idx2 idx
ON seq.n = idx.n
-AND (seq.dist = idx.dist AND seq.p ~= idx.p OR seq.p IS NULL AND idx.p IS NULL)
-WHERE seq.n IS NULL OR idx.n IS NULL;
+WHERE seq.dist IS DISTINCT FROM idx.dist;
n | dist | p | n | dist | p
---+------+---+---+------+---
(0 rows)
EXPLAIN (COSTS OFF)
-SELECT rank() OVER (ORDER BY p <-> '333,400') n, p <-> '333,400' dist, p
+SELECT row_number() OVER (ORDER BY p <-> '333,400') n, p <-> '333,400' dist, p
FROM quad_point_tbl WHERE p IS NOT NULL;
QUERY PLAN
-----------------------------------------------------------
@@ -381,12 +379,11 @@ FROM quad_point_tbl WHERE p IS NOT NULL;
(4 rows)
CREATE TEMP TABLE quad_point_tbl_ord_idx3 AS
-SELECT rank() OVER (ORDER BY p <-> '333,400') n, p <-> '333,400' dist, p
+SELECT row_number() OVER (ORDER BY p <-> '333,400') n, p <-> '333,400' dist, p
FROM quad_point_tbl WHERE p IS NOT NULL;
SELECT * FROM quad_point_tbl_ord_seq3 seq FULL JOIN quad_point_tbl_ord_idx3 idx
ON seq.n = idx.n
-AND (seq.dist = idx.dist AND seq.p ~= idx.p OR seq.p IS NULL AND idx.p IS NULL)
-WHERE seq.n IS NULL OR idx.n IS NULL;
+WHERE seq.dist IS DISTINCT FROM idx.dist;
n | dist | p | n | dist | p
---+------+---+---+------+---
(0 rows)
@@ -497,7 +494,7 @@ SELECT count(*) FROM kd_point_tbl WHERE p ~= '(4585, 365)';
(1 row)
EXPLAIN (COSTS OFF)
-SELECT rank() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
+SELECT row_number() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
FROM kd_point_tbl;
QUERY PLAN
-------------------------------------------------------
@@ -507,18 +504,17 @@ FROM kd_point_tbl;
(3 rows)
CREATE TEMP TABLE kd_point_tbl_ord_idx1 AS
-SELECT rank() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
+SELECT row_number() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
FROM kd_point_tbl;
SELECT * FROM quad_point_tbl_ord_seq1 seq FULL JOIN kd_point_tbl_ord_idx1 idx
-ON seq.n = idx.n AND
-(seq.dist = idx.dist AND seq.p ~= idx.p OR seq.p IS NULL AND idx.p IS NULL)
-WHERE seq.n IS NULL OR idx.n IS NULL;
+ON seq.n = idx.n
+WHERE seq.dist IS DISTINCT FROM idx.dist;
n | dist | p | n | dist | p
---+------+---+---+------+---
(0 rows)
EXPLAIN (COSTS OFF)
-SELECT rank() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
+SELECT row_number() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
FROM kd_point_tbl WHERE p <@ box '(200,200,1000,1000)';
QUERY PLAN
---------------------------------------------------------
@@ -529,18 +525,17 @@ FROM kd_point_tbl WHERE p <@ box '(200,200,1000,1000)';
(4 rows)
CREATE TEMP TABLE kd_point_tbl_ord_idx2 AS
-SELECT rank() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
+SELECT row_number() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
FROM kd_point_tbl WHERE p <@ box '(200,200,1000,1000)';
SELECT * FROM quad_point_tbl_ord_seq2 seq FULL JOIN kd_point_tbl_ord_idx2 idx
-ON seq.n = idx.n AND
-(seq.dist = idx.dist AND seq.p ~= idx.p OR seq.p IS NULL AND idx.p IS NULL)
-WHERE seq.n IS NULL OR idx.n IS NULL;
+ON seq.n = idx.n
+WHERE seq.dist IS DISTINCT FROM idx.dist;
n | dist | p | n | dist | p
---+------+---+---+------+---
(0 rows)
EXPLAIN (COSTS OFF)
-SELECT rank() OVER (ORDER BY p <-> '333,400') n, p <-> '333,400' dist, p
+SELECT row_number() OVER (ORDER BY p <-> '333,400') n, p <-> '333,400' dist, p
FROM kd_point_tbl WHERE p IS NOT NULL;
QUERY PLAN
-------------------------------------------------------
@@ -551,12 +546,11 @@ FROM kd_point_tbl WHERE p IS NOT NULL;
(4 rows)
CREATE TEMP TABLE kd_point_tbl_ord_idx3 AS
-SELECT rank() OVER (ORDER BY p <-> '333,400') n, p <-> '333,400' dist, p
+SELECT row_number() OVER (ORDER BY p <-> '333,400') n, p <-> '333,400' dist, p
FROM kd_point_tbl WHERE p IS NOT NULL;
SELECT * FROM quad_point_tbl_ord_seq3 seq FULL JOIN kd_point_tbl_ord_idx3 idx
-ON seq.n = idx.n AND
-(seq.dist = idx.dist AND seq.p ~= idx.p OR seq.p IS NULL AND idx.p IS NULL)
-WHERE seq.n IS NULL OR idx.n IS NULL;
+ON seq.n = idx.n
+WHERE seq.dist IS DISTINCT FROM idx.dist;
n | dist | p | n | dist | p
---+------+---+---+------+---
(0 rows)
diff --git a/src/test/regress/sql/create_index_spgist.sql b/src/test/regress/sql/create_index_spgist.sql
index 6ada7022801..8e6c4533076 100644
--- a/src/test/regress/sql/create_index_spgist.sql
+++ b/src/test/regress/sql/create_index_spgist.sql
@@ -53,15 +53,15 @@ SELECT count(*) FROM quad_point_tbl WHERE p >^ '(5000, 4000)';
SELECT count(*) FROM quad_point_tbl WHERE p ~= '(4585, 365)';
CREATE TEMP TABLE quad_point_tbl_ord_seq1 AS
-SELECT rank() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
+SELECT row_number() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
FROM quad_point_tbl;
CREATE TEMP TABLE quad_point_tbl_ord_seq2 AS
-SELECT rank() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
+SELECT row_number() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
FROM quad_point_tbl WHERE p <@ box '(200,200,1000,1000)';
CREATE TEMP TABLE quad_point_tbl_ord_seq3 AS
-SELECT rank() OVER (ORDER BY p <-> '333,400') n, p <-> '333,400' dist, p
+SELECT row_number() OVER (ORDER BY p <-> '333,400') n, p <-> '333,400' dist, p
FROM quad_point_tbl WHERE p IS NOT NULL;
SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdef';
@@ -138,37 +138,34 @@ SELECT count(*) FROM quad_point_tbl WHERE p ~= '(4585, 365)';
SELECT count(*) FROM quad_point_tbl WHERE p ~= '(4585, 365)';
EXPLAIN (COSTS OFF)
-SELECT rank() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
+SELECT row_number() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
FROM quad_point_tbl;
CREATE TEMP TABLE quad_point_tbl_ord_idx1 AS
-SELECT rank() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
+SELECT row_number() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
FROM quad_point_tbl;
SELECT * FROM quad_point_tbl_ord_seq1 seq FULL JOIN quad_point_tbl_ord_idx1 idx
ON seq.n = idx.n
-AND (seq.dist = idx.dist AND seq.p ~= idx.p OR seq.p IS NULL AND idx.p IS NULL)
-WHERE seq.n IS NULL OR idx.n IS NULL;
+WHERE seq.dist IS DISTINCT FROM idx.dist;
EXPLAIN (COSTS OFF)
-SELECT rank() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
+SELECT row_number() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
FROM quad_point_tbl WHERE p <@ box '(200,200,1000,1000)';
CREATE TEMP TABLE quad_point_tbl_ord_idx2 AS
-SELECT rank() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
+SELECT row_number() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
FROM quad_point_tbl WHERE p <@ box '(200,200,1000,1000)';
SELECT * FROM quad_point_tbl_ord_seq2 seq FULL JOIN quad_point_tbl_ord_idx2 idx
ON seq.n = idx.n
-AND (seq.dist = idx.dist AND seq.p ~= idx.p OR seq.p IS NULL AND idx.p IS NULL)
-WHERE seq.n IS NULL OR idx.n IS NULL;
+WHERE seq.dist IS DISTINCT FROM idx.dist;
EXPLAIN (COSTS OFF)
-SELECT rank() OVER (ORDER BY p <-> '333,400') n, p <-> '333,400' dist, p
+SELECT row_number() OVER (ORDER BY p <-> '333,400') n, p <-> '333,400' dist, p
FROM quad_point_tbl WHERE p IS NOT NULL;
CREATE TEMP TABLE quad_point_tbl_ord_idx3 AS
-SELECT rank() OVER (ORDER BY p <-> '333,400') n, p <-> '333,400' dist, p
+SELECT row_number() OVER (ORDER BY p <-> '333,400') n, p <-> '333,400' dist, p
FROM quad_point_tbl WHERE p IS NOT NULL;
SELECT * FROM quad_point_tbl_ord_seq3 seq FULL JOIN quad_point_tbl_ord_idx3 idx
ON seq.n = idx.n
-AND (seq.dist = idx.dist AND seq.p ~= idx.p OR seq.p IS NULL AND idx.p IS NULL)
-WHERE seq.n IS NULL OR idx.n IS NULL;
+WHERE seq.dist IS DISTINCT FROM idx.dist;
EXPLAIN (COSTS OFF)
SELECT count(*) FROM kd_point_tbl WHERE p <@ box '(200,200,1000,1000)';
@@ -199,37 +196,34 @@ SELECT count(*) FROM kd_point_tbl WHERE p ~= '(4585, 365)';
SELECT count(*) FROM kd_point_tbl WHERE p ~= '(4585, 365)';
EXPLAIN (COSTS OFF)
-SELECT rank() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
+SELECT row_number() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
FROM kd_point_tbl;
CREATE TEMP TABLE kd_point_tbl_ord_idx1 AS
-SELECT rank() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
+SELECT row_number() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
FROM kd_point_tbl;
SELECT * FROM quad_point_tbl_ord_seq1 seq FULL JOIN kd_point_tbl_ord_idx1 idx
-ON seq.n = idx.n AND
-(seq.dist = idx.dist AND seq.p ~= idx.p OR seq.p IS NULL AND idx.p IS NULL)
-WHERE seq.n IS NULL OR idx.n IS NULL;
+ON seq.n = idx.n
+WHERE seq.dist IS DISTINCT FROM idx.dist;
EXPLAIN (COSTS OFF)
-SELECT rank() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
+SELECT row_number() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
FROM kd_point_tbl WHERE p <@ box '(200,200,1000,1000)';
CREATE TEMP TABLE kd_point_tbl_ord_idx2 AS
-SELECT rank() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
+SELECT row_number() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
FROM kd_point_tbl WHERE p <@ box '(200,200,1000,1000)';
SELECT * FROM quad_point_tbl_ord_seq2 seq FULL JOIN kd_point_tbl_ord_idx2 idx
-ON seq.n = idx.n AND
-(seq.dist = idx.dist AND seq.p ~= idx.p OR seq.p IS NULL AND idx.p IS NULL)
-WHERE seq.n IS NULL OR idx.n IS NULL;
+ON seq.n = idx.n
+WHERE seq.dist IS DISTINCT FROM idx.dist;
EXPLAIN (COSTS OFF)
-SELECT rank() OVER (ORDER BY p <-> '333,400') n, p <-> '333,400' dist, p
+SELECT row_number() OVER (ORDER BY p <-> '333,400') n, p <-> '333,400' dist, p
FROM kd_point_tbl WHERE p IS NOT NULL;
CREATE TEMP TABLE kd_point_tbl_ord_idx3 AS
-SELECT rank() OVER (ORDER BY p <-> '333,400') n, p <-> '333,400' dist, p
+SELECT row_number() OVER (ORDER BY p <-> '333,400') n, p <-> '333,400' dist, p
FROM kd_point_tbl WHERE p IS NOT NULL;
SELECT * FROM quad_point_tbl_ord_seq3 seq FULL JOIN kd_point_tbl_ord_idx3 idx
-ON seq.n = idx.n AND
-(seq.dist = idx.dist AND seq.p ~= idx.p OR seq.p IS NULL AND idx.p IS NULL)
-WHERE seq.n IS NULL OR idx.n IS NULL;
+ON seq.n = idx.n
+WHERE seq.dist IS DISTINCT FROM idx.dist;
EXPLAIN (COSTS OFF)
SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdef';
generated by cgit v1.2.3 (git 2.25.1) at 2025-05-24 20:40:09 +0000