Differential check of the v5 series for bug #19705 (Kirill's v5-0001 BRIN, v5-0002 GiST, and the NaN point check for GiST polygon/circle searches), all built without assertions. master 87762cbaa4f; REL_18_STABLE e1b2a86d6e7 with v5-REL_18-0001 + v5-0002 + the NaN point patch (code only; v5-0002 needs the rebase below). For each index (one per table), NaN value and position of the NaN rows, every operator the opclass lists in pg_amop is run with a seq scan and with the index (EXPLAIN checked that the index was used in every case). Search operators compare count(*), ordering operators the first 50 distances. A mismatch is any difference, including an ERROR on one side only. ===== m0nc (index | mismatches | checks) checks 6769, mismatches 493, index not used 0 brin box_inclusion_ops|79|1092 gist box_ops|87|1092 gist circle_ops|58|804 gist point_ops|162|864 gist poly_ops|69|440 spgist box_ops|10|1092 spgist kd_point_ops|0|756 spgist poly_ops|28|440 spgist quad_point_ops|0|189 ===== m5nc (index | mismatches | checks) checks 6769, mismatches 129, index not used 0 brin box_inclusion_ops|0|1092 gist box_ops|20|1092 gist circle_ops|14|804 gist point_ops|18|864 gist poly_ops|39|440 spgist box_ops|10|1092 spgist kd_point_ops|0|756 spgist poly_ops|28|440 spgist quad_point_ops|0|189 ===== r0nc (index | mismatches | checks) checks 6769, mismatches 498, index not used 0 brin box_inclusion_ops|79|1092 gist box_ops|86|1092 gist circle_ops|62|804 gist point_ops|162|864 gist poly_ops|71|440 spgist box_ops|10|1092 spgist kd_point_ops|0|756 spgist poly_ops|28|440 spgist quad_point_ops|0|189 ===== r5nc (index | mismatches | checks) checks 6769, mismatches 129, index not used 0 brin box_inclusion_ops|0|1092 gist box_ops|20|1092 gist circle_ops|14|804 gist point_ops|18|864 gist poly_ops|39|440 spgist box_ops|10|1092 spgist kd_point_ops|0|756 spgist poly_ops|28|440 spgist quad_point_ops|0|189 ===== r0nc-to-r5nc (index | mismatches | checks) checks 6769, mismatches 126, index not used 0 brin box_inclusion_ops|0|1092 gist box_ops|20|1092 gist circle_ops|14|804 gist point_ops|15|864 gist poly_ops|39|440 spgist box_ops|10|1092 spgist kd_point_ops|0|756 spgist poly_ops|28|440 spgist quad_point_ops|0|189 (r0nc-to-r5nc: indexes built by unpatched REL_18, searched by patched REL_18, no REINDEX) ===== the 129 remaining mismatches with v5 on master gist box_ops | nan (1,NaN),(0,0) head | order <->(box,point) | (0.5,0.5) seq=0,0.5,0.5,0.5,0.7071067811865476,1.5,1.5,1.5811388300841898, idx=ERROR: inconsistent point values gist box_ops | nan (1,NaN),(0,0) head | order <->(box,point) | (1,NaN) seq=NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN, idx=ERROR: inconsistent point values gist box_ops | nan (1,NaN),(0,0) only | order <->(box,point) | (0.5,0.5) seq=0.5 idx=ERROR: inconsistent point values gist box_ops | nan (1,NaN),(0,0) only | order <->(box,point) | (1,NaN) seq=NaN idx=ERROR: inconsistent point values gist box_ops | nan (1,NaN),(0,0) sparse | order <->(box,point) | (0.5,0.5) seq=0.5,0.5,0.5,0.5,0.5,0.5,0.5,0.5,0.5,0.5,0.5,0.5,0.5,0.707106 idx=ERROR: inconsistent point values gist box_ops | nan (1,NaN),(0,0) sparse | order <->(box,point) | (1,NaN) seq=NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN, idx=ERROR: inconsistent point values gist box_ops | nan (1,NaN),(0,0) tail | order <->(box,point) | (0.5,0.5) seq=0,0.5,0.5,0.5,0.7071067811865476,1.5,1.5,1.5811388300841898, idx=ERROR: inconsistent point values gist box_ops | nan (1,NaN),(0,0) tail | order <->(box,point) | (1,NaN) seq=NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN, idx=ERROR: inconsistent point values gist box_ops | nan (NaN,NaN),(0,0) head | order <->(box,point) | (0.5,0.5) seq=0,0.5,0.5,0.7071067811865476,1.5,1.5,1.5811388300841898,1.58 idx=0,0.5,0.5,0.7071067811865476,NaN,1.5,1.5,1.5811388300841898, gist box_ops | nan (NaN,NaN),(0,0) head | order <->(box,point) | (-1,-1) seq=1.4142135623730951,2.23606797749979,2.23606797749979,2.82842 idx=NaN,1.4142135623730951,2.23606797749979,2.23606797749979,2.8 gist box_ops | nan (NaN,NaN),(0,0) head | order <->(box,point) | (1,NaN) seq=NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN, idx=ERROR: inconsistent point values gist box_ops | nan (NaN,NaN),(0,0) sparse | order <->(box,point) | (0.5,0.5) seq=0.5,0.5,0.7071067811865476,1.5,1.5,1.5811388300841898,1.5811 idx=0.5,0.5,0.7071067811865476,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,N gist box_ops | nan (NaN,NaN),(0,0) sparse | order <->(box,point) | (-1,-1) seq=2.23606797749979,2.23606797749979,2.8284271247461903,3.16227 idx=NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,2.23606797749979 gist box_ops | nan (NaN,NaN),(0,0) sparse | order <->(box,point) | (1,NaN) seq=NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN, idx=ERROR: inconsistent point values gist box_ops | nan (NaN,NaN),(0,0) tail | order <->(box,point) | (0.5,0.5) seq=0,0.5,0.5,0.7071067811865476,1.5,1.5,1.5811388300841898,1.58 idx=0,0.5,0.5,0.7071067811865476,NaN,1.5,1.5,1.5811388300841898, gist box_ops | nan (NaN,NaN),(0,0) tail | order <->(box,point) | (-1,-1) seq=1.4142135623730951,2.23606797749979,2.23606797749979,2.82842 idx=NaN,1.4142135623730951,2.23606797749979,2.23606797749979,2.8 gist box_ops | nan (NaN,NaN),(0,0) tail | order <->(box,point) | (1,NaN) seq=NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN, idx=ERROR: inconsistent point values gist box_ops | nan (NaN,NaN),(NaN,NaN) head | order <->(box,point) | (1,NaN) seq=NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN, idx=ERROR: inconsistent point values gist box_ops | nan (NaN,NaN),(NaN,NaN) sparse | order <->(box,point) | (1,NaN) seq=NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN, idx=ERROR: inconsistent point values gist box_ops | nan (NaN,NaN),(NaN,NaN) tail | order <->(box,point) | (1,NaN) seq=NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN, idx=ERROR: inconsistent point values gist circle_ops | nan <(0,0),NaN> head | order <->(circle,point) | (1,NaN) seq=NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN, idx=ERROR: inconsistent point values gist circle_ops | nan <(0,0),NaN> sparse | order <->(circle,point) | (1,NaN) seq=NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN, idx=ERROR: inconsistent point values gist circle_ops | nan <(0,0),NaN> tail | order <->(circle,point) | (1,NaN) seq=NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN, idx=ERROR: inconsistent point values gist circle_ops | nan <(1,NaN),1> head | order <->(circle,point) | (0.5,0.5) seq=0,0,0,0,0.5811388300841898,0.5811388300841898,0.581138830084 idx=ERROR: inconsistent point values gist circle_ops | nan <(1,NaN),1> head | order <->(circle,point) | (1,NaN) seq=NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN, idx=ERROR: inconsistent point values gist circle_ops | nan <(1,NaN),1> only | order <->(circle,point) | (0.5,0.5) seq=NaN idx=ERROR: inconsistent point values gist circle_ops | nan <(1,NaN),1> only | order <->(circle,point) | (1,NaN) seq=NaN idx=ERROR: inconsistent point values gist circle_ops | nan <(1,NaN),1> sparse | order <->(circle,point) | (0.5,0.5) seq=0,0,0,0.5811388300841898,0.5811388300841898,0.58113883008418 idx=ERROR: inconsistent point values gist circle_ops | nan <(1,NaN),1> sparse | order <->(circle,point) | (1,NaN) seq=NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN, idx=ERROR: inconsistent point values gist circle_ops | nan <(1,NaN),1> tail | order <->(circle,point) | (0.5,0.5) seq=0,0,0,0,0.5811388300841898,0.5811388300841898,0.581138830084 idx=ERROR: inconsistent point values gist circle_ops | nan <(1,NaN),1> tail | order <->(circle,point) | (1,NaN) seq=NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN, idx=ERROR: inconsistent point values gist circle_ops | nan <(NaN,NaN),1> head | order <->(circle,point) | (1,NaN) seq=NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN, idx=ERROR: inconsistent point values gist circle_ops | nan <(NaN,NaN),1> sparse | order <->(circle,point) | (1,NaN) seq=NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN, idx=ERROR: inconsistent point values gist circle_ops | nan <(NaN,NaN),1> tail | order <->(circle,point) | (1,NaN) seq=NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN, idx=ERROR: inconsistent point values gist point_ops | nan (1,NaN) head | order <->(point,point) | (1,NaN) seq=NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN, idx=ERROR: inconsistent point values gist point_ops | nan (1,NaN) sparse | order <->(point,point) | (1,NaN) seq=NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN, idx=ERROR: inconsistent point values gist point_ops | nan (1,NaN) tail | order <->(point,point) | (1,NaN) seq=NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN, idx=ERROR: inconsistent point values gist point_ops | nan (NaN,1) head | order <->(point,point) | (1,NaN) seq=NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN, idx=ERROR: inconsistent point values gist point_ops | nan (NaN,1) sparse | order <->(point,point) | (1,NaN) seq=NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN, idx=ERROR: inconsistent point values gist point_ops | nan (NaN,1) tail | order <->(point,point) | (1,NaN) seq=NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN, idx=ERROR: inconsistent point values gist point_ops | nan (NaN,NaN) head | order <->(point,point) | (1,NaN) seq=NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN, idx=ERROR: inconsistent point values gist point_ops | nan (NaN,NaN) sparse | order <->(point,point) | (1,NaN) seq=NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN, idx=ERROR: inconsistent point values gist point_ops | nan (NaN,NaN) tail | order <->(point,point) | (1,NaN) seq=NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN, idx=ERROR: inconsistent point values gist point_ops | nan (1,NaN) head | search <@(point,polygon) | ((NaN,NaN),(0,0),(1,1)) seq=2026 idx=0 gist point_ops | nan (1,NaN) sparse | search <@(point,polygon) | ((NaN,NaN),(0,0),(1,1)) seq=2025 idx=0 gist point_ops | nan (1,NaN) tail | search <@(point,polygon) | ((NaN,NaN),(0,0),(1,1)) seq=2026 idx=0 gist point_ops | nan (NaN,1) head | search <@(point,polygon) | ((NaN,NaN),(0,0),(1,1)) seq=2026 idx=0 gist point_ops | nan (NaN,1) sparse | search <@(point,polygon) | ((NaN,NaN),(0,0),(1,1)) seq=2025 idx=0 gist point_ops | nan (NaN,1) tail | search <@(point,polygon) | ((NaN,NaN),(0,0),(1,1)) seq=2026 idx=0 gist point_ops | nan (NaN,NaN) head | search <@(point,polygon) | ((NaN,NaN),(0,0),(1,1)) seq=2026 idx=0 gist point_ops | nan (NaN,NaN) sparse | search <@(point,polygon) | ((NaN,NaN),(0,0),(1,1)) seq=2025 idx=0 gist point_ops | nan (NaN,NaN) tail | search <@(point,polygon) | ((NaN,NaN),(0,0),(1,1)) seq=2026 idx=0 gist poly_ops | nan ((0,0),(NaN,1),(1,1)) head | order <->(polygon,point) | (0.5,0.5) seq=0,0,0.5,0.5,0.7071067811865476,1.5,1.5,1.5811388300841898,1. idx=ERROR: inconsistent point values gist poly_ops | nan ((0,0),(NaN,1),(1,1)) head | order <->(polygon,point) | (1,NaN) seq=0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0, idx=ERROR: inconsistent point values gist poly_ops | nan ((0,0),(NaN,1),(1,1)) head | order <->(polygon,point) | (NaN,NaN) seq=0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0, idx=ERROR: index returned tuples in wrong order gist poly_ops | nan ((0,0),(NaN,1),(1,1)) only | order <->(polygon,point) | (0.5,0.5) seq=0 idx=ERROR: inconsistent point values gist poly_ops | nan ((0,0),(NaN,1),(1,1)) only | order <->(polygon,point) | (10,10) seq=12.727922061357855 idx=ERROR: index returned tuples in wrong order gist poly_ops | nan ((0,0),(NaN,1),(1,1)) only | order <->(polygon,point) | (1,NaN) seq=0 idx=ERROR: index returned tuples in wrong order gist poly_ops | nan ((0,0),(NaN,1),(1,1)) only | order <->(polygon,point) | (44,44) seq=60.81118318204309 idx=ERROR: index returned tuples in wrong order gist poly_ops | nan ((0,0),(NaN,1),(1,1)) only | order <->(polygon,point) | (99,99) seq=138.59292911256333 idx=ERROR: index returned tuples in wrong order gist poly_ops | nan ((0,0),(NaN,1),(1,1)) only | order <->(polygon,point) | (NaN,NaN) seq=0 idx=ERROR: index returned tuples in wrong order gist poly_ops | nan ((0,0),(NaN,1),(1,1)) sparse | order <->(polygon,point) | (0.5,0.5) seq=0,0,0,0,0,0,0,0,0,0,0,0.5,0.5,0.7071067811865476,1.5,1.5,1.5 idx=ERROR: inconsistent point values gist poly_ops | nan ((0,0),(NaN,1),(1,1)) sparse | order <->(polygon,point) | (1,NaN) seq=0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0, idx=ERROR: inconsistent point values gist poly_ops | nan ((0,0),(NaN,1),(1,1)) sparse | order <->(polygon,point) | (NaN,NaN) seq=0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0, idx=ERROR: index returned tuples in wrong order gist poly_ops | nan ((0,0),(NaN,1),(1,1)) tail | order <->(polygon,point) | (0.5,0.5) seq=0,0,0.5,0.5,0.7071067811865476,1.5,1.5,1.5811388300841898,1. idx=ERROR: inconsistent point values gist poly_ops | nan ((0,0),(NaN,1),(1,1)) tail | order <->(polygon,point) | (1,NaN) seq=0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0, idx=ERROR: inconsistent point values gist poly_ops | nan ((0,0),(NaN,1),(1,1)) tail | order <->(polygon,point) | (NaN,NaN) seq=0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0, idx=ERROR: index returned tuples in wrong order gist poly_ops | nan ((NaN,NaN),(0,0),(1,1)) head | order <->(polygon,point) | (0.5,0.5) seq=0,0,0.5,0.5,0.7071067811865476,1.5,1.5,1.5811388300841898,1. idx=ERROR: index returned tuples in wrong order gist poly_ops | nan ((NaN,NaN),(0,0),(1,1)) head | order <->(polygon,point) | (10,10) seq=0,0,0,0,0,1,1,1,1,1,1,1,1,1.4142135623730951,1.4142135623730 idx=0,0,0,0,1,1,1,1,1,1,1,1,1.4142135623730951,1.414213562373095 gist poly_ops | nan ((NaN,NaN),(0,0),(1,1)) head | order <->(polygon,point) | (1,NaN) seq=0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0, idx=ERROR: inconsistent point values gist poly_ops | nan ((NaN,NaN),(0,0),(1,1)) head | order <->(polygon,point) | (44,44) seq=0,0,0,0,0,1,1,1,1,1.4142135623730951,2,2,2,2,2.2360679774997 idx=0,0,0,0,1,1,1,1,1.4142135623730951,2,2,2,2,2.23606797749979, gist poly_ops | nan ((NaN,NaN),(0,0),(1,1)) head | order <->(polygon,point) | (99,99) seq=0,76.36753236814714,77.07788269017254,77.07788269017254,77.7 idx=76.36753236814714,77.07788269017254,77.07788269017254,77.781 gist poly_ops | nan ((NaN,NaN),(0,0),(1,1)) head | order <->(polygon,point) | (NaN,NaN) seq=0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0, idx=ERROR: index returned tuples in wrong order gist poly_ops | nan ((NaN,NaN),(0,0),(1,1)) only | order <->(polygon,point) | (0.5,0.5) seq=0 idx=ERROR: index returned tuples in wrong order gist poly_ops | nan ((NaN,NaN),(0,0),(1,1)) only | order <->(polygon,point) | (10,10) seq=0 idx=ERROR: index returned tuples in wrong order gist poly_ops | nan ((NaN,NaN),(0,0),(1,1)) only | order <->(polygon,point) | (1,NaN) seq=0 idx=ERROR: index returned tuples in wrong order gist poly_ops | nan ((NaN,NaN),(0,0),(1,1)) only | order <->(polygon,point) | (44,44) seq=0 idx=ERROR: index returned tuples in wrong order gist poly_ops | nan ((NaN,NaN),(0,0),(1,1)) only | order <->(polygon,point) | (99,99) seq=0 idx=ERROR: index returned tuples in wrong order gist poly_ops | nan ((NaN,NaN),(0,0),(1,1)) only | order <->(polygon,point) | (NaN,NaN) seq=0 idx=ERROR: index returned tuples in wrong order gist poly_ops | nan ((NaN,NaN),(0,0),(1,1)) sparse | order <->(polygon,point) | (0.5,0.5) seq=0,0,0,0,0,0,0,0,0,0,0,0.5,0.5,0.7071067811865476,1.5,1.5,1.5 idx=ERROR: index returned tuples in wrong order gist poly_ops | nan ((NaN,NaN),(0,0),(1,1)) sparse | order <->(polygon,point) | (10,10) seq=0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,1,1,1,1,1,1,1,1.414213562373 idx=0,0,0,0,1,1,1,1,1,1,1,1,1.4142135623730951,1.414213562373095 gist poly_ops | nan ((NaN,NaN),(0,0),(1,1)) sparse | order <->(polygon,point) | (1,NaN) seq=0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0, idx=ERROR: inconsistent point values gist poly_ops | nan ((NaN,NaN),(0,0),(1,1)) sparse | order <->(polygon,point) | (44,44) seq=0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,1,1,1,1.4142135623730951,2,2 idx=0,0,0,0,1,1,1,1,1.4142135623730951,2,2,2,2,2.23606797749979, gist poly_ops | nan ((NaN,NaN),(0,0),(1,1)) sparse | order <->(polygon,point) | (99,99) seq=0,0,0,0,0,0,0,0,0,0,0,76.36753236814714,77.07788269017254,77 idx=76.36753236814714,77.07788269017254,77.07788269017254,77.781 gist poly_ops | nan ((NaN,NaN),(0,0),(1,1)) sparse | order <->(polygon,point) | (NaN,NaN) seq=0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0, idx=ERROR: index returned tuples in wrong order gist poly_ops | nan ((NaN,NaN),(0,0),(1,1)) tail | order <->(polygon,point) | (0.5,0.5) seq=0,0,0.5,0.5,0.7071067811865476,1.5,1.5,1.5811388300841898,1. idx=ERROR: index returned tuples in wrong order gist poly_ops | nan ((NaN,NaN),(0,0),(1,1)) tail | order <->(polygon,point) | (10,10) seq=0,0,0,0,0,1,1,1,1,1,1,1,1,1.4142135623730951,1.4142135623730 idx=0,0,0,0,1,1,1,1,1,1,1,1,1.4142135623730951,1.414213562373095 gist poly_ops | nan ((NaN,NaN),(0,0),(1,1)) tail | order <->(polygon,point) | (1,NaN) seq=0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0, idx=ERROR: inconsistent point values gist poly_ops | nan ((NaN,NaN),(0,0),(1,1)) tail | order <->(polygon,point) | (44,44) seq=0,0,0,0,0,1,1,1,1,1.4142135623730951,2,2,2,2,2.2360679774997 idx=0,0,0,0,1,1,1,1,1.4142135623730951,2,2,2,2,2.23606797749979, gist poly_ops | nan ((NaN,NaN),(0,0),(1,1)) tail | order <->(polygon,point) | (99,99) seq=0,76.36753236814714,77.07788269017254,77.07788269017254,77.7 idx=76.36753236814714,77.07788269017254,77.07788269017254,77.781 gist poly_ops | nan ((NaN,NaN),(0,0),(1,1)) tail | order <->(polygon,point) | (NaN,NaN) seq=0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0, idx=ERROR: index returned tuples in wrong order spgist box_ops | nan (NaN,NaN),(0,0) head | search ~=(box,box) | (NaN,NaN),(0,0) seq=1 idx=0 spgist box_ops | nan (NaN,NaN),(0,0) sparse | search ~=(box,box) | (NaN,NaN),(0,0) seq=11 idx=0 spgist box_ops | nan (NaN,NaN),(0,0) tail | search ~=(box,box) | (NaN,NaN),(0,0) seq=1 idx=0 spgist box_ops | nan (1,NaN),(0,0) tail | order <->(box,point) | (0.5,0.5) seq=0,0.5,0.5,0.5,0.7071067811865476,1.5,1.5,1.5811388300841898, idx=0.5,0,0.5,0.5,0.7071067811865476,1.5,1.5,1.5811388300841898, spgist box_ops | nan (NaN,NaN),(0,0) head | order <->(box,point) | (0.5,0.5) seq=0,0.5,0.5,0.7071067811865476,1.5,1.5,1.5811388300841898,1.58 idx=0,NaN,0.5,0.5,0.7071067811865476,1.5,1.5,1.5811388300841898, spgist box_ops | nan (NaN,NaN),(0,0) head | order <->(box,point) | (-1,-1) seq=1.4142135623730951,2.23606797749979,2.23606797749979,2.82842 idx=1.4142135623730951,NaN,2.23606797749979,2.23606797749979,2.8 spgist box_ops | nan (NaN,NaN),(0,0) sparse | order <->(box,point) | (0.5,0.5) seq=0.5,0.5,0.7071067811865476,1.5,1.5,1.5811388300841898,1.5811 idx=NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,0.5,0.5,0.707106 spgist box_ops | nan (NaN,NaN),(0,0) sparse | order <->(box,point) | (-1,-1) seq=2.23606797749979,2.23606797749979,2.8284271247461903,3.16227 idx=NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,2.23606797749979 spgist box_ops | nan (NaN,NaN),(0,0) tail | order <->(box,point) | (0.5,0.5) seq=0,0.5,0.5,0.7071067811865476,1.5,1.5,1.5811388300841898,1.58 idx=NaN,0,0.5,0.5,0.7071067811865476,1.5,1.5,1.5811388300841898, spgist box_ops | nan (NaN,NaN),(0,0) tail | order <->(box,point) | (-1,-1) seq=1.4142135623730951,2.23606797749979,2.23606797749979,2.82842 idx=NaN,1.4142135623730951,2.23606797749979,2.23606797749979,2.8 spgist poly_ops | nan ((0,0),(NaN,1),(1,1)) head | order <->(polygon,point) | (1,NaN) seq=0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0, idx=ERROR: index returned tuples in wrong order spgist poly_ops | nan ((0,0),(NaN,1),(1,1)) head | order <->(polygon,point) | (NaN,NaN) seq=0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0, idx=ERROR: index returned tuples in wrong order spgist poly_ops | nan ((0,0),(NaN,1),(1,1)) only | order <->(polygon,point) | (1,NaN) seq=0 idx=ERROR: index returned tuples in wrong order spgist poly_ops | nan ((0,0),(NaN,1),(1,1)) only | order <->(polygon,point) | (NaN,NaN) seq=0 idx=ERROR: index returned tuples in wrong order spgist poly_ops | nan ((0,0),(NaN,1),(1,1)) sparse | order <->(polygon,point) | (1,NaN) seq=0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0, idx=ERROR: index returned tuples in wrong order spgist poly_ops | nan ((0,0),(NaN,1),(1,1)) sparse | order <->(polygon,point) | (NaN,NaN) seq=0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0, idx=ERROR: index returned tuples in wrong order spgist poly_ops | nan ((0,0),(NaN,1),(1,1)) tail | order <->(polygon,point) | (1,NaN) seq=0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0, idx=ERROR: index returned tuples in wrong order spgist poly_ops | nan ((0,0),(NaN,1),(1,1)) tail | order <->(polygon,point) | (NaN,NaN) seq=0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0, idx=ERROR: index returned tuples in wrong order spgist poly_ops | nan ((NaN,NaN),(0,0),(1,1)) head | order <->(polygon,point) | (10,10) seq=0,0,0,0,0,1,1,1,1,1,1,1,1,1.4142135623730951,1.4142135623730 idx=0,0,0,0,1,1,1,1,1,1,1,1,1.4142135623730951,1.414213562373095 spgist poly_ops | nan ((NaN,NaN),(0,0),(1,1)) head | order <->(polygon,point) | (1,NaN) seq=0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0, idx=ERROR: index returned tuples in wrong order spgist poly_ops | nan ((NaN,NaN),(0,0),(1,1)) head | order <->(polygon,point) | (44,44) seq=0,0,0,0,0,1,1,1,1,1.4142135623730951,2,2,2,2,2.2360679774997 idx=0,0,0,0,1,1,1,1,1.4142135623730951,2,2,2,2,2.23606797749979, spgist poly_ops | nan ((NaN,NaN),(0,0),(1,1)) head | order <->(polygon,point) | (99,99) seq=0,76.36753236814714,77.07788269017254,77.07788269017254,77.7 idx=76.36753236814714,77.07788269017254,77.07788269017254,77.781 spgist poly_ops | nan ((NaN,NaN),(0,0),(1,1)) head | order <->(polygon,point) | (NaN,NaN) seq=0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0, idx=ERROR: index returned tuples in wrong order spgist poly_ops | nan ((NaN,NaN),(0,0),(1,1)) only | order <->(polygon,point) | (1,NaN) seq=0 idx=ERROR: index returned tuples in wrong order spgist poly_ops | nan ((NaN,NaN),(0,0),(1,1)) only | order <->(polygon,point) | (NaN,NaN) seq=0 idx=ERROR: index returned tuples in wrong order spgist poly_ops | nan ((NaN,NaN),(0,0),(1,1)) sparse | order <->(polygon,point) | (10,10) seq=0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,1,1,1,1,1,1,1,1.414213562373 idx=0,0,0,0,1,1,1,1,1,1,1,1,1.4142135623730951,1.414213562373095 spgist poly_ops | nan ((NaN,NaN),(0,0),(1,1)) sparse | order <->(polygon,point) | (1,NaN) seq=0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0, idx=ERROR: index returned tuples in wrong order spgist poly_ops | nan ((NaN,NaN),(0,0),(1,1)) sparse | order <->(polygon,point) | (44,44) seq=0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,1,1,1,1.4142135623730951,2,2 idx=0,0,0,0,1,1,1,1,1.4142135623730951,2,2,2,2,2.23606797749979, spgist poly_ops | nan ((NaN,NaN),(0,0),(1,1)) sparse | order <->(polygon,point) | (99,99) seq=0,0,0,0,0,0,0,0,0,0,0,76.36753236814714,77.07788269017254,77 idx=76.36753236814714,77.07788269017254,77.07788269017254,77.781 spgist poly_ops | nan ((NaN,NaN),(0,0),(1,1)) sparse | order <->(polygon,point) | (NaN,NaN) seq=0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0, idx=ERROR: index returned tuples in wrong order spgist poly_ops | nan ((NaN,NaN),(0,0),(1,1)) tail | order <->(polygon,point) | (10,10) seq=0,0,0,0,0,1,1,1,1,1,1,1,1,1.4142135623730951,1.4142135623730 idx=0,0,0,0,1,1,1,1,1,1,1,1,1.4142135623730951,1.414213562373095 spgist poly_ops | nan ((NaN,NaN),(0,0),(1,1)) tail | order <->(polygon,point) | (1,NaN) seq=0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0, idx=ERROR: index returned tuples in wrong order spgist poly_ops | nan ((NaN,NaN),(0,0),(1,1)) tail | order <->(polygon,point) | (44,44) seq=0,0,0,0,0,1,1,1,1,1.4142135623730951,2,2,2,2,2.2360679774997 idx=0,0,0,0,1,1,1,1,1.4142135623730951,2,2,2,2,2.23606797749979, spgist poly_ops | nan ((NaN,NaN),(0,0),(1,1)) tail | order <->(polygon,point) | (99,99) seq=0,76.36753236814714,77.07788269017254,77.07788269017254,77.7 idx=76.36753236814714,77.07788269017254,77.07788269017254,77.781 spgist poly_ops | nan ((NaN,NaN),(0,0),(1,1)) tail | order <->(polygon,point) | (NaN,NaN) seq=0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0, idx=ERROR: index returned tuples in wrong order spgist poly_ops | nan ((NaN,NaN),(0,0),(1,1)) head | search ~=(polygon,polygon) | ((NaN,NaN),(0,0),(1,1)) seq=1 idx=0 spgist poly_ops | nan ((NaN,NaN),(0,0),(1,1)) sparse | search ~=(polygon,polygon) | ((NaN,NaN),(0,0),(1,1)) seq=11 idx=0 spgist poly_ops | nan ((NaN,NaN),(0,0),(1,1)) tail | search ~=(polygon,polygon) | ((NaN,NaN),(0,0),(1,1)) seq=1 idx=0 ===== minimal case: box <-> point on GiST, one box with NaN in one coordinate CREATE TABLE b (v box); INSERT INTO b VALUES ('(1,NaN),(0,0)'); INSERT INTO b SELECT box(point(x, y), point(x + 1, y + 1)) FROM generate_series(0, 44) x, generate_series(0, 44) y; CREATE INDEX ON b USING gist (v); SET enable_seqscan = off; SELECT v <-> point '(0.5,0.5)' AS d FROM b ORDER BY v <-> point '(0.5,0.5)' LIMIT 3; RESET enable_seqscan; SET enable_indexscan = off; SET enable_bitmapscan = off; SELECT v <-> point '(0.5,0.5)' AS d FROM b ORDER BY v <-> point '(0.5,0.5)' LIMIT 3; -- index scan: ERROR: inconsistent point values (unpatched and v5, master and REL_18) -- with --enable-cassert: TRAP: failed Assert("box->low.y <= box->high.y") in computeDistance() -- seq scan: 0, 0.5, 0.5 ===== v5-0002 on REL_18: the only conflict (palloc_object is not in 18) 121- unionL = (BOX *) palloc(sizeof(BOX)); 122-- *unionL = *cur; 123:+ snapBox(unionL, cur); 124- } 125- else -- 130- unionR = (BOX *) palloc(sizeof(BOX)); 131-- *unionR = *cur; 132:+ snapBox(unionR, cur); 133- } 134- else ===== nan_matrix.sql -- Differential check for bug #19705: does an index on a geometric column -- return the same rows as a sequential scan, when some stored values have a -- NaN coordinate? -- -- nan_setup() creates one table per (index kind, NaN value, NaN position), -- each with exactly one index, so the planner has nothing else to choose. -- nan_check(label) then runs, for every operator the operator class declares -- in pg_amop, a count(*) with a sequential scan and with the index, and for -- ordering operators it compares the first 50 distances. It records -- whether EXPLAIN really used the index. -- -- Setup and check are separate so that the indexes can be built by one -- binary and searched by another (a minor upgrade, no REINDEX). CREATE TABLE IF NOT EXISTS nan_case ( tbl text PRIMARY KEY, typ text, -- box, point, polygon, circle am text, opclass text, nanval text, pos text, -- head, tail, only, sparse err text -- error from CREATE INDEX, if any (then nothing to check) ); CREATE OR REPLACE FUNCTION nan_setup() RETURNS int LANGUAGE plpgsql AS $$ DECLARE k record; nv text; pos text; t text; n int := 0; finite text; nanexpr text; err text; BEGIN SET LOCAL client_min_messages = warning; FOR k IN SELECT * FROM (VALUES ('box', 'brin', 'box_inclusion_ops'), ('box', 'gist', 'box_ops'), ('box', 'spgist', 'box_ops'), ('point', 'gist', 'point_ops'), ('point', 'spgist', 'quad_point_ops'), ('point', 'spgist', 'kd_point_ops'), ('polygon', 'gist', 'poly_ops'), ('polygon', 'spgist', 'poly_ops'), ('circle', 'gist', 'circle_ops')) AS x(typ, am, opclass) LOOP -- 45 x 45 = 2025 finite values spread over a grid, so GiST and -- SP-GiST split into several levels and BRIN has many ranges. finite := CASE k.typ WHEN 'box' THEN 'box(point(x, y), point(x + 1, y + 1))' WHEN 'point' THEN 'point(x, y)' WHEN 'polygon' THEN 'polygon(box(point(x, y), point(x + 1, y + 1)))' WHEN 'circle' THEN 'circle(point(x, y), 1)' END; FOREACH nv IN ARRAY CASE k.typ WHEN 'box' THEN ARRAY['(NaN,NaN),(0,0)', '(1,NaN),(0,0)', '(NaN,NaN),(NaN,NaN)'] WHEN 'point' THEN ARRAY['(NaN,NaN)', '(NaN,1)', '(1,NaN)'] WHEN 'polygon' THEN ARRAY['((NaN,NaN),(0,0),(1,1))', '((0,0),(NaN,1),(1,1))'] WHEN 'circle' THEN ARRAY['<(NaN,NaN),1>', '<(0,0),NaN>', '<(1,NaN),1>'] END LOOP nanexpr := format('%L::%s', nv, k.typ); FOREACH pos IN ARRAY ARRAY['head', 'tail', 'only', 'sparse'] LOOP n := n + 1; t := format('nan_%s_%s', k.typ, n); EXECUTE format('DROP TABLE IF EXISTS %I', t); EXECUTE format('CREATE TABLE %I (id serial, v %s)', t, k.typ); IF pos = 'head' THEN EXECUTE format('INSERT INTO %I (v) VALUES (%s)', t, nanexpr); END IF; IF pos <> 'only' THEN EXECUTE format('INSERT INTO %I (v) SELECT CASE WHEN %L = ''sparse'' AND (x * 45 + y) %% 200 = 0 ' 'THEN %s ELSE %s END FROM generate_series(0, 44) x, generate_series(0, 44) y', t, pos, nanexpr, finite); END IF; IF pos IN ('tail', 'only') THEN EXECUTE format('INSERT INTO %I (v) VALUES (%s)', t, nanexpr); END IF; err := NULL; BEGIN EXECUTE format('CREATE INDEX %I ON %I USING %s (v %s)%s', t || '_idx', t, k.am, k.opclass, CASE WHEN k.am = 'brin' THEN ' WITH (pages_per_range = 1)' ELSE '' END); EXCEPTION WHEN OTHERS THEN err := SQLERRM; END; EXECUTE format('ANALYZE %I', t); INSERT INTO nan_case VALUES (t, k.typ, k.am, k.opclass, nv, pos, err) ON CONFLICT (tbl) DO UPDATE SET typ = EXCLUDED.typ, am = EXCLUDED.am, opclass = EXCLUDED.opclass, nanval = EXCLUDED.nanval, pos = EXCLUDED.pos, err = EXCLUDED.err; END LOOP; END LOOP; END LOOP; RETURN n; END $$; CREATE TABLE IF NOT EXISTS nan_result ( label text, tbl text, op text, probe text, kind text, seq text, idx text, used_index bool, same bool ); CREATE OR REPLACE FUNCTION nan_probes(typ text) RETURNS text[] LANGUAGE sql IMMUTABLE AS $$ SELECT CASE typ WHEN 'box' THEN ARRAY['(-2,-2),(2,2)', '(0,0),(1,1)', '(10,10),(12,12)', '(40,40),(46,46)', '(100,100),(200,200)', '(NaN,NaN),(0,0)', '(-Infinity,-Infinity),(Infinity,Infinity)'] WHEN 'point' THEN ARRAY['(0.5,0.5)', '(10,10)', '(44,44)', '(99,99)', '(-1,-1)', '(NaN,NaN)', '(1,NaN)'] WHEN 'polygon' THEN ARRAY['((0,0),(0,20),(20,20),(20,0))', '((5,5),(5,6),(6,6))', '((0,0),(0,1),(1,1),(1,0))', '((NaN,NaN),(0,0),(1,1))'] WHEN 'circle' THEN ARRAY['<(0,0),5>', '<(20,20),3>', '<(44,44),0.5>', '<(NaN,NaN),1>', '<(0,0),Infinity>'] END $$; CREATE OR REPLACE FUNCTION nan_check(p_label text) RETURNS TABLE (checks int, mismatches int, not_indexed int) LANGUAGE plpgsql AS $$ DECLARE c record; o record; pr text; q text; s_res text; i_res text; plan text; line text; used bool; nchk int := 0; nbad int := 0; nnoidx int := 0; BEGIN DELETE FROM nan_result WHERE label = p_label; FOR c IN SELECT * FROM nan_case WHERE err IS NULL ORDER BY tbl LOOP FOR o IN SELECT DISTINCT ao.amoppurpose AS purpose, ao.amopopr::regoperator AS opr, op.oprname, format_type(op.oprright, NULL) AS rtype FROM pg_opclass oc JOIN pg_am am ON am.oid = oc.opcmethod JOIN pg_amop ao ON ao.amopfamily = oc.opcfamily JOIN pg_operator op ON op.oid = ao.amopopr WHERE oc.opcname = c.opclass AND am.amname = c.am AND format_type(op.oprleft, NULL) = c.typ LOOP -- Probes: the probe set of the operator's right-hand type, if we have one. IF o.rtype NOT IN ('box', 'point', 'polygon', 'circle') THEN CONTINUE; END IF; FOREACH pr IN ARRAY nan_probes(o.rtype) LOOP IF o.purpose = 's' THEN q := format('SELECT count(*)::text FROM %I WHERE v %s %L::%s', c.tbl, o.oprname, pr, o.rtype); ELSE q := format('SELECT string_agg(d::text, '','' ORDER BY rn) FROM (SELECT d, row_number() OVER () rn ' 'FROM (SELECT v %s %L::%s AS d FROM %I ORDER BY v %s %L::%s LIMIT 50) a) b', o.oprname, pr, o.rtype, c.tbl, o.oprname, pr, o.rtype); END IF; PERFORM set_config('enable_seqscan', 'on', true); PERFORM set_config('enable_indexscan', 'off', true); PERFORM set_config('enable_bitmapscan', 'off', true); BEGIN EXECUTE q INTO s_res; EXCEPTION WHEN OTHERS THEN s_res := 'ERROR: ' || SQLERRM; END; PERFORM set_config('enable_seqscan', 'off', true); PERFORM set_config('enable_indexscan', 'on', true); PERFORM set_config('enable_bitmapscan', 'on', true); -- Leaves the last index query in the server log, in case it crashes. RAISE LOG 'nan_check % % [nan % %]: %', c.am, c.opclass, c.nanval, c.pos, q; plan := ''; FOR line IN EXECUTE 'EXPLAIN (COSTS OFF) ' || q LOOP plan := plan || line || E'\n'; END LOOP; used := plan LIKE '%' || c.tbl || '_idx%'; BEGIN EXECUTE q INTO i_res; EXCEPTION WHEN OTHERS THEN i_res := 'ERROR: ' || SQLERRM; END; nchk := nchk + 1; IF NOT used THEN nnoidx := nnoidx + 1; END IF; IF s_res IS DISTINCT FROM i_res THEN nbad := nbad + 1; END IF; INSERT INTO nan_result VALUES (p_label, c.tbl, o.opr::text, pr, CASE o.purpose WHEN 's' THEN 'search' ELSE 'order' END, s_res, i_res, used, s_res IS NOT DISTINCT FROM i_res); END LOOP; END LOOP; END LOOP; RETURN QUERY SELECT nchk, nbad, nnoidx; END $$; ===== run.sh #!/bin/bash # Runs nan_matrix.sql. # run.sh fresh BUILD initdb, nan_setup() and nan_check() with BUILD # run.sh upgrade OLD NEW nan_setup() with OLD, then nan_check() with NEW # on the same data directory (a minor upgrade) # Writes