docs/_docs/sql-reference/grammar-reference.adoc

// Licensed to the Apache Software Foundation (ASF) under one or more
// contributor license agreements.  See the NOTICE file distributed with
// this work for additional information regarding copyright ownership.
// The ASF licenses this file to You under the Apache License, Version 2.0
// (the "License"); you may not use this file except in compliance with
// the License.  You may obtain a copy of the License at
//
// http://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.
= Grammar Reference

This section describes grammar elements that are common to multiple SQL functions (link:sql-reference/ddl[DDL], link:sql-reference/distribution-zones[Distribution Zones], etc.).

== assign

[.diagram-container]
Diagram(
NonTerminal('identifier '),
Terminal('='),
NonTerminal('expression')
)

=== Parameters

- `identifier` - the name of table, column or other element that will be updated by the operation.
- `expression` - a valid SQL expression that returns the values that must be assigned to the `identifier`.

Referenced by:

* link:sql-reference/dml#merge[MERGE]
* link:sql-reference/dml#update[UPDATE]

'''

== column_definition

[.diagram-container]
Diagram(
Sequence(
NonTerminal('column_name'),
NonTerminal('DATA TYPE', {href:'./data-types'}),
Optional(Sequence(Optional('NOT'),Terminal('NULL')))
),
End({type:'complex'})
)

[.diagram-container]
Diagram(
Start({type:'complex'}),
Sequence(
Optional(Sequence(Terminal('DEFAULT'), Choice(1,NonTerminal('identifier'),
NonTerminal('literal_value'),
Sequence(Terminal('CURRENT_TIMESTAMP'), Sequence(Terminal('+'),Terminal('INTERVAL'), NonTerminal('interval'))
)
),)),
Optional(Terminal('PRIMARY KEY')),
))

Keywords and parameters:

* `column_name` - a column name.
* `DATA TYPE` - the link:sql-reference/data-types[data type] allowed in the column.
* `identifier` - the random identifier for the row. Can be generated by using the <<rand_uuid>> function.
* `literal_value` - a value to be assigned as default.
* `CURRENT_TIMESTAMP` - the function that returns current time. Can only be used for `TIMESTAMP WITH LOCAL TIME ZONE` columns.
* `interval` - the time interval by which the timestamp will be offset. Required if `CURRENT_TIMESTAMP` is used. The interval can be specified in:
+
- `SECONDS`
- `MINUTES`
- `HOURS`
- `DAYS`
- `MONTHS`
+
If the interval is `0`, current time is used.

Referenced by:

* link:sql-reference/ddl#сreate-table[CREATE TABLE]
* link:sql-reference/ddl#сreate-cache[CREATE CACHE]
* link:sql-reference/ddl#alter-table[ALTER TABLE]
* <<project_item>>
* <<join_condition>>

'''

== column_definition_or_list

[.diagram-container]
Diagram(
Choice(0,
Sequence(
Choice(0,
Sequence(
Choice(0,Sequence(
NonTerminal('column_name'),
NonTerminal('data_type'))
)),
Sequence(
Terminal('('),
OneOrMore(Sequence(
NonTerminal('column_name'),
NonTerminal('data_type')),
Terminal(',')
),Terminal(')')
)))))

Keywords and parameters:

* `column_name` - a column name.
* `data_type` - a valid link:sql-reference/data-types[data type].


Referenced by:

* link:sql-reference/ddl#alter-table[ALTER TABLE]
* <<with_item>>

'''

== column_list

[.diagram-container]
Diagram(
Terminal('('),
OneOrMore(Sequence(
NonTerminal('column_name')),
Terminal(',')
),Terminal(')')
)


Keywords and parameters:

* `column_name` - a column name.


Referenced by:

* link:sql-reference/dml#insert[INSERT]
* <<join_condition >>

'''

== column_name_or_list

[.diagram-container]
Diagram(
Choice(0,
Sequence(
Choice(0,
Sequence(
Choice(0,Sequence(
NonTerminal('column_name'))
)),
Sequence(
Terminal('('),
OneOrMore(Sequence(
NonTerminal('column_name')),
Terminal(',')
),Terminal(')')
)))))


Keywords and parameters:

* `column_name` - a column name.


Referenced by:

* link:sql-reference/ddl#alter-table[ALTER TABLE]

'''

== constraint

[.diagram-container]
Diagram(Sequence(
Optional(Sequence(Terminal('CONSTRAINT'),NonTerminal('constraint_name')
)),
Terminal('PRIMARY KEY'),
Optional(
Choice(0,
Sequence(
Terminal('USING'),
Choice (0,
Sequence(Terminal('SORTED'), NonTerminal('sorted_column_list', {href:'./grammar-reference/#sorted_column_list'})
),
Sequence('HASH', NonTerminal('column_list', {href:'./grammar-reference/#column_list'})))
))
)))

Keywords and parameters:

* `constraint_name` - a name of the constraint.

Referenced by:

* link:sql-reference/ddl#create-table[CREATE TABLE]
* link:sql-reference/ddl#create-cache[CREATE CACHE]

'''

== group_item

[.diagram-container]
Diagram(
Choice(0,
NonTerminal('expression'),
Sequence(
Terminal('('), Terminal(')'),
),
Sequence(
Terminal('('), Sequence(OneOrMore(NonTerminal('expression'), Terminal(',')), ), Terminal(')'),
),
))

=== Parameters

- `expression` - a valid SQL expression that returns the values that must be assigned to the `identifier`.

Referenced by:

* link:sql-reference/dml#select[SELECT]

'''

== join_condition

[.diagram-container]
Diagram(
Choice(0,
Sequence(
Terminal('ON'), NonTerminal('boolean_expression'),
),
Sequence(Terminal('USING'), NonTerminal('column_list', {href:'./grammar-reference/#column_list'})),
))

=== Parameters

- `boolean_expression` - an SQL expression that returns a boolean value. Only the records for which `TRUE` was returned will be returned. If not specified, all matching records are returned.

Referenced by:

* <<table_expression>>

'''

== order_item

[.diagram-container]
Diagram(
NonTerminal('expression'),
Optional(Choice(0,
Terminal('ASC'),
Terminal('DESC')
)),
Optional(Choice(0,
Terminal('NULLS FIRST'),
Terminal('NULLS LAST')
)),
)

=== Parameters

- `expression` - a valid SQL expression that denotes the specific item in the SELECT clause.


Referenced by:

* <<query>>

'''

== parameter

[.diagram-container]
Diagram(
NonTerminal('parameter_name'),
Terminal('='),
NonTerminal('parameter_value'))

Parameters:

* `parameter_name` - the name of the parameter.
* `parameter_value` - the value of the parameter.

When a parameter is specified, you can provide it as a literal value or as an identifier. For example:

----
CREATE ZONE test_zone;
CREATE TABLE test_table (id INT PRIMARY KEY, val INT) WITH PRIMARY_ZONE=test_zone;
----

In this case, `test_zone` is the identifier, and is used as an identifier. When used like this, the parameters are not case-sensitive.

----
CREATE ZONE "test_zone";
CREATE TABLE test_table (id INT PRIMARY KEY, val INT) WITH PRIMARY_ZONE='test_zone';
----

In this case, `test_zone` is created as a literal value, and is used as a literal. When used like this, the parameter is case-sensitive.

----
CREATE ZONE test_zone;
CREATE TABLE test_table (id INT PRIMARY KEY, val INT) WITH PRIMARY_ZONE=`TEST_ZONE`;
----

In this case, `test_zone` is created as an identifier, and is case-insensitive. As such, when `TEST_ZONE` is used as a literal, it still matches the identifier.


Referenced by:

* link:sql-reference/distribution-zones#create-zone[CREATE ZONE]
* link:sql-reference/distribution-zones#alter-zone[ALTER ZONE]

'''

== project_item

[.diagram-container]
Diagram(
Choice(0,Sequence(
NonTerminal('expression'),
Optional('AS'),
NonTerminal('column_definition', {href:'./grammar-reference/#column_definition'})
),
Sequence(NonTerminal('table_alias'), Terminal('.'), Terminal('\*')),
Terminal('*')
)
)

=== Parameters

- `expression` - a valid SQL expression that denotes the specific item in the SELECT clause.
- `table_alias` - a qualified table alias to use.

Referenced by:

* link:sql-reference/dml#select[SELECT]
* <<select_without_from>>

'''

== qualified_table_name

[.diagram-container]
Diagram(Sequence(
Optional(Sequence(NonTerminal('schema'),NonTerminal('.')
),),
NonTerminal('table_name')
),
)

Keywords and parameters:

* `schema` - a name of the table schema.
* `table_name` - a name of the table.

Referenced by:

* link:sql-reference/ddl#create-table[CREATE TABLE]
* link:sql-reference/ddl#alter-table[ALTER TABLE]
* link:sql-reference/ddl#drop-table[DROP TABLE]
* link:sql-reference/ddl#create-index[CREATE INDEX]
* link:sql-reference/dml#delete[DELETE]
* link:sql-reference/dml#insert[INSERT]
* link:sql-reference/dml#merge[MERGE]
* link:sql-reference/dml#update[UPDATE]
* <<table_primary>>

'''

== query

[.diagram-container]
Diagram(
Choice(0,
Sequence(Terminal('WITH'),
OneOrMore(
NonTerminal('with_item', {href:'./grammar-reference/#with_item'}), Terminal(',')), NonTerminal('query', {href:'./grammar-reference/#query'})),
Sequence(
Choice(1,
Terminal('SELECT', {href:'./operational-commands/#select'}),
Terminal('select_without_from', {href:'./grammar-reference/#select_without_from'}),
Sequence(NonTerminal('query', {href:'./grammar-reference/#query'}), Choice(0, Terminal('UNION'), Terminal('EXCEPT'),Terminal('MINUS'),  Terminal('INTERSECT')), Optional(Choice(0, Terminal('ALL'), Terminal('DISTINCT'))),NonTerminal('query', {href:'./grammar-reference/#query'}))
),
Optional(Sequence(
Terminal('ORDER BY'),
OneOrMore(NonTerminal('order_item', {href:'./grammar-reference/#order_item'}), Terminal(',')),
)),
Optional(Sequence(
Terminal('LIMIT'),
Optional(NonTerminal('start')),
Choice(0, NonTerminal('count'), Terminal('ALL'))
)),
Optional(Sequence(
Terminal('OFFSET'),
NonTerminal('start'),
Choice(0, Terminal('ROW'), Terminal('ROWS'))
)),
Optional(Sequence(
Terminal('FETCH'),
Choice(0, Terminal('FIRST'), Terminal('NEXT')),
Optional(NonTerminal('count')),
Choice(0, Terminal('ROW'), Terminal('ROWS')),
Terminal('ONLY'))
)
),
),
End({type:'complex'})
)

=== Parameters

- `expression` - a valid SQL expression.
- `start` - the number of result to start the query from.
- `count` - the number of values to fetch.


Referenced by:

* link:sql-reference/dml#insert[INSERT]
* <<with_item>>
* <<table_primary>>

'''

== select_without_from

[.diagram-container]
Diagram(
Terminal('SELECT', {href:'./operational-commands/#select'}),
Optional(
Choice(0,
Terminal('ALL'),
Terminal('DISTINCT'),
)),
OneOrMore(Sequence(
NonTerminal('project_item', {href:'./grammar-reference/#project_item'})),
Terminal(',')
),
)

Referenced by:

* <<query>>

'''

== sorted_column_list

[.diagram-container]
Diagram(
Sequence(
'(',
OneOrMore(
Sequence(
NonTerminal('column_name'),
Optional(
Choice(0, Terminal('ASC'), Terminal('DESC')
)),
Optional(
Sequence(
Terminal('NULLS'),
Choice(0, Terminal('FIRST'), Terminal('LAST'))
))),
','
),
')'
))


Keywords and parameters:

* `column_name` - a column name.
* `NULLS FIRST` - if specified, places any NULL values before all non-NULL in that column's ordering.
* `NULLS LAST` - if specified, places NULLs after all non-NULLs in that column's ordering.


Referenced by:

* link:sql-reference/ddl#create-index[CREATE INDEX]
* <<constraint>>

'''

== table_expression

[.diagram-container]
Diagram(
Choice(0,
Sequence(
Choice(0,
Sequence(
Choice(0,Sequence(
NonTerminal('table_expression', {href:'./grammar-reference/#table_expression'}),
Optional('NATURAL'),
Optional(Sequence(Choice(0,
Terminal('LEFT'),
Terminal('RIGHT'),
Terminal('FULL')
),
Optional('OUTER')
)),
Terminal('JOIN'),
NonTerminal('table_expression', {href:'./grammar-reference/#table_expression'}),
Optional(NonTerminal('join_condition', {href:'./grammar-reference/#join_condition'}))
),
)),
Sequence(
Choice(0,Sequence(
NonTerminal('table_expression', {href:'./grammar-reference/#table_expression'}),
Terminal('CROSS JOIN'),
NonTerminal('table_expression', {href:'./grammar-reference/#table_expression'}))
)),
Sequence(
OneOrMore(Sequence(
NonTerminal('table_reference', {href:'./grammar-reference/#table_reference'})),
Terminal(',')
)
)))))

=== Parameters


Referenced by:

* link:sql-reference/dml#select[SELECT]

'''

== table_primary

[.diagram-container]
Diagram(
NonTerminal('qualified_table_name', {href:'./grammar-reference/#qualified_table_name'}),
Terminal('('),
Terminal('TABLE'),
NonTerminal('qualified_table_name', {href:'./grammar-reference/#qualified_table_name'}),
Terminal(')')
,
End({type:'complex'})
)


[.diagram-container]
Diagram(
Start({type:'complex'}),
Choice(0,
Sequence(
NonTerminal('table_primary', {href:'./grammar-reference/#table_primary'}), Optional(NonTerminal('hint_comment'))),
Sequence(
Terminal('('), NonTerminal('query', {href:'./grammar-reference/#query'}), Terminal(')')
),
Sequence(
Terminal('TABLE'), Terminal('('), NonTerminal('function_name'), Terminal('('), OneOrMore('expression', Terminal(',')),  Terminal(')'),  Terminal(')')
))
)

=== Parameters

- `hint_comment` - an sql link:sql-tuning#optimizer-hints[optimizer hint].
- `expression` - a valid SQL expression.
- `function_name` - the name of the link:sql-reference/operators-and-functions[SQL function] to use.

Referenced by:

* <<table_reference>>

== table_reference

[.diagram-container]
Diagram(
NonTerminal('table_primary', {href:'./grammar-reference/#table_primary'}),
Optional(Sequence(
Optional('AS'), NonTerminal('alias'), Optional(Sequence(Terminal('('), OneOrMore('column_alias', Terminal(',')),  Terminal(')')))
))
)

=== Parameters

- `alias` - the alias that will be used for the table.
- `column_alias` - the alias used for column.


Referenced by:

* <<table_expression>>

'''

== with_item

[.diagram-container]
Diagram(
NonTerminal('item_name'),
Optional(NonTerminal('column_list', {href:'./grammar-reference/#column_list'})),
Terminal('AS'),
Terminal('('),
NonTerminal('query', {href:'./grammar-reference/#query'}),
Terminal(')'),
)

Referenced by:

* <<query>>

'''