|
|
|
# Тип запроса 18
|
|
|
|
|
|
|
|
## Prostore SQL
|
|
|
|
|
|
|
|
```sql
|
|
|
|
-- transactions distributed by (col1 datatypeX, col2 datatype2)
|
|
|
|
-- accounts distributed by (colA datatype1, colB datatype2)
|
|
|
|
-- col1 and colA are not(!) of equal datatype
|
|
|
|
-- col2 and colB are of equal datatype
|
|
|
|
SELECT *
|
|
|
|
FROM transactions t
|
|
|
|
JOIN accounts a ON a.id = t.account_id
|
|
|
|
WHERE t.col1 = 1 AND t.col2 = 2 AND a.colA = 1 AND a.colB = 2
|
|
|
|
```
|
|
|
|
|
|
|
|
## Tarantool SQL
|
|
|
|
|
|
|
|
```sql
|
|
|
|
SELECT *
|
|
|
|
FROM
|
|
|
|
(SELECT "col1", "col2", "account_id", "amount"
|
|
|
|
FROM "dev__testdblongtext__transactions_history"
|
|
|
|
WHERE "sys_from" <= 0 AND "sys_to" >= 0
|
|
|
|
UNION ALL
|
|
|
|
SELECT "col1", "col2", "account_id", "amount"
|
|
|
|
FROM "dev__testdblongtext__transactions_actual"
|
|
|
|
WHERE "sys_from" <= 0) AS "t3"
|
|
|
|
INNER JOIN
|
|
|
|
(SELECT "id", "cola", "colb"
|
|
|
|
FROM "dev__testdblongtext__accounts_history"
|
|
|
|
WHERE "sys_from" <= 0 AND "sys_to" >= 0
|
|
|
|
UNION ALL
|
|
|
|
SELECT "id", "cola", "colb"
|
|
|
|
FROM "dev__testdblongtext__accounts_actual"
|
|
|
|
WHERE "sys_from" <= 0) AS "t8"
|
|
|
|
ON "t3"."account_id" = "t8"."id"
|
|
|
|
WHERE "t3"."col1" = 1 AND "t3"."col2" = 2 AND ("t8"."cola" = 1 AND "t8"."colb" = 2)
|
|
|
|
```
|
|
|
|
|
|
|
|
## AST дерево
|
|
|
|
|
|
|
|
```json
|
|
|
|
[
|
|
|
|
{
|
|
|
|
"Query": {
|
|
|
|
"with": null,
|
|
|
|
"body": {
|
|
|
|
"Select": {
|
|
|
|
"distinct": false,
|
|
|
|
"top": null,
|
|
|
|
"projection": [
|
|
|
|
"Wildcard"
|
|
|
|
],
|
|
|
|
"from": [
|
|
|
|
{
|
|
|
|
"relation": {
|
|
|
|
"Derived": {
|
|
|
|
"lateral": false,
|
|
|
|
"subquery": {
|
|
|
|
"with": null,
|
|
|
|
"body": {
|
|
|
|
"SetOperation": {
|
|
|
|
"op": "Union",
|
|
|
|
"all": true,
|
|
|
|
"left": {
|
|
|
|
"Select": {
|
|
|
|
"distinct": false,
|
|
|
|
"top": null,
|
|
|
|
"projection": [
|
|
|
|
{
|
|
|
|
"UnnamedExpr": {
|
|
|
|
"Identifier": {
|
|
|
|
"value": "col1",
|
|
|
|
"quote_style": "\""
|
|
|
|
}
|
|
|
|
}
|
|
|
|
},
|
|
|
|
{
|
|
|
|
"UnnamedExpr": {
|
|
|
|
"Identifier": {
|
|
|
|
"value": "col2",
|
|
|
|
"quote_style": "\""
|
|
|
|
}
|
|
|
|
}
|
|
|
|
},
|
|
|
|
{
|
|
|
|
"UnnamedExpr": {
|
|
|
|
"Identifier": {
|
|
|
|
"value": "account_id",
|
|
|
|
"quote_style": "\""
|
|
|
|
}
|
|
|
|
}
|
|
|
|
},
|
|
|
|
{
|
|
|
|
"UnnamedExpr": {
|
|
|
|
"Identifier": {
|
|
|
|
"value": "amount",
|
|
|
|
"quote_style": "\""
|
|
|
|
}
|
|
|
|
}
|
|
|
|
}
|
|
|
|
],
|
|
|
|
"from": [
|
|
|
|
{
|
|
|
|
"relation": {
|
|
|
|
"Table": {
|
|
|
|
"name": [
|
|
|
|
{
|
|
|
|
"value": "dev__testdblongtext__transactions_history",
|
|
|
|
"quote_style": "\""
|
|
|
|
}
|
|
|
|
],
|
|
|
|
"alias": null,
|
|
|
|
"args": [],
|
|
|
|
"with_hints": []
|
|
|
|
}
|
|
|
|
},
|
|
|
|
"joins": []
|
|
|
|
}
|
|
|
|
],
|
|
|
|
"lateral_views": [],
|
|
|
|
"selection": {
|
|
|
|
"BinaryOp": {
|
|
|
|
"left": {
|
|
|
|
"BinaryOp": {
|
|
|
|
"left": {
|
|
|
|
"Identifier": {
|
|
|
|
"value": "sys_from",
|
|
|
|
"quote_style": "\""
|
|
|
|
}
|
|
|
|
},
|
|
|
|
"op": "LtEq",
|
|
|
|
"right": {
|
|
|
|
"Value": {
|
|
|
|
"Number": [
|
|
|
|
"0",
|
|
|
|
false
|
|
|
|
]
|
|
|
|
}
|
|
|
|
}
|
|
|
|
}
|
|
|
|
},
|
|
|
|
"op": "And",
|
|
|
|
"right": {
|
|
|
|
"BinaryOp": {
|
|
|
|
"left": {
|
|
|
|
"Identifier": {
|
|
|
|
"value": "sys_to",
|
|
|
|
"quote_style": "\""
|
|
|
|
}
|
|
|
|
},
|
|
|
|
"op": "GtEq",
|
|
|
|
"right": {
|
|
|
|
"Value": {
|
|
|
|
"Number": [
|
|
|
|
"0",
|
|
|
|
false
|
|
|
|
]
|
|
|
|
}
|
|
|
|
}
|
|
|
|
}
|
|
|
|
}
|
|
|
|
}
|
|
|
|
},
|
|
|
|
"group_by": [],
|
|
|
|
"cluster_by": [],
|
|
|
|
"distribute_by": [],
|
|
|
|
"sort_by": [],
|
|
|
|
"having": null
|
|
|
|
}
|
|
|
|
},
|
|
|
|
"right": {
|
|
|
|
"Select": {
|
|
|
|
"distinct": false,
|
|
|
|
"top": null,
|
|
|
|
"projection": [
|
|
|
|
{
|
|
|
|
"UnnamedExpr": {
|
|
|
|
"Identifier": {
|
|
|
|
"value": "col1",
|
|
|
|
"quote_style": "\""
|
|
|
|
}
|
|
|
|
}
|
|
|
|
},
|
|
|
|
{
|
|
|
|
"UnnamedExpr": {
|
|
|
|
"Identifier": {
|
|
|
|
"value": "col2",
|
|
|
|
"quote_style": "\""
|
|
|
|
}
|
|
|
|
}
|
|
|
|
},
|
|
|
|
{
|
|
|
|
"UnnamedExpr": {
|
|
|
|
"Identifier": {
|
|
|
|
"value": "account_id",
|
|
|
|
"quote_style": "\""
|
|
|
|
}
|
|
|
|
}
|
|
|
|
},
|
|
|
|
{
|
|
|
|
"UnnamedExpr": {
|
|
|
|
"Identifier": {
|
|
|
|
"value": "amount",
|
|
|
|
"quote_style": "\""
|
|
|
|
}
|
|
|
|
}
|
|
|
|
}
|
|
|
|
],
|
|
|
|
"from": [
|
|
|
|
{
|
|
|
|
"relation": {
|
|
|
|
"Table": {
|
|
|
|
"name": [
|
|
|
|
{
|
|
|
|
"value": "dev__testdblongtext__transactions_actual",
|
|
|
|
"quote_style": "\""
|
|
|
|
}
|
|
|
|
],
|
|
|
|
"alias": null,
|
|
|
|
"args": [],
|
|
|
|
"with_hints": []
|
|
|
|
}
|
|
|
|
},
|
|
|
|
"joins": []
|
|
|
|
}
|
|
|
|
],
|
|
|
|
"lateral_views": [],
|
|
|
|
"selection": {
|
|
|
|
"BinaryOp": {
|
|
|
|
"left": {
|
|
|
|
"Identifier": {
|
|
|
|
"value": "sys_from",
|
|
|
|
"quote_style": "\""
|
|
|
|
}
|
|
|
|
},
|
|
|
|
"op": "LtEq",
|
|
|
|
"right": {
|
|
|
|
"Value": {
|
|
|
|
"Number": [
|
|
|
|
"0",
|
|
|
|
false
|
|
|
|
]
|
|
|
|
}
|
|
|
|
}
|
|
|
|
}
|
|
|
|
},
|
|
|
|
"group_by": [],
|
|
|
|
"cluster_by": [],
|
|
|
|
"distribute_by": [],
|
|
|
|
"sort_by": [],
|
|
|
|
"having": null
|
|
|
|
}
|
|
|
|
}
|
|
|
|
}
|
|
|
|
},
|
|
|
|
"order_by": [],
|
|
|
|
"limit": null,
|
|
|
|
"offset": null,
|
|
|
|
"fetch": null
|
|
|
|
},
|
|
|
|
"alias": {
|
|
|
|
"name": {
|
|
|
|
"value": "t3",
|
|
|
|
"quote_style": "\""
|
|
|
|
},
|
|
|
|
"columns": []
|
|
|
|
}
|
|
|
|
}
|
|
|
|
},
|
|
|
|
"joins": [
|
|
|
|
{
|
|
|
|
"relation": {
|
|
|
|
"Derived": {
|
|
|
|
"lateral": false,
|
|
|
|
"subquery": {
|
|
|
|
"with": null,
|
|
|
|
"body": {
|
|
|
|
"SetOperation": {
|
|
|
|
"op": "Union",
|
|
|
|
"all": true,
|
|
|
|
"left": {
|
|
|
|
"Select": {
|
|
|
|
"distinct": false,
|
|
|
|
"top": null,
|
|
|
|
"projection": [
|
|
|
|
{
|
|
|
|
"UnnamedExpr": {
|
|
|
|
"Identifier": {
|
|
|
|
"value": "id",
|
|
|
|
"quote_style": "\""
|
|
|
|
}
|
|
|
|
}
|
|
|
|
},
|
|
|
|
{
|
|
|
|
"UnnamedExpr": {
|
|
|
|
"Identifier": {
|
|
|
|
"value": "cola",
|
|
|
|
"quote_style": "\""
|
|
|
|
}
|
|
|
|
}
|
|
|
|
},
|
|
|
|
{
|
|
|
|
"UnnamedExpr": {
|
|
|
|
"Identifier": {
|
|
|
|
"value": "colb",
|
|
|
|
"quote_style": "\""
|
|
|
|
}
|
|
|
|
}
|
|
|
|
}
|
|
|
|
],
|
|
|
|
"from": [
|
|
|
|
{
|
|
|
|
"relation": {
|
|
|
|
"Table": {
|
|
|
|
"name": [
|
|
|
|
{
|
|
|
|
"value": "dev__testdblongtext__accounts_history",
|
|
|
|
"quote_style": "\""
|
|
|
|
}
|
|
|
|
],
|
|
|
|
"alias": null,
|
|
|
|
"args": [],
|
|
|
|
"with_hints": []
|
|
|
|
}
|
|
|
|
},
|
|
|
|
"joins": []
|
|
|
|
}
|
|
|
|
],
|
|
|
|
"lateral_views": [],
|
|
|
|
"selection": {
|
|
|
|
"BinaryOp": {
|
|
|
|
"left": {
|
|
|
|
"BinaryOp": {
|
|
|
|
"left": {
|
|
|
|
"Identifier": {
|
|
|
|
"value": "sys_from",
|
|
|
|
"quote_style": "\""
|
|
|
|
}
|
|
|
|
},
|
|
|
|
"op": "LtEq",
|
|
|
|
"right": {
|
|
|
|
"Value": {
|
|
|
|
"Number": [
|
|
|
|
"0",
|
|
|
|
false
|
|
|
|
]
|
|
|
|
}
|
|
|
|
}
|
|
|
|
}
|
|
|
|
},
|
|
|
|
"op": "And",
|
|
|
|
"right": {
|
|
|
|
"BinaryOp": {
|
|
|
|
"left": {
|
|
|
|
"Identifier": {
|
|
|
|
"value": "sys_to",
|
|
|
|
"quote_style": "\""
|
|
|
|
}
|
|
|
|
},
|
|
|
|
"op": "GtEq",
|
|
|
|
"right": {
|
|
|
|
"Value": {
|
|
|
|
"Number": [
|
|
|
|
"0",
|
|
|
|
false
|
|
|
|
]
|
|
|
|
}
|
|
|
|
}
|
|
|
|
}
|
|
|
|
}
|
|
|
|
}
|
|
|
|
},
|
|
|
|
"group_by": [],
|
|
|
|
"cluster_by": [],
|
|
|
|
"distribute_by": [],
|
|
|
|
"sort_by": [],
|
|
|
|
"having": null
|
|
|
|
}
|
|
|
|
},
|
|
|
|
"right": {
|
|
|
|
"Select": {
|
|
|
|
"distinct": false,
|
|
|
|
"top": null,
|
|
|
|
"projection": [
|
|
|
|
{
|
|
|
|
"UnnamedExpr": {
|
|
|
|
"Identifier": {
|
|
|
|
"value": "id",
|
|
|
|
"quote_style": "\""
|
|
|
|
}
|
|
|
|
}
|
|
|
|
},
|
|
|
|
{
|
|
|
|
"UnnamedExpr": {
|
|
|
|
"Identifier": {
|
|
|
|
"value": "cola",
|
|
|
|
"quote_style": "\""
|
|
|
|
}
|
|
|
|
}
|
|
|
|
},
|
|
|
|
{
|
|
|
|
"UnnamedExpr": {
|
|
|
|
"Identifier": {
|
|
|
|
"value": "colb",
|
|
|
|
"quote_style": "\""
|
|
|
|
}
|
|
|
|
}
|
|
|
|
}
|
|
|
|
],
|
|
|
|
"from": [
|
|
|
|
{
|
|
|
|
"relation": {
|
|
|
|
"Table": {
|
|
|
|
"name": [
|
|
|
|
{
|
|
|
|
"value": "dev__testdblongtext__accounts_actual",
|
|
|
|
"quote_style": "\""
|
|
|
|
}
|
|
|
|
],
|
|
|
|
"alias": null,
|
|
|
|
"args": [],
|
|
|
|
"with_hints": []
|
|
|
|
}
|
|
|
|
},
|
|
|
|
"joins": []
|
|
|
|
}
|
|
|
|
],
|
|
|
|
"lateral_views": [],
|
|
|
|
"selection": {
|
|
|
|
"BinaryOp": {
|
|
|
|
"left": {
|
|
|
|
"Identifier": {
|
|
|
|
"value": "sys_from",
|
|
|
|
"quote_style": "\""
|
|
|
|
}
|
|
|
|
},
|
|
|
|
"op": "LtEq",
|
|
|
|
"right": {
|
|
|
|
"Value": {
|
|
|
|
"Number": [
|
|
|
|
"0",
|
|
|
|
false
|
|
|
|
]
|
|
|
|
}
|
|
|
|
}
|
|
|
|
}
|
|
|
|
},
|
|
|
|
"group_by": [],
|
|
|
|
"cluster_by": [],
|
|
|
|
"distribute_by": [],
|
|
|
|
"sort_by": [],
|
|
|
|
"having": null
|
|
|
|
}
|
|
|
|
}
|
|
|
|
}
|
|
|
|
},
|
|
|
|
"order_by": [],
|
|
|
|
"limit": null,
|
|
|
|
"offset": null,
|
|
|
|
"fetch": null
|
|
|
|
},
|
|
|
|
"alias": {
|
|
|
|
"name": {
|
|
|
|
"value": "t8",
|
|
|
|
"quote_style": "\""
|
|
|
|
},
|
|
|
|
"columns": []
|
|
|
|
}
|
|
|
|
}
|
|
|
|
},
|
|
|
|
"join_operator": {
|
|
|
|
"Inner": {
|
|
|
|
"On": {
|
|
|
|
"BinaryOp": {
|
|
|
|
"left": {
|
|
|
|
"CompoundIdentifier": [
|
|
|
|
{
|
|
|
|
"value": "t3",
|
|
|
|
"quote_style": "\""
|
|
|
|
},
|
|
|
|
{
|
|
|
|
"value": "account_id",
|
|
|
|
"quote_style": "\""
|
|
|
|
}
|
|
|
|
]
|
|
|
|
},
|
|
|
|
"op": "Eq",
|
|
|
|
"right": {
|
|
|
|
"CompoundIdentifier": [
|
|
|
|
{
|
|
|
|
"value": "t8",
|
|
|
|
"quote_style": "\""
|
|
|
|
},
|
|
|
|
{
|
|
|
|
"value": "id",
|
|
|
|
"quote_style": "\""
|
|
|
|
}
|
|
|
|
]
|
|
|
|
}
|
|
|
|
}
|
|
|
|
}
|
|
|
|
}
|
|
|
|
}
|
|
|
|
}
|
|
|
|
]
|
|
|
|
}
|
|
|
|
],
|
|
|
|
"lateral_views": [],
|
|
|
|
"selection": {
|
|
|
|
"BinaryOp": {
|
|
|
|
"left": {
|
|
|
|
"BinaryOp": {
|
|
|
|
"left": {
|
|
|
|
"BinaryOp": {
|
|
|
|
"left": {
|
|
|
|
"CompoundIdentifier": [
|
|
|
|
{
|
|
|
|
"value": "t3",
|
|
|
|
"quote_style": "\""
|
|
|
|
},
|
|
|
|
{
|
|
|
|
"value": "col1",
|
|
|
|
"quote_style": "\""
|
|
|
|
}
|
|
|
|
]
|
|
|
|
},
|
|
|
|
"op": "Eq",
|
|
|
|
"right": {
|
|
|
|
"Value": {
|
|
|
|
"Number": [
|
|
|
|
"1",
|
|
|
|
false
|
|
|
|
]
|
|
|
|
}
|
|
|
|
}
|
|
|
|
}
|
|
|
|
},
|
|
|
|
"op": "And",
|
|
|
|
"right": {
|
|
|
|
"BinaryOp": {
|
|
|
|
"left": {
|
|
|
|
"CompoundIdentifier": [
|
|
|
|
{
|
|
|
|
"value": "t3",
|
|
|
|
"quote_style": "\""
|
|
|
|
},
|
|
|
|
{
|
|
|
|
"value": "col2",
|
|
|
|
"quote_style": "\""
|
|
|
|
}
|
|
|
|
]
|
|
|
|
},
|
|
|
|
"op": "Eq",
|
|
|
|
"right": {
|
|
|
|
"Value": {
|
|
|
|
"Number": [
|
|
|
|
"2",
|
|
|
|
false
|
|
|
|
]
|
|
|
|
}
|
|
|
|
}
|
|
|
|
}
|
|
|
|
}
|
|
|
|
}
|
|
|
|
},
|
|
|
|
"op": "And",
|
|
|
|
"right": {
|
|
|
|
"Nested": {
|
|
|
|
"BinaryOp": {
|
|
|
|
"left": {
|
|
|
|
"BinaryOp": {
|
|
|
|
"left": {
|
|
|
|
"CompoundIdentifier": [
|
|
|
|
{
|
|
|
|
"value": "t8",
|
|
|
|
"quote_style": "\""
|
|
|
|
},
|
|
|
|
{
|
|
|
|
"value": "cola",
|
|
|
|
"quote_style": "\""
|
|
|
|
}
|
|
|
|
]
|
|
|
|
},
|
|
|
|
"op": "Eq",
|
|
|
|
"right": {
|
|
|
|
"Value": {
|
|
|
|
"Number": [
|
|
|
|
"1",
|
|
|
|
false
|
|
|
|
]
|
|
|
|
}
|
|
|
|
}
|
|
|
|
}
|
|
|
|
},
|
|
|
|
"op": "And",
|
|
|
|
"right": {
|
|
|
|
"BinaryOp": {
|
|
|
|
"left": {
|
|
|
|
"CompoundIdentifier": [
|
|
|
|
{
|
|
|
|
"value": "t8",
|
|
|
|
"quote_style": "\""
|
|
|
|
},
|
|
|
|
{
|
|
|
|
"value": "colb",
|
|
|
|
"quote_style": "\""
|
|
|
|
}
|
|
|
|
]
|
|
|
|
},
|
|
|
|
"op": "Eq",
|
|
|
|
"right": {
|
|
|
|
"Value": {
|
|
|
|
"Number": [
|
|
|
|
"2",
|
|
|
|
false
|
|
|
|
]
|
|
|
|
}
|
|
|
|
}
|
|
|
|
}
|
|
|
|
}
|
|
|
|
}
|
|
|
|
}
|
|
|
|
}
|
|
|
|
}
|
|
|
|
},
|
|
|
|
"group_by": [],
|
|
|
|
"cluster_by": [],
|
|
|
|
"distribute_by": [],
|
|
|
|
"sort_by": [],
|
|
|
|
"having": null
|
|
|
|
}
|
|
|
|
},
|
|
|
|
"order_by": [],
|
|
|
|
"limit": null,
|
|
|
|
"offset": null,
|
|
|
|
"fetch": null
|
|
|
|
}
|
|
|
|
}
|
|
|
|
]
|
|
|
|
``` |
|
|
|
\ No newline at end of file |
