mariadb/mysql-test/main/spatial_utility_function_geohash.test

# Copyright (c) 2014, Oracle and/or its affiliates
# Copyright (c) 2024, MariaDB Corporation.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1335  USA

-- disable_warnings

#####################################################################
# ST_LONGFROMGEOHASH()
#####################################################################

# Check for all valid characters and inputs
--echo # valid characters
SELECT ST_LONGFROMGEOHASH("0");

SELECT ST_LONGFROMGEOHASH("z");

SELECT ST_LONGFROMGEOHASH("0z");

SELECT ST_LONGFROMGEOHASH("upbp");

SELECT ST_LONGFROMGEOHASH("h000");

SELECT ST_LONGFROMGEOHASH("s000");

SELECT ST_LONGFROMGEOHASH("0123456789");

SELECT ST_LONGFROMGEOHASH("9876543210");

SELECT ST_LONGFROMGEOHASH("bcdefghjkmnpqrstuvwxyz");

SELECT ST_LONGFROMGEOHASH("zyxwvutsrqpnmkjhgfedcb");

SELECT ST_LONGFROMGEOHASH("zzzzzzzzzzzzzzzzzzzz");

SELECT ST_LONGFROMGEOHASH("pbpbpbpbpbpbpbpbpbpb");

SELECT ST_LONGFROMGEOHASH("bpbpbpbpbpbpbpbpbpbp");

SELECT ST_LONGFROMGEOHASH("00000000000000000000");

SELECT ST_LONGFROMGEOHASH("rzzzzzzzzzzzzzzzzzzz");

SELECT ST_LONGFROMGEOHASH("2pbpbpbpbpbpbpbpbpbp");

SELECT ST_LONGFROMGEOHASH("7zzzzzzzzzzzzzzzzzzz");

SELECT ST_LONGFROMGEOHASH("gzzzzzzzzzzzzzzzzzzz");

SELECT ST_LONGFROMGEOHASH("5bpbpbpbpbpbpbpbpbpb");

SELECT ST_LONGFROMGEOHASH("0000000000zzzzzzzzzz");

SELECT ST_LONGFROMGEOHASH("zzzzzzzzzz0000000000");

SELECT ST_LONGFROMGEOHASH("s000000001z7wsg7zzm6");

SELECT ST_LONGFROMGEOHASH("kpbpbpbpbnpkqe5kpbtm");

SELECT ST_LONGFROMGEOHASH("ebpbpbpbpcbe9kuebp6d");

SELECT ST_LONGFROMGEOHASH("7zzzzzzzzy0s37hs00dt");

SELECT ST_LONGFROMGEOHASH("tzzzzzzzzzzzzzzzzzzz");

SELECT ST_LONGFROMGEOHASH("jzzzzzzzzzzzzzzzzzzz");

SELECT ST_LONGFROMGEOHASH("9zzzzzzzzzzzzzzzzzzz");

SELECT ST_LONGFROMGEOHASH("1zzzzzzzzzzzzzzzzzzz");

SELECT ST_LONGFROMGEOHASH("ypzpgxczbzurypzpgxcz");

SELECT ST_LONGFROMGEOHASH("czbzurypzpgxczbzuryp");

SELECT ST_LONGFROMGEOHASH("0z0z0z0z0z0z0z0z0z0z0z0z0z0z0z0z");

SELECT ST_LONGFROMGEOHASH("0123456789bcdefghjkmnpqrstuvwxyz");

SELECT ST_LONGFROMGEOHASH("0123456789BCDEFGHJKMNPQRSTUVWXYZ");

SELECT ST_LONGFROMGEOHASH("zyxwvutsrqpnmkjhgfedcb9876543210");

SELECT ST_LONGFROMGEOHASH("ZYXWVUTSRQPNMKJHGFEDCB9876543210");

SELECT ST_LONGFROMGEOHASH("1e1");

SELECT ST_LONGFROMGEOHASH("100");

SELECT ST_LONGFROMGEOHASH(CAST(100 AS CHAR));

SELECT ST_LONGFROMGEOHASH("10111000110001111001");

SELECT ST_LONGFROMGEOHASH("11111111111111111111");

SELECT ST_LONGFROMGEOHASH("99999999999999999999");

SELECT ST_LONGFROMGEOHASH(CAST("012" AS BINARY));

SELECT ST_LONGFROMGEOHASH(NULL);

SELECT ST_LONGFROMGEOHASH(null);

# Invalid characters and inputs
--echo # invalid characters and inputs
--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_LONGFROMGEOHASH("0123a45");

--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_LONGFROMGEOHASH("xyzi");

--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_LONGFROMGEOHASH("zyxLwv");

--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_LONGFROMGEOHASH("bcdjo");

--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_LONGFROMGEOHASH("zyx**wv");

--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_LONGFROMGEOHASH("1 2 3 4");

--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_LONGFROMGEOHASH("1''2345");

--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_LONGFROMGEOHASH("12.345");

--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_LONGFROMGEOHASH("   ");

--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_LONGFROMGEOHASH("NULL");

--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_LONGFROMGEOHASH("0a 0d");

--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_LONGFROMGEOHASH("-100");

--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_LONGFROMGEOHASH("");

--error ER_GIS_INVALID_DATA
SELECT ST_LONGFROMGEOHASH(9876543210);

--error ER_GIS_INVALID_DATA
SELECT ST_LONGFROMGEOHASH(0123456789);

--error ER_GIS_INVALID_DATA
SELECT ST_LONGFROMGEOHASH(1e1);

--error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
SELECT ST_LONGFROMGEOHASH();

--error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
SELECT ST_LONGFROMGEOHASH("123","456");

--error ER_PARSE_ERROR
SELECT ST_LONGFROMGEOHASH("123",);

--error ER_PARSE_ERROR
SELECT ST_LONGFROMGEOHASH(,"456");

--error ER_PARSE_ERROR
SELECT ST_LONGFROMGEOHASH(,);

--error ER_PARSE_ERROR
SELECT ST_LONGFROMGEOHASH("0123456"789);

--error ER_BAD_FIELD_ERROR
SELECT ST_LONGFROMGEOHASH(abcdef);

# Test geohashes that are long
--echo # very long geohash
SELECT ST_LONGFROMGEOHASH("0123456789bcdefghjkmnpqrstuvwxyz0123456789bcdefghjkm"
                          "npqrstuvwxyz0123456789bcdefghjkmnpqrstuvwxyz01234567"
                          "89bcdefghjkmnpqrstuvwxyz") c;

SELECT ST_LONGFROMGEOHASH("0123456789BCDEFGHJKMNPQRSTUVWXYZ0123456789BCDEFGHJKM"
                          "NPQRSTUVWXYZ0123456789BCDEFGHJKMNPQRSTUVWXYZ01234567"
                          "89BCDEFGHJKMNPQRSTUVWXYZ") c;

SELECT ST_LONGFROMGEOHASH("zyxwvutsrqpnmkjhgfedcb9876543210zyxwvutsrqpnmkjhgfed"
                          "cb9876543210zyxwvutsrqpnmkjhgfedcb9876543210zyxwvuts"
                          "rqpnmkjhgfedcb9876543210") c;

SELECT ST_LONGFROMGEOHASH("ZYXWVUTSRQPNMKJHGFEDCB9876543210ZYXWVUTSRQPNMKJHGFED"
                          "CB9876543210ZYXWVUTSRQPNMKJHGFEDCB9876543210ZYXWVUTS"
                          "RQPNMKJHGFEDCB9876543210") c;

# Table with different extreme values
CREATE TABLE geohashes  (gid INT NOT NULL PRIMARY KEY, hash_value VARCHAR(255));
INSERT INTO geohashes VALUES
  (1, "000000000000000000000"),
  (2, "zzzzzzzzzzzzzzzzzzzzz"),
  (3, NULL),
  (4, "s00t"),
  (5, "7zzzm"),
  (6, "s00d"),
  (7, "0"),
  (8, "z"),
  (9, "3ejh6z75ddt2d839zh2u"),
  (10, "twtsuqg3q7vh3nrbt0nn"),
  (11, "yw8s10dxddhe4s06nsph"),
  (12, "h4g4h9yrjtgzvewxm0ru"),
  (13, "9kqbredcnhq1b44ue48s"),
  (14, "1pckwjkqw3km0v6ye5d2"),
  (15, "wm313fnr92ggsysm64e6"),
  (16, "vqghx20fx6d8r5vfkbgf"),
  (17, "wvetm3u23kr9r6663k31"),
  (18, "e5t2p7sk291vpyb08pwu");

--echo # different random geohash values
SELECT ST_LONGFROMGEOHASH(hash_value) FROM geohashes;

#####################################################################
# ST_LATFROMGEOHASH()
#####################################################################

# Check for all valid characters and inputs
--echo # valid characters
SELECT ST_LATFROMGEOHASH("0");

SELECT ST_LATFROMGEOHASH("z");

SELECT ST_LATFROMGEOHASH("0z");

SELECT ST_LATFROMGEOHASH("xbpb");

SELECT ST_LATFROMGEOHASH("8000");

SELECT ST_LATFROMGEOHASH("s000");

SELECT ST_LATFROMGEOHASH("0123456789");

SELECT ST_LATFROMGEOHASH("9876543210");

SELECT ST_LATFROMGEOHASH("bcdefghjkmnpqrstuvwxyz");

SELECT ST_LATFROMGEOHASH("zyxwvutsrqpnmkjhgfedcb");

SELECT ST_LATFROMGEOHASH("zzzzzzzzzzzzzzzzzzzz");

SELECT ST_LATFROMGEOHASH("bpbpbpbpbpbpbpbpbpbp");

SELECT ST_LATFROMGEOHASH("pbpbpbpbpbpbpbpbpbpb");

SELECT ST_LATFROMGEOHASH("00000000000000000000");

SELECT ST_LATFROMGEOHASH("gzzzzzzzzzzzzzzzzzzz");

SELECT ST_LATFROMGEOHASH("5bpbpbpbpbpbpbpbpbpb");

SELECT ST_LATFROMGEOHASH("7zzzzzzzzzzzzzzzzzzz");

SELECT ST_LATFROMGEOHASH("rzzzzzzzzzzzzzzzzzzz");

SELECT ST_LATFROMGEOHASH("2pbpbpbpbpbpbpbpbpbp");

SELECT ST_LATFROMGEOHASH("0000000000zzzzzzzzzz");

SELECT ST_LATFROMGEOHASH("zzzzzzzzzz0000000000");

SELECT ST_LATFROMGEOHASH("s000000001z7wsg7zzm6");

SELECT ST_LATFROMGEOHASH("ebpbpbpbpcbe9kuebp6d");

SELECT ST_LATFROMGEOHASH("kpbpbpbpbnpkqe5kpbtm");

SELECT ST_LATFROMGEOHASH("7zzzzzzzzy0s37hs00dt");

SELECT ST_LATFROMGEOHASH("tzzzzzzzzzzzzzzzzzzz");

SELECT ST_LATFROMGEOHASH("9zzzzzzzzzzzzzzzzzzz");

SELECT ST_LATFROMGEOHASH("jzzzzzzzzzzzzzzzzzzz");

SELECT ST_LATFROMGEOHASH("1zzzzzzzzzzzzzzzzzzz");

SELECT ST_LATFROMGEOHASH("zbzurypzpgxczbzurypz");

SELECT ST_LATFROMGEOHASH("5zpgxczbzurypzpgxczb");

SELECT ST_LATFROMGEOHASH("0z0z0z0z0z0z0z0z0z0z0z0z0z0z0z0z");

SELECT ST_LATFROMGEOHASH("0123456789bcdefghjkmnpqrstuvwxyz");

SELECT ST_LATFROMGEOHASH("0123456789BCDEFGHJKMNPQRSTUVWXYZ");

SELECT ST_LATFROMGEOHASH("zyxwvutsrqpnmkjhgfedcb9876543210");

SELECT ST_LATFROMGEOHASH("ZYXWVUTSRQPNMKJHGFEDCB9876543210");

SELECT ST_LATFROMGEOHASH("1e1");

SELECT ST_LATFROMGEOHASH("100");

SELECT ST_LATFROMGEOHASH(CAST(100 AS CHAR));

SELECT ST_LATFROMGEOHASH("10111000110001111001");

SELECT ST_LATFROMGEOHASH("11111111111111111111");

SELECT ST_LATFROMGEOHASH("99999999999999999999");

SELECT ST_LATFROMGEOHASH(NULL);

SELECT ST_LATFROMGEOHASH(null);

SELECT ST_LATFROMGEOHASH(CAST("012" AS BINARY));

# Invalid characters and inputs
--echo # invalid characters and inputs
--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_LATFROMGEOHASH("0123a45");

--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_LATFROMGEOHASH("xyzi");

--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_LATFROMGEOHASH("zyxLwv");

--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_LATFROMGEOHASH("bcdjo");

--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_LATFROMGEOHASH("zyx**wv");

--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_LATFROMGEOHASH("1 2 3 4");

--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_LATFROMGEOHASH("1''2345");

--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_LATFROMGEOHASH("12.345");

--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_LATFROMGEOHASH("   ");

--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_LATFROMGEOHASH("NULL");

--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_LATFROMGEOHASH("-100");

--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_LATFROMGEOHASH("");

--error ER_GIS_INVALID_DATA
SELECT ST_LATFROMGEOHASH(9876543210);

--error ER_GIS_INVALID_DATA
SELECT ST_LATFROMGEOHASH(0123456789);

--error ER_GIS_INVALID_DATA
SELECT ST_LATFROMGEOHASH(1e1);

--error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
SELECT ST_LATFROMGEOHASH();

--error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
SELECT ST_LATFROMGEOHASH("123","456");

--error ER_PARSE_ERROR
SELECT ST_LATFROMGEOHASH("123",);

--error ER_PARSE_ERROR
SELECT ST_LATFROMGEOHASH(,"456");

--error ER_PARSE_ERROR
SELECT ST_LATFROMGEOHASH(,);

--error ER_PARSE_ERROR
SELECT ST_LATFROMGEOHASH("0123456"789);

--error ER_BAD_FIELD_ERROR
SELECT ST_LATFROMGEOHASH(abcdef);

# Test geohashes that are long
--echo # very long geohash
SELECT ST_LATFROMGEOHASH("0123456789bcdefghjkmnpqrstuvwxyz0123456789bcdefghjkmn"
                         "pqrstuvwxyz0123456789bcdefghjkmnpqrstuvwxyz0123456789"
                         "bcdefghjkmnpqrstuvwxyz") c;

SELECT ST_LATFROMGEOHASH("0123456789BCDEFGHJKMNPQRSTUVWXYZ0123456789BCDEFGHJKMN"
                         "PQRSTUVWXYZ0123456789BCDEFGHJKMNPQRSTUVWXYZ0123456789"
                         "BCDEFGHJKMNPQRSTUVWXYZ") c;

SELECT ST_LATFROMGEOHASH("zyxwvutsrqpnmkjhgfedcb9876543210zyxwvutsrqpnmkjhgfedc"
                         "b9876543210zyxwvutsrqpnmkjhgfedcb9876543210zyxwvutsrq"
                         "pnmkjhgfedcb9876543210") c;

SELECT ST_LATFROMGEOHASH("ZYXWVUTSRQPNMKJHGFEDCB9876543210ZYXWVUTSRQPNMKJHGFEDC"
                         "B9876543210ZYXWVUTSRQPNMKJHGFEDCB9876543210ZYXWVUTSRQ"
                         "PNMKJHGFEDCB9876543210") c;

--echo # different random geohash values
SELECT ST_LATFROMGEOHASH(hash_value) FROM geohashes;

#####################################################################
# ST_POINTFROMGEOHASH()
#####################################################################

# Check for all valid characters and inputs
--echo # valid characters
SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("0", 0));

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("z", 0));

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("0z", 0));

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("xbpb", 0));

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("8000", 0));

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("s000", 0));

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("0123456789", 0));

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("9876543210", 0));

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("bcdefghjkmnpqrstuvwxyz", 0));

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("zyxwvutsrqpnmkjhgfedcb", 0));

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("zzzzzzzzzzzzzzzzzzzz", 0));

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("bpbpbpbpbpbpbpbpbpbp", 0));

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("pbpbpbpbpbpbpbpbpbpb", 0));

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("00000000000000000000", 0));

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("gzzzzzzzzzzzzzzzzzzz", 0));

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("5bpbpbpbpbpbpbpbpbpb", 0));

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("7zzzzzzzzzzzzzzzzzzz", 0));

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("rzzzzzzzzzzzzzzzzzzz", 0));

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("2pbpbpbpbpbpbpbpbpbp", 0));

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("0000000000zzzzzzzzzz", 0));

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("zzzzzzzzzz0000000000", 0));

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("s000000001z7wsg7zzm6", 0));

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("ebpbpbpbpcbe9kuebp6d", 0));

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("kpbpbpbpbnpkqe5kpbtm", 0));

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("7zzzzzzzzy0s37hs00dt", 0));

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("tzzzzzzzzzzzzzzzzzzz", 0));

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("9zzzzzzzzzzzzzzzzzzz", 0));

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("jzzzzzzzzzzzzzzzzzzz", 0));

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("1zzzzzzzzzzzzzzzzzzz", 0));

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("zbzurypzpgxczbzurypz", 0));

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("5zpgxczbzurypzpgxczb", 0));

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("0z0z0z0z0z0z0z0z0z0z0z0z0z0z0z0z", 0)) c;

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("0123456789bcdefghjkmnpqrstuvwxyz", 0)) c;

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("0123456789BCDEFGHJKMNPQRSTUVWXYZ", 0)) c;

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("zyxwvutsrqpnmkjhgfedcb9876543210", 0)) c;

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("ZYXWVUTSRQPNMKJHGFEDCB9876543210", 0)) c;

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("1e1", " "));

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("100", "    0"));

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(CAST(100 AS CHAR), 0));

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("10111000110001111001", "0"));

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("11111111111111111111", 0));

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("99999999999999999999", 0));

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("00000000000000000000", " ***** ")) c;

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("00000000000000000000", " 0 "));

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("zzzzzzzzzzzzzzzzzzzz", (CAST(0 AS CHAR)))) c;

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(NULL, 0));

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(null, 0));

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("tzzzzzzzzzzzzzzzzzzz", NULL));

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("9zzzzzzzzzzzzzzzzzzz", null));

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(CAST("012" AS BINARY), 0));

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("zzzzzzzzzzzzzzzzzzzz", (CAST(0 AS BINARY)))) c;

# Invalid characters and inputs

--echo # invalid characters and inputs
--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("0123a45", 0));

--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("xyzi", 0));

--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("zyxLwv", 0));

--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("bcdjo", 0));

--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("zyx**wv", 0));

--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("1 2 3 4", 0));

--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("1''2345", 0));

--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("12.345", 0));

--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("   ", 0));

--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("NULL", 0));

--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("0a 0d", 0));

--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("-100", 0));

--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("", 2000));

--error ER_GIS_INVALID_DATA
SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(9876543210, 0));

--error ER_GIS_INVALID_DATA
SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(0123456789, 0));

--error ER_GIS_INVALID_DATA
SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(1e1, 0));

--error ER_PARSE_ERROR
SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("123",));

--error ER_PARSE_ERROR
SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(,"456"));

--error ER_PARSE_ERROR
SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(,));

--error ER_PARSE_ERROR
SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("0123456"789));

--error ER_PARSE_ERROR
SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("7zzzzzzzzzzzzzzzzzzz", ****));

--error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("zzzzzzzzzzzzzzzzzzzz"));

--error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
SELECT ST_ASTEXT(ST_POINTFROMGEOHASH());

--error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(abcdef));

--error ER_OPERAND_COLUMNS
SELECT ST_ASTEXT(ST_POINTFROMGEOHASH((1, 1), 1));

# Test geohashes that are long
--echo # very long geohash
SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("0123456789bcdefghjkmnpqrstuvwxyz0123456789bc"
                                     "defghjkmnpqrstuvwxyz0123456789bcdefghjkmnpqr"
                                     "stuvwxyz0123456789bcdefghjkmnpqrstuvwxyz", 0)) c;

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("0123456789BCDEFGHJKMNPQRSTUVWXYZ0123456789BC"
                                     "DEFGHJKMNPQRSTUVWXYZ0123456789BCDEFGHJKMNPQR"
                                     "STUVWXYZ0123456789BCDEFGHJKMNPQRSTUVWXYZ", 0)) c;

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("zyxwvutsrqpnmkjhgfedcb9876543210zyxwvutsrqpn"
                                     "mkjhgfedcb9876543210zyxwvutsrqpnmkjhgfedcb98"
                                     "76543210zyxwvutsrqpnmkjhgfedcb9876543210", 0)) c;

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("ZYXWVUTSRQPNMKJHGFEDCB9876543210ZYXWVUTSRQPN"
                                     "MKJHGFEDCB9876543210ZYXWVUTSRQPNMKJHGFEDCB98"
                                     "76543210ZYXWVUTSRQPNMKJHGFEDCB9876543210", 0)) c;

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("0123456789bcdefghjkmnpqrstuvwxyz0123456789bc"
                                     "defghjkmnpqrstuvwxyz0123456789bcdefghjkmnpqr"
                                     "stuvwxyz0123456789bcdefghjkmnpqrstuvwxyz0123"
                                     "456789bcdefghjkmnpqrstuvwxyz0123456789bcdefg"
                                     "hjkmnpqrstuvwxyz0123456789bcdefghjkmnpqrstuv"
                                     "wxyz01234567890123456789bcdefghjkmnpqrstuvwx"
                                     "yz0123456789bcdefghjkmnpqrstuvwxyz0123456789"
                                     "bcdefghjkmnpqrstuvwxyz0123456789bcdefghjkmnp"
                                     "qrstuvwxyz0123456789bcdefghjkmnpqrstuvwxyz01"
                                     "23456789bcdefghjkmnpqrstuvwxyz0123456789bcde"
                                     "fghjkmnpqrstuvwxyz", 4326)) c;

--echo # different random geohash values
SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(hash_value,0)) FROM geohashes;

--echo # Test create table from SELECT statement
CREATE TABLE t1 AS SELECT ST_POINTFROMGEOHASH("0123", 4326);
EXPLAIN t1;
DROP TABLE t1;

#####################################################################
# ST_GEOHASH()
#####################################################################

# Check for all valid inputs
--echo # valid inputs
SELECT ST_GEOHASH(0,0,1);

SELECT ST_GEOHASH(180,0,20);

SELECT ST_GEOHASH(-180,0,20);

SELECT ST_GEOHASH(0,90,20);

SELECT ST_GEOHASH(0,-90,20);

SELECT ST_GEOHASH(180,90,20);

SELECT ST_GEOHASH(180,-90,20);

SELECT ST_GEOHASH(-180,90,20);

SELECT ST_GEOHASH(-180,-90,20);

SELECT ST_GEOHASH(100,45,5);

SELECT ST_GEOHASH(100,-45,10);

SELECT ST_GEOHASH(-100,45,+50);

SELECT ST_GEOHASH(0.0001,0.0001,10);

SELECT ST_GEOHASH(0.0001,-0.0001,30);

SELECT ST_GEOHASH(-0.0001,0.0001,90);

SELECT ST_GEOHASH(-0.0001,-0.0001,100);

SELECT ST_GEOHASH("",90,10);

SELECT ST_GEOHASH(0,"90",10);

SELECT ST_GEOHASH("0","0",10);

SELECT ST_GEOHASH(180,90,"20");

SELECT ST_GEOHASH("180","90","20");

SELECT ST_GEOHASH("***",90,"20");

SELECT ST_GEOHASH(180,"***",20);

SELECT ST_GEOHASH("abcd",90,20);

SELECT ST_GEOHASH(180,"abcd",25-5);

SELECT ST_GEOHASH(NULL,90,10);

SELECT ST_GEOHASH(180,NULL,10);

SELECT ST_GEOHASH(180,90,NULL);

SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(0 0)'),10);

SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(180 0)'),20);

SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(-180 0)'),25);

SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(0 90)'),30);

SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(0 -90)'),35);

SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(180 90)'),40);

SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(180 -90)'),45);

SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(-180 90)'),50);

SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(-180 -90)'),55);

SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(100 45)'),60);

SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(100 -45)'),65);

SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(-100 45)'),70);

SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(-100 -45)'),75);

SELECT ST_GEOHASH(ST_GEOMFROMWKB(ST_ASWKB(ST_GEOMFROMTEXT('POINT(0.0001 0.0001)'))),80) c;

SELECT ST_GEOHASH(ST_GEOMFROMWKB(ST_ASWKB(ST_GEOMFROMTEXT('POINT(0.0001 -0.0001)'))),85) c;

SELECT ST_GEOHASH(ST_GEOMFROMWKB(ST_ASWKB(ST_GEOMFROMTEXT('POINT(-0.0001 0.0001)'))),90) c;

SELECT ST_GEOHASH(ST_GEOMFROMWKB(ST_ASWKB(ST_GEOMFROMTEXT('POINT(-0.0001 -0.0001)'))),100) c;

SELECT ST_GEOHASH(ST_GEOMFROMWKB(0x010100000000000000008066400000000000805640),20) c;

SELECT ST_GEOHASH(ST_GEOMFROMWKB(0x010100000000000000008066C00000000000805640),20) c;

SELECT ST_GEOHASH(ST_GEOMFROMWKB(0x00000000014066800000000000C056800000000000),20) c;

SELECT ST_GEOHASH(ST_GEOMFROMWKB(0x0000000001C066800000000000C056800000000000),20) c;

SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(0 0)'),"1");

SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(0 0)')," 10 ");

SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(0 0)'),"+100");

SELECT ST_GEOHASH(NULL,100);

SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(-0.0001 -0.0001)'),NULL);

SELECT ST_GEOHASH(ST_DIFFERENCE(ST_GEOMFROMTEXT('POINT(180 90)'),ST_GEOMFROMTEXT('POINT(0 0)')),20) c;

SELECT ST_GEOHASH(ST_DIFFERENCE(ST_GEOMFROMTEXT('POINT(-180 90)'),ST_GEOMFROMTEXT('MULTIPOINT(0 0,100 100)')),20) c;

SELECT ST_GEOHASH(ST_INTERSECTION(ST_GEOMFROMTEXT('POINT(180 -90)'),ST_GEOMFROMTEXT('POINT(180 -90)')),20) c;

SELECT ST_GEOHASH(ST_INTERSECTION(ST_GEOMFROMTEXT('POINT(-180 -90)'),ST_GEOMFROMTEXT('POINT(-180 -90)')),20) c;

SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(180 90)'),(CAST(10 AS BINARY))) c;

SELECT ST_GEOHASH((CAST(10 AS BINARY)),20,1);

SELECT ST_GEOHASH(10,(CAST(20 AS BINARY)),1);

SELECT ST_GEOHASH((CAST(10 AS BINARY)),(CAST(20 AS BINARY)),1);

SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT()'),20);

SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(1)'),20);

SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(1 a)'),20);

SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(180,90)'),20);

# Invalid inputs
--echo # invalid inputs
--error ER_STD_OUT_OF_RANGE_ERROR
SELECT ST_GEOHASH(181,0,10);

--error ER_STD_OUT_OF_RANGE_ERROR
SELECT ST_GEOHASH(1000,90,10);

--error ER_STD_OUT_OF_RANGE_ERROR
SELECT ST_GEOHASH(180.0000000000001,-90,10);

--error ER_STD_OUT_OF_RANGE_ERROR
SELECT ST_GEOHASH(-181,0,10);

--error ER_STD_OUT_OF_RANGE_ERROR
SELECT ST_GEOHASH(-1000,90,10);

--error ER_STD_OUT_OF_RANGE_ERROR
SELECT ST_GEOHASH(-180.0000000000001,-90,10);

--error ER_STD_OUT_OF_RANGE_ERROR
SELECT ST_GEOHASH(0,91,10);

--error ER_STD_OUT_OF_RANGE_ERROR
SELECT ST_GEOHASH(180,500,10);

--error ER_STD_OUT_OF_RANGE_ERROR
SELECT ST_GEOHASH(-180,90.0000000000001,10);

--error ER_STD_OUT_OF_RANGE_ERROR
SELECT ST_GEOHASH(0,-91,10);

--error ER_STD_OUT_OF_RANGE_ERROR
SELECT ST_GEOHASH(180,-500,10);

--error ER_STD_OUT_OF_RANGE_ERROR
SELECT ST_GEOHASH(-180,-90.0000000000001,10);

--error ER_STD_OUT_OF_RANGE_ERROR
SELECT ST_GEOHASH(180,90,0);

--error ER_STD_OUT_OF_RANGE_ERROR
SELECT ST_GEOHASH(-180,90,-1);

--error ER_STD_OUT_OF_RANGE_ERROR
SELECT ST_GEOHASH(180,-90,-100);

--error ER_STD_OUT_OF_RANGE_ERROR
SELECT ST_GEOHASH(-180,-90,101);

--error ER_STD_OUT_OF_RANGE_ERROR
SELECT ST_GEOHASH(0,90,1000);

--error ER_STD_OUT_OF_RANGE_ERROR
SELECT ST_GEOHASH("181",90,20);

--error ER_STD_OUT_OF_RANGE_ERROR
SELECT ST_GEOHASH("-181",90,20);

--error ER_STD_OUT_OF_RANGE_ERROR
SELECT ST_GEOHASH(180,"91",20);

--error ER_STD_OUT_OF_RANGE_ERROR
SELECT ST_GEOHASH(180,"-91",20);

--error ER_STD_OUT_OF_RANGE_ERROR
SELECT ST_GEOHASH(180,90,"0");

--error ER_STD_OUT_OF_RANGE_ERROR
SELECT ST_GEOHASH(180,90,"-1");

--error ER_STD_OUT_OF_RANGE_ERROR
SELECT ST_GEOHASH(180,90,"-100");

--error ER_STD_OUT_OF_RANGE_ERROR
SELECT ST_GEOHASH(180,90,"101");

--error ER_STD_OUT_OF_RANGE_ERROR
SELECT ST_GEOHASH(180,90,"1000");

--error ER_STD_OUT_OF_RANGE_ERROR
SELECT ST_GEOHASH(180,90,"");

--error ER_STD_OUT_OF_RANGE_ERROR
SELECT ST_GEOHASH(180,90,"****");

--error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
SELECT ST_GEOHASH();

--error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
SELECT ST_GEOHASH(1);

--error ER_GIS_INVALID_DATA
SELECT ST_GEOHASH(1,2);

# --error ER_GIS_INVALID_DATA
# SELECT ST_GEOHASH(-100,-45,100.1);

--error ER_PARSE_ERROR
SELECT ST_GEOHASH(, ,);

--error ER_PARSE_ERROR
SELECT ST_GEOHASH(1,2,);

--error ER_PARSE_ERROR
SELECT ST_GEOHASH(*,0,10);

--error ER_PARSE_ERROR
SELECT ST_GEOHASH(0,*,10);

--error ER_PARSE_ERROR
SELECT ST_GEOHASH(0,0,*);

--error ER_BAD_FIELD_ERROR
SELECT ST_GEOHASH(a,90,10);

--error ER_BAD_FIELD_ERROR
SELECT ST_GEOHASH(180,p,10);

--error ER_BAD_FIELD_ERROR
SELECT ST_GEOHASH(180,90,z);

--error ER_GIS_INVALID_DATA
SELECT ST_GEOHASH((CAST(180 AS DATE)),90,10);

--error ER_GIS_INVALID_DATA
SELECT ST_GEOHASH(180,(CAST(90 AS DATE)),10);

--error ER_STD_OUT_OF_RANGE_ERROR
SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(181 0)'),10);

--error ER_STD_OUT_OF_RANGE_ERROR
SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(1000 90)'),10);

--error ER_STD_OUT_OF_RANGE_ERROR
SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(180.0000000000001 -90)'),10);

--error ER_STD_OUT_OF_RANGE_ERROR
SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(-181 0)'),10);

--error ER_STD_OUT_OF_RANGE_ERROR
SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(-1000 90)'),10);

--error ER_STD_OUT_OF_RANGE_ERROR
SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(-180.0000000000001 -90)'),10);

--error ER_STD_OUT_OF_RANGE_ERROR
SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(0 91)'),10);

--error ER_STD_OUT_OF_RANGE_ERROR
SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(180 500)'),10);

--error ER_STD_OUT_OF_RANGE_ERROR
SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(-180 90.0000000000001)'),10);

--error ER_STD_OUT_OF_RANGE_ERROR
SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(0 -91)'),10);

--error ER_STD_OUT_OF_RANGE_ERROR
SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(180 -500)'),10);

--error ER_STD_OUT_OF_RANGE_ERROR
SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(-180 -90.0000000000001)'),10);

--error ER_STD_OUT_OF_RANGE_ERROR
SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(180 90)'),0);

--error ER_STD_OUT_OF_RANGE_ERROR
SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(180 -90)'),-1);

--error ER_STD_OUT_OF_RANGE_ERROR
SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(-180 90)'),-100);

--error ER_STD_OUT_OF_RANGE_ERROR
SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(-180 -90)'),101);

--error ER_STD_OUT_OF_RANGE_ERROR
SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(0 0)'),10000);

--error ER_STD_OUT_OF_RANGE_ERROR
SELECT ST_GEOHASH(ST_GEOMFROMWKB(ST_ASWKB(ST_GEOMFROMTEXT('POINT(180 90)'))),"0");

--error ER_STD_OUT_OF_RANGE_ERROR
SELECT ST_GEOHASH(ST_GEOMFROMWKB(ST_ASWKB(ST_GEOMFROMTEXT('POINT(180 -90)'))),"-1");

--error ER_STD_OUT_OF_RANGE_ERROR
SELECT ST_GEOHASH(ST_GEOMFROMWKB(ST_ASWKB(ST_GEOMFROMTEXT('POINT(-180 90)'))),"-100");

--error ER_STD_OUT_OF_RANGE_ERROR
SELECT ST_GEOHASH(ST_GEOMFROMWKB(ST_ASWKB(ST_GEOMFROMTEXT('POINT(-180 -90)'))),"101");

--error ER_STD_OUT_OF_RANGE_ERROR
SELECT ST_GEOHASH(ST_GEOMFROMWKB(ST_ASWKB(ST_GEOMFROMTEXT('POINT(0 0)'))),"10000");

--error ER_STD_OUT_OF_RANGE_ERROR
SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(0 0)')," ");

--error ER_STD_OUT_OF_RANGE_ERROR
SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(0 0)'),"***");

--error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(0 0)'));

--error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
SELECT ST_GEOHASH(ST_GEOMFROMTEXT(),20);

--error ER_PARSE_ERROR
SELECT ST_GEOHASH((ST_GEOMFROMTEXT('POINT(0 0)'),);

--error ER_PARSE_ERROR
SELECT ST_GEOHASH(,10);

--error ER_BAD_FIELD_ERROR
SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(0 0)'),a);

--error ER_GIS_INVALID_DATA
SELECT ST_GEOHASH(ST_GEOMFROMTEXT('LINESTRING(0 0,10 10)'),10);

--error ER_GIS_INVALID_DATA
SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POLYGON((0 0,10 0,10 10,0 10,0 0))'),10);

--error ER_GIS_INVALID_DATA
SELECT ST_GEOHASH(ST_GEOMFROMTEXT('MULTIPOINT(0 0,10 10)'),10);

--error ER_GIS_INVALID_DATA
SELECT ST_GEOHASH(ST_GEOMFROMTEXT('MULTILINESTRING((0 0,10 10),(20 20,30 30))'),10);

--error ER_GIS_INVALID_DATA
SELECT ST_GEOHASH(ST_GEOMFROMTEXT('MULTIPOLYGON(((0 0,0 10,10 10,10 0,0 0),(4 4,4 6,6 6,6 4,4 4)))'),10);

--error ER_GIS_INVALID_DATA
SELECT ST_GEOHASH(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(0 0))'),10);

--error ER_STD_OUT_OF_RANGE_ERROR
SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(180 90)'),20.0001);

--error ER_STD_OUT_OF_RANGE_ERROR
SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(180 90)'),(CAST(10 AS DATE)));

--error ER_GIS_INVALID_DATA
SELECT ST_GEOHASH(ST_INTERSECTION(ST_GEOMFROMTEXT('POINT(180 90)'),ST_GEOMFROMTEXT('POINT(1 1)')),20);

--error ER_GIS_INVALID_DATA
SELECT ST_GEOHASH(ST_UNION(ST_GEOMFROMTEXT('POINT(180 90)'),ST_GEOMFROMTEXT('POINT(-180 -90)')),20);

--error ER_GIS_INVALID_DATA
SELECT ST_GEOHASH(ST_DIFFERENCE(ST_GEOMFROMTEXT('POINT(180 90)'),ST_GEOMFROMTEXT('MULTIPOINT(0 0,180 90)')),20);

--error ER_GIS_INVALID_DATA
SELECT ST_GEOHASH(ST_SYMDIFFERENCE(ST_GEOMFROMTEXT('POINT(180 90)'),ST_GEOMFROMTEXT('POINT(0 0)')),20);

#####################################################################
# Nested geohash functions
#####################################################################

--echo #=============================================================
--echo # ST_LONGFROMGEOHASH()
--echo #=============================================================
SELECT ST_LONGFROMGEOHASH(ST_GEOHASH(180,90,20));

SELECT ST_LONGFROMGEOHASH(ST_GEOHASH(180,-90,20));

SELECT ST_LONGFROMGEOHASH(ST_GEOHASH(-180,90,20));

SELECT ST_LONGFROMGEOHASH(ST_GEOHASH(-180,-90,20));

SELECT ST_LONGFROMGEOHASH(ST_GEOHASH(180,0,20));

SELECT ST_LONGFROMGEOHASH(ST_GEOHASH(-180,0,20));

SELECT ST_LONGFROMGEOHASH(ST_GEOHASH(-0,0,20));

SELECT ST_LONGFROMGEOHASH(ST_GEOHASH(0.10,90,20));

SELECT ST_LONGFROMGEOHASH(ST_GEOHASH(100,45,20));

SELECT ST_LONGFROMGEOHASH(ST_GEOHASH(179.999999,90,20));

SELECT ST_LONGFROMGEOHASH(ST_GEOHASH(-179.999999,0,20));

SELECT ST_LONGFROMGEOHASH(ST_GEOHASH(18*10,900/10,20));

SELECT ST_LONGFROMGEOHASH(ST_GEOHASH(ST_GEOMFROMTEXT('POINT(100 10)'),10)) c;

SELECT ST_LONGFROMGEOHASH(ST_GEOHASH(ST_GEOMFROMTEXT('POINT(10 10)'),10)) c;

SELECT ST_LONGFROMGEOHASH(ST_GEOHASH(ST_GEOMFROMTEXT('POINT(-10 10)'),10)) c;

SELECT ST_LONGFROMGEOHASH(ST_GEOHASH(ST_GEOMFROMTEXT('POINT(1.000 10)'),10)) c;

SELECT ST_LONGFROMGEOHASH(ST_GEOHASH(ST_GEOMFROMTEXT('POINT(-1.000 10)'),10)) c;

SELECT ST_LONGFROMGEOHASH(ST_GEOHASH(ST_GEOMFROMTEXT('POINT(1.00101 90)'),20)) c;

SELECT ST_LONGFROMGEOHASH(ST_GEOHASH(ST_GEOMFROMTEXT('POINT(-1.00101 90)'),20)) c;

SELECT ST_LONGFROMGEOHASH(ST_GEOHASH(ST_GEOMFROMTEXT('POINT(20 20)'),10)) c;

--echo #=============================================================
--echo # ST_LATFROMGEOHASH()
--echo #=============================================================
SELECT ST_LATFROMGEOHASH(ST_GEOHASH(180,90,20));

SELECT ST_LATFROMGEOHASH(ST_GEOHASH(180,-90,20));

SELECT ST_LATFROMGEOHASH(ST_GEOHASH(-180,90,20));

SELECT ST_LATFROMGEOHASH(ST_GEOHASH(-180,-90,20));

SELECT ST_LATFROMGEOHASH(ST_GEOHASH(0,90,20));

SELECT ST_LATFROMGEOHASH(ST_GEOHASH(0,-90,20));

SELECT ST_LATFROMGEOHASH(ST_GEOHASH(0,-0,20));

SELECT ST_LATFROMGEOHASH(ST_GEOHASH(180,0.101,20));

SELECT ST_LATFROMGEOHASH(ST_GEOHASH(100,45,20));

SELECT ST_LATFROMGEOHASH(ST_GEOHASH(180,89.99999990,20));

SELECT ST_LATFROMGEOHASH(ST_GEOHASH(0,89.999999,20));

SELECT ST_LATFROMGEOHASH(ST_GEOHASH(18*10,900/10,20));

SELECT ST_LATFROMGEOHASH(ST_GEOHASH(ST_GEOMFROMTEXT('POINT(10 50)'),10)) c;

SELECT ST_LATFROMGEOHASH(ST_GEOHASH(ST_GEOMFROMTEXT('POINT(10 10)'),10)) c;

SELECT ST_LATFROMGEOHASH(ST_GEOHASH(ST_GEOMFROMTEXT('POINT(10 -10)'),10)) c;

SELECT ST_LATFROMGEOHASH(ST_GEOHASH(ST_GEOMFROMTEXT('POINT(10 1.000)'),10)) c;

SELECT ST_LATFROMGEOHASH(ST_GEOHASH(ST_GEOMFROMTEXT('POINT(10 -1.0000)'),10)) c;

SELECT ST_LATFROMGEOHASH(ST_GEOHASH(ST_GEOMFROMTEXT('POINT(180 1.00101)'),20)) c;

SELECT ST_LATFROMGEOHASH(ST_GEOHASH(ST_GEOMFROMTEXT('POINT(-100 -1.00101)'),20)) c;

SELECT ST_LATFROMGEOHASH(ST_GEOHASH(ST_GEOMFROMTEXT('POINT(20 -20)'),10)) c;

--echo #=============================================================
--echo # ST_POINTFROMGEOHASH()
--echo #=============================================================

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(ST_GEOHASH(180,90,20),0));

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(ST_GEOHASH(180,-90,20),0));

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(ST_GEOHASH(-180,90,20),0));

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(ST_GEOHASH(-180,-90,20),0));

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(ST_GEOHASH(180,0,20),0));

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(ST_GEOHASH(-180,0,20),0));

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(ST_GEOHASH(0,90,20),0));

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(ST_GEOHASH(0,-90,20),0));

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(ST_GEOHASH(0,0,20),0));

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(ST_GEOHASH(-0,0,20),0));

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(ST_GEOHASH(0,-0,20),0));

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(ST_GEOHASH(0.10,90,20),0));

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(ST_GEOHASH(100,45,20),0));

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(ST_GEOHASH(179.999999,90,20),0));

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(ST_GEOHASH(-179.999999,0,20),0));

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(ST_GEOHASH(180,89.999999,20),0));

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(ST_GEOHASH(0,-89.999999,20),0));

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(ST_GEOHASH(18*10,900/10,20),0));

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(ST_GEOHASH(ST_GEOMFROMTEXT('POINT(100 50)'),10),0)) c;

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(ST_GEOHASH(ST_GEOMFROMTEXT('POINT(10 10)'),10),0)) c;

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(ST_GEOHASH(ST_GEOMFROMTEXT('POINT(-10 10)'),10),0)) c;

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(ST_GEOHASH(ST_GEOMFROMTEXT('POINT(1.000 1.00010)'),10),0)) c;

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(ST_GEOHASH(ST_GEOMFROMTEXT('POINT(-1.000 -1.1010)'),10),0)) c;

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(ST_GEOHASH(ST_GEOMFROMTEXT('POINT(1.00101 1.000)'),10),0)) c;

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(ST_GEOHASH(ST_GEOMFROMTEXT('POINT(-1.00101 -1.0000)'),20),0)) c;

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(ST_GEOHASH(ST_GEOMFROMTEXT('POINT(20 20)'),10),0)) c;

--echo #=============================================================
--echo # ST_GEOHASH()
--echo #=============================================================
SELECT ST_GEOHASH(ST_POINTFROMGEOHASH("upbp",0),4);

SELECT ST_GEOHASH(ST_POINTFROMGEOHASH("h000",0),8);

SELECT ST_GEOHASH(ST_POINTFROMGEOHASH("s000",0),10);

SELECT ST_GEOHASH(ST_POINTFROMGEOHASH("zzzzm",0),10);

SELECT ST_GEOHASH(ST_POINTFROMGEOHASH("pbpbt",0),10);

SELECT ST_GEOHASH(ST_POINTFROMGEOHASH("bpbp6",0),10);

SELECT ST_GEOHASH(ST_POINTFROMGEOHASH("0000d",0),10);

SELECT ST_GEOHASH(ST_POINTFROMGEOHASH("xbpbpbpbp",0),9);

SELECT ST_GEOHASH(ST_POINTFROMGEOHASH("800000000",0),12);

SELECT ST_GEOHASH(ST_POINTFROMGEOHASH("zzzzzzzzzzzzzzzzzzzz",0),20);

SELECT ST_GEOHASH(ST_POINTFROMGEOHASH("00000000000000000000",0),20);

SELECT ST_GEOHASH(ST_POINTFROMGEOHASH("pbpbpbpbpbpbpbpbpbpb",0),20);

SELECT ST_GEOHASH(ST_POINTFROMGEOHASH("bpbpbpbpbpbpbpbpbpbp",0),20);

SELECT ST_GEOHASH(ST_LONGFROMGEOHASH("zzzzzzzzzzzzzzzzzzzz"),ST_LATFROMGEOHASH("zzzzzzzzzzzzzzzzzzzz"),20) c;

SELECT ST_GEOHASH(ST_LONGFROMGEOHASH("00000000000000000000"),ST_LATFROMGEOHASH("00000000000000000000"),20) c;

SELECT ST_GEOHASH(ST_LONGFROMGEOHASH("zzzzzzzzzzzzzzzzzzzz"),ST_LATFROMGEOHASH("00000000000000000000"),20) c;

SELECT ST_GEOHASH(ST_LONGFROMGEOHASH("00000000000000000000"),ST_LATFROMGEOHASH("zzzzzzzzzzzzzzzzzzzz"),20) c;

SELECT ST_GEOHASH(ST_LONGFROMGEOHASH("pbpbpbpbpbpbpbpbpbpb"),ST_LATFROMGEOHASH("zzzzzzzzzzzzzzzzzzzz"),20) c;

SELECT ST_GEOHASH(ST_LONGFROMGEOHASH("zzzzzzzzzzzzzzzzzzzz"),ST_LATFROMGEOHASH("pbpbpbpbpbpbpbpbpbpb"),20) c;

SELECT ST_GEOHASH(ST_LONGFROMGEOHASH("zzzzzzzzzzzzzzzzzzzz"),ST_LATFROMGEOHASH("bpbpbpbpbpbpbpbpbpbp"),20) c;

SELECT ST_GEOHASH(ST_LONGFROMGEOHASH("bpbpbpbpbpbpbpbpbpbp"),ST_LATFROMGEOHASH("zzzzzzzzzzzzzzzzzzzz"),20) c;

SELECT ST_GEOHASH(ST_LONGFROMGEOHASH("bpbpbpbpbpbpbpbpbpbp"),ST_LATFROMGEOHASH("pbpbpbpbpbpbpbpbpbpb"),20) c;

#####################################################################
# Bug#19657725
#####################################################################
SELECT
  ST_LONGFROMGEOHASH(
    CAST("0123456789bcdefghjkmnpqrstuvwxyz0123456789BCDEFGHJKMNPQRSTUVWXYZ"
         AS CHAR CHARACTER SET utf8) COLLATE utf8_persian_ci) c;

SELECT
  ST_LONGFROMGEOHASH(
    CAST("0123456789bcdefghjkmnpqrstuvwxyz0123456789BCDEFGHJKMNPQRSTUVWXYZ"
         AS CHAR CHARACTER SET utf8) COLLATE utf8_czech_ci) c;

SELECT
  ST_LONGFROMGEOHASH(
    CAST("0123456789bcdefghjkmnpqrstuvwxyz0123456789BCDEFGHJKMNPQRSTUVWXYZ"
         AS CHAR CHARACTER SET utf8) COLLATE utf8_unicode_ci) c;

SELECT
  ST_LONGFROMGEOHASH(
    CAST("0123456789bcdefghjkmnpqrstuvwxyz0123456789BCDEFGHJKMNPQRSTUVWXYZ"
         AS CHAR CHARACTER SET utf8) COLLATE utf8_hungarian_ci) c;

SELECT
  ST_LATFROMGEOHASH(
    CAST("0123456789bcdefghjkmnpqrstuvwxyz0123456789BCDEFGHJKMNPQRSTUVWXYZ"
         AS CHAR CHARACTER SET utf8) COLLATE utf8_persian_ci) c;

SELECT
  ST_LATFROMGEOHASH(
    CAST("0123456789bcdefghjkmnpqrstuvwxyz0123456789BCDEFGHJKMNPQRSTUVWXYZ"
         AS CHAR CHARACTER SET utf8) COLLATE utf8_czech_ci) c;

SELECT
  ST_LATFROMGEOHASH(
    CAST("0123456789bcdefghjkmnpqrstuvwxyz0123456789BCDEFGHJKMNPQRSTUVWXYZ"
         AS CHAR CHARACTER SET utf8) COLLATE utf8_unicode_ci) c;

SELECT
  ST_LATFROMGEOHASH(
    CAST("0123456789bcdefghjkmnpqrstuvwxyz0123456789BCDEFGHJKMNPQRSTUVWXYZ"
         AS CHAR CHARACTER SET utf8) COLLATE utf8_hungarian_ci) c;

SELECT
  ST_ASTEXT(
    ST_POINTFROMGEOHASH(
      CAST("0123456789bcdefghjkmnpqrstuvwxyz0123456789BCDEFGHJKMNPQRSTUVWXYZ"
           AS CHAR CHARACTER SET utf8) COLLATE utf8_persian_ci,
      4326
    )
  ) c;

SELECT
  ST_ASTEXT(
    ST_POINTFROMGEOHASH(
      CAST("0123456789bcdefghjkmnpqrstuvwxyz0123456789BCDEFGHJKMNPQRSTUVWXYZ"
           AS CHAR CHARACTER SET utf8) COLLATE utf8_czech_ci,
      4326
    )
  ) c;

SELECT
  ST_ASTEXT(
    ST_POINTFROMGEOHASH(
      CAST("0123456789bcdefghjkmnpqrstuvwxyz0123456789BCDEFGHJKMNPQRSTUVWXYZ"
           AS CHAR CHARACTER SET utf8) COLLATE utf8_unicode_ci,
      4326
    )
  ) c;

SELECT
  ST_ASTEXT(
    ST_POINTFROMGEOHASH(
      CAST("0123456789bcdefghjkmnpqrstuvwxyz0123456789BCDEFGHJKMNPQRSTUVWXYZ"
           AS CHAR CHARACTER SET utf8) COLLATE utf8_hungarian_ci,
      4326
    )
  ) c;

#####################################################################
# Bug#19383904
#####################################################################
# This table is for testing functions with DOUBLE values. Literals like 10.2 are
# recognized as DECIMAL.
CREATE TABLE t1 (val DOUBLE);
INSERT INTO t1 (val) VALUES (12.2);
SELECT val INTO @double FROM t1;
DROP TABLE t1;

SET @null = NULL;
SET @geohash = "01bbgcee";
PREPARE stmt FROM "SELECT ST_LONGFROMGEOHASH(?)";
EXECUTE stmt USING @geohash;
DEALLOCATE PREPARE stmt;
SELECT ST_LONGFROMGEOHASH(@geohash);
SELECT ST_LONGFROMGEOHASH(@null);

PREPARE stmt FROM "SELECT ST_LATFROMGEOHASH(?)";
EXECUTE stmt USING @geohash;
DEALLOCATE PREPARE stmt;
SELECT ST_LATFROMGEOHASH(@geohash);
SELECT ST_LATFROMGEOHASH(@null);

PREPARE stmt FROM "SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(?, 0))";
EXECUTE stmt USING @geohash;
DEALLOCATE PREPARE stmt;
SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(@geohash, 0));

SET @srid = 4326;
PREPARE stmt FROM "SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(\"00\", ?))";
EXECUTE stmt USING @srid;
DEALLOCATE PREPARE stmt;
SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("00", @srid));

SET @maxlen = 3;
PREPARE stmt FROM "SELECT ST_GeoHash(POINT(10, 10), ?)";
EXECUTE stmt USING @maxlen;
DEALLOCATE PREPARE stmt;
SELECT ST_GeoHash(POINT(10, 10), @maxlen);
SELECT ST_GeoHash(POINT(10, 10), @null);

SET @point = POINT(10, 10);
PREPARE stmt FROM "SELECT ST_GeoHash(?, 10)";
EXECUTE stmt USING @point;
DEALLOCATE PREPARE stmt;
SELECT ST_GeoHash(@point, 10);
SELECT ST_GeoHash(@null, 10);

SET @long = 22.0311;
PREPARE stmt FROM "SELECT ST_GeoHash(?, 10, 10)";
EXECUTE stmt USING @long;
DEALLOCATE PREPARE stmt;
SELECT ST_GeoHash(@long, 10, 10);
SELECT ST_GeoHash(@null, 10, 10);

SET @lat = -54.123;
PREPARE stmt FROM "SELECT ST_GeoHash(10, ?, 10)";
EXECUTE stmt USING @lat;
DEALLOCATE PREPARE stmt;
SELECT ST_GeoHash(10, @lat, 10);
SELECT ST_GeoHash(10, @null, 10);

PREPARE stmt FROM "SELECT ST_GeoHash(10, 10, ?)";
EXECUTE stmt USING @maxlen;
DEALLOCATE PREPARE stmt;
SELECT ST_GeoHash(10, 10, @maxlen);
SELECT ST_GeoHash(@double, @double, 10);

--error ER_STD_OUT_OF_RANGE_ERROR
SELECT ST_GeoHash(POINT(10, 10), @double);
--error ER_GIS_INVALID_DATA
SELECT ST_POINTFROMGEOHASH("00", @double);

#####################################################################
# Bug#20293609
#####################################################################

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a VARCHAR(255));
INSERT INTO t1 (a) VALUES ('  ');
--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_LONGFROMGEOHASH(a) FROM t1;
--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_LATFROMGEOHASH(a) FROM t1;
--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_POINTFROMGEOHASH(a, 0) FROM t1;

DELETE FROM t1;
INSERT INTO t1 VALUES ('swpwwwwww'), ('guqtjvooguqtjvoo');
--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_LONGFROMGEOHASH(a) FROM t1;
--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_LATFROMGEOHASH(a) FROM t1;
--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_POINTFROMGEOHASH(a, 0) FROM t1;

--echo # Clean up
DROP TABLE geohashes;

DROP TABLE t1;

--echo #
--echo # Bug#22165582 ST_*FROMGEOHASH ROUNDS INCORRECTLY
--echo #
SELECT ST_GeoHash(ST_PointFromGeoHash('xkcd', 0), 4);
SELECT ST_LongFromGeoHash('xkcd');
SELECT ST_LatFromGeoHash('xkcd');
SELECT ST_GeoHash(ST_PointFromGeoHash('ebrb', 0), 4);
SELECT ST_LatFromGeoHash('m7s9pyctu9bbwqkgbw5x6vutzkztd9szjh86gmz9w9nsz6792d') c;

--echo #
--echo # Bug#22838668 CRASH IN ITEM_FUNC_LATLONGFROMGEOHASH::VAL_REAL
--echo # WITH BAD PARAMETERS
--echo #
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
DO ST_Disjoint(
  POLYGON(
    LINESTRING(POINT(1, 1))
  ),
  ST_LatFromGeoHash(ExtractValue(1, NULL))
);

--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
DO ST_Disjoint(
  POLYGON(
    LINESTRING(POINT(1, 1))
  ),
  ST_LongFromGeoHash(ExtractValue(1, NULL))
);

--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
DO ST_Disjoint(
  POLYGON(
    LINESTRING(POINT(1, 1))
  ),
  ST_LatFromGeoHash(UpdateXML(1, NULL, NULL))
);

--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
DO ST_Disjoint(
  POLYGON(
    LINESTRING(POINT(1, 1))
  ),
  ST_LongFromGeoHash(UpdateXML(1, NULL, NULL))
);

--echo #
--echo # Bug#25912557 ST_LATFROMGEOHASH/ST_LONGFROMGEOHASH ODD INPUT TYPE
--echo #              REQUIREMENT
--echo #
CREATE TABLE t1 (col1 CHAR(4));
INSERT INTO t1 VALUES ('bbbb');
SELECT ST_LatFromGeohash(col1) FROM t1;
SELECT ST_LongFromGeohash(col1) FROM t1;
SELECT ST_AsText(ST_PointFromGeohash(col1, 4326)) FROM t1;
DROP TABLE t1;

--echo #
--echo # MDEV-36168 ASAN global-buffer-overflow in Item_func_latlongfromgeohash::decode_geohash
--echo #
--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_LatFromGeoHash("ї");
--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_LongFromGeoHash("ї");

--echo #
--echo # MDEV-34158 st_geohash error reporting and null handling
--echo #
--error ER_STD_OUT_OF_RANGE_ERROR
SELECT  ST_GeoHash(( POINTFROMTEXT(' POINT( 4 1 ) ') ), 1e0);
--error ER_STD_OUT_OF_RANGE_ERROR
SELECT  ST_GeoHash(( POINTFROMTEXT(' POINT( 4 1 ) ') ), 0);
SELECT ST_GeoHash(45,-20,null);
SELECT ST_GeoHash(0,-20,null);
SELECT  ST_GeoHash(( POINTFROMTEXT(' POINT( 4 1 ) ') ), NULL);