MySQL Bugs: #51562: incorrect status/error returned for certain queries with an error

Bug #51562	incorrect status/error returned for certain queries with an error
Submitted:	26 Feb 2010 18:05	Modified:	2 Jul 2010 9:09
Reporter:	Lenin Adrian	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / C++	Severity:	S3 (Non-critical)
Version:	All	OS:	Any (Windows 7, Mac OS X)
Assigned to:	Andrey Hristov	CPU Architecture:	Any
Tags:	error, message, subquery

Description:
when I was traying to do this kind of query I spend a lot of time looking for my mistake 'cause workbench didnt tell me what my error was, it just say "OK" in response tab.

so a tray with MySql Administrator and then I saw the error.

this make me feel not sure about querys in workbench, hope fix that.

How to repeat:
select field1,(select field2 from table2) from table1

Thank you for the bug report. I don't understand what are you reporting could you please print here or attach a picture which the error is. Thanks in advance.

well, thats the problem, with that kind of query, workbench should tell me what's the error is, but id doesn't.

that query was a test, and I found that there's no error for that kind.

In the response tab just say "Ok" and show me no results.

Still I don't get what are you saying, according with the context that query is valid, i.e: there are table1 with field1 and table2 with field2:

Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.1.42-community-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test
Database changed
mysql> desc table1
    -> ;
+--------+---------+------+-----+---------+-------+
| Field  | Type    | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| field1 | int(11) | YES  |     | NULL    |       |
+--------+---------+------+-----+---------+-------+
1 row in set (0.01 sec)

mysql> desc table2;
+--------+---------+------+-----+---------+-------+
| Field  | Type    | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| field2 | int(11) | YES  |     | NULL    |       |
+--------+---------+------+-----+---------+-------+
1 row in set (0.01 sec)

mysql> select field1,(select field2 from table2) from table1;
+--------+-----------------------------+
| field1 | (select field2 from table2) |
+--------+-----------------------------+
|      1 |                           2 |
+--------+-----------------------------+
1 row in set (0.00 sec)

mysql>

Workbench

Attachment: workbench.png (image/png, text), 40.00 KiB.

Mysql Administrator (browser)

Attachment: Administrator.png (image/png, text), 176.32 KiB.

Sorry my english is poor, but in my case can't do that kind of query this are my print screen from both workbench and mysql administrator.

the error that should be is "Subquery returns more than 1 rows" error 1242
like apears in the Administrator.

Verified just as described on Mac OS X. 

This query, for example:

mysql> select 1, (select 'def' union all select 'abc') as a from mysql.user;
ERROR 1242 (21000): Subquery returns more than 1 row

will always fail with error, but Workbench 5.2.16 says it worked 'OK' in the Output tab.

Rewording Synopsis and reassigning to Connectors team as this is a Connector/C++ issue.

Andrey has pushed a fix to the C/C++ repository. I've reviewed and tested it.

Although the issue had been caused by a documented C API / server behavior, I'd like to note that it would be much nicer if the server would report the error when running the query but not when storing results.

Ulf

Andrey has pushed a fix to the C/C++ repository. I've reviewed and tested it.

Although the issue had been caused by a documented C API / server behavior, I'd like to note that it would be much nicer if the server would report the error when running the query but not when storing results.

Ulf