Skip to content

Commit 40e42e1

Browse files
anarazelanarazel
committed
Attempt to fix endianess issues in new hash partition test.
The tests added as part of 9fdb675 yield differing results depending on endianess, causing buildfarm failures. As the differences are expected, split the hash partitioning tests into a different file and maintain alternative output. The separate file is so the amount of duplicated output is reduced. David produced the alternative output without a machine to test on, so it's possible this'll require a buildfarm cycle or two to get right. Author: David Rowley Discussion: https://postgr.es/m/CAKJS1f-6f4c2Qhuipe-GY7BKmFd0FMBobRnLS7hVCoAmTszsBg@mail.gmail.com
1 parent 8c3debb commit 40e42e1

7 files changed

+419
-223
lines changed

‎src/test/regress/expected/partition_prune.out

Lines changed: 0 additions & 185 deletions
Original file line numberDiff line numberDiff line change
@@ -1331,188 +1331,3 @@ explain (costs off) select * from rparted_by_int2 where a > 100000000000000;
13311331
(3 rows)
13321332

13331333
drop table lp, coll_pruning, rlp, mc3p, mc2p, boolpart, rp, coll_pruning_multi, like_op_noprune, lparted_by_int2, rparted_by_int2;
1334-
-- hash partitioning
1335-
create table hp (a int, b text) partition by hash (a, b);
1336-
create table hp0 partition of hp for values with (modulus 4, remainder 0);
1337-
create table hp3 partition of hp for values with (modulus 4, remainder 3);
1338-
create table hp1 partition of hp for values with (modulus 4, remainder 1);
1339-
create table hp2 partition of hp for values with (modulus 4, remainder 2);
1340-
insert into hp values (null, null);
1341-
insert into hp values (1, null);
1342-
insert into hp values (1, 'xxx');
1343-
insert into hp values (null, 'xxx');
1344-
insert into hp values (10, 'xxx');
1345-
insert into hp values (10, 'yyy');
1346-
select tableoid::regclass, * from hp order by 1;
1347-
tableoid | a | b
1348-
----------+----+-----
1349-
hp0 | |
1350-
hp0 | 1 |
1351-
hp0 | 1 | xxx
1352-
hp3 | 10 | yyy
1353-
hp1 | | xxx
1354-
hp2 | 10 | xxx
1355-
(6 rows)
1356-
1357-
-- partial keys won't prune, nor would non-equality conditions
1358-
explain (costs off) select * from hp where a = 1;
1359-
QUERY PLAN
1360-
-------------------------
1361-
Append
1362-
-> Seq Scan on hp0
1363-
Filter: (a = 1)
1364-
-> Seq Scan on hp1
1365-
Filter: (a = 1)
1366-
-> Seq Scan on hp2
1367-
Filter: (a = 1)
1368-
-> Seq Scan on hp3
1369-
Filter: (a = 1)
1370-
(9 rows)
1371-
1372-
explain (costs off) select * from hp where b = 'xxx';
1373-
QUERY PLAN
1374-
-----------------------------------
1375-
Append
1376-
-> Seq Scan on hp0
1377-
Filter: (b = 'xxx'::text)
1378-
-> Seq Scan on hp1
1379-
Filter: (b = 'xxx'::text)
1380-
-> Seq Scan on hp2
1381-
Filter: (b = 'xxx'::text)
1382-
-> Seq Scan on hp3
1383-
Filter: (b = 'xxx'::text)
1384-
(9 rows)
1385-
1386-
explain (costs off) select * from hp where a is null;
1387-
QUERY PLAN
1388-
-----------------------------
1389-
Append
1390-
-> Seq Scan on hp0
1391-
Filter: (a IS NULL)
1392-
-> Seq Scan on hp1
1393-
Filter: (a IS NULL)
1394-
-> Seq Scan on hp2
1395-
Filter: (a IS NULL)
1396-
-> Seq Scan on hp3
1397-
Filter: (a IS NULL)
1398-
(9 rows)
1399-
1400-
explain (costs off) select * from hp where b is null;
1401-
QUERY PLAN
1402-
-----------------------------
1403-
Append
1404-
-> Seq Scan on hp0
1405-
Filter: (b IS NULL)
1406-
-> Seq Scan on hp1
1407-
Filter: (b IS NULL)
1408-
-> Seq Scan on hp2
1409-
Filter: (b IS NULL)
1410-
-> Seq Scan on hp3
1411-
Filter: (b IS NULL)
1412-
(9 rows)
1413-
1414-
explain (costs off) select * from hp where a < 1 and b = 'xxx';
1415-
QUERY PLAN
1416-
-------------------------------------------------
1417-
Append
1418-
-> Seq Scan on hp0
1419-
Filter: ((a < 1) AND (b = 'xxx'::text))
1420-
-> Seq Scan on hp1
1421-
Filter: ((a < 1) AND (b = 'xxx'::text))
1422-
-> Seq Scan on hp2
1423-
Filter: ((a < 1) AND (b = 'xxx'::text))
1424-
-> Seq Scan on hp3
1425-
Filter: ((a < 1) AND (b = 'xxx'::text))
1426-
(9 rows)
1427-
1428-
explain (costs off) select * from hp where a <> 1 and b = 'yyy';
1429-
QUERY PLAN
1430-
--------------------------------------------------
1431-
Append
1432-
-> Seq Scan on hp0
1433-
Filter: ((a <> 1) AND (b = 'yyy'::text))
1434-
-> Seq Scan on hp1
1435-
Filter: ((a <> 1) AND (b = 'yyy'::text))
1436-
-> Seq Scan on hp2
1437-
Filter: ((a <> 1) AND (b = 'yyy'::text))
1438-
-> Seq Scan on hp3
1439-
Filter: ((a <> 1) AND (b = 'yyy'::text))
1440-
(9 rows)
1441-
1442-
-- pruning should work if non-null values are provided for all the keys
1443-
explain (costs off) select * from hp where a is null and b is null;
1444-
QUERY PLAN
1445-
-----------------------------------------------
1446-
Append
1447-
-> Seq Scan on hp0
1448-
Filter: ((a IS NULL) AND (b IS NULL))
1449-
(3 rows)
1450-
1451-
explain (costs off) select * from hp where a = 1 and b is null;
1452-
QUERY PLAN
1453-
-------------------------------------------
1454-
Append
1455-
-> Seq Scan on hp0
1456-
Filter: ((b IS NULL) AND (a = 1))
1457-
(3 rows)
1458-
1459-
explain (costs off) select * from hp where a = 1 and b = 'xxx';
1460-
QUERY PLAN
1461-
-------------------------------------------------
1462-
Append
1463-
-> Seq Scan on hp0
1464-
Filter: ((a = 1) AND (b = 'xxx'::text))
1465-
(3 rows)
1466-
1467-
explain (costs off) select * from hp where a is null and b = 'xxx';
1468-
QUERY PLAN
1469-
-----------------------------------------------------
1470-
Append
1471-
-> Seq Scan on hp1
1472-
Filter: ((a IS NULL) AND (b = 'xxx'::text))
1473-
(3 rows)
1474-
1475-
explain (costs off) select * from hp where a = 10 and b = 'xxx';
1476-
QUERY PLAN
1477-
--------------------------------------------------
1478-
Append
1479-
-> Seq Scan on hp2
1480-
Filter: ((a = 10) AND (b = 'xxx'::text))
1481-
(3 rows)
1482-
1483-
explain (costs off) select * from hp where a = 10 and b = 'yyy';
1484-
QUERY PLAN
1485-
--------------------------------------------------
1486-
Append
1487-
-> Seq Scan on hp3
1488-
Filter: ((a = 10) AND (b = 'yyy'::text))
1489-
(3 rows)
1490-
1491-
explain (costs off) select * from hp where (a = 10 and b = 'yyy') or (a = 10 and b = 'xxx') or (a is null and b is null);
1492-
QUERY PLAN
1493-
-------------------------------------------------------------------------------------------------------------------------
1494-
Append
1495-
-> Seq Scan on hp0
1496-
Filter: (((a = 10) AND (b = 'yyy'::text)) OR ((a = 10) AND (b = 'xxx'::text)) OR ((a IS NULL) AND (b IS NULL)))
1497-
-> Seq Scan on hp2
1498-
Filter: (((a = 10) AND (b = 'yyy'::text)) OR ((a = 10) AND (b = 'xxx'::text)) OR ((a IS NULL) AND (b IS NULL)))
1499-
-> Seq Scan on hp3
1500-
Filter: (((a = 10) AND (b = 'yyy'::text)) OR ((a = 10) AND (b = 'xxx'::text)) OR ((a IS NULL) AND (b IS NULL)))
1501-
(7 rows)
1502-
1503-
-- hash partitiong pruning doesn't occur with <> operator clauses
1504-
explain (costs off) select * from hp where a <> 1 and b <> 'xxx';
1505-
QUERY PLAN
1506-
---------------------------------------------------
1507-
Append
1508-
-> Seq Scan on hp0
1509-
Filter: ((a <> 1) AND (b <> 'xxx'::text))
1510-
-> Seq Scan on hp1
1511-
Filter: ((a <> 1) AND (b <> 'xxx'::text))
1512-
-> Seq Scan on hp2
1513-
Filter: ((a <> 1) AND (b <> 'xxx'::text))
1514-
-> Seq Scan on hp3
1515-
Filter: ((a <> 1) AND (b <> 'xxx'::text))
1516-
(9 rows)
1517-
1518-
drop table hp;

‎src/test/regress/expected/partition_prune_hash.out

Lines changed: 189 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,189 @@
1+
--
2+
-- Test Partition pruning for HASH partitioning
3+
-- We keep this as a seperate test as hash functions return
4+
-- values will vary based on CPU architecture.
5+
--
6+
create table hp (a int, b text) partition by hash (a, b);
7+
create table hp0 partition of hp for values with (modulus 4, remainder 0);
8+
create table hp3 partition of hp for values with (modulus 4, remainder 3);
9+
create table hp1 partition of hp for values with (modulus 4, remainder 1);
10+
create table hp2 partition of hp for values with (modulus 4, remainder 2);
11+
insert into hp values (null, null);
12+
insert into hp values (1, null);
13+
insert into hp values (1, 'xxx');
14+
insert into hp values (null, 'xxx');
15+
insert into hp values (10, 'xxx');
16+
insert into hp values (10, 'yyy');
17+
select tableoid::regclass, * from hp order by 1;
18+
tableoid | a | b
19+
----------+----+-----
20+
hp0 | |
21+
hp0 | 1 |
22+
hp0 | 1 | xxx
23+
hp3 | 10 | yyy
24+
hp1 | | xxx
25+
hp2 | 10 | xxx
26+
(6 rows)
27+
28+
-- partial keys won't prune, nor would non-equality conditions
29+
explain (costs off) select * from hp where a = 1;
30+
QUERY PLAN
31+
-------------------------
32+
Append
33+
-> Seq Scan on hp0
34+
Filter: (a = 1)
35+
-> Seq Scan on hp1
36+
Filter: (a = 1)
37+
-> Seq Scan on hp2
38+
Filter: (a = 1)
39+
-> Seq Scan on hp3
40+
Filter: (a = 1)
41+
(9 rows)
42+
43+
explain (costs off) select * from hp where b = 'xxx';
44+
QUERY PLAN
45+
-----------------------------------
46+
Append
47+
-> Seq Scan on hp0
48+
Filter: (b = 'xxx'::text)
49+
-> Seq Scan on hp1
50+
Filter: (b = 'xxx'::text)
51+
-> Seq Scan on hp2
52+
Filter: (b = 'xxx'::text)
53+
-> Seq Scan on hp3
54+
Filter: (b = 'xxx'::text)
55+
(9 rows)
56+
57+
explain (costs off) select * from hp where a is null;
58+
QUERY PLAN
59+
-----------------------------
60+
Append
61+
-> Seq Scan on hp0
62+
Filter: (a IS NULL)
63+
-> Seq Scan on hp1
64+
Filter: (a IS NULL)
65+
-> Seq Scan on hp2
66+
Filter: (a IS NULL)
67+
-> Seq Scan on hp3
68+
Filter: (a IS NULL)
69+
(9 rows)
70+
71+
explain (costs off) select * from hp where b is null;
72+
QUERY PLAN
73+
-----------------------------
74+
Append
75+
-> Seq Scan on hp0
76+
Filter: (b IS NULL)
77+
-> Seq Scan on hp1
78+
Filter: (b IS NULL)
79+
-> Seq Scan on hp2
80+
Filter: (b IS NULL)
81+
-> Seq Scan on hp3
82+
Filter: (b IS NULL)
83+
(9 rows)
84+
85+
explain (costs off) select * from hp where a < 1 and b = 'xxx';
86+
QUERY PLAN
87+
-------------------------------------------------
88+
Append
89+
-> Seq Scan on hp0
90+
Filter: ((a < 1) AND (b = 'xxx'::text))
91+
-> Seq Scan on hp1
92+
Filter: ((a < 1) AND (b = 'xxx'::text))
93+
-> Seq Scan on hp2
94+
Filter: ((a < 1) AND (b = 'xxx'::text))
95+
-> Seq Scan on hp3
96+
Filter: ((a < 1) AND (b = 'xxx'::text))
97+
(9 rows)
98+
99+
explain (costs off) select * from hp where a <> 1 and b = 'yyy';
100+
QUERY PLAN
101+
--------------------------------------------------
102+
Append
103+
-> Seq Scan on hp0
104+
Filter: ((a <> 1) AND (b = 'yyy'::text))
105+
-> Seq Scan on hp1
106+
Filter: ((a <> 1) AND (b = 'yyy'::text))
107+
-> Seq Scan on hp2
108+
Filter: ((a <> 1) AND (b = 'yyy'::text))
109+
-> Seq Scan on hp3
110+
Filter: ((a <> 1) AND (b = 'yyy'::text))
111+
(9 rows)
112+
113+
-- pruning should work if non-null values are provided for all the keys
114+
explain (costs off) select * from hp where a is null and b is null;
115+
QUERY PLAN
116+
-----------------------------------------------
117+
Append
118+
-> Seq Scan on hp0
119+
Filter: ((a IS NULL) AND (b IS NULL))
120+
(3 rows)
121+
122+
explain (costs off) select * from hp where a = 1 and b is null;
123+
QUERY PLAN
124+
-------------------------------------------
125+
Append
126+
-> Seq Scan on hp0
127+
Filter: ((b IS NULL) AND (a = 1))
128+
(3 rows)
129+
130+
explain (costs off) select * from hp where a = 1 and b = 'xxx';
131+
QUERY PLAN
132+
-------------------------------------------------
133+
Append
134+
-> Seq Scan on hp0
135+
Filter: ((a = 1) AND (b = 'xxx'::text))
136+
(3 rows)
137+
138+
explain (costs off) select * from hp where a is null and b = 'xxx';
139+
QUERY PLAN
140+
-----------------------------------------------------
141+
Append
142+
-> Seq Scan on hp1
143+
Filter: ((a IS NULL) AND (b = 'xxx'::text))
144+
(3 rows)
145+
146+
explain (costs off) select * from hp where a = 10 and b = 'xxx';
147+
QUERY PLAN
148+
--------------------------------------------------
149+
Append
150+
-> Seq Scan on hp2
151+
Filter: ((a = 10) AND (b = 'xxx'::text))
152+
(3 rows)
153+
154+
explain (costs off) select * from hp where a = 10 and b = 'yyy';
155+
QUERY PLAN
156+
--------------------------------------------------
157+
Append
158+
-> Seq Scan on hp3
159+
Filter: ((a = 10) AND (b = 'yyy'::text))
160+
(3 rows)
161+
162+
explain (costs off) select * from hp where (a = 10 and b = 'yyy') or (a = 10 and b = 'xxx') or (a is null and b is null);
163+
QUERY PLAN
164+
-------------------------------------------------------------------------------------------------------------------------
165+
Append
166+
-> Seq Scan on hp0
167+
Filter: (((a = 10) AND (b = 'yyy'::text)) OR ((a = 10) AND (b = 'xxx'::text)) OR ((a IS NULL) AND (b IS NULL)))
168+
-> Seq Scan on hp2
169+
Filter: (((a = 10) AND (b = 'yyy'::text)) OR ((a = 10) AND (b = 'xxx'::text)) OR ((a IS NULL) AND (b IS NULL)))
170+
-> Seq Scan on hp3
171+
Filter: (((a = 10) AND (b = 'yyy'::text)) OR ((a = 10) AND (b = 'xxx'::text)) OR ((a IS NULL) AND (b IS NULL)))
172+
(7 rows)
173+
174+
-- hash partitiong pruning doesn't occur with <> operator clauses
175+
explain (costs off) select * from hp where a <> 1 and b <> 'xxx';
176+
QUERY PLAN
177+
---------------------------------------------------
178+
Append
179+
-> Seq Scan on hp0
180+
Filter: ((a <> 1) AND (b <> 'xxx'::text))
181+
-> Seq Scan on hp1
182+
Filter: ((a <> 1) AND (b <> 'xxx'::text))
183+
-> Seq Scan on hp2
184+
Filter: ((a <> 1) AND (b <> 'xxx'::text))
185+
-> Seq Scan on hp3
186+
Filter: ((a <> 1) AND (b <> 'xxx'::text))
187+
(9 rows)
188+
189+
drop table hp;

0 commit comments

Comments
 (0)