Skip to content

Commit 659326f

Browse files
astefanastefan
authored
SQL: Add protocol tests and remove jdbc_type from drivers response (#37516)
1 parent 8932750 commit 659326f

13 files changed

Lines changed: 237 additions & 64 deletions

File tree

‎x-pack/plugin/sql/qa/single-node/src/test/java/org/elasticsearch/xpack/sql/qa/single_node/SqlProtocolIT.java‎

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,12 @@
1+
/*
2+
* Copyright Elasticsearch B.V. and/or licensed to Elasticsearch B.V. under one
3+
* or more contributor license agreements. Licensed under the Elastic License;
4+
* you may not use this file except in compliance with the Elastic License.
5+
*/
6+
7+
package org.elasticsearch.xpack.sql.qa.single_node;
8+
9+
import org.elasticsearch.xpack.sql.qa.SqlProtocolTestCase;
10+
11+
public class SqlProtocolIT extends SqlProtocolTestCase {
12+
}

‎x-pack/plugin/sql/qa/src/main/java/org/elasticsearch/xpack/sql/qa/SqlProtocolTestCase.java‎

Lines changed: 193 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,193 @@
1+
/*
2+
* Copyright Elasticsearch B.V. and/or licensed to Elasticsearch B.V. under one
3+
* or more contributor license agreements. Licensed under the Elastic License;
4+
* you may not use this file except in compliance with the Elastic License.
5+
*/
6+
7+
package org.elasticsearch.xpack.sql.qa;
8+
9+
import org.apache.http.entity.ContentType;
10+
import org.apache.http.entity.StringEntity;
11+
import org.elasticsearch.client.Request;
12+
import org.elasticsearch.client.RequestOptions;
13+
import org.elasticsearch.client.Response;
14+
import org.elasticsearch.common.xcontent.XContentHelper;
15+
import org.elasticsearch.common.xcontent.XContentType;
16+
import org.elasticsearch.common.xcontent.cbor.CborXContent;
17+
import org.elasticsearch.common.xcontent.json.JsonXContent;
18+
import org.elasticsearch.common.xcontent.smile.SmileXContent;
19+
import org.elasticsearch.common.xcontent.yaml.YamlXContent;
20+
import org.elasticsearch.test.rest.ESRestTestCase;
21+
import org.elasticsearch.xpack.sql.proto.Mode;
22+
23+
import java.io.IOException;
24+
import java.io.InputStream;
25+
import java.util.ArrayList;
26+
import java.util.HashMap;
27+
import java.util.List;
28+
import java.util.Locale;
29+
import java.util.Map;
30+
31+
import static org.elasticsearch.xpack.sql.proto.RequestInfo.CLIENT_IDS;
32+
import static org.elasticsearch.xpack.sql.qa.rest.RestSqlTestCase.mode;
33+
34+
public abstract class SqlProtocolTestCase extends ESRestTestCase {
35+
36+
public void testNulls() throws IOException {
37+
assertQuery("SELECT NULL", "NULL", "null", null, 0);
38+
}
39+
40+
public void testBooleanType() throws IOException {
41+
assertQuery("SELECT TRUE", "TRUE", "boolean", true, 1);
42+
assertQuery("SELECT FALSE", "FALSE", "boolean", false, 1);
43+
}
44+
45+
public void testNumericTypes() throws IOException {
46+
assertQuery("SELECT CAST(3 AS TINYINT)", "CAST(3 AS TINYINT)", "byte", 3, 5);
47+
assertQuery("SELECT CAST(-123 AS TINYINT)", "CAST(-123 AS TINYINT)", "byte", -123, 5);
48+
assertQuery("SELECT CAST(5 AS SMALLINT)", "CAST(5 AS SMALLINT)", "short", 5, 6);
49+
assertQuery("SELECT CAST(-25 AS SMALLINT)", "CAST(-25 AS SMALLINT)", "short", -25, 6);
50+
assertQuery("SELECT 123", "123", "integer", 123, 11);
51+
assertQuery("SELECT -2123", "-2123", "integer", -2123, 11);
52+
assertQuery("SELECT 1234567890123", "1234567890123", "long", 1234567890123L, 20);
53+
assertQuery("SELECT -1234567890123", "-1234567890123", "long", -1234567890123L, 20);
54+
assertQuery("SELECT 1234567890123.34", "1234567890123.34", "double", 1234567890123.34, 25);
55+
assertQuery("SELECT -1234567890123.34", "-1234567890123.34", "double", -1234567890123.34, 25);
56+
assertQuery("SELECT CAST(1234.34 AS REAL)", "CAST(1234.34 AS REAL)", "float", 1234.34f, 15);
57+
assertQuery("SELECT CAST(-1234.34 AS REAL)", "CAST(-1234.34 AS REAL)", "float", -1234.34f, 15);
58+
assertQuery("SELECT CAST(1234567890123.34 AS FLOAT)", "CAST(1234567890123.34 AS FLOAT)", "double", 1234567890123.34, 25);
59+
assertQuery("SELECT CAST(-1234567890123.34 AS FLOAT)", "CAST(-1234567890123.34 AS FLOAT)", "double", -1234567890123.34, 25);
60+
}
61+
62+
public void testTextualType() throws IOException {
63+
assertQuery("SELECT 'abc123'", "'abc123'", "keyword", "abc123", 0);
64+
}
65+
66+
public void testDateTimes() throws IOException {
67+
assertQuery("SELECT CAST('2019-01-14T12:29:25.000Z' AS DATE)", "CAST('2019-01-14T12:29:25.000Z' AS DATE)", "date",
68+
"2019-01-14T12:29:25.000Z", 24);
69+
assertQuery("SELECT CAST(-26853765751000 AS DATE)", "CAST(-26853765751000 AS DATE)", "date", "1119-01-15T12:37:29.000Z", 24);
70+
assertQuery("SELECT CAST(CAST('-26853765751000' AS BIGINT) AS DATE)", "CAST(CAST('-26853765751000' AS BIGINT) AS DATE)", "date",
71+
"1119-01-15T12:37:29.000Z", 24);
72+
}
73+
74+
public void testIPs() throws IOException {
75+
assertQuery("SELECT CAST('12.13.14.15' AS IP)", "CAST('12.13.14.15' AS IP)", "ip", "12.13.14.15", 0);
76+
assertQuery("SELECT CAST('2001:0db8:0000:0000:0000:ff00:0042:8329' AS IP)", "CAST('2001:0db8:0000:0000:0000:ff00:0042:8329' AS IP)",
77+
"ip", "2001:0db8:0000:0000:0000:ff00:0042:8329", 0);
78+
}
79+
80+
public void testDateTimeIntervals() throws IOException {
81+
assertQuery("SELECT INTERVAL '326' YEAR", "INTERVAL '326' YEAR", "interval_year", "P326Y", 7);
82+
assertQuery("SELECT INTERVAL '50' MONTH", "INTERVAL '50' MONTH", "interval_month", "P50M", 7);
83+
assertQuery("SELECT INTERVAL '520' DAY", "INTERVAL '520' DAY", "interval_day", "PT12480H", 23);
84+
assertQuery("SELECT INTERVAL '163' HOUR", "INTERVAL '163' HOUR", "interval_hour", "PT163H", 23);
85+
assertQuery("SELECT INTERVAL '163' MINUTE", "INTERVAL '163' MINUTE", "interval_minute", "PT2H43M", 23);
86+
assertQuery("SELECT INTERVAL '223.16' SECOND", "INTERVAL '223.16' SECOND", "interval_second", "PT3M43.016S", 23);
87+
assertQuery("SELECT INTERVAL '163-11' YEAR TO MONTH", "INTERVAL '163-11' YEAR TO MONTH", "interval_year_to_month", "P163Y11M", 7);
88+
assertQuery("SELECT INTERVAL '163 12' DAY TO HOUR", "INTERVAL '163 12' DAY TO HOUR", "interval_day_to_hour", "PT3924H", 23);
89+
assertQuery("SELECT INTERVAL '163 12:39' DAY TO MINUTE", "INTERVAL '163 12:39' DAY TO MINUTE", "interval_day_to_minute",
90+
"PT3924H39M", 23);
91+
assertQuery("SELECT INTERVAL '163 12:39:59.163' DAY TO SECOND", "INTERVAL '163 12:39:59.163' DAY TO SECOND",
92+
"interval_day_to_second", "PT3924H39M59.163S", 23);
93+
assertQuery("SELECT INTERVAL -'163 23:39:56.23' DAY TO SECOND", "INTERVAL -'163 23:39:56.23' DAY TO SECOND",
94+
"interval_day_to_second", "PT-3935H-39M-56.023S", 23);
95+
assertQuery("SELECT INTERVAL '163:39' HOUR TO MINUTE", "INTERVAL '163:39' HOUR TO MINUTE", "interval_hour_to_minute",
96+
"PT163H39M", 23);
97+
assertQuery("SELECT INTERVAL '163:39:59.163' HOUR TO SECOND", "INTERVAL '163:39:59.163' HOUR TO SECOND", "interval_hour_to_second",
98+
"PT163H39M59.163S", 23);
99+
assertQuery("SELECT INTERVAL '163:59.163' MINUTE TO SECOND", "INTERVAL '163:59.163' MINUTE TO SECOND", "interval_minute_to_second",
100+
"PT2H43M59.163S", 23);
101+
}
102+
103+
@SuppressWarnings({ "unchecked" })
104+
private void assertQuery(String sql, String columnName, String columnType, Object columnValue, int displaySize) throws IOException {
105+
for (Mode mode : Mode.values()) {
106+
Map<String, Object> response = runSql(mode.toString(), sql);
107+
List<Object> columns = (ArrayList<Object>) response.get("columns");
108+
assertEquals(1, columns.size());
109+
110+
Map<String, Object> column = (HashMap<String, Object>) columns.get(0);
111+
assertEquals(columnName, column.get("name"));
112+
assertEquals(columnType, column.get("type"));
113+
if (mode != Mode.PLAIN) {
114+
assertEquals(3, column.size());
115+
assertEquals(displaySize, column.get("display_size"));
116+
} else {
117+
assertEquals(2, column.size());
118+
}
119+
120+
List<Object> rows = (ArrayList<Object>) response.get("rows");
121+
assertEquals(1, rows.size());
122+
List<Object> row = (ArrayList<Object>) rows.get(0);
123+
assertEquals(1, row.size());
124+
125+
// from xcontent we can get float or double, depending on the conversion
126+
// method of the specific xcontent format implementation
127+
if (columnValue instanceof Float && row.get(0) instanceof Double) {
128+
assertEquals(columnValue, (float)((Number) row.get(0)).doubleValue());
129+
} else {
130+
assertEquals(columnValue, row.get(0));
131+
}
132+
}
133+
}
134+
135+
private Map<String, Object> runSql(String mode, String sql) throws IOException {
136+
Request request = new Request("POST", "/_sql");
137+
String requestContent = "{\"query\":\"" + sql + "\"" + mode(mode) + "}";
138+
String format = randomFrom(XContentType.values()).name().toLowerCase(Locale.ROOT);
139+
140+
// add a client_id to the request
141+
if (randomBoolean()) {
142+
String clientId = randomFrom(randomFrom(CLIENT_IDS), randomAlphaOfLengthBetween(10, 20));
143+
requestContent = new StringBuilder(requestContent)
144+
.insert(requestContent.length() - 1, ",\"client_id\":\"" + clientId + "\"").toString();
145+
}
146+
if (randomBoolean()) {
147+
request.addParameter("error_trace", "true");
148+
}
149+
if (randomBoolean()) {
150+
request.addParameter("pretty", "true");
151+
}
152+
if (!"json".equals(format) || randomBoolean()) {
153+
// since we default to JSON if a format is not specified, randomize setting it or not, explicitly;
154+
// for any other format, just set the format explicitly
155+
request.addParameter("format", format);
156+
}
157+
if (randomBoolean()) {
158+
// randomly use the Accept header for the response format
159+
RequestOptions.Builder options = request.getOptions().toBuilder();
160+
options.addHeader("Accept", randomFrom("*/*", "application/" + format));
161+
request.setOptions(options);
162+
}
163+
164+
// send the query either as body or as request parameter
165+
if (randomBoolean()) {
166+
request.setEntity(new StringEntity(requestContent, ContentType.APPLICATION_JSON));
167+
} else {
168+
request.setEntity(null);
169+
request.addParameter("source", requestContent);
170+
request.addParameter("source_content_type", ContentType.APPLICATION_JSON.getMimeType());
171+
RequestOptions.Builder options = request.getOptions().toBuilder();
172+
options.addHeader("Content-Type", "application/json");
173+
request.setOptions(options);
174+
}
175+
176+
Response response = client().performRequest(request);
177+
try (InputStream content = response.getEntity().getContent()) {
178+
switch(format) {
179+
case "cbor": {
180+
return XContentHelper.convertToMap(CborXContent.cborXContent, content, false);
181+
}
182+
case "yaml": {
183+
return XContentHelper.convertToMap(YamlXContent.yamlXContent, content, false);
184+
}
185+
case "smile": {
186+
return XContentHelper.convertToMap(SmileXContent.smileXContent, content, false);
187+
}
188+
default:
189+
return XContentHelper.convertToMap(JsonXContent.jsonXContent, content, false);
190+
}
191+
}
192+
}
193+
}

‎x-pack/plugin/sql/qa/src/main/java/org/elasticsearch/xpack/sql/qa/jdbc/JdbcTestUtils.java‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -117,7 +117,7 @@ public static void logLikeCLI(ResultSet rs, Logger logger) throws SQLException {
117117

118118
for (int i = 1; i <= columns; i++) {
119119
cols.add(new ColumnInfo(metaData.getTableName(i), metaData.getColumnName(i), metaData.getColumnTypeName(i),
120-
metaData.getColumnType(i), metaData.getColumnDisplaySize(i)));
120+
metaData.getColumnDisplaySize(i)));
121121
}
122122

123123

‎x-pack/plugin/sql/qa/src/main/java/org/elasticsearch/xpack/sql/qa/rest/RestSqlTestCase.java‎

Lines changed: 0 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -57,7 +57,6 @@ public static Map<String, Object> columnInfo(String mode, String name, String ty
5757
column.put("name", name);
5858
column.put("type", type);
5959
if ("jdbc".equals(mode)) {
60-
column.put("jdbc_type", jdbcType.getVendorTypeNumber());
6160
column.put("display_size", size);
6261
}
6362
return unmodifiableMap(column);

‎x-pack/plugin/sql/qa/src/main/resources/agg.csv-spec‎

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -98,6 +98,14 @@ SELECT CAST(SUM(salary) AS DOUBLE) FROM test_emp;
9898
4824855.0
9999
;
100100

101+
aggregateWithUpCastAsFloat
102+
SELECT CAST(SUM(salary) AS FLOAT) FROM test_emp;
103+
104+
CAST(SUM(salary) AS FLOAT)
105+
-----------------------------
106+
4824855.0
107+
;
108+
101109
aggregateWithCastNumericToString
102110
SELECT CAST(AVG(salary) AS VARCHAR) FROM test_emp;
103111

‎x-pack/plugin/sql/sql-action/src/main/java/org/elasticsearch/xpack/sql/action/SqlQueryResponse.java‎

Lines changed: 4 additions & 17 deletions
Original file line numberDiff line numberDiff line change
@@ -183,29 +183,16 @@ public static ColumnInfo readColumnInfo(StreamInput in) throws IOException {
183183
String table = in.readString();
184184
String name = in.readString();
185185
String esType = in.readString();
186-
Integer jdbcType;
187-
int displaySize;
188-
if (in.readBoolean()) {
189-
jdbcType = in.readVInt();
190-
displaySize = in.readVInt();
191-
} else {
192-
jdbcType = null;
193-
displaySize = 0;
194-
}
195-
return new ColumnInfo(table, name, esType, jdbcType, displaySize);
186+
Integer displaySize = in.readOptionalVInt();
187+
188+
return new ColumnInfo(table, name, esType, displaySize);
196189
}
197190

198191
public static void writeColumnInfo(StreamOutput out, ColumnInfo columnInfo) throws IOException {
199192
out.writeString(columnInfo.table());
200193
out.writeString(columnInfo.name());
201194
out.writeString(columnInfo.esType());
202-
if (columnInfo.jdbcType() != null) {
203-
out.writeBoolean(true);
204-
out.writeVInt(columnInfo.jdbcType());
205-
out.writeVInt(columnInfo.displaySize());
206-
} else {
207-
out.writeBoolean(false);
208-
}
195+
out.writeOptionalVInt(columnInfo.displaySize());
209196
}
210197

211198
@Override

‎x-pack/plugin/sql/sql-action/src/test/java/org/elasticsearch/xpack/sql/action/SqlQueryResponseTests.java‎

Lines changed: 1 addition & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -46,8 +46,7 @@ public static SqlQueryResponse createRandomInstance(String cursor, Mode mode) {
4646
if (randomBoolean()) {
4747
columns = new ArrayList<>(columnCount);
4848
for (int i = 0; i < columnCount; i++) {
49-
columns.add(new ColumnInfo(randomAlphaOfLength(10), randomAlphaOfLength(10), randomAlphaOfLength(10),
50-
randomInt(), randomInt(25)));
49+
columns.add(new ColumnInfo(randomAlphaOfLength(10), randomAlphaOfLength(10), randomAlphaOfLength(10), randomInt(25)));
5150
}
5251
}
5352

@@ -96,7 +95,6 @@ public void testToXContent() throws IOException {
9695
assertEquals(columnInfo.name(), columnMap.get("name"));
9796
assertEquals(columnInfo.esType(), columnMap.get("type"));
9897
assertEquals(columnInfo.displaySize(), columnMap.get("display_size"));
99-
assertEquals(columnInfo.jdbcType(), columnMap.get("jdbc_type"));
10098
}
10199
} else {
102100
assertNull(rootMap.get("columns"));

‎x-pack/plugin/sql/sql-cli/src/test/java/org/elasticsearch/xpack/sql/cli/command/ServerQueryCliCommandTests.java‎

Lines changed: 1 addition & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -12,7 +12,6 @@
1212
import org.elasticsearch.xpack.sql.proto.SqlQueryResponse;
1313

1414
import java.sql.SQLException;
15-
import java.sql.Types;
1615
import java.util.Collections;
1716
import java.util.List;
1817

@@ -109,7 +108,7 @@ private SqlQueryResponse fakeResponse(String cursor, boolean includeColumns, Str
109108
List<List<Object>> rows;
110109
List<ColumnInfo> columns;
111110
if (includeColumns) {
112-
columns = singletonList(new ColumnInfo("", "field", "string", Types.VARCHAR, 0));
111+
columns = singletonList(new ColumnInfo("", "field", "string", 0));
113112
} else {
114113
columns = null;
115114
}

0 commit comments

Comments
 (0)