[MDEV-9425] Hybrid functions and UNION do not preserve spatial REF_SYSTEM_ID - Jira

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (g GEOMETRY REF_SYSTEM_ID=101);

DROP TABLE IF EXISTS t2;

CREATE TABLE t2 AS SELECT g AS g_column, COALESCE(g) AS g_coalesce FROM t1;

DROP TABLE IF EXISTS t3;

CREATE TABLE t3 AS SELECT g AS g_union FROM t1 UNION SELECT g FROM t1;

SELECT G_TABLE_NAME, G_GEOMETRY_COLUMN, SRID FROM INFORMATION_SCHEMA.GEOMETRY_COLUMNS;

returns

+--------------+-------------------+------+

| G_TABLE_NAME | G_GEOMETRY_COLUMN | SRID |

+--------------+-------------------+------+

| t1           | g                 |  101 |

| t2           | g_column          |  101 |

| t2           | g_coalesce        |    0 |

| t3           | g_union           |    0 |

+--------------+-------------------+------+

Notice, g_column preserved the original REF_SYSTEM_ID, which is correct.
More complex cases (e.g. g_coalesce and g_union) did not, as they should.

relates to

MDEV-4912 Add a plugin to field types (column types)

MDEV-9405 SP do not preserve geometry type

MDEV-11303 MIN and MAX do not preserve GEOMETRY data type

MDEV-11304 SP do not preserve SRID