resultsetregressiontest.java

开发MySql数据库的最新JDBC驱动。

/*
 Copyright (C) 2002-2007 MySQL AB

 This program is free software; you can redistribute it and/or modify
 it under the terms of version 2 of the GNU General Public License as
 published by the Free Software Foundation.

 There are special exceptions to the terms and conditions of the GPL
 as it is applied to this software. View the full text of the
 exception in file EXCEPTIONS-CONNECTOR-J in the directory of this
 software distribution.

 This program is distributed in the hope that it will be useful,
 but WITHOUT ANY WARRANTY; without even the implied warranty of
 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 GNU General Public License for more details.

 You should have received a copy of the GNU General Public License
 along with this program; if not, write to the Free Software
 Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA



 */
package testsuite.regression;

import java.io.Reader;
import java.lang.reflect.Array;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.GregorianCalendar;
import java.util.List;
import java.util.Locale;
import java.util.Properties;
import java.util.TimeZone;

import testsuite.BaseTestCase;

import com.mysql.jdbc.Messages;
import com.mysql.jdbc.MysqlDataTruncation;
import com.mysql.jdbc.NotUpdatable;
import com.mysql.jdbc.SQLError;
import com.mysql.jdbc.StringUtils;
import com.mysql.jdbc.log.StandardLogger;

/**
 * Regression test cases for the ResultSet class.
 * 
 * @author Mark Matthews
 */
public class ResultSetRegressionTest extends BaseTestCase {
	/**
	 * Creates a new ResultSetRegressionTest
	 * 
	 * @param name
	 *            the name of the test to run
	 */
	public ResultSetRegressionTest(String name) {
		super(name);
	}

	/**
	 * Runs all test cases in this test suite
	 * 
	 * @param args
	 */
	public static void main(String[] args) {
		junit.textui.TestRunner.run(ResultSetRegressionTest.class);
	}

	/**
	 * Tests fix for BUG#???? -- Numeric types and server-side prepared
	 * statements incorrectly detect nulls.
	 * 
	 * @throws Exception
	 *             if the test fails
	 */
	public void testBug2359() throws Exception {
		try {
			/*
			 * this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug2359");
			 * this.stmt.executeUpdate("CREATE TABLE testBug2359 (field1 INT)
			 * TYPE=InnoDB"); this.stmt.executeUpdate("INSERT INTO testBug2359
			 * VALUES (null), (1)");
			 * 
			 * this.pstmt = this.conn.prepareStatement("SELECT field1 FROM
			 * testBug2359 WHERE field1 IS NULL"); this.rs =
			 * this.pstmt.executeQuery();
			 * 
			 * assertTrue(this.rs.next());
			 * 
			 * assertTrue(this.rs.getByte(1) == 0);
			 * assertTrue(this.rs.wasNull());
			 * 
			 * assertTrue(this.rs.getShort(1) == 0);
			 * assertTrue(this.rs.wasNull());
			 * 
			 * assertTrue(this.rs.getInt(1) == 0);
			 * assertTrue(this.rs.wasNull());
			 * 
			 * assertTrue(this.rs.getLong(1) == 0);
			 * assertTrue(this.rs.wasNull());
			 * 
			 * assertTrue(this.rs.getFloat(1) == 0);
			 * assertTrue(this.rs.wasNull());
			 * 
			 * assertTrue(this.rs.getDouble(1) == 0);
			 * assertTrue(this.rs.wasNull());
			 * 
			 * assertTrue(this.rs.getBigDecimal(1) == null);
			 * assertTrue(this.rs.wasNull());
			 * 
			 * this.rs.close();
			 * 
			 * this.pstmt = this.conn.prepareStatement("SELECT max(field1) FROM
			 * testBug2359 WHERE field1 IS NOT NULL"); this.rs =
			 * this.pstmt.executeQuery(); assertTrue(this.rs.next());
			 * 
			 * assertTrue(this.rs.getByte(1) == 1);
			 * assertTrue(!this.rs.wasNull());
			 * 
			 * assertTrue(this.rs.getShort(1) == 1);
			 * assertTrue(!this.rs.wasNull());
			 * 
			 * assertTrue(this.rs.getInt(1) == 1);
			 * assertTrue(!this.rs.wasNull());
			 * 
			 * assertTrue(this.rs.getLong(1) == 1);
			 * assertTrue(!this.rs.wasNull());
			 * 
			 * assertTrue(this.rs.getFloat(1) == 1);
			 * assertTrue(!this.rs.wasNull());
			 * 
			 * assertTrue(this.rs.getDouble(1) == 1);
			 * assertTrue(!this.rs.wasNull());
			 * 
			 * assertTrue(this.rs.getBigDecimal(1) != null);
			 * assertTrue(!this.rs.wasNull());
			 * 
			 */
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug2359_1");
			this.stmt
					.executeUpdate("CREATE TABLE testBug2359_1 (id INT) TYPE=InnoDB");
			this.stmt.executeUpdate("INSERT INTO testBug2359_1 VALUES (1)");

			this.pstmt = this.conn
					.prepareStatement("SELECT max(id) FROM testBug2359_1");
			this.rs = this.pstmt.executeQuery();

			if (this.rs.next()) {
				assertTrue(this.rs.getInt(1) != 0);
				this.rs.close();
			}

			this.rs.close();
		} finally {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug2359_1");
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug2359");

			this.rs.close();
			this.pstmt.close();
		}
	}

	/**
	 * Tests fix for BUG#2643, ClassCastException when using this.rs.absolute()
	 * and server-side prepared statements.
	 * 
	 * @throws Exception
	 */
	public void testBug2623() throws Exception {
		PreparedStatement pStmt = null;

		try {
			pStmt = this.conn
					.prepareStatement("SELECT NOW()",
							ResultSet.TYPE_SCROLL_SENSITIVE,
							ResultSet.CONCUR_READ_ONLY);

			this.rs = pStmt.executeQuery();

			this.rs.absolute(1);
		} finally {
			if (this.rs != null) {
				this.rs.close();
			}

			this.rs = null;

			if (pStmt != null) {
				pStmt.close();
			}
		}
	}

	/**
	 * Tests fix for BUG#2654, "Column 'column.table' not found" when "order by"
	 * in query"
	 * 
	 * @throws Exception
	 *             if the test fails
	 */
	public void testBug2654() throws Exception {
		if (false) { // this is currently a server-level bug

			try {
				this.stmt.executeUpdate("DROP TABLE IF EXISTS foo");
				this.stmt.executeUpdate("DROP TABLE IF EXISTS bar");

				this.stmt.executeUpdate("CREATE TABLE foo ("
						+ "  id tinyint(3) default NULL,"
						+ "  data varchar(255) default NULL"
						+ ") TYPE=MyISAM DEFAULT CHARSET=latin1");
				this.stmt
						.executeUpdate("INSERT INTO foo VALUES (1,'male'),(2,'female')");

				this.stmt.executeUpdate("CREATE TABLE bar ("
						+ "id tinyint(3) unsigned default NULL,"
						+ "data char(3) default '0'"
						+ ") TYPE=MyISAM DEFAULT CHARSET=latin1");

				this.stmt
						.executeUpdate("INSERT INTO bar VALUES (1,'yes'),(2,'no')");

				String statement = "select foo.id, foo.data, "
						+ "bar.data from foo, bar" + "	where "
						+ "foo.id = bar.id order by foo.id";

				String column = "foo.data";

				this.rs = this.stmt.executeQuery(statement);

				ResultSetMetaData rsmd = this.rs.getMetaData();
				System.out.println(rsmd.getTableName(1));
				System.out.println(rsmd.getColumnName(1));

				this.rs.next();

				String fooData = this.rs.getString(column);
			} finally {
				this.stmt.executeUpdate("DROP TABLE IF EXISTS foo");
				this.stmt.executeUpdate("DROP TABLE IF EXISTS bar");
			}
		}
	}

	/**
	 * Tests for fix to BUG#1130
	 * 
	 * @throws Exception
	 *             if the test fails
	 */
	public void testClobTruncate() throws Exception {
		if (isRunningOnJdk131()) {
			return; // test not valid on JDK-1.3.1
		}

		try {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testClobTruncate");
			this.stmt
					.executeUpdate("CREATE TABLE testClobTruncate (field1 TEXT)");
			this.stmt
					.executeUpdate("INSERT INTO testClobTruncate VALUES ('abcdefg')");

			this.rs = this.stmt.executeQuery("SELECT * FROM testClobTruncate");
			this.rs.next();

			Clob clob = this.rs.getClob(1);
			clob.truncate(3);

			Reader reader = clob.getCharacterStream();
			char[] buf = new char[8];
			int charsRead = reader.read(buf);

			String clobAsString = new String(buf, 0, charsRead);

			assertTrue(clobAsString.equals("abc"));
		} finally {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testClobTruncate");
		}
	}

	/**
	 * Tests that streaming result sets are registered correctly.
	 * 
	 * @throws Exception
	 *             if any errors occur
	 */
	public void testClobberStreamingRS() throws Exception {
		try {
			Properties props = new Properties();
			props.setProperty("clobberStreamingResults", "true");

			Connection clobberConn = getConnectionWithProps(props);

			Statement clobberStmt = clobberConn.createStatement();

			clobberStmt.executeUpdate("DROP TABLE IF EXISTS StreamingClobber");
			clobberStmt
					.executeUpdate("CREATE TABLE StreamingClobber ( DUMMYID "
							+ " INTEGER NOT NULL, DUMMYNAME VARCHAR(32),PRIMARY KEY (DUMMYID) )");
			clobberStmt
					.executeUpdate("INSERT INTO StreamingClobber (DUMMYID, DUMMYNAME) VALUES (0, NULL)");
			clobberStmt
					.executeUpdate("INSERT INTO StreamingClobber (DUMMYID, DUMMYNAME) VALUES (1, 'nro 1')");
			clobberStmt
					.executeUpdate("INSERT INTO StreamingClobber (DUMMYID, DUMMYNAME) VALUES (2, 'nro 2')");
			clobberStmt
					.executeUpdate("INSERT INTO StreamingClobber (DUMMYID, DUMMYNAME) VALUES (3, 'nro 3')");

			Statement streamStmt = null;

			try {
				streamStmt = clobberConn.createStatement(
						java.sql.ResultSet.TYPE_FORWARD_ONLY,
						java.sql.ResultSet.CONCUR_READ_ONLY);
				streamStmt.setFetchSize(Integer.MIN_VALUE);

				this.rs = streamStmt.executeQuery("SELECT DUMMYID, DUMMYNAME "
						+ "FROM StreamingClobber ORDER BY DUMMYID");

				this.rs.next();

				// This should proceed normally, after the driver
				// clears the input stream
				clobberStmt.executeQuery("SHOW VARIABLES");
				this.rs.close();
			} finally {
				if (streamStmt != null) {
					streamStmt.close();
				}
			}
		} finally {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS StreamingClobber");
		}
	}

	/**
	 * DOCUMENT ME!
	 * 
	 * @throws Exception
	 *             DOCUMENT ME!
	 */
	public void testEmptyResultSetGet() throws Exception {
		try {
			this.rs = this.stmt.executeQuery("SHOW VARIABLES LIKE 'foo'");
			System.out.println(this.rs.getInt(1));
		} catch (SQLException sqlEx) {
			assertTrue("Correct exception not thrown",
					SQLError.SQL_STATE_GENERAL_ERROR
							.equals(sqlEx.getSQLState()));
		}
	}

	/**
	 * Checks fix for BUG#1592 -- cross-database updatable result sets are not
	 * checked for updatability correctly.
	 * 
	 * @throws Exception
	 *             if the test fails.
	 */
	public void testFixForBug1592() throws Exception {
		if (versionMeetsMinimum(4, 1)) {
			Statement updatableStmt = this.conn
					.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
							ResultSet.CONCUR_UPDATABLE);

			try {
				updatableStmt.execute("SELECT * FROM mysql.user");

				this.rs = updatableStmt.getResultSet();
			} catch (SQLException sqlEx) {
				String message = sqlEx.getMessage();

				if ((message != null) && (message.indexOf("denied") != -1)) {
					System.err
							.println("WARN: Can't complete testFixForBug1592(), access to"
									+ " 'mysql' database not allowed");
				} else {
					throw sqlEx;
				}
			}
		}
	}

	/**
	 * Tests fix for BUG#2006, where 2 columns with same name in a result set
	 * are returned via findColumn() in the wrong order...The JDBC spec states,
	 * that the _first_ matching column should be returned.
	 * 
	 * @throws Exception
	 *             if the test fails
	 */
	public void testFixForBug2006() throws Exception {
		try {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testFixForBug2006_1");
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testFixForBug2006_2");
			this.stmt
					.executeUpdate("CREATE TABLE testFixForBug2006_1 (key_field INT NOT NULL)");
			this.stmt
					.executeUpdate("CREATE TABLE testFixForBug2006_2 (key_field INT NULL)");
			this.stmt
					.executeUpdate("INSERT INTO testFixForBug2006_1 VALUES (1)");

			this.rs = this.stmt
					.executeQuery("SELECT testFixForBug2006_1.key_field, testFixForBug2006_2.key_field FROM testFixForBug2006_1 LEFT JOIN testFixForBug2006_2 USING(key_field)");

			ResultSetMetaData rsmd = this.rs.getMetaData();

			assertTrue(rsmd.getColumnName(1).equals(rsmd.getColumnName(2)));
			assertTrue(rsmd.isNullable(this.rs.findColumn("key_field")) == ResultSetMetaData.columnNoNulls);
			assertTrue(rsmd.isNullable(2) == ResultSetMetaData.columnNullable);
			assertTrue(this.rs.next());
			assertTrue(this.rs.getObject(1) != null);
			assertTrue(this.rs.getObject(2) == null);
		} finally {
			if (this.rs != null) {
				try {
					this.rs.close();
				} catch (SQLException sqlEx) {
					// ignore
				}

				this.rs = null;
			}