sql: selecting from TimeTZ index may miss values in other time zones

[rytaft]

Consider the following example. We have two tables with identical data in a timetz column. The only difference is that one table has the timetz column as the primary key (and therefore the column is indexed).

CREATE TABLE tab (t TIMETZ);
INSERT INTO tab VALUES 
  ('05:00:00.000001'),
  ('07:00:00.000001+02:00:00'),
  ('09:00:00.000001+04:00:00'),
  ('20:59:00.000001+15:59:00');
SELECT count(*) FROM tab WHERE t > '05:00:00';
  count
---------
      4

SELECT count(*) FROM tab WHERE t < '05:00:00.000001';
  count
---------
      3


CREATE TABLE tab2 (t TIMETZ PRIMARY KEY);
INSERT INTO tab2 VALUES 
  ('05:00:00.000001'),
  ('07:00:00.000001+02:00:00'),
  ('09:00:00.000001+04:00:00'),
  ('20:59:00.000001+15:59:00');
SELECT count(*) FROM tab2 WHERE t > '05:00:00';
  count
---------
      1

SELECT count(*) FROM tab2 WHERE t < '05:00:00.000001';
  count
---------
      0

The queries should return the same results on the two different tables, but they are different. This is because the queries over tab2 are performing a constrained scan of the primary index using an incorrect constraint that misses some values. The reason the constraint is incorrect is because the index constraint code in the optimizer may call datum.Next() or datum.Prev() to create an inclusive constraint, and the DTimeTZ implementations of Next() and Prev() are incorrect. These functions return a value that is not the next (or previous) possible value according to the ordering of the key encoded values of TimeTZ.

This bug has existed since the timetz datatype was first added in 20.1.