Description:
This happens when the C API for prepared statements is used.
A sample C source has been included.
A query like:
select double from T
union all
select long from T
gives the expected result:
52570.00
41250.00
38250.00
10.00
20.00
30.00
Switching the parts of the union:
select long from T
union all
select double from T
gives the WRONG result:
0.00
0.00
0.00
0.00
0.00
0.00
How to repeat:
-- phpMyAdmin SQL Dump
-- version 2.6.0-alpha2
-- http://www.phpmyadmin.net
--
-- Host: eddie.ssainternal.net
-- Generation Time: Jun 22, 2004 at 11:20 AM
-- Server version: 4.1.2
-- PHP Version: 4.3.3
--
-- Database : `baan`
--
-- --------------------------------------------------------
--
-- Table structure for table `dbtst120`
--
CREATE TABLE `dbtst120` (
`t_empno` int(11) NOT NULL default '0',
`t_firstnme` varchar(20) character set latin1 collate latin1_bin NOT NULL default '',
`t_salary` double NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Dumping data for table `dbtst120`
--
INSERT INTO `dbtst120` VALUES (10, 'CHRISTINE', 52750);
INSERT INTO `dbtst120` VALUES (20, 'MICHAEL', 41250);
INSERT INTO `dbtst120` VALUES (30, 'SALLY', 38250);
#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 )
{
int bug_detected;
MYSQL_STMT * stmt;
char * query;
char * query_ok;
char * query_err;
MYSQL_BIND ResultBind[2];
unsigned long ResultLength[2];
my_bool ResultIsNull[2];
double ResultDblData;
char ResultStrData[ 128 ];
*desc =
"MySQL-#4233 Baan-040620 wrong result with UNION of different types \n"
" ok: select ? /* double */ union all select ? /* long */ \n"
" err: select ? /* long */ union all select ? /* double */ \n"
;
if ( !(stmt = mysql_stmt_init(mysql) ) ) {
return BUG_UNKNOWN;
}
query_err = "select t_firstnme, t_empno from baan.dbtst120 UNION ALL select t_firstnme, t_salary from baan.dbtst120";
query_ok = "select t_firstnme, t_salary from baan.dbtst120 UNION ALL select t_firstnme, t_empno from baan.dbtst120";
query = query_err;
/* Using query_ok gives the expected result
query = query_ok;
*/
if (mysql_stmt_prepare(stmt, query, strlen(query))) {
printf( "Prepare ERR: %s\n", query );
mysql_stmt_close(stmt);
return BUG_UNKNOWN;
}
/* Bind the result buffers */
ResultBind[0].buffer_type= MYSQL_TYPE_STRING;
ResultBind[0].buffer= ResultStrData;
ResultBind[0].buffer_length = sizeof( ResultStrData );
ResultBind[0].is_null= &ResultIsNull[0];
ResultBind[0].length= &ResultLength[0];
ResultBind[1].buffer_type= MYSQL_TYPE_DOUBLE;
ResultBind[1].buffer= (char *) &ResultDblData;
ResultBind[1].is_null= &ResultIsNull[1];
ResultBind[1].length= &ResultLength[1];
if (mysql_stmt_bind_result(stmt, ResultBind)) {
mysql_stmt_close(stmt);
return BUG_UNKNOWN;
}
/* Execute */
if (mysql_stmt_execute(stmt)) {
mysql_stmt_close(stmt);
return BUG_UNKNOWN;
}
/* Fetch all rows */
bug_detected = 0;
while (!mysql_stmt_fetch(stmt))
{
printf( "Fetched name = '%s', empno/salary = %f\n", ResultStrData, ResultDblData );
if ( ResultDblData < 1 ) {
bug_detected = 1;
}
}
/* Close the statement */
mysql_stmt_close(stmt);
if ( bug_detected ) {
return BUG_PRESENT;
} else {
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 */ Pwd,
/* 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;
}
Description: This happens when the C API for prepared statements is used. A sample C source has been included. A query like: select double from T union all select long from T gives the expected result: 52570.00 41250.00 38250.00 10.00 20.00 30.00 Switching the parts of the union: select long from T union all select double from T gives the WRONG result: 0.00 0.00 0.00 0.00 0.00 0.00 How to repeat: -- phpMyAdmin SQL Dump -- version 2.6.0-alpha2 -- http://www.phpmyadmin.net -- -- Host: eddie.ssainternal.net -- Generation Time: Jun 22, 2004 at 11:20 AM -- Server version: 4.1.2 -- PHP Version: 4.3.3 -- -- Database : `baan` -- -- -------------------------------------------------------- -- -- Table structure for table `dbtst120` -- CREATE TABLE `dbtst120` ( `t_empno` int(11) NOT NULL default '0', `t_firstnme` varchar(20) character set latin1 collate latin1_bin NOT NULL default '', `t_salary` double NOT NULL default '0' ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -- Dumping data for table `dbtst120` -- INSERT INTO `dbtst120` VALUES (10, 'CHRISTINE', 52750); INSERT INTO `dbtst120` VALUES (20, 'MICHAEL', 41250); INSERT INTO `dbtst120` VALUES (30, 'SALLY', 38250); #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 ) { int bug_detected; MYSQL_STMT * stmt; char * query; char * query_ok; char * query_err; MYSQL_BIND ResultBind[2]; unsigned long ResultLength[2]; my_bool ResultIsNull[2]; double ResultDblData; char ResultStrData[ 128 ]; *desc = "MySQL-#4233 Baan-040620 wrong result with UNION of different types \n" " ok: select ? /* double */ union all select ? /* long */ \n" " err: select ? /* long */ union all select ? /* double */ \n" ; if ( !(stmt = mysql_stmt_init(mysql) ) ) { return BUG_UNKNOWN; } query_err = "select t_firstnme, t_empno from baan.dbtst120 UNION ALL select t_firstnme, t_salary from baan.dbtst120"; query_ok = "select t_firstnme, t_salary from baan.dbtst120 UNION ALL select t_firstnme, t_empno from baan.dbtst120"; query = query_err; /* Using query_ok gives the expected result query = query_ok; */ if (mysql_stmt_prepare(stmt, query, strlen(query))) { printf( "Prepare ERR: %s\n", query ); mysql_stmt_close(stmt); return BUG_UNKNOWN; } /* Bind the result buffers */ ResultBind[0].buffer_type= MYSQL_TYPE_STRING; ResultBind[0].buffer= ResultStrData; ResultBind[0].buffer_length = sizeof( ResultStrData ); ResultBind[0].is_null= &ResultIsNull[0]; ResultBind[0].length= &ResultLength[0]; ResultBind[1].buffer_type= MYSQL_TYPE_DOUBLE; ResultBind[1].buffer= (char *) &ResultDblData; ResultBind[1].is_null= &ResultIsNull[1]; ResultBind[1].length= &ResultLength[1]; if (mysql_stmt_bind_result(stmt, ResultBind)) { mysql_stmt_close(stmt); return BUG_UNKNOWN; } /* Execute */ if (mysql_stmt_execute(stmt)) { mysql_stmt_close(stmt); return BUG_UNKNOWN; } /* Fetch all rows */ bug_detected = 0; while (!mysql_stmt_fetch(stmt)) { printf( "Fetched name = '%s', empno/salary = %f\n", ResultStrData, ResultDblData ); if ( ResultDblData < 1 ) { bug_detected = 1; } } /* Close the statement */ mysql_stmt_close(stmt); if ( bug_detected ) { return BUG_PRESENT; } else { 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 */ Pwd, /* 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; }