Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-41571

SRID arguments >= 2^32 are silently taken modulo 2^32: ST_SRID(ST_GeomFromText('POINT(0 0)', 4294967297)) = 1

    XMLWordPrintable

Details

    Description

      An SRID is an unsigned 32-bit value (4294967295 is accepted as itself), but the
      SRID argument of the geometry constructors is narrowed with a plain (uint32) cast,
      so a larger value becomes a different, valid-looking SRID, without a warning:

      SELECT ST_SRID(ST_GeomFromText('POINT(0 0)', 4294967295));   -- 4294967295
      SELECT ST_SRID(ST_GeomFromText('POINT(0 0)', 4294967296));   -- 0
      SELECT ST_SRID(ST_GeomFromText('POINT(0 0)', 4294967297));   -- 1
      SELECT ST_SRID(ST_GeomFromText('POINT(0 0)', -4294967295));  -- 1
      SELECT SRID(GeomFromText('LineString(1 1,2 2)', 4294967397)); -- 101
      SELECT ST_SRID(ST_PointFromText('POINT(0 0)', 4294967297));  -- 1
      SELECT ST_SRID(ST_GeomFromWKB(ST_AsWKB(POINT(0,0)), 4294967297));                           -- 1
      SELECT ST_SRID(ST_GeomFromGeoJSON('{"type": "Point", "coordinates": [102.0, 0.0]}', 1, 4294971622));  -- 4326
      

      4294971622 = 2^32 + 4326, so the last value is labelled WGS 84. The wrapped SRID
      is what gets stored:

      CREATE TABLE t (g GEOMETRY);
      INSERT INTO t VALUES (ST_GeomFromText('POINT(0 0)', 4294971622));
      SELECT ST_SRID(g) FROM t;     -- 4326
      

      MySQL raises ERROR 1690 (22003) "SRID value is out of range in 'st_geomfromtext'"
      (and likewise for st_pointfromtext, st_geomfromwkb and st_geomfromgeojson, and for the INSERT) for the
      out-of-range values above (MySQL Bug #80499).

      Where (main, 82964737): sql/item_geofunc.cc

         70      srid= (uint32)args[1]->val_int();     Item_func_geometry_from_text::val_str
         97      srid= (uint32)args[1]->val_int();     Item_func_geometry_from_wkb::val_str
        111      srid= (uint32)args[1]->val_int();     (same function, second path)
        164      srid= (uint32)args[2]->val_int();     Item_func_geometry_from_json::val_str
      

      Not the same code path as MDEV-40971 / MDEV-40987 (JSON path indexes, PERIOD_ADD,
      SUBSTRING_INDEX, ELT): this is the SRID argument of the geometry constructors, in Item_func_geometry_from_text/_wkb/_json::val_str() (sql/item_geofunc.cc);
      a fix there does not change this one.

      ================================================================================
      STEPS TO REPRODUCE
      ================================================================================

      SELECT ST_SRID(ST_GeomFromText('POINT(0 0)', 4294967296));                       -- 1
      SELECT ST_SRID(ST_GeomFromText('POINT(0 0)', 4294967297));                       -- 2
      SELECT ST_SRID(ST_GeomFromText('POINT(0 0)', 4294967295));                       -- 3
      SELECT ST_SRID(ST_GeomFromGeoJSON('{"type": "Point", "coordinates": [102.0, 0.0]}', 1, 4294971622));  -- 4
      

      ================================================================================
      EXPECTED
      ================================================================================

      1.  error (or at least a warning and NULL): SRID out of range
      2.  error (or at least a warning and NULL): SRID out of range
      3.  4294967295
      4.  error (or at least a warning and NULL): SRID out of range
      

      ================================================================================
      ACTUAL (13.2.0 main 82964737; identical on 13.0.2, 11.8.9, 10.11.19, 10.6.28)
      ================================================================================

      1.  0
      2.  1
      3.  4294967295
      4.  4326
      

      No warnings.

      ================================================================================
      SUGGESTED FIX
      ================================================================================

      Read the SRID as a longlong and reject a value outside 0..UINT_MAX32 (or a negative
      signed value) with ER_DATA_OUT_OF_RANGE / ER_WRONG_ARGUMENTS before narrowing,
      in all four places.

      ================================================================================
      ATTACHMENTS
      ================================================================================

      repro.py (pymysql; exits 1 while the recorded wrong answers are returned).
      VERIFY-main-82964737-2026-10-14.txt: transcript on main and the four release images.

      Attachments

        Issue Links

          Activity

            People

              rucha174 Rucha Deodhar
              kehan5800 Ke Han
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.