Bug #5944 Wrong type returned for aggregate query

• Submitted: 7 Oct 2004 2:29
• Modified: 13 Oct 2004 16:41
• Reporter: Keith Dreibelbis
• Status: Not a Bug
• Category: Connector / NET
• Severity: S3 (Non-critical)
• Version: 1.0.0 beta (1.0.0.22722)
• OS: Windows (Windows)
• Assigned to: Reggie Burnett
• CPU Architecture: Any

[7 Oct 2004 2:29] Keith Dreibelbis

Description:
Performing an aggregate query on a column returns the wrong type.  A regular query returns the correct type.

How to repeat:
// Here is an NUnit-style test, put into a TestFixture.

		private int ExecuteNonQuery(MySqlConnection con, string query)
		{
			MySqlCommand cmd = new MySqlCommand(query, con);
			int rowCnt = cmd.ExecuteNonQuery();
			return rowCnt;
		}

		[Test]
		public void AggregateTypesTest()
		{
		
			MySqlConnection con = new MySqlConnection(connectionString);
			con.Open();	

			IDataReader rdr = null;
			try 
			{
				this.ExecuteNonQuery(con, "DROP TABLE IF EXISTS foo");

				this.ExecuteNonQuery(con, "CREATE TABLE foo (abigint bigint, aint int)");

				this.ExecuteNonQuery(con, "INSERT INTO foo VALUES (1, 2)");
				this.ExecuteNonQuery(con, "INSERT INTO foo VALUES (2, 3)");
				this.ExecuteNonQuery(con, "INSERT INTO foo VALUES (3, 4)");
				this.ExecuteNonQuery(con, "INSERT INTO foo VALUES (3, 5)");
							
				// Try a normal query
				string NORMAL_QRY = "SELECT abigint, aint FROM foo WHERE abigint = {0}";
				string qry = String.Format(NORMAL_QRY, 3);
				MySqlCommand cmd1 = new MySqlCommand(qry, con);
				rdr = cmd1.ExecuteReader();
				while (rdr.Read())
				{
					long field1 = rdr.GetInt64(0);
					int field2 = rdr.GetInt32(1); // <--- aint... this succeeds
				}

				rdr.Close();
				rdr = null;

				// Now try an aggregate query
				string AGG_QRY = "SELECT abigint, max(aint) FROM foo GROUP BY abigint";

				MySqlCommand cmd = new MySqlCommand(AGG_QRY, con);
				rdr = cmd.ExecuteReader();
				while (rdr.Read())
				{
					long field1 = rdr.GetInt64(0);
					int field2 = rdr.GetInt32(1); // <--- max(aint)... this fails
				}

				rdr.Close();
				rdr = null;
			}
			finally
			{
				if (rdr != null)
					rdr.Close();		

				this.ExecuteNonQuery(con, "DROP TABLE IF EXISTS foo");
				con.Close();
			}			
		}

[7 Oct 2004 2:31] Keith Dreibelbis

Also, this bug does not exist in ByteFX 0.76

[13 Oct 2004 16:41] Reggie Burnett

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

Keith

This is not a bug.  Max doesn't return the max value of the columns it is working with.  It returns the max value of the type of data it is given.  Consider this, select max(2);

You may disagree with the choice, but max(), when given integer data, returns the largest integer that MySQL can handle (in this case Int64).  So, a user must know to use .GetInt64() when accessing a field that is the result of Max() (or maybe other aggregate functions).