It's reasonable to allow "where x > 13" on decimal
fields. Handle at least int64 and float64.
Once this is up, we find that aggregates can return
non-values, such as nil, in some cases; for instance,
`percentile(x) where x > 13` can yield a nil if x is
never greater than 13, rather than making up a value
from zero data points. So we accept nil as a valid
result type in PQL aggregates.
As a result of this, change two tests which were
unintentionally testing for an arcane edge case bug
in which (1) we can't render a condition to PQL,
such as because you specified an integer for a decimal
field, and (2) the filter is using an aliased name,
in which we would end up failing to generate a PQL
filter, but *also* losing the SQL-layer filter, and
produce wrong results as though there were no filter.
We also alter the tests to use `o.price > 9`, because
this lets us generate three user names, but only two
distinct user names, so the test using DISTINCT returns
a different value than the test not using DISTINCT,
which helps us verify that it's actually working and
not just lucky.
As part of fixing that, there was an intermediate
state where we rejected as an error any case where
generating the PQL filter failed. This broke 21 more
test cases, but in all of those cases, the SQL filter
was actually working.
... But in two of them, we SHOULD have been able to
generate PQL, because they were testing bools for
null, which works fine. We just had a list of
field types we allowed null tests against and
omitted bool because I forgot that bool isn't always
just treated as a kind of mutex.
* first time quantum queries working
* implement select from timequantum columns
* skip a dax test
* make linter happy
* addressed review feedback
* reverted over eager test elimination
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.
* Fix PQL distinct in dax
When issuing a PQL Distinct() call (or any other call with a "index=" arg),
this commit will attempt to convert the value in the index arg with a
TableKeyer.
* Apply change to call.Children as well
* Add some PQL Distinct (join) test coverage
(cherry picked from commit 4e8fe488de)
this commit changes the way the plan is retrieved; implements Stringer on types.PlanExpression in preparation for HAVING support; removes last vestiges internal float64 arithmetic; implements a filter on PlanOpFilter; fixes various bugs in the PlanOptimizer when rewriting qualified references
* fixed selects with unqualified identifiers
* handle bad and non-existent query param inputs more appropriately
* added test coverage for PlanExpression Stringer
Co-authored-by: Matthew Jaffee <jaffee@pilosa.com>
(cherry picked from commit 5f662d2bce)
