Skip to content
  • Erik Froseth's avatar
    0e0cd7b2
    Bug#22930020 INVALID DATA FOR ST_GEOMFROMGEOJSON() IS HARD TO DIAGNOSE · 0e0cd7b2
    Erik Froseth authored
    Problem
    =======
    The GeoJSON standard specifies the following: "A complete GeoJSON data
    structure is always an object (in JSON terms).". That means that if the input
    to ST_GeomFromGeoJSON is NOT a JSON object, it is considered illegal. And as
    with almost all other MySQL functions, if any of the input arguments to
    ST_GeomFromGeoJSON is SQL NULL, we return SQL NULL.
    
    The use case reported can be boiled down to the following statements for
    simplicity:
    
      CREATE TABLE t1 (col1 JSON);
      INSERT INTO t1 VALUES ('{ "type": "Feature", "geometry": null,
    "properties": {} }');
      ALTER TABLE t1 ADD COLUMN `col2` geometry GENERATED ALWAYS AS
    (ST_GeomFromGeoJSON(col1->'$.geometry')) VIRTUAL;
      SELECT * FROM t1;
    
    ... or even simpler:
    
      SELECT ST_GeomFromGeoJSON(CAST('null' AS JSON));
    
    The query "SELECT * FROM t1;" fails, because when the virtual column 'col2' tries to
    extract the property 'geometry' from the JSON field, it gets a JSON NULL and
    not a SQL NULL. Hence, it is considered as an illegal/invalid GeoJSON input.
    
    Fix
    ===
    Treat JSON NULL as a SQL NULL in ST_GeomFromGeoJSON, and thus return SQL NULL
    from ST_GeomFromGeoJSON when the input value is a JSON NULL.
    
    Also change Item_func_geomfromgeojson to set maybe_null always to true, since
    a GeoJSON Feature object can cause ST_GeomFromGeoJSON to return SQL NULL.
    
    (cherry picked from commit 42a9f760aa2f5e69cc0e72f899b55ad78dda459d)
    
    Conflicts:
    	mysql-test/suite/gis/r/geojson_functions.result
    	mysql-test/suite/gis/t/geojson_functions.test
    	sql/item_geofunc.cc
    0e0cd7b2
    Bug#22930020 INVALID DATA FOR ST_GEOMFROMGEOJSON() IS HARD TO DIAGNOSE
    Erik Froseth authored
    Problem
    =======
    The GeoJSON standard specifies the following: "A complete GeoJSON data
    structure is always an object (in JSON terms).". That means that if the input
    to ST_GeomFromGeoJSON is NOT a JSON object, it is considered illegal. And as
    with almost all other MySQL functions, if any of the input arguments to
    ST_GeomFromGeoJSON is SQL NULL, we return SQL NULL.
    
    The use case reported can be boiled down to the following statements for
    simplicity:
    
      CREATE TABLE t1 (col1 JSON);
      INSERT INTO t1 VALUES ('{ "type": "Feature", "geometry": null,
    "properties": {} }');
      ALTER TABLE t1 ADD COLUMN `col2` geometry GENERATED ALWAYS AS
    (ST_GeomFromGeoJSON(col1->'$.geometry')) VIRTUAL;
      SELECT * FROM t1;
    
    ... or even simpler:
    
      SELECT ST_GeomFromGeoJSON(CAST('null' AS JSON));
    
    The query "SELECT * FROM t1;" fails, because when the virtual column 'col2' tries to
    extract the property 'geometry' from the JSON field, it gets a JSON NULL and
    not a SQL NULL. Hence, it is considered as an illegal/invalid GeoJSON input.
    
    Fix
    ===
    Treat JSON NULL as a SQL NULL in ST_GeomFromGeoJSON, and thus return SQL NULL
    from ST_GeomFromGeoJSON when the input value is a JSON NULL.
    
    Also change Item_func_geomfromgeojson to set maybe_null always to true, since
    a GeoJSON Feature object can cause ST_GeomFromGeoJSON to return SQL NULL.
    
    (cherry picked from commit 42a9f760aa2f5e69cc0e72f899b55ad78dda459d)
    
    Conflicts:
    	mysql-test/suite/gis/r/geojson_functions.result
    	mysql-test/suite/gis/t/geojson_functions.test
    	sql/item_geofunc.cc
Loading