MySQL Bugs: #16934: Unsigned values > 2^63 (UInt64) cannot be used in prepared statements

Bug #16934	Unsigned values > 2^63 (UInt64) cannot be used in prepared statements
Submitted:	31 Jan 2006 13:22	Modified:	4 Jun 2006 4:04
Reporter:	Thomas Krüger	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	1.0.7	OS:	Windows (XP Professional)
Assigned to:		CPU Architecture:	Any

Description:
When using a Unsigned Int64 field in a prepared statement, the unsigned bit is lost when the type is written. Therefore values > 2^63 cannot be stored.
("Value was either too large or too small for a UInt64.")

How to repeat:
Use a DECIMAL(20) column to store the value of System.UInt64.MaxValue in a prepared statement.

Suggested fix:
MySQLParameter.cs

		internal int GetPSType()
		{	// unsigned bit is 1<<15 == 0x8000
			if (this.mySqlDbType == MySqlDbType.Bit)
				return (int)MySqlDbType.Int64 | (1<<15);
			else 
			{
				if (isUnsigned) // unsigned bit must be given, otherwise UInt64.Max not storable
					return (int)this.mySqlDbType | (1<<15);
				return (int)this.mySqlDbType;
			}
		}

Hi Thomas.
Can you please provide us with MySQL server version and small but complete test case for this problem?

The server version was 5.0.20 (18+19 have the same problem too.)

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;
using MySql.Data.MySqlClient;

/*
 * Shows the problem http://bugs.mysql.com/bug.php?id=16934
 * Please see the comment at Prepare() !
 */
/* Create a database with name 'testint64' before.
CREATE DATABASE `mysqltest`;
DROP TABLE IF EXISTS `mysqltest`.`testint64`;
CREATE TABLE  `mysqltest`.`testint64` (
  `id` int(10) unsigned default NULL,
  `sval` bigint(20) default NULL,
  `uval` bigint(20) unsigned default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
      
*/

namespace mysqltest
{
    class Program
    {
        readonly static string connstr = "Server=localhost;Database=mysqltest;Username=tom;Password=tom;allowzerodatetime=true;";

        static void Main(string[] args)
        {
            using (MySqlConnection con = new MySqlConnection(connstr))
            {
                con.Open();
                Console.WriteLine("Server version: " + con.ServerVersion);
                Delete(con);
                Insert(con);
                Query(con);
            }
        }

        static void AddParam(IDbCommand cmd, string n, object v)
        {
            IDataParameter p = cmd.CreateParameter();
            p.ParameterName = n;
            p.Value = v;
            cmd.Parameters.Add(p);
        }

        static void Delete(IDbConnection con)
        {
            using (IDbCommand cmd = con.CreateCommand())
            {
                cmd.CommandText = "DELETE FROM testint64 WHERE id > 0";
                cmd.ExecuteNonQuery();
            }
        }

        static void Insert(IDbConnection con)
        {
            using (IDbCommand cmd = con.CreateCommand())
            {
                cmd.CommandText = "INSERT INTO testint64 VALUES(?1,?2,?3)";
                //TONI: There are actually two different errors here:
                // (1) If Prepare is called, the value cannot be stored->exception
                // (2) If Prepare is not called, the value is wrong.
                // Please see the output on the screen as well.
                cmd.Prepare();
                int simpleId = 45;
                AddParam(cmd,"?1",simpleId);
                long neg = System.Int64.MinValue;
                AddParam(cmd,"?2",neg);
                ulong pos = System.UInt64.MaxValue;
                AddParam(cmd,"?3",pos);
                Console.WriteLine("Inserting id=" + simpleId + ", sval=" + neg+", uval="+pos); 
                Console.WriteLine("Rows=" + cmd.ExecuteNonQuery());                
            }
        }

        static void Query(IDbConnection con)
        {
            IDbCommand cmd = con.CreateCommand();
            cmd.CommandText = "select id, sval, uval from testint64";
            cmd.Prepare();
            IDataReader rdr = cmd.ExecuteReader();
            ShowResult(rdr);
            rdr.Close();
        }

        static void ShowResult(IDataReader rdr)
        {
            int n = 0;
            while (rdr.Read())
            {
                Console.Write("[" + n + "] "); n++;
                for(int f = 0; f < rdr.FieldCount; f++)
                {
                    if (f > 0)
                        Console.Write(" ");
                    Console.Write(rdr.GetName(f));
                    Console.Write("=");
                    object o = rdr.GetValue(f);
                    Console.Write(o);
                }
                Console.WriteLine("");
            }
        }
    }
}

Verified as described by reporter using test case provided.
Environment:
  MySQL server 5.0.22 bk build
  WinXP SP2
  Connector/NET 1.0.7 svn Rev: 233, 2006-04-25
  Test cases differ only in that for second case com.Prepare line is remarked

Findings:
PREPARED EXECUTION (incorrect):
		      5 Init DB     test
		      5 Query       DELETE FROM testint64 WHERE id > 0
060518  8:04:05	      5 Prepare     [1] 
060518  8:17:06	      6 Connect     root@localhost on test
		      6 Query       SHOW FIELDS FROM testint64
		      6 Query       SHOW KEYS FROM testint64
		      6 Quit       
060518  8:40:32	      4 Quit       
060518  8:55:36	      5 Execute     [1] INSERT INTO testint64 VALUES(45,-9223372036854775808,-1)
//should be: VALUES(45,-9223372036854775808,18446744073709551615)

mysql> select * from testint64;
+------+----------------------+------+
| id   | sval                 | uval |
+------+----------------------+------+
|   45 | -9223372036854775808 |    0 |
+------+----------------------+------+
1 row in set (0.00 sec)

UNPREPARED EXECUTION (correct):
		      8 Init DB     test
		      8 Query       DELETE FROM testint64 WHERE id > 0
		      8 Query       INSERT INTO testint64 VALUES(45,-9223372036854775808,18446744073709551615)

mysql> select * from testint64;
+------+----------------------+----------------------+
| id   | sval                 | uval                 |
+------+----------------------+----------------------+
|   45 | -9223372036854775808 | 18446744073709551615 |
+------+----------------------+----------------------+
1 row in set (0.00 sec)

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/6645

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/6646

Fixed in 1.0.8

Added to the changelog.