blob: 22061c5edeed201dc6ad98a4176197c7f6052537 [file] [log] [blame]
<!DOCTYPE HTML>
<html lang="en-US">
<head>
<meta charset="UTF-8">
<title>SQL language</title>
<meta name="viewport" content="width=device-width,initial-scale=1">
<meta name="generator" content="Jekyll v3.7.3">
<link rel="stylesheet" href="//fonts.googleapis.com/css?family=Lato:300,300italic,400,400italic,700,700italic,900">
<link rel="stylesheet" href="/css/screen.css">
<link rel="icon" type="image/x-icon" href="/favicon.ico">
<!--[if lt IE 9]>
<script src="/js/html5shiv.min.js"></script>
<script src="/js/respond.min.js"></script>
<![endif]-->
</head>
<body class="wrap">
<header role="banner">
<div class="grid">
<div class="unit center-on-mobiles">
<h1>
<a href="/">
<span class="sr-only">Apache Calcite</span>
<img src="/img/logo.svg" alt="Calcite Logo">
</a>
</h1>
</div>
<nav class="main-nav">
<ul>
<li class="">
<a href="/">Home</a>
</li>
<li class="">
<a href="/downloads/">Download</a>
</li>
<li class="">
<a href="/community/">Community</a>
</li>
<li class="">
<a href="/develop/">Develop</a>
</li>
<li class="">
<a href="/news/">News</a>
</li>
<li class="current">
<a href="/docs/">Docs</a>
</li>
</ul>
</nav>
</div>
</header>
<section class="docs">
<div class="grid">
<div class="docs-nav-mobile unit whole show-on-mobiles">
<select onchange="if (this.value) window.location.href=this.value">
<option value="">Navigate the docs…</option>
<optgroup label="Overview">
</optgroup>
<optgroup label="Advanced">
</optgroup>
<optgroup label="Avatica">
</optgroup>
<optgroup label="Reference">
</optgroup>
<optgroup label="Meta">
</optgroup>
</select>
</div>
<div class="unit four-fifths">
<article>
<h1>SQL language</h1>
<!--
-->
<style>
.container {
width: 400px;
height: 26px;
}
.gray {
width: 60px;
height: 26px;
background: gray;
float: left;
}
.r15 {
width: 40px;
height: 6px;
background: yellow;
margin-top: 4px;
margin-left: 10px;
}
.r12 {
width: 10px;
height: 6px;
background: yellow;
margin-top: 4px;
margin-left: 10px;
}
.r13 {
width: 20px;
height: 6px;
background: yellow;
margin-top: 4px;
margin-left: 10px;
}
.r2 {
width: 2px;
height: 6px;
background: yellow;
margin-top: 4px;
margin-left: 20px;
}
.r24 {
width: 20px;
height: 6px;
background: yellow;
margin-top: 4px;
margin-left: 20px;
}
.r35 {
width: 20px;
height: 6px;
background: yellow;
margin-top: 4px;
margin-left: 30px;
}
</style>
<p>The page describes the SQL dialect recognized by Calcite’s default SQL parser.</p>
<h2 id="grammar">Grammar</h2>
<p>SQL grammar in <a href="https://en.wikipedia.org/wiki/Backus%E2%80%93Naur_Form">BNF</a>-like
form.</p>
<figure class="highlight"><pre><code class="language-sql" data-lang="sql"><span class="k">statement</span><span class="p">:</span>
<span class="n">setStatement</span>
<span class="o">|</span> <span class="n">resetStatement</span>
<span class="o">|</span> <span class="k">explain</span>
<span class="o">|</span> <span class="k">describe</span>
<span class="o">|</span> <span class="k">insert</span>
<span class="o">|</span> <span class="k">update</span>
<span class="o">|</span> <span class="n">merge</span>
<span class="o">|</span> <span class="k">delete</span>
<span class="o">|</span> <span class="n">query</span>
<span class="n">statementList</span><span class="p">:</span>
<span class="k">statement</span> <span class="p">[</span> <span class="s1">';'</span> <span class="k">statement</span> <span class="p">]</span><span class="o">*</span> <span class="p">[</span> <span class="s1">';'</span> <span class="p">]</span>
<span class="n">setStatement</span><span class="p">:</span>
<span class="p">[</span> <span class="k">ALTER</span> <span class="p">(</span> <span class="k">SYSTEM</span> <span class="o">|</span> <span class="k">SESSION</span> <span class="p">)</span> <span class="p">]</span> <span class="k">SET</span> <span class="n">identifier</span> <span class="s1">'='</span> <span class="n">expression</span>
<span class="n">resetStatement</span><span class="p">:</span>
<span class="p">[</span> <span class="k">ALTER</span> <span class="p">(</span> <span class="k">SYSTEM</span> <span class="o">|</span> <span class="k">SESSION</span> <span class="p">)</span> <span class="p">]</span> <span class="k">RESET</span> <span class="n">identifier</span>
<span class="o">|</span> <span class="p">[</span> <span class="k">ALTER</span> <span class="p">(</span> <span class="k">SYSTEM</span> <span class="o">|</span> <span class="k">SESSION</span> <span class="p">)</span> <span class="p">]</span> <span class="k">RESET</span> <span class="k">ALL</span>
<span class="k">explain</span><span class="p">:</span>
<span class="k">EXPLAIN</span> <span class="n">PLAN</span>
<span class="p">[</span> <span class="k">WITH</span> <span class="k">TYPE</span> <span class="o">|</span> <span class="k">WITH</span> <span class="k">IMPLEMENTATION</span> <span class="o">|</span> <span class="k">WITHOUT</span> <span class="k">IMPLEMENTATION</span> <span class="p">]</span>
<span class="p">[</span> <span class="k">EXCLUDING</span> <span class="n">ATTRIBUTES</span> <span class="o">|</span> <span class="k">INCLUDING</span> <span class="p">[</span> <span class="k">ALL</span> <span class="p">]</span> <span class="n">ATTRIBUTES</span> <span class="p">]</span>
<span class="p">[</span> <span class="k">AS</span> <span class="n">JSON</span> <span class="o">|</span> <span class="k">AS</span> <span class="n">XML</span> <span class="p">]</span>
<span class="k">FOR</span> <span class="p">(</span> <span class="n">query</span> <span class="o">|</span> <span class="k">insert</span> <span class="o">|</span> <span class="k">update</span> <span class="o">|</span> <span class="n">merge</span> <span class="o">|</span> <span class="k">delete</span> <span class="p">)</span>
<span class="k">describe</span><span class="p">:</span>
<span class="k">DESCRIBE</span> <span class="k">DATABASE</span> <span class="n">databaseName</span>
<span class="o">|</span> <span class="k">DESCRIBE</span> <span class="k">CATALOG</span> <span class="p">[</span> <span class="n">databaseName</span> <span class="p">.</span> <span class="p">]</span> <span class="n">catalogName</span>
<span class="o">|</span> <span class="k">DESCRIBE</span> <span class="k">SCHEMA</span> <span class="p">[</span> <span class="p">[</span> <span class="n">databaseName</span> <span class="p">.</span> <span class="p">]</span> <span class="n">catalogName</span> <span class="p">]</span> <span class="p">.</span> <span class="n">schemaName</span>
<span class="o">|</span> <span class="k">DESCRIBE</span> <span class="p">[</span> <span class="k">TABLE</span> <span class="p">]</span> <span class="p">[</span> <span class="p">[</span> <span class="p">[</span> <span class="n">databaseName</span> <span class="p">.</span> <span class="p">]</span> <span class="n">catalogName</span> <span class="p">.</span> <span class="p">]</span> <span class="n">schemaName</span> <span class="p">.</span> <span class="p">]</span> <span class="n">tableName</span> <span class="p">[</span> <span class="n">columnName</span> <span class="p">]</span>
<span class="o">|</span> <span class="k">DESCRIBE</span> <span class="p">[</span> <span class="k">STATEMENT</span> <span class="p">]</span> <span class="p">(</span> <span class="n">query</span> <span class="o">|</span> <span class="k">insert</span> <span class="o">|</span> <span class="k">update</span> <span class="o">|</span> <span class="n">merge</span> <span class="o">|</span> <span class="k">delete</span> <span class="p">)</span>
<span class="k">insert</span><span class="p">:</span>
<span class="p">(</span> <span class="k">INSERT</span> <span class="o">|</span> <span class="n">UPSERT</span> <span class="p">)</span> <span class="k">INTO</span> <span class="n">tablePrimary</span>
<span class="p">[</span> <span class="s1">'('</span> <span class="k">column</span> <span class="p">[,</span> <span class="k">column</span> <span class="p">]</span><span class="o">*</span> <span class="s1">')'</span> <span class="p">]</span>
<span class="n">query</span>
<span class="k">update</span><span class="p">:</span>
<span class="k">UPDATE</span> <span class="n">tablePrimary</span>
<span class="k">SET</span> <span class="n">assign</span> <span class="p">[,</span> <span class="n">assign</span> <span class="p">]</span><span class="o">*</span>
<span class="p">[</span> <span class="k">WHERE</span> <span class="n">booleanExpression</span> <span class="p">]</span>
<span class="n">assign</span><span class="p">:</span>
<span class="n">identifier</span> <span class="s1">'='</span> <span class="n">expression</span>
<span class="n">merge</span><span class="p">:</span>
<span class="n">MERGE</span> <span class="k">INTO</span> <span class="n">tablePrimary</span> <span class="p">[</span> <span class="p">[</span> <span class="k">AS</span> <span class="p">]</span> <span class="k">alias</span> <span class="p">]</span>
<span class="k">USING</span> <span class="n">tablePrimary</span>
<span class="k">ON</span> <span class="n">booleanExpression</span>
<span class="p">[</span> <span class="k">WHEN</span> <span class="n">MATCHED</span> <span class="k">THEN</span> <span class="k">UPDATE</span> <span class="k">SET</span> <span class="n">assign</span> <span class="p">[,</span> <span class="n">assign</span> <span class="p">]</span><span class="o">*</span> <span class="p">]</span>
<span class="p">[</span> <span class="k">WHEN</span> <span class="k">NOT</span> <span class="n">MATCHED</span> <span class="k">THEN</span> <span class="k">INSERT</span> <span class="k">VALUES</span> <span class="s1">'('</span> <span class="n">value</span> <span class="p">[</span> <span class="p">,</span> <span class="n">value</span> <span class="p">]</span><span class="o">*</span> <span class="s1">')'</span> <span class="p">]</span>
<span class="k">delete</span><span class="p">:</span>
<span class="k">DELETE</span> <span class="k">FROM</span> <span class="n">tablePrimary</span> <span class="p">[</span> <span class="p">[</span> <span class="k">AS</span> <span class="p">]</span> <span class="k">alias</span> <span class="p">]</span>
<span class="p">[</span> <span class="k">WHERE</span> <span class="n">booleanExpression</span> <span class="p">]</span>
<span class="n">query</span><span class="p">:</span>
<span class="k">values</span>
<span class="o">|</span> <span class="k">WITH</span> <span class="n">withItem</span> <span class="p">[</span> <span class="p">,</span> <span class="n">withItem</span> <span class="p">]</span><span class="o">*</span> <span class="n">query</span>
<span class="o">|</span> <span class="err">{</span>
<span class="k">select</span>
<span class="o">|</span> <span class="n">selectWithoutFrom</span>
<span class="o">|</span> <span class="n">query</span> <span class="k">UNION</span> <span class="p">[</span> <span class="k">ALL</span> <span class="o">|</span> <span class="k">DISTINCT</span> <span class="p">]</span> <span class="n">query</span>
<span class="o">|</span> <span class="n">query</span> <span class="k">EXCEPT</span> <span class="p">[</span> <span class="k">ALL</span> <span class="o">|</span> <span class="k">DISTINCT</span> <span class="p">]</span> <span class="n">query</span>
<span class="o">|</span> <span class="n">query</span> <span class="n">MINUS</span> <span class="p">[</span> <span class="k">ALL</span> <span class="o">|</span> <span class="k">DISTINCT</span> <span class="p">]</span> <span class="n">query</span>
<span class="o">|</span> <span class="n">query</span> <span class="k">INTERSECT</span> <span class="p">[</span> <span class="k">ALL</span> <span class="o">|</span> <span class="k">DISTINCT</span> <span class="p">]</span> <span class="n">query</span>
<span class="err">}</span>
<span class="p">[</span> <span class="k">ORDER</span> <span class="k">BY</span> <span class="n">orderItem</span> <span class="p">[,</span> <span class="n">orderItem</span> <span class="p">]</span><span class="o">*</span> <span class="p">]</span>
<span class="p">[</span> <span class="k">LIMIT</span> <span class="p">[</span> <span class="k">start</span><span class="p">,</span> <span class="p">]</span> <span class="err">{</span> <span class="k">count</span> <span class="o">|</span> <span class="k">ALL</span> <span class="err">}</span> <span class="p">]</span>
<span class="p">[</span> <span class="k">OFFSET</span> <span class="k">start</span> <span class="err">{</span> <span class="k">ROW</span> <span class="o">|</span> <span class="k">ROWS</span> <span class="err">}</span> <span class="p">]</span>
<span class="p">[</span> <span class="k">FETCH</span> <span class="err">{</span> <span class="k">FIRST</span> <span class="o">|</span> <span class="k">NEXT</span> <span class="err">}</span> <span class="p">[</span> <span class="k">count</span> <span class="p">]</span> <span class="err">{</span> <span class="k">ROW</span> <span class="o">|</span> <span class="k">ROWS</span> <span class="err">}</span> <span class="k">ONLY</span> <span class="p">]</span>
<span class="n">withItem</span><span class="p">:</span>
<span class="n">name</span>
<span class="p">[</span> <span class="s1">'('</span> <span class="k">column</span> <span class="p">[,</span> <span class="k">column</span> <span class="p">]</span><span class="o">*</span> <span class="s1">')'</span> <span class="p">]</span>
<span class="k">AS</span> <span class="s1">'('</span> <span class="n">query</span> <span class="s1">')'</span>
<span class="n">orderItem</span><span class="p">:</span>
<span class="n">expression</span> <span class="p">[</span> <span class="k">ASC</span> <span class="o">|</span> <span class="k">DESC</span> <span class="p">]</span> <span class="p">[</span> <span class="n">NULLS</span> <span class="k">FIRST</span> <span class="o">|</span> <span class="n">NULLS</span> <span class="k">LAST</span> <span class="p">]</span>
<span class="k">select</span><span class="p">:</span>
<span class="k">SELECT</span> <span class="p">[</span> <span class="n">STREAM</span> <span class="p">]</span> <span class="p">[</span> <span class="k">ALL</span> <span class="o">|</span> <span class="k">DISTINCT</span> <span class="p">]</span>
<span class="err">{</span> <span class="o">*</span> <span class="o">|</span> <span class="n">projectItem</span> <span class="p">[,</span> <span class="n">projectItem</span> <span class="p">]</span><span class="o">*</span> <span class="err">}</span>
<span class="k">FROM</span> <span class="n">tableExpression</span>
<span class="p">[</span> <span class="k">WHERE</span> <span class="n">booleanExpression</span> <span class="p">]</span>
<span class="p">[</span> <span class="k">GROUP</span> <span class="k">BY</span> <span class="err">{</span> <span class="n">groupItem</span> <span class="p">[,</span> <span class="n">groupItem</span> <span class="p">]</span><span class="o">*</span> <span class="err">}</span> <span class="p">]</span>
<span class="p">[</span> <span class="k">HAVING</span> <span class="n">booleanExpression</span> <span class="p">]</span>
<span class="p">[</span> <span class="n">WINDOW</span> <span class="n">windowName</span> <span class="k">AS</span> <span class="n">windowSpec</span> <span class="p">[,</span> <span class="n">windowName</span> <span class="k">AS</span> <span class="n">windowSpec</span> <span class="p">]</span><span class="o">*</span> <span class="p">]</span>
<span class="n">selectWithoutFrom</span><span class="p">:</span>
<span class="k">SELECT</span> <span class="p">[</span> <span class="k">ALL</span> <span class="o">|</span> <span class="k">DISTINCT</span> <span class="p">]</span>
<span class="err">{</span> <span class="o">*</span> <span class="o">|</span> <span class="n">projectItem</span> <span class="p">[,</span> <span class="n">projectItem</span> <span class="p">]</span><span class="o">*</span> <span class="err">}</span>
<span class="n">projectItem</span><span class="p">:</span>
<span class="n">expression</span> <span class="p">[</span> <span class="p">[</span> <span class="k">AS</span> <span class="p">]</span> <span class="n">columnAlias</span> <span class="p">]</span>
<span class="o">|</span> <span class="n">tableAlias</span> <span class="p">.</span> <span class="o">*</span>
<span class="n">tableExpression</span><span class="p">:</span>
<span class="n">tableReference</span> <span class="p">[,</span> <span class="n">tableReference</span> <span class="p">]</span><span class="o">*</span>
<span class="o">|</span> <span class="n">tableExpression</span> <span class="p">[</span> <span class="k">NATURAL</span> <span class="p">]</span> <span class="p">[</span> <span class="p">(</span> <span class="k">LEFT</span> <span class="o">|</span> <span class="k">RIGHT</span> <span class="o">|</span> <span class="k">FULL</span> <span class="p">)</span> <span class="p">[</span> <span class="k">OUTER</span> <span class="p">]</span> <span class="p">]</span> <span class="k">JOIN</span> <span class="n">tableExpression</span> <span class="p">[</span> <span class="n">joinCondition</span> <span class="p">]</span>
<span class="o">|</span> <span class="n">tableExpression</span> <span class="k">CROSS</span> <span class="k">JOIN</span> <span class="n">tableExpression</span>
<span class="o">|</span> <span class="n">tableExpression</span> <span class="p">[</span> <span class="k">CROSS</span> <span class="o">|</span> <span class="k">OUTER</span> <span class="p">]</span> <span class="n">APPLY</span> <span class="n">tableExpression</span>
<span class="n">joinCondition</span><span class="p">:</span>
<span class="k">ON</span> <span class="n">booleanExpression</span>
<span class="o">|</span> <span class="k">USING</span> <span class="s1">'('</span> <span class="k">column</span> <span class="p">[,</span> <span class="k">column</span> <span class="p">]</span><span class="o">*</span> <span class="s1">')'</span>
<span class="n">tableReference</span><span class="p">:</span>
<span class="n">tablePrimary</span>
<span class="p">[</span> <span class="k">FOR</span> <span class="n">SYSTEM_TIME</span> <span class="k">AS</span> <span class="k">OF</span> <span class="n">expression</span> <span class="p">]</span>
<span class="p">[</span> <span class="n">matchRecognize</span> <span class="p">]</span>
<span class="p">[</span> <span class="p">[</span> <span class="k">AS</span> <span class="p">]</span> <span class="k">alias</span> <span class="p">[</span> <span class="s1">'('</span> <span class="n">columnAlias</span> <span class="p">[,</span> <span class="n">columnAlias</span> <span class="p">]</span><span class="o">*</span> <span class="s1">')'</span> <span class="p">]</span> <span class="p">]</span>
<span class="n">tablePrimary</span><span class="p">:</span>
<span class="p">[</span> <span class="p">[</span> <span class="n">catalogName</span> <span class="p">.</span> <span class="p">]</span> <span class="n">schemaName</span> <span class="p">.</span> <span class="p">]</span> <span class="n">tableName</span>
<span class="s1">'('</span> <span class="k">TABLE</span> <span class="p">[</span> <span class="p">[</span> <span class="n">catalogName</span> <span class="p">.</span> <span class="p">]</span> <span class="n">schemaName</span> <span class="p">.</span> <span class="p">]</span> <span class="n">tableName</span> <span class="s1">')'</span>
<span class="o">|</span> <span class="n">tablePrimary</span> <span class="p">[</span> <span class="n">EXTEND</span> <span class="p">]</span> <span class="s1">'('</span> <span class="n">columnDecl</span> <span class="p">[,</span> <span class="n">columnDecl</span> <span class="p">]</span><span class="o">*</span> <span class="s1">')'</span>
<span class="o">|</span> <span class="p">[</span> <span class="k">LATERAL</span> <span class="p">]</span> <span class="s1">'('</span> <span class="n">query</span> <span class="s1">')'</span>
<span class="o">|</span> <span class="k">UNNEST</span> <span class="s1">'('</span> <span class="n">expression</span> <span class="s1">')'</span> <span class="p">[</span> <span class="k">WITH</span> <span class="k">ORDINALITY</span> <span class="p">]</span>
<span class="o">|</span> <span class="p">[</span> <span class="k">LATERAL</span> <span class="p">]</span> <span class="k">TABLE</span> <span class="s1">'('</span> <span class="p">[</span> <span class="k">SPECIFIC</span> <span class="p">]</span> <span class="n">functionName</span> <span class="s1">'('</span> <span class="n">expression</span> <span class="p">[,</span> <span class="n">expression</span> <span class="p">]</span><span class="o">*</span> <span class="s1">')'</span> <span class="s1">')'</span>
<span class="n">columnDecl</span><span class="p">:</span>
<span class="k">column</span> <span class="k">type</span> <span class="p">[</span> <span class="k">NOT</span> <span class="k">NULL</span> <span class="p">]</span>
<span class="k">values</span><span class="p">:</span>
<span class="k">VALUES</span> <span class="n">expression</span> <span class="p">[,</span> <span class="n">expression</span> <span class="p">]</span><span class="o">*</span>
<span class="n">groupItem</span><span class="p">:</span>
<span class="n">expression</span>
<span class="o">|</span> <span class="s1">'('</span> <span class="s1">')'</span>
<span class="o">|</span> <span class="s1">'('</span> <span class="n">expression</span> <span class="p">[,</span> <span class="n">expression</span> <span class="p">]</span><span class="o">*</span> <span class="s1">')'</span>
<span class="o">|</span> <span class="k">CUBE</span> <span class="s1">'('</span> <span class="n">expression</span> <span class="p">[,</span> <span class="n">expression</span> <span class="p">]</span><span class="o">*</span> <span class="s1">')'</span>
<span class="o">|</span> <span class="k">ROLLUP</span> <span class="s1">'('</span> <span class="n">expression</span> <span class="p">[,</span> <span class="n">expression</span> <span class="p">]</span><span class="o">*</span> <span class="s1">')'</span>
<span class="o">|</span> <span class="k">GROUPING</span> <span class="k">SETS</span> <span class="s1">'('</span> <span class="n">groupItem</span> <span class="p">[,</span> <span class="n">groupItem</span> <span class="p">]</span><span class="o">*</span> <span class="s1">')'</span>
<span class="n">window</span><span class="p">:</span>
<span class="n">windowName</span>
<span class="o">|</span> <span class="n">windowSpec</span>
<span class="n">windowSpec</span><span class="p">:</span>
<span class="s1">'('</span>
<span class="p">[</span> <span class="n">windowName</span> <span class="p">]</span>
<span class="p">[</span> <span class="k">ORDER</span> <span class="k">BY</span> <span class="n">orderItem</span> <span class="p">[,</span> <span class="n">orderItem</span> <span class="p">]</span><span class="o">*</span> <span class="p">]</span>
<span class="p">[</span> <span class="n">PARTITION</span> <span class="k">BY</span> <span class="n">expression</span> <span class="p">[,</span> <span class="n">expression</span> <span class="p">]</span><span class="o">*</span> <span class="p">]</span>
<span class="p">[</span>
<span class="n">RANGE</span> <span class="n">numericOrIntervalExpression</span> <span class="err">{</span> <span class="n">PRECEDING</span> <span class="o">|</span> <span class="n">FOLLOWING</span> <span class="err">}</span>
<span class="o">|</span> <span class="k">ROWS</span> <span class="n">numericExpression</span> <span class="err">{</span> <span class="n">PRECEDING</span> <span class="o">|</span> <span class="n">FOLLOWING</span> <span class="err">}</span>
<span class="p">]</span>
<span class="s1">')'</span></code></pre></figure>
<p>In <em>insert</em>, if the INSERT or UPSERT statement does not specify a
list of target columns, the query must have the same number of
columns as the target table, except in certain
<a href="/apidocs/org/apache/calcite/sql/validate/SqlConformance.html#isInsertSubsetColumnsAllowed--">conformance levels</a>.</p>
<p>In <em>merge</em>, at least one of the WHEN MATCHED and WHEN NOT MATCHED clauses must
be present.</p>
<p><em>tablePrimary</em> may only contain an EXTEND clause in certain
<a href="/apidocs/org/apache/calcite/sql/validate/SqlConformance.html#allowExtend--">conformance levels</a>;
in those same conformance levels, any <em>column</em> in <em>insert</em> may be replaced by
<em>columnDecl</em>, which has a similar effect to including it in an EXTEND clause.</p>
<p>In <em>orderItem</em>, if <em>expression</em> is a positive integer <em>n</em>, it denotes
the <em>n</em>th item in the SELECT clause.</p>
<p>In <em>query</em>, <em>count</em> and <em>start</em> may each be either an unsigned integer literal
or a dynamic parameter whose value is an integer.</p>
<p>An aggregate query is a query that contains a GROUP BY or a HAVING
clause, or aggregate functions in the SELECT clause. In the SELECT,
HAVING and ORDER BY clauses of an aggregate query, all expressions
must be constant within the current group (that is, grouping constants
as defined by the GROUP BY clause, or constants), or aggregate
functions, or a combination of constants and aggregate
functions. Aggregate and grouping functions may only appear in an
aggregate query, and only in a SELECT, HAVING or ORDER BY clause.</p>
<p>A scalar sub-query is a sub-query used as an expression.
If the sub-query returns no rows, the value is NULL; if it
returns more than one row, it is an error.</p>
<p>IN, EXISTS and scalar sub-queries can occur
in any place where an expression can occur (such as the SELECT clause,
WHERE clause, ON clause of a JOIN, or as an argument to an aggregate
function).</p>
<p>An IN, EXISTS or scalar sub-query may be correlated; that is, it
may refer to tables in the FROM clause of an enclosing query.</p>
<p><em>selectWithoutFrom</em> is equivalent to VALUES,
but is not standard SQL and is only allowed in certain
<a href="/apidocs/org/apache/calcite/sql/validate/SqlConformance.html#isFromRequired--">conformance levels</a>.</p>
<p>MINUS is equivalent to EXCEPT,
but is not standard SQL and is only allowed in certain
<a href="/apidocs/org/apache/calcite/sql/validate/SqlConformance.html#isMinusAllowed--">conformance levels</a>.</p>
<p>CROSS APPLY and OUTER APPLY are only allowed in certain
<a href="/apidocs/org/apache/calcite/sql/validate/SqlConformance.html#isApplyAllowed--">conformance levels</a>.</p>
<p>“LIMIT start, count” is equivalent to “LIMIT count OFFSET start”
but is only allowed in certain
<a href="/apidocs/org/apache/calcite/sql/validate/SqlConformance.html#isLimitStartCountAllowed--">conformance levels</a>.</p>
<h2 id="keywords">Keywords</h2>
<p>The following is a list of SQL keywords.
Reserved keywords are <strong>bold</strong>.</p>
<p>A,
<strong>ABS</strong>,
ABSENT,
ABSOLUTE,
ACTION,
ADA,
ADD,
ADMIN,
AFTER,
<strong>ALL</strong>,
<strong>ALLOCATE</strong>,
<strong>ALLOW</strong>,
<strong>ALTER</strong>,
ALWAYS,
<strong>AND</strong>,
<strong>ANY</strong>,
APPLY,
<strong>ARE</strong>,
<strong>ARRAY</strong>,
<strong>ARRAY_MAX_CARDINALITY</strong>,
<strong>AS</strong>,
ASC,
<strong>ASENSITIVE</strong>,
ASSERTION,
ASSIGNMENT,
<strong>ASYMMETRIC</strong>,
<strong>AT</strong>,
<strong>ATOMIC</strong>,
ATTRIBUTE,
ATTRIBUTES,
<strong>AUTHORIZATION</strong>,
<strong>AVG</strong>,
BEFORE,
<strong>BEGIN</strong>,
<strong>BEGIN_FRAME</strong>,
<strong>BEGIN_PARTITION</strong>,
BERNOULLI,
<strong>BETWEEN</strong>,
<strong>BIGINT</strong>,
<strong>BINARY</strong>,
<strong>BIT</strong>,
<strong>BLOB</strong>,
<strong>BOOLEAN</strong>,
<strong>BOTH</strong>,
BREADTH,
<strong>BY</strong>,
C,
<strong>CALL</strong>,
<strong>CALLED</strong>,
<strong>CARDINALITY</strong>,
CASCADE,
<strong>CASCADED</strong>,
<strong>CASE</strong>,
<strong>CAST</strong>,
CATALOG,
CATALOG_NAME,
<strong>CEIL</strong>,
<strong>CEILING</strong>,
CENTURY,
CHAIN,
<strong>CHAR</strong>,
<strong>CHARACTER</strong>,
CHARACTERISTICS,
CHARACTERS,
<strong>CHARACTER_LENGTH</strong>,
CHARACTER_SET_CATALOG,
CHARACTER_SET_NAME,
CHARACTER_SET_SCHEMA,
<strong>CHAR_LENGTH</strong>,
<strong>CHECK</strong>,
<strong>CLASSIFIER</strong>,
CLASS_ORIGIN,
<strong>CLOB</strong>,
<strong>CLOSE</strong>,
<strong>COALESCE</strong>,
COBOL,
<strong>COLLATE</strong>,
COLLATION,
COLLATION_CATALOG,
COLLATION_NAME,
COLLATION_SCHEMA,
<strong>COLLECT</strong>,
<strong>COLUMN</strong>,
COLUMN_NAME,
COMMAND_FUNCTION,
COMMAND_FUNCTION_CODE,
<strong>COMMIT</strong>,
COMMITTED,
<strong>CONDITION</strong>,
CONDITIONAL,
CONDITION_NUMBER,
<strong>CONNECT</strong>,
CONNECTION,
CONNECTION_NAME,
<strong>CONSTRAINT</strong>,
CONSTRAINTS,
CONSTRAINT_CATALOG,
CONSTRAINT_NAME,
CONSTRAINT_SCHEMA,
CONSTRUCTOR,
<strong>CONTAINS</strong>,
CONTINUE,
<strong>CONVERT</strong>,
<strong>CORR</strong>,
<strong>CORRESPONDING</strong>,
<strong>COUNT</strong>,
<strong>COVAR_POP</strong>,
<strong>COVAR_SAMP</strong>,
<strong>CREATE</strong>,
<strong>CROSS</strong>,
<strong>CUBE</strong>,
<strong>CUME_DIST</strong>,
<strong>CURRENT</strong>,
<strong>CURRENT_CATALOG</strong>,
<strong>CURRENT_DATE</strong>,
<strong>CURRENT_DEFAULT_TRANSFORM_GROUP</strong>,
<strong>CURRENT_PATH</strong>,
<strong>CURRENT_ROLE</strong>,
<strong>CURRENT_ROW</strong>,
<strong>CURRENT_SCHEMA</strong>,
<strong>CURRENT_TIME</strong>,
<strong>CURRENT_TIMESTAMP</strong>,
<strong>CURRENT_TRANSFORM_GROUP_FOR_TYPE</strong>,
<strong>CURRENT_USER</strong>,
<strong>CURSOR</strong>,
CURSOR_NAME,
<strong>CYCLE</strong>,
DATA,
DATABASE,
<strong>DATE</strong>,
DATETIME_INTERVAL_CODE,
DATETIME_INTERVAL_PRECISION,
<strong>DAY</strong>,
<strong>DEALLOCATE</strong>,
<strong>DEC</strong>,
DECADE,
<strong>DECIMAL</strong>,
<strong>DECLARE</strong>,
<strong>DEFAULT</strong>,
DEFAULTS,
DEFERRABLE,
DEFERRED,
<strong>DEFINE</strong>,
DEFINED,
DEFINER,
DEGREE,
<strong>DELETE</strong>,
<strong>DENSE_RANK</strong>,
DEPTH,
<strong>DEREF</strong>,
DERIVED,
DESC,
<strong>DESCRIBE</strong>,
DESCRIPTION,
DESCRIPTOR,
<strong>DETERMINISTIC</strong>,
DIAGNOSTICS,
<strong>DISALLOW</strong>,
<strong>DISCONNECT</strong>,
DISPATCH,
<strong>DISTINCT</strong>,
DOMAIN,
<strong>DOUBLE</strong>,
DOW,
DOY,
<strong>DROP</strong>,
<strong>DYNAMIC</strong>,
DYNAMIC_FUNCTION,
DYNAMIC_FUNCTION_CODE,
<strong>EACH</strong>,
<strong>ELEMENT</strong>,
<strong>ELSE</strong>,
<strong>EMPTY</strong>,
ENCODING,
<strong>END</strong>,
<strong>END-EXEC</strong>,
<strong>END_FRAME</strong>,
<strong>END_PARTITION</strong>,
EPOCH,
<strong>EQUALS</strong>,
ERROR,
<strong>ESCAPE</strong>,
<strong>EVERY</strong>,
<strong>EXCEPT</strong>,
EXCEPTION,
EXCLUDE,
EXCLUDING,
<strong>EXEC</strong>,
<strong>EXECUTE</strong>,
<strong>EXISTS</strong>,
<strong>EXP</strong>,
<strong>EXPLAIN</strong>,
<strong>EXTEND</strong>,
<strong>EXTERNAL</strong>,
<strong>EXTRACT</strong>,
<strong>FALSE</strong>,
<strong>FETCH</strong>,
<strong>FILTER</strong>,
FINAL,
FIRST,
<strong>FIRST_VALUE</strong>,
<strong>FLOAT</strong>,
<strong>FLOOR</strong>,
FOLLOWING,
<strong>FOR</strong>,
<strong>FOREIGN</strong>,
FORMAT,
FORTRAN,
FOUND,
FRAC_SECOND,
<strong>FRAME_ROW</strong>,
<strong>FREE</strong>,
<strong>FROM</strong>,
<strong>FULL</strong>,
<strong>FUNCTION</strong>,
<strong>FUSION</strong>,
G,
GENERAL,
GENERATED,
GEOMETRY,
<strong>GET</strong>,
<strong>GLOBAL</strong>,
GO,
GOTO,
<strong>GRANT</strong>,
GRANTED,
<strong>GROUP</strong>,
<strong>GROUPING</strong>,
<strong>GROUPS</strong>,
<strong>HAVING</strong>,
HIERARCHY,
<strong>HOLD</strong>,
<strong>HOUR</strong>,
<strong>IDENTITY</strong>,
IGNORE,
IMMEDIATE,
IMMEDIATELY,
IMPLEMENTATION,
<strong>IMPORT</strong>,
<strong>IN</strong>,
INCLUDING,
INCREMENT,
<strong>INDICATOR</strong>,
<strong>INITIAL</strong>,
INITIALLY,
<strong>INNER</strong>,
<strong>INOUT</strong>,
INPUT,
<strong>INSENSITIVE</strong>,
<strong>INSERT</strong>,
INSTANCE,
INSTANTIABLE,
<strong>INT</strong>,
<strong>INTEGER</strong>,
<strong>INTERSECT</strong>,
<strong>INTERSECTION</strong>,
<strong>INTERVAL</strong>,
<strong>INTO</strong>,
INVOKER,
<strong>IS</strong>,
ISODOW,
ISOLATION,
ISOYEAR,
JAVA,
<strong>JOIN</strong>,
JSON,
<strong>JSON_ARRAY</strong>,
<strong>JSON_ARRAYAGG</strong>,
<strong>JSON_EXISTS</strong>,
<strong>JSON_OBJECT</strong>,
<strong>JSON_OBJECTAGG</strong>,
<strong>JSON_QUERY</strong>,
<strong>JSON_VALUE</strong>,
K,
KEY,
KEY_MEMBER,
KEY_TYPE,
LABEL,
<strong>LAG</strong>,
<strong>LANGUAGE</strong>,
<strong>LARGE</strong>,
LAST,
<strong>LAST_VALUE</strong>,
<strong>LATERAL</strong>,
<strong>LEAD</strong>,
<strong>LEADING</strong>,
<strong>LEFT</strong>,
LENGTH,
LEVEL,
LIBRARY,
<strong>LIKE</strong>,
<strong>LIKE_REGEX</strong>,
<strong>LIMIT</strong>,
<strong>LN</strong>,
<strong>LOCAL</strong>,
<strong>LOCALTIME</strong>,
<strong>LOCALTIMESTAMP</strong>,
LOCATOR,
<strong>LOWER</strong>,
M,
MAP,
<strong>MATCH</strong>,
MATCHED,
<strong>MATCHES</strong>,
<strong>MATCH_NUMBER</strong>,
<strong>MATCH_RECOGNIZE</strong>,
<strong>MAX</strong>,
MAXVALUE,
<strong>MEASURES</strong>,
<strong>MEMBER</strong>,
<strong>MERGE</strong>,
MESSAGE_LENGTH,
MESSAGE_OCTET_LENGTH,
MESSAGE_TEXT,
<strong>METHOD</strong>,
MICROSECOND,
MILLENNIUM,
MILLISECOND,
<strong>MIN</strong>,
<strong>MINUS</strong>,
<strong>MINUTE</strong>,
MINVALUE,
<strong>MOD</strong>,
<strong>MODIFIES</strong>,
<strong>MODULE</strong>,
<strong>MONTH</strong>,
MORE,
<strong>MULTISET</strong>,
MUMPS,
NAME,
NAMES,
NANOSECOND,
<strong>NATIONAL</strong>,
<strong>NATURAL</strong>,
<strong>NCHAR</strong>,
<strong>NCLOB</strong>,
NESTING,
<strong>NEW</strong>,
<strong>NEXT</strong>,
<strong>NO</strong>,
<strong>NONE</strong>,
<strong>NORMALIZE</strong>,
NORMALIZED,
<strong>NOT</strong>,
<strong>NTH_VALUE</strong>,
<strong>NTILE</strong>,
<strong>NULL</strong>,
NULLABLE,
<strong>NULLIF</strong>,
NULLS,
NUMBER,
<strong>NUMERIC</strong>,
OBJECT,
<strong>OCCURRENCES_REGEX</strong>,
OCTETS,
<strong>OCTET_LENGTH</strong>,
<strong>OF</strong>,
<strong>OFFSET</strong>,
<strong>OLD</strong>,
<strong>OMIT</strong>,
<strong>ON</strong>,
<strong>ONE</strong>,
<strong>ONLY</strong>,
<strong>OPEN</strong>,
OPTION,
OPTIONS,
<strong>OR</strong>,
<strong>ORDER</strong>,
ORDERING,
ORDINALITY,
OTHERS,
<strong>OUT</strong>,
<strong>OUTER</strong>,
OUTPUT,
<strong>OVER</strong>,
<strong>OVERLAPS</strong>,
<strong>OVERLAY</strong>,
OVERRIDING,
PAD,
<strong>PARAMETER</strong>,
PARAMETER_MODE,
PARAMETER_NAME,
PARAMETER_ORDINAL_POSITION,
PARAMETER_SPECIFIC_CATALOG,
PARAMETER_SPECIFIC_NAME,
PARAMETER_SPECIFIC_SCHEMA,
PARTIAL,
<strong>PARTITION</strong>,
PASCAL,
PASSING,
PASSTHROUGH,
PAST,
PATH,
<strong>PATTERN</strong>,
<strong>PER</strong>,
<strong>PERCENT</strong>,
<strong>PERCENTILE_CONT</strong>,
<strong>PERCENTILE_DISC</strong>,
<strong>PERCENT_RANK</strong>,
<strong>PERIOD</strong>,
<strong>PERMUTE</strong>,
PLACING,
PLAN,
PLI,
<strong>PORTION</strong>,
<strong>POSITION</strong>,
<strong>POSITION_REGEX</strong>,
<strong>POWER</strong>,
<strong>PRECEDES</strong>,
PRECEDING,
<strong>PRECISION</strong>,
<strong>PREPARE</strong>,
PRESERVE,
<strong>PREV</strong>,
<strong>PRIMARY</strong>,
PRIOR,
PRIVILEGES,
<strong>PROCEDURE</strong>,
PUBLIC,
QUARTER,
<strong>RANGE</strong>,
<strong>RANK</strong>,
READ,
<strong>READS</strong>,
<strong>REAL</strong>,
<strong>RECURSIVE</strong>,
<strong>REF</strong>,
<strong>REFERENCES</strong>,
<strong>REFERENCING</strong>,
<strong>REGR_AVGX</strong>,
<strong>REGR_AVGY</strong>,
<strong>REGR_COUNT</strong>,
<strong>REGR_INTERCEPT</strong>,
<strong>REGR_R2</strong>,
<strong>REGR_SLOPE</strong>,
<strong>REGR_SXX</strong>,
<strong>REGR_SXY</strong>,
<strong>REGR_SYY</strong>,
RELATIVE,
<strong>RELEASE</strong>,
REPEATABLE,
REPLACE,
<strong>RESET</strong>,
RESPECT,
RESTART,
RESTRICT,
<strong>RESULT</strong>,
<strong>RETURN</strong>,
RETURNED_CARDINALITY,
RETURNED_LENGTH,
RETURNED_OCTET_LENGTH,
RETURNED_SQLSTATE,
RETURNING,
<strong>RETURNS</strong>,
<strong>REVOKE</strong>,
<strong>RIGHT</strong>,
ROLE,
<strong>ROLLBACK</strong>,
<strong>ROLLUP</strong>,
ROUTINE,
ROUTINE_CATALOG,
ROUTINE_NAME,
ROUTINE_SCHEMA,
<strong>ROW</strong>,
<strong>ROWS</strong>,
ROW_COUNT,
<strong>ROW_NUMBER</strong>,
<strong>RUNNING</strong>,
<strong>SAVEPOINT</strong>,
SCALAR,
SCALE,
SCHEMA,
SCHEMA_NAME,
<strong>SCOPE</strong>,
SCOPE_CATALOGS,
SCOPE_NAME,
SCOPE_SCHEMA,
<strong>SCROLL</strong>,
<strong>SEARCH</strong>,
<strong>SECOND</strong>,
SECTION,
SECURITY,
<strong>SEEK</strong>,
<strong>SELECT</strong>,
SELF,
<strong>SENSITIVE</strong>,
SEQUENCE,
SERIALIZABLE,
SERVER,
SERVER_NAME,
SESSION,
<strong>SESSION_USER</strong>,
<strong>SET</strong>,
SETS,
<strong>SHOW</strong>,
<strong>SIMILAR</strong>,
SIMPLE,
SIZE,
<strong>SKIP</strong>,
<strong>SMALLINT</strong>,
<strong>SOME</strong>,
SOURCE,
SPACE,
<strong>SPECIFIC</strong>,
<strong>SPECIFICTYPE</strong>,
SPECIFIC_NAME,
<strong>SQL</strong>,
<strong>SQLEXCEPTION</strong>,
<strong>SQLSTATE</strong>,
<strong>SQLWARNING</strong>,
SQL_BIGINT,
SQL_BINARY,
SQL_BIT,
SQL_BLOB,
SQL_BOOLEAN,
SQL_CHAR,
SQL_CLOB,
SQL_DATE,
SQL_DECIMAL,
SQL_DOUBLE,
SQL_FLOAT,
SQL_INTEGER,
SQL_INTERVAL_DAY,
SQL_INTERVAL_DAY_TO_HOUR,
SQL_INTERVAL_DAY_TO_MINUTE,
SQL_INTERVAL_DAY_TO_SECOND,
SQL_INTERVAL_HOUR,
SQL_INTERVAL_HOUR_TO_MINUTE,
SQL_INTERVAL_HOUR_TO_SECOND,
SQL_INTERVAL_MINUTE,
SQL_INTERVAL_MINUTE_TO_SECOND,
SQL_INTERVAL_MONTH,
SQL_INTERVAL_SECOND,
SQL_INTERVAL_YEAR,
SQL_INTERVAL_YEAR_TO_MONTH,
SQL_LONGVARBINARY,
SQL_LONGVARCHAR,
SQL_LONGVARNCHAR,
SQL_NCHAR,
SQL_NCLOB,
SQL_NUMERIC,
SQL_NVARCHAR,
SQL_REAL,
SQL_SMALLINT,
SQL_TIME,
SQL_TIMESTAMP,
SQL_TINYINT,
SQL_TSI_DAY,
SQL_TSI_FRAC_SECOND,
SQL_TSI_HOUR,
SQL_TSI_MICROSECOND,
SQL_TSI_MINUTE,
SQL_TSI_MONTH,
SQL_TSI_QUARTER,
SQL_TSI_SECOND,
SQL_TSI_WEEK,
SQL_TSI_YEAR,
SQL_VARBINARY,
SQL_VARCHAR,
<strong>SQRT</strong>,
<strong>START</strong>,
STATE,
STATEMENT,
<strong>STATIC</strong>,
<strong>STDDEV_POP</strong>,
<strong>STDDEV_SAMP</strong>,
<strong>STREAM</strong>,
STRUCTURE,
STYLE,
SUBCLASS_ORIGIN,
<strong>SUBMULTISET</strong>,
<strong>SUBSET</strong>,
SUBSTITUTE,
<strong>SUBSTRING</strong>,
<strong>SUBSTRING_REGEX</strong>,
<strong>SUCCEEDS</strong>,
<strong>SUM</strong>,
<strong>SYMMETRIC</strong>,
<strong>SYSTEM</strong>,
<strong>SYSTEM_TIME</strong>,
<strong>SYSTEM_USER</strong>,
<strong>TABLE</strong>,
<strong>TABLESAMPLE</strong>,
TABLE_NAME,
TEMPORARY,
<strong>THEN</strong>,
TIES,
<strong>TIME</strong>,
<strong>TIMESTAMP</strong>,
TIMESTAMPADD,
TIMESTAMPDIFF,
<strong>TIMEZONE_HOUR</strong>,
<strong>TIMEZONE_MINUTE</strong>,
<strong>TINYINT</strong>,
<strong>TO</strong>,
TOP_LEVEL_COUNT,
<strong>TRAILING</strong>,
TRANSACTION,
TRANSACTIONS_ACTIVE,
TRANSACTIONS_COMMITTED,
TRANSACTIONS_ROLLED_BACK,
TRANSFORM,
TRANSFORMS,
<strong>TRANSLATE</strong>,
<strong>TRANSLATE_REGEX</strong>,
<strong>TRANSLATION</strong>,
<strong>TREAT</strong>,
<strong>TRIGGER</strong>,
TRIGGER_CATALOG,
TRIGGER_NAME,
TRIGGER_SCHEMA,
<strong>TRIM</strong>,
<strong>TRIM_ARRAY</strong>,
<strong>TRUE</strong>,
<strong>TRUNCATE</strong>,
TYPE,
<strong>UESCAPE</strong>,
UNBOUNDED,
UNCOMMITTED,
UNCONDITIONAL,
UNDER,
<strong>UNION</strong>,
<strong>UNIQUE</strong>,
<strong>UNKNOWN</strong>,
UNNAMED,
<strong>UNNEST</strong>,
<strong>UPDATE</strong>,
<strong>UPPER</strong>,
<strong>UPSERT</strong>,
USAGE,
<strong>USER</strong>,
USER_DEFINED_TYPE_CATALOG,
USER_DEFINED_TYPE_CODE,
USER_DEFINED_TYPE_NAME,
USER_DEFINED_TYPE_SCHEMA,
<strong>USING</strong>,
UTF16,
UTF32,
UTF8,
<strong>VALUE</strong>,
<strong>VALUES</strong>,
<strong>VALUE_OF</strong>,
<strong>VARBINARY</strong>,
<strong>VARCHAR</strong>,
<strong>VARYING</strong>,
<strong>VAR_POP</strong>,
<strong>VAR_SAMP</strong>,
VERSION,
<strong>VERSIONING</strong>,
VIEW,
WEEK,
<strong>WHEN</strong>,
<strong>WHENEVER</strong>,
<strong>WHERE</strong>,
<strong>WIDTH_BUCKET</strong>,
<strong>WINDOW</strong>,
<strong>WITH</strong>,
<strong>WITHIN</strong>,
<strong>WITHOUT</strong>,
WORK,
WRAPPER,
WRITE,
XML,
<strong>YEAR</strong>,
ZONE.</p>
<h2 id="identifiers">Identifiers</h2>
<p>Identifiers are the names of tables, columns and other metadata
elements used in a SQL query.</p>
<p>Unquoted identifiers, such as emp, must start with a letter and can
only contain letters, digits, and underscores. They are implicitly
converted to upper case.</p>
<p>Quoted identifiers, such as <code class="highlighter-rouge">"Employee Name"</code>, start and end with
double quotes. They may contain virtually any character, including
spaces and other punctuation. If you wish to include a double quote
in an identifier, use another double quote to escape it, like this:
<code class="highlighter-rouge">"An employee called ""Fred""."</code>.</p>
<p>In Calcite, matching identifiers to the name of the referenced object is
case-sensitive. But remember that unquoted identifiers are implicitly
converted to upper case before matching, and if the object it refers
to was created using an unquoted identifier for its name, then its
name will have been converted to upper case also.</p>
<h2 id="data-types">Data types</h2>
<h3 id="scalar-types">Scalar types</h3>
<table>
<thead>
<tr>
<th style="text-align: left">Data type</th>
<th style="text-align: left">Description</th>
<th style="text-align: left">Range and example literals</th>
</tr>
</thead>
<tbody>
<tr>
<td style="text-align: left">BOOLEAN</td>
<td style="text-align: left">Logical values</td>
<td style="text-align: left">Values: TRUE, FALSE, UNKNOWN</td>
</tr>
<tr>
<td style="text-align: left">TINYINT</td>
<td style="text-align: left">1 byte signed integer</td>
<td style="text-align: left">Range is -128 to 127</td>
</tr>
<tr>
<td style="text-align: left">SMALLINT</td>
<td style="text-align: left">2 byte signed integer</td>
<td style="text-align: left">Range is -32768 to 32767</td>
</tr>
<tr>
<td style="text-align: left">INTEGER, INT</td>
<td style="text-align: left">4 byte signed integer</td>
<td style="text-align: left">Range is -2147483648 to 2147483647</td>
</tr>
<tr>
<td style="text-align: left">BIGINT</td>
<td style="text-align: left">8 byte signed integer</td>
<td style="text-align: left">Range is -9223372036854775808 to 9223372036854775807</td>
</tr>
<tr>
<td style="text-align: left">DECIMAL(p, s)</td>
<td style="text-align: left">Fixed point</td>
<td style="text-align: left">Example: 123.45 is a DECIMAL(5, 2) value.</td>
</tr>
<tr>
<td style="text-align: left">NUMERIC</td>
<td style="text-align: left">Fixed point</td>
<td style="text-align: left"> </td>
</tr>
<tr>
<td style="text-align: left">REAL, FLOAT</td>
<td style="text-align: left">4 byte floating point</td>
<td style="text-align: left">6 decimal digits precision</td>
</tr>
<tr>
<td style="text-align: left">DOUBLE</td>
<td style="text-align: left">8 byte floating point</td>
<td style="text-align: left">15 decimal digits precision</td>
</tr>
<tr>
<td style="text-align: left">CHAR(n), CHARACTER(n)</td>
<td style="text-align: left">Fixed-width character string</td>
<td style="text-align: left">‘Hello’, ‘’ (empty string), _latin1’Hello’, n’Hello’, _UTF16’Hello’, ‘Hello’ ‘there’ (literal split into multiple parts)</td>
</tr>
<tr>
<td style="text-align: left">VARCHAR(n), CHARACTER VARYING(n)</td>
<td style="text-align: left">Variable-length character string</td>
<td style="text-align: left">As CHAR(n)</td>
</tr>
<tr>
<td style="text-align: left">BINARY(n)</td>
<td style="text-align: left">Fixed-width binary string</td>
<td style="text-align: left">x’45F0AB’, x’’ (empty binary string), x’AB’ ‘CD’ (multi-part binary string literal)</td>
</tr>
<tr>
<td style="text-align: left">VARBINARY(n), BINARY VARYING(n)</td>
<td style="text-align: left">Variable-length binary string</td>
<td style="text-align: left">As BINARY(n)</td>
</tr>
<tr>
<td style="text-align: left">DATE</td>
<td style="text-align: left">Date</td>
<td style="text-align: left">Example: DATE ‘1969-07-20’</td>
</tr>
<tr>
<td style="text-align: left">TIME</td>
<td style="text-align: left">Time of day</td>
<td style="text-align: left">Example: TIME ‘20:17:40’</td>
</tr>
<tr>
<td style="text-align: left">TIMESTAMP [ WITHOUT TIME ZONE ]</td>
<td style="text-align: left">Date and time</td>
<td style="text-align: left">Example: TIMESTAMP ‘1969-07-20 20:17:40’</td>
</tr>
<tr>
<td style="text-align: left">TIMESTAMP WITH LOCAL TIME ZONE</td>
<td style="text-align: left">Date and time with local time zone</td>
<td style="text-align: left">Example: TIMESTAMP ‘1969-07-20 20:17:40 America/Los Angeles’</td>
</tr>
<tr>
<td style="text-align: left">TIMESTAMP WITH TIME ZONE</td>
<td style="text-align: left">Date and time with time zone</td>
<td style="text-align: left">Example: TIMESTAMP ‘1969-07-20 20:17:40 America/Los Angeles’</td>
</tr>
<tr>
<td style="text-align: left">INTERVAL timeUnit [ TO timeUnit ]</td>
<td style="text-align: left">Date time interval</td>
<td style="text-align: left">Examples: INTERVAL ‘1-5’ YEAR TO MONTH, INTERVAL ‘45’ DAY, INTERVAL ‘1 2:34:56.789’ DAY TO SECOND</td>
</tr>
<tr>
<td style="text-align: left">GEOMETRY</td>
<td style="text-align: left">Geometry</td>
<td style="text-align: left">Examples: ST_GeomFromText(‘POINT (30 10)’)</td>
</tr>
</tbody>
</table>
<p>Where:</p>
<figure class="highlight"><pre><code class="language-sql" data-lang="sql"><span class="n">timeUnit</span><span class="p">:</span>
<span class="n">MILLENNIUM</span> <span class="o">|</span> <span class="n">CENTURY</span> <span class="o">|</span> <span class="n">DECADE</span> <span class="o">|</span> <span class="k">YEAR</span> <span class="o">|</span> <span class="n">QUARTER</span> <span class="o">|</span> <span class="k">MONTH</span> <span class="o">|</span> <span class="n">WEEK</span> <span class="o">|</span> <span class="n">DOY</span> <span class="o">|</span> <span class="n">DOW</span> <span class="o">|</span> <span class="k">DAY</span> <span class="o">|</span> <span class="n">HOUR</span> <span class="o">|</span> <span class="k">MINUTE</span> <span class="o">|</span> <span class="k">SECOND</span> <span class="o">|</span> <span class="n">EPOCH</span></code></pre></figure>
<p>Note:</p>
<ul>
<li>DATE, TIME and TIMESTAMP have no time zone. For those types, there is not
even an implicit time zone, such as UTC (as in Java) or the local time zone.
It is left to the user or application to supply a time zone. In turn,
TIMESTAMP WITH LOCAL TIME ZONE does not store the time zone internally, but
it will rely on the supplied time zone to provide correct semantics.</li>
<li>GEOMETRY is allowed only in certain
<a href="/apidocs/org/apache/calcite/sql/validate/SqlConformance.html#allowGeometry--">conformance levels</a>.</li>
</ul>
<h3 id="non-scalar-types">Non-scalar types</h3>
<table>
<thead>
<tr>
<th style="text-align: left">Type</th>
<th style="text-align: left">Description</th>
<th style="text-align: left">Example literals</th>
</tr>
</thead>
<tbody>
<tr>
<td style="text-align: left">ANY</td>
<td style="text-align: left">A value of an unknown type</td>
<td style="text-align: left"> </td>
</tr>
<tr>
<td style="text-align: left">ROW</td>
<td style="text-align: left">Row with 1 or more columns</td>
<td style="text-align: left">Example: Row(f0 int null, f1 varchar)</td>
</tr>
<tr>
<td style="text-align: left">MAP</td>
<td style="text-align: left">Collection of keys mapped to values</td>
<td style="text-align: left"> </td>
</tr>
<tr>
<td style="text-align: left">MULTISET</td>
<td style="text-align: left">Unordered collection that may contain duplicates</td>
<td style="text-align: left">Example: int multiset</td>
</tr>
<tr>
<td style="text-align: left">ARRAY</td>
<td style="text-align: left">Ordered, contiguous collection that may contain duplicates</td>
<td style="text-align: left">Example: varchar(10) array</td>
</tr>
<tr>
<td style="text-align: left">CURSOR</td>
<td style="text-align: left">Cursor over the result of executing a query</td>
<td style="text-align: left"> </td>
</tr>
</tbody>
</table>
<p>Note:</p>
<ul>
<li>Every <code class="highlighter-rouge">ROW</code> column type can have an optional [ NULL | NOT NULL ] suffix
to indicate if this column type is nullable, default is not nullable.</li>
</ul>
<h3 id="spatial-types">Spatial types</h3>
<p>Spatial data is represented as character strings encoded as
<a href="https://en.wikipedia.org/wiki/Well-known_text">well-known text (WKT)</a>
or binary strings encoded as
<a href="https://en.wikipedia.org/wiki/Well-known_binary">well-known binary (WKB)</a>.</p>
<p>Where you would use a literal, apply the <code class="highlighter-rouge">ST_GeomFromText</code> function,
for example <code class="highlighter-rouge">ST_GeomFromText('POINT (30 10)')</code>.</p>
<table>
<thead>
<tr>
<th style="text-align: left">Data type</th>
<th style="text-align: left">Type code</th>
<th style="text-align: left">Examples in WKT</th>
</tr>
</thead>
<tbody>
<tr>
<td style="text-align: left">GEOMETRY</td>
<td style="text-align: left">0</td>
<td style="text-align: left">generalization of Point, Curve, Surface, GEOMETRYCOLLECTION</td>
</tr>
<tr>
<td style="text-align: left">POINT</td>
<td style="text-align: left">1</td>
<td style="text-align: left"><code>ST_GeomFromText(&#8203;'POINT (30 10)')</code> is a point in 2D space; <code>ST_GeomFromText(&#8203;'POINT Z(30 10 2)')</code> is point in 3D space</td>
</tr>
<tr>
<td style="text-align: left">CURVE</td>
<td style="text-align: left">13</td>
<td style="text-align: left">generalization of LINESTRING</td>
</tr>
<tr>
<td style="text-align: left">LINESTRING</td>
<td style="text-align: left">2</td>
<td style="text-align: left"><code>ST_GeomFromText(&#8203;'LINESTRING (30 10, 10 30, 40 40)')</code></td>
</tr>
<tr>
<td style="text-align: left">SURFACE</td>
<td style="text-align: left">14</td>
<td style="text-align: left">generalization of Polygon, PolyhedralSurface</td>
</tr>
<tr>
<td style="text-align: left">POLYGON</td>
<td style="text-align: left">3</td>
<td style="text-align: left"><code>ST_GeomFromText(&#8203;'POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))')</code> is a pentagon; <code>ST_GeomFromText(&#8203;'POLYGON ((35 10, 45 45, 15 40, 10 20, 35 10), (20 30, 35 35, 30 20, 20 30))')</code> is a pentagon with a quadrilateral hole</td>
</tr>
<tr>
<td style="text-align: left">POLYHEDRALSURFACE</td>
<td style="text-align: left">15</td>
<td style="text-align: left"> </td>
</tr>
<tr>
<td style="text-align: left">GEOMETRYCOLLECTION</td>
<td style="text-align: left">7</td>
<td style="text-align: left">a collection of zero or more GEOMETRY instances; a generalization of MULTIPOINT, MULTILINESTRING, MULTIPOLYGON</td>
</tr>
<tr>
<td style="text-align: left">MULTIPOINT</td>
<td style="text-align: left">4</td>
<td style="text-align: left"><code>ST_GeomFromText(&#8203;'MULTIPOINT ((10 40), (40 30), (20 20), (30 10))')</code> is equivalent to <code>ST_GeomFromText(&#8203;'MULTIPOINT (10 40, 40 30, 20 20, 30 10)')</code></td>
</tr>
<tr>
<td style="text-align: left">MULTICURVE</td>
<td style="text-align: left">-</td>
<td style="text-align: left">generalization of MULTILINESTRING</td>
</tr>
<tr>
<td style="text-align: left">MULTILINESTRING</td>
<td style="text-align: left">5</td>
<td style="text-align: left"><code>ST_GeomFromText(&#8203;'MULTILINESTRING ((10 10, 20 20, 10 40), (40 40, 30 30, 40 20, 30 10))')</code></td>
</tr>
<tr>
<td style="text-align: left">MULTISURFACE</td>
<td style="text-align: left">-</td>
<td style="text-align: left">generalization of MULTIPOLYGON</td>
</tr>
<tr>
<td style="text-align: left">MULTIPOLYGON</td>
<td style="text-align: left">6</td>
<td style="text-align: left"><code>ST_GeomFromText(&#8203;'MULTIPOLYGON (((30 20, 45 40, 10 40, 30 20)), ((15 5, 40 10, 10 20, 5 10, 15 5)))')</code></td>
</tr>
</tbody>
</table>
<h2 id="operators-and-functions">Operators and functions</h2>
<h3 id="operator-precedence">Operator precedence</h3>
<p>The operator precedence and associativity, highest to lowest.</p>
<table>
<thead>
<tr>
<th style="text-align: left">Operator</th>
<th style="text-align: left">Associativity</th>
</tr>
</thead>
<tbody>
<tr>
<td style="text-align: left">.</td>
<td style="text-align: left">left</td>
</tr>
<tr>
<td style="text-align: left">::</td>
<td style="text-align: left">left</td>
</tr>
<tr>
<td style="text-align: left">[ ] (array element)</td>
<td style="text-align: left">left</td>
</tr>
<tr>
<td style="text-align: left">+ - (unary plus, minus)</td>
<td style="text-align: left">right</td>
</tr>
<tr>
<td style="text-align: left">* / % ||</td>
<td style="text-align: left">left</td>
</tr>
<tr>
<td style="text-align: left">+ -</td>
<td style="text-align: left">left</td>
</tr>
<tr>
<td style="text-align: left">BETWEEN, IN, LIKE, SIMILAR, OVERLAPS, CONTAINS etc.</td>
<td style="text-align: left">-</td>
</tr>
<tr>
<td style="text-align: left">&lt; &gt; = &lt;= &gt;= &lt;&gt; !=</td>
<td style="text-align: left">left</td>
</tr>
<tr>
<td style="text-align: left">IS NULL, IS FALSE, IS NOT TRUE etc.</td>
<td style="text-align: left">-</td>
</tr>
<tr>
<td style="text-align: left">NOT</td>
<td style="text-align: left">right</td>
</tr>
<tr>
<td style="text-align: left">AND</td>
<td style="text-align: left">left</td>
</tr>
<tr>
<td style="text-align: left">OR</td>
<td style="text-align: left">left</td>
</tr>
</tbody>
</table>
<p>Note that <code class="highlighter-rouge">::</code> is dialect-specific, but is shown in this table for
completeness.</p>
<h3 id="comparison-operators">Comparison operators</h3>
<table>
<thead>
<tr>
<th style="text-align: left">Operator syntax</th>
<th style="text-align: left">Description</th>
</tr>
</thead>
<tbody>
<tr>
<td style="text-align: left">value1 = value2</td>
<td style="text-align: left">Equals</td>
</tr>
<tr>
<td style="text-align: left">value1 &lt;&gt; value2</td>
<td style="text-align: left">Not equal</td>
</tr>
<tr>
<td style="text-align: left">value1 != value2</td>
<td style="text-align: left">Not equal (only available at some conformance levels)</td>
</tr>
<tr>
<td style="text-align: left">value1 &gt; value2</td>
<td style="text-align: left">Greater than</td>
</tr>
<tr>
<td style="text-align: left">value1 &gt;= value2</td>
<td style="text-align: left">Greater than or equal</td>
</tr>
<tr>
<td style="text-align: left">value1 &lt; value2</td>
<td style="text-align: left">Less than</td>
</tr>
<tr>
<td style="text-align: left">value1 &lt;= value2</td>
<td style="text-align: left">Less than or equal</td>
</tr>
<tr>
<td style="text-align: left">value IS NULL</td>
<td style="text-align: left">Whether <em>value</em> is null</td>
</tr>
<tr>
<td style="text-align: left">value IS NOT NULL</td>
<td style="text-align: left">Whether <em>value</em> is not null</td>
</tr>
<tr>
<td style="text-align: left">value1 IS DISTINCT FROM value2</td>
<td style="text-align: left">Whether two values are not equal, treating null values as the same</td>
</tr>
<tr>
<td style="text-align: left">value1 IS NOT DISTINCT FROM value2</td>
<td style="text-align: left">Whether two values are equal, treating null values as the same</td>
</tr>
<tr>
<td style="text-align: left">value1 BETWEEN value2 AND value3</td>
<td style="text-align: left">Whether <em>value1</em> is greater than or equal to <em>value2</em> and less than or equal to <em>value3</em></td>
</tr>
<tr>
<td style="text-align: left">value1 NOT BETWEEN value2 AND value3</td>
<td style="text-align: left">Whether <em>value1</em> is less than <em>value2</em> or greater than <em>value3</em></td>
</tr>
<tr>
<td style="text-align: left">string1 LIKE string2 [ ESCAPE string3 ]</td>
<td style="text-align: left">Whether <em>string1</em> matches pattern <em>string2</em></td>
</tr>
<tr>
<td style="text-align: left">string1 NOT LIKE string2 [ ESCAPE string3 ]</td>
<td style="text-align: left">Whether <em>string1</em> does not match pattern <em>string2</em></td>
</tr>
<tr>
<td style="text-align: left">string1 SIMILAR TO string2 [ ESCAPE string3 ]</td>
<td style="text-align: left">Whether <em>string1</em> matches regular expression <em>string2</em></td>
</tr>
<tr>
<td style="text-align: left">string1 NOT SIMILAR TO string2 [ ESCAPE string3 ]</td>
<td style="text-align: left">Whether <em>string1</em> does not match regular expression <em>string2</em></td>
</tr>
<tr>
<td style="text-align: left">value IN (value [, value]*)</td>
<td style="text-align: left">Whether <em>value</em> is equal to a value in a list</td>
</tr>
<tr>
<td style="text-align: left">value NOT IN (value [, value]*)</td>
<td style="text-align: left">Whether <em>value</em> is not equal to every value in a list</td>
</tr>
<tr>
<td style="text-align: left">value IN (sub-query)</td>
<td style="text-align: left">Whether <em>value</em> is equal to a row returned by <em>sub-query</em></td>
</tr>
<tr>
<td style="text-align: left">value NOT IN (sub-query)</td>
<td style="text-align: left">Whether <em>value</em> is not equal to every row returned by <em>sub-query</em></td>
</tr>
<tr>
<td style="text-align: left">value comparison SOME (sub-query)</td>
<td style="text-align: left">Whether <em>value</em> <em>comparison</em> at least one row returned by <em>sub-query</em></td>
</tr>
<tr>
<td style="text-align: left">value comparison ANY (sub-query)</td>
<td style="text-align: left">Synonym for SOME</td>
</tr>
<tr>
<td style="text-align: left">value comparison ALL (sub-query)</td>
<td style="text-align: left">Whether <em>value</em> <em>comparison</em> every row returned by <em>sub-query</em></td>
</tr>
<tr>
<td style="text-align: left">EXISTS (sub-query)</td>
<td style="text-align: left">Whether <em>sub-query</em> returns at least one row</td>
</tr>
</tbody>
</table>
<figure class="highlight"><pre><code class="language-sql" data-lang="sql"><span class="n">comp</span><span class="p">:</span>
<span class="o">=</span>
<span class="o">|</span> <span class="o">&lt;&gt;</span>
<span class="o">|</span> <span class="o">&gt;</span>
<span class="o">|</span> <span class="o">&gt;=</span>
<span class="o">|</span> <span class="o">&lt;</span>
<span class="o">|</span> <span class="o">&lt;=</span></code></pre></figure>
<h3 id="logical-operators">Logical operators</h3>
<table>
<thead>
<tr>
<th style="text-align: left">Operator syntax</th>
<th style="text-align: left">Description</th>
</tr>
</thead>
<tbody>
<tr>
<td style="text-align: left">boolean1 OR boolean2</td>
<td style="text-align: left">Whether <em>boolean1</em> is TRUE or <em>boolean2</em> is TRUE</td>
</tr>
<tr>
<td style="text-align: left">boolean1 AND boolean2</td>
<td style="text-align: left">Whether <em>boolean1</em> and <em>boolean2</em> are both TRUE</td>
</tr>
<tr>
<td style="text-align: left">NOT boolean</td>
<td style="text-align: left">Whether <em>boolean</em> is not TRUE; returns UNKNOWN if <em>boolean</em> is UNKNOWN</td>
</tr>
<tr>
<td style="text-align: left">boolean IS FALSE</td>
<td style="text-align: left">Whether <em>boolean</em> is FALSE; returns FALSE if <em>boolean</em> is UNKNOWN</td>
</tr>
<tr>
<td style="text-align: left">boolean IS NOT FALSE</td>
<td style="text-align: left">Whether <em>boolean</em> is not FALSE; returns TRUE if <em>boolean</em> is UNKNOWN</td>
</tr>
<tr>
<td style="text-align: left">boolean IS TRUE</td>
<td style="text-align: left">Whether <em>boolean</em> is TRUE; returns FALSE if <em>boolean</em> is UNKNOWN</td>
</tr>
<tr>
<td style="text-align: left">boolean IS NOT TRUE</td>
<td style="text-align: left">Whether <em>boolean</em> is not TRUE; returns TRUE if <em>boolean</em> is UNKNOWN</td>
</tr>
<tr>
<td style="text-align: left">boolean IS UNKNOWN</td>
<td style="text-align: left">Whether <em>boolean</em> is UNKNOWN</td>
</tr>
<tr>
<td style="text-align: left">boolean IS NOT UNKNOWN</td>
<td style="text-align: left">Whether <em>boolean</em> is not UNKNOWN</td>
</tr>
</tbody>
</table>
<h3 id="arithmetic-operators-and-functions">Arithmetic operators and functions</h3>
<table>
<thead>
<tr>
<th style="text-align: left">Operator syntax</th>
<th style="text-align: left">Description</th>
</tr>
</thead>
<tbody>
<tr>
<td style="text-align: left">+ numeric</td>
<td style="text-align: left">Returns <em>numeric</em></td>
</tr>
<tr>
<td style="text-align: left">- numeric</td>
<td style="text-align: left">Returns negative <em>numeric</em></td>
</tr>
<tr>
<td style="text-align: left">numeric1 + numeric2</td>
<td style="text-align: left">Returns <em>numeric1</em> plus <em>numeric2</em></td>
</tr>
<tr>
<td style="text-align: left">numeric1 - numeric2</td>
<td style="text-align: left">Returns <em>numeric1</em> minus <em>numeric2</em></td>
</tr>
<tr>
<td style="text-align: left">numeric1 * numeric2</td>
<td style="text-align: left">Returns <em>numeric1</em> multiplied by <em>numeric2</em></td>
</tr>
<tr>
<td style="text-align: left">numeric1 / numeric2</td>
<td style="text-align: left">Returns <em>numeric1</em> divided by <em>numeric2</em></td>
</tr>
<tr>
<td style="text-align: left">numeric1 % numeric2</td>
<td style="text-align: left">As <em>MOD(numeric1, numeric2)</em> (only in certain <a href="/apidocs/org/apache/calcite/sql/validate/SqlConformance.html#isPercentRemainderAllowed--">conformance levels</a>)</td>
</tr>
<tr>
<td style="text-align: left">POWER(numeric1, numeric2)</td>
<td style="text-align: left">Returns <em>numeric1</em> raised to the power of <em>numeric2</em></td>
</tr>
<tr>
<td style="text-align: left">ABS(numeric)</td>
<td style="text-align: left">Returns the absolute value of <em>numeric</em></td>
</tr>
<tr>
<td style="text-align: left">MOD(numeric1, numeric2)</td>
<td style="text-align: left">Returns the remainder (modulus) of <em>numeric1</em> divided by <em>numeric2</em>. The result is negative only if <em>numeric1</em> is negative</td>
</tr>
<tr>
<td style="text-align: left">SQRT(numeric)</td>
<td style="text-align: left">Returns the square root of <em>numeric</em></td>
</tr>
<tr>
<td style="text-align: left">LN(numeric)</td>
<td style="text-align: left">Returns the natural logarithm (base <em>e</em>) of <em>numeric</em></td>
</tr>
<tr>
<td style="text-align: left">LOG10(numeric)</td>
<td style="text-align: left">Returns the base 10 logarithm of <em>numeric</em></td>
</tr>
<tr>
<td style="text-align: left">EXP(numeric)</td>
<td style="text-align: left">Returns <em>e</em> raised to the power of <em>numeric</em></td>
</tr>
<tr>
<td style="text-align: left">CEIL(numeric)</td>
<td style="text-align: left">Rounds <em>numeric</em> up, returning the smallest integer that is greater than or equal to <em>numeric</em></td>
</tr>
<tr>
<td style="text-align: left">FLOOR(numeric)</td>
<td style="text-align: left">Rounds <em>numeric</em> down, returning the largest integer that is less than or equal to <em>numeric</em></td>
</tr>
<tr>
<td style="text-align: left">RAND([seed])</td>
<td style="text-align: left">Generates a random double between 0 and 1 inclusive, optionally initializing the random number generator with <em>seed</em></td>
</tr>
<tr>
<td style="text-align: left">RAND_INTEGER([seed, ] numeric)</td>
<td style="text-align: left">Generates a random integer between 0 and <em>numeric</em> - 1 inclusive, optionally initializing the random number generator with <em>seed</em></td>
</tr>
<tr>
<td style="text-align: left">ACOS(numeric)</td>
<td style="text-align: left">Returns the arc cosine of <em>numeric</em></td>
</tr>
<tr>
<td style="text-align: left">ASIN(numeric)</td>
<td style="text-align: left">Returns the arc sine of <em>numeric</em></td>
</tr>
<tr>
<td style="text-align: left">ATAN(numeric)</td>
<td style="text-align: left">Returns the arc tangent of <em>numeric</em></td>
</tr>
<tr>
<td style="text-align: left">ATAN2(numeric, numeric)</td>
<td style="text-align: left">Returns the arc tangent of the <em>numeric</em> coordinates</td>
</tr>
<tr>
<td style="text-align: left">COS(numeric)</td>
<td style="text-align: left">Returns the cosine of <em>numeric</em></td>
</tr>
<tr>
<td style="text-align: left">COT(numeric)</td>
<td style="text-align: left">Returns the cotangent of <em>numeric</em></td>
</tr>
<tr>
<td style="text-align: left">DEGREES(numeric)</td>
<td style="text-align: left">Converts <em>numeric</em> from radians to degrees</td>
</tr>
<tr>
<td style="text-align: left">PI()</td>
<td style="text-align: left">Returns a value that is closer than any other value to <em>pi</em></td>
</tr>
<tr>
<td style="text-align: left">RADIANS(numeric)</td>
<td style="text-align: left">Converts <em>numeric</em> from degrees to radians</td>
</tr>
<tr>
<td style="text-align: left">ROUND(numeric1 [, numeric2])</td>
<td style="text-align: left">Rounds <em>numeric1</em> to optionally <em>numeric2</em> (if not specified 0) places right to the decimal point</td>
</tr>
<tr>
<td style="text-align: left">SIGN(numeric)</td>
<td style="text-align: left">Returns the signum of <em>numeric</em></td>
</tr>
<tr>
<td style="text-align: left">SIN(numeric)</td>
<td style="text-align: left">Returns the sine of <em>numeric</em></td>
</tr>
<tr>
<td style="text-align: left">TAN(numeric)</td>
<td style="text-align: left">Returns the tangent of <em>numeric</em></td>
</tr>
<tr>
<td style="text-align: left">TRUNCATE(numeric1 [, numeric2])</td>
<td style="text-align: left">Truncates <em>numeric1</em> to optionally <em>numeric2</em> (if not specified 0) places right to the decimal point</td>
</tr>
</tbody>
</table>
<h3 id="character-string-operators-and-functions">Character string operators and functions</h3>
<table>
<thead>
<tr>
<th style="text-align: left">Operator syntax</th>
<th style="text-align: left">Description</th>
</tr>
</thead>
<tbody>
<tr>
<td style="text-align: left">string || string</td>
<td style="text-align: left">Concatenates two character strings</td>
</tr>
<tr>
<td style="text-align: left">CHAR_LENGTH(string)</td>
<td style="text-align: left">Returns the number of characters in a character string</td>
</tr>
<tr>
<td style="text-align: left">CHARACTER_LENGTH(string)</td>
<td style="text-align: left">As CHAR_LENGTH(<em>string</em>)</td>
</tr>
<tr>
<td style="text-align: left">UPPER(string)</td>
<td style="text-align: left">Returns a character string converted to upper case</td>
</tr>
<tr>
<td style="text-align: left">LOWER(string)</td>
<td style="text-align: left">Returns a character string converted to lower case</td>
</tr>
<tr>
<td style="text-align: left">POSITION(string1 IN string2)</td>
<td style="text-align: left">Returns the position of the first occurrence of <em>string1</em> in <em>string2</em></td>
</tr>
<tr>
<td style="text-align: left">POSITION(string1 IN string2 FROM integer)</td>
<td style="text-align: left">Returns the position of the first occurrence of <em>string1</em> in <em>string2</em> starting at a given point (not standard SQL)</td>
</tr>
<tr>
<td style="text-align: left">TRIM( { BOTH | LEADING | TRAILING } string1 FROM string2)</td>
<td style="text-align: left">Removes the longest string containing only the characters in <em>string1</em> from the start/end/both ends of <em>string1</em></td>
</tr>
<tr>
<td style="text-align: left">OVERLAY(string1 PLACING string2 FROM integer [ FOR integer2 ])</td>
<td style="text-align: left">Replaces a substring of <em>string1</em> with <em>string2</em></td>
</tr>
<tr>
<td style="text-align: left">SUBSTRING(string FROM integer)</td>
<td style="text-align: left">Returns a substring of a character string starting at a given point</td>
</tr>
<tr>
<td style="text-align: left">SUBSTRING(string FROM integer FOR integer)</td>
<td style="text-align: left">Returns a substring of a character string starting at a given point with a given length</td>
</tr>
<tr>
<td style="text-align: left">INITCAP(string)</td>
<td style="text-align: left">Returns <em>string</em> with the first letter of each word converter to upper case and the rest to lower case. Words are sequences of alphanumeric characters separated by non-alphanumeric characters.</td>
</tr>
</tbody>
</table>
<p>Not implemented:</p>
<ul>
<li>SUBSTRING(string FROM regexp FOR regexp)</li>
</ul>
<h3 id="binary-string-operators-and-functions">Binary string operators and functions</h3>
<table>
<thead>
<tr>
<th style="text-align: left">Operator syntax</th>
<th style="text-align: left">Description</th>
</tr>
</thead>
<tbody>
<tr>
<td style="text-align: left">binary || binary</td>
<td style="text-align: left">Concatenates two binary strings</td>
</tr>
<tr>
<td style="text-align: left">POSITION(binary1 IN binary2)</td>
<td style="text-align: left">Returns the position of the first occurrence of <em>binary1</em> in <em>binary2</em></td>
</tr>
<tr>
<td style="text-align: left">POSITION(binary1 IN binary2 FROM integer)</td>
<td style="text-align: left">Returns the position of the first occurrence of <em>binary1</em> in <em>binary2</em> starting at a given point (not standard SQL)</td>
</tr>
<tr>
<td style="text-align: left">OVERLAY(binary1 PLACING binary2 FROM integer [ FOR integer2 ])</td>
<td style="text-align: left">Replaces a substring of <em>binary1</em> with <em>binary2</em></td>
</tr>
<tr>
<td style="text-align: left">SUBSTRING(binary FROM integer)</td>
<td style="text-align: left">Returns a substring of <em>binary</em> starting at a given point</td>
</tr>
<tr>
<td style="text-align: left">SUBSTRING(binary FROM integer FOR integer)</td>
<td style="text-align: left">Returns a substring of <em>binary</em> starting at a given point with a given length</td>
</tr>
</tbody>
</table>
<h3 id="datetime-functions">Date/time functions</h3>
<table>
<thead>
<tr>
<th style="text-align: left">Operator syntax</th>
<th style="text-align: left">Description</th>
</tr>
</thead>
<tbody>
<tr>
<td style="text-align: left">LOCALTIME</td>
<td style="text-align: left">Returns the current date and time in the session time zone in a value of datatype TIME</td>
</tr>
<tr>
<td style="text-align: left">LOCALTIME(precision)</td>
<td style="text-align: left">Returns the current date and time in the session time zone in a value of datatype TIME, with <em>precision</em> digits of precision</td>
</tr>
<tr>
<td style="text-align: left">LOCALTIMESTAMP</td>
<td style="text-align: left">Returns the current date and time in the session time zone in a value of datatype TIMESTAMP</td>
</tr>
<tr>
<td style="text-align: left">LOCALTIMESTAMP(precision)</td>
<td style="text-align: left">Returns the current date and time in the session time zone in a value of datatype TIMESTAMP, with <em>precision</em> digits of precision</td>
</tr>
<tr>
<td style="text-align: left">CURRENT_TIME</td>
<td style="text-align: left">Returns the current time in the session time zone, in a value of datatype TIMESTAMP WITH TIME ZONE</td>
</tr>
<tr>
<td style="text-align: left">CURRENT_DATE</td>
<td style="text-align: left">Returns the current date in the session time zone, in a value of datatype DATE</td>
</tr>
<tr>
<td style="text-align: left">CURRENT_TIMESTAMP</td>
<td style="text-align: left">Returns the current date and time in the session time zone, in a value of datatype TIMESTAMP WITH TIME ZONE</td>
</tr>
<tr>
<td style="text-align: left">EXTRACT(timeUnit FROM datetime)</td>
<td style="text-align: left">Extracts and returns the value of a specified datetime field from a datetime value expression</td>
</tr>
<tr>
<td style="text-align: left">FLOOR(datetime TO timeUnit)</td>
<td style="text-align: left">Rounds <em>datetime</em> down to <em>timeUnit</em></td>
</tr>
<tr>
<td style="text-align: left">CEIL(datetime TO timeUnit)</td>
<td style="text-align: left">Rounds <em>datetime</em> up to <em>timeUnit</em></td>
</tr>
<tr>
<td style="text-align: left">YEAR(date)</td>
<td style="text-align: left">Equivalent to <code class="highlighter-rouge">EXTRACT(YEAR FROM date)</code>. Returns an integer.</td>
</tr>
<tr>
<td style="text-align: left">QUARTER(date)</td>
<td style="text-align: left">Equivalent to <code class="highlighter-rouge">EXTRACT(QUARTER FROM date)</code>. Returns an integer between 1 and 4.</td>
</tr>
<tr>
<td style="text-align: left">MONTH(date)</td>
<td style="text-align: left">Equivalent to <code class="highlighter-rouge">EXTRACT(MONTH FROM date)</code>. Returns an integer between 1 and 12.</td>
</tr>
<tr>
<td style="text-align: left">WEEK(date)</td>
<td style="text-align: left">Equivalent to <code class="highlighter-rouge">EXTRACT(WEEK FROM date)</code>. Returns an integer between 1 and 53.</td>
</tr>
<tr>
<td style="text-align: left">DAYOFYEAR(date)</td>
<td style="text-align: left">Equivalent to <code class="highlighter-rouge">EXTRACT(DOY FROM date)</code>. Returns an integer between 1 and 366.</td>
</tr>
<tr>
<td style="text-align: left">DAYOFMONTH(date)</td>
<td style="text-align: left">Equivalent to <code class="highlighter-rouge">EXTRACT(DAY FROM date)</code>. Returns an integer between 1 and 31.</td>
</tr>
<tr>
<td style="text-align: left">DAYOFWEEK(date)</td>
<td style="text-align: left">Equivalent to <code class="highlighter-rouge">EXTRACT(DOW FROM date)</code>. Returns an integer between 1 and 7.</td>
</tr>
<tr>
<td style="text-align: left">HOUR(date)</td>
<td style="text-align: left">Equivalent to <code class="highlighter-rouge">EXTRACT(HOUR FROM date)</code>. Returns an integer between 0 and 23.</td>
</tr>
<tr>
<td style="text-align: left">MINUTE(date)</td>
<td style="text-align: left">Equivalent to <code class="highlighter-rouge">EXTRACT(MINUTE FROM date)</code>. Returns an integer between 0 and 59.</td>
</tr>
<tr>
<td style="text-align: left">SECOND(date)</td>
<td style="text-align: left">Equivalent to <code class="highlighter-rouge">EXTRACT(SECOND FROM date)</code>. Returns an integer between 0 and 59.</td>
</tr>
<tr>
<td style="text-align: left">TIMESTAMPADD(timeUnit, integer, datetime)</td>
<td style="text-align: left">Returns <em>datetime</em> with an interval of (signed) <em>integer</em> <em>timeUnit</em>s added. Equivalent to <code class="highlighter-rouge">datetime + INTERVAL 'integer' timeUnit</code></td>
</tr>
<tr>
<td style="text-align: left">TIMESTAMPDIFF(timeUnit, datetime, datetime2)</td>
<td style="text-align: left">Returns the (signed) number of <em>timeUnit</em> intervals between <em>datetime</em> and <em>datetime2</em>. Equivalent to <code class="highlighter-rouge">(datetime2 - datetime) timeUnit</code></td>
</tr>
<tr>
<td style="text-align: left">LAST_DAY(date)</td>
<td style="text-align: left">Returns the date of the last day of the month in a value of datatype DATE; For example, it returns DATE’2020-02-29’ for both DATE’2020-02-10’ and TIMESTAMP’2020-02-10 10:10:10’</td>
</tr>
</tbody>
</table>
<p>Calls to niladic functions such as <code class="highlighter-rouge">CURRENT_DATE</code> do not accept parentheses in
standard SQL. Calls with parentheses, such as <code class="highlighter-rouge">CURRENT_DATE()</code> are accepted in certain
<a href="/apidocs/org/apache/calcite/sql/validate/SqlConformance.html#allowNiladicParentheses--">conformance levels</a>.</p>
<p>Not implemented:</p>
<ul>
<li>CEIL(interval)</li>
<li>FLOOR(interval)</li>
<li>+ interval</li>
<li>- interval</li>
<li>interval + interval</li>
<li>interval - interval</li>
<li>interval / interval</li>
</ul>
<h3 id="system-functions">System functions</h3>
<table>
<thead>
<tr>
<th style="text-align: left">Operator syntax</th>
<th style="text-align: left">Description</th>
</tr>
</thead>
<tbody>
<tr>
<td style="text-align: left">USER</td>
<td style="text-align: left">Equivalent to CURRENT_USER</td>
</tr>
<tr>
<td style="text-align: left">CURRENT_USER</td>
<td style="text-align: left">User name of current execution context</td>
</tr>
<tr>
<td style="text-align: left">SESSION_USER</td>
<td style="text-align: left">Session user name</td>
</tr>
<tr>
<td style="text-align: left">SYSTEM_USER</td>
<td style="text-align: left">Returns the name of the current data store user as identified by the operating system</td>
</tr>
<tr>
<td style="text-align: left">CURRENT_PATH</td>
<td style="text-align: left">Returns a character string representing the current lookup scope for references to user-defined routines and types</td>
</tr>
<tr>
<td style="text-align: left">CURRENT_ROLE</td>
<td style="text-align: left">Returns the current active role</td>
</tr>
<tr>
<td style="text-align: left">CURRENT_SCHEMA</td>
<td style="text-align: left">Returns the current schema</td>
</tr>
</tbody>
</table>
<h3 id="conditional-functions-and-operators">Conditional functions and operators</h3>
<table>
<thead>
<tr>
<th style="text-align: left">Operator syntax</th>
<th style="text-align: left">Description</th>
</tr>
</thead>
<tbody>
<tr>
<td style="text-align: left">CASE value<br />WHEN value1 [, value11 ]* THEN result1<br />[ WHEN valueN [, valueN1 ]* THEN resultN ]*<br />[ ELSE resultZ ]<br /> END</td>
<td style="text-align: left">Simple case</td>
</tr>
<tr>
<td style="text-align: left">CASE<br />WHEN condition1 THEN result1<br />[ WHEN conditionN THEN resultN ]*<br />[ ELSE resultZ ]<br />END</td>
<td style="text-align: left">Searched case</td>
</tr>
<tr>
<td style="text-align: left">NULLIF(value, value)</td>
<td style="text-align: left">Returns NULL if the values are the same.<br /><br />For example, <code>NULLIF(5, 5)</code> returns NULL; <code>NULLIF(5, 0)</code> returns 5.</td>
</tr>
<tr>
<td style="text-align: left">COALESCE(value, value [, value ]*)</td>
<td style="text-align: left">Provides a value if the first value is null.<br /><br />For example, <code>COALESCE(NULL, 5)</code> returns 5.</td>
</tr>
</tbody>
</table>
<h3 id="type-conversion">Type conversion</h3>
<p>Generally an expression cannot contain values of different datatypes. For example, an expression cannot multiply 5 by 10 and then add ‘JULIAN’.
However, Calcite supports both implicit and explicit conversion of values from one datatype to another.</p>
<h4 id="implicit-and-explicit-type-conversion">Implicit and Explicit Type Conversion</h4>
<p>Calcite recommends that you specify explicit conversions, rather than rely on implicit or automatic conversions, for these reasons:</p>
<ul>
<li>SQL statements are easier to understand when you use explicit datatype conversion functions.</li>
<li>Implicit datatype conversion can have a negative impact on performance, especially if the datatype of a column value is converted to that of a constant rather than the other way around.</li>
<li>Implicit conversion depends on the context in which it occurs and may not work the same way in every case. For example, implicit conversion from a datetime value to a VARCHAR value may return an unexpected format.</li>
</ul>
<p>Algorithms for implicit conversion are subject to change across Calcite releases. Behavior of explicit conversions is more predictable.</p>
<h4 id="explicit-type-conversion">Explicit Type Conversion</h4>
<table>
<thead>
<tr>
<th style="text-align: left">Operator syntax</th>
<th style="text-align: left">Description</th>
</tr>
</thead>
<tbody>
<tr>
<td style="text-align: left">CAST(value AS type)</td>
<td style="text-align: left">Converts a value to a given type.</td>
</tr>
</tbody>
</table>
<p>Supported data types syntax:</p>
<figure class="highlight"><pre><code class="language-sql" data-lang="sql"><span class="k">type</span><span class="p">:</span>
<span class="n">typeName</span>
<span class="p">[</span> <span class="n">collectionsTypeName</span> <span class="p">]</span><span class="o">*</span>
<span class="n">typeName</span><span class="p">:</span>
<span class="n">sqlTypeName</span>
<span class="o">|</span> <span class="n">rowTypeName</span>
<span class="o">|</span> <span class="n">compoundIdentifier</span>
<span class="n">sqlTypeName</span><span class="p">:</span>
<span class="n">char</span> <span class="p">[</span> <span class="k">precision</span> <span class="p">]</span> <span class="p">[</span> <span class="n">charSet</span> <span class="p">]</span>
<span class="o">|</span> <span class="n">varchar</span> <span class="p">[</span> <span class="k">precision</span> <span class="p">]</span> <span class="p">[</span> <span class="n">charSet</span> <span class="p">]</span>
<span class="o">|</span> <span class="n">DATE</span>
<span class="o">|</span> <span class="n">time</span>
<span class="o">|</span> <span class="k">timestamp</span>
<span class="o">|</span> <span class="n">GEOMETRY</span>
<span class="o">|</span> <span class="n">decimal</span> <span class="p">[</span> <span class="k">precision</span> <span class="p">[,</span> <span class="k">scale</span><span class="p">]</span> <span class="p">]</span>
<span class="o">|</span> <span class="n">BOOLEAN</span>
<span class="o">|</span> <span class="n">integer</span>
<span class="o">|</span> <span class="n">BINARY</span> <span class="p">[</span> <span class="k">precision</span> <span class="p">]</span>
<span class="o">|</span> <span class="n">varbinary</span> <span class="p">[</span> <span class="k">precision</span> <span class="p">]</span>
<span class="o">|</span> <span class="n">TINYINT</span>
<span class="o">|</span> <span class="n">SMALLINT</span>
<span class="o">|</span> <span class="n">BIGINT</span>
<span class="o">|</span> <span class="n">REAL</span>
<span class="o">|</span> <span class="n">double</span>
<span class="o">|</span> <span class="n">FLOAT</span>
<span class="o">|</span> <span class="k">ANY</span> <span class="p">[</span> <span class="k">precision</span> <span class="p">[,</span> <span class="k">scale</span><span class="p">]</span> <span class="p">]</span>
<span class="n">collectionsTypeName</span><span class="p">:</span>
<span class="n">ARRAY</span> <span class="o">|</span> <span class="n">MULTISET</span>
<span class="n">rowTypeName</span><span class="p">:</span>
<span class="k">ROW</span> <span class="s1">'('</span>
<span class="n">fieldName1</span> <span class="n">fieldType1</span> <span class="p">[</span> <span class="k">NULL</span> <span class="o">|</span> <span class="k">NOT</span> <span class="k">NULL</span> <span class="p">]</span>
<span class="p">[</span> <span class="p">,</span> <span class="n">fieldName2</span> <span class="n">fieldType2</span> <span class="p">[</span> <span class="k">NULL</span> <span class="o">|</span> <span class="k">NOT</span> <span class="k">NULL</span> <span class="p">]</span> <span class="p">]</span><span class="o">*</span>
<span class="s1">')'</span>
<span class="n">char</span><span class="p">:</span>
<span class="n">CHARACTER</span> <span class="o">|</span> <span class="n">CHAR</span>
<span class="n">varchar</span><span class="p">:</span>
<span class="n">char</span> <span class="n">VARYING</span> <span class="o">|</span> <span class="n">VARCHAR</span>
<span class="n">decimal</span><span class="p">:</span>
<span class="n">DECIMAL</span> <span class="o">|</span> <span class="n">DEC</span> <span class="o">|</span> <span class="n">NUMERIC</span>
<span class="n">integer</span><span class="p">:</span>
<span class="n">INTEGER</span> <span class="o">|</span> <span class="n">INT</span>
<span class="n">varbinary</span><span class="p">:</span>
<span class="n">BINARY</span> <span class="n">VARYING</span> <span class="o">|</span> <span class="n">VARBINARY</span>
<span class="n">double</span><span class