mirror of
https://github.com/featurebasedb/featurebase.git
synced 2026-10-09 04:17:51 +00:00
158cc669d9
* tighten up checks for order by expressions fixed ordering by expressions * added testing to cover order by cases * Add DecimalAgg member to proto GroupCount definition In DAX, where we have split the orchestrator from the executor, and the orchestrator can run on a different host, there are cases where `GroupCount`s can travel over the wire via the Internal Client. In these cases, when the group count contains a decimal aggregate, we need to send that value as the appropriate type. * fixed missing cases in order by and case block eval Co-authored-by: Travis Turner <travis@molecula.com>
225 lines
5.8 KiB
Go
225 lines
5.8 KiB
Go
package defs
|
|
|
|
import (
|
|
featurebase "github.com/molecula/featurebase/v3"
|
|
"github.com/molecula/featurebase/v3/dax"
|
|
"github.com/molecula/featurebase/v3/pql"
|
|
)
|
|
|
|
// groupby tests
|
|
var groupByTests = TableTest{
|
|
Table: tbl(
|
|
"groupby_test",
|
|
srcHdrs(
|
|
srcHdr("_id", fldTypeID),
|
|
srcHdr("i1", fldTypeInt, "min 0", "max 1000"),
|
|
srcHdr("d1", fldTypeDecimal2),
|
|
srcHdr("s1", fldTypeString),
|
|
srcHdr("i2", fldTypeInt, "min 0", "max 1000"),
|
|
),
|
|
srcRows(
|
|
srcRow(int64(1), int64(10), float64(10), string("10"), int64(100)),
|
|
srcRow(int64(2), int64(10), float64(10), string("10"), int64(200)),
|
|
srcRow(int64(3), int64(11), float64(11), string("11"), nil),
|
|
srcRow(int64(4), int64(12), float64(12), string("12"), nil),
|
|
srcRow(int64(5), int64(12), float64(12), string("12"), nil),
|
|
srcRow(int64(6), int64(13), float64(13), string("13"), nil),
|
|
),
|
|
),
|
|
SQLTests: []SQLTest{
|
|
{
|
|
SQLs: sqls(
|
|
"SELECT COUNT(*), i1 FROM groupby_test group by i1 order by count(*) asc",
|
|
"SELECT COUNT(_id), i1 FROM groupby_test group by i1 order by count(*) asc",
|
|
),
|
|
ExpErr: "column reference, alias reference or column position expected",
|
|
},
|
|
{
|
|
SQLs: sqls(
|
|
"SELECT COUNT(*), i1 FROM groupby_test group by i1 order by 2 asc",
|
|
"SELECT COUNT(_id), i1 FROM groupby_test group by i1 order by 2 asc",
|
|
),
|
|
ExpHdrs: hdrs(
|
|
hdr("", fldTypeInt),
|
|
hdr("i1", fldTypeInt),
|
|
),
|
|
ExpRows: rows(
|
|
row(int64(2), int64(10)),
|
|
row(int64(1), int64(11)),
|
|
row(int64(2), int64(12)),
|
|
row(int64(1), int64(13)),
|
|
),
|
|
Compare: CompareExactOrdered,
|
|
},
|
|
{
|
|
SQLs: sqls(
|
|
"SELECT COUNT(*), i1 as c FROM groupby_test group by i1 order by c asc",
|
|
"SELECT COUNT(_id), i1 as c FROM groupby_test group by i1 order by c asc",
|
|
),
|
|
ExpHdrs: hdrs(
|
|
hdr("", fldTypeInt),
|
|
hdr("c", fldTypeInt),
|
|
),
|
|
ExpRows: rows(
|
|
row(int64(2), int64(10)),
|
|
row(int64(1), int64(11)),
|
|
row(int64(2), int64(12)),
|
|
row(int64(1), int64(13)),
|
|
),
|
|
Compare: CompareExactOrdered,
|
|
},
|
|
{
|
|
SQLs: sqls(
|
|
"SELECT COUNT(*), i1 as c FROM groupby_test group by i1 order by i1 asc",
|
|
"SELECT COUNT(_id), i1 as c FROM groupby_test group by i1 order by i1 asc",
|
|
),
|
|
ExpHdrs: hdrs(
|
|
hdr("", fldTypeInt),
|
|
hdr("c", fldTypeInt),
|
|
),
|
|
ExpRows: rows(
|
|
row(int64(2), int64(10)),
|
|
row(int64(1), int64(11)),
|
|
row(int64(2), int64(12)),
|
|
row(int64(1), int64(13)),
|
|
),
|
|
Compare: CompareExactOrdered,
|
|
},
|
|
{
|
|
SQLs: sqls(
|
|
"SELECT COUNT(*), i1 FROM groupby_test group by i1",
|
|
"SELECT COUNT(_id), i1 FROM groupby_test group by i1",
|
|
),
|
|
ExpHdrs: hdrs(
|
|
hdr("", fldTypeInt),
|
|
hdr("i1", fldTypeInt),
|
|
),
|
|
ExpRows: rows(
|
|
row(int64(2), int64(10)),
|
|
row(int64(1), int64(11)),
|
|
row(int64(2), int64(12)),
|
|
row(int64(1), int64(13)),
|
|
),
|
|
Compare: CompareExactUnordered,
|
|
},
|
|
{
|
|
SQLs: sqls(
|
|
"SELECT COUNT(distinct i2) AS count_rows, i1 FROM groupby_test group by i1",
|
|
),
|
|
ExpHdrs: hdrs(
|
|
hdr("count_rows", fldTypeInt),
|
|
hdr("i1", fldTypeInt),
|
|
),
|
|
ExpRows: rows(
|
|
row(int64(2), int64(10)),
|
|
row(int64(0), int64(11)),
|
|
row(int64(0), int64(12)),
|
|
row(int64(0), int64(13)),
|
|
),
|
|
Compare: CompareExactUnordered,
|
|
},
|
|
{
|
|
SQLs: sqls(
|
|
"SELECT sum(i2) AS sum_rows, i1 FROM groupby_test group by i1",
|
|
),
|
|
ExpHdrs: hdrs(
|
|
hdr("sum_rows", fldTypeInt),
|
|
hdr("i1", fldTypeInt),
|
|
),
|
|
ExpRows: rows(
|
|
row(int64(300), int64(10)),
|
|
),
|
|
Compare: CompareExactUnordered,
|
|
},
|
|
{
|
|
SQLs: sqls(
|
|
"SELECT COUNT(*) FROM groupby_test group by i1",
|
|
),
|
|
ExpHdrs: hdrs(
|
|
hdr("", fldTypeInt),
|
|
),
|
|
ExpRows: rows(
|
|
row(int64(2)),
|
|
row(int64(1)),
|
|
row(int64(2)),
|
|
row(int64(1)),
|
|
),
|
|
Compare: CompareExactUnordered,
|
|
},
|
|
{
|
|
SQLs: sqls(
|
|
"select count(distinct i2) AS count_rows, sum(i2) as sum_rows, i1 from groupby_test group by i1",
|
|
),
|
|
ExpHdrs: hdrs(
|
|
hdr("count_rows", fldTypeInt),
|
|
hdr("sum_rows", fldTypeInt),
|
|
hdr("i1", fldTypeInt),
|
|
),
|
|
ExpRows: rows(
|
|
row(int64(2), int64(300), int64(10)),
|
|
row(int64(0), nil, int64(11)),
|
|
row(int64(0), nil, int64(12)),
|
|
row(int64(0), nil, int64(13)),
|
|
),
|
|
Compare: CompareExactUnordered,
|
|
},
|
|
{
|
|
SQLs: sqls(
|
|
"select avg(i1) as avg_rows, i1 from groupby_test group by i1",
|
|
),
|
|
ExpHdrs: hdrs(
|
|
hdr("avg_rows", featurebase.WireQueryField{
|
|
Type: dax.BaseTypeDecimal + "(4)",
|
|
BaseType: dax.BaseTypeDecimal,
|
|
TypeInfo: map[string]interface{}{"scale": int64(4)},
|
|
}),
|
|
hdr("i1", fldTypeInt),
|
|
),
|
|
ExpRows: rows(
|
|
row(pql.NewDecimal(100000, 4), int64(10)),
|
|
row(pql.NewDecimal(110000, 4), int64(11)),
|
|
row(pql.NewDecimal(120000, 4), int64(12)),
|
|
row(pql.NewDecimal(130000, 4), int64(13)),
|
|
),
|
|
Compare: CompareExactUnordered,
|
|
},
|
|
{
|
|
SQLs: sqls(
|
|
"select avg(d1) as avg_rows, i1 from groupby_test group by i1",
|
|
),
|
|
ExpHdrs: hdrs(
|
|
hdr("avg_rows", featurebase.WireQueryField{
|
|
Type: dax.BaseTypeDecimal + "(4)",
|
|
BaseType: dax.BaseTypeDecimal,
|
|
TypeInfo: map[string]interface{}{"scale": int64(4)},
|
|
}),
|
|
hdr("i1", fldTypeInt),
|
|
),
|
|
ExpRows: rows(
|
|
row(pql.NewDecimal(100000, 4), int64(10)),
|
|
row(pql.NewDecimal(110000, 4), int64(11)),
|
|
row(pql.NewDecimal(120000, 4), int64(12)),
|
|
row(pql.NewDecimal(130000, 4), int64(13)),
|
|
),
|
|
Compare: CompareExactUnordered,
|
|
},
|
|
{
|
|
SQLs: sqls(
|
|
"select percentile(i1, 0) as p_rows, i1 from groupby_test group by i1",
|
|
),
|
|
ExpErr: "aggregate 'PERCENTILE()' not allowed in GROUP BY",
|
|
},
|
|
{
|
|
SQLs: sqls(
|
|
"select min(i1) as p_rows, i1 from groupby_test group by i1",
|
|
),
|
|
ExpErr: "aggregate 'MIN()' not allowed in GROUP BY",
|
|
},
|
|
{
|
|
SQLs: sqls(
|
|
"select max(i1) as p_rows, i1 from groupby_test group by i1",
|
|
),
|
|
ExpErr: "aggregate 'MAX()' not allowed in GROUP BY",
|
|
},
|
|
},
|
|
}
|