Arseniy Volynets authored 5 months ago and Denis Smirnov committed 5 months ago

- Add new line in explain reporting on which buckets query will
  be executed.
- For queries consisting of a single subtree we can say exactly on
  which buckets it will be executed, for queries with more subtrees
  (with motions), we provide an upper bound of total buckets used
  in the query. Upper bound is computed by merging buckets from the
  leaf subtrees.
- In case for DML query with non-local motion we can't provide an
  upper bound, and print 'buckets: unknown'

Examples:
```
explain select a from t
->
projection ("t"."a"::integer -> "a")
    scan "t"
execution options:
    vdbe_max_steps = 45000
    vtable_max_rows = 5000
buckets = [1-3000]

explain select t.a from t join t as t2
on t.a = t2.b
->
projection ("t"."a"::integer -> "a")
  ...
execution options:
    vdbe_max_steps = 45000
    vtable_max_rows = 5000
buckets <= [1-3000]

explain select id from _pico_table
->
projection ("_pico_table"."id"::unsigned -> "id")
    scan "_pico_table"
execution options:
    vdbe_max_steps = 45000
    vtable_max_rows = 5000
buckets = any

explain insert into t values (1, 2)
->
insert "t" on conflict: fail
motion [policy: segment([ref("COLUMN_1")])]
    values
        value row (...)
execution options:
    vdbe_max_steps = 45000
    vtable_max_rows = 5000
buckets = unknown
```

- We didn't apply vtable max rows value
when executing local sql. We tried to lookup
it in the execute options hashmap and took
the default value instead, though it was
not stored in the hashmap.

- Support simple queries that do not select
data from tables. This is similar to `values ..`,
but allows to add aliases for expressions.
- Such selects can also be used in subqueries
- Examples:
```
select 1;
select (select count(*) from t);
select 2 as foo, 3 as bar;

select a from t
where b in (select 100)
```

Dmitry Rodionov authored 8 months ago and Denis Smirnov committed 6 months ago

Previous version was using yaml-rust which is marked as unmaintained.
This gets reported by dependency scanner we use for certification.
Though worth noting that serde-yaml itself is now archived. There is a
promising replacement library but no need to hurry with that just yet.
But anyway, we don't need this library in the production build, only
for tests. So, moved it to development dependencies.

Co-authored-by: Denis Smirnov <sd@picodata.io>

- Support like operator with signature:

expr1 LIKE expr2 [ESCAPE expr3]

which returns TRUE only if expr1 matches the
string specified by expr2 (pattern). '_' in
pattern matches any single character, '%'
matches any character 0 or more times. All
other characters match itself according to
case.

- Optional escape clause specifies character
to use for escaping '_' and '%'

feat: support additional SubQuery children for any relational operator, interpret SubQuery with single output as expression

fix: remove useless SubQuery nodes, fix logic of vtable columns naming (automatic indexes instead of names from local SQL metadata)

Arseniy Volynets authored 9 months ago and Arseniy Volynets committed 6 months ago

- For cacheable requests, we now send only cache required data needed to
execute the query if it is in the storage cache. If query is not in the
storage cache, storage returns empty result with flag indicating there
was a cache miss. Then coordinator sends the second round of requests to
replicasets where cache miss happened. It sends the full message there.
- Parameters for required message are now computed without compilation
to SyntaxPlan - Fixed parameter order in SyntaxPlan

Previously we cached only the leaf sub-trees of the IR plan under
the first level of motions. The problem was in temporary tables as
we used random names for them and dropped after usage (and invalidated
corresponding prepared statements as well). Now we use idempotent
names for temporary tables and truncate them after usage. It allows
us to keep prepared statements in the cache. One more thing: as we
use the same temporary table for multiple queries, we need to use
latch locks for the prepared statement cache. One day we'll switch
to more granular locks (mostly for vinyl spaces), but let's use
current approach for the start.

We also removed can_be_cached flag from the code. Only DQL uses VDBE
and needs caching, and all these queries can be cached. The only
exception are the queries with Local and LocalSegment motion policies
but only DML can have them. So, no need for this flag and let's make the
code cleaner.

Arseniy Volynets authored 8 months ago and Arseniy Volynets committed 7 months ago

- Normalize name to lowercase from parser
- Identifiers are stored as normalized strings
without quotes
- During conversion to local SQL we wrap
identifiers in quotes, because tarantool
normalizes names without quotes to uppercase
- For errors and explain, we also need to
wrap names into quotes for better readability