Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly
Appearance settings

Commit f82ab18

Browse filesBrowse files
Green-ChanGreen-Chan
authored
Stabilize array test. (#135)
Make this test output more independent of PostgreSQL server version and system. Authored-by: Karina Litskevich <k.lickevich@postgrespro.ru>
1 parent 34619f9 commit f82ab18
Copy full SHA for f82ab18

File tree

5 files changed

+132
-1850
lines changed
Filter options

5 files changed

+132
-1850
lines changed

‎expected/array.out

Copy file name to clipboardExpand all lines: expected/array.out
+49-25Lines changed: 49 additions & 25 deletions
Original file line numberDiff line numberDiff line change
@@ -7,12 +7,6 @@
77
* array.sql and array_1.sql
88
* --------------------
99
* Test output for 64-bit and 32-bit systems respectively.
10-
*
11-
* --------------------
12-
* array_2.sql and array_3.sql
13-
* --------------------
14-
* Since 6ed83d5fa55c in PostgreSQL 17, the order of rows
15-
* in the output has been changed.
1610
*/
1711
set enable_seqscan=off;
1812
set enable_sort=off;
@@ -859,41 +853,71 @@ EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i % '{}';
859853
DROP INDEX idx_array;
860854
/*
861855
* Check ordering using distance operator
856+
*
857+
* We want to check that index scan provides us correct ordering by distance
858+
* operator. File 'data/rum_array.data' contains two arrays that statisfy
859+
* i @> '{23,20}' and have finite distance i <=> '{51}', and a bunch of arrays
860+
* that statisfy i @> '{23,20}' and have infinite distance i <=> '{51}'.
861+
*
862+
* When ordering by distance the order of this bunch of arrays with infinite
863+
* distance is not determined and may depend of PostgreSQL version and system.
864+
* We don't add another sort expression to ORDER BY because that might cause
865+
* the planner to avoid using the index. Instead, we replace arrays that have
866+
* infinite distance with {-1} to unambiguously determine the test output.
867+
*
868+
* 'Infinity' is printed differently in the output in different PostgreSQL
869+
* versions, so we replace it with -1.
862870
*/
863871
CREATE TABLE test_array_order (
864872
i int2[]
865873
);
866874
\copy test_array_order(i) from 'data/rum_array.data';
867875
CREATE INDEX idx_array_order ON test_array_order USING rum (i rum_anyarray_ops);
876+
/*
877+
* Check that plan of the query uses ordering provided by index scan
878+
*/
868879
EXPLAIN (COSTS OFF)
869-
SELECT *, i <=> '{51}' from test_array_order WHERE i @> '{23,20}' order by i <=> '{51}';
870-
QUERY PLAN
871-
------------------------------------------------------
872-
Index Scan using idx_array_order on test_array_order
873-
Index Cond: (i @> '{23,20}'::smallint[])
874-
Order By: (i <=> '{51}'::smallint[])
875-
(3 rows)
880+
SELECT
881+
CASE WHEN distance = 'Infinity' THEN '{-1}'
882+
ELSE i
883+
END i,
884+
CASE WHEN distance = 'Infinity' THEN -1
885+
ELSE distance::numeric(18,14)
886+
END distance
887+
FROM
888+
(SELECT *, (i <=> '{51}') AS distance
889+
FROM test_array_order WHERE i @> '{23,20}' ORDER BY distance) t;
890+
QUERY PLAN
891+
------------------------------------------------------------
892+
Subquery Scan on t
893+
-> Index Scan using idx_array_order on test_array_order
894+
Index Cond: (i @> '{23,20}'::smallint[])
895+
Order By: (i <=> '{51}'::smallint[])
896+
(4 rows)
876897

877-
SELECT i,
898+
SELECT
899+
CASE WHEN distance = 'Infinity' THEN '{-1}'
900+
ELSE i
901+
END i,
878902
CASE WHEN distance = 'Infinity' THEN -1
879903
ELSE distance::numeric(18,14)
880904
END distance
881905
FROM
882906
(SELECT *, (i <=> '{51}') AS distance
883-
FROM test_array_order WHERE i @> '{23,20}' ORDER BY i <=> '{51}') t;
907+
FROM test_array_order WHERE i @> '{23,20}' ORDER BY distance) t;
884908
i | distance
885909
---------------------+------------------
886910
{20,23,51} | 1.73205080756888
887911
{33,51,20,77,23,65} | 2.44948974278318
888-
{23,76,34,23,2,20} | -1
889-
{20,60,45,23,29} | -1
890-
{23,89,38,20,40,95} | -1
891-
{23,20,72} | -1
892-
{73,23,20} | -1
893-
{6,97,20,89,23} | -1
894-
{20,98,30,23,1,66} | -1
895-
{57,23,39,46,50,20} | -1
896-
{81,20,26,22,23} | -1
897-
{18,23,10,90,15,20} | -1
912+
{-1} | -1
913+
{-1} | -1
914+
{-1} | -1
915+
{-1} | -1
916+
{-1} | -1
917+
{-1} | -1
918+
{-1} | -1
919+
{-1} | -1
920+
{-1} | -1
921+
{-1} | -1
898922
(12 rows)
899923

‎expected/array_1.out

Copy file name to clipboardExpand all lines: expected/array_1.out
+49-25Lines changed: 49 additions & 25 deletions
Original file line numberDiff line numberDiff line change
@@ -7,12 +7,6 @@
77
* array.sql and array_1.sql
88
* --------------------
99
* Test output for 64-bit and 32-bit systems respectively.
10-
*
11-
* --------------------
12-
* array_2.sql and array_3.sql
13-
* --------------------
14-
* Since 6ed83d5fa55c in PostgreSQL 17, the order of rows
15-
* in the output has been changed.
1610
*/
1711
set enable_seqscan=off;
1812
set enable_sort=off;
@@ -852,41 +846,71 @@ EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i % '{}';
852846
DROP INDEX idx_array;
853847
/*
854848
* Check ordering using distance operator
849+
*
850+
* We want to check that index scan provides us correct ordering by distance
851+
* operator. File 'data/rum_array.data' contains two arrays that statisfy
852+
* i @> '{23,20}' and have finite distance i <=> '{51}', and a bunch of arrays
853+
* that statisfy i @> '{23,20}' and have infinite distance i <=> '{51}'.
854+
*
855+
* When ordering by distance the order of this bunch of arrays with infinite
856+
* distance is not determined and may depend of PostgreSQL version and system.
857+
* We don't add another sort expression to ORDER BY because that might cause
858+
* the planner to avoid using the index. Instead, we replace arrays that have
859+
* infinite distance with {-1} to unambiguously determine the test output.
860+
*
861+
* 'Infinity' is printed differently in the output in different PostgreSQL
862+
* versions, so we replace it with -1.
855863
*/
856864
CREATE TABLE test_array_order (
857865
i int2[]
858866
);
859867
\copy test_array_order(i) from 'data/rum_array.data';
860868
CREATE INDEX idx_array_order ON test_array_order USING rum (i rum_anyarray_ops);
869+
/*
870+
* Check that plan of the query uses ordering provided by index scan
871+
*/
861872
EXPLAIN (COSTS OFF)
862-
SELECT *, i <=> '{51}' from test_array_order WHERE i @> '{23,20}' order by i <=> '{51}';
863-
QUERY PLAN
864-
------------------------------------------------------
865-
Index Scan using idx_array_order on test_array_order
866-
Index Cond: (i @> '{23,20}'::smallint[])
867-
Order By: (i <=> '{51}'::smallint[])
868-
(3 rows)
873+
SELECT
874+
CASE WHEN distance = 'Infinity' THEN '{-1}'
875+
ELSE i
876+
END i,
877+
CASE WHEN distance = 'Infinity' THEN -1
878+
ELSE distance::numeric(18,14)
879+
END distance
880+
FROM
881+
(SELECT *, (i <=> '{51}') AS distance
882+
FROM test_array_order WHERE i @> '{23,20}' ORDER BY distance) t;
883+
QUERY PLAN
884+
------------------------------------------------------------
885+
Subquery Scan on t
886+
-> Index Scan using idx_array_order on test_array_order
887+
Index Cond: (i @> '{23,20}'::smallint[])
888+
Order By: (i <=> '{51}'::smallint[])
889+
(4 rows)
869890

870-
SELECT i,
891+
SELECT
892+
CASE WHEN distance = 'Infinity' THEN '{-1}'
893+
ELSE i
894+
END i,
871895
CASE WHEN distance = 'Infinity' THEN -1
872896
ELSE distance::numeric(18,14)
873897
END distance
874898
FROM
875899
(SELECT *, (i <=> '{51}') AS distance
876-
FROM test_array_order WHERE i @> '{23,20}' ORDER BY i <=> '{51}') t;
900+
FROM test_array_order WHERE i @> '{23,20}' ORDER BY distance) t;
877901
i | distance
878902
---------------------+------------------
879903
{20,23,51} | 1.73205080756888
880904
{33,51,20,77,23,65} | 2.44948974278318
881-
{23,76,34,23,2,20} | -1
882-
{20,60,45,23,29} | -1
883-
{23,89,38,20,40,95} | -1
884-
{23,20,72} | -1
885-
{73,23,20} | -1
886-
{6,97,20,89,23} | -1
887-
{20,98,30,23,1,66} | -1
888-
{57,23,39,46,50,20} | -1
889-
{81,20,26,22,23} | -1
890-
{18,23,10,90,15,20} | -1
905+
{-1} | -1
906+
{-1} | -1
907+
{-1} | -1
908+
{-1} | -1
909+
{-1} | -1
910+
{-1} | -1
911+
{-1} | -1
912+
{-1} | -1
913+
{-1} | -1
914+
{-1} | -1
891915
(12 rows)
892916

0 commit comments

Comments
0 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.