Bug #24179 "select b into $var" fails with --cursor_protocol
Submitted: 10 Nov 2006 11:05 Modified: 25 Jan 2007 11:34
Reporter: Magnus Blåudd Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Prepared statements Severity:S3 (Non-critical)
Version:5.0.30 OS:
Assigned to: Konstantin Osipov CPU Architecture:Any
Tags: cursor, rt_q1_2007

[10 Nov 2006 11:05] Magnus Blåudd
Description:
When running queries like below with --cursor-protocol
select 3 into @v1;
they will fail with error 
1322: Cursor statement must be a SELECT

I'm not sure if this kind of select should be possible to perform with cursor protocol. 

If not, we can detect it with the regular expressions that are used to find out if a query supports to be run with cursor_protocol. 

How to repeat:
./mtr --cursor-protocol explain

Fails in several other tests as well.
[10 Nov 2006 11:34] Konstantin Osipov
Yes, I believe it should be fixed in mysqltest. SELECT .. INTO doesn't return a result set and can't have a cursor open for it.
[10 Nov 2006 11:34] Konstantin Osipov
The error message, however, is rather misleading.
[10 Nov 2006 11:44] Magnus Blåudd
That means the error message should be changed to say something like "Cursor can only be used for queries returning a result set"
[13 Nov 2006 14:43] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/15238

ChangeSet@1.2556, 2006-11-13 15:43:22+01:00, msvensson@neptunus.(none) +1 -0
  Bug#24179 "select b into $var" fails with --cursor_protocol
   - Add regex that filter quieries like "SELECT INTO @|OUTFILE|DUMPFILE" to 
     not be run as server side cursor - it requires that the query returns a
     result set
[13 Nov 2006 14:47] Magnus Blåudd
I found the below to error messages  in 5.0 and it looks like the second one fits much better for this problem. Will check why that is not returned in this case.

ER_SP_BAD_CURSOR_QUERY 42000 
	eng "Cursor statement must be a SELECT"
	ger "Cursor-Anweisung muss ein SELECT sein"
ER_SP_BAD_CURSOR_SELECT 42000 
	eng "Cursor SELECT must not have INTO"
	ger "Cursor-SELECT darf kein INTO haben"
[13 Nov 2006 14:50] Magnus Blåudd
The code in sql_yacc.yy look slike this, maybe lex->result isn't inited to 0?

if (lex->result)
{
  my_message(ER_SP_BAD_CURSOR_SELECT, ER(ER_SP_BAD_CURSOR_SELECT),
                         MYF(0));
  YYABORT;
}
[15 Nov 2006 19:58] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/15385

ChangeSet@1.2303, 2006-11-15 22:57:38+03:00, kostja@bodhi.local +4 -0
  A fix and a test case for Bug#24179 "select b into $var" fails with 
  --cursor_protocol": fix a misleading error message in case of
  SELECT .. INTO.
[27 Nov 2006 19:19] [ name withheld ]
I recently went through a major struggle with a 'select y into x' statement executed with a prepared statement -- it always returned 0.  This 'bug' was resolved by using the configuration 'set x = select y'. It was a damn complex sql statement but it worked perfectly after that. 
Cheers,
John
[25 Jan 2007 11:34] Konstantin Osipov
This is a bug in the test suite, needs no changelog entry.
Thanks Paul.