Bug #11589 prepared statements - strange output, float/double/real with zerofill
Submitted: 27 Jun 2005 12:29 Modified: 25 Oct 2007 0:02
Reporter: Matthias Leich Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Prepared statements Severity:S3 (Non-critical)
Version:4.1 OS:Any
Assigned to: Magnus Blåudd CPU Architecture:Any

[27 Jun 2005 12:29] Matthias Leich
Description:
The option "--ps-protocol" causes strange output when selecting
on columns of the data type real/double/float zerofill.
Example:
CREATE TABLE t1 (f1 real zerofill,f2 double zerofill,f3 float zerofill);
INSERT INTO t1 VALUES ( 0.314152e+1, 0.314152e+1, 0.314152e+1);
PREPARE stmt1 FROM 'select f1, f2, f3 FROM t1';
<without --ps-protocol>
select f1, f2, f3 FROM t1;
f1	f2	f3
0000000000000003.14152	0000000000000003.14152	000003.14152
select f1, f2, f3 FROM t1;    <-- executed via eval $stmt
f1	f2	f3
0000000000000003.14152	0000000000000003.14152	000003.14152
EXECUTE stmt1;
f1	f2	f3
0000000000000003.14152	0000000000000003.14152	000003.14152

<with --ps-protocol>
select f1, f2, f3 FROM t1;
f1	f2	f3
3.14152	3.14152	3.14152
#################
select f1, f2, f3 FROM t1;   <-- executed via eval $stmt
f1	f2	f3
0000000000000003.14152	0000000000000003.14152	000003.14152
EXECUTE stmt1;
f1	f2	f3
0000000000000003.14152	0000000000000003.14152	000003.14152

The option "--ps-protocol" should cause the usage of prepared 
statements as much as possible.
Therefore I expect to get an output similar to the explicit use 
(Prepare,Execute) of prepared statements.
Unfortunately that does not happen.
I guess 
- the printed values are logically correct, but "--ps-protocol"
  uses the "wrong" physical formatting
- this bug is not important for customers .
But it harms the development of testscripts, because it is expected,
that testprotocols are nearly independend of the usage of the
"--ps-protocol" option.

My environment:
   - Intel PC with Linux(SuSE 9.3)
   - MySQL compiled from source
        Version 4.1 ChangeSet@1.2306, 2005-06-21
        Version 5.0 ChangeSet@1.1989.2.1, 2005-06-23

How to repeat:
Please use the attached testcase ml021.test.

  copy it to mysql-test/t
  touch r/ml021.result     # Produce a dummy file with 
                                   # expected results
  ./mysql-test-run ml021
   mv r/ml021.reject r/ml021.result
  ./mysql-test-run --ps-protocol ml021
  compare r/ml021.reject and r/ml021.result
[27 Jun 2005 12:30] Matthias Leich
Testcase

Attachment: ml021.test (application/test, text), 364 bytes.

[8 Jul 2005 8:36] Matthias Leich
The problem with the differing output does also appear with non
zerofill float/double/real values.
Without --ps-protocol :
CREATE TABLE t1 (f1 DOUBLE);
INSERT INTO t1 SET f1 = 
-1.79769313486231e+308;;
SELECT f1 AS f1_direct FROM t1;
f1_direct
-1.79769313486231e+308
SELECT f1 AS f1_eval FROM t1;
f1_eval          (Executed via eval)
-1.79769313486231e+308
PREPARE my_sel FROM 'SELECT f1 AS f1_prepared FROM t1';
EXECUTE my_sel;
f1_prepared
-1.79769313486231e+308
       All SELECT variants give the same result 
       -1.79769313486231e+308 (= mantissa 15 digits).
With --ps-protocol :
... 
SELECT f1 AS f1_direct FROM t1;
f1_direct
-1.7976931348623e+308  ( = mantissa 14 digits)
                         ###
      All other SELECT variants give the same result 
      -1.79769313486231e+308.

When I shorten the mantissa to 14 digits
(INSERT of -1.7976931348623e+308)
the problem disappears.
 
The file ml033.test contains this testcase.
[8 Jul 2005 8:39] Matthias Leich
testcase

Attachment: ml033.test (application/test, text), 486 bytes.

[21 Feb 2006 13:56] 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/2972
[21 Feb 2006 17:51] Magnus Blåudd
The patch will correct "zerofill" problem. I.e first part of this bug report.
[21 Jul 2006 10:05] Magnus Blåudd
Splitting this bug in two, new bug#21205 created for the second problem.

The patch already provided is ready to go into 5.0 when approved.
[18 Oct 2007 21:36] Bugs System
Pushed into 5.1.23-beta
[18 Oct 2007 21:37] Bugs System
Pushed into 5.0.52
[25 Oct 2007 0:02] Paul Dubois
Noted in 5.0.52, 5.1.23 changelogs.

Values of types REAL ZEROFILL, DOUBLE ZEROFILL, FLOAT ZEROFILL, were not zero-filled when
converted to a character representation in the C prepared statement API.