Skip to content

Commit 2ce8347

Browse files
matrivmatriv
committed
SQL: Fix casting from date to numeric type to use millis (#37869)
Previously casting from a DATE[TIME] type to a numeric (DOUBLE, LONG, INT, etc. used seconds instead of the epoch millis. Fixes: #37655
1 parent e48982c commit 2ce8347

2 files changed

Lines changed: 137 additions & 53 deletions

File tree

‎x-pack/plugin/sql/src/main/java/org/elasticsearch/xpack/sql/type/DataTypeConversion.java‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -460,9 +460,9 @@ private static Function<Object, Object> fromString(Function<String, Object> conv
460460
private static Function<Object, Object> fromBool(Function<Boolean, Object> converter) {
461461
return (Object l) -> converter.apply(((Boolean) l));
462462
}
463-
463+
464464
private static Function<Object, Object> fromDate(Function<Long, Object> converter) {
465-
return l -> ((ReadableInstant) l).getMillis();
465+
return l -> converter.apply(((ReadableInstant) l).getMillis());
466466
}
467467

468468
private static Function<Object, Object> toDate(Conversion conversion) {

‎x-pack/plugin/sql/src/test/java/org/elasticsearch/xpack/sql/type/DataTypeConversionTests.java‎

Lines changed: 135 additions & 51 deletions
Original file line numberDiff line numberDiff line change
@@ -13,16 +13,36 @@
1313
import org.joda.time.DateTimeZone;
1414

1515
import static org.elasticsearch.xpack.sql.tree.Location.EMPTY;
16+
import static org.elasticsearch.xpack.sql.type.DataType.BOOLEAN;
17+
import static org.elasticsearch.xpack.sql.type.DataType.BYTE;
18+
import static org.elasticsearch.xpack.sql.type.DataType.DATE;
19+
import static org.elasticsearch.xpack.sql.type.DataType.DOUBLE;
20+
import static org.elasticsearch.xpack.sql.type.DataType.FLOAT;
21+
import static org.elasticsearch.xpack.sql.type.DataType.INTEGER;
22+
import static org.elasticsearch.xpack.sql.type.DataType.KEYWORD;
23+
import static org.elasticsearch.xpack.sql.type.DataType.LONG;
24+
import static org.elasticsearch.xpack.sql.type.DataType.NULL;
25+
import static org.elasticsearch.xpack.sql.type.DataType.SHORT;
26+
import static org.elasticsearch.xpack.sql.type.DataType.TEXT;
27+
import static org.elasticsearch.xpack.sql.type.DataType.UNSUPPORTED;
28+
import static org.elasticsearch.xpack.sql.type.DataTypeConversion.commonType;
29+
import static org.elasticsearch.xpack.sql.type.DataTypeConversion.conversionFor;
1630

1731
public class DataTypeConversionTests extends ESTestCase {
18-
public void testConversionToString() {
19-
Conversion conversion = DataTypeConversion.conversionFor(DataType.DOUBLE, DataType.KEYWORD);
20-
assertNull(conversion.convert(null));
21-
assertEquals("10.0", conversion.convert(10.0));
2232

23-
conversion = DataTypeConversion.conversionFor(DataType.DATE, DataType.KEYWORD);
24-
assertNull(conversion.convert(null));
25-
assertEquals("1970-01-01T00:00:00.000Z", conversion.convert(new DateTime(0, DateTimeZone.UTC)));
33+
public void testConversionToString() {
34+
DataType to = KEYWORD;
35+
{
36+
DataTypeConversion.Conversion conversion = conversionFor(DOUBLE, to);
37+
assertNull(conversion.convert(null));
38+
assertEquals("10.0", conversion.convert(10.0));
39+
}
40+
{
41+
DataTypeConversion.Conversion conversion = conversionFor(DATE, to);
42+
assertNull(conversion.convert(null));
43+
assertEquals("1973-11-29T21:33:09.101Z", conversion.convert(new DateTime(123456789101L, DateTimeZone.UTC)));
44+
assertEquals("1966-02-02T02:26:50.899Z", conversion.convert(new DateTime(-123456789101L, DateTimeZone.UTC)));
45+
}
2646
}
2747

2848
/**
@@ -51,12 +71,20 @@ public void testConversionToLong() {
5171
assertEquals(1L, conversion.convert(true));
5272
assertEquals(0L, conversion.convert(false));
5373
}
54-
Conversion conversion = DataTypeConversion.conversionFor(DataType.KEYWORD, to);
55-
assertNull(conversion.convert(null));
56-
assertEquals(1L, conversion.convert("1"));
57-
assertEquals(0L, conversion.convert("-0"));
58-
Exception e = expectThrows(SqlIllegalArgumentException.class, () -> conversion.convert("0xff"));
59-
assertEquals("cannot cast [0xff] to [Long]", e.getMessage());
74+
{
75+
Conversion conversion = conversionFor(DATE, to);
76+
assertNull(conversion.convert(null));
77+
assertEquals(123456789101L, conversion.convert(new DateTime(123456789101L, DateTimeZone.UTC)));
78+
assertEquals(-123456789101L, conversion.convert(new DateTime(-123456789101L, DateTimeZone.UTC)));
79+
}
80+
{
81+
Conversion conversion = conversionFor(KEYWORD, to);
82+
assertNull(conversion.convert(null));
83+
assertEquals(1L, conversion.convert("1"));
84+
assertEquals(0L, conversion.convert("-0"));
85+
Exception e = expectThrows(SqlIllegalArgumentException.class, () -> conversion.convert("0xff"));
86+
assertEquals("cannot cast [0xff] to [Long]", e.getMessage());
87+
}
6088
}
6189

6290
public void testConversionToDate() {
@@ -82,25 +110,36 @@ public void testConversionToDate() {
82110
assertEquals(new DateTime(1, DateTimeZone.UTC), conversion.convert(true));
83111
assertEquals(new DateTime(0, DateTimeZone.UTC), conversion.convert(false));
84112
}
85-
Conversion conversion = DataTypeConversion.conversionFor(DataType.KEYWORD, to);
86-
assertNull(conversion.convert(null));
113+
{
114+
Conversion conversion = conversionFor(DATE, to);
115+
assertNull(conversion.convert(null));
116+
assertEquals(new DateTime(123456789101L, DateTimeZone.UTC), conversion.convert(new DateTime(123456789101L, DateTimeZone.UTC)));
117+
assertEquals(new DateTime(-123456789101L, DateTimeZone.UTC),
118+
conversion.convert(new DateTime(-123456789101L, DateTimeZone.UTC)));
119+
}
120+
{
121+
Conversion conversion = conversionFor(KEYWORD, to);
122+
assertNull(conversion.convert(null));
87123

88-
assertEquals(new DateTime(1000L, DateTimeZone.UTC), conversion.convert("1970-01-01T00:00:01Z"));
89-
assertEquals(new DateTime(1483228800000L, DateTimeZone.UTC), conversion.convert("2017-01-01T00:00:00Z"));
90-
assertEquals(new DateTime(18000000L, DateTimeZone.UTC), conversion.convert("1970-01-01T00:00:00-05:00"));
91-
92-
// double check back and forth conversion
93-
DateTime dt = DateTime.now(DateTimeZone.UTC);
94-
Conversion forward = DataTypeConversion.conversionFor(DataType.DATE, DataType.KEYWORD);
95-
Conversion back = DataTypeConversion.conversionFor(DataType.KEYWORD, DataType.DATE);
96-
assertEquals(dt, back.convert(forward.convert(dt)));
97-
Exception e = expectThrows(SqlIllegalArgumentException.class, () -> conversion.convert("0xff"));
98-
assertEquals("cannot cast [0xff] to [Date]:Invalid format: \"0xff\" is malformed at \"xff\"", e.getMessage());
124+
assertEquals(new DateTime(1000L, DateTimeZone.UTC), conversion.convert("1970-01-01T00:00:01Z"));
125+
assertEquals(new DateTime(1483228800000L, DateTimeZone.UTC), conversion.convert("2017-01-01T00:00:00Z"));
126+
assertEquals(new DateTime(1483228800000L, DateTimeZone.UTC), conversion.convert("2017-01-01T00:00:00Z"));
127+
assertEquals(new DateTime(18000000L, DateTimeZone.UTC), conversion.convert("1970-01-01T00:00:00-05:00"));
128+
129+
// double check back and forth conversion
130+
DateTime dt = DateTime.now(DateTimeZone.UTC);
131+
Conversion forward = DataTypeConversion.conversionFor(DataType.DATE, DataType.KEYWORD);
132+
Conversion back = DataTypeConversion.conversionFor(DataType.KEYWORD, DataType.DATE);
133+
assertEquals(dt, back.convert(forward.convert(dt)));
134+
Exception e = expectThrows(SqlIllegalArgumentException.class, () -> conversion.convert("0xff"));
135+
assertEquals("cannot cast [0xff] to [Date]:Invalid format: \"0xff\" is malformed at \"xff\"", e.getMessage());
136+
}
99137
}
100138

101139
public void testConversionToDouble() {
140+
DataType to = DOUBLE;
102141
{
103-
Conversion conversion = DataTypeConversion.conversionFor(DataType.FLOAT, DataType.DOUBLE);
142+
Conversion conversion = conversionFor(FLOAT, to);
104143
assertNull(conversion.convert(null));
105144
assertEquals(10.0, (double) conversion.convert(10.0f), 0.00001);
106145
assertEquals(10.1, (double) conversion.convert(10.1f), 0.00001);
@@ -113,13 +152,19 @@ public void testConversionToDouble() {
113152
assertEquals(-134.0, (double) conversion.convert(-134), 0.00001);
114153
}
115154
{
116-
Conversion conversion = DataTypeConversion.conversionFor(DataType.BOOLEAN, DataType.DOUBLE);
155+
Conversion conversion = conversionFor(BOOLEAN, to);
117156
assertNull(conversion.convert(null));
118157
assertEquals(1.0, (double) conversion.convert(true), 0);
119158
assertEquals(0.0, (double) conversion.convert(false), 0);
120159
}
121160
{
122-
Conversion conversion = DataTypeConversion.conversionFor(DataType.KEYWORD, DataType.DOUBLE);
161+
Conversion conversion = conversionFor(DATE, to);
162+
assertNull(conversion.convert(null));
163+
assertEquals(1.23456789101E11, (double) conversion.convert(new DateTime(123456789101L, DateTimeZone.UTC)), 0);
164+
assertEquals(-1.23456789101E11, (double) conversion.convert(new DateTime(-123456789101L, DateTimeZone.UTC)), 0);
165+
}
166+
{
167+
Conversion conversion = conversionFor(KEYWORD, to);
123168
assertNull(conversion.convert(null));
124169
assertEquals(1.0, (double) conversion.convert("1"), 0);
125170
assertEquals(0.0, (double) conversion.convert("-0"), 0);
@@ -130,36 +175,44 @@ public void testConversionToDouble() {
130175
}
131176

132177
public void testConversionToBoolean() {
178+
DataType to = BOOLEAN;
133179
{
134-
Conversion conversion = DataTypeConversion.conversionFor(DataType.FLOAT, DataType.BOOLEAN);
180+
Conversion conversion = conversionFor(FLOAT, to);
135181
assertNull(conversion.convert(null));
136182
assertEquals(true, conversion.convert(10.0f));
137183
assertEquals(true, conversion.convert(-10.0f));
138184
assertEquals(false, conversion.convert(0.0f));
139185
}
140186
{
141-
Conversion conversion = DataTypeConversion.conversionFor(DataType.INTEGER, DataType.BOOLEAN);
187+
Conversion conversion = conversionFor(INTEGER, to);
142188
assertNull(conversion.convert(null));
143189
assertEquals(true, conversion.convert(10));
144190
assertEquals(true, conversion.convert(-10));
145191
assertEquals(false, conversion.convert(0));
146192
}
147193
{
148-
Conversion conversion = DataTypeConversion.conversionFor(DataType.LONG, DataType.BOOLEAN);
194+
Conversion conversion = conversionFor(LONG, to);
149195
assertNull(conversion.convert(null));
150196
assertEquals(true, conversion.convert(10L));
151197
assertEquals(true, conversion.convert(-10L));
152198
assertEquals(false, conversion.convert(0L));
153199
}
154200
{
155-
Conversion conversion = DataTypeConversion.conversionFor(DataType.DOUBLE, DataType.BOOLEAN);
201+
Conversion conversion = conversionFor(DOUBLE, to);
156202
assertNull(conversion.convert(null));
157203
assertEquals(true, conversion.convert(10.0d));
158204
assertEquals(true, conversion.convert(-10.0d));
159205
assertEquals(false, conversion.convert(0.0d));
160206
}
161207
{
162-
Conversion conversion = DataTypeConversion.conversionFor(DataType.KEYWORD, DataType.BOOLEAN);
208+
Conversion conversion = conversionFor(DATE, to);
209+
assertNull(conversion.convert(null));
210+
assertEquals(true, conversion.convert(new DateTime(123456789101L, DateTimeZone.UTC)));
211+
assertEquals(true, conversion.convert(new DateTime(-123456789101L, DateTimeZone.UTC)));
212+
assertEquals(false, conversion.convert(new DateTime(0L, DateTimeZone.UTC)));
213+
}
214+
{
215+
Conversion conversion = conversionFor(KEYWORD, to);
163216
assertNull(conversion.convert(null));
164217
// We only handled upper and lower case true and false
165218
assertEquals(true, conversion.convert("true"));
@@ -183,39 +236,70 @@ public void testConversionToBoolean() {
183236
}
184237

185238
public void testConversionToInt() {
239+
DataType to = INTEGER;
186240
{
187-
Conversion conversion = DataTypeConversion.conversionFor(DataType.DOUBLE, DataType.INTEGER);
241+
Conversion conversion = conversionFor(DOUBLE, to);
188242
assertNull(conversion.convert(null));
189243
assertEquals(10, conversion.convert(10.0));
190244
assertEquals(10, conversion.convert(10.1));
191245
assertEquals(11, conversion.convert(10.6));
192246
Exception e = expectThrows(SqlIllegalArgumentException.class, () -> conversion.convert(Long.MAX_VALUE));
193247
assertEquals("[" + Long.MAX_VALUE + "] out of [Int] range", e.getMessage());
194248
}
249+
{
250+
Conversion conversion = conversionFor(DATE, to);
251+
assertNull(conversion.convert(null));
252+
assertEquals(12345678, conversion.convert(new DateTime(12345678L, DateTimeZone.UTC)));
253+
assertEquals(223456789, conversion.convert(new DateTime(223456789L, DateTimeZone.UTC)));
254+
assertEquals(-123456789, conversion.convert(new DateTime(-123456789L, DateTimeZone.UTC)));
255+
Exception e = expectThrows(SqlIllegalArgumentException.class,
256+
() -> conversion.convert(new DateTime(Long.MAX_VALUE, DateTimeZone.UTC)));
257+
assertEquals("[" + Long.MAX_VALUE + "] out of [Int] range", e.getMessage());
258+
}
195259
}
196260

197261
public void testConversionToShort() {
262+
DataType to = SHORT;
198263
{
199-
Conversion conversion = DataTypeConversion.conversionFor(DataType.DOUBLE, DataType.SHORT);
264+
Conversion conversion = conversionFor(DOUBLE, to);
200265
assertNull(conversion.convert(null));
201266
assertEquals((short) 10, conversion.convert(10.0));
202267
assertEquals((short) 10, conversion.convert(10.1));
203268
assertEquals((short) 11, conversion.convert(10.6));
204269
Exception e = expectThrows(SqlIllegalArgumentException.class, () -> conversion.convert(Integer.MAX_VALUE));
205270
assertEquals("[" + Integer.MAX_VALUE + "] out of [Short] range", e.getMessage());
206271
}
272+
{
273+
Conversion conversion = conversionFor(DATE, to);
274+
assertNull(conversion.convert(null));
275+
assertEquals((short) 12345, conversion.convert(new DateTime(12345L, DateTimeZone.UTC)));
276+
assertEquals((short) -12345, conversion.convert(new DateTime(-12345L, DateTimeZone.UTC)));
277+
Exception e = expectThrows(SqlIllegalArgumentException.class,
278+
() -> conversion.convert(new DateTime(Integer.MAX_VALUE, DateTimeZone.UTC)));
279+
assertEquals("[" + Integer.MAX_VALUE + "] out of [Short] range", e.getMessage());
280+
}
207281
}
208282

209283
public void testConversionToByte() {
284+
DataType to = BYTE;
210285
{
211-
Conversion conversion = DataTypeConversion.conversionFor(DataType.DOUBLE, DataType.BYTE);
286+
Conversion conversion = conversionFor(DOUBLE, to);
212287
assertNull(conversion.convert(null));
213288
assertEquals((byte) 10, conversion.convert(10.0));
214289
assertEquals((byte) 10, conversion.convert(10.1));
215290
assertEquals((byte) 11, conversion.convert(10.6));
216291
Exception e = expectThrows(SqlIllegalArgumentException.class, () -> conversion.convert(Short.MAX_VALUE));
217292
assertEquals("[" + Short.MAX_VALUE + "] out of [Byte] range", e.getMessage());
218293
}
294+
{
295+
Conversion conversion = conversionFor(DATE, to);
296+
assertNull(conversion.convert(null));
297+
assertEquals((byte) 123, conversion.convert(new DateTime(123L, DateTimeZone.UTC)));
298+
assertEquals((byte) -123, conversion.convert(new DateTime(-123L, DateTimeZone.UTC)));
299+
Exception e = expectThrows(SqlIllegalArgumentException.class,
300+
() -> conversion.convert(new DateTime(Integer.MAX_VALUE, DateTimeZone.UTC)));
301+
assertEquals("[" + Integer.MAX_VALUE + "] out of [Byte] range", e.getMessage());
302+
}
219303
}
220304

221305
public void testConversionToNull() {
@@ -237,17 +321,17 @@ public void testConversionToIdentity() {
237321
}
238322

239323
public void testCommonType() {
240-
assertEquals(DataType.BOOLEAN, DataTypeConversion.commonType(DataType.BOOLEAN, DataType.NULL));
241-
assertEquals(DataType.BOOLEAN, DataTypeConversion.commonType(DataType.NULL, DataType.BOOLEAN));
242-
assertEquals(DataType.BOOLEAN, DataTypeConversion.commonType(DataType.BOOLEAN, DataType.BOOLEAN));
243-
assertEquals(DataType.NULL, DataTypeConversion.commonType(DataType.NULL, DataType.NULL));
244-
assertEquals(DataType.INTEGER, DataTypeConversion.commonType(DataType.INTEGER, DataType.KEYWORD));
245-
assertEquals(DataType.LONG, DataTypeConversion.commonType(DataType.TEXT, DataType.LONG));
246-
assertEquals(null, DataTypeConversion.commonType(DataType.TEXT, DataType.KEYWORD));
247-
assertEquals(DataType.SHORT, DataTypeConversion.commonType(DataType.SHORT, DataType.BYTE));
248-
assertEquals(DataType.FLOAT, DataTypeConversion.commonType(DataType.BYTE, DataType.FLOAT));
249-
assertEquals(DataType.FLOAT, DataTypeConversion.commonType(DataType.FLOAT, DataType.INTEGER));
250-
assertEquals(DataType.DOUBLE, DataTypeConversion.commonType(DataType.DOUBLE, DataType.FLOAT));
324+
assertEquals(BOOLEAN, commonType(BOOLEAN, NULL));
325+
assertEquals(BOOLEAN, commonType(NULL, BOOLEAN));
326+
assertEquals(BOOLEAN, commonType(BOOLEAN, BOOLEAN));
327+
assertEquals(NULL, commonType(NULL, NULL));
328+
assertEquals(INTEGER, commonType(INTEGER, KEYWORD));
329+
assertEquals(LONG, commonType(TEXT, LONG));
330+
assertNull(commonType(TEXT, KEYWORD));
331+
assertEquals(SHORT, commonType(SHORT, BYTE));
332+
assertEquals(FLOAT, commonType(BYTE, FLOAT));
333+
assertEquals(FLOAT, commonType(FLOAT, INTEGER));
334+
assertEquals(DOUBLE, commonType(DOUBLE, FLOAT));
251335
}
252336

253337
public void testEsDataTypes() {
@@ -257,9 +341,9 @@ public void testEsDataTypes() {
257341
}
258342

259343
public void testConversionToUnsupported() {
260-
Exception e = expectThrows(SqlIllegalArgumentException.class,
261-
() -> DataTypeConversion.conversionFor(DataType.INTEGER, DataType.UNSUPPORTED));
262-
assertEquals("cannot convert from [INTEGER] to [UNSUPPORTED]", e.getMessage());
344+
Exception e = expectThrows(SqlIllegalArgumentException.class,
345+
() -> conversionFor(INTEGER, UNSUPPORTED));
346+
assertEquals("cannot convert from [INTEGER] to [UNSUPPORTED]", e.getMessage());
263347
}
264348

265349
public void testStringToIp() {

0 commit comments

Comments
 (0)