mirror of
https://github.com/featurebasedb/featurebase.git
synced 2026-08-28 10:54:59 +00:00
b7e9879526
This forward-ports a number of tests from the previous SQL implementation. The porting is approximate in a number of ways, and not all tests are implemented/tested yet. In particular, several tests are currently disabled because we don't support `limit n` constructs. The tests that were primarily tests of the parser have been brought forward as parser tests. One of them has been altered to add parentheses, because our parser interprets fld1 between 1 and 3 and fld2 = 2 as: fld1 between (1 and 3) and (fld2 = 2) which is invalid, while the old parser apparently interpreted it as: (fld1 between 1 and 3) and (fld2 = 2) We have not yet verified the SQL spec's requirements here, but sqlite agrees with our old parser, not our new parser, so this may be a regression. The old tests expected an INNER JOIN to suppress duplicate values. Our new code does not, which is consistent with other SQL implementations. This is a change, but the old behavior appears to have been wrong. (You can still suppress duplicate values by specifying DISTINCT.) In the previous implementations, a value like `count(*)` had `count(*)` as its column name. In the new implementation, it has an empty string as its column name. Related to this, the prior implementation allowed you to write select age, count(*) from grouper group by age having count > 1 but the new implementationt requires that to be spelled as having count(*) > 1 This is consistent with other SQL implementations, so I think the new behavior is correct. The behavior of SHOW COLUMNS and SHOW TABLES has changed, in that the specific results returned are significantly different. Perhaps more significantly, the old system spelled the former query as SHOW FIELDS, rather than SHOW COLUMNS. This may be considered a regression, in that `SHOW FIELDS` no longer works, and we should consider whether any hypothetical users might have been relying on the output of either of these. (I hope not, the new output is much better.) Some of the old tests (the ones in handler_test) were accommodated by adding a couple of specific test cases to existing tests, specifically: * handling timestamp values with `Z` rather than `+00:00` * a join with a WHERE clause referring to fields in both source tables We introduce a new "partial" comparison type, because there's no way for a test of `SHOW TABLES` to contain a correct table row, because `SHOW TABLES` includes timestamps from when tables were created. I'm not sure this is the right way to do this. We add corresponding changes to dax_test, because the DAX tree tests against the SQL tests. We change the returned types of field names and field types to plain strings, ironically because DAX needs this -- the test code in the DAX tree is getting them back as plain strings, rather than as dax.FieldName and dax.BaseType. The tests using `having` are commented out because they don't seem to be working, a ticket has been filed for this. Two of the tests that should return strings are instead returning untranslated integer IDs, but only for DAX, not for the regular SQL tests, and the `delete` test has been commented out for DAX-specific errors. If we merge this, the next step is to ticket those and address them separately.
559 lines
17 KiB
Go
559 lines
17 KiB
Go
package defs
|
|
|
|
import (
|
|
"fmt"
|
|
"time"
|
|
)
|
|
|
|
var sql1TestsGrouper = TableTest{
|
|
name: "sql1testsgrouper",
|
|
Table: tbl(
|
|
"grouper",
|
|
srcHdrs(
|
|
srcHdr("_id", fldTypeID),
|
|
srcHdr("color", fldTypeString),
|
|
srcHdr("score", fldTypeInt, "min -1000", "max 1000"),
|
|
srcHdr("age", fldTypeInt, "min 0", "max 100"),
|
|
srcHdr("height", fldTypeInt, "min 0", "max 1000"),
|
|
srcHdr("timestamp", fldTypeTimestamp),
|
|
),
|
|
srcRows(
|
|
srcRow(int64(1), string("blue"), int64(-10), int64(27), int64(20), string("2011-04-02T12:32:00Z")),
|
|
srcRow(int64(2), string("blue"), int64(-8), int64(16), int64(30), string("2011-01-02T12:32:00Z")),
|
|
srcRow(int64(3), string("red"), int64(6), int64(19), int64(40), string("2012-01-02T12:32:00Z")),
|
|
srcRow(int64(4), string("green"), int64(0), int64(27), int64(50), string("2013-09-02T12:32:00Z")),
|
|
srcRow(int64(5), string("blue"), int64(-2), int64(16), int64(60), string("2014-01-02T12:32:00Z")),
|
|
srcRow(int64(6), string("blue"), int64(100), int64(34), int64(70), string("2010-05-02T12:32:00Z")),
|
|
srcRow(int64(7), string("blue"), int64(0), int64(27), int64(80), string("2016-08-02T12:32:00Z")),
|
|
srcRow(int64(8), nil, int64(-13), int64(16), int64(90), string("2020-01-02T12:32:00Z")),
|
|
srcRow(int64(9), string("red"), int64(80), int64(16), int64(100), string("2000-03-02T12:32:00Z")),
|
|
srcRow(int64(10), string("red"), int64(-2), int64(31), int64(110), string("2018-01-02T12:32:00Z")),
|
|
),
|
|
),
|
|
SQLTests: nil,
|
|
}
|
|
|
|
var sql1TestsJoiner = TableTest{
|
|
name: "sql1testsjoiner",
|
|
Table: tbl(
|
|
"joiner",
|
|
srcHdrs(
|
|
srcHdr("_id", fldTypeID),
|
|
srcHdr("grouperid", fldTypeInt, "min 0", "max 1000"),
|
|
srcHdr("jointype", fldTypeInt, "min -1000", "max 1000"),
|
|
),
|
|
srcRows(
|
|
srcRow(int64(1), int64(1), int64(1)),
|
|
srcRow(int64(2), int64(2), int64(1)),
|
|
srcRow(int64(3), int64(5), int64(1)),
|
|
srcRow(int64(4), int64(6), int64(1)),
|
|
srcRow(int64(5), int64(7), int64(1)),
|
|
srcRow(int64(6), int64(3), int64(2)),
|
|
srcRow(int64(7), int64(8), int64(2)),
|
|
srcRow(int64(8), int64(9), int64(2)),
|
|
srcRow(int64(9), int64(1), int64(3)),
|
|
srcRow(int64(10), int64(2), int64(3)),
|
|
),
|
|
),
|
|
SQLTests: nil,
|
|
}
|
|
|
|
var sql1TestsDelete = TableTest{
|
|
name: "sql1testsdelete",
|
|
Table: tbl(
|
|
"delete_me",
|
|
srcHdrs(
|
|
srcHdr("_id", fldTypeID),
|
|
srcHdr("unused", fldTypeInt),
|
|
),
|
|
srcRows(srcRow(int64(1), int64(1))),
|
|
),
|
|
SQLTests: nil,
|
|
}
|
|
|
|
// grouperTimeX extracts the time associated with record ID x.
|
|
// note that the record IDs are 1..10, not 0..9, so we subtract one.
|
|
func grouperTimeX(x int) time.Time {
|
|
t, err := time.ParseInLocation(time.RFC3339, sql1TestsGrouper.Table.rows[0][x-1][5].(string), time.UTC)
|
|
if err != nil {
|
|
panic(fmt.Sprintf("failed to parse time for id %d", x))
|
|
}
|
|
return t
|
|
}
|
|
|
|
var sql1TestsQueries = TableTest{
|
|
name: "sql1testsqueries",
|
|
SQLTests: []SQLTest{
|
|
{
|
|
// Extract(Limit(All(), limit=100, offset=0),Rows(age))
|
|
SQLs: sqls("select age from grouper;"),
|
|
ExpHdrs: hdrs(
|
|
hdr("age", fldTypeInt),
|
|
),
|
|
ExpRows: rows(
|
|
row(int64(27)),
|
|
row(int64(16)),
|
|
row(int64(19)),
|
|
row(int64(27)),
|
|
row(int64(16)),
|
|
row(int64(34)),
|
|
row(int64(27)),
|
|
row(int64(16)),
|
|
row(int64(16)),
|
|
row(int64(31)),
|
|
),
|
|
Compare: CompareExactOrdered,
|
|
},
|
|
{
|
|
// Extract(Limit(ConstRow(columns=[2]), limit=100, offset=0),Rows(age),Rows(color),Rows(height),Rows(score))
|
|
SQLs: sqls("select * from grouper where _id=2;"),
|
|
ExpHdrs: hdrs(
|
|
hdr("_id", fldTypeID),
|
|
hdr("age", fldTypeInt),
|
|
hdr("color", fldTypeString),
|
|
hdr("height", fldTypeInt),
|
|
hdr("score", fldTypeInt),
|
|
hdr("timestamp", fldTypeTimestamp),
|
|
),
|
|
ExpRows: rows(
|
|
row(int64(2), int64(16), string("blue"), int64(30), int64(-8), grouperTimeX(2)),
|
|
),
|
|
Compare: CompareExactOrdered,
|
|
},
|
|
{
|
|
// Extract(Limit(ConstRow(columns=[2]), limit=100, offset=0),Rows(age),Rows(color),Rows(height),Rows(score))
|
|
SQLs: sqls("select * from grouper;"),
|
|
ExpHdrs: hdrs(
|
|
hdr("_id", fldTypeID),
|
|
hdr("age", fldTypeInt),
|
|
hdr("color", fldTypeString),
|
|
hdr("height", fldTypeInt),
|
|
hdr("score", fldTypeInt),
|
|
hdr("timestamp", fldTypeTimestamp),
|
|
),
|
|
ExpRows: rows(
|
|
row(int64(1), int64(27), string("blue"), int64(20), int64(-10), grouperTimeX(0+1)),
|
|
row(int64(2), int64(16), string("blue"), int64(30), int64(-8), grouperTimeX(1+1)),
|
|
row(int64(3), int64(19), string("red"), int64(40), int64(6), grouperTimeX(2+1)),
|
|
row(int64(4), int64(27), string("green"), int64(50), int64(0), grouperTimeX(3+1)),
|
|
row(int64(5), int64(16), string("blue"), int64(60), int64(-2), grouperTimeX(4+1)),
|
|
row(int64(6), int64(34), string("blue"), int64(70), int64(100), grouperTimeX(5+1)),
|
|
row(int64(7), int64(27), string("blue"), int64(80), int64(0), grouperTimeX(6+1)),
|
|
row(int64(8), int64(16), nil, int64(90), int64(-13), grouperTimeX(7+1)),
|
|
row(int64(9), int64(16), string("red"), int64(100), int64(80), grouperTimeX(8+1)),
|
|
row(int64(10), int64(31), string("red"), int64(110), int64(-2), grouperTimeX(9+1)),
|
|
),
|
|
Compare: CompareExactOrdered,
|
|
},
|
|
// join
|
|
{
|
|
// Count(Intersect(All(),Distinct(Row(grouperid!=null),index='joiner',field='grouperid')))
|
|
SQLs: sqls("select count(*) from grouper g INNER JOIN joiner j ON g._id = j.grouperid;"),
|
|
ExpHdrs: hdrs(
|
|
hdr("", fldTypeInt),
|
|
),
|
|
ExpRows: rows(
|
|
row(int64(10)),
|
|
),
|
|
Compare: CompareExactOrdered,
|
|
},
|
|
{
|
|
// Intersect(All(),Distinct(Row(grouperid!=null),index='joiner',field='grouperid'))
|
|
SQLs: sqls("select distinct _id from grouper g INNER JOIN joiner j ON g._id = j.grouperid;"),
|
|
ExpHdrs: hdrs(hdr("_id", fldTypeID)),
|
|
ExpRows: rows(
|
|
row(int64(1)),
|
|
row(int64(2)),
|
|
row(int64(3)),
|
|
row(int64(5)),
|
|
row(int64(6)),
|
|
row(int64(7)),
|
|
row(int64(8)),
|
|
row(int64(9)),
|
|
),
|
|
Compare: CompareExactUnordered,
|
|
},
|
|
{
|
|
// Intersect(Row(color='red'),Distinct(Row(grouperid!=null),index='joiner',field='grouperid'))
|
|
SQLs: sqls("select _id from grouper g INNER JOIN joiner j ON g._id = j.grouperid where g.color = 'red';"),
|
|
ExpHdrs: hdrs(hdr("_id", fldTypeID)),
|
|
ExpRows: rows(
|
|
row(int64(3)),
|
|
row(int64(9)),
|
|
),
|
|
Compare: CompareExactUnordered,
|
|
},
|
|
{
|
|
// Intersect(Row(color='red'),Distinct(Row(jointype=2),index='joiner',field='grouperid'))
|
|
SQLs: sqls("select _id from grouper g INNER JOIN joiner j ON g._id = j.grouperid where g.color = 'red' and j.jointype = 2;"),
|
|
ExpHdrs: hdrs(hdr("_id", fldTypeID)),
|
|
ExpRows: rows(
|
|
row(int64(3)),
|
|
row(int64(9)),
|
|
),
|
|
Compare: CompareExactUnordered,
|
|
},
|
|
// order by
|
|
{
|
|
// Distinct(Row(score!=null),index='grouper',field='score')
|
|
SQLs: sqls("select distinct score from grouper order by score asc;"),
|
|
ExpHdrs: hdrs(hdr("score", fldTypeInt)),
|
|
ExpRows: rows(
|
|
row(int64(-13)),
|
|
row(int64(-10)),
|
|
row(int64(-8)),
|
|
row(int64(-2)),
|
|
row(int64(0)),
|
|
row(int64(6)),
|
|
row(int64(80)),
|
|
row(int64(100)),
|
|
),
|
|
Compare: CompareExactOrdered,
|
|
},
|
|
{
|
|
// Distinct(Row(score!=null),index='grouper',field='score')
|
|
SQLs: sqls("select distinct score from grouper order by score desc;"),
|
|
ExpHdrs: hdrs(hdr("score", fldTypeInt)),
|
|
ExpRows: rows(
|
|
row(int64(100)),
|
|
row(int64(80)),
|
|
row(int64(6)),
|
|
row(int64(0)),
|
|
row(int64(-2)),
|
|
row(int64(-8)),
|
|
row(int64(-10)),
|
|
row(int64(-13)),
|
|
),
|
|
Compare: CompareExactOrdered,
|
|
},
|
|
// {
|
|
// // Distinct(Row(score!=null),index='grouper',field='score')
|
|
// SQLs: sqls("select distinct score from grouper order by score asc limit 5;"),
|
|
// ExpHdrs: hdrs(hdr("score", fldTypeInt)),
|
|
// ExpRows: rows(
|
|
// row(int64(-13)),
|
|
// row(int64(-10)),
|
|
// row(int64(-8)),
|
|
// row(int64(-2)),
|
|
// row(int64(0)),
|
|
// ),
|
|
// Compare: CompareExactOrdered,
|
|
// },
|
|
// {
|
|
// // Distinct(Row(score!=null),index='grouper',field='score')
|
|
// SQLs: sqls("select distinct score from grouper order by score desc limit 5;"),
|
|
// ExpHdrs: hdrs(hdr("score", fldTypeInt)),
|
|
// ExpRows: rows(
|
|
// row(int64(100)),
|
|
// row(int64(80)),
|
|
// row(int64(6)),
|
|
// row(int64(0)),
|
|
// row(int64(-2)),
|
|
// ),
|
|
// Compare: CompareExactOrdered,
|
|
// },
|
|
// distinct
|
|
{
|
|
// Distinct(Row(score!=null),index='grouper',field='score')
|
|
SQLs: sqls("select distinct score from grouper;"),
|
|
ExpHdrs: hdrs(hdr("score", fldTypeInt)),
|
|
ExpRows: rows(
|
|
row(int64(-13)),
|
|
row(int64(-10)),
|
|
row(int64(-8)),
|
|
row(int64(-2)),
|
|
row(int64(0)),
|
|
row(int64(6)),
|
|
row(int64(80)),
|
|
row(int64(100)),
|
|
),
|
|
Compare: CompareExactUnordered,
|
|
},
|
|
{
|
|
// Distinct(Row(height!=null),index='grouper',field='height')
|
|
SQLs: sqls("select distinct height from grouper;"),
|
|
ExpHdrs: hdrs(hdr("height", fldTypeInt)),
|
|
ExpRows: rows(
|
|
row(int64(20)),
|
|
row(int64(30)),
|
|
row(int64(40)),
|
|
row(int64(50)),
|
|
row(int64(60)),
|
|
row(int64(70)),
|
|
row(int64(80)),
|
|
row(int64(90)),
|
|
row(int64(100)),
|
|
row(int64(110)),
|
|
),
|
|
Compare: CompareExactUnordered,
|
|
},
|
|
// groupby
|
|
{
|
|
// GroupBy(Rows(field='age'),limit=100)
|
|
SQLs: sqls("select age as yrs, count(*) as cnt from grouper group by age;"),
|
|
ExpHdrs: hdrs(
|
|
hdr("yrs", fldTypeInt),
|
|
hdr("cnt", fldTypeInt),
|
|
),
|
|
ExpRows: rows(
|
|
row(int64(16), int64(4)),
|
|
row(int64(19), int64(1)),
|
|
row(int64(27), int64(3)),
|
|
row(int64(31), int64(1)),
|
|
row(int64(34), int64(1)),
|
|
),
|
|
Compare: CompareExactUnordered,
|
|
},
|
|
// {
|
|
// // GroupBy(Rows(field='age'),Rows(field='color'),limit=100)
|
|
// SQLs: sqls("select age, color, count(*) from grouper group by age, color;"),
|
|
// ExpHdrs: hdrs(
|
|
// hdr("age", fldTypeInt),
|
|
// hdr("color", fldTypeString),
|
|
// hdr("", fldTypeInt),
|
|
// ),
|
|
// ExpRows: rows(
|
|
// row(int64(16), "blue", int64(2)),
|
|
// row(int64(16), "red", int64(1)),
|
|
// row(int64(19), "red", int64(1)),
|
|
// row(int64(27), "blue", int64(2)),
|
|
// row(int64(27), "green", int64(1)),
|
|
// row(int64(31), "red", int64(1)),
|
|
// row(int64(34), "blue", int64(1)),
|
|
// ),
|
|
// Compare: CompareExactUnordered,
|
|
// },
|
|
// {
|
|
// // GroupBy(Rows(field='age'),Rows(field='color'),limit=100,filter=Row(age=27),aggregate=Sum(field='height'))
|
|
// SQLs: sqls("select age, color, sum(height) from grouper where age = 27 group by age, color;"),
|
|
// ExpHdrs: hdrs(
|
|
// hdr("age", fldTypeInt),
|
|
// hdr("color", fldTypeString),
|
|
// hdr("", fldTypeInt),
|
|
// ),
|
|
// ExpRows: rows(
|
|
// row(int64(27), "blue", int64(100)),
|
|
// row(int64(27), "green", int64(50)),
|
|
// ),
|
|
// Compare: CompareExactUnordered,
|
|
// },
|
|
// {
|
|
// // GroupBy(Rows(field='age'),limit=100,having=Condition(count>1))
|
|
// SQLs: sqls("select age, count(*) as cnt from grouper group by age having count(*) > 1;"),
|
|
// ExpHdrs: hdrs(
|
|
// hdr("age", fldTypeInt),
|
|
// hdr("", fldTypeInt),
|
|
// ),
|
|
// ExpRows: rows(
|
|
// row(int64(16), int64(4)),
|
|
// row(int64(27), int64(3)),
|
|
// ),
|
|
// Compare: CompareExactUnordered,
|
|
// },
|
|
// {
|
|
// // GroupBy(Rows(field='age'),limit=100,having=Condition(1<=count<=3))
|
|
// SQLs: sqls("select age, count(*) from grouper group by age having count(*) between 1 and 3;"),
|
|
// ExpHdrs: hdrs(
|
|
// hdr("age", fldTypeInt),
|
|
// hdr("", fldTypeInt),
|
|
// ),
|
|
// ExpRows: rows(
|
|
// row(int64(19), int64(1)),
|
|
// row(int64(27), int64(3)),
|
|
// row(int64(31), int64(1)),
|
|
// row(int64(34), int64(1)),
|
|
// ),
|
|
// Compare: CompareExactUnordered,
|
|
// },
|
|
// {
|
|
// // GroupBy(Rows(field='age'),limit=3)
|
|
// SQLs: sqls("select age, count(*) as cnt from grouper group by age order by cnt desc, age desc limit 3;"),
|
|
// ExpHdrs: hdrs(
|
|
// hdr("age", fldTypeInt),
|
|
// hdr("cnt", fldTypeInt),
|
|
// ),
|
|
// ExpRows: rows(
|
|
// row(int64(16), int64(4)),
|
|
// row(int64(27), int64(3)),
|
|
// row(int64(19), int64(1)),
|
|
// ),
|
|
// Compare: CompareExactOrdered,
|
|
// },
|
|
{
|
|
// GroupBy(Rows(field='age'),Rows(field='height'),filter=Intersect(Row(timestamp>"2017-09-02T12:32:00Z"),Row(height>40)))
|
|
SQLs: sqls("select age, height from grouper where timestamp > '2017-09-02T12:32:00Z' and height > 40 group by age, height;"),
|
|
ExpHdrs: hdrs(
|
|
hdr("age", fldTypeInt),
|
|
hdr("height", fldTypeInt),
|
|
),
|
|
ExpRows: rows(
|
|
row(int64(16), int64(90)),
|
|
row(int64(31), int64(110)),
|
|
),
|
|
Compare: CompareExactUnordered,
|
|
},
|
|
{
|
|
// Extract(Union(Row(timestamp>"2017-09-02T12:32:00Z"),Row(height>90)),Rows(age), Rows(height))
|
|
SQLs: sqls("select age, height from grouper where timestamp > '2017-09-02T12:32:00Z' or height > 90;"),
|
|
ExpHdrs: hdrs(
|
|
hdr("age", fldTypeInt),
|
|
hdr("height", fldTypeInt),
|
|
),
|
|
ExpRows: rows(
|
|
row(int64(16), int64(90)),
|
|
row(int64(16), int64(100)),
|
|
row(int64(31), int64(110)),
|
|
),
|
|
Compare: CompareExactUnordered,
|
|
},
|
|
{
|
|
//Extract(Intersect(Row(timestamp>"2017-09-02T12:32:00Z"),Row(timestamp<"2019-09-02T12:32:00Z")),Rows(age), Rows(height))
|
|
SQLs: sqls("select age, height from grouper where timestamp > '2017-09-02T12:32:00Z' and timestamp < '2019-09-02T12:32:00Z';"),
|
|
ExpHdrs: hdrs(
|
|
hdr("age", fldTypeInt),
|
|
hdr("height", fldTypeInt),
|
|
),
|
|
ExpRows: rows(
|
|
row(int64(31), int64(110)),
|
|
),
|
|
Compare: CompareExactUnordered,
|
|
},
|
|
{
|
|
//Extract(Intersect(Row(timestamp>"2017-09-02T12:32:00Z"),Row(timestamp<"2019-09-02T12:32:00Z")),Rows(age), Rows(height))
|
|
//Testing the parenthesis around where clause
|
|
SQLs: sqls("select age, height from grouper where (timestamp > '2017-09-02T12:32:00Z' and timestamp < '2019-09-02T12:32:00Z');"),
|
|
ExpHdrs: hdrs(
|
|
hdr("age", fldTypeInt),
|
|
hdr("height", fldTypeInt),
|
|
),
|
|
ExpRows: rows(
|
|
row(int64(31), int64(110)),
|
|
),
|
|
Compare: CompareExactUnordered,
|
|
},
|
|
{
|
|
//Testing empty parenthesis around where clause
|
|
SQLs: sqls("select age, height from grouper where ();"),
|
|
ExpErr: "expected expression, found",
|
|
},
|
|
{
|
|
//Distinct(Row(timestamp>"2019-09-02T12:32:00Z"), index='grouper',field='age')
|
|
SQLs: sqls("select distinct age from grouper where timestamp > '2019-09-02T12:32:00Z';"),
|
|
ExpHdrs: hdrs(
|
|
hdr("age", fldTypeInt),
|
|
),
|
|
ExpRows: rows(
|
|
row(int64(16)),
|
|
),
|
|
Compare: CompareExactUnordered,
|
|
},
|
|
{
|
|
SQLs: sqls("show tables;"),
|
|
ExpHdrs: hdrs(
|
|
hdr("_id", fldTypeString),
|
|
hdr("name", fldTypeString),
|
|
hdr("owner", fldTypeString),
|
|
hdr("updated_by", fldTypeString),
|
|
hdr("created_at", fldTypeTimestamp),
|
|
hdr("updated_at", fldTypeTimestamp),
|
|
hdr("keys", fldTypeBool),
|
|
hdr("space_used", fldTypeInt),
|
|
hdr("description", fldTypeString),
|
|
),
|
|
ExpRows: rows(
|
|
row(nil, "grouper"),
|
|
row(nil, "joiner"),
|
|
row(nil, "delete_me"),
|
|
),
|
|
Compare: ComparePartial,
|
|
},
|
|
{
|
|
SQLs: sqls("show columns from grouper;"),
|
|
ExpHdrs: hdrs(
|
|
hdr("_id", fldTypeString),
|
|
hdr("name", fldTypeString),
|
|
hdr("type", fldTypeString),
|
|
hdr("created_at", fldTypeTimestamp),
|
|
hdr("keys", fldTypeBool),
|
|
hdr("cache_type", fldTypeString),
|
|
hdr("cache_size", fldTypeInt),
|
|
hdr("scale", fldTypeInt),
|
|
hdr("min", fldTypeInt),
|
|
hdr("max", fldTypeInt),
|
|
hdr("timeunit", fldTypeString),
|
|
hdr("epoch", fldTypeInt),
|
|
hdr("timequantum", fldTypeString),
|
|
hdr("ttl", fldTypeString),
|
|
),
|
|
ExpRows: rows(
|
|
row(nil, string("age"), string("int")),
|
|
row(nil, string("color"), string("string")),
|
|
row(nil, string("height"), string("int")),
|
|
row(nil, string("score"), string("int")),
|
|
row(nil, string("timestamp"), string("timestamp")),
|
|
),
|
|
Compare: ComparePartial,
|
|
},
|
|
// {
|
|
// SQLs: sqls("drop table delete_me;"),
|
|
// ExpHdrs: hdrs(),
|
|
// ExpRows: rows(),
|
|
// Compare: CompareExactOrdered,
|
|
// },
|
|
// The following cases test different paths within the `case *sqlparser.AndExpr`
|
|
// of extract.go by providing different WHERE conditions.
|
|
{
|
|
// len(left) == 2 && len(right) == 1
|
|
// right[0].table == left[0].table
|
|
SQLs: sqls("select _id from grouper g INNER JOIN joiner j ON g._id = j.grouperid where g.color = 'red' and j.jointype = 2 and g.age = 16;"),
|
|
ExpHdrs: hdrs(hdr("_id", fldTypeID)),
|
|
ExpRows: rows(
|
|
row(int64(9)),
|
|
),
|
|
Compare: CompareExactUnordered,
|
|
},
|
|
{
|
|
// len(left) == 2 && len(right) == 1
|
|
// right[0].table == left[1].table {
|
|
SQLs: sqls("select _id from grouper g INNER JOIN joiner j ON g._id = j.grouperid where j.jointype = 2 and g.color = 'red' and g.age = 16;"),
|
|
ExpHdrs: hdrs(hdr("_id", fldTypeID)),
|
|
ExpRows: rows(
|
|
row(int64(9)),
|
|
),
|
|
Compare: CompareExactUnordered,
|
|
},
|
|
{
|
|
// len(left) == 1 && len(right) == 1 && left[0].table != right[0].table
|
|
SQLs: sqls("select _id from grouper g INNER JOIN joiner j ON g._id = j.grouperid where g.color = 'red' and g.age = 16 and j.jointype = 2;"),
|
|
ExpHdrs: hdrs(hdr("_id", fldTypeID)),
|
|
ExpRows: rows(
|
|
row(int64(9)),
|
|
),
|
|
Compare: CompareExactUnordered,
|
|
},
|
|
{
|
|
SQLs: sqls("select * from index_not_found;"),
|
|
ExpErr: "table or view 'index_not_found' not found",
|
|
},
|
|
{
|
|
SQLs: sqls("select field_not_found from grouper;"),
|
|
ExpErr: "column 'field_not_found' not found",
|
|
},
|
|
{
|
|
SQLs: sqls("select * from grouper, index_not_found;"),
|
|
ExpErr: "table or view 'index_not_found' not found",
|
|
},
|
|
{
|
|
SQLs: sqls("select _id, age, field_not_found from grouper;"),
|
|
ExpErr: "column 'field_not_found' not found",
|
|
},
|
|
{
|
|
SQLs: sqls("select age, color, count(*) from grouper group by field_not_found, age, color;"),
|
|
ExpErr: "column 'field_not_found' not found",
|
|
},
|
|
{
|
|
SQLs: sqls("select count(*) from grouper inner join joiner on grouper._id = joiner.field_not_found;"),
|
|
ExpErr: "column 'field_not_found' not found",
|
|
},
|
|
},
|
|
}
|