From 68067eb1ea6e6611ce6b56acf70e2077cb3f9a13 Mon Sep 17 00:00:00 2001 From: Iwao AVE! Date: Mon, 13 May 2019 16:46:57 +0900 Subject: [PATCH] Fix for #93444 - improve setObject behavior for LocalDate, LocalDateTime and LocalTime This fixes the following issues. - Inserted values are altered when there is timezone difference between server and client. - Nanoseconds part of LocalTime is lost. --- .../java/com/mysql/cj/QueryBindings.java | 3 + .../java/com/mysql/cj/util/TimeUtil.java | 40 ++++-- .../com/mysql/cj/AbstractQueryBindings.java | 66 ++++++++-- .../mysql/cj/ClientPreparedQueryBindings.java | 22 ++++ .../cj/ServerPreparedQueryBindValue.java | 37 +++++- .../mysql/cj/ServerPreparedQueryBindings.java | 20 +++ .../regression/StatementRegressionTest.java | 115 ++++++++++++++++++ .../java/testsuite/simple/StatementsTest.java | 94 ++++++++++++++ 8 files changed, 371 insertions(+), 26 deletions(-) diff --git a/src/main/core-api/java/com/mysql/cj/QueryBindings.java b/src/main/core-api/java/com/mysql/cj/QueryBindings.java index cb41bff39..12cbffd4c 100644 --- a/src/main/core-api/java/com/mysql/cj/QueryBindings.java +++ b/src/main/core-api/java/com/mysql/cj/QueryBindings.java @@ -38,6 +38,7 @@ import java.sql.NClob; import java.sql.Time; import java.sql.Timestamp; +import java.time.LocalDateTime; import java.util.Calendar; import com.mysql.cj.protocol.ColumnDefinition; @@ -160,6 +161,8 @@ // int getInt(int parameterIndex); + void setLocalDateTime(int parameterIndex, LocalDateTime x, MysqlType targetMysqlType); + void setLong(int parameterIndex, long x); // long getLong(int parameterIndex); diff --git a/src/main/core-api/java/com/mysql/cj/util/TimeUtil.java b/src/main/core-api/java/com/mysql/cj/util/TimeUtil.java index 2de162933..6cf152f77 100644 --- a/src/main/core-api/java/com/mysql/cj/util/TimeUtil.java +++ b/src/main/core-api/java/com/mysql/cj/util/TimeUtil.java @@ -147,26 +147,40 @@ public static String getCanonicalTimezone(String timezoneStr, ExceptionIntercept * @return A new Timestamp object cloned from original ones and then rounded or truncated according to required fsp value */ public static Timestamp adjustTimestampNanosPrecision(Timestamp ts, int fsp, boolean serverRoundFracSecs) { + int nanos = roundOrTruncateNanos(ts.getNanos(), fsp, serverRoundFracSecs); + Timestamp res = (Timestamp) ts.clone(); + if (nanos > 999999999) { + nanos %= 1000000000; // get only last 9 digits + res.setTime(res.getTime() + 1000); // increment seconds + } + res.setNanos(nanos); + + return res; + } + + /** + * Round or truncate the nanoseconds value according to field precision and sql mode. + * + * @param nanos + * Value to round or truncate + * @param fsp + * Value in the range from 0 to 6 specifying fractional seconds precision + * @param serverRoundFracSecs + * Flag indicating whether rounding or truncation occurs on server when inserting a TIME, DATE, or TIMESTAMP value with a fractional seconds part + * into a column having the same type but fewer fractional digits: true means rounding, false means truncation. The proper value should be + * detected by analyzing sql_mode server variable for TIME_TRUNCATE_FRACTIONAL presence. + * @return Nanoseconds value rounded or truncated according to required fsp value, could be larger than 999999999 when serverRoundFracSecs is true + */ + public static int roundOrTruncateNanos(int nanos, int fsp, boolean serverRoundFracSecs) { if (fsp < 0 || fsp > 6) { throw ExceptionFactory.createException(WrongArgumentException.class, "fsp value must be in 0 to 6 range."); } - Timestamp res = (Timestamp) ts.clone(); - int nanos = res.getNanos(); double tail = Math.pow(10, 9 - fsp); - if (serverRoundFracSecs) { - nanos = (int) Math.round(nanos / tail) * (int) tail; - if (nanos > 999999999) { - nanos %= 1000000000; // get only last 9 digits - res.setTime(res.getTime() + 1000); // increment seconds - } - } else { - nanos = (int) (nanos / tail) * (int) tail; + return (int) Math.round(nanos / tail) * (int) tail; } - res.setNanos(nanos); - - return res; + return (int) (nanos / tail) * (int) tail; } /** diff --git a/src/main/core-impl/java/com/mysql/cj/AbstractQueryBindings.java b/src/main/core-impl/java/com/mysql/cj/AbstractQueryBindings.java index f2c242dfa..e1159155a 100644 --- a/src/main/core-impl/java/com/mysql/cj/AbstractQueryBindings.java +++ b/src/main/core-impl/java/com/mysql/cj/AbstractQueryBindings.java @@ -36,7 +36,6 @@ import java.io.ObjectOutputStream; import java.math.BigDecimal; import java.math.BigInteger; -import java.sql.Date; import java.sql.Time; import java.sql.Timestamp; import java.text.ParsePosition; @@ -63,6 +62,9 @@ protected final static byte[] HEX_DIGITS = new byte[] { (byte) '0', (byte) '1', (byte) '2', (byte) '3', (byte) '4', (byte) '5', (byte) '6', (byte) '7', (byte) '8', (byte) '9', (byte) 'A', (byte) 'B', (byte) 'C', (byte) 'D', (byte) 'E', (byte) 'F' }; + protected final static LocalDate DEFAULT_DATE = LocalDate.of(1970, 1, 1); + protected final static LocalTime DEFAULT_TIME = LocalTime.of(0, 0); + protected Session session; /** Bind values for individual fields */ @@ -244,13 +246,13 @@ public void setObject(int parameterIndex, Object parameterObj) { setString(parameterIndex, parameterObj.toString()); } else if (parameterObj instanceof LocalDate) { - setDate(parameterIndex, Date.valueOf((LocalDate) parameterObj)); + setLocalDateTime(parameterIndex, LocalDateTime.of((LocalDate) parameterObj, DEFAULT_TIME), MysqlType.DATE); } else if (parameterObj instanceof LocalDateTime) { - setTimestamp(parameterIndex, Timestamp.valueOf((LocalDateTime) parameterObj)); + setLocalDateTime(parameterIndex, (LocalDateTime) parameterObj, MysqlType.DATETIME); } else if (parameterObj instanceof LocalTime) { - setTime(parameterIndex, Time.valueOf((LocalTime) parameterObj)); + setLocalDateTime(parameterIndex, LocalDateTime.of(DEFAULT_DATE, (LocalTime) parameterObj), MysqlType.TIME); } else { setSerializableObject(parameterIndex, parameterObj); @@ -284,14 +286,6 @@ public void setObject(int parameterIndex, Object parameterObj, MysqlType targetM if (parameterObj == null) { setNull(parameterIndex); } else { - if (parameterObj instanceof LocalDate) { - parameterObj = Date.valueOf((LocalDate) parameterObj); - } else if (parameterObj instanceof LocalDateTime) { - parameterObj = Timestamp.valueOf((LocalDateTime) parameterObj); - } else if (parameterObj instanceof LocalTime) { - parameterObj = Time.valueOf((LocalTime) parameterObj); - } - try { /* * From Table-B5 in the JDBC Spec @@ -375,7 +369,28 @@ public void setObject(int parameterIndex, Object parameterObj, MysqlType targetM case DATE: case DATETIME: case TIMESTAMP: + if (parameterObj instanceof LocalDate) { + setLocalDateTime(parameterIndex, LocalDateTime.of((LocalDate) parameterObj, DEFAULT_TIME), targetMysqlType); + break; + } else if (parameterObj instanceof LocalDateTime) { + setLocalDateTime(parameterIndex, ((LocalDateTime) parameterObj), targetMysqlType); + break; + } else if (parameterObj instanceof LocalTime) { + setLocalDateTime(parameterIndex, LocalDateTime.of(DEFAULT_DATE, (LocalTime) parameterObj), targetMysqlType); + break; + } + case YEAR: + if (parameterObj instanceof LocalDate) { + setNumericObject(parameterIndex, ((LocalDate) parameterObj).getYear(), targetMysqlType, scaleOrLength); + break; + } else if (parameterObj instanceof LocalDateTime) { + setNumericObject(parameterIndex, ((LocalDateTime) parameterObj).getYear(), targetMysqlType, scaleOrLength); + break; + } else if (parameterObj instanceof LocalTime) { + setNumericObject(parameterIndex, DEFAULT_DATE.getYear(), targetMysqlType, scaleOrLength); + break; + } java.util.Date parameterAsDate; @@ -428,6 +443,10 @@ public void setObject(int parameterIndex, Object parameterObj, MysqlType targetM } else if (parameterObj instanceof Timestamp) { Timestamp xT = (Timestamp) parameterObj; setTime(parameterIndex, new java.sql.Time(xT.getTime())); + } else if (parameterObj instanceof LocalDateTime) { + setLocalDateTime(parameterIndex, (LocalDateTime) parameterObj, MysqlType.TIME); + } else if (parameterObj instanceof LocalTime) { + setLocalDateTime(parameterIndex, LocalDateTime.of(DEFAULT_DATE, (LocalTime) parameterObj), MysqlType.TIME); } else { setTime(parameterIndex, (java.sql.Time) parameterObj); } @@ -672,4 +691,27 @@ public boolean isNull(int parameterIndex) { } } } + + protected LocalDateTime adjustNanos(LocalDateTime x, int parameterIndex) { + if (!this.sendFractionalSeconds.getValue() || !this.session.getServerSession().getCapabilities().serverSupportsFracSecs()) { + // truncate nanoseconds + return x.withNano(0); + } + + int fractLen = 6; // max supported length (i.e. microsecond) + if (this.columnDefinition != null && parameterIndex <= this.columnDefinition.getFields().length && parameterIndex >= 0) { + // use the column definition if available + fractLen = this.columnDefinition.getFields()[parameterIndex].getDecimals(); + } + int originalNano = x.getNano(); + int adjustedNano = TimeUtil.roundOrTruncateNanos(originalNano, fractLen, !this.session.getServerSession().isServerTruncatesFracSecs()); + if (adjustedNano != originalNano) { + if (adjustedNano > 999999999) { + adjustedNano %= 1000000000; + x = x.plusSeconds(1); + } + x = x.withNano(adjustedNano); + } + return x; + } } diff --git a/src/main/core-impl/java/com/mysql/cj/ClientPreparedQueryBindings.java b/src/main/core-impl/java/com/mysql/cj/ClientPreparedQueryBindings.java index b4bc448e9..fe1fcd8ab 100644 --- a/src/main/core-impl/java/com/mysql/cj/ClientPreparedQueryBindings.java +++ b/src/main/core-impl/java/com/mysql/cj/ClientPreparedQueryBindings.java @@ -47,6 +47,7 @@ import java.sql.Time; import java.sql.Timestamp; import java.text.SimpleDateFormat; +import java.time.LocalDateTime; import java.util.Calendar; import com.mysql.cj.conf.PropertyKey; @@ -440,6 +441,27 @@ public void setInt(int parameterIndex, int x) { setValue(parameterIndex, String.valueOf(x), MysqlType.INT); } + @Override + public void setLocalDateTime(int parameterIndex, LocalDateTime x, MysqlType targetMysqlType) { + if (targetMysqlType == MysqlType.DATE) { + setValue(parameterIndex, "'" + x.toLocalDate() + "'", MysqlType.DATE); + } else { + x = adjustNanos(x, parameterIndex); + + switch (targetMysqlType) { + case TIME: + setValue(parameterIndex, "'" + x.toLocalTime() + "'", targetMysqlType); + break; + case DATETIME: + case TIMESTAMP: + setValue(parameterIndex, "'" + x.toLocalDate() + " " + x.toLocalTime() + "'", targetMysqlType); + break; + default: + break; + } + } + } + @Override public void setLong(int parameterIndex, long x) { setValue(parameterIndex, String.valueOf(x), MysqlType.BIGINT); diff --git a/src/main/core-impl/java/com/mysql/cj/ServerPreparedQueryBindValue.java b/src/main/core-impl/java/com/mysql/cj/ServerPreparedQueryBindValue.java index 09cf833a8..546c35e0e 100644 --- a/src/main/core-impl/java/com/mysql/cj/ServerPreparedQueryBindValue.java +++ b/src/main/core-impl/java/com/mysql/cj/ServerPreparedQueryBindValue.java @@ -29,6 +29,7 @@ package com.mysql.cj; +import java.time.LocalDateTime; import java.util.Calendar; import java.util.Locale; import java.util.TimeZone; @@ -257,6 +258,12 @@ public void storeBinding(NativePacketPayload intoPacket, boolean isLoadDataQuery private void storeTime(NativePacketPayload intoPacket) { + if (this.value instanceof LocalDateTime) { + LocalDateTime ldt = (LocalDateTime) this.value; + storeLocalDateTime(intoPacket, 0, 0, 0, ldt.getHour(), ldt.getMinute(), ldt.getSecond(), ldt.getNano(), true); + return; + } + intoPacket.ensureCapacity(9); intoPacket.writeInteger(IntegerDataType.INT1, 8); // length intoPacket.writeInteger(IntegerDataType.INT1, 0); // neg flag @@ -278,6 +285,13 @@ private void storeTime(NativePacketPayload intoPacket) { */ private void storeDateTime(NativePacketPayload intoPacket) { synchronized (this) { + if (this.value instanceof LocalDateTime) { + LocalDateTime ldt = (LocalDateTime) this.value; + storeLocalDateTime(intoPacket, ldt.getYear(), ldt.getMonthValue(), ldt.getDayOfMonth(), ldt.getHour(), ldt.getMinute(), ldt.getSecond(), + ldt.getNano(), false); + return; + } + if (this.calendar == null) { this.calendar = Calendar.getInstance(this.defaultTimeZone, Locale.US); } @@ -296,7 +310,7 @@ private void storeDateTime(NativePacketPayload intoPacket) { length = (byte) 11; } - intoPacket.ensureCapacity(length); + intoPacket.ensureCapacity(length + 1); intoPacket.writeInteger(IntegerDataType.INT1, length); // length @@ -325,6 +339,27 @@ private void storeDateTime(NativePacketPayload intoPacket) { } } + private void storeLocalDateTime(NativePacketPayload intoPacket, int year, int month, int day, int hour, int minute, int second, int nano, + boolean prependNegFlag) { + byte length = (byte) (prependNegFlag ? 12 : 11); + intoPacket.ensureCapacity(length + 1); + intoPacket.writeInteger(IntegerDataType.INT1, length); // length + + if (prependNegFlag) { + intoPacket.writeInteger(IntegerDataType.INT1, 0); // neg flag + } + + intoPacket.writeInteger(IntegerDataType.INT2, year); + intoPacket.writeInteger(IntegerDataType.INT1, month); + intoPacket.writeInteger(IntegerDataType.INT1, day); + + intoPacket.writeInteger(IntegerDataType.INT1, hour); + intoPacket.writeInteger(IntegerDataType.INT1, minute); + intoPacket.writeInteger(IntegerDataType.INT1, second); + // microseconds + intoPacket.writeInteger(IntegerDataType.INT4, nano / 1000); + } + @Override public byte[] getByteValue() { if (!this.isStream) { diff --git a/src/main/core-impl/java/com/mysql/cj/ServerPreparedQueryBindings.java b/src/main/core-impl/java/com/mysql/cj/ServerPreparedQueryBindings.java index b027237b9..100e20e83 100644 --- a/src/main/core-impl/java/com/mysql/cj/ServerPreparedQueryBindings.java +++ b/src/main/core-impl/java/com/mysql/cj/ServerPreparedQueryBindings.java @@ -39,6 +39,7 @@ import java.sql.NClob; import java.sql.Time; import java.sql.Timestamp; +import java.time.LocalDateTime; import java.util.Calendar; import java.util.concurrent.atomic.AtomicBoolean; @@ -366,6 +367,25 @@ public void setInt(int parameterIndex, int x) { binding.parameterType = MysqlType.INT; } + @Override + public void setLocalDateTime(int parameterIndex, LocalDateTime x, MysqlType targetMysqlType) { + ServerPreparedQueryBindValue binding = getBinding(parameterIndex, false); + if (targetMysqlType == MysqlType.DATE) { + x = LocalDateTime.of(x.toLocalDate(), DEFAULT_TIME); + this.sendTypesToServer.compareAndSet(false, binding.resetToType(MysqlType.FIELD_TYPE_DATE, this.numberOfExecutions)); + } else { + x = adjustNanos(x, parameterIndex); + if (targetMysqlType == MysqlType.TIME) { + this.sendTypesToServer.compareAndSet(false, binding.resetToType(MysqlType.FIELD_TYPE_TIME, this.numberOfExecutions)); + } else { + // DATETIME or TIMESTAMP + this.sendTypesToServer.compareAndSet(false, binding.resetToType(MysqlType.FIELD_TYPE_DATETIME, this.numberOfExecutions)); + } + } + binding.parameterType = targetMysqlType; + binding.value = x; + } + @Override public void setLong(int parameterIndex, long x) { ServerPreparedQueryBindValue binding = getBinding(parameterIndex, false); diff --git a/src/test/java/testsuite/regression/StatementRegressionTest.java b/src/test/java/testsuite/regression/StatementRegressionTest.java index 9c91c2815..0098a47a8 100644 --- a/src/test/java/testsuite/regression/StatementRegressionTest.java +++ b/src/test/java/testsuite/regression/StatementRegressionTest.java @@ -68,6 +68,8 @@ import java.sql.Timestamp; import java.sql.Types; import java.text.SimpleDateFormat; +import java.time.LocalDateTime; +import java.time.LocalTime; import java.util.ArrayList; import java.util.Arrays; import java.util.Calendar; @@ -8943,12 +8945,22 @@ public void testBug77449() throws Exception { TimeUtil.getSimpleDateFormat(null, "yyyy-MM-dd HH:mm:ss.SSS", null, null).parse("2014-12-31 23:59:59.999").getTime()); Timestamp roundedTs = new Timestamp(originalTs.getTime() + 1); Timestamp truncatedTs = new Timestamp(originalTs.getTime() - 999); + LocalDateTime originalLdt = LocalDateTime.of(2014, 12, 31, 23, 59, 59, 999000000); + LocalDateTime roundedLdt = LocalDateTime.of(2015, 1, 1, 0, 0); + LocalDateTime truncatedLdt = LocalDateTime.of(2014, 12, 31, 23, 59, 59); + // MySQL can store 24:00:00, but LocalTime cannot. + // ResultSet#getObject(i, LocalTime.class) might need some adjustment. + LocalTime originalLt = LocalTime.of(22, 59, 59, 999000000); + LocalTime roundedLt = LocalTime.of(23, 0, 0); + LocalTime truncatedLt = LocalTime.of(22, 59, 59); assertEquals("2014-12-31 23:59:59.999", originalTs.toString()); assertEquals("2014-12-31 23:59:59.0", TimeUtil.truncateFractionalSeconds(originalTs).toString()); createTable("testBug77449", "(id INT PRIMARY KEY, ts_short TIMESTAMP, ts_long TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6))"); createProcedure("testBug77449", "(ts_short TIMESTAMP, ts_long TIMESTAMP(6)) BEGIN SELECT ts_short, ts_long; END"); + createTable("testBug77449_time", "(id INT PRIMARY KEY, t_short TIME, t_long TIME(6))"); + createProcedure("testBug77449_time", "(t_short TIME, t_long TIME(6)) BEGIN SELECT t_short, t_long; END"); for (int tst = 0; tst < 8; tst++) { boolean useLegacyDatetimeCode = (tst & 0x1) != 0; @@ -8991,6 +9003,27 @@ public void testBug77449() throws Exception { this.rs.updateTimestamp("ts_long", originalTs); this.rs.insertRow(); + // Send LocalDateTime using PreparedStatement -> truncation occurs according to 'sendFractionalSeconds' value. + testPStmt = testConn.prepareStatement("INSERT INTO testBug77449 VALUES (5, ?, ?)"); + testPStmt.setObject(1, originalLdt); + testPStmt.setObject(2, originalLdt); + assertEquals(testCase, 1, testPStmt.executeUpdate()); + testPStmt.close(); + + // Send LocalDateTime using UpdatableResultSet -> truncation occurs according to 'sendFractionalSeconds' value. + testStmt = testConn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE); + testStmt.executeUpdate("INSERT INTO testBug77449 VALUES (6, NOW(), NOW())/* no_ts_trunk */"); // insert dummy row + this.rs = testStmt.executeQuery("SELECT * FROM testBug77449 WHERE id = 6"); + assertTrue(testCase, this.rs.next()); + this.rs.updateObject("ts_short", originalLdt); + this.rs.updateObject("ts_long", originalLdt); + this.rs.updateRow(); + this.rs.moveToInsertRow(); + this.rs.updateInt("id", 7); + this.rs.updateObject("ts_short", originalLdt); + this.rs.updateObject("ts_long", originalLdt); + this.rs.insertRow(); + // Assert values from previous inserts/updates. // 1st row: from Statement sent as String, no subject to TZ conversions. this.rs = this.stmt.executeQuery("SELECT * FROM testBug77449 WHERE id = 1"); @@ -9006,9 +9039,46 @@ public void testBug77449() throws Exception { assertEquals(testCase, sendFractionalSeconds ? roundedTs : truncatedTs, this.rs.getTimestamp(2)); assertEquals(testCase, sendFractionalSeconds ? originalTs : truncatedTs, this.rs.getTimestamp(3)); } + // 5th row: from PreparedStatement; 6th row: from UpdatableResultSet.updateRow(); 7th row: from UpdatableResultSet.insertRow() + for (int i = 5; i <= 7; i++) { + assertTrue(testCase, this.rs.next()); + assertEquals(testCase, i, this.rs.getInt(1)); + assertEquals(testCase, sendFractionalSeconds ? roundedLdt : truncatedLdt, this.rs.getObject(2, LocalDateTime.class)); + assertEquals(testCase, sendFractionalSeconds ? originalLdt : truncatedLdt, this.rs.getObject(3, LocalDateTime.class)); + } this.stmt.execute("DELETE FROM testBug77449"); + // Send LocalTime using PreparedStatement -> truncation occurs according to 'sendFractionalSeconds' value. + testPStmt = testConn.prepareStatement("INSERT INTO testBug77449_time VALUES (1, ?, ?)"); + testPStmt.setObject(1, originalLt); + testPStmt.setObject(2, originalLt); + assertEquals(testCase, 1, testPStmt.executeUpdate()); + testPStmt.close(); + // Send LocalTime using UpdatableResultSet -> truncation occurs according to 'sendFractionalSeconds' value. + testStmt = testConn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE); + testStmt.executeUpdate("INSERT INTO testBug77449_time VALUES (2, NOW(), NOW())/* no_ts_trunk */"); // insert dummy row + this.rs = testStmt.executeQuery("SELECT * FROM testBug77449_time WHERE id = 2"); + assertTrue(testCase, this.rs.next()); + this.rs.updateObject("t_short", originalLt); + this.rs.updateObject("t_long", originalLt); + this.rs.updateRow(); + this.rs.moveToInsertRow(); + this.rs.updateInt("id", 3); + this.rs.updateObject("t_short", originalLt); + this.rs.updateObject("t_long", originalLt); + this.rs.insertRow(); + // Assert previous LocalTime insertions + this.rs = this.stmt.executeQuery("SELECT * FROM testBug77449_time"); + for (int i = 1; i <= 3; i++) { + assertTrue(testCase, this.rs.next()); + assertEquals(testCase, i, this.rs.getInt(1)); + assertEquals(testCase, sendFractionalSeconds ? roundedLt : truncatedLt, this.rs.getObject(2, LocalTime.class)); + assertEquals(testCase, sendFractionalSeconds ? originalLt : truncatedLt, this.rs.getObject(3, LocalTime.class)); + } + + this.stmt.execute("DELETE FROM testBug77449_time"); + // Compare Connector/J with client truncation -> truncation occurs according to 'sendFractionalSeconds' value. testPStmt = testConn.prepareStatement("SELECT ? = ?"); testPStmt.setTimestamp(1, originalTs); @@ -9021,6 +9091,30 @@ public void testBug77449() throws Exception { assertTrue(testCase, this.rs.getBoolean(1)); } testPStmt.close(); + // Same test with LocalDateTime + testPStmt = testConn.prepareStatement("SELECT ? = ?"); + testPStmt.setObject(1, originalLdt); + testPStmt.setObject(2, truncatedLdt); + this.rs = testPStmt.executeQuery(); + assertTrue(testCase, this.rs.next()); + if (sendFractionalSeconds) { + assertFalse(testCase, this.rs.getBoolean(1)); + } else { + assertTrue(testCase, this.rs.getBoolean(1)); + } + testPStmt.close(); + // Same test with LocalTime + testPStmt = testConn.prepareStatement("SELECT ? = ?"); + testPStmt.setObject(1, originalLt); + testPStmt.setObject(2, truncatedLt); + this.rs = testPStmt.executeQuery(); + assertTrue(testCase, this.rs.next()); + if (sendFractionalSeconds) { + assertFalse(testCase, this.rs.getBoolean(1)); + } else { + assertTrue(testCase, this.rs.getBoolean(1)); + } + testPStmt.close(); // Send timestamps using CallableStatement -> truncation occurs according to 'sendFractionalSeconds' value. CallableStatement cstmt = testConn.prepareCall("{call testBug77449(?, ?)}"); @@ -9031,6 +9125,27 @@ public void testBug77449() throws Exception { assertTrue(testCase, this.rs.next()); assertEquals(testCase, sendFractionalSeconds ? roundedTs : truncatedTs, this.rs.getTimestamp(1)); assertEquals(testCase, sendFractionalSeconds ? originalTs : truncatedTs, this.rs.getTimestamp(2)); + cstmt.close(); + // Same test with LocalDateTime + cstmt = testConn.prepareCall("{call testBug77449(?, ?)}"); + cstmt.setObject("ts_short", originalLdt); + cstmt.setObject("ts_long", originalLdt); + cstmt.execute(); + this.rs = cstmt.getResultSet(); + assertTrue(testCase, this.rs.next()); + assertEquals(testCase, sendFractionalSeconds ? roundedLdt : truncatedLdt, this.rs.getObject(1, LocalDateTime.class)); + assertEquals(testCase, sendFractionalSeconds ? originalLdt : truncatedLdt, this.rs.getObject(2, LocalDateTime.class)); + cstmt.close(); + // Same test with LocalTime + cstmt = testConn.prepareCall("{call testBug77449_time(?, ?)}"); + cstmt.setObject("t_short", originalLt); + cstmt.setObject("t_long", originalLt); + cstmt.execute(); + this.rs = cstmt.getResultSet(); + assertTrue(testCase, this.rs.next()); + assertEquals(testCase, sendFractionalSeconds ? roundedLt : truncatedLt, this.rs.getObject(1, LocalTime.class)); + assertEquals(testCase, sendFractionalSeconds ? originalLt : truncatedLt, this.rs.getObject(2, LocalTime.class)); + cstmt.close(); testConn.close(); } diff --git a/src/test/java/testsuite/simple/StatementsTest.java b/src/test/java/testsuite/simple/StatementsTest.java index 37ddf939a..49af602be 100644 --- a/src/test/java/testsuite/simple/StatementsTest.java +++ b/src/test/java/testsuite/simple/StatementsTest.java @@ -47,6 +47,7 @@ import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.sql.SQLFeatureNotSupportedException; +import java.sql.SQLType; import java.sql.Statement; import java.sql.Time; import java.sql.Timestamp; @@ -59,6 +60,7 @@ import java.time.OffsetTime; import java.time.ZoneOffset; import java.util.Properties; +import java.util.TimeZone; import java.util.concurrent.Callable; import com.mysql.cj.CharsetMapping; @@ -3705,4 +3707,96 @@ public Void call() throws Exception { } }); } + + public void testSetObjectLocalDateInDifferentTimezone() throws Exception { + Properties props = new Properties(); + for (int i = 0; i < 2; i++) { + setObjectInDifferentTimezone("DATE", LocalDate.of(2019, 12, 31), null, "2019-12-31", props); + setObjectInDifferentTimezone("DATE", LocalDate.of(2019, 12, 31), MysqlType.DATE, "2019-12-31", props); + setObjectInDifferentTimezone("DATETIME", LocalDate.of(2019, 12, 31), null, "2019-12-31T00:00:00", props); + setObjectInDifferentTimezone("DATETIME", LocalDate.of(2019, 12, 31), MysqlType.DATETIME, "2019-12-31T00:00:00", props); + setObjectInDifferentTimezone("TIMESTAMP", LocalDate.of(2019, 12, 31), null, "2019-12-31T00:00:00", props); + setObjectInDifferentTimezone("TIMESTAMP", LocalDate.of(2019, 12, 31), MysqlType.TIMESTAMP, "2019-12-31T00:00:00", props); + // setObjectInDifferentTimezone("YEAR", LocalDate.of(2019, 1, 1), null, "2019", props); + setObjectInDifferentTimezone("YEAR", LocalDate.of(2019, 1, 1), MysqlType.YEAR, "2019", props); + // setObjectInDifferentTimezone("TIME", LocalDate.of(2019, 12, 31), null, "00:00:00", props); + // setObjectInDifferentTimezone("TIME", LocalDate.of(2019, 12, 31), MysqlType.TIME, "00:00:00", props); + + props.put(PropertyKey.useServerPrepStmts.getKeyName(), "true"); + } + } + + public void testSetObjectLocalDateTimeInDifferentTimezone() throws Exception { + // truncation/rounding is tested in StatementRegressionTest#testBug77449 + Properties props = new Properties(); + for (int i = 0; i < 2; i++) { + setObjectInDifferentTimezone("DATETIME(6)", LocalDateTime.of(2019, 12, 31, 11, 22, 33, 123456000), null, "2019-12-31T11:22:33.123456", props); + setObjectInDifferentTimezone("DATETIME(6)", LocalDateTime.of(2019, 12, 31, 11, 22, 33, 123456000), MysqlType.DATETIME, "2019-12-31T11:22:33.123456", + props); + setObjectInDifferentTimezone("TIMESTAMP(6)", LocalDateTime.of(2019, 12, 31, 11, 22, 33, 123456000), null, "2019-12-31T11:22:33.123456", props); + setObjectInDifferentTimezone("TIMESTAMP(6)", LocalDateTime.of(2019, 12, 31, 11, 22, 33, 123456000), MysqlType.TIMESTAMP, + "2019-12-31T11:22:33.123456", props); + setObjectInDifferentTimezone("DATE", LocalDateTime.of(2019, 12, 31, 11, 22, 33, 123456000), null, "2019-12-31", props); + setObjectInDifferentTimezone("DATE", LocalDateTime.of(2019, 12, 31, 11, 22, 33, 123456000), MysqlType.DATE, "2019-12-31", props); + // setObjectInDifferentTimezone("YEAR", LocalDateTime.of(2019, 1, 1, 11, 22, 33, 123456000), null, "2019", props); + setObjectInDifferentTimezone("YEAR", LocalDateTime.of(2019, 1, 1, 11, 22, 33, 123456000), MysqlType.YEAR, "2019", props); + setObjectInDifferentTimezone("TIME(6)", LocalDateTime.of(2019, 12, 31, 11, 22, 33, 123456000), null, "11:22:33.123456", props); + setObjectInDifferentTimezone("TIME(6)", LocalDateTime.of(2019, 12, 31, 11, 22, 33, 123456000), MysqlType.TIME, "11:22:33.123456", props); + + props.put(PropertyKey.useServerPrepStmts.getKeyName(), "true"); + } + } + + public void testSetObjectLocalTimeInDifferentTimezone() throws Exception { + // truncation/rounding is tested in StatementRegressionTest#testBug77449 + Properties props = new Properties(); + for (int i = 0; i < 2; i++) { + setObjectInDifferentTimezone("TIME(6)", LocalTime.of(11, 22, 33, 123456000), null, "11:22:33.123456", props); + setObjectInDifferentTimezone("TIME(6)", LocalTime.of(11, 22, 33, 123456000), MysqlType.TIME, "11:22:33.123456", props); + // setObjectInDifferentTimezone("DATE", LocalTime.of(11, 22, 33, 123456000), null, "1970-01-01", props); + setObjectInDifferentTimezone("DATE", LocalTime.of(11, 22, 33, 123456000), MysqlType.DATE, "1970-01-01", props); + // setObjectInDifferentTimezone("DATETIME(6)", LocalTime.of(11, 22, 33, 123456000), null, "1970-01-01T11:22:33.123456", props); + setObjectInDifferentTimezone("DATETIME(6)", LocalTime.of(11, 22, 33, 123456000), MysqlType.TIMESTAMP, "1970-01-01T11:22:33.123456", props); + // setObjectInDifferentTimezone("TIMESTAMP(6)", LocalTime.of(11, 22, 33, 123456000), null, "1970-01-01T11:22:33.123456", props); + setObjectInDifferentTimezone("TIMESTAMP(6)", LocalTime.of(11, 22, 33, 123456000), MysqlType.TIMESTAMP, "1970-01-01T11:22:33.123456", props); + // setObjectInDifferentTimezone("YEAR", LocalTime.of(11, 22, 33, 123456000), null, "1970", props); + setObjectInDifferentTimezone("YEAR", LocalTime.of(11, 22, 33, 123456000), MysqlType.YEAR, "1970", props); + + props.put(PropertyKey.useServerPrepStmts.getKeyName(), "true"); + } + } + + private void setObjectInDifferentTimezone(String columnType, Object parameter, SQLType targetSqlType, String expectedValue, Properties props) + throws SQLException { + if (props == null) { + props = new Properties(); + } + final String tableName = "testSetObjectDateAndTime"; + final TimeZone origTz = TimeZone.getDefault(); + try { + TimeZone.setDefault(TimeZone.getTimeZone("GMT+23:50")); + createTable(tableName, "(id INT, d " + columnType + ")"); + try (Connection testConn = getConnectionWithProps(props)) { + try (PreparedStatement localPstmt = testConn.prepareStatement("INSERT INTO " + tableName + " VALUES (?, ?)")) { + localPstmt.setInt(1, 1); + if (targetSqlType == null) { + localPstmt.setObject(2, parameter); + } else { + localPstmt.setObject(2, parameter, targetSqlType); + } + assertEquals(1, localPstmt.executeUpdate()); + } + try (Statement localStmt = testConn.createStatement(); + ResultSet localRs = localStmt.executeQuery("SELECT COUNT(*) FROM " + tableName + " WHERE id = 1 AND d = '" + expectedValue + "'")) { + assertTrue(localRs.next()); + assertEquals( + String.format("column type: '%s', parameter: '%s', sqlType: '%s', properties: '%s'.", columnType, parameter, targetSqlType, props), + 1, localRs.getInt(1)); + } + } + dropTable(tableName); + } finally { + TimeZone.setDefault(origTz); + } + } }