MySQL Bugs: #77712: mysql_real_query does not report an error for some queries!

Bug #77712	mysql_real_query does not report an error for some queries!
Submitted:	14 Jul 2015 7:16	Modified:	14 Jul 2015 7:42
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: GIS	Severity:	S3 (Non-critical)
Version:	8.0.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
some failing queries report a successful result code, then client applications written according to the spec will fail to notice these errors.

mysql client itself only reports an error because it checks the first character of mysql_error() string is not null.  This is not the proper way to detect errors.

How to repeat:
read https://dev.mysql.com/doc/refman/5.7/en/mysql-real-query.html

run each of these two queries with mysql_real_query and see the difference in return codes and errors.

select polygon(linestring(point(1,1))) ;
select a ;

Setting as verified.   See the sample program output.  Notice, if mysql_real_query returns an error,  we should not call mysql_store_result...

So why then does the polygon query only return the error after mysql_store_result ???

[x@x ~]$ ./bug77712.cpp
Connected: 127.0.0.1 via TCP/IP [5.8.0-m17-debug]

query: select polygon(linestring(point(1,1))) ;;
mysql_real_query returned: 0 - mysql_errno: 0,  mysql_error:
mysql_store_result returned: (nil) - mysql_errno: 3037,  mysql_error: Invalid GIS data provided to function linestring.

query: select a ;;
mysql_real_query returned: 1 - mysql_errno: 1054,  mysql_error: Unknown column 'a' in 'field list'

testcase

Attachment: bug77712.cpp (text/plain), 1.73 KiB.

just to clarify.  my wish hereis that the query
"select polygon(linestring(point(1,1)))" 

causes a nonzero return from mysql_real_query()

Posted by developer:
 
Output from attached test program when using Erik's POW() query:

query: select POW(9999, 999999);;
mysql_real_query returned: 0 - mysql_errno: 0,  mysql_error: 
mysql_store_result returned: (nil) - mysql_errno: 1690,  mysql_error: DOUBLE value is out of range in 'pow(9999,999999)'

This is not a GIS bug. I'm moving it to the optimizer category.

It's not just GIS functions that have this problem, uuid_to_bin() for example has the same issue. It appears that any function that can throw an error during execution of the function can have this issue. mysql --quick can be used to reproduce this:

mysql> select uuid_to_bin('');
+------------------------------------+
| uuid_to_bin('')                    |
+------------------------------------+
+------------------------------------+
ERROR 1411 (HY000): Incorrect string value: '' for function uuid_to_bin
0 rows in set (0.00 sec)

mysql> select a;
ERROR 1054 (42S22): Unknown column 'a' in 'field list'
mysql> select uuid_to_bin();
ERROR 1582 (42000): Incorrect parameter count in the call to native function 'uuid_to_bin'
mysql> select polygon(linestring(point(1,1)));
++
| polygon(linestring(point(1,1))) |
++
++
ERROR 3037 (22023): Invalid GIS data provided to function linestring.
0 rows in set (0.00 sec)

Notice how both results and error are sent in some of these.

Posted by developer:
 
This does not look like an optimizer bug to me. With Alfredo's queries:

mysql> select a;
ERROR 1054 (42S22): Unknown column 'a' in 'field list'
mysql> select uuid_to_bin();
ERROR 1582 (42000): Incorrect parameter count in the call to native function
'uuid_to_bin'

For these two queries, the error is caught during resolving.
This means, we don't get an optimized query and an error is sent early to the client.

mysql> select uuid_to_bin(');
+------------------------------------+
| uuid_to_bin(')                    |
+------------------------------------+
+------------------------------------+
ERROR 1411 (HY000): Incorrect string value: ' for function uuid_to_bin
0 rows in set (0.00 sec)
 
mysql> select polygon(linestring(point(1,1)));
++
| polygon(linestring(point(1,1))) |
++
++
ERROR 3037 (22023): Invalid GIS data provided to function linestring.
0 rows in set (0.00 sec)
 
For these two queries, the server has made an optimized version and starts execution.
First part of that is sending metadata of query (column headers).
However, execution is terminated with an error and we finally provide an error message.

Apparently, there is either something in the server protocol implementation or
client protocol implementation that is unable to process this sequence of events.