You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by jh...@apache.org on 2014/06/29 19:33:35 UTC
git commit: [OPTIQ-316] SqlRun: Match output regardless of order if
ORDER BY not present
Repository: incubator-optiq
Updated Branches:
refs/heads/master 4f80e6424 -> 8a91027c1
[OPTIQ-316] SqlRun: Match output regardless of order if ORDER BY not present
Add EMPS table to SqlRun's "post" connection.
Project: http://git-wip-us.apache.org/repos/asf/incubator-optiq/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-optiq/commit/8a91027c
Tree: http://git-wip-us.apache.org/repos/asf/incubator-optiq/tree/8a91027c
Diff: http://git-wip-us.apache.org/repos/asf/incubator-optiq/diff/8a91027c
Branch: refs/heads/master
Commit: 8a91027c157e8c34ee1944faabb1353ec866d72a
Parents: 4f80e64
Author: Julian Hyde <ju...@gmail.com>
Authored: Sun Jun 29 10:24:48 2014 -0700
Committer: Julian Hyde <ju...@gmail.com>
Committed: Sun Jun 29 10:24:48 2014 -0700
----------------------------------------------------------------------
.../java/net/hydromatic/optiq/tools/SqlRun.java | 144 +++++++++++++++----
.../net/hydromatic/optiq/test/OptiqAssert.java | 11 ++
core/src/test/resources/sql/agg.oq | 16 +--
3 files changed, 132 insertions(+), 39 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/8a91027c/core/src/main/java/net/hydromatic/optiq/tools/SqlRun.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/net/hydromatic/optiq/tools/SqlRun.java b/core/src/main/java/net/hydromatic/optiq/tools/SqlRun.java
index 076f692..53f3bb2 100644
--- a/core/src/main/java/net/hydromatic/optiq/tools/SqlRun.java
+++ b/core/src/main/java/net/hydromatic/optiq/tools/SqlRun.java
@@ -19,7 +19,10 @@ package net.hydromatic.optiq.tools;
import net.hydromatic.optiq.prepare.OptiqPrepareImpl;
-import com.google.common.collect.ImmutableList;
+import org.eigenbase.util.Util;
+
+import com.google.common.base.Function;
+import com.google.common.collect.*;
import java.io.*;
import java.sql.*;
@@ -29,11 +32,22 @@ import java.util.*;
* Runs a SQL script.
*/
public class SqlRun {
+ private static final Ordering<String[]> ORDERING =
+ Ordering.natural().nullsLast().lexicographical().onResultOf(
+ new Function<String[], Iterable<Comparable>>() {
+ public Iterable<Comparable> apply(String[] input) {
+ return Arrays.<Comparable>asList(input);
+ }
+ });
+
private BufferedReader reader;
private Writer writer;
private PrintWriter printWriter;
private final Map<Property, Object> map = new HashMap<Property, Object>();
+ /** Result set from SQL statement just executed. */
private ResultSet resultSet;
+ /** Whether to sort result set before printing. */
+ private boolean sort;
private SQLException resultSetException;
private final List<String> lines = new ArrayList<String>();
private String pushedLine;
@@ -146,8 +160,9 @@ public class SqlRun {
/** Parser. */
private class Parser {
+ final List<Command> commands = new ArrayList<Command>();
+
Command parse() {
- List<Command> commands = new ArrayList<Command>();
for (;;) {
Command command;
try {
@@ -182,7 +197,8 @@ public class SqlRun {
return new UseCommand(lines, parts[1]);
}
if (line.startsWith("ok")) {
- return new CheckResultCommand(lines);
+ SqlCommand command = (SqlCommand) Util.last(commands);
+ return new CheckResultCommand(lines, command);
}
if (line.startsWith("skip")) {
return new SkipCommand(lines);
@@ -267,7 +283,9 @@ public class SqlRun {
enum OutputFormat {
CSV {
@Override
- public void format(ResultSet resultSet, SqlRun run) throws Exception {
+ public void format(ResultSet resultSet, List<String> headerLines,
+ List<String> bodyLines, List<String> footerLines, SqlRun run)
+ throws Exception {
final ResultSetMetaData metaData = resultSet.getMetaData();
final PrintWriter pw = run.printWriter;
final int n = metaData.getColumnCount();
@@ -299,8 +317,11 @@ public class SqlRun {
// (2 rows)
PSQL {
@Override
- public void format(ResultSet resultSet, SqlRun run) throws Exception {
- SqlRun.format(resultSet, run.printWriter, false);
+ public void format(ResultSet resultSet, List<String> headerLines,
+ List<String> bodyLines, List<String> footerLines, SqlRun run)
+ throws Exception {
+ SqlRun.format(resultSet, headerLines, bodyLines, footerLines, run.sort,
+ false);
}
},
@@ -315,17 +336,22 @@ public class SqlRun {
// (2 rows)
MYSQL {
@Override
- public void format(ResultSet resultSet, SqlRun run) throws Exception {
- SqlRun.format(resultSet, run.printWriter, true);
+ public void format(ResultSet resultSet, List<String> headerLines,
+ List<String> bodyLines, List<String> footerLines, SqlRun run)
+ throws Exception {
+ SqlRun.format(resultSet, headerLines, bodyLines, footerLines, run.sort,
+ true);
}
};
- public abstract void format(ResultSet resultSet, SqlRun run)
+ public abstract void format(ResultSet resultSet, List<String> headerLines,
+ List<String> bodyLines, List<String> footerLines, SqlRun run)
throws Exception;
}
- private static void format(ResultSet resultSet, PrintWriter pw, boolean b)
- throws SQLException {
+ private static void format(ResultSet resultSet, List<String> headerLines,
+ List<String> bodyLines, List<String> footerLines, boolean sort,
+ boolean mysql) throws SQLException {
final ResultSetMetaData metaData = resultSet.getMetaData();
final int n = metaData.getColumnCount();
final int[] widths = new int[n];
@@ -351,43 +377,57 @@ public class SqlRun {
}
}
+ if (sort) {
+ Collections.sort(rows, ORDERING);
+ }
+
// Compute "+-----+---+" (if b)
// or "-----+---" (if not b)
final StringBuilder buf = new StringBuilder();
for (int i = 0; i < n; i++) {
- buf.append(b || i > 0 ? "+" : "");
+ buf.append(mysql || i > 0 ? "+" : "");
buf.append(chars('-', widths[i] + 2));
}
- buf.append(b ? "+" : "");
- String hyphens = buf.toString();
+ buf.append(mysql ? "+" : "");
+ String hyphens = flush(buf);
- if (b) {
- pw.println(hyphens);
+ if (mysql) {
+ headerLines.add(hyphens);
}
// Print "| FOO | B |"
for (int i = 0; i < n; i++) {
- pw.print(i > 0 ? " | " : b ? "| " : " ");
- pw.print(pad(metaData.getColumnLabel(i + 1), widths[i], false));
+ buf.append(i > 0 ? " | " : mysql ? "| " : " ");
+ buf.append(pad(metaData.getColumnLabel(i + 1), widths[i], false));
}
- pw.println(b ? " |" : "");
- pw.println(hyphens);
+ buf.append(mysql ? " |" : "");
+ headerLines.add(flush(buf));
+ headerLines.add(hyphens);
for (String[] row : rows) {
for (int i = 0; i < n; i++) {
- pw.print(i > 0 ? " | " : b ? "| " : " ");
+ buf.append(i > 0 ? " | " : mysql ? "| " : " ");
// don't pad the last field if it is left-justified
- final String s = !b && i == n - 1 && !rights[i]
+ final String s = !mysql && i == n - 1 && !rights[i]
? row[i]
: pad(row[i], widths[i], rights[i]);
- pw.print(s);
+ buf.append(s);
}
- pw.println(b ? " |" : "");
+ buf.append(mysql ? " |" : "");
+ bodyLines.add(flush(buf));
}
- if (b) {
- pw.println(hyphens);
+ if (mysql) {
+ footerLines.add(hyphens);
}
- pw.println(rows.size() == 1 ? "(1 row)" : "(" + rows.size() + " rows)");
- pw.println();
+ footerLines.add(
+ rows.size() == 1 ? "(1 row)" : "(" + rows.size() + " rows)");
+ footerLines.add("");
+ }
+
+ /** Returns the contents of a StringBuilder and clears it for the next use. */
+ private static String flush(StringBuilder buf) {
+ final String s = buf.toString();
+ buf.setLength(0);
+ return s;
}
/** Command. */
@@ -439,15 +479,58 @@ public class SqlRun {
/** Command that executes a SQL statement and checks its result. */
class CheckResultCommand extends SimpleCommand {
- public CheckResultCommand(List<String> lines) {
+ private final SqlCommand sqlCommand;
+
+ public CheckResultCommand(List<String> lines, SqlCommand sqlCommand) {
super(lines);
+ this.sqlCommand = sqlCommand;
}
public void execute() throws Exception {
if (execute) {
OutputFormat format = (OutputFormat) map.get(Property.OUTPUTFORMAT);
if (resultSet != null) {
- format.format(resultSet, SqlRun.this);
+ final List<String> headerLines = new ArrayList<String>();
+ final List<String> bodyLines = new ArrayList<String>();
+ final List<String> footerLines = new ArrayList<String>();
+ format.format(resultSet, headerLines, bodyLines, footerLines,
+ SqlRun.this);
+
+ // Construct the original body.
+ // Strip the header and footer from the actual output.
+ // We assume that original and actual header have the same line count.
+ // Ditto footers.
+ final List<String> lines = new ArrayList<String>(sqlCommand.output);
+ for (String line : headerLines) {
+ if (!lines.isEmpty()) {
+ lines.remove(0);
+ }
+ }
+ for (String line : footerLines) {
+ if (!lines.isEmpty()) {
+ lines.remove(lines.size() - 1);
+ }
+ }
+
+ // Print the actual header.
+ for (String line : headerLines) {
+ printWriter.println(line);
+ }
+ // Print all lines that occurred in the actual output ("bodyLines"),
+ // but in their original order ("lines").
+ for (String line : lines) {
+ if (bodyLines.remove(line)) {
+ printWriter.println(line);
+ }
+ }
+ // Print lines that occurred in the actual output but not original.
+ for (String line : bodyLines) {
+ printWriter.println(line);
+ }
+ // Print the actual footer.
+ for (String line : footerLines) {
+ printWriter.println(line);
+ }
resultSet.close();
} else if (resultSetException != null) {
resultSetException.printStackTrace(printWriter);
@@ -487,6 +570,7 @@ public class SqlRun {
System.out.println("sql=" + sql);
}
resultSet = statement.executeQuery(sql);
+ sort = !sql.toUpperCase().contains("ORDER BY");
} catch (SQLException e) {
resultSetException = e;
}
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/8a91027c/core/src/test/java/net/hydromatic/optiq/test/OptiqAssert.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/net/hydromatic/optiq/test/OptiqAssert.java b/core/src/test/java/net/hydromatic/optiq/test/OptiqAssert.java
index cd85119..6ff5c76 100644
--- a/core/src/test/java/net/hydromatic/optiq/test/OptiqAssert.java
+++ b/core/src/test/java/net/hydromatic/optiq/test/OptiqAssert.java
@@ -526,6 +526,17 @@ public class OptiqAssert {
+ " (30, 'Engineering'),\n"
+ " (40, 'Empty')) as t(deptno, dname)",
ImmutableList.<String>of()));
+ post.add("EMPS",
+ ViewTable.viewMacro(
+ post,
+ "select * from (values\n"
+ + " (100, 'Fred', 10, CAST(NULL AS CHAR(1)), CAST(NULL AS VARCHAR(20)), 40, 25, TRUE, FALSE, DATE '1996-08-03'),\n"
+ + " (110, 'Eric', 20, 'M', 'San Francisco', 3, 80, UNKNOWN, FALSE, DATE '2001-01-01'),\n"
+ + " (110, 'John', 40, 'M', 'Vancouver', 2, CAST(NULL AS INT), FALSE, TRUE, DATE '2002-05-03'),\n"
+ + " (120, 'Wilma', 20, 'F', CAST(NULL AS VARCHAR(20)), 1, 5, UNKNOWN, TRUE, DATE '2005-09-07'),\n"
+ + " (130, 'Alice', 40, 'F', 'Vancouver', 2, CAST(NULL AS INT), FALSE, TRUE, DATE '2007-01-01'))\n"
+ + " as t(empno, name, deptno, gender, city, empid, age, slacker, manager, joinedat)",
+ ImmutableList.<String>of()));
}
if (schemaList.contains("metadata")) {
// always present
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/8a91027c/core/src/test/resources/sql/agg.oq
----------------------------------------------------------------------
diff --git a/core/src/test/resources/sql/agg.oq b/core/src/test/resources/sql/agg.oq
index 5597cb2..e9aef88 100644
--- a/core/src/test/resources/sql/agg.oq
+++ b/core/src/test/resources/sql/agg.oq
@@ -48,9 +48,7 @@ select count(deptno, ename, 1, deptno) as c from emp;
!ok
!}
-!use foodmart
-
-select city, gender as c from "emps";
+select city, gender as c from emps;
+---------------+---+
| CITY | C |
+---------------+---+
@@ -65,7 +63,7 @@ select city, gender as c from "emps";
!ok
# SELECT DISTINCT includes fully and partially null rows
-select distinct city, gender from "emps";
+select distinct city, gender from emps;
+---------------+--------+
| CITY | GENDER |
+---------------+--------+
@@ -80,7 +78,7 @@ select distinct city, gender from "emps";
!ok
# COUNT excludes fully or partially null rows
-select count(city, gender) as c from "emps";
+select count(city, gender) as c from emps;
+---+
| C |
+---+
@@ -91,7 +89,7 @@ select count(city, gender) as c from "emps";
!ok
# COUNT-DISTINCT excludes fully or partially null rows
-select count(distinct city, gender) as c from "emps";
+select count(distinct city, gender) as c from emps;
+---+
| C |
+---+
@@ -101,7 +99,7 @@ select count(distinct city, gender) as c from "emps";
!ok
-select distinct mod(deptno, 20) as m, gender as c from "emps";
+select distinct mod(deptno, 20) as m, gender as c from emps;
+----+---+
| M | C |
+----+---+
@@ -114,7 +112,7 @@ select distinct mod(deptno, 20) as m, gender as c from "emps";
!ok
# Partially null row (10, NULL) is excluded from count.
-select count(distinct mod(deptno, 20), gender) as c from "emps";
+select count(distinct mod(deptno, 20), gender) as c from emps;
+---+
| C |
+---+
@@ -124,7 +122,7 @@ select count(distinct mod(deptno, 20), gender) as c from "emps";
!ok
-select count(mod(deptno, 20), gender) as c from "emps";
+select count(mod(deptno, 20), gender) as c from emps;
+---+
| C |
+---+