Skip to content

Commit b3fd410

Browse files
mihaibudiumihaibudiu
committed
[SQL] Allow ROW field access without qualifying table
Signed-off-by: Mihai Budiu <mbudiu@feldera.com>
1 parent 0b33d7c commit b3fd410

9 files changed

Lines changed: 125 additions & 56 deletions

File tree

‎docs.feldera.com/docs/sql/types.md‎

Lines changed: 0 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -165,15 +165,6 @@ CREATE TABLE T(street VARCHAR, city VARCHAR, year INT);
165165
CREATE VIEW V AS SELECT address_typ(T.street, city, 'CA', 94087) as address, T.year as year FROM T;
166166
```
167167

168-
Tables can have structure-valued columns, but these have to be fully
169-
qualified using both the table name and the column name in programs:
170-
171-
```sql
172-
CREATE TABLE PERS(p0 employee_typ, p1 employee_typ);
173-
CREATE VIEW V AS SELECT PERS.p0.address FROM PERS
174-
WHERE PERS.p0.first_name = 'Mike'
175-
```
176-
177168
## Grammar for specifying types
178169

179170
```

‎sql-to-dbsp-compiler/SQL-compiler/src/main/java/org/dbsp/sqlCompiler/compiler/DBSPCompiler.java‎

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -781,6 +781,11 @@ private CompilationError improveErrorMessage(CalciteContextException e) {
781781
return new CompilationError(message, range);
782782
}
783783
}
784+
String newMessage = message.replace(":PEEK_NO_EXPAND", "");
785+
newMessage = newMessage.replace("RECORDTYPE", "ROW");
786+
if (!newMessage.equals(message)) {
787+
return new CompilationError(newMessage, getRange(e));
788+
}
784789
}
785790
return new CompilationError(e);
786791
}

‎sql-to-dbsp-compiler/SQL-compiler/src/main/java/org/dbsp/sqlCompiler/compiler/frontend/calciteCompiler/SqlToRelCompiler.java‎

Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -63,6 +63,7 @@
6363
import org.apache.calcite.rel.type.RelDataTypeSystemImpl;
6464
import org.apache.calcite.rel.type.RelProtoDataType;
6565
import org.apache.calcite.rel.type.RelRecordType;
66+
import org.apache.calcite.rel.type.StructKind;
6667
import org.apache.calcite.rex.RexBuilder;
6768
import org.apache.calcite.rex.RexCall;
6869
import org.apache.calcite.rex.RexInputRef;
@@ -128,6 +129,7 @@
128129
import org.apache.calcite.sql2rel.SqlToRelConverter;
129130
import org.apache.calcite.tools.RelBuilder;
130131
import org.apache.calcite.util.Litmus;
132+
import org.apache.calcite.util.Pair;
131133
import org.dbsp.generated.parser.DbspParserImpl;
132134
import org.dbsp.sqlCompiler.compiler.CompilerOptions;
133135
import org.dbsp.sqlCompiler.compiler.IErrorReporter;
@@ -462,6 +464,25 @@ public Charset getDefaultCharset() {
462464
return Charsets.UTF_8;
463465
}
464466

467+
@Override
468+
public RelDataType createStructType(
469+
final List<RelDataType> typeList,
470+
final List<String> fieldNameList) {
471+
return super.createStructType(StructKind.PEEK_FIELDS_NO_EXPAND, typeList, fieldNameList);
472+
}
473+
474+
@Override
475+
public RelDataType createStructType(
476+
final List<? extends Map.Entry<String, RelDataType>> fieldList) {
477+
return this.createStructType(Pair.right(fieldList), Pair.left(fieldList));
478+
}
479+
480+
@Override
481+
@SuppressWarnings("deprecation")
482+
public FieldInfoBuilder builder() {
483+
return new FieldInfoBuilder(this).kind(StructKind.PEEK_FIELDS_NO_EXPAND);
484+
}
485+
465486
@Override
466487
public RelDataType enforceTypeWithNullability(RelDataType type, boolean nullable) {
467488
if (type.isNullable() == nullable)

‎sql-to-dbsp-compiler/SQL-compiler/src/test/java/org/dbsp/sqlCompiler/compiler/sql/MultiCrateTests.java‎

Lines changed: 0 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -74,19 +74,6 @@ static void compileProgramToMultiCrate(String sql, boolean check)
7474
compileToMultiCrate(file.getAbsolutePath(), check);
7575
}
7676

77-
@Test @Ignore("These tests are slow")
78-
public void qaTests() throws IOException, SQLException, InterruptedException {
79-
for (File c : getQATests()) {
80-
String sql = Utilities.readFile(c.getPath());
81-
try {
82-
compileProgramToMultiCrate(sql, true);
83-
} catch (UnsupportedException ex) {
84-
// This is probably a file containing an ad-hoc query
85-
System.out.println(c.getName() + " skipped due to unsupported features.");
86-
}
87-
}
88-
}
89-
9077
@Test
9178
public void issue4049() throws SQLException, IOException, InterruptedException {
9279
String sql = """

‎sql-to-dbsp-compiler/SQL-compiler/src/test/java/org/dbsp/sqlCompiler/compiler/sql/QATests.java‎

Lines changed: 72 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -4,35 +4,103 @@
44
import org.dbsp.sqlCompiler.compiler.errors.CompilerMessages;
55
import org.dbsp.sqlCompiler.compiler.errors.UnsupportedException;
66
import org.dbsp.sqlCompiler.compiler.sql.tools.BaseSQLTests;
7+
import org.dbsp.util.Utilities;
78
import org.junit.Test;
89

910
import java.io.File;
11+
import java.io.IOException;
12+
import java.nio.file.Files;
1013
import java.sql.SQLException;
14+
import java.util.ArrayList;
15+
import java.util.Arrays;
16+
import java.util.List;
17+
import java.util.regex.Matcher;
18+
import java.util.regex.Pattern;
1119

1220
/** Compile the SQL programs in the feldera-qa repository, and rust-compile the results. */
1321
public class QATests {
14-
static void compileAndCheck(File file) throws SQLException {
22+
// Some workload files have connectors generated by python scripts
23+
// Use a regex to replace them with an empty array
24+
// Ex:
25+
// 'connectors' = '{input_connectors}'
26+
// is replaced by
27+
// 'connectors' = '[]'
28+
// and
29+
// ) WITH ('connectors' = '{orders1_connectors}');
30+
// is replaced by
31+
// ) WITH ('connectors' = '[]');
32+
static File replaceConnectors(File source) throws IOException {
33+
Pattern p = Pattern.compile("^(.*)'connectors' = '\\{(.*)_connectors\\}'(.*)$");
34+
35+
List<String> strings = Files.readAllLines(source.toPath());
36+
for (int i = 0; i < strings.size(); i++) {
37+
var s = strings.get(i);
38+
Matcher m = p.matcher(s);
39+
if (m.matches()) {
40+
final String repl = m.group(1) + "'connectors' = '[]'" + m.group(3);
41+
strings.set(i, repl);
42+
}
43+
}
44+
File file = BaseSQLTests.createInputScript(String.join("\n", strings));
45+
file.deleteOnExit();
46+
return file;
47+
}
48+
49+
static void compileAndCheck(File file) throws SQLException, IOException {
50+
File input = replaceConnectors(file);
1551
CompilerMessages messages = CompilerMain.execute(
1652
"-i", "--alltables", "--ignoreOrder",
17-
"-o", BaseSQLTests.TEST_FILE_PATH, file.getAbsolutePath());
53+
"-o", BaseSQLTests.TEST_FILE_PATH, input.getAbsolutePath());
1854
if (messages.errorCount() > 0) {
1955
messages.print();
2056
throw new RuntimeException("Error during compilation");
2157
}
58+
Utilities.deleteFile(input, false);
2259
// BaseSQLTests.compileAndCheckRust(true);
2360
}
2461

62+
/** Enumerate the SQL files in the QA repository.
63+
* The assumption is that this repository has been checked out in parallel with feldera. */
64+
static List<File> getQATests() {
65+
List<File> result = new ArrayList<>();
66+
String dir = "../../../feldera-qa";
67+
String[] subdirs = new String[] { "new/workloads", "." };
68+
for (var subdir : subdirs) {
69+
File file = new File(dir + "/" + subdir);
70+
if (file.exists()) {
71+
File[] directories = file.listFiles();
72+
if (directories == null)
73+
return result;
74+
Arrays.sort(directories);
75+
for (File d : directories) {
76+
File[] files = d.listFiles();
77+
if (files == null)
78+
continue;
79+
for (File c : files) {
80+
// The following eliminate some fda scripts
81+
if (c.getName().contains("adhoc")) continue;
82+
if (c.getName().matches("query.*view.sql")) continue;
83+
if (c.getName().endsWith(".sql")) {
84+
result.add(c);
85+
}
86+
}
87+
}
88+
}
89+
}
90+
return result;
91+
}
92+
2593
@Test
2694
public void qaTests() throws SQLException {
2795
// BaseSQLTests.showPlan();
28-
for (File c : BaseSQLTests.getQATests()) {
96+
for (File c : getQATests()) {
2997
// This program cannot be compiled because it contains a udf
3098
if (c.toString().matches(".*swiss.*-q1.*")) continue;
3199
// if (!c.toString().contains("x.sql")) continue;
32100
System.out.println("Compiling " + c);
33101
try {
34102
compileAndCheck(c);
35-
} catch (UnsupportedException ex) {
103+
} catch (UnsupportedException | IOException ex) {
36104
// This is probably a file containing an ad-hoc query
37105
System.out.println(c.getName() + " skipped due to unsupported features.");
38106
}

‎sql-to-dbsp-compiler/SQL-compiler/src/test/java/org/dbsp/sqlCompiler/compiler/sql/simple/Regression2Tests.java‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -281,7 +281,7 @@ public void issue5637() {
281281
this.statementsFailingInCompilation("""
282282
CREATE MATERIALIZED VIEW v AS SELECT
283283
(DATE '2020-06-21', DATE '2020-06-21' + INTERVAL '1' YEAR) CONTAINS TIME '12:00:00' AS res;""",
284-
"Cannot apply 'CONTAINS' to arguments of type '<RECORDTYPE(DATE EXPR$0, DATE EXPR$1)> CONTAINS <TIME(0)>'");
284+
"Cannot apply 'CONTAINS' to arguments of type '<ROW(DATE EXPR$0, DATE EXPR$1)> CONTAINS <TIME(0)>'");
285285
}
286286

287287
@Test

‎sql-to-dbsp-compiler/SQL-compiler/src/test/java/org/dbsp/sqlCompiler/compiler/sql/simple/Regression3Tests.java‎

Lines changed: 25 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -637,4 +637,29 @@ public void issue6352() {
637637
this.qf("SELECT CAST('blah' AS BOOLEAN)",
638638
"Cannot convert string 'blah' to BOOLEAN");
639639
}
640+
641+
@Test
642+
public void issue3636() {
643+
this.getCC("""
644+
CREATE TABLE T (
645+
id INT,
646+
col ROW(field1 VARCHAR, field2 INT)
647+
);
648+
CREATE VIEW W AS SELECT col.field2 FROM T;""");
649+
650+
this.getCC("""
651+
CREATE TABLE t (
652+
id VARCHAR,
653+
r ROW(b VARCHAR)
654+
);
655+
CREATE VIEW V AS SELECT id, t.r.b, r.b FROM t;""");
656+
657+
this.getCC("""
658+
CREATE TABLE T(
659+
b VARCHAR,
660+
r ROW (b VARCHAR)
661+
);
662+
663+
CREATE VIEW Z AS SELECT b, r.b, t.b FROM T;""");
664+
}
640665
}

‎sql-to-dbsp-compiler/SQL-compiler/src/test/java/org/dbsp/sqlCompiler/compiler/sql/tools/BaseSQLTests.java‎

Lines changed: 0 additions & 28 deletions
Original file line numberDiff line numberDiff line change
@@ -258,34 +258,6 @@ public static File createInputFile(File file, String contents) throws IOExceptio
258258
return file;
259259
}
260260

261-
/** Enumerate the SQL files in the QA repository.
262-
* The assumption is that this repository has been checked out in parallel with feldera. */
263-
public static List<File> getQATests() {
264-
List<File> result = new ArrayList<>();
265-
String dir = "../../../feldera-qa";
266-
File file = new File(dir);
267-
if (file.exists()) {
268-
File[] directories = file.listFiles();
269-
if (directories == null)
270-
return result;
271-
Arrays.sort(directories);
272-
for (File d: directories) {
273-
File[] files = d.listFiles();
274-
if (files == null)
275-
continue;
276-
for (File c: files) {
277-
// The following eliminate some fda scripts
278-
if (c.getName().contains("adhoc")) continue;
279-
if (c.getName().matches("query.*view.sql")) continue;
280-
if (c.getName().endsWith(".sql")) {
281-
result.add(c);
282-
}
283-
}
284-
}
285-
}
286-
return result;
287-
}
288-
289261
public static File createInputScript(String contents) throws IOException {
290262
File result = File.createTempFile("script", ".sql", new File(RUST_DIRECTORY));
291263
return createInputFile(result, contents);

‎sql-to-dbsp-compiler/calcite_version.env‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -3,5 +3,5 @@ CALCITE_REPO="https://github.com/apache/calcite.git"
33
#CALCITE_REPO="https://github.com/mihaibudiu/calcite"
44
CALCITE_BRANCH="main"
55
CALCITE_CURRENT="1.42.0"
6-
CALCITE_NEXT_COMMIT="00251024abe97eae08ec40d158d3d7da1b3a989f"
6+
CALCITE_NEXT_COMMIT="fda2874d73250c373f1e74b445547f1c0b0debd8"
77
CALCITE_NEXT="1.43.0"

0 commit comments

Comments
 (0)