sql: NULL geography concatenation is inconsistent with Postgres

[mgartner]

From #67791 (comment):

In Postgres 14.2:

marcus=# SELECT NULL::GEOGRAPHY || NULL::GEOGRAPHY[] || NULL::GEOGRAPHY;
  ?column?
-------------
 {NULL:NULL}

In CRDB 21.2.7:

defaultdb> SELECT NULL::GEOGRAPHY || NULL::GEOGRAPHY[] || NULL::GEOGRAPHY;
   ?column?
---------------
  {NULL,NULL}

Jira issue: CRDB-15892

[otan]

i wonder if this is just a display difference with non-postgres defined types.

HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
otan=# SELECT NULL::GEOGRAPHY || NULL::GEOGRAPHY[] || NULL::GEOGRAPHY || NULL::GEOGRAPHY;
     ?column?
------------------
 {NULL:NULL:NULL}
(1 row)

otan=# SELECT NULL::int || NULL::int[] || NULL::int || NULL::int;
     ?column?
------------------
 {NULL,NULL,NULL}
(1 row)

otan=# SELECT array_length(NULL::GEOGRAPHY || NULL::GEOGRAPHY[] || NULL::GEOGRAPHY || NULL::GEOGRAPHY, 1);
 array_length
--------------
            3
(1 row)

otan=# select ( NULL::int || NULL::int[] || NULL::int || NULL::int)::text;
       text
------------------
 {NULL,NULL,NULL}
(1 row)

otan=# SELECT (NULL::GEOGRAPHY || NULL::GEOGRAPHY[] || NULL::GEOGRAPHY || NULL::GEOGRAPHY)::text;
       text
------------------
 {NULL:NULL:NULL}
(1 row)

[otan]

TIL about typdelim from pg_type, which is : for _geography:

otan=# select typdelim from pg_type where typname = '_geography';
 typdelim
----------
 :
(1 row)

spooky:

otan=# select typname, typdelim from pg_type where typdelim <> ',';
  typname   | typdelim
------------+----------
 box        | ;
 _box       | ;
 geometry   | :
 _geometry  | :
 geography  | :
 _geography | :
(6 rows)

[otan]

seems like there's to changes to make here:

• make a method Delimiter in the types package, which returns ; for box, : for geom/geog and , for everything else
• change the delimiter for Format for DArray:

  cockroach/pkg/sql/sem/tree/datum.go

  Line 4414 in b9a6752

  comma = ","

• change pg_type table to return the correct typdelim:

  cockroach/pkg/sql/pg_catalog.go

  Line 2938 in 4c32814

  var pgCatalogTypeTable = virtualSchemaTable{

• add the relevant logic tests

[asing1]

@otan I can start looking at it. Please assign it to me.

[surahman]

@otan I have the PR underway but I am unsure of the changes that must happen for the pgCatalogTypeTable. Could you please elaborate I am new to the code base?

[otan]

Thanks for looking @surahman ! i've added some comments in #82304 (comment)