MySQL Bugs: #22869: Error not raised if binding doesn't work

Bug #22869	Error not raised if binding doesn't work
Submitted:	1 Oct 2006 10:14	Modified:	19 Oct 2006 8:12
Reporter:	Tonci Grgin	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Prepared statements	Severity:	S3 (Non-critical)
Version:	5.0. BK, 5.1. BK	OS:	Linux (Suse linux 10.0)
Assigned to:		CPU Architecture:	Any

Description:
Prepared statement fails to update DECIMAL field even though result from PS execution suggests otherwise (total affected rows(UPDATE 1): 1). Checking metadata reveals the error happened (mysql_stmt_result_metadata(), returned no meta information)
This is the continuation of BUG#22290

How to repeat:
All necessary info, SQL statements, expected/gotten results inside attached test case.

DROP TABLE IF EXISTS testbug22290;
CREATE TABLE  testbug22290 (
  `id` int(11) NOT NULL default '1',
  `cost` decimal(10,2) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO testbug22290 (`id`,`cost`) VALUES (1,'1.00');

RESULTS from cl client (plain update cost = cost + 1.1):
mysql> select * from testbug22290;
+----+------+
| id | cost |
+----+------+
|  1 | 2.11 |
+----+------+
1 row in set (0.00 sec)

FROM the test case
Connected!

Running {update testbug22290 set cost = cost + ? where id = 1}

 total parameters in UPDATE: 1

Exec prep stmt
 total affected rows(UPDATE 1): 1

mysql_stmt_result_metadata(), returned no meta information
Segmentation fault

Test case

Attachment: bug22290.c (text/x-csrc), 6.76 KiB.

Before running:
mysql> select * from testbug22290;
+----+------+
| id | cost |
+----+------+
|  1 | 1.00 |
+----+------+
1 row in set (0.02 sec)

Running:
sbester@linux:~/code> ./bug22290
Connected to '5.0.26-debug-log' !
Running 'update testbug22290 set cost = cost + ? where id = 1'
total parameters in UPDATE: 1

Exec prep stmt
mysql_stmt_affected_rows returned 1
test complete!
sbester@linux:~/code>

After running:
mysql> select * from testbug22290;
+----+------+
| id | cost |
+----+------+
|  1 | 2.11 |
+----+------+
1 row in set (0.00 sec)

General query log:
061001 15:23:28         48 Connect     root@ as anonymous on test
            48 Prepare     [1] update testbug22290 set cost = cost + ? where id = 1
            48 Execute     [1] update testbug22290 set cost = cost + 1.11000 where id = 1
            48 Quit

as shown by private testcase, the update works fine when the bind parameter values for decimal are stored in char* variable, instead of double in C. Also, buffer_length and length are specified.

Synopsis changed.
We shouldn't have silent failures if the binding doesn't work. If the only working binding for DECIMAL parameters is to use a char*, the server should complain if a user uses something different.

Tonci, 
the problem you're facing is a documentation issue that is described in Bug#20169.
When binding input parameters, you the typecode is there to describe the type of C buffers, not target buffers. We simply should not _allow_ using MYSQL_TYPE_DECIMAL/MYSQL_TYPE_NEWDECIMAL on input, perhaps, to not confuse matters.
Here's my email to Paul with an additional explanation on this matter:

Subject: Re: http://bugs.mysql.com/bug.php?id=20169

* Paul DuBois <paul@mysql.com> [06/09/12 10:49]:

> I'm going to try to close this bug report now, but I need a little feed
> back from you.
>
> You indicate that there's a difference between using the type codes for
> input vs for output, and that for BIT there is no corresponding C type.
> I took a look at the code for mysql_stmt_bind_param() and the table of
> MYSQL_TYPE_xxx values in the manual, and it appears that MYSQL_TYPE_BIT
> is the only value listed in the table not handled by
> mysql_stmt_bind_param(), so I guess I will just describe it in the
> manual as the "odd case" and point out that for input you should use one
> of the integer type codes instead and associate the value with an
> integer variable of the appropriate size.  That's the appropriate
> strategy, correct?

MYSQL_TYPE_BIT is not the only typecode that is not supported by
mysql_stmt_bind_param. It also lacks support MYSQL_TYPE_ENUM,
MYSQL_TYPE_SET, MYSQL_TYPE_INT24.

Strictly speaking, MYSQL_TYPE_NEWDECIMAL/MYSQL_TYPE_DECIMAL
shouldn't be in the switch either.

I believe we've been discussing this not once: when you bind input
parameters, in the typecode field you specify a typecode for the
type of parameter in your application language.

There is no C language type BIT or ENUM or NEWDECIMAL.

Therefore, the table in the documentation for
mysql_stmt_bind_param, should only list the typecodes for the C
language types:

 - const char * that the server should interpret as a string in
   character_set_client: MYSQL_TYPE_STRING
 - const char * treated as a binary string: MYSQL_TYPE_BLOB
 - char: MYSQL_TYPE_TINY
 - unsigned char: MYSQL_TYPE_TINY + is_unsigned flag is set.
 - short - MYSQL_TYPE_SHORT
and so on.

You don not specify the typecode of the target column when binding
input data.

> In the course of thinking this over, I came up with some other questions:
>
> - One thing I think I should make a bit clearer is how to transfer
> UNSIGNED integer SQL types.

You can't transfer an SQL type from the client as the client has
no SQL types. It has only types of the language the client program
is written in.

> The way to do this is the same as for signed integers, except
> that you set the is_unsigned member, and you use the
> corresponding unsigned C integer type ... right?  For example,
> to transfer a SMALLINT, you use MYSQL_TYPE_SHORT and a short
> int, whereas to transfer a SMALLINT UNSIGNED, you use
> MYSQL_TYPE_SHORT, an unsigned short int, and you set
> is_unsigned.  Is that correct?

You don't have to use unsigned short to insert a value into an
unsigned short column. You can use anything, including const char *
and double. If there is a truncation at insert, you'll get a
warning (well, hopefully).

But if you have unsigned short in your application, then yes, you
should specify MYSQL_TYPE_SHORT and use 'unsigned flag'.

I will split the enum one day, the concept of different sets of
types seems to be impossible to explain (or at least doesn't stick
in) unless different type codes are used to denote different type
sets.

> - MySQL allows FLOAT UNSIGNED and DOUBLE UNSIGNED.  But there are not
> unsigned float and unsigned double C types.  How can you pass or receive
> FLOAT|DOUBLE UNSIGNED values through the binary protocol?  Or can't it
> be done?

There is a no-conversion mapping for some SQL types to C types.
E.g. SHORT INTEGER maps well to unsigned short.

But this is not the case for all types. E.g. DECIMAL doesn't map to
anything conversion-less in C. The same is true for DOUBLE UNSIGNED.

--
Konstantin Osipov, Software Developer
MySQL AB, www.mysql.com

The comment re rasining an error in case binding doesn't work: in your original case the binding did work. The client library just interpreted the FLOAT buffer as a string, and thus nothing happened. But there is no way for the client library to find out that the type of the input buffer is different from the one specified by the typecode (param->buffer_type).