costfuzz failed: could not parse geometry: error parsing EWKB hex: encoding/hex: invalid byte

[michae2]

Reduced form of this costfuzz failure is:

CREATE TABLE table1 (
  col1_1  INT8,
  col1_2  INT4,
  col1_3  GEOMETRY,
  col1_4  DECIMAL,
  col1_5  CHAR NULL,
  col1_6  UUID,
  col1_7  STRING,
  col1_8  BOOL,
  col1_9  FLOAT8,
  col1_10 DECIMAL,
  col1_11 REGPROCEDURE,
  col1_12 DECIMAL,
  col1_13 STRING AS (CASE WHEN col1_11 IS NULL THEN '' ELSE '' END) STORED NOT NULL,
  col1_14 DECIMAL AS (col1_4 + col1_10) VIRTUAL,
  PRIMARY KEY (col1_13, col1_14),
  INDEX (col1_1, col1_8 DESC) STORING(col1_3, col1_4, col1_5, col1_6, col1_7, col1_9),
  FAMILY (col1_11, col1_12)
);

INSERT
  INTO table1 (col1_1, col1_2, col1_4, col1_5, col1_7, col1_8, col1_10, col1_11)
VALUES (7180780305679979620, 0, 770594405223.763700, NULL, '', false, 0, 0);

  WITH with_6589 (col_43785, col_43786, col_43787) AS (
                                                    SELECT *
                                                      FROM (
                                                            VALUES (
                                                                    '01050000C00200000001020000C00300000078D06793391E02429E83446CADA2FCC15E4EA37CBA1400420C60F9105D0BEE41E0F54FC42101CBC1965EFB608644F7412C093EC05FE1F941760570A3BF8AF3C1A403F27F3ECE00C2682328C8F689FB4148684196744E01C2C38DADEBEADDF5C101020000C002000000101BDAC47276E4413CCE236A4A85004210A8EEA4449CE1414AC0D5EF8F57FEC1583309664AFBDC4178D5C5723380F641F8C7E8A217D3D7414
022E998DE47D841':::GEOMETRY,
                                                                    '06:02:55.044222':::TIME,
                                                                    2465982143:::OID
                                                                   )
                                                           ) AS tab_20330 (
                                                                            col_43785,
                                                                            col_43786,
                                                                            col_43787
                                                                          )
                                                   ),
       with_6590 (col_43788, col_43789) AS (
                                          SELECT *
                                            FROM (
                                                  VALUES (
                                                          '1977-12-06 20:20:07.000092+00:00':::TIMESTAMPTZ,
                                                          B'1110101111000010101100001000101111001101110011'
                                                         ),
                                                         (
                                                          '2031-09-18 23:35:31.000012+00:00':::TIMESTAMPTZ,
                                                          B'0111111111111111111111111111111111111111111111'
                                                         ),
                                                         (
                                                          '2015-06-26 11:26:42.000381+00:00':::TIMESTAMPTZ,
                                                          NULL
                                                         ),
                                                         (
                                                          (
                                                            '2019-05-17 02:50:10.000799+00:00':::TIMESTAMPTZ::TIMESTAMPTZ
                                                            - '-1 years -6 mons -82 days -09:05:03.175796':::INTERVAL::INTERVAL
                                                          )::TIMESTAMPTZ,
                                                          B'0000001011011000011010100110010110101001111100'
                                                         )
                                                 ) AS tab_20331 (col_43788, col_43789)
                                        ),
       with_6591 (col_43790) AS (
                              SELECT tab_20332.col1_2 AS col_43790
                                FROM defaultdb.public.table1@[0] AS tab_20332
                                JOIN defaultdb.public.table1@[0] AS tab_20333 ON tab_20332.col1_10
                                                                                 = tab_20333.col1_4
                                                                             AND tab_20332.col1_13
                                                                                 = tab_20333.col1_13
                               WHERE false
                             )
SELECT tab_20335.col1_14 AS col_43791,
       tab_20334.col1_14 AS col_43792,
       tab_20334.col1_14 AS col_43793,
       1.833716154445905180E+33:::DECIMAL AS col_43794,
       B'0100110001001111101110101110110000101110001100' AS col_43795,
       (-5.9066989891665884E+24):::DECIMAL AS col_43796,
       (-5.683191569621986073E+20):::DECIMAL AS col_43798,
       ('12:53:04.293945+00:49:00':::TIMETZ::TIMETZ + NULL::DATE)::TIMESTAMPTZ AS col_43799,
       '-Infinity':::DECIMAL AS col_43800
  FROM defaultdb.public.table1 AS tab_20334
  JOIN defaultdb.public.table1 AS tab_20335 ON tab_20334.col1_13 = tab_20335.col1_13
                                           AND tab_20334.col1_1 = tab_20335.col1_1
                                           AND tab_20334.col1_8 = tab_20335.col1_8
                                           AND tab_20334.col1_14 = tab_20335.col1_14,
       with_6590 AS cte_ref_2216;

SET testing_optimizer_random_cost_seed = 7894054333181340462;

  WITH with_6589 (col_43785, col_43786, col_43787) AS (
                                                    SELECT *
                                                      FROM (
                                                            VALUES (
                                                                    '01050000C00200000001020000C00300000078D06793391E02429E83446CADA2FCC15E4EA37CBA1400420C60F9105D0BEE41E0F54FC42101CBC1965EFB608644F7412C093EC05FE1F941760570A3BF8AF3C1A403F27F3ECE00C2682328C8F689FB4148684196744E01C2C38DADEBEADDF5C101020000C002000000101BDAC47276E4413CCE236A4A85004210A8EEA4449CE1414AC0D5EF8F57FEC1583309664AFBDC4178D5C5723380F641F8C7E8A217D3D7414022E998DE47D841':::GEOMETRY,
                                                                    '06:02:55.044222':::TIME,
                                                                    2465982143:::OID
                                                                   )
                                                           ) AS tab_20330 (
                                                                            col_43785,
                                                                            col_43786,
                                                                            col_43787
                                                                          )
                                                   ),
       with_6590 (col_43788, col_43789) AS (
                                          SELECT *
                                            FROM (
                                                  VALUES (
                                                          '1977-12-06 20:20:07.000092+00:00':::TIMESTAMPTZ,
                                                          B'1110101111000010101100001000101111001101110011'
                                                         ),
                                                         (
                                                          '2031-09-18 23:35:31.000012+00:00':::TIMESTAMPTZ,
                                                          B'0111111111111111111111111111111111111111111111'
                                                         ),
                                                         (
                                                          '2015-06-26 11:26:42.000381+00:00':::TIMESTAMPTZ,
                                                          NULL
                                                         ),
                                                         (
                                                          (
                                                            '2019-05-17 02:50:10.000799+00:00':::TIMESTAMPTZ::TIMESTAMPTZ
                                                            - '-1 years -6 mons -82 days -09:05:03.175796':::INTERVAL::INTERVAL
                                                          )::TIMESTAMPTZ,
                                                          B'0000001011011000011010100110010110101001111100'
                                                         )
                                                 ) AS tab_20331 (col_43788, col_43789)
                                        ),
       with_6591 (col_43790) AS (
                              SELECT tab_20332.col1_2 AS col_43790
                                FROM defaultdb.public.table1@[0] AS tab_20332
                                JOIN defaultdb.public.table1@[0] AS tab_20333 ON tab_20332.col1_10
                                                                                 = tab_20333.col1_4
                                                                             AND tab_20332.col1_13
                                                                                 = tab_20333.col1_13
                               WHERE false
                             )
SELECT tab_20335.col1_14 AS col_43791,
       tab_20334.col1_14 AS col_43792,
       tab_20334.col1_14 AS col_43793,
       1.833716154445905180E+33:::DECIMAL AS col_43794,
       B'0100110001001111101110101110110000101110001100' AS col_43795,
       (-5.9066989891665884E+24):::DECIMAL AS col_43796,
       (-5.683191569621986073E+20):::DECIMAL AS col_43798,
       ('12:53:04.293945+00:49:00':::TIMETZ::TIMETZ + NULL::DATE)::TIMESTAMPTZ AS col_43799,
       '-Infinity':::DECIMAL AS col_43800
  FROM defaultdb.public.table1 AS tab_20334
  JOIN defaultdb.public.table1 AS tab_20335 ON tab_20334.col1_13 = tab_20335.col1_13
                                           AND tab_20334.col1_1 = tab_20335.col1_1
                                           AND tab_20334.col1_8 = tab_20335.col1_8
                                           AND tab_20334.col1_14 = tab_20335.col1_14,
       with_6590 AS cte_ref_2216;

The difference in results:

ERROR: could not parse geometry: error parsing EWKB hex: encoding/hex: invalid byte: U+000A
SQLSTATE: 22023

vs

      col_43791     |      col_43792      |      col_43793      |        col_43794         |                   col_43795                    |        col_43796        |         col_43798         | col_43799 | col_43800
--------------------+---------------------+---------------------+--------------------------+------------------------------------------------+-------------------------+---------------------------+-----------+------------
  770594405223.7637 | 770594405223.763700 | 770594405223.763700 | 1.833716154445905180E+33 | 0100110001001111101110101110110000101110001100 | -5.9066989891665884E+24 | -5.683191569621986073E+20 | NULL      | -Infinity
  770594405223.7637 | 770594405223.763700 | 770594405223.763700 | 1.833716154445905180E+33 | 0100110001001111101110101110110000101110001100 | -5.9066989891665884E+24 | -5.683191569621986073E+20 | NULL      | -Infinity
  770594405223.7637 | 770594405223.763700 | 770594405223.763700 | 1.833716154445905180E+33 | 0100110001001111101110101110110000101110001100 | -5.9066989891665884E+24 | -5.683191569621986073E+20 | NULL      | -Infinity
  770594405223.7637 | 770594405223.763700 | 770594405223.763700 | 1.833716154445905180E+33 | 0100110001001111101110101110110000101110001100 | -5.9066989891665884E+24 | -5.683191569621986073E+20 | NULL      | -Infinity
(4 rows)


Time: 3ms total (execution 3ms / network 0ms)

Jira issue: CRDB-16800

[mgartner]

The offending expression is:

'01050000C00200000001020000C00300000078D06793391E02429E83446CADA2FCC15E4EA37CBA1400420C60F9105D0BEE41E0F54FC42101CBC1965EFB608644F7412C093EC05FE1F941760570A3BF8AF3C1A403F27F3ECE00C2682328C8F689FB4148684196744E01C2C38DADEBEADDF5C101020000C002000000101BDAC47276E4413CCE236A4A85004210A8EEA4449CE1414AC0D5EF8F57FEC1583309664AFBDC4178D5C5723380F641F8C7E8A217D3D7414022E998DE47D841':::GEOMETRY

This is not a valid geometry. In postgres:

psql:83023.sql:3: ERROR:  XX000: Invalid hex string, length (375) has to be a multiple of two!
LINE 1: SELECT e'01050000C00200000001020000C00300000078D06793391E024...
               ^
LOCATION:  pg_error, lwgeom_pg.c:345

You can get the same error with:

SELECT e'01010000000000000000000000000000000000000'::GEOMETRY;

I believe we error in one case because the expression is not eliminated during normalization - it exists in a CTE that is never referenced, so there is no need to evaluate it.

This should not have caused a costfuzz failure after #81416 was merged, which happened before the costfuzz failure was reported. That remains a mystery. But the artifacts are long-gone and there's no obvious improvement to costfuzz to be made. So I'll close this issue.