Details
-
Task
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.3.3-1
Description
SQL Standards starting from SQL-1999 uses the construct <table value constructor> as
one of the alternatives for <simple table>:
<simple table> ::=
|
<query specification>
|
| <table value constructor>
|
| <explicit table>
|
Currently MariaDB uses <table value constructor> only in insert statements:
INSERT INTO t VALUES (1,'xx'), (5,'yyy'), (1,'zzz'); |
With <table value constructor> it will be possible to use such CTE specification:
WITH t (a,c) AS (SELECT * FROM VALUES (1,'xx'), (5,'yyy'), (1,'zzz')) ... |
Now instead of this we have to use something like this:
WITH t (a,c) AS (SELECT 1, 'xx' UNION ALL SELECT 5, 'yyy' UNION ALL 1, 'zzz') ... |
Processing of the latter will take much more memory.
Besides with the possibility of using table value constructors in CTE specifications some optimization transformations for IN predicates will be possible.
Attachments
Issue Links
- blocks
-
-
- Closed
-
Activity
First evaluation results:
( see the the list of commits here https://github.com/MariaDB/server/compare/10.3...shagalla:10.3-mdev12172 )
1. New structure Table Value Constructor (TVC) was added in grammar so it can be used in such ways:
values (1,2);
|
select 3,4 union values(1,2);
|
select * from t1 where (t1.a,t1.b) in (values (1,2),(2,3));
|
create view v1 as values (1,2);
|
2. TVC was made as a class in new file sql_tvc.h (class table_value_constr).
Each TVC instance contains its list of values and link on the results of the query where results of this TVC execution will be stored (result).
It was decided to start with the case when TVC is a part of UNION statement, so all forward statements are only for this case.
3. Link on TVC instance is stored in st_select_lex class. If TVC is recognised during parsing, new instance of st_select_lex is initialized with non-zero link on TVC.
4. sql_tvc.cc was created where table_value_constr::prepare and table_value_constr::exec are stored.
So TVC has different prepare and execution stages from st_select_lex and doesn't have optimization stage.
5. On prepare stage TVCs values types and names of columns of future temporary table where TVC will be stored are searched. As SQL Standard says nothing about how columns should be named it was decided to name them as elements of the first group of values of this TVC.
6. On execution stage TVCs values are sent to result using send_date method.
7. Now this example is working:
select 5,7 union values (1,2),(2,3);
|
|
|
+---+---+
|
| 5 | 7 |
|
+---+---+
|
| 5 | 7 |
|
| 1 | 2 |
|
| 2 | 3 |
|
+---+---+
|
Hi Dinuka,
It's great that you are interested in this task, but have you noticed that this task is coupled with the task mdev-12176?
hi,
I am undergraduate of computer engineering department, Faculty of Engineering, University of Peradeniya, Sri Lanka.
And I am interested in this project. I have lot of experience with MySQL also.
Thank you.
Second evaluation results:
( see the the list of commits here https://github.com/MariaDB/server/compare/10.3...shagalla:10.3-mdev12172 )
Queries where TVC(s) are used are processed successfully.
TVCs can be used separately as in:
values (1,2);+---+---+| 1 | 2 |+---+---+| 1 | 2 |+---+---+with UNION/UNION ALL:
values (1,2) union select 1,2;+---+---+| 1 | 2 |+---+---+| 1 | 2 |+---+---+in derived tables:
select * from (values (1,2),(3,4)) as t2;+---+---+| 1 | 2 |+---+---+| 1 | 2 || 3 | 4 |+---+---+views:
create view v1 as values (1,2),(3,4);select * from v1;+---+---+| 1 | 2 |+---+---+| 1 | 2 || 3 | 4 |+---+---+and non-recursive common table expressions:
with t2 as (values (1,2),(3,4)) select * from t2;+---+---+| 1 | 2 |+---+---+| 1 | 2 || 3 | 4 |