Bug #4200 Parse error on LIKE ESCPAPE with parameter binding
Submitted: 18 Jun 2004 11:28 Modified: 22 Jun 2004 18:16
Reporter: Anthon Ouwendijk Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.2 OS:HP/UX (HP-UX 11)
Assigned to: Dmitry Lenev

[18 Jun 2004 11:28] Anthon Ouwendijk
Description:
This happens when the C API for prepared statements is used.
A sample C source has been included.

LIKE ... ESCAPE gives parse error (error 1064) on ESCAPE ?

The parser does not accept a parameter bind after the ESCAPE keyword.
The query: select str from T where str like ? escape ?
gives:
You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near '?' at line 1

The query: select str from T where str like ? escape '?'
parses, but then the literal '?' is taken as escape character instead of a parameter.

How to repeat:
#include <stdlib.h>
#include <mysql.h>

typedef enum {
	BUG_UNKNOWN	= -1,	/* Error occurred */
	BUG_SOLVED	= 0,
	BUG_PRESENT	= 1,
} BugStatus;

char * str_bug_stat( BugStatus bugstat )
{
	if ( bugstat == BUG_PRESENT  ) {
		return "Bug still present";
	} else if ( bugstat == BUG_SOLVED ) {
		return "Bug solved";
	}
	return "Status UNKNOWN (or error occurred)";
}

BugStatus do_the_bug_test( MYSQL * mysql, char ** desc )
{
	MYSQL_STMT *	stmt;
	char *		query;

	*desc = 
	"MySQL-#.... Baan-040618	LIKE ... ESCAPE gives parse error on ESCAPE ? \n"
	"			ok:	select str from T where str like ? escape '?' \n"
	"				but that is not a parameter but the literal question mark \n"
	"			err:	select str from T where str like ? escape ? \n";

	if ( !(stmt = mysql_stmt_init(mysql) ) ) {
		return BUG_UNKNOWN;
	}

	query = "select name from mysql.help_topic where name like ? escape '?'";

	if (mysql_stmt_prepare(stmt, query, strlen(query))) {
		printf( "Prepare ERR: %s\n", query );
		mysql_stmt_close(stmt);
		return BUG_UNKNOWN;
	}
	printf( "Prepare OK : %s\n", query );

	query = "select name from mysql.help_topic where name like ? escape ?";
	if (mysql_stmt_prepare(stmt, query, strlen(query))) {
		printf( "Prepare ERR: %s\n", query );
		mysql_stmt_close(stmt);
		return BUG_PRESENT;
	}
	printf( "Prepare OK : %s\n", query );

	mysql_stmt_close(stmt);
	return BUG_SOLVED;
}

main()
{
	MYSQL *		mysql = mysql_init( NULL );
	BugStatus	bugstat;
	char *		desc;

	/* Bind data */

	if ( ! mysql_real_connect( mysql,
					/* host */ NULL,
					/* user */ User,
					/* pwd */ Passwd,
					/* dbase */ Dbase, 

					/* uint port */ 0,
					/* char * socket */ NULL,
					/* client_flag */ 0 ) )
	{
		fprintf(stderr, " mysql_real_connec() failed\n");
		exit(0);
	}

	bugstat = do_the_bug_test( mysql, &desc );
	fprintf(stdout, "%s\n%s\n\n", desc, str_bug_stat( bugstat ) );

	return 0;
}
[22 Jun 2004 18:16] Dmitry Lenev
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

ChangeSet 1.1925.10.1 2004/06/22 19:27:16 dlenev@brandersnatch.localdomain
  Fix for Bug# 4200 "Parse error on LIKE ESCAPE with parameter binding"
  Now ESCAPE in LIKE will accept not only string literal but constant 
  delimited expression.

Fix will also appear in 4.1.3 which should be out really soon