MySQL Bugs: #113694: misleading error message comparing a spatial column with numeric using

Bug #113694	misleading error message comparing a spatial column with numeric using < or such
Submitted:	20 Jan 2024 1:16	Modified:	22 Jan 2024 12:30
Reporter:	Mikhail Izioumtchenko	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	8.0.34	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
executing SELECT ... WHERE ... col1 < col2 ...

where col2 is a spatial column whereas col2 is numeric results in this error message:

ERROR: 1210 (HY000): Incorrect arguments to >=

2 problems:

1. there is no >= but rather <
2. 'Incorrect arguments' is vague, it would be better to mention geospatial

overall it results in a high unpleasant surprise factor.

How to repeat:
table:

show create table test_schema_longer_name_000000001.tt1\G
*************************** 1. row ***************************
       Table: tt1
Create Table: CREATE TABLE `tt1` (
  `pk1` tinyint NOT NULL,
  `pk2` smallint NOT NULL,
  `col1` varbinary(58) NOT NULL,
  `col2` varchar(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `col3` json DEFAULT NULL,
  `__col4` json NOT NULL,
  `test_5_column_abc123defaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa` int NOT NULL,
  `col6` tinytext,
  `col7` varchar(100) NOT NULL,
  `__col8` multipolygon NOT NULL /*!80003 SRID 4326 */,
  `test_9_column_abc123defaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa` timestamp NULL DEFAULT NULL,
  `__col10` binary(8) NOT NULL,
  PRIMARY KEY (`pk1`,`pk2`)
) /*!80023 AUTOEXTEND_SIZE=4194304 */ ENGINE=InnoDB DEFAULT CHARSET=latin1 STATS_PERSISTENT=1 STATS_SAMPLE_PAGES=1 ROW_FORMAT=DYNAMIC

SELECT:

SELECT * FROM test_schema_longer_name_000000001.tt1 WHERE test_5_column_abc123defaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa < 'aaaaaaaaaaaaaaaaaaaaaaaaa    aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa    aaaaaaaaaa' AND col2 <=> __col4 <=> col1 OR 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa    aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' AND NOT  'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa    aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa    aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' < __col8 OR test_9_column_abc123defaaaaaaaaaaaaaa    aaaaaaaaaaaaaaaaaaa IS NULL;

the culprit is 'a' < __col8 part because __col8 is a MULTIPOLIGON

Hi Mr. Izioumthchenko,

Thank you for your bug report.

We have repeated your bug report and your report is definitely not a bug.

SQL standard strictly prohibits using different data types in any expressions, including the comparison expressions in the WHERE clause.

In short, you are prohibited to compare integer to strings, polygon to strings , json type with varbinary or any CHAR-derived type.

According to the SQL standard, the error should be returned. But, MySQL database tries to do more , when it can do it and in the manner when it can do it.

Hence, it is our choice which common denominator would we use or which  error messages we are going to return. What you saw in the output is our choice for MySQL and we do not see a necessity to change it.

Not a bug.

indeed there is nothing in the standard that says that error messages should make any sense