If we can't successfully generate a PQL Percentile call, error
out rather than implementing an actual Percentile function in SQL.
This can be revisited if anyone needs it.
So there's a lot going on here.
Percentile just did not work, even a little, with decimals.
In theory we try to make the int val part of ValCount work, in
ValCountize, but you can't actually use that for everything because
it unconditionally adds bsig.Base even when it shouldn't. But it
doesn't matter that we were returning those values from, say,
(Field).MinForShard, because ValCount.Smaller was not preserving them
when identifying the smaller of two Decimal ValCounts anyway.
And even if it did, the logic in Percentile wouldn't have worked
with passing the raw unscaled integer in as a value to compare
against.
But that's fine because the logic was also more generally wrong.
According to the existing logic, a value is the median value if
exactly as many values are less than it as are greater than it.
This is... not actually very accurate to what we usually mean by
"median". Because some values are *equal* to a given value. So
for instance, say you have the values {1, 1, 1, [a million 2s], 3}.
Our logic would regard 2 as being too high to be the median, because
3 times as many values are lower as are higher.
New interpretation: Imagine a sorted list of all your values, with
N entries. You want the Nth percentile, which is to say, you want N%
of values to be less than the vale you pick, and (100-N)% to be greater.
You can round both of these down. So for instance, if you have 6 values,
and want the median, you want 3 values greater, and 3 values less. To
be picky, we could demand the average of those middle two values, but
we're not in a good position to do that in this implementation.
If the number of desired things less than, or greater than, a target
is 0, we can short-circuit to the minimum or maximum value. This can
happen when nth is close to an end and the number of things is small,
not just at nth=0/nth=100.
So we rework this, and we rework the tests for this behavior to reflect
that logic.
We change executePercentile to be able to return a nil rather than
a weird ValCount in cases where there's no result, such as when
there's no values to compute a percentile of.
We also change the SQL tests to match the new behavior, since some
of them were expecting everything done on a decimal field with values
10-13 to come back as 10.00 as a decimal because that is what the
code returned.
We also propagate these changes to DAX, and along the way, fix up a
TODO item in the DAX copy, and stop skipping the test that was
failing because of that TODO item.
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.
analyzeExpression - tupleLiteralExpression was covered by something
else between the ticket getting filed and me starting on it.
(*ExecutionPlanner).analyzeBinaryExpression now has increased
coverage for IN / NOT IN. Several bugs got revealed by adding tests;
those tests are commented out but can be re-enabled by whoever ends
up working on the bugs. Tickets are filed.
The attempt to set the TrackExistence option for fields
happened before checking whether the field was created
successfully or not. Credit to Rachith for spotting this.
Bug was introduced with the TrackExistence stuff, but
we apparently never had a test case for invalid min/max
values.
Assignment compatibility checking in analyzeBulkInsertStatement is
now tested. This isn't checking the values themselves, it's there
to make sure the structure is correct for mapping values to columns.
FB-2045
aggregate{Avg,Min,Max}->Update now tested for DataTypeDecimal.
{avg,min,max}PlanExpression->WithChildren now tested.
percentilePlanExpression->{Evaluate,Plan,WithChildren} is not
tested because percentile gets sent directly to PQL rather than
getting planned and evaluated in SQL.
aggregateLast->everything is not tested because Last is not yet
completely implemented.
There's a lot going on here. First, we were treating "the test is
a Condition" as implying BSI, which it doesn't anymore. Second, the
behavior of conditions was weird and BSI-specific. Third, we had
to propagate these changes and features throughout a bunch of code,
including both the core featurebase code and the DAX replacements/copies
of it, plus the SQL3 layer.
We refactor this so that tests for equality and inequality work for
non-BSI fields, so now if you accidentally use `==` in a Row call
on a non-BSI field, it still works; that's not specific to BSI
fields anymore.
We add a TrackExistence flag to fields, and propagate it through
things like our protobuf code, etcetera, so that we can successfully
create fields. Newly-created fields get this by default, because
we add it unconditionally to them, but the paths that are being
called with existing fields don't add it. So, when we "create"
(really, just load the definition of) a field from something stored
in the schema, we don't add TrackExistence to it, but any path to
creating a new field should.
A time quantum field with NoStandardView will *effectively*
lack TrackExistence.
For sets, mutexes, and time quantums with a standard view, anything
that sets bits will also set a corresponding bit for the record in
a new "existence" view. This allows us to distinguish between an
empty set and a null, and also allows null checks to be constant-time.
When clearing bits, we don't clear existence bits EXCEPT that if
you clear a bit in a mutex, *and the bit actually existed*, we clear
the existence bit. For sets and time quantums, clearing bits never
clears the existence bit.
Deleting records clears the existence bit.
We also add code to the `batch` subpackage to generate suitable
existence field bitmaps and import them. This logic correctly handles
empty sets and nils. The `batch` package does not allow specification
of anything equivalent to clearing a single bit from an existing
record, so we don't have to deal with the mutex complexity in that
case, which is good because it would be impossible.
This requires a number of other subtle changes, such as allowing
new fields to have more than one FieldOption specified for them.
We also drop the handful of implementation bits relating to the
"fullySorted" internal-use-only import flag, which existed only to
support the JSON ingest API, which we've removed.
The most dangerous part of this is that the mutex semantics are
impossible to implement on top of our existing API, because they
require us to know, not how *many* bits we cleared, but which
*specific* bits we cleared. I've implemented this as a new Tx method,
which is almost certainly going to be tech debt one day; if we some
day drop the Import API, we should remove that.
The testing for this is only currently covering the Set/Clear
behavior of PQL, and the Import API. The batch tests haven't been
written yet.
Fields that don't have existence tracking enabled refuse to perform
null/not-null tests. They should also report themselves as having
no null values -- if a record exists, sets in it are considered
empty rather than null.
The SQL3 support requires a number of subtle modifications to both
featurebase and some addon tooling. The essential thing is dropping
the unconditional translation of nil slices to non-nil empty slices
in translateResult, both in the executor and the orchestrator. We
also modify the logic that handles generating results from Extract
calls, to ensure that non-null sets get an empty slice created for
them even if they never have any values assigned.
The expected results for some tests are different now; we expect to
get nil slices, rather than 0-length non-nil slices, for fields which
were never written for a given record. Most tests were not changed.
(In every case, if a test was failing, I actually checked the logic
before changing expected results. This required a lot of tracking down
of edge cases.)
The batch package now rejects as an error attempts to clear single
bits from mutex fields, because so far as I can tell it's simply
impossible to have a roaring import that specifies the correct semantics
there; you can't tell whether to clear an existence bit without
access to the currently-set bits, which the batch API doesn't have.
We already supported the special case of specifying a clear value
of nil for clearing a mutex field; now that is the only allowed
value for a mutex field to have in row.Clears.
We change the logic for fixing up incoming view names (in two places)
to stop assuming that any view in a time field other than "" that does
not have viewStandard as a prefix is a partial time quantum name that
should have "standard_" prepended to it. This allows us to submit
bitmaps for "existence" to time quantum fields and not have them
silently transformed into "standard_existence" because that's what we'd
do with "202203".
We drop the field ClearBits method, which was totally unused.
We drop the sliceDifference function, which was used in a previous
mutex implementation and hasn't been used in ages, and the test
case for it, and the helper function used only by that test case.
FB-2041
Added tests for typeIsTimeQuantum and typeIsSet and made them pass.
Added tests for DataTypeTuple cases in typesAreAssignmentCompatible.
Timestamp conversion checking is handled before it gets to that
point but I left those branches in as a backstop.
Checking to see if DataType[String,ID]SetQuantum can be assigned
to themselves doesn't appear to be reachable currently but left
those branches in, because something may use them in future.
Added one test to the DAX skip list since it's the IDSetQ version
of a StringSetQ test that was already on there, changed skip list
to refer to both tests by name instead of by number.
For on-prem serverless, if we restart the process containing the
controller and computer(s), when they come back up, the controller
doesn't know that the computers have been restarted, so it doesn't send
them a directive. This change forces the controller to send a directive
upon startup by a computer.
FB-1897
The specs for the datetimepart function are, as far as i can tell,
identical to the existing datepart function. Per Pat, replaced the
datepart function with datetimepart rather than just adding
datetimepart as an alias. Made sure existing tests that were using
datepart got switched over.
Second go at this after sorting out weirdness with git.
FB-1896
Added the datetimename function, which returns parts of a timestamp
as strings. Month and day of the week are named ("January", "Monday")
while others are returned as a string of digits ("2023").
Added tests to the test definitions.
* 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
* correct reference for `having count(*)`
It turns out that `having count(*) ...` was always treating
the count(*) as exactly 1. After studying this a lot, I noticed
that in fact, we correctly handle other counts. The reason is
that there's already code to recognize aggregates in `having`
clauses as matching aggregates that are being computed -- but
it only covers the other aggregate clause types, not the newly
added `countStarPlanExpression` from making `count(*)` work even
if there's no `_id` field.
We add several corresponding test cases.
* fix sum(a_decimal) type conversion
Added a test case for this, and also added a fix for it.
Underlying issue: qualifiedRefPlanExpression could end up
producing an int64 instead of a pql.Decimal, even though it
had expected type Decimal.
Originally this worked by politely converting an int64 to
a pql.Decimal in the Evaluate phase, but this was not ideal;
the real question is why it was coming out as an int64 at
that step. Showed this to Pat, who spent a while studying it
and produced a better fix.
* temporarily comment out test which fails in DAX
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.
* Removed support for EPOCH column constraint from TIMESTAMP SQL data type.
* Implicit conversion of integers to timestamp will treat the integer value as seconds since unix epoch.
* Add new ToTimeStamp(num, timeunit) SQL scalar function to help convert integer values to timestamp.
* make nodeid come from the correct table
* refactored aggregates; added ability to aggregate on expressions not just references
* addressed feedback
* now with the compiler errors fixed after rebase
Note: We now skip a test because we can't pass it but fixing
it is presently beyond my understanding. In parser_test.go,
we skip
SELECT * FROM X INNER JOIN Y ON true INNER JOIN Z ON false
because if we stringify it, we put the "ON true" in the wrong
place, and end up with something we can't parse.
The main change here is modifying AssertParseStatement and
AssertStatementStringer (and the corresponding Expression
functions) to also verify that they can clone and round-trip,
and that we can walk expressions. This gives us a ton
more coverage of Clone and conversions to string, and caught
a number of subtle typos and missing type switch cases.
Some of the changes are mostly cosmetic, such as only
including optional words when stringifying expressions
or statements if those optional words were present originally,
as shown by the Pos value stored for those words.
We also drop a lot of trailing apostrophes from some of the
parse test cases, which appear to be harmless but won't be
reproduced when converting back to strings.
We also add a number of additional test cases, or add
clauses to existing test cases, to improve coverage of a
lot of error testing. For instance, we added a decimal
field to the tests of show table, and added cases
using KEYPARTITIONS. (Although it doesn't *do* anything.)
Similarly, whenever we create a statement, we check
the behavior of requesting a list of sources from it, to
verify that source finding code at least runs.
* fb-1940 re-implemented some changes that got missed private-public
* fb-1939 fixes to between + decimals
* fb-1935 - avg() on and id type + fixed some tests
* fb-1953 add min/max for string types
* fb-1938 - remove internal_type column from show columns
* fb-1964 - fix space_used in fb_cluster_nodes to be int
* fb-1996 - make sure all Idents that are being used as object references to schema objects are lowercased
* fixed failing test
* added some missed changes
* fb-1969 found another case issue with identifier used for column idents
* Support Drop Table in serverless (include Snapshotter, Writelogger)
* Finish Database methods
Things like:
- `Databases`
- `DatabaseByID`
- `DatabaseByName`
- `DropDatabase`
* Change Poller to use NodeService instead of its own map
Instead of the Poller maintaining its own map of Addresses to poll, this
commit changes the Poller to use the NodeService interface to get all
known nodes from the Controller.
The next commit needs to:
Next, the logic in the boltdb NodeService implementation was moved to
the boltdb Balancer implementation. That way, the Balancer can be the
source of truth for all things nodes/workers/jobs.
* Move NodeService from Controller to Balancer
This commit moves the implementation of the NodeService into the
Balancer, and aligns `Balancer.AddWorker` with `NodeService.CreateNode`
so that they stay in sync. (Same for `Balancer.RemoveWorker` and
`NodeService.DeleteNode`).
* fix import of private repo
* Fix go vet issues
* Fix bug in DeregisterNode
We need to remove the node from the NodeService even if it's not
assigned to a database. The logic had a bug in it.
This also adds some no-op implementations for SnapshotService and
WriteloggerService. If a directory was not configured for that, then the
computer node would panic on trying to read from the Snapshotter upon
receiving a Directive.
* queryer response content-type: json
* Add support for NULL to WriteloggerDir and SnapshotterDir configs
This commit changes the way WriteLoggerDir and SnapshotterDir are
handled.
If value is empty `""`, an error will be returned on computer startup.
If value is `"NULL"`, a no-op implementation of the service will be
used. This would be for a case that wanted to run serverless on-prem
with no durable storage.
Finally, any other value will be used as the directory to use.
Some things which aren't considered here and may result in unexpected
behavior:
- a value with spaces `" "`
- any "null" which is not "NULL"... like lowercase.
* Finish the DropTable test
* Change "disable service" value to case-insensitive "off"
This commit also removes an unnecessary sleep in the tests.
* Fix docker-compose variables for IDK test
Co-authored-by: Matthew Jaffee <jaffee@pilosa.com>
* implement CREATE/ALTER/DROP VIEW
* fixed failing test
* another failing test
* fixed some broken serverless tests
(cherry picked from commit c620aae350)
