| Bug #85981 | ST_LatFromGeohash/ST_LongFromGeohash odd input type requirement | ||
|---|---|---|---|
| Submitted: | 18 Apr 2017 11:18 | Modified: | 19 Apr 2017 18:52 |
| Reporter: | Dima Q | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Data Types | Severity: | S3 (Non-critical) |
| Version: | 5.7.18 | OS: | Debian (mysql docker image) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | cast, geohash | ||
[18 Apr 2017 11:35]
MySQL Verification Team
Please provide show create table instead of describe. Thanks.
[18 Apr 2017 12:11]
Dima Q
| weather | CREATE TABLE `weather` ( `date` char(10) NOT NULL, `geohash` char(4) NOT NULL, `weather` json DEFAULT NULL, `todo` tinyint(4) GENERATED ALWAYS AS (isnull(`weather`)) VIRTUAL, PRIMARY KEY (`date`,`geohash`), KEY `todo` (`todo`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | Perhaps it's the charset... not that it should matter to geohash values which are ascii...
[18 Apr 2017 22:14]
MySQL Verification Team
Thank you for the bug report.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.19 Source distribution pull: 2017-apr-05
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql 5.7 > CREATE DATABASE g;
Query OK, 1 row affected (0,00 sec)
mysql 5.7 > USE g
Database changed
mysql 5.7 > CREATE TABLE `weather` (
-> `date` char(10) NOT NULL,
-> `geohash` char(4) NOT NULL,
-> `weather` json DEFAULT NULL,
-> `todo` tinyint(4) GENERATED ALWAYS AS (isnull(`weather`)) VIRTUAL,
-> PRIMARY KEY (`date`,`geohash`),
-> KEY `todo` (`todo`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0,39 sec)
mysql 5.7 > INSERT INTO weather (date,geohash) VALUES ("2010-10-10","w21z");
Query OK, 1 row affected (0,04 sec)
mysql 5.7 > SELECT * FROM weather;
+------------+---------+---------+------+
| date | geohash | weather | todo |
+------------+---------+---------+------+
| 2010-10-10 | w21z | NULL | 1 |
+------------+---------+---------+------+
1 row in set (0,00 sec)
mysql 5.7 > SELECT ST_LatFromGeohash(geohash) FROM weather;
ERROR 3064 (HY000): Incorrect type for argument geohash in function ST_LATFROMGEOHASH.
mysql 5.7 > SELECT ST_LatFromGeohash(CAST(geohash AS CHAR(4))) FROM weather;
+---------------------------------------------+
| ST_LatFromGeohash(CAST(geohash AS CHAR(4))) |
+---------------------------------------------+
| 1.3 |
+---------------------------------------------+
1 row in set (0,00 sec)
mysql 5.7 >
[19 Apr 2017 18:26]
Paul DuBois
Posted by developer: Noted in 8.0.2 changelog. The ST_LatFromGeohash(), ST_LongFromGeohash(), and ST_PointFromGeohash() functions did not allow data that originated from a CHAR column.
[19 Apr 2017 18:52]
Dima Q
Good to know. It would help if 8.0.2 changelog was public...
Description: ST_LatFromGeohash(NULL) OK ST_LatFromGeohash('abcd') OK # literal constant ST_LatFromGeohash(CAST(column AS CHAR(4))) OK ST_LatFromGeohash(column) errors out, even though column type is CHAR(4) as above and data is correct. Pretty SQL: https://gist.github.com/dimaqq/2f1c725fd7d544f083dd03a47b64abc2 How to repeat: mysql> describe weather; +---------+------------+------+-----+---------+-------------------+ | Field | Type | Null | Key | Default | Extra | +---------+------------+------+-----+---------+-------------------+ | date | char(10) | NO | PRI | NULL | | | geohash | char(4) | NO | PRI | NULL | | | weather | json | YES | | NULL | | | todo | tinyint(4) | YES | MUL | NULL | VIRTUAL GENERATED | +---------+------------+------+-----+---------+-------------------+ 4 rows in set (0.01 sec) mysql> select * from weather; +------------+---------+---------+------+ | date | geohash | weather | todo | +------------+---------+---------+------+ | 2010-10-10 | w21z | NULL | 1 | +------------+---------+---------+------+ 1 row in set (0.00 sec) mysql> select ST_LatFromGeohash(geohash) from weather; ERROR 3064 (HY000): Incorrect type for argument geohash in function ST_LATFROMGEOHASH. mysql> select ST_LatFromGeohash(CAST(geohash as CHAR(4))) from weather; +---------------------------------------------+ | ST_LatFromGeohash(CAST(geohash as CHAR(4))) | +---------------------------------------------+ | 1.3 | +---------------------------------------------+ 1 row in set (0.00 sec)