Default page

Edit File: geohash_functions.test

#####################################################################
#                                                                   #
# This test is for validating the functionality of the different    #
# geohash functions.                                                #
#                                                                   #
# Creation Date: 2014-06-13                                         #
# Author : Erik Frøseth                                             #
#                                                                   #
#####################################################################

#####################################################################
# 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(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("0ë 0Ñ");

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

--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_LONGFROMGEOHASH("");

--error ER_INCORRECT_TYPE
SELECT ST_LONGFROMGEOHASH(9876543210);

--error ER_INCORRECT_TYPE
SELECT ST_LONGFROMGEOHASH(0123456789);

--error ER_INCORRECT_TYPE
SELECT ST_LONGFROMGEOHASH(1e1);

--error ER_INCORRECT_TYPE
SELECT ST_LONGFROMGEOHASH(CAST("012" AS BINARY));

--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");

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

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

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

# 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);

# 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("0ë 0Ñ");

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

--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_LATFROMGEOHASH("");

--error ER_INCORRECT_TYPE
SELECT ST_LATFROMGEOHASH(9876543210);

--error ER_INCORRECT_TYPE
SELECT ST_LATFROMGEOHASH(0123456789);

--error ER_INCORRECT_TYPE
SELECT ST_LATFROMGEOHASH(1e1);

--error ER_INCORRECT_TYPE
SELECT ST_LATFROMGEOHASH(CAST("012" AS BINARY));

--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");

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

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

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

--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", 1));

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("0123456789bcdefghjkmnpqrstuvwxyz", 04294967295));

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("0123456789BCDEFGHJKMNPQRSTUVWXYZ", 04294967295));

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("zyxwvutsrqpnmkjhgfedcb9876543210", 04294967295));

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("ZYXWVUTSRQPNMKJHGFEDCB9876543210", 04294967295));

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

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("100", "1 2 3"));

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

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

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

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

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

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

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

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(NULL, 200));

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(null, 200));

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

--error ER_INCORRECT_TYPE
SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(9876543210, 1));

--error ER_INCORRECT_TYPE
SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(0123456789, 1));

--error ER_INCORRECT_TYPE
SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(1e1, 1));

--error ER_INCORRECT_TYPE
SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(CAST("012" AS BINARY), 1));

--error ER_INCORRECT_TYPE
SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("zzzzzzzzzzzzzzzzzzzz", (CAST(100 AS BINARY))));

--error ER_INCORRECT_TYPE
SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("rzzzzzzzzzzzzzzzzzzz", 12345.67890));

--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("zzzzzzzzzzzzzzzzzzzz", 4294967296));

--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("bpbpbpbpbpbpbpbpbpbp", 10000000000));

--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("pbpbpbpbpbpbpbpbpbpb", 4294967295000));

--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("gzzzzzzzzzzzzzzzzzzz", -1));

--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("5bpbpbpbpbpbpbpbpbpb", -1024));

--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("7zzzzzzzzzzzzzzzzzzz", -04294967295));

--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("00000000000000000000", "4294967296"));

--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("00000000000000000000", "10000000000"));

--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("00000000000000000000", "-1"));

--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("00000000000000000000", "-1024"));

--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));

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

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

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

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

--echo # different random geohash values
SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(hash_value,1000)) 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);

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

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

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

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

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

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

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

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);

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

--error ER_DATA_OUT_OF_RANGE
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_INCORRECT_TYPE
SELECT ST_GEOHASH(1,2);

--error ER_INCORRECT_TYPE
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_INCORRECT_TYPE
SELECT ST_GEOHASH((CAST(180 AS DATE)),90,10);

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

--error ER_DATA_OUT_OF_RANGE
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_INCORRECT_TYPE
SELECT ST_GEOHASH(ST_GEOMFROMTEXT('LINESTRING(0 0,10 10)'),10);

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

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

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

--error ER_INCORRECT_TYPE
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_INCORRECT_TYPE
SELECT ST_GEOHASH(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(0 0))'),10);

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

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

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

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

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

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

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

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

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

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

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

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

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

--error ER_GIS_INVALID_DATA
SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(180,90)'),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));

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

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

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

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

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

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

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

--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));

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

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

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

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

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

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

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

--echo #=============================================================
--echo # ST_POINTFROMGEOHASH()
--echo #=============================================================
SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(ST_GEOHASH(180,90,20),10));

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

SELECT
  ST_LONGFROMGEOHASH(
    CAST("0123456789bcdefghjkmnpqrstuvwxyz0123456789BCDEFGHJKMNPQRSTUVWXYZ"
         AS CHAR CHARACTER SET gb18030) COLLATE gb18030_unicode_520_ci);

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

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

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

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

SELECT
  ST_LATFROMGEOHASH(
    CAST("0123456789bcdefghjkmnpqrstuvwxyz0123456789BCDEFGHJKMNPQRSTUVWXYZ"
         AS CHAR CHARACTER SET gb18030) COLLATE gb18030_unicode_520_ci);

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

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

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

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

SELECT
  ST_ASTEXT(
    ST_POINTFROMGEOHASH(
      CAST("0123456789bcdefghjkmnpqrstuvwxyz0123456789BCDEFGHJKMNPQRSTUVWXYZ"
           AS CHAR CHARACTER SET gb18030) COLLATE gb18030_unicode_520_ci,
      4326
    )
  );

#####################################################################
# 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(?, 1))";
EXECUTE stmt USING @geohash;
DEALLOCATE PREPARE stmt;
SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(@geohash, 1));

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_INCORRECT_TYPE
SELECT ST_GeoHash(POINT(10, 10), @double);
--error ER_INCORRECT_TYPE
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, 1) 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, 1) 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');

--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;