I ran the src/test/regressplans.sh script, which runs the regression tests
under exclusion of various join and scan types. Without merge joins (-fm)
I get an assertion failure in opr_sanity.
The query is:
SELECT p1.oid, p1.aggname
FROM pg_aggregate as p1
WHERE p1.aggfinalfn = 0 AND p1.aggfinaltype != p1.aggtranstype;
(The plan for this query is a seq scan on pg_aggregate.)
The backtrace is:
#0 0x4012b131 in __kill () from /lib/libc.so.6
#1 0x4012aead in raise (sig=6) at ../sysdeps/posix/raise.c:27
#2 0x4012c534 in abort () at ../sysdeps/generic/abort.c:88
#3 0x8149b98 in ExceptionalCondition (
conditionName=0x81988a0 "!(((file) > 0 && (file) < (int) SizeVfdCache
&& VfdCache[file].fileName != ((void *)0)))", exceptionP=0x81b93c8,
detail=0x0,
fileName=0x8198787 "fd.c", lineNumber=851) at assert.c:70
#4 0x8105e6e in FileSeek (file=33, offset=0, whence=2) at fd.c:851
#5 0x810e692 in _mdnblocks (file=33, blcksz=8192) at md.c:1095
#6 0x810de9b in mdnblocks (reln=0x403a35f4) at md.c:667
#7 0x810ec80 in smgrnblocks (which=0, reln=0x403a35f4) at smgr.c:441
#8 0x8103303 in RelationGetNumberOfBlocks (relation=0x403a35f4)
at xlog_bufmgr.c:1161
#9 0x8072b04 in initscan (scan=0x822af94, relation=0x403a35f4, atend=0,
nkeys=0, key=0x0) at heapam.c:128
#10 0x8073fa0 in heap_beginscan (relation=0x403a35f4, atend=0,
snapshot=0x822b438, nkeys=0, key=0x0) at heapam.c:811
#11 0x80c69e4 in ExecBeginScan (relation=0x403a35f4, nkeys=0, skeys=0x0,
isindex=0, dir=ForwardScanDirection, snapshot=0x822b438) at
execAmi.c:156
#12 0x80c6986 in ExecOpenScanR (relOid=16960, nkeys=0, skeys=0x0,
isindex=0 '\000', dir=ForwardScanDirection, snapshot=0x822b438,
returnRelation=0xbffff074, returnScanDesc=0xbffff078) at execAmi.c:104
#13 0x80d098c in InitScanRelation (node=0x822ae60, estate=0x822aeec,
scanstate=0x822b084) at nodeSeqscan.c:172
#14 0x80d0a62 in ExecInitSeqScan (node=0x822ae60, estate=0x822aeec,
parent=0x0)
at nodeSeqscan.c:242
#15 0x80c917f in ExecInitNode (node=0x822ae60, estate=0x822aeec,
parent=0x0)
at execProcnode.c:152
#16 0x80c7be9 in InitPlan (operation=CMD_SELECT, parseTree=0x823b108,
plan=0x822ae60, estate=0x822aeec) at execMain.c:621
#17 0x80c765b in ExecutorStart (queryDesc=0x822b41c, estate=0x822aeec)
at execMain.c:135
#18 0x8111439 in ProcessQuery (parsetree=0x823b108, plan=0x822ae60,
dest=Remote) at pquery.c:263
#19 0x810ffea in pg_exec_query_string (
query_string=0x823a548 "SELECT p1.oid, p1.aggname\nFROM pg_aggregate
as p1\nWHERE p1.aggfinalfn = 0 AND p1.aggfinaltype != p1.aggtranstype;",
dest=Remote,
parse_context=0x81f13b0) at postgres.c:818
<snipped>
This failure is completely reproduceable by running
src/test/regress$ PGOPTIONS=-fm ./pg_regress opr_sanity
The problem also happens with the setting '-fn -fm', but *not* with the
setting '-fm -fh'. (Adding or removing -fs or -fi doesn't affect the
outcome.)
The only other two failures are the join test when both merge and hash
joins are disabled and alter_table without index scans. Both seem
harmless; see attached diffs.
The former is related to outer joins apparently not working with nest
loops. The latter is a missing ORDER BY, which I'm inclined to fix.
--
Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
*** ./expected/alter_table.out Tue Aug 29 13:22:31 2000
--- ./results/alter_table.out Wed Nov 22 15:49:21 2000
***************
*** 103,128 ****
SELECT unique1 FROM ten_k WHERE unique1 < 20;
unique1
---------
! 0
! 1
! 2
! 3
4
! 5
6
! 7
! 8
9
! 10
! 11
! 12
13
! 14
! 15
! 16
! 17
! 18
19
(20 rows)
-- 20 values, sorted
--- 103,128 ----
SELECT unique1 FROM ten_k WHERE unique1 < 20;
unique1
---------
! 18
! 15
4
! 2
! 1
6
! 14
9
! 8
! 5
! 3
13
! 12
19
+ 17
+ 11
+ 7
+ 10
+ 16
+ 0
(20 rows)
-- 20 values, sorted
***************
*** 262,272 ****
SELECT unique1 FROM tenk1 WHERE unique1 < 5;
unique1
---------
! 0
! 1
2
3
! 4
(5 rows)
-- FOREIGN KEY CONSTRAINT adding TEST
--- 262,272 ----
SELECT unique1 FROM tenk1 WHERE unique1 < 5;
unique1
---------
! 4
2
+ 1
3
! 0
(5 rows)
-- FOREIGN KEY CONSTRAINT adding TEST
======================================================================
*** ./expected/join.out Mon Nov 6 20:23:47 2000
--- ./results/join.out Wed Nov 22 15:37:01 2000
***************
*** 214,226 ****
WHERE t1.a = t2.d;
xxx | a | e
-----+---+----
- | 0 |
| 1 | -1
| 2 | 2
| 2 | 4
| 3 | -3
| 5 | -5
| 5 | -5
(7 rows)
--
--- 214,226 ----
WHERE t1.a = t2.d;
xxx | a | e
-----+---+----
| 1 | -1
| 2 | 2
| 2 | 4
| 3 | -3
| 5 | -5
| 5 | -5
+ | 0 |
(7 rows)
--
***************
*** 1567,1579 ****
FROM J1_TBL INNER JOIN J2_TBL USING (i);
xxx | i | j | t | k
-----+---+---+-------+----
- | 0 | | zero |
| 1 | 4 | one | -1
| 2 | 3 | two | 2
| 2 | 3 | two | 4
| 3 | 2 | three | -3
| 5 | 0 | five | -5
| 5 | 0 | five | -5
(7 rows)
-- Same as above, slightly different syntax
--- 1567,1579 ----
FROM J1_TBL INNER JOIN J2_TBL USING (i);
xxx | i | j | t | k
-----+---+---+-------+----
| 1 | 4 | one | -1
| 2 | 3 | two | 2
| 2 | 3 | two | 4
| 3 | 2 | three | -3
| 5 | 0 | five | -5
| 5 | 0 | five | -5
+ | 0 | | zero |
(7 rows)
-- Same as above, slightly different syntax
***************
*** 1581,1593 ****
FROM J1_TBL JOIN J2_TBL USING (i);
xxx | i | j | t | k
-----+---+---+-------+----
- | 0 | | zero |
| 1 | 4 | one | -1
| 2 | 3 | two | 2
| 2 | 3 | two | 4
| 3 | 2 | three | -3
| 5 | 0 | five | -5
| 5 | 0 | five | -5
(7 rows)
SELECT '' AS "xxx", *
--- 1581,1593 ----
FROM J1_TBL JOIN J2_TBL USING (i);
xxx | i | j | t | k
-----+---+---+-------+----
| 1 | 4 | one | -1
| 2 | 3 | two | 2
| 2 | 3 | two | 4
| 3 | 2 | three | -3
| 5 | 0 | five | -5
| 5 | 0 | five | -5
+ | 0 | | zero |
(7 rows)
SELECT '' AS "xxx", *
***************
*** 1623,1657 ****
FROM J1_TBL NATURAL JOIN J2_TBL;
xxx | i | j | t | k
-----+---+---+-------+----
- | 0 | | zero |
| 1 | 4 | one | -1
| 2 | 3 | two | 2
| 2 | 3 | two | 4
| 3 | 2 | three | -3
| 5 | 0 | five | -5
| 5 | 0 | five | -5
(7 rows)
SELECT '' AS "xxx", *
FROM J1_TBL t1 (a, b, c) NATURAL JOIN J2_TBL t2 (a, d);
xxx | a | b | c | d
-----+---+---+-------+----
- | 0 | | zero |
| 1 | 4 | one | -1
| 2 | 3 | two | 2
| 2 | 3 | two | 4
| 3 | 2 | three | -3
| 5 | 0 | five | -5
| 5 | 0 | five | -5
(7 rows)
SELECT '' AS "xxx", *
FROM J1_TBL t1 (a, b, c) NATURAL JOIN J2_TBL t2 (d, a);
xxx | a | b | c | d
-----+---+---+------+---
- | 0 | | zero |
| 2 | 3 | two | 2
| 4 | 1 | four | 2
(3 rows)
-- mismatch number of columns
--- 1623,1657 ----
FROM J1_TBL NATURAL JOIN J2_TBL;
xxx | i | j | t | k
-----+---+---+-------+----
| 1 | 4 | one | -1
| 2 | 3 | two | 2
| 2 | 3 | two | 4
| 3 | 2 | three | -3
| 5 | 0 | five | -5
| 5 | 0 | five | -5
+ | 0 | | zero |
(7 rows)
SELECT '' AS "xxx", *
FROM J1_TBL t1 (a, b, c) NATURAL JOIN J2_TBL t2 (a, d);
xxx | a | b | c | d
-----+---+---+-------+----
| 1 | 4 | one | -1
| 2 | 3 | two | 2
| 2 | 3 | two | 4
| 3 | 2 | three | -3
| 5 | 0 | five | -5
| 5 | 0 | five | -5
+ | 0 | | zero |
(7 rows)
SELECT '' AS "xxx", *
FROM J1_TBL t1 (a, b, c) NATURAL JOIN J2_TBL t2 (d, a);
xxx | a | b | c | d
-----+---+---+------+---
| 2 | 3 | two | 2
| 4 | 1 | four | 2
+ | 0 | | zero |
(3 rows)
-- mismatch number of columns
***************
*** 1660,1672 ****
FROM J1_TBL t1 (a, b) NATURAL JOIN J2_TBL t2 (a);
xxx | a | b | t | k
-----+---+---+-------+----
- | 0 | | zero |
| 1 | 4 | one | -1
| 2 | 3 | two | 2
| 2 | 3 | two | 4
| 3 | 2 | three | -3
| 5 | 0 | five | -5
| 5 | 0 | five | -5
(7 rows)
--
--- 1660,1672 ----
FROM J1_TBL t1 (a, b) NATURAL JOIN J2_TBL t2 (a);
xxx | a | b | t | k
-----+---+---+-------+----
| 1 | 4 | one | -1
| 2 | 3 | two | 2
| 2 | 3 | two | 4
| 3 | 2 | three | -3
| 5 | 0 | five | -5
| 5 | 0 | five | -5
+ | 0 | | zero |
(7 rows)
--
***************
*** 1676,1697 ****
FROM J1_TBL JOIN J2_TBL ON (J1_TBL.i = J2_TBL.i);
xxx | i | j | t | i | k
-----+---+---+-------+---+----
- | 0 | | zero | 0 |
| 1 | 4 | one | 1 | -1
| 2 | 3 | two | 2 | 2
| 2 | 3 | two | 2 | 4
| 3 | 2 | three | 3 | -3
| 5 | 0 | five | 5 | -5
| 5 | 0 | five | 5 | -5
(7 rows)
SELECT '' AS "xxx", *
FROM J1_TBL JOIN J2_TBL ON (J1_TBL.i = J2_TBL.k);
xxx | i | j | t | i | k
-----+---+---+------+---+---
- | 0 | | zero | | 0
| 2 | 3 | two | 2 | 2
| 4 | 1 | four | 2 | 4
(3 rows)
--
--- 1676,1697 ----
FROM J1_TBL JOIN J2_TBL ON (J1_TBL.i = J2_TBL.i);
xxx | i | j | t | i | k
-----+---+---+-------+---+----
| 1 | 4 | one | 1 | -1
| 2 | 3 | two | 2 | 2
| 2 | 3 | two | 2 | 4
| 3 | 2 | three | 3 | -3
| 5 | 0 | five | 5 | -5
| 5 | 0 | five | 5 | -5
+ | 0 | | zero | 0 |
(7 rows)
SELECT '' AS "xxx", *
FROM J1_TBL JOIN J2_TBL ON (J1_TBL.i = J2_TBL.k);
xxx | i | j | t | i | k
-----+---+---+------+---+---
| 2 | 3 | two | 2 | 2
| 4 | 1 | four | 2 | 4
+ | 0 | | zero | | 0
(3 rows)
--
***************
*** 1720,1726 ****
FROM J1_TBL LEFT OUTER JOIN J2_TBL USING (i);
xxx | i | j | t | k
-----+---+---+-------+----
- | 0 | | zero |
| 1 | 4 | one | -1
| 2 | 3 | two | 2
| 2 | 3 | two | 4
--- 1720,1725 ----
***************
*** 1731,1736 ****
--- 1730,1736 ----
| 6 | 6 | six |
| 7 | 7 | seven |
| 8 | 8 | eight |
+ | 0 | | zero |
| | | null |
| | 0 | zero |
(13 rows)
***************
*** 1739,1745 ****
FROM J1_TBL LEFT JOIN J2_TBL USING (i);
xxx | i | j | t | k
-----+---+---+-------+----
- | 0 | | zero |
| 1 | 4 | one | -1
| 2 | 3 | two | 2
| 2 | 3 | two | 4
--- 1739,1744 ----
***************
*** 1750,1755 ****
--- 1749,1755 ----
| 6 | 6 | six |
| 7 | 7 | seven |
| 8 | 8 | eight |
+ | 0 | | zero |
| | | null |
| | 0 | zero |
(13 rows)
***************
*** 1758,1770 ****
FROM J1_TBL RIGHT OUTER JOIN J2_TBL USING (i);
xxx | i | j | t | k
-----+---+---+-------+----
- | 0 | | zero |
| 1 | 4 | one | -1
| 2 | 3 | two | 2
- | 2 | 3 | two | 4
| 3 | 2 | three | -3
| 5 | 0 | five | -5
| 5 | 0 | five | -5
| | | |
| | | | 0
(9 rows)
--- 1758,1770 ----
FROM J1_TBL RIGHT OUTER JOIN J2_TBL USING (i);
xxx | i | j | t | k
-----+---+---+-------+----
| 1 | 4 | one | -1
| 2 | 3 | two | 2
| 3 | 2 | three | -3
+ | 2 | 3 | two | 4
| 5 | 0 | five | -5
| 5 | 0 | five | -5
+ | 0 | | zero |
| | | |
| | | | 0
(9 rows)
***************
*** 1773,1831 ****
FROM J1_TBL RIGHT JOIN J2_TBL USING (i);
xxx | i | j | t | k
-----+---+---+-------+----
- | 0 | | zero |
| 1 | 4 | one | -1
| 2 | 3 | two | 2
- | 2 | 3 | two | 4
| 3 | 2 | three | -3
| 5 | 0 | five | -5
| 5 | 0 | five | -5
| | | |
| | | | 0
(9 rows)
SELECT '' AS "xxx", *
FROM J1_TBL FULL OUTER JOIN J2_TBL USING (i);
! xxx | i | j | t | k
! -----+---+---+-------+----
! | 0 | | zero |
! | 1 | 4 | one | -1
! | 2 | 3 | two | 2
! | 2 | 3 | two | 4
! | 3 | 2 | three | -3
! | 4 | 1 | four |
! | 5 | 0 | five | -5
! | 5 | 0 | five | -5
! | | | |
! | | | | 0
! | 6 | 6 | six |
! | 7 | 7 | seven |
! | 8 | 8 | eight |
! | | | null |
! | | 0 | zero |
! (15 rows)
!
SELECT '' AS "xxx", *
FROM J1_TBL FULL JOIN J2_TBL USING (i);
! xxx | i | j | t | k
! -----+---+---+-------+----
! | 0 | | zero |
! | 1 | 4 | one | -1
! | 2 | 3 | two | 2
! | 2 | 3 | two | 4
! | 3 | 2 | three | -3
! | 4 | 1 | four |
! | 5 | 0 | five | -5
! | 5 | 0 | five | -5
! | | | |
! | | | | 0
! | 6 | 6 | six |
! | 7 | 7 | seven |
! | 8 | 8 | eight |
! | | | null |
! | | 0 | zero |
! (15 rows)
!
SELECT '' AS "xxx", *
FROM J1_TBL LEFT JOIN J2_TBL USING (i) WHERE (k = 1);
xxx | i | j | t | k
--- 1773,1795 ----
FROM J1_TBL RIGHT JOIN J2_TBL USING (i);
xxx | i | j | t | k
-----+---+---+-------+----
| 1 | 4 | one | -1
| 2 | 3 | two | 2
| 3 | 2 | three | -3
+ | 2 | 3 | two | 4
| 5 | 0 | five | -5
| 5 | 0 | five | -5
+ | 0 | | zero |
| | | |
| | | | 0
(9 rows)
SELECT '' AS "xxx", *
FROM J1_TBL FULL OUTER JOIN J2_TBL USING (i);
! ERROR: Unable to devise a query plan for the given query
SELECT '' AS "xxx", *
FROM J1_TBL FULL JOIN J2_TBL USING (i);
! ERROR: Unable to devise a query plan for the given query
SELECT '' AS "xxx", *
FROM J1_TBL LEFT JOIN J2_TBL USING (i) WHERE (k = 1);
xxx | i | j | t | k
======================================================================
