resultsetregressiontest.java

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

	 * @throws Exception
	 *             if the test fails.
	 */
	public void testBug6537() throws Exception {
		if (versionMeetsMinimum(4, 1, 0)) {
			String tableName = "testBug6537";

			try {
				createTable(
						tableName,
						"(`id` int(11) NOT NULL default '0',"
								+ "`value` decimal(10,2) NOT NULL default '0.00', `stringval` varchar(10),"
								+ "PRIMARY KEY  (`id`)"
								+ ") ENGINE=MyISAM DEFAULT CHARSET=latin1");
				this.stmt
						.executeUpdate("INSERT INTO "
								+ tableName
								+ "(id, value, stringval) VALUES (1, 100.00, '100.00'), (2, 200, '200')");

				String sql = "SELECT SUM(value) as total FROM " + tableName
						+ " WHERE id = ? ";
				PreparedStatement pStmt = this.conn.prepareStatement(sql);
				pStmt.setInt(1, 1);
				this.rs = pStmt.executeQuery();
				assertTrue(this.rs.next());

				assertTrue("100.00".equals(this.rs.getBigDecimal("total")
						.toString()));

				sql = "SELECT stringval as total FROM " + tableName
						+ " WHERE id = ? ";
				pStmt = this.conn.prepareStatement(sql);
				pStmt.setInt(1, 2);
				this.rs = pStmt.executeQuery();
				assertTrue(this.rs.next());

				assertTrue("200.00".equals(this.rs.getBigDecimal("total", 2)
						.toString()));

			} finally {
				dropTable(tableName);
			}
		}
	}

	/**
	 * Tests fix for BUG#6231, ResultSet.getTimestamp() on a column with TIME in
	 * it fails.
	 * 
	 * @throws Exception
	 *             if the test fails.
	 */
	public void testBug6231() throws Exception {

		try {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug6231");
			this.stmt.executeUpdate("CREATE TABLE testBug6231 (field1 TIME)");
			this.stmt
					.executeUpdate("INSERT INTO testBug6231 VALUES ('09:16:00')");

			this.rs = this.stmt.executeQuery("SELECT field1 FROM testBug6231");
			this.rs.next();
			long asMillis = this.rs.getTimestamp(1).getTime();
			Calendar cal = Calendar.getInstance();

			if (isRunningOnJdk131()) {
				cal.setTime(new Date(asMillis));
			} else {
				cal.setTimeInMillis(asMillis);
			}

			assertEquals(9, cal.get(Calendar.HOUR));
			assertEquals(16, cal.get(Calendar.MINUTE));
			assertEquals(0, cal.get(Calendar.SECOND));
		} finally {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug6231");
		}
	}

	public void testBug6619() throws Exception {
		try {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug6619");
			this.stmt.executeUpdate("CREATE TABLE testBug6619 (field1 int)");
			this.stmt.executeUpdate("INSERT INTO testBug6619 VALUES (1), (2)");

			PreparedStatement pStmt = this.conn
					.prepareStatement("SELECT SUM(field1) FROM testBug6619");

			this.rs = pStmt.executeQuery();
			this.rs.next();
			System.out.println(this.rs.getString(1));

		} finally {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug6619");
		}
	}

	public void testBug6743() throws Exception {
		// 0x835C U+30BD # KATAKANA LETTER SO
		String katakanaStr = "\u30BD";

		Properties props = new Properties();

		props.setProperty("useUnicode", "true");
		props.setProperty("characterEncoding", "SJIS");

		Connection sjisConn = null;
		Statement sjisStmt = null;

		try {
			sjisConn = getConnectionWithProps(props);
			sjisStmt = sjisConn.createStatement(
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_UPDATABLE);

			sjisStmt.executeUpdate("DROP TABLE IF EXISTS testBug6743");
			StringBuffer queryBuf = new StringBuffer(
					"CREATE TABLE testBug6743 (pkField INT NOT NULL PRIMARY KEY, field1 VARCHAR(32)");

			if (versionMeetsMinimum(4, 1)) {
				queryBuf.append(" CHARACTER SET SJIS");
			}

			queryBuf.append(")");
			sjisStmt.executeUpdate(queryBuf.toString());
			sjisStmt.executeUpdate("INSERT INTO testBug6743 VALUES (1, 'abc')");

			this.rs = sjisStmt
					.executeQuery("SELECT pkField, field1 FROM testBug6743");
			this.rs.next();
			this.rs.updateString(2, katakanaStr);
			this.rs.updateRow();

			String retrString = this.rs.getString(2);
			assertTrue(katakanaStr.equals(retrString));

			this.rs = sjisStmt
					.executeQuery("SELECT pkField, field1 FROM testBug6743");
			this.rs.next();

			retrString = this.rs.getString(2);
			assertTrue(katakanaStr.equals(retrString));
		} finally {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug6743");

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

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

	/**
	 * Tests for presence of BUG#6561, NPE thrown when dealing with 0 dates and
	 * non-unpacked result sets.
	 * 
	 * @throws Exception
	 *             if the test occurs.
	 */
	public void testBug6561() throws Exception {

		try {
			Properties props = new Properties();
			props.setProperty("zeroDateTimeBehavior", "convertToNull");

			Connection zeroConn = getConnectionWithProps(props);

			this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug6561");
			this.stmt
					.executeUpdate("CREATE TABLE testBug6561 (ofield int, field1 DATE, field2 integer, field3 integer)");
			this.stmt
					.executeUpdate("INSERT INTO testBug6561 (ofield, field1,field2,field3)	VALUES (1, 0,NULL,0)");
			this.stmt
					.executeUpdate("INSERT INTO testBug6561 (ofield, field1,field2,field3) VALUES (2, '2004-11-20',NULL,0)");

			PreparedStatement ps = zeroConn
					.prepareStatement("SELECT field1,field2,field3 FROM testBug6561 ORDER BY ofield");
			this.rs = ps.executeQuery();

			assertTrue(this.rs.next());
			assertTrue(null == this.rs.getObject("field1"));
			assertTrue(null == this.rs.getObject("field2"));
			assertTrue(0 == this.rs.getInt("field3"));

			assertTrue(this.rs.next());
			assertEquals("2004-11-20", this.rs.getString("field1"));
			assertTrue(null == this.rs.getObject("field2"));
			assertTrue(0 == this.rs.getInt("field3"));

			ps.close();
		} finally {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS test");
		}
	}

	public void testBug7686() throws SQLException {
		String tableName = "testBug7686";
		createTable(tableName, "(id1 int(10) unsigned NOT NULL,"
				+ " id2 DATETIME, "
				+ " field1 varchar(128) NOT NULL default '',"
				+ " PRIMARY KEY  (id1, id2)) TYPE=InnoDB;");

		this.stmt.executeUpdate("insert into " + tableName
				+ " (id1, id2, field1)"
				+ " values (1, '2005-01-05 13:59:20', 'foo')");

		String sQuery = " SELECT * FROM " + tableName
				+ " WHERE id1 = ? AND id2 = ?";
		this.pstmt = this.conn.prepareStatement(sQuery,
				ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

		this.conn.setAutoCommit(false);
		this.pstmt.setInt(1, 1);
		GregorianCalendar cal = new GregorianCalendar();
		cal.clear();
		cal.set(2005, 00, 05, 13, 59, 20);

		Timestamp jan5before2pm = null;

		if (isRunningOnJdk131()) {
			jan5before2pm = new java.sql.Timestamp(cal.getTime().getTime());
		} else {
			jan5before2pm = new java.sql.Timestamp(cal.getTimeInMillis());
		}

		this.pstmt.setTimestamp(2, jan5before2pm);
		this.rs = this.pstmt.executeQuery();
		assertTrue(this.rs.next());
		this.rs.absolute(1);
		this.rs.updateString("field1", "bar");
		this.rs.updateRow();
		this.conn.commit();
		this.conn.setAutoCommit(true);
	}

	/**
	 * Tests fix for BUG#7715 - Timestamps converted incorrectly to strings with
	 * SSPS and Upd. Result Sets.
	 * 
	 * @throws Exception
	 *             if the test fails.
	 */
	public void testBug7715() throws Exception {
		PreparedStatement pStmt = null;

		try {
			this.stmt
					.executeUpdate("DROP TABLE IF EXISTS testConvertedBinaryTimestamp");
			this.stmt
					.executeUpdate("CREATE TABLE testConvertedBinaryTimestamp (field1 VARCHAR(32), field2 VARCHAR(32), field3 VARCHAR(32), field4 TIMESTAMP)");
			this.stmt
					.executeUpdate("INSERT INTO testConvertedBinaryTimestamp VALUES ('abc', 'def', 'ghi', NOW())");

			pStmt = this.conn
					.prepareStatement(
							"SELECT field1, field2, field3, field4 FROM testConvertedBinaryTimestamp",
							ResultSet.TYPE_SCROLL_SENSITIVE,
							ResultSet.CONCUR_UPDATABLE);

			this.rs = pStmt.executeQuery();
			assertTrue(this.rs.next());

			this.rs.getObject(4); // fails if bug exists
		} finally {
			this.stmt
					.executeUpdate("DROP TABLE IF EXISTS testConvertedBinaryTimestamp");
		}
	}

	/**
	 * Tests fix for BUG#8428 - getString() doesn't maintain format stored on
	 * server.
	 * 
	 * @throws Exception
	 *             if the test fails.
	 */
	public void testBug8428() throws Exception {
		Connection noSyncConn = null;

		try {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug8428");
			this.stmt
					.executeUpdate("CREATE TABLE testBug8428 (field1 YEAR, field2 DATETIME)");
			this.stmt
					.executeUpdate("INSERT INTO testBug8428 VALUES ('1999', '2005-02-11 12:54:41')");

			Properties props = new Properties();
			props.setProperty("noDatetimeStringSync", "true");
			props.setProperty("useUsageAdvisor", "true");
			props.setProperty("yearIsDateType", "false"); // for 3.1.9+

			noSyncConn = getConnectionWithProps(props);

			this.rs = noSyncConn.createStatement().executeQuery(
					"SELECT field1, field2 FROM testBug8428");
			this.rs.next();
			assertEquals("1999", this.rs.getString(1));
			assertEquals("2005-02-11 12:54:41", this.rs.getString(2));

			this.rs = noSyncConn.prepareStatement(
					"SELECT field1, field2 FROM testBug8428").executeQuery();
			this.rs.next();
			assertEquals("1999", this.rs.getString(1));
			assertEquals("2005-02-11 12:54:41", this.rs.getString(2));
		} finally {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug8428");
		}
	}

	/**
	 * Tests fix for Bug#8868, DATE_FORMAT() queries returned as BLOBs from
	 * getObject().
	 * 
	 * @throws Exception
	 *             if the test fails.
	 */
	public void testBug8868() throws Exception {
		if (versionMeetsMinimum(4, 1)) {
			createTable("testBug8868",
					"(field1 DATE, field2 VARCHAR(32) CHARACTER SET BINARY)");
			this.stmt
					.executeUpdate("INSERT INTO testBug8868 VALUES (NOW(), 'abcd')");
			try {
				this.rs = this.stmt
						.executeQuery("SELECT DATE_FORMAT(field1,'%b-%e %l:%i%p') as fmtddate, field2 FROM testBug8868");
				this.rs.next();
				assertEquals("java.lang.String", this.rs.getObject(1)
						.getClass().getName());
			} finally {
				if (this.rs != null) {
					this.rs.close();
				}
			}
		}
	}

	/**
	 * Tests fix for BUG#9098 - Server doesn't give us info to distinguish
	 * between CURRENT_TIMESTAMP and 'CURRENT_TIMESTAMP' for default values.
	 * 
	 * @throws Exception
	 *             if the test fails
	 */
	public void testBug9098() throws Exception {
		if (versionMeetsMinimum(4, 1, 10)) {
			Statement updatableStmt = null;

			try {
				this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug9098");
				this.stmt
						.executeUpdate("CREATE TABLE testBug9098(pkfield INT PRIMARY KEY NOT NULL AUTO_INCREMENT, \n"
								+ "tsfield TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, tsfield2 TIMESTAMP NOT NULL DEFAULT '2005-12-25 12:20:52', charfield VARCHAR(4) NOT NULL DEFAULT 'abcd')");
				updatableStmt = this.conn.createStatement(
						ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_UPDATABLE);
				this.rs = updatableStmt
						.executeQuery("SELECT pkfield, tsfield, tsfield2, charfield FROM testBug9098");
				this.rs.moveToInsertRow();
				this.rs.insertRow();

			} finally {
				this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug9098");
			}
		}
	}

	/**
	 * Tests fix for BUG#9236, a continuation of BUG#8868, where functions used
	 * in queries that should return non-string types when resolved by temporary
	 * tables suddenly become opaque binary strings (work-around for server
	 * limitation)
	 * 
	 * @throws Exception
	 *             if the test fails.
	 */
	public void testBug9236() throws Exception {
		if (versionMeetsMinimum(4, 1)) {
			try {
				createTable(
						"testBug9236",
						"("
								+ "field_1 int(18) NOT NULL auto_increment,"
								+ "field_2 varchar(50) NOT NULL default '',"
								+ "field_3 varchar(12) default NULL,"
								+ "field_4 int(18) default NULL,"
								+ "field_5 int(18) default NULL,"
								+ "field_6 datetime default NULL,"
								+ "field_7 varchar(30) default NULL,"
								+ "field_8 varchar(50) default NULL,"
								+ "field_9 datetime default NULL,"
								+ "field_10 int(18) NOT NULL default '0',"
								+ "field_11 int(18) default NULL,"
								+ "field_12 datetime NOT NULL default '0000-00-00 00:00:00',"
								+ "PRIMARY KEY  (field_1)," + "KEY (field_4),"
								+ "KEY (field_2)," + "KEY (field_3),"
								+ "KEY (field_7,field_1)," + "KEY (field_5),"
								+ "KEY (field_6,field_10,field_9),"
								+ "KEY (field_11,field_10),"
								+ "KEY (field_12,field_10)"
								+ ") ENGINE=InnoDB DEFAULT CHARSET=latin1");

				this.stmt
						.executeUpdate("INSERT INTO testBug9236 VALUES "
								+ "(1,'0',NULL,-1,0,'0000-00-00 00:00:00','123456789','-1','2004-03-13 14:21:38',0,NULL,'2004-03-13 14:21:38'),"
								+ "(2,'0',NULL,1,0,'0000-00-00 00:00:00',NULL,'1',NULL,0,NULL,'2004-07-13 14:29:52'),"
								+ "(3,'0',NULL,1,0,'0000-00-00 00:00:00',NULL,'2',NULL,0,NULL,'2004-07-16 13:20:51'),"
								+ "(4,'0',NULL,1,0,'0000-00-00 00:00:00',NULL,'3','2004-07-16 13:43:39',0,NULL,'2004-07-16 13:22:01'),"
								+ "(5,'0',NULL,1,0,'0000-00-00 00:00:00',NULL,'4','2004-07-16 13:23:48',0,NULL,'2004-07-16 13:23:01'),"
								+ "(6,'0',NULL,1,0,'0000-00-00 00:00:00',NULL,'5',NULL,0,NULL,'2004-07-16 14:41:07'),"
								+ "(7,'0',NULL,1,0,'0000-00-00 00:00:00',NULL,'6',NULL,0,NULL,'2004-07-16 14:41:34'),"
								+ "(8,'0',NULL,1,0,'0000-00-00 00:00:00',NULL,'7',NULL,0,NULL,'2004-07-16 14:41:54'),"
								+ "(9,'0',NULL,1,0,'0000-00-00 00:00:00',NULL,'8',NULL,0,NULL,'2004-07-16 14:42:42'),"
								+ "(10,'0','PI',1,0,'0000-00-00 00:00:00',NULL,'9',NULL,0,NULL,'2004-07-18 10:51:30'),"
								+ "(11,'0',NULL,1,0,'0000-00-00 00:00:00',NULL,'10','2004-07-23 17:23:06',0,NULL,'2004-07-23 17:18:19'),"
								+ "(12,'0',NULL,1,0,'0000-00-00 00:00:00',NULL,'11','2004-07-23 17:24:45',0,NULL,'2004-07-23 17:23:57'),"
								+ "(13,'0',NULL,1,0,'0000-00-00 00:00:00',NULL,'12','2004-07-23 17:30:51',0,NULL,'2004-07-23 17:30:15'),"
								+ "(14,'0',NULL,1,0,'0000-00-00 00:00:00',NULL,'13','2004-07-26 17:50:19',0,NULL,'2004-07-26 17:49:38'),"
								+ "(15,'0','FRL',1,0,'0000-00-00 00:00:00',NULL,'1',NULL,0,NULL,'2004-08-19 18:29:18'),"
								+ "(16,'0','FRL',1,0,'0000-00-00 00:00:00',NULL,'15',NULL,0,NULL,'2005-03-16 12:08:28')");

				createTable("testBug9236_1",
						"(field1 CHAR(2) CHARACTER SET BINARY)");
				this.stmt
						.executeUpdate("INSERT INTO testBug9236_1 VALUES ('ab')");
				this.rs = this.stmt
						.executeQuery("SELECT field1 FROM testBug9236_1");

				ResultSetMetaData rsmd = this.rs.getMetaData();
				assertEquals("[B", rsmd.getColumnClassName(1));
				assertTrue(this.rs.next());
				Object asObject = this.rs.getObject(1);
				assertEquals("[B", asObject.getClass().getName());

				this.rs = this.stmt
						.executeQuery("select DATE_FORMAT(field_12, '%Y-%m-%d') as date, count(*) as count from testBug9236 where field_10 = 0 and field_3 = 'FRL' and field_12 >= '2005-03-02 00:00:00' and field_12 <= '2005-03-17 00:00:00' group by date");
				rsmd = this.rs.getMetaData();
				assertEquals("java.lang.String", rsmd.getColumnClassName(1));
				this.rs.next();