| <!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="s1">'/*+'</span> <span class="n">hint</span> <span class="p">[,</span> <span class="n">hint</span><span class="p">]</span><span class="o">*</span> <span class="s1">'*/'</span> <span class="p">]</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="s1">'/*+'</span> <span class="n">hint</span> <span class="p">[,</span> <span class="n">hint</span><span class="p">]</span><span class="o">*</span> <span class="s1">'*/'</span> <span class="p">]</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="n">hint</span><span class="p">:</span> |
| <span class="n">hintName</span> |
| <span class="o">|</span> <span class="n">hintName</span> <span class="s1">'('</span> <span class="n">hintOptions</span> <span class="s1">')'</span> |
| |
| <span class="n">hintOptions</span><span class="p">:</span> |
| <span class="n">hintKVOption</span> <span class="p">[,</span> <span class="n">hintKVOption</span><span class="p">]</span><span class="o">*</span> |
| <span class="o">|</span> <span class="n">optionName</span> <span class="p">[,</span> <span class="n">optionName</span><span class="p">]</span><span class="o">*</span> |
| <span class="o">|</span> <span class="n">optionValue</span> <span class="p">[,</span> <span class="n">optionValue</span><span class="p">]</span><span class="o">*</span> |
| |
| <span class="n">hintKVOption</span><span class="p">:</span> |
| <span class="n">optionName</span> <span class="s1">'='</span> <span class="n">stringLiteral</span> |
| <span class="o">|</span> <span class="n">stringLiteral</span> <span class="s1">'='</span> <span class="n">stringLiteral</span> |
| |
| <span class="n">optionValue</span><span class="p">:</span> |
| <span class="n">stringLiteral</span> |
| <span class="o">|</span> <span class="n">numericLiteral</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="/javadocAggregate/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="/javadocAggregate/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="/javadocAggregate/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="/javadocAggregate/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="/javadocAggregate/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="/javadocAggregate/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>, |
| DAYS, |
| <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>, |
| HOURS, |
| <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>, |
| MINUTES, |
| MINVALUE, |
| <strong>MOD</strong>, |
| <strong>MODIFIES</strong>, |
| <strong>MODULE</strong>, |
| <strong>MONTH</strong>, |
| MONTHS, |
| 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>, |
| SECONDS, |
| 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>, |
| TUMBLE, |
| 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>, |
| YEARS, |
| 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="/javadocAggregate/org/apache/calcite/sql/validate/SqlConformance.html#allowGeometry--">conformance levels</a>.</li> |
| <li>Interval literals may only use time units |
| YEAR, MONTH, DAY, HOUR, MINUTE and SECOND. In certain |
| <a href="/javadocAggregate/org/apache/calcite/sql/validate/SqlConformance.html#allowPluralTimeUnits--">conformance levels</a>, |
| we also allow their plurals, YEARS, MONTHS, DAYS, HOURS, MINUTES and SECONDS.</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(​'POINT (30 10)')</code> is a point in 2D space; <code>ST_GeomFromText(​'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(​'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(​'POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))')</code> is a pentagon; <code>ST_GeomFromText(​'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(​'MULTIPOINT ((10 40), (40 30), (20 20), (30 10))')</code> is equivalent to <code>ST_GeomFromText(​'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(​'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(​'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">< > = <= >= <> !=</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 <> 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 > value2</td> |
| <td style="text-align: left">Greater than</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">value1 >= value2</td> |
| <td style="text-align: left">Greater than or equal</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">value1 < value2</td> |
| <td style="text-align: left">Less than</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">value1 <= 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"><></span> |
| <span class="o">|</span> <span class="o">></span> |
| <span class="o">|</span> <span class="o">>=</span> |
| <span class="o">|</span> <span class="o"><</span> |
| <span class="o">|</span> <span class="o"><=</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="/javadocAggregate/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">CBRT(numeric)</td> |
| <td style="text-align: left">Returns the cube root of <em>numeric</em></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="/javadocAggregate/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="p">:</span> |
| <span class="n">DOUBLE</span> <span class="p">[</span> <span class="k">PRECISION</span> <span class="p">]</span> |
| |
| <span class="n">time</span><span class="p">:</span> |
| <span class="n">TIME</span> <span class="p">[</span> <span class="k">precision</span> <span class="p">]</span> <span class="p">[</span> <span class="n">timeZone</span> <span class="p">]</span> |
| |
| <span class="k">timestamp</span><span class="p">:</span> |
| <span class="k">TIMESTAMP</span> <span class="p">[</span> <span class="k">precision</span> <span class="p">]</span> <span class="p">[</span> <span class="n">timeZone</span> <span class="p">]</span> |
| |
| <span class="n">charSet</span><span class="p">:</span> |
| <span class="n">CHARACTER</span> <span class="k">SET</span> <span class="n">charSetName</span> |
| |
| <span class="n">timeZone</span><span class="p">:</span> |
| <span class="k">WITHOUT</span> <span class="n">TIME</span> <span class="k">ZONE</span> |
| <span class="o">|</span> <span class="k">WITH</span> <span class="k">LOCAL</span> <span class="n">TIME</span> <span class="k">ZONE</span></code></pre></figure> |
| |
| <h4 id="implicit-type-conversion">Implicit Type Conversion</h4> |
| |
| <p>Calcite automatically converts a value from one datatype to another |
| when such a conversion makes sense. The table below is a matrix of |
| Calcite type conversions. The table shows all possible conversions, |
| without regard to the context in which it is made. The rules governing |
| these details follow the table.</p> |
| |
| <table> |
| <thead> |
| <tr> |
| <th style="text-align: left">FROM - TO</th> |
| <th style="text-align: left">NULL</th> |
| <th style="text-align: left">BOOLEAN</th> |
| <th style="text-align: left">TINYINT</th> |
| <th style="text-align: left">SMALLINT</th> |
| <th style="text-align: left">INT</th> |
| <th style="text-align: left">BIGINT</th> |
| <th style="text-align: left">DECIMAL</th> |
| <th style="text-align: left">FLOAT or REAL</th> |
| <th style="text-align: left">DOUBLE</th> |
| <th style="text-align: left">INTERVAL</th> |
| <th style="text-align: left">DATE</th> |
| <th style="text-align: left">TIME</th> |
| <th style="text-align: left">TIMESTAMP</th> |
| <th style="text-align: left">CHAR or VARCHAR</th> |
| <th style="text-align: left">BINARY or VARBINARY</th> |
| </tr> |
| </thead> |
| <tbody> |
| <tr> |
| <td style="text-align: left">NULL</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">i</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">BOOLEAN</td> |
| <td style="text-align: left">x</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">e</td> |
| <td style="text-align: left">e</td> |
| <td style="text-align: left">e</td> |
| <td style="text-align: left">e</td> |
| <td style="text-align: left">e</td> |
| <td style="text-align: left">e</td> |
| <td style="text-align: left">e</td> |
| <td style="text-align: left">x</td> |
| <td style="text-align: left">x</td> |
| <td style="text-align: left">x</td> |
| <td style="text-align: left">x</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">x</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">TINYINT</td> |
| <td style="text-align: left">x</td> |
| <td style="text-align: left">e</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">e</td> |
| <td style="text-align: left">x</td> |
| <td style="text-align: left">x</td> |
| <td style="text-align: left">e</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">x</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">SMALLINT</td> |
| <td style="text-align: left">x</td> |
| <td style="text-align: left">e</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">e</td> |
| <td style="text-align: left">x</td> |
| <td style="text-align: left">x</td> |
| <td style="text-align: left">e</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">x</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">INT</td> |
| <td style="text-align: left">x</td> |
| <td style="text-align: left">e</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">e</td> |
| <td style="text-align: left">x</td> |
| <td style="text-align: left">x</td> |
| <td style="text-align: left">e</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">x</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">BIGINT</td> |
| <td style="text-align: left">x</td> |
| <td style="text-align: left">e</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">e</td> |
| <td style="text-align: left">x</td> |
| <td style="text-align: left">x</td> |
| <td style="text-align: left">e</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">x</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">DECIMAL</td> |
| <td style="text-align: left">x</td> |
| <td style="text-align: left">e</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">e</td> |
| <td style="text-align: left">x</td> |
| <td style="text-align: left">x</td> |
| <td style="text-align: left">e</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">x</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">FLOAT/REAL</td> |
| <td style="text-align: left">x</td> |
| <td style="text-align: left">e</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">x</td> |
| <td style="text-align: left">x</td> |
| <td style="text-align: left">x</td> |
| <td style="text-align: left">e</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">x</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">DOUBLE</td> |
| <td style="text-align: left">x</td> |
| <td style="text-align: left">e</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">x</td> |
| <td style="text-align: left">x</td> |
| <td style="text-align: left">x</td> |
| <td style="text-align: left">e</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">x</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">INTERVAL</td> |
| <td style="text-align: left">x</td> |
| <td style="text-align: left">x</td> |
| <td style="text-align: left">e</td> |
| <td style="text-align: left">e</td> |
| <td style="text-align: left">e</td> |
| <td style="text-align: left">e</td> |
| <td style="text-align: left">e</td> |
| <td style="text-align: left">x</td> |
| <td style="text-align: left">x</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">x</td> |
| <td style="text-align: left">x</td> |
| <td style="text-align: left">x</td> |
| <td style="text-align: left">e</td> |
| <td style="text-align: left">x</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">DATE</td> |
| <td style="text-align: left">x</td> |
| <td style="text-align: left">x</td> |
| <td style="text-align: left">x</td> |
| <td style="text-align: left">x</td> |
| <td style="text-align: left">x</td> |
| <td style="text-align: left">x</td> |
| <td style="text-align: left">x</td> |
| <td style="text-align: left">x</td> |
| <td style="text-align: left">x</td> |
| <td style="text-align: left">x</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">x</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">x</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">TIME</td> |
| <td style="text-align: left">x</td> |
| <td style="text-align: left">x</td> |
| <td style="text-align: left">x</td> |
| <td style="text-align: left">x</td> |
| <td style="text-align: left">x</td> |
| <td style="text-align: left">x</td> |
| <td style="text-align: left">x</td> |
| <td style="text-align: left">x</td> |
| <td style="text-align: left">x</td> |
| <td style="text-align: left">x</td> |
| <td style="text-align: left">x</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">e</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">x</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">TIMESTAMP</td> |
| <td style="text-align: left">x</td> |
| <td style="text-align: left">x</td> |
| <td style="text-align: left">e</td> |
| <td style="text-align: left">e</td> |
| <td style="text-align: left">e</td> |
| <td style="text-align: left">e</td> |
| <td style="text-align: left">e</td> |
| <td style="text-align: left">e</td> |
| <td style="text-align: left">e</td> |
| <td style="text-align: left">x</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">e</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">x</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">CHAR or VARCHAR</td> |
| <td style="text-align: left">x</td> |
| <td style="text-align: left">e</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">i</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">BINARY or VARBINARY</td> |
| <td style="text-align: left">x</td> |
| <td style="text-align: left">x</td> |
| <td style="text-align: left">x</td> |
| <td style="text-align: left">x</td> |
| <td style="text-align: left">x</td> |
| <td style="text-align: left">x</td> |
| <td style="text-align: left">x</td> |
| <td style="text-align: left">x</td> |
| <td style="text-align: left">x</td> |
| <td style="text-align: left">x</td> |
| <td style="text-align: left">e</td> |
| <td style="text-align: left">e</td> |
| <td style="text-align: left">e</td> |
| <td style="text-align: left">i</td> |
| <td style="text-align: left">i</td> |
| </tr> |
| </tbody> |
| </table> |
| |
| <p>i: implicit cast / e: explicit cast / x: not allowed</p> |
| |
| <h5 id="conversion-contexts-and-strategies">Conversion Contexts and Strategies</h5> |
| |
| <ul> |
| <li>Set operation (<code class="highlighter-rouge">UNION</code>, <code class="highlighter-rouge">EXCEPT</code>, <code class="highlighter-rouge">INTERSECT</code>): compare every branch |
| row data type and find the common type of each fields pair;</li> |
| <li>Binary arithmetic expression (<code class="highlighter-rouge">+</code>, <code class="highlighter-rouge">-</code>, <code class="highlighter-rouge">&</code>, <code class="highlighter-rouge">^</code>, <code class="highlighter-rouge">/</code>, <code class="highlighter-rouge">%</code>): promote |
| string operand to data type of the other numeric operand;</li> |
| <li>Binary comparison (<code class="highlighter-rouge">=</code>, <code class="highlighter-rouge"><</code>, <code class="highlighter-rouge"><=</code>, <code class="highlighter-rouge"><></code>, <code class="highlighter-rouge">></code>, <code class="highlighter-rouge">>=</code>): |
| if operands are <code class="highlighter-rouge">STRING</code> and <code class="highlighter-rouge">TIMESTAMP</code>, promote to <code class="highlighter-rouge">TIMESTAMP</code>; |
| make <code class="highlighter-rouge">1 = true</code> and <code class="highlighter-rouge">0 = false</code> always evaluate to <code class="highlighter-rouge">TRUE</code>; |
| if there is numeric type operand, find common type for both operands.</li> |
| <li><code class="highlighter-rouge">IN</code> sub-query: compare type of LHS and RHS, and find the common type; |
| if it is struct type, find wider type for every field;</li> |
| <li><code class="highlighter-rouge">IN</code> expression list: compare every expression to find the common type;</li> |
| <li><code class="highlighter-rouge">CASE WHEN</code> expression or <code class="highlighter-rouge">COALESCE</code>: find the common wider type of the <code class="highlighter-rouge">THEN</code> |
| and <code class="highlighter-rouge">ELSE</code> operands;</li> |
| <li>Character + <code class="highlighter-rouge">INTERVAL</code> or character - <code class="highlighter-rouge">INTERVAL</code>: promote character to |
| <code class="highlighter-rouge">TIMESTAMP</code>;</li> |
| <li>Built-in function: look up the type families registered in the checker, |
| find the family default type if checker rules allow it;</li> |
| <li>User-defined function (UDF): coerce based on the declared argument types |
| of the <code class="highlighter-rouge">eval()</code> method;</li> |
| <li><code class="highlighter-rouge">INSERT</code> and <code class="highlighter-rouge">UPDATE</code>: coerce a source field to counterpart target table |
| field’s type if the two fields differ with type name or precision(scale).</li> |
| </ul> |
| |
| <p>Note:</p> |
| |
| <p>Implicit type coercion of following cases are ignored:</p> |
| |
| <ul> |
| <li>One of the type is <code class="highlighter-rouge">ANY</code>;</li> |
| <li>Type coercion within <code class="highlighter-rouge">CHARACTER</code> types are always ignored, |
| i.e. from <code class="highlighter-rouge">CHAR(20)</code> to <code class="highlighter-rouge">VARCHAR(30)</code>;</li> |
| <li>Type coercion from a numeric to another with higher precedence is ignored, |
| i.e. from <code class="highlighter-rouge">INT</code> to <code class="highlighter-rouge">LONG</code>.</li> |
| </ul> |
| |
| <h5 id="strategies-for-finding-common-type">Strategies for Finding Common Type</h5> |
| |
| <ul> |
| <li>If the operator has expected data types, just take them as the |
| desired one. (e.g. the UDF would have <code class="highlighter-rouge">eval()</code> method which has |
| reflection argument types);</li> |
| <li>If there is no expected data type but the data type families are |
| registered, try to coerce the arguments to the family’s default data |
| type, i.e. the String family will have a <code class="highlighter-rouge">VARCHAR</code> type;</li> |
| <li>If neither expected data type nor families are specified, try to |
| find the tightest common type of the node types, i.e. <code class="highlighter-rouge">INTEGER</code> and |
| <code class="highlighter-rouge">DOUBLE</code> will return <code class="highlighter-rouge">DOUBLE</code>, the numeric precision does not lose |
| for this case;</li> |
| <li>If no tightest common type is found, try to find a wider type, |
| i.e. <code class="highlighter-rouge">VARCHAR</code> and <code class="highlighter-rouge">INTEGER</code> will return <code class="highlighter-rouge">INTEGER</code>, |
| we allow some precision loss when widening decimal to fractional, |
| or promote to <code class="highlighter-rouge">VARCHAR</code> type.</li> |
| </ul> |
| |
| <h3 id="value-constructors">Value constructors</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">ROW (value [, value ]*)</td> |
| <td style="text-align: left">Creates a row from a list of values.</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">(value [, value ]* )</td> |
| <td style="text-align: left">Creates a row from a list of values.</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">map ‘[’ key ‘]’</td> |
| <td style="text-align: left">Returns the element of a map with a particular key.</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">array ‘[’ index ‘]’</td> |
| <td style="text-align: left">Returns the element at a particular location in an array.</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">ARRAY ‘[’ value [, value ]* ‘]’</td> |
| <td style="text-align: left">Creates an array from a list of values.</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">MAP ‘[’ key, value [, key, value ]* ‘]’</td> |
| <td style="text-align: left">Creates a map from a list of key-value pairs.</td> |
| </tr> |
| </tbody> |
| </table> |
| |
| <h3 id="collection-functions">Collection 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">ELEMENT(value)</td> |
| <td style="text-align: left">Returns the sole element of an array or multiset; null if the collection is empty; throws if it has more than one element.</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">CARDINALITY(value)</td> |
| <td style="text-align: left">Returns the number of elements in an array or multiset.</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">value MEMBER OF multiset</td> |
| <td style="text-align: left">Returns whether the <em>value</em> is a member of <em>multiset</em>.</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">multiset IS A SET</td> |
| <td style="text-align: left">Whether <em>multiset</em> is a set (has no duplicates).</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">multiset IS NOT A SET</td> |
| <td style="text-align: left">Whether <em>multiset</em> is not a set (has duplicates).</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">multiset IS EMPTY</td> |
| <td style="text-align: left">Whether <em>multiset</em> contains zero elements.</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">multiset IS NOT EMPTY</td> |
| <td style="text-align: left">Whether <em>multiset</em> contains one or more elements.</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">multiset SUBMULTISET OF multiset2</td> |
| <td style="text-align: left">Whether <em>multiset</em> is a submultiset of <em>multiset2</em>.</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">multiset NOT SUBMULTISET OF multiset2</td> |
| <td style="text-align: left">Whether <em>multiset</em> is not a submultiset of <em>multiset2</em>.</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">multiset MULTISET UNION [ ALL | DISTINCT ] multiset2</td> |
| <td style="text-align: left">Returns the union <em>multiset</em> and <em>multiset2</em>, eliminating duplicates if DISTINCT is specified (ALL is the default).</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">multiset MULTISET INTERSECT [ ALL | DISTINCT ] multiset2</td> |
| <td style="text-align: left">Returns the intersection of <em>multiset</em> and <em>multiset2</em>, eliminating duplicates if DISTINCT is specified (ALL is the default).</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">multiset MULTISET EXCEPT [ ALL | DISTINCT ] multiset2</td> |
| <td style="text-align: left">Returns the difference of <em>multiset</em> and <em>multiset2</em>, eliminating duplicates if DISTINCT is specified (ALL is the default).</td> |
| </tr> |
| </tbody> |
| </table> |
| |
| <p>See also: the UNNEST relational operator converts a collection to a relation.</p> |
| |
| <h3 id="period-predicates">Period predicates</h3> |
| |
| <table> |
| <tr> |
| <th>Operator syntax</th> |
| <th>Description</th> |
| </tr> |
| <tr> |
| <td>period1 CONTAINS datetime</td> |
| <td> |
| <div class="container"> |
| <div class="gray"><div class="r15"></div><div class="r2"></div></div> |
| </div> |
| </td> |
| </tr> |
| <tr> |
| <td>period1 CONTAINS period2</td> |
| <td> |
| <div class="container"> |
| <div class="gray"><div class="r15"></div><div class="r24"></div></div> |
| <div class="gray"><div class="r15"></div><div class="r13"></div></div> |
| <div class="gray"><div class="r15"></div><div class="r35"></div></div> |
| <div class="gray"><div class="r15"></div><div class="r15"></div></div> |
| </div> |
| </td> |
| </tr> |
| <tr> |
| <td>period1 OVERLAPS period2</td> |
| <td> |
| <div class="container"> |
| <div class="gray"><div class="r15"></div><div class="r24"></div></div> |
| <div class="gray"><div class="r15"></div><div class="r13"></div></div> |
| <div class="gray"><div class="r15"></div><div class="r35"></div></div> |
| <div class="gray"><div class="r15"></div><div class="r15"></div></div> |
| <div class="gray"><div class="r24"></div><div class="r15"></div></div> |
| <div class="gray"><div class="r13"></div><div class="r15"></div></div> |
| <div class="gray"><div class="r35"></div><div class="r15"></div></div> |
| <div class="gray"><div class="r24"></div><div class="r13"></div></div> |
| <div class="gray"><div class="r13"></div><div class="r24"></div></div> |
| </div> |
| </td> |
| </tr> |
| <tr> |
| <td>period1 EQUALS period2</td> |
| <td> |
| <div class="container"> |
| <div class="gray"><div class="r15"></div><div class="r15"></div></div> |
| </div> |
| </td> |
| </tr> |
| <tr> |
| <td>period1 PRECEDES period2</td> |
| <td> |
| <div class="container"> |
| <div class="gray"><div class="r12"></div><div class="r35"></div></div> |
| <div class="gray"><div class="r13"></div><div class="r35"></div></div> |
| </div> |
| </td> |
| </tr> |
| <tr> |
| <td>period1 IMMEDIATELY PRECEDES period2</td> |
| <td> |
| <div class="container"> |
| <div class="gray"><div class="r13"></div><div class="r35"></div></div> |
| </div> |
| </td> |
| </tr> |
| <tr> |
| <td>period1 SUCCEEDS period2</td> |
| <td> |
| <div class="container"> |
| <div class="gray"><div class="r35"></div><div class="r12"></div></div> |
| <div class="gray"><div class="r35"></div><div class="r13"></div></div> |
| </div> |
| </td> |
| </tr> |
| <tr> |
| <td>period1 IMMEDIATELY SUCCEEDS period2</td> |
| <td> |
| <div class="container"> |
| <div class="gray"><div class="r35"></div><div class="r13"></div></div> |
| </div> |
| </td> |
| </tr> |
| </table> |
| |
| <p>Where <em>period1</em> and <em>period2</em> are period expressions:</p> |
| |
| <figure class="highlight"><pre><code class="language-sql" data-lang="sql"><span class="n">period</span><span class="p">:</span> |
| <span class="p">(</span><span class="n">datetime</span><span class="p">,</span> <span class="n">datetime</span><span class="p">)</span> |
| <span class="o">|</span> <span class="p">(</span><span class="n">datetime</span><span class="p">,</span> <span class="n">interval</span><span class="p">)</span> |
| <span class="o">|</span> <span class="n">PERIOD</span> <span class="p">(</span><span class="n">datetime</span><span class="p">,</span> <span class="n">datetime</span><span class="p">)</span> |
| <span class="o">|</span> <span class="n">PERIOD</span> <span class="p">(</span><span class="n">datetime</span><span class="p">,</span> <span class="n">interval</span><span class="p">)</span></code></pre></figure> |
| |
| <h3 id="jdbc-function-escape">JDBC function escape</h3> |
| |
| <h4 id="numeric">Numeric</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">{fn ABS(numeric)}</td> |
| <td style="text-align: left">Returns the absolute value of <em>numeric</em></td> |
| </tr> |
| <tr> |
| <td style="text-align: left">{fn ACOS(numeric)}</td> |
| <td style="text-align: left">Returns the arc cosine of <em>numeric</em></td> |
| </tr> |
| <tr> |
| <td style="text-align: left">{fn ASIN(numeric)}</td> |
| <td style="text-align: left">Returns the arc sine of <em>numeric</em></td> |
| </tr> |
| <tr> |
| <td style="text-align: left">{fn ATAN(numeric)}</td> |
| <td style="text-align: left">Returns the arc tangent of <em>numeric</em></td> |
| </tr> |
| <tr> |
| <td style="text-align: left">{fn 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">{fn CBRT(numeric)}</td> |
| <td style="text-align: left">Returns the cube root of <em>numeric</em></td> |
| </tr> |
| <tr> |
| <td style="text-align: left">{fn CEILING(numeric)}</td> |
| <td style="text-align: left">Rounds <em>numeric</em> up, and returns the smallest number that is greater than or equal to <em>numeric</em></td> |
| </tr> |
| <tr> |
| <td style="text-align: left">{fn COS(numeric)}</td> |
| <td style="text-align: left">Returns the cosine of <em>numeric</em></td> |
| </tr> |
| <tr> |
| <td style="text-align: left">{fn COT(numeric)}</td> |
| <td style="text-align: left">Returns the cotangent of <em>numeric</em></td> |
| </tr> |
| <tr> |
| <td style="text-align: left">{fn DEGREES(numeric)}</td> |
| <td style="text-align: left">Converts <em>numeric</em> from radians to degrees</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">{fn 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">{fn FLOOR(numeric)}</td> |
| <td style="text-align: left">Rounds <em>numeric</em> down, and returns the largest number that is less than or equal to <em>numeric</em></td> |
| </tr> |
| <tr> |
| <td style="text-align: left">{fn LOG(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">{fn 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">{fn 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">{fn 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">{fn 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">{fn RADIANS(numeric)}</td> |
| <td style="text-align: left">Converts <em>numeric</em> from degrees to radians</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">{fn RAND(numeric)}</td> |
| <td style="text-align: left">Returns a random double using <em>numeric</em> as the seed value</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">{fn ROUND(numeric1, numeric2)}</td> |
| <td style="text-align: left">Rounds <em>numeric1</em> to <em>numeric2</em> places right to the decimal point</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">{fn SIGN(numeric)}</td> |
| <td style="text-align: left">Returns the signum of <em>numeric</em></td> |
| </tr> |
| <tr> |
| <td style="text-align: left">{fn SIN(numeric)}</td> |
| <td style="text-align: left">Returns the sine of <em>numeric</em></td> |
| </tr> |
| <tr> |
| <td style="text-align: left">{fn SQRT(numeric)}</td> |
| <td style="text-align: left">Returns the square root of <em>numeric</em></td> |
| </tr> |
| <tr> |
| <td style="text-align: left">{fn TAN(numeric)}</td> |
| <td style="text-align: left">Returns the tangent of <em>numeric</em></td> |
| </tr> |
| <tr> |
| <td style="text-align: left">{fn TRUNCATE(numeric1, numeric2)}</td> |
| <td style="text-align: left">Truncates <em>numeric1</em> to <em>numeric2</em> places right to the decimal point</td> |
| </tr> |
| </tbody> |
| </table> |
| |
| <h4 id="string">String</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">{fn ASCII(string)}</td> |
| <td style="text-align: left">Returns the ASCII code of the first character of <em>string</em>; if the first character is a non-ASCII character, returns its Unicode code point; returns 0 if <em>string</em> is empty</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">{fn CONCAT(character, character)}</td> |
| <td style="text-align: left">Returns the concatenation of character strings</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">{fn INSERT(string1, start, length, string2)}</td> |
| <td style="text-align: left">Inserts <em>string2</em> into a slot in <em>string1</em></td> |
| </tr> |
| <tr> |
| <td style="text-align: left">{fn LCASE(string)}</td> |
| <td style="text-align: left">Returns a string in which all alphabetic characters in <em>string</em> have been converted to lower case</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">{fn LENGTH(string)}</td> |
| <td style="text-align: left">Returns the number of characters in a string</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">{fn LOCATE(string1, string2 [, integer])}</td> |
| <td style="text-align: left">Returns the position in <em>string2</em> of the first occurrence of <em>string1</em>. Searches from the beginning of <em>string2</em>, unless <em>integer</em> is specified.</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">{fn LEFT(string, length)}</td> |
| <td style="text-align: left">Returns the leftmost <em>length</em> characters from <em>string</em></td> |
| </tr> |
| <tr> |
| <td style="text-align: left">{fn LTRIM(string)}</td> |
| <td style="text-align: left">Returns <em>string</em> with leading space characters removed</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">{fn REPLACE(string, search, replacement)}</td> |
| <td style="text-align: left">Returns a string in which all the occurrences of <em>search</em> in <em>string</em> are replaced with <em>replacement</em>; if <em>replacement</em> is the empty string, the occurrences of <em>search</em> are removed</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">{fn REVERSE(string)}</td> |
| <td style="text-align: left">Returns <em>string</em> with the order of the characters reversed</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">{fn RIGHT(string, integer)}</td> |
| <td style="text-align: left">Returns the rightmost <em>length</em> characters from <em>string</em></td> |
| </tr> |
| <tr> |
| <td style="text-align: left">{fn RTRIM(string)}</td> |
| <td style="text-align: left">Returns <em>string</em> with trailing space characters removed</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">{fn SUBSTRING(string, offset, length)}</td> |
| <td style="text-align: left">Returns a character string that consists of <em>length</em> characters from <em>string</em> starting at the <em>offset</em> position</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">{fn UCASE(string)}</td> |
| <td style="text-align: left">Returns a string in which all alphabetic characters in <em>string</em> have been converted to upper case</td> |
| </tr> |
| </tbody> |
| </table> |
| |
| <p>Not implemented:</p> |
| |
| <ul> |
| <li>{fn CHAR(string)}</li> |
| </ul> |
| |
| <h4 id="datetime">Date/time</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">{fn CURDATE()}</td> |
| <td style="text-align: left">Equivalent to <code class="highlighter-rouge">CURRENT_DATE</code></td> |
| </tr> |
| <tr> |
| <td style="text-align: left">{fn CURTIME()}</td> |
| <td style="text-align: left">Equivalent to <code class="highlighter-rouge">LOCALTIME</code></td> |
| </tr> |
| <tr> |
| <td style="text-align: left">{fn NOW()}</td> |
| <td style="text-align: left">Equivalent to <code class="highlighter-rouge">LOCALTIMESTAMP</code></td> |
| </tr> |
| <tr> |
| <td style="text-align: left">{fn 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">{fn 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">{fn 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">{fn 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">{fn 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">{fn 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">{fn 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">{fn 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">{fn 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">{fn 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">{fn TIMESTAMPADD(timeUnit, count, datetime)}</td> |
| <td style="text-align: left">Adds an interval of <em>count</em> <em>timeUnit</em>s to a datetime</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">{fn TIMESTAMPDIFF(timeUnit, timestamp1, timestamp2)}</td> |
| <td style="text-align: left">Subtracts <em>timestamp1</em> from <em>timestamp2</em> and returns the result in <em>timeUnit</em>s</td> |
| </tr> |
| </tbody> |
| </table> |
| |
| <h4 id="system">System</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">{fn DATABASE()}</td> |
| <td style="text-align: left">Equivalent to <code class="highlighter-rouge">CURRENT_CATALOG</code></td> |
| </tr> |
| <tr> |
| <td style="text-align: left">{fn IFNULL(value1, value2)}</td> |
| <td style="text-align: left">Returns value2 if value1 is null</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">{fn USER()}</td> |
| <td style="text-align: left">Equivalent to <code class="highlighter-rouge">CURRENT_USER</code></td> |
| </tr> |
| </tbody> |
| </table> |
| |
| <h4 id="conversion">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">{fn CONVERT(value, type)}</td> |
| <td style="text-align: left">Cast <em>value</em> into <em>type</em></td> |
| </tr> |
| </tbody> |
| </table> |
| |
| <h3 id="aggregate-functions">Aggregate functions</h3> |
| |
| <p>Syntax:</p> |
| |
| <figure class="highlight"><pre><code class="language-sql" data-lang="sql"><span class="n">aggregateCall</span><span class="p">:</span> |
| <span class="n">agg</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="n">value</span> <span class="p">[,</span> <span class="n">value</span> <span class="p">]</span><span class="o">*</span><span class="p">)</span> |
| <span class="p">[</span> <span class="n">WITHIN</span> <span class="k">GROUP</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="p">[</span> <span class="n">FILTER</span> <span class="p">(</span><span class="k">WHERE</span> <span class="n">condition</span><span class="p">)</span> <span class="p">]</span> |
| <span class="o">|</span> <span class="n">agg</span><span class="p">(</span><span class="o">*</span><span class="p">)</span> <span class="p">[</span> <span class="n">FILTER</span> <span class="p">(</span><span class="k">WHERE</span> <span class="n">condition</span><span class="p">)</span> <span class="p">]</span></code></pre></figure> |
| |
| <p>where <em>agg</em> is one of the operators in the following table, or a user-defined |
| aggregate function.</p> |
| |
| <p>If <code class="highlighter-rouge">FILTER</code> is present, the aggregate function only considers rows for which |
| <em>condition</em> evaluates to TRUE.</p> |
| |
| <p>If <code class="highlighter-rouge">DISTINCT</code> is present, duplicate argument values are eliminated before being |
| passed to the aggregate function.</p> |
| |
| <p>If <code class="highlighter-rouge">WITHIN GROUP</code> is present, the aggregate function sorts the input rows |
| according to the <code class="highlighter-rouge">ORDER BY</code> clause inside <code class="highlighter-rouge">WITHIN GROUP</code> before aggregating |
| values. <code class="highlighter-rouge">WITHIN GROUP</code> is only allowed for hypothetical set functions (<code class="highlighter-rouge">RANK</code>, |
| <code class="highlighter-rouge">DENSE_RANK</code>, <code class="highlighter-rouge">PERCENT_RANK</code> and <code class="highlighter-rouge">CUME_DIST</code>), inverse distribution functions |
| (<code class="highlighter-rouge">PERCENTILE_CONT</code> and <code class="highlighter-rouge">PERCENTILE_DISC</code>) and collection functions (<code class="highlighter-rouge">COLLECT</code> |
| and <code class="highlighter-rouge">LISTAGG</code>).</p> |
| |
| <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">COLLECT( [ ALL | DISTINCT ] value)</td> |
| <td style="text-align: left">Returns a multiset of the values</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">LISTAGG( [ ALL | DISTINCT ] value [, separator])</td> |
| <td style="text-align: left">Returns values concatenated into a string, delimited by separator (default ‘,’)</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">COUNT( [ ALL | DISTINCT ] value [, value ]*)</td> |
| <td style="text-align: left">Returns the number of input rows for which <em>value</em> is not null (wholly not null if <em>value</em> is composite)</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">COUNT(*)</td> |
| <td style="text-align: left">Returns the number of input rows</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">FUSION(multiset)</td> |
| <td style="text-align: left">Returns the multiset union of <em>multiset</em> across all input values</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">APPROX_COUNT_DISTINCT(value [, value ]*)</td> |
| <td style="text-align: left">Returns the approximate number of distinct values of <em>value</em>; the database is allowed to use an approximation but is not required to</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">AVG( [ ALL | DISTINCT ] numeric)</td> |
| <td style="text-align: left">Returns the average (arithmetic mean) of <em>numeric</em> across all input values</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">SUM( [ ALL | DISTINCT ] numeric)</td> |
| <td style="text-align: left">Returns the sum of <em>numeric</em> across all input values</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">MAX( [ ALL | DISTINCT ] value)</td> |
| <td style="text-align: left">Returns the maximum value of <em>value</em> across all input values</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">MIN( [ ALL | DISTINCT ] value)</td> |
| <td style="text-align: left">Returns the minimum value of <em>value</em> across all input values</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">ANY_VALUE( [ ALL | DISTINCT ] value)</td> |
| <td style="text-align: left">Returns one of the values of <em>value</em> across all input values; this is NOT specified in the SQL standard</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">BIT_AND( [ ALL | DISTINCT ] value)</td> |
| <td style="text-align: left">Returns the bitwise AND of all non-null input values, or null if none</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">BIT_OR( [ ALL | DISTINCT ] value)</td> |
| <td style="text-align: left">Returns the bitwise OR of all non-null input values, or null if none</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">BIT_XOR( [ ALL | DISTINCT ] value)</td> |
| <td style="text-align: left">Returns the bitwise XOR of all non-null input values, or null if none</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">STDDEV_POP( [ ALL | DISTINCT ] numeric)</td> |
| <td style="text-align: left">Returns the population standard deviation of <em>numeric</em> across all input values</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">STDDEV_SAMP( [ ALL | DISTINCT ] numeric)</td> |
| <td style="text-align: left">Returns the sample standard deviation of <em>numeric</em> across all input values</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">STDDEV( [ ALL | DISTINCT ] numeric)</td> |
| <td style="text-align: left">Synonym for <code class="highlighter-rouge">STDDEV_SAMP</code></td> |
| </tr> |
| <tr> |
| <td style="text-align: left">VAR_POP( [ ALL | DISTINCT ] value)</td> |
| <td style="text-align: left">Returns the population variance (square of the population standard deviation) of <em>numeric</em> across all input values</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">VAR_SAMP( [ ALL | DISTINCT ] numeric)</td> |
| <td style="text-align: left">Returns the sample variance (square of the sample standard deviation) of <em>numeric</em> across all input values</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">COVAR_POP(numeric1, numeric2)</td> |
| <td style="text-align: left">Returns the population covariance of the pair (<em>numeric1</em>, <em>numeric2</em>) across all input values</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">COVAR_SAMP(numeric1, numeric2)</td> |
| <td style="text-align: left">Returns the sample covariance of the pair (<em>numeric1</em>, <em>numeric2</em>) across all input values</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">REGR_COUNT(numeric1, numeric2)</td> |
| <td style="text-align: left">Returns the number of rows where both dependent and independent expressions are not null</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">REGR_SXX(numeric1, numeric2)</td> |
| <td style="text-align: left">Returns the sum of squares of the dependent expression in a linear regression model</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">REGR_SYY(numeric1, numeric2)</td> |
| <td style="text-align: left">Returns the sum of squares of the independent expression in a linear regression model</td> |
| </tr> |
| </tbody> |
| </table> |
| |
| <p>Not implemented:</p> |
| |
| <ul> |
| <li>REGR_AVGX(numeric1, numeric2)</li> |
| <li>REGR_AVGY(numeric1, numeric2)</li> |
| <li>REGR_INTERCEPT(numeric1, numeric2)</li> |
| <li>REGR_R2(numeric1, numeric2)</li> |
| <li>REGR_SLOPE(numeric1, numeric2)</li> |
| <li>REGR_SXY(numeric1, numeric2)</li> |
| </ul> |
| |
| <h3 id="window-functions">Window functions</h3> |
| |
| <p>Syntax:</p> |
| |
| <figure class="highlight"><pre><code class="language-sql" data-lang="sql"><span class="n">windowedAggregateCall</span><span class="p">:</span> |
| <span class="n">agg</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="n">value</span> <span class="p">[,</span> <span class="n">value</span> <span class="p">]</span><span class="o">*</span><span class="p">)</span> |
| <span class="p">[</span> <span class="n">RESPECT</span> <span class="n">NULLS</span> <span class="o">|</span> <span class="k">IGNORE</span> <span class="n">NULLS</span> <span class="p">]</span> |
| <span class="p">[</span> <span class="n">WITHIN</span> <span class="k">GROUP</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="p">[</span> <span class="n">FILTER</span> <span class="p">(</span><span class="k">WHERE</span> <span class="n">condition</span><span class="p">)</span> <span class="p">]</span> |
| <span class="n">OVER</span> <span class="n">window</span> |
| <span class="o">|</span> <span class="n">agg</span><span class="p">(</span><span class="o">*</span><span class="p">)</span> |
| <span class="p">[</span> <span class="n">FILTER</span> <span class="p">(</span><span class="k">WHERE</span> <span class="n">condition</span><span class="p">)</span> <span class="p">]</span> |
| <span class="n">OVER</span> <span class="n">window</span></code></pre></figure> |
| |
| <p>where <em>agg</em> is one of the operators in the following table, or a user-defined |
| aggregate function.</p> |
| |
| <p><code class="highlighter-rouge">DISTINCT</code>, <code class="highlighter-rouge">FILTER</code> and <code class="highlighter-rouge">WITHIN GROUP</code> are as described for aggregate |
| functions.</p> |
| |
| <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">COUNT(value [, value ]*) OVER window</td> |
| <td style="text-align: left">Returns the number of rows in <em>window</em> for which <em>value</em> is not null (wholly not null if <em>value</em> is composite)</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">COUNT(*) OVER window</td> |
| <td style="text-align: left">Returns the number of rows in <em>window</em></td> |
| </tr> |
| <tr> |
| <td style="text-align: left">AVG(numeric) OVER window</td> |
| <td style="text-align: left">Returns the average (arithmetic mean) of <em>numeric</em> across all values in <em>window</em></td> |
| </tr> |
| <tr> |
| <td style="text-align: left">SUM(numeric) OVER window</td> |
| <td style="text-align: left">Returns the sum of <em>numeric</em> across all values in <em>window</em></td> |
| </tr> |
| <tr> |
| <td style="text-align: left">MAX(value) OVER window</td> |
| <td style="text-align: left">Returns the maximum value of <em>value</em> across all values in <em>window</em></td> |
| </tr> |
| <tr> |
| <td style="text-align: left">MIN(value) OVER window</td> |
| <td style="text-align: left">Returns the minimum value of <em>value</em> across all values in <em>window</em></td> |
| </tr> |
| <tr> |
| <td style="text-align: left">RANK() OVER window</td> |
| <td style="text-align: left">Returns the rank of the current row with gaps; same as ROW_NUMBER of its first peer</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">DENSE_RANK() OVER window</td> |
| <td style="text-align: left">Returns the rank of the current row without gaps; this function counts peer groups</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">ROW_NUMBER() OVER window</td> |
| <td style="text-align: left">Returns the number of the current row within its partition, counting from 1</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">FIRST_VALUE(value) OVER window</td> |
| <td style="text-align: left">Returns <em>value</em> evaluated at the row that is the first row of the window frame</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">LAST_VALUE(value) OVER window</td> |
| <td style="text-align: left">Returns <em>value</em> evaluated at the row that is the last row of the window frame</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">LEAD(value, offset, default) OVER window</td> |
| <td style="text-align: left">Returns <em>value</em> evaluated at the row that is <em>offset</em> rows after the current row within the partition; if there is no such row, instead returns <em>default</em>. Both <em>offset</em> and <em>default</em> are evaluated with respect to the current row. If omitted, <em>offset</em> defaults to 1 and <em>default</em> to NULL</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">LAG(value, offset, default) OVER window</td> |
| <td style="text-align: left">Returns <em>value</em> evaluated at the row that is <em>offset</em> rows before the current row within the partition; if there is no such row, instead returns <em>default</em>. Both <em>offset</em> and <em>default</em> are evaluated with respect to the current row. If omitted, <em>offset</em> defaults to 1 and <em>default</em> to NULL</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">NTH_VALUE(value, nth) OVER window</td> |
| <td style="text-align: left">Returns <em>value</em> evaluated at the row that is the <em>n</em>th row of the window frame</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">NTILE(value) OVER window</td> |
| <td style="text-align: left">Returns an integer ranging from 1 to <em>value</em>, dividing the partition as equally as possible</td> |
| </tr> |
| </tbody> |
| </table> |
| |
| <p>Note:</p> |
| |
| <ul> |
| <li>You may specify null treatment (<code class="highlighter-rouge">IGNORE NULLS</code>, <code class="highlighter-rouge">RESPECT NULLS</code>) for |
| <code class="highlighter-rouge">FIRST_VALUE</code>, <code class="highlighter-rouge">LAST_VALUE</code>, <code class="highlighter-rouge">NTH_VALUE</code>, <code class="highlighter-rouge">LEAD</code> and <code class="highlighter-rouge">LAG</code> functions. The |
| syntax handled by the parser, but only <code class="highlighter-rouge">RESPECT NULLS</code> is implemented at |
| runtime.</li> |
| </ul> |
| |
| <p>Not implemented:</p> |
| |
| <ul> |
| <li>COUNT(DISTINCT value [, value ]*) OVER window</li> |
| <li>APPROX_COUNT_DISTINCT(value [, value ]*) OVER window</li> |
| <li>PERCENT_RANK(value) OVER window</li> |
| <li>CUME_DIST(value) OVER window</li> |
| </ul> |
| |
| <h3 id="grouping-functions">Grouping 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">GROUPING(expression [, expression ]*)</td> |
| <td style="text-align: left">Returns a bit vector of the given grouping expressions</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">GROUP_ID()</td> |
| <td style="text-align: left">Returns an integer that uniquely identifies the combination of grouping keys</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">GROUPING_ID(expression [, expression ]*)</td> |
| <td style="text-align: left">Synonym for <code class="highlighter-rouge">GROUPING</code></td> |
| </tr> |
| </tbody> |
| </table> |
| |
| <h3 id="descriptor">DESCRIPTOR</h3> |
| <p>| Operator syntax | Description |
| |:——————– |:———– |
| | DESCRIPTOR(name [, name ]*) | DESCRIPTOR appears as an argument in a function to indicate a list of names. The interpretation of names is left to the function.</p> |
| |
| <h3 id="table-valued-functions">Table-valued functions.</h3> |
| <p>Table-valued functions occur in the <code class="highlighter-rouge">FROM</code> clause.</p> |
| |
| <h4 id="tumble">TUMBLE</h4> |
| <p>In streaming queries, TUMBLE assigns a window for each row of a relation based on a timestamp column. An assigned window |
| is specified by its beginning and ending. All assigned windows have the same length, and that’s why tumbling sometimes |
| is named as “fixed windowing”.</p> |
| |
| <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">TUMBLE(table, DESCRIPTOR(column_name), interval [, time ])</td> |
| <td style="text-align: left">Indicates a tumbling window of <em>interval</em> for <em>datetime</em>, optionally aligned at <em>time</em>. Tumbling is applied on table in which there is a watermarked column specified by descriptor.</td> |
| </tr> |
| </tbody> |
| </table> |
| |
| <p>Here is an example: |
| <code class="highlighter-rouge">SELECT * FROM TABLE(TUMBLE(TABLE orders, DESCRIPTOR(rowtime), INTERVAL '1' MINUTE))</code>, |
| will apply tumbling with 1 minute window size on rows from table orders. rowtime is the |
| watermarked column of table orders that tells data completeness.</p> |
| |
| <h3 id="grouped-window-functions">Grouped window functions</h3> |
| |
| <p>Grouped window functions occur in the <code class="highlighter-rouge">GROUP BY</code> clause and define a key value |
| that represents a window containing several rows.</p> |
| |
| <p>In some window functions, a row may belong to more than one window. |
| For example, if a query is grouped using |
| <code class="highlighter-rouge">HOP(t, INTERVAL '2' HOUR, INTERVAL '1' HOUR)</code>, a row with timestamp ‘10:15:00’ |
| will occur in both the 10:00 - 11:00 and 11:00 - 12:00 totals.</p> |
| |
| <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">HOP(datetime, slide, size [, time ])</td> |
| <td style="text-align: left">Indicates a hopping window for <em>datetime</em>, covering rows within the interval of <em>size</em>, shifting every <em>slide</em>, and optionally aligned at <em>time</em></td> |
| </tr> |
| <tr> |
| <td style="text-align: left">SESSION(datetime, interval [, time ])</td> |
| <td style="text-align: left">Indicates a session window of <em>interval</em> for <em>datetime</em>, optionally aligned at <em>time</em></td> |
| </tr> |
| <tr> |
| <td style="text-align: left">TUMBLE(datetime, interval [, time ])</td> |
| <td style="text-align: left">Indicates a tumbling window of <em>interval</em> for <em>datetime</em>, optionally aligned at <em>time</em></td> |
| </tr> |
| </tbody> |
| </table> |
| |
| <h3 id="grouped-auxiliary-functions">Grouped auxiliary functions</h3> |
| |
| <p>Grouped auxiliary functions allow you to access properties of a window defined |
| by a grouped window function.</p> |
| |
| <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">HOP_END(expression, slide, size [, time ])</td> |
| <td style="text-align: left">Returns the value of <em>expression</em> at the end of the window defined by a <code class="highlighter-rouge">HOP</code> function call</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">HOP_START(expression, slide, size [, time ])</td> |
| <td style="text-align: left">Returns the value of <em>expression</em> at the beginning of the window defined by a <code class="highlighter-rouge">HOP</code> function call</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">SESSION_END(expression, interval [, time])</td> |
| <td style="text-align: left">Returns the value of <em>expression</em> at the end of the window defined by a <code class="highlighter-rouge">SESSION</code> function call</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">SESSION_START(expression, interval [, time])</td> |
| <td style="text-align: left">Returns the value of <em>expression</em> at the beginning of the window defined by a <code class="highlighter-rouge">SESSION</code> function call</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">TUMBLE_END(expression, interval [, time ])</td> |
| <td style="text-align: left">Returns the value of <em>expression</em> at the end of the window defined by a <code class="highlighter-rouge">TUMBLE</code> function call</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">TUMBLE_START(expression, interval [, time ])</td> |
| <td style="text-align: left">Returns the value of <em>expression</em> at the beginning of the window defined by a <code class="highlighter-rouge">TUMBLE</code> function call</td> |
| </tr> |
| </tbody> |
| </table> |
| |
| <h3 id="spatial-functions">Spatial functions</h3> |
| |
| <p>In the following:</p> |
| |
| <ul> |
| <li><em>geom</em> is a GEOMETRY;</li> |
| <li><em>geomCollection</em> is a GEOMETRYCOLLECTION;</li> |
| <li><em>point</em> is a POINT;</li> |
| <li><em>lineString</em> is a LINESTRING;</li> |
| <li><em>iMatrix</em> is a <a href="https://en.wikipedia.org/wiki/DE-9IM">DE-9IM intersection matrix</a>;</li> |
| <li><em>distance</em>, <em>tolerance</em>, <em>segmentLengthFraction</em>, <em>offsetDistance</em> are of type double;</li> |
| <li><em>dimension</em>, <em>quadSegs</em>, <em>srid</em>, <em>zoom</em> are of type integer;</li> |
| <li><em>layerType</em> is a character string;</li> |
| <li><em>gml</em> is a character string containing <a href="https://en.wikipedia.org/wiki/Geography_Markup_Language">Geography Markup Language (GML)</a>;</li> |
| <li><em>wkt</em> is a character string containing <a href="https://en.wikipedia.org/wiki/Well-known_text">well-known text (WKT)</a>;</li> |
| <li><em>wkb</em> is a binary string containing <a href="https://en.wikipedia.org/wiki/Well-known_binary">well-known binary (WKB)</a>.</li> |
| </ul> |
| |
| <p>In the “C” (for “compatibility”) column, “o” indicates that the function |
| implements the OpenGIS Simple Features Implementation Specification for SQL, |
| <a href="https://www.opengeospatial.org/standards/sfs">version 1.2.1</a>; |
| “p” indicates that the function is a |
| <a href="https://www.postgis.net/docs/reference.html">PostGIS</a> extension to OpenGIS.</p> |
| |
| <h4 id="geometry-conversion-functions-2d">Geometry conversion functions (2D)</h4> |
| |
| <table> |
| <thead> |
| <tr> |
| <th style="text-align: left">C</th> |
| <th style="text-align: left">Operator syntax</th> |
| <th style="text-align: left">Description</th> |
| </tr> |
| </thead> |
| <tbody> |
| <tr> |
| <td style="text-align: left">p</td> |
| <td style="text-align: left">ST_AsText(geom)</td> |
| <td style="text-align: left">Alias for <code class="highlighter-rouge">ST_AsWKT</code></td> |
| </tr> |
| <tr> |
| <td style="text-align: left">o</td> |
| <td style="text-align: left">ST_AsWKT(geom)</td> |
| <td style="text-align: left">Converts <em>geom</em> → WKT</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">o</td> |
| <td style="text-align: left">ST_GeomFromText(wkt [, srid ])</td> |
| <td style="text-align: left">Returns a specified GEOMETRY value from WKT representation</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">o</td> |
| <td style="text-align: left">ST_LineFromText(wkt [, srid ])</td> |
| <td style="text-align: left">Converts WKT → LINESTRING</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">o</td> |
| <td style="text-align: left">ST_MLineFromText(wkt [, srid ])</td> |
| <td style="text-align: left">Converts WKT → MULTILINESTRING</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">o</td> |
| <td style="text-align: left">ST_MPointFromText(wkt [, srid ])</td> |
| <td style="text-align: left">Converts WKT → MULTIPOINT</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">o</td> |
| <td style="text-align: left">ST_MPolyFromText(wkt [, srid ]) Converts WKT → MULTIPOLYGON</td> |
| <td style="text-align: left"> </td> |
| </tr> |
| <tr> |
| <td style="text-align: left">o</td> |
| <td style="text-align: left">ST_PointFromText(wkt [, srid ])</td> |
| <td style="text-align: left">Converts WKT → POINT</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">o</td> |
| <td style="text-align: left">ST_PolyFromText(wkt [, srid ])</td> |
| <td style="text-align: left">Converts WKT → POLYGON</td> |
| </tr> |
| </tbody> |
| </table> |
| |
| <p>Not implemented:</p> |
| |
| <ul> |
| <li>ST_AsBinary(geom) GEOMETRY → WKB</li> |
| <li>ST_AsGML(geom) GEOMETRY → GML</li> |
| <li>ST_Force2D(geom) 3D GEOMETRY → 2D GEOMETRY</li> |
| <li>ST_GeomFromGML(gml [, srid ]) GML → GEOMETRY</li> |
| <li>ST_GeomFromWKB(wkb [, srid ]) WKB → GEOMETRY</li> |
| <li>ST_GoogleMapLink(geom [, layerType [, zoom ]]) GEOMETRY → Google map link</li> |
| <li>ST_LineFromWKB(wkb [, srid ]) WKB → LINESTRING</li> |
| <li>ST_OSMMapLink(geom [, marker ]) GEOMETRY → OSM map link</li> |
| <li>ST_PointFromWKB(wkb [, srid ]) WKB → POINT</li> |
| <li>ST_PolyFromWKB(wkb [, srid ]) WKB → POLYGON</li> |
| <li>ST_ToMultiLine(geom) Converts the coordinates of <em>geom</em> (which may be a GEOMETRYCOLLECTION) into a MULTILINESTRING</li> |
| <li>ST_ToMultiPoint(geom)) Converts the coordinates of <em>geom</em> (which may be a GEOMETRYCOLLECTION) into a MULTIPOINT</li> |
| <li>ST_ToMultiSegments(geom) Converts <em>geom</em> (which may be a GEOMETRYCOLLECTION) into a set of distinct segments stored in a MULTILINESTRING</li> |
| </ul> |
| |
| <h4 id="geometry-conversion-functions-3d">Geometry conversion functions (3D)</h4> |
| |
| <p>Not implemented:</p> |
| |
| <ul> |
| <li>ST_Force3D(geom) 2D GEOMETRY → 3D GEOMETRY</li> |
| </ul> |
| |
| <h4 id="geometry-creation-functions-2d">Geometry creation functions (2D)</h4> |
| |
| <table> |
| <thead> |
| <tr> |
| <th style="text-align: left">C</th> |
| <th style="text-align: left">Operator syntax</th> |
| <th style="text-align: left">Description</th> |
| </tr> |
| </thead> |
| <tbody> |
| <tr> |
| <td style="text-align: left">o</td> |
| <td style="text-align: left">ST_MakeLine(point1 [, point ]*)</td> |
| <td style="text-align: left">Creates a line-string from the given POINTs (or MULTIPOINTs)</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">p</td> |
| <td style="text-align: left">ST_MakePoint(x, y [, z ])</td> |
| <td style="text-align: left">Alias for <code class="highlighter-rouge">ST_Point</code></td> |
| </tr> |
| <tr> |
| <td style="text-align: left">o</td> |
| <td style="text-align: left">ST_Point(x, y [, z ])</td> |
| <td style="text-align: left">Constructs a point from two or three coordinates</td> |
| </tr> |
| </tbody> |
| </table> |
| |
| <p>Not implemented:</p> |
| |
| <ul> |
| <li>ST_BoundingCircle(geom) Returns the minimum bounding circle of <em>geom</em></li> |
| <li>ST_Expand(geom, distance) Expands <em>geom</em>’s envelope</li> |
| <li>ST_Expand(geom, deltaX, deltaY) Expands <em>geom</em>’s envelope</li> |
| <li>ST_MakeEllipse(point, width, height) Constructs an ellipse</li> |
| <li>ST_MakeEnvelope(xMin, yMin, xMax, yMax [, srid ]) Creates a rectangular POLYGON</li> |
| <li>ST_MakeGrid(geom, deltaX, deltaY) Calculates a regular grid of POLYGONs based on <em>geom</em></li> |
| <li>ST_MakeGridPoints(geom, deltaX, deltaY) Calculates a regular grid of points based on <em>geom</em></li> |
| <li>ST_MakePolygon(lineString [, hole ]<em>) Creates a POLYGON from *lineString</em> with the given holes (which are required to be closed LINESTRINGs)</li> |
| <li>ST_MinimumDiameter(geom) Returns the minimum diameter of <em>geom</em></li> |
| <li>ST_MinimumRectangle(geom) Returns the minimum rectangle enclosing <em>geom</em></li> |
| <li>ST_OctogonalEnvelope(geom) Returns the octogonal envelope of <em>geom</em></li> |
| <li>ST_RingBuffer(geom, distance, bufferCount [, endCapStyle [, doDifference]]) Returns a MULTIPOLYGON of buffers centered at <em>geom</em> and of increasing buffer size</li> |
| </ul> |
| |
| <h3 id="geometry-creation-functions-3d">Geometry creation functions (3D)</h3> |
| |
| <p>Not implemented:</p> |
| |
| <ul> |
| <li>ST_Extrude(geom, height [, flag]) Extrudes a GEOMETRY</li> |
| <li>ST_GeometryShadow(geom, point, height) Computes the shadow footprint of <em>geom</em></li> |
| <li>ST_GeometryShadow(geom, azimuth, altitude, height [, unify ]) Computes the shadow footprint of <em>geom</em></li> |
| </ul> |
| |
| <h4 id="geometry-properties-2d">Geometry properties (2D)</h4> |
| |
| <table> |
| <thead> |
| <tr> |
| <th style="text-align: left">C</th> |
| <th style="text-align: left">Operator syntax</th> |
| <th style="text-align: left">Description</th> |
| </tr> |
| </thead> |
| <tbody> |
| <tr> |
| <td style="text-align: left">o</td> |
| <td style="text-align: left">ST_Boundary(geom [, srid ])</td> |
| <td style="text-align: left">Returns the boundary of <em>geom</em></td> |
| </tr> |
| <tr> |
| <td style="text-align: left">o</td> |
| <td style="text-align: left">ST_Distance(geom1, geom2)</td> |
| <td style="text-align: left">Returns the distance between <em>geom1</em> and <em>geom2</em></td> |
| </tr> |
| <tr> |
| <td style="text-align: left">o</td> |
| <td style="text-align: left">ST_GeometryType(geom)</td> |
| <td style="text-align: left">Returns the type of <em>geom</em></td> |
| </tr> |
| <tr> |
| <td style="text-align: left">o</td> |
| <td style="text-align: left">ST_GeometryTypeCode(geom)</td> |
| <td style="text-align: left">Returns the OGC SFS type code of <em>geom</em></td> |
| </tr> |
| <tr> |
| <td style="text-align: left">o</td> |
| <td style="text-align: left">ST_Envelope(geom [, srid ])</td> |
| <td style="text-align: left">Returns the envelope of <em>geom</em> (which may be a GEOMETRYCOLLECTION) as a GEOMETRY</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">o</td> |
| <td style="text-align: left">ST_X(geom)</td> |
| <td style="text-align: left">Returns the x-value of the first coordinate of <em>geom</em></td> |
| </tr> |
| <tr> |
| <td style="text-align: left">o</td> |
| <td style="text-align: left">ST_Y(geom)</td> |
| <td style="text-align: left">Returns the y-value of the first coordinate of <em>geom</em></td> |
| </tr> |
| </tbody> |
| </table> |
| |
| <p>Not implemented:</p> |
| |
| <ul> |
| <li>ST_Centroid(geom) Returns the centroid of <em>geom</em> (which may be a GEOMETRYCOLLECTION)</li> |
| <li>ST_CompactnessRatio(polygon) Returns the square root of <em>polygon</em>’s area divided by the area of the circle with circumference equal to its perimeter</li> |
| <li>ST_CoordDim(geom) Returns the dimension of the coordinates of <em>geom</em></li> |
| <li>ST_Dimension(geom) Returns the dimension of <em>geom</em></li> |
| <li>ST_EndPoint(lineString) Returns the last coordinate of <em>lineString</em></li> |
| <li>ST_Envelope(geom [, srid ]) Returns the envelope of <em>geom</em> (which may be a GEOMETRYCOLLECTION) as a GEOMETRY</li> |
| <li>ST_Explode(query [, fieldName]) Explodes the GEOMETRYCOLLECTIONs in the <em>fieldName</em> column of a query into multiple geometries</li> |
| <li>ST_Extent(geom) Returns the minimum bounding box of <em>geom</em> (which may be a GEOMETRYCOLLECTION)</li> |
| <li>ST_ExteriorRing(polygon) Returns the exterior ring of <em>polygon</em> as a linear-ring</li> |
| <li>ST_GeometryN(geomCollection, n) Returns the <em>n</em>th GEOMETRY of <em>geomCollection</em></li> |
| <li>ST_InteriorRingN(polygon, n) Returns the <em>n</em>th interior ring of <em>polygon</em></li> |
| <li>ST_IsClosed(geom) Returns whether <em>geom</em> is a closed LINESTRING or MULTILINESTRING</li> |
| <li>ST_IsEmpty(geom) Returns whether <em>geom</em> is empty</li> |
| <li>ST_IsRectangle(geom) Returns whether <em>geom</em> is a rectangle</li> |
| <li>ST_IsRing(geom) Returns whether <em>geom</em> is a closed and simple line-string or MULTILINESTRING</li> |
| <li>ST_IsSimple(geom) Returns whether <em>geom</em> is simple</li> |
| <li>ST_IsValid(geom) Returns whether <em>geom</em> is valid</li> |
| <li>ST_IsValidDetail(geom [, selfTouchValid ]) Returns a valid detail as an array of objects</li> |
| <li>ST_IsValidReason(geom [, selfTouchValid ]) Returns text stating whether <em>geom</em> is valid, and if not valid, a reason why</li> |
| <li>ST_NPoints(geom) Returns the number of points in <em>geom</em></li> |
| <li>ST_NumGeometries(geom) Returns the number of geometries in <em>geom</em> (1 if it is not a GEOMETRYCOLLECTION)</li> |
| <li>ST_NumInteriorRing(geom) Alias for <code class="highlighter-rouge">ST_NumInteriorRings</code></li> |
| <li>ST_NumInteriorRings(geom) Returns the number of interior rings of <em>geom</em></li> |
| <li>ST_NumPoints(lineString) Returns the number of points in <em>lineString</em></li> |
| <li>ST_PointN(geom, n) Returns the <em>n</em>th point of a <em>lineString</em></li> |
| <li>ST_PointOnSurface(geom) Returns an interior or boundary point of <em>geom</em></li> |
| <li>ST_SRID(geom) Returns SRID value of <em>geom</em> or 0 if it does not have one</li> |
| <li>ST_StartPoint(lineString) Returns the first coordinate of <em>lineString</em></li> |
| <li>ST_XMax(geom) Returns the maximum x-value of <em>geom</em></li> |
| <li>ST_XMin(geom) Returns the minimum x-value of <em>geom</em></li> |
| <li>ST_YMax(geom) Returns the maximum y-value of <em>geom</em></li> |
| <li>ST_YMin(geom) Returns the minimum y-value of <em>geom</em></li> |
| </ul> |
| |
| <h4 id="geometry-properties-3d">Geometry properties (3D)</h4> |
| |
| <table> |
| <thead> |
| <tr> |
| <th style="text-align: left">C</th> |
| <th style="text-align: left">Operator syntax</th> |
| <th style="text-align: left">Description</th> |
| </tr> |
| </thead> |
| <tbody> |
| <tr> |
| <td style="text-align: left">p</td> |
| <td style="text-align: left">ST_Is3D(s)</td> |
| <td style="text-align: left">Returns whether <em>geom</em> has at least one z-coordinate</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">o</td> |
| <td style="text-align: left">ST_Z(geom)</td> |
| <td style="text-align: left">Returns the z-value of the first coordinate of <em>geom</em></td> |
| </tr> |
| </tbody> |
| </table> |
| |
| <p>Not implemented:</p> |
| |
| <ul> |
| <li>ST_ZMax(geom) Returns the maximum z-value of <em>geom</em></li> |
| <li>ST_ZMin(geom) Returns the minimum z-value of <em>geom</em></li> |
| </ul> |
| |
| <h3 id="geometry-predicates">Geometry predicates</h3> |
| |
| <table> |
| <thead> |
| <tr> |
| <th style="text-align: left">C</th> |
| <th style="text-align: left">Operator syntax</th> |
| <th style="text-align: left">Description</th> |
| </tr> |
| </thead> |
| <tbody> |
| <tr> |
| <td style="text-align: left">o</td> |
| <td style="text-align: left">ST_Contains(geom1, geom2)</td> |
| <td style="text-align: left">Returns whether <em>geom1</em> contains <em>geom2</em></td> |
| </tr> |
| <tr> |
| <td style="text-align: left">p</td> |
| <td style="text-align: left">ST_ContainsProperly(geom1, geom2)</td> |
| <td style="text-align: left">Returns whether <em>geom1</em> contains <em>geom2</em> but does not intersect its boundary</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">o</td> |
| <td style="text-align: left">ST_Crosses(geom1, geom2)</td> |
| <td style="text-align: left">Returns whether <em>geom1</em> crosses <em>geom2</em></td> |
| </tr> |
| <tr> |
| <td style="text-align: left">o</td> |
| <td style="text-align: left">ST_Disjoint(geom1, geom2)</td> |
| <td style="text-align: left">Returns whether <em>geom1</em> and <em>geom2</em> are disjoint</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">p</td> |
| <td style="text-align: left">ST_DWithin(geom1, geom2, distance)</td> |
| <td style="text-align: left">Returns whether <em>geom1</em> and <em>geom</em> are within <em>distance</em> of one another</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">o</td> |
| <td style="text-align: left">ST_EnvelopesIntersect(geom1, geom2)</td> |
| <td style="text-align: left">Returns whether the envelope of <em>geom1</em> intersects the envelope of <em>geom2</em></td> |
| </tr> |
| <tr> |
| <td style="text-align: left">o</td> |
| <td style="text-align: left">ST_Equals(geom1, geom2)</td> |
| <td style="text-align: left">Returns whether <em>geom1</em> equals <em>geom2</em></td> |
| </tr> |
| <tr> |
| <td style="text-align: left">o</td> |
| <td style="text-align: left">ST_Intersects(geom1, geom2)</td> |
| <td style="text-align: left">Returns whether <em>geom1</em> intersects <em>geom2</em></td> |
| </tr> |
| <tr> |
| <td style="text-align: left">o</td> |
| <td style="text-align: left">ST_Overlaps(geom1, geom2)</td> |
| <td style="text-align: left">Returns whether <em>geom1</em> overlaps <em>geom2</em></td> |
| </tr> |
| <tr> |
| <td style="text-align: left">o</td> |
| <td style="text-align: left">ST_Touches(geom1, geom2)</td> |
| <td style="text-align: left">Returns whether <em>geom1</em> touches <em>geom2</em></td> |
| </tr> |
| <tr> |
| <td style="text-align: left">o</td> |
| <td style="text-align: left">ST_Within(geom1, geom2)</td> |
| <td style="text-align: left">Returns whether <em>geom1</em> is within <em>geom2</em></td> |
| </tr> |
| </tbody> |
| </table> |
| |
| <p>Not implemented:</p> |
| |
| <ul> |
| <li>ST_Covers(geom1, geom2) Returns whether no point in <em>geom2</em> is outside <em>geom1</em></li> |
| <li>ST_OrderingEquals(geom1, geom2) Returns whether <em>geom1</em> equals <em>geom2</em> and their coordinates and component Geometries are listed in the same order</li> |
| <li>ST_Relate(geom1, geom2) Returns the DE-9IM intersection matrix of <em>geom1</em> and <em>geom2</em></li> |
| <li>ST_Relate(geom1, geom2, iMatrix) Returns whether <em>geom1</em> and <em>geom2</em> are related by the given intersection matrix <em>iMatrix</em></li> |
| </ul> |
| |
| <h4 id="geometry-operators-2d">Geometry operators (2D)</h4> |
| |
| <p>The following functions combine 2D geometries.</p> |
| |
| <table> |
| <thead> |
| <tr> |
| <th style="text-align: left">C</th> |
| <th style="text-align: left">Operator syntax</th> |
| <th style="text-align: left">Description</th> |
| </tr> |
| </thead> |
| <tbody> |
| <tr> |
| <td style="text-align: left">o</td> |
| <td style="text-align: left">ST_Buffer(geom, distance [, quadSegs | style ])</td> |
| <td style="text-align: left">Computes a buffer around <em>geom</em></td> |
| </tr> |
| <tr> |
| <td style="text-align: left">o</td> |
| <td style="text-align: left">ST_Union(geom1, geom2)</td> |
| <td style="text-align: left">Computes the union of <em>geom1</em> and <em>geom2</em></td> |
| </tr> |
| <tr> |
| <td style="text-align: left">o</td> |
| <td style="text-align: left">ST_Union(geomCollection)</td> |
| <td style="text-align: left">Computes the union of the geometries in <em>geomCollection</em></td> |
| </tr> |
| </tbody> |
| </table> |
| |
| <p>See also: the <code class="highlighter-rouge">ST_Union</code> aggregate function.</p> |
| |
| <p>Not implemented:</p> |
| |
| <ul> |
| <li>ST_ConvexHull(geom) Computes the smallest convex polygon that contains all the points in <em>geom</em></li> |
| <li>ST_Difference(geom1, geom2) Computes the difference between two geometries</li> |
| <li>ST_Intersection(geom1, geom2) Computes the intersection of two geometries</li> |
| <li>ST_SymDifference(geom1, geom2) Computes the symmetric difference between two geometries</li> |
| </ul> |
| |
| <h4 id="affine-transformation-functions-3d-and-2d">Affine transformation functions (3D and 2D)</h4> |
| |
| <p>Not implemented:</p> |
| |
| <ul> |
| <li>ST_Rotate(geom, angle [, origin | x, y]) Rotates a <em>geom</em> counter-clockwise by <em>angle</em> (in radians) about <em>origin</em> (or the point (<em>x</em>, <em>y</em>))</li> |
| <li>ST_Scale(geom, xFactor, yFactor [, zFactor ]) Scales <em>geom</em> by multiplying the ordinates by the indicated scale factors</li> |
| <li>ST_Translate(geom, x, y, [, z]) Translates <em>geom</em></li> |
| </ul> |
| |
| <h4 id="geometry-editing-functions-2d">Geometry editing functions (2D)</h4> |
| |
| <p>The following functions modify 2D geometries.</p> |
| |
| <p>Not implemented:</p> |
| |
| <ul> |
| <li>ST_AddPoint(geom, point [, tolerance ]) Adds <em>point</em> to <em>geom</em> with a given <em>tolerance</em> (default 0)</li> |
| <li>ST_CollectionExtract(geom, dimension) Filters <em>geom</em>, returning a multi-geometry of those members with a given <em>dimension</em> (1 = point, 2 = line-string, 3 = polygon)</li> |
| <li>ST_Densify(geom, tolerance) Inserts extra vertices every <em>tolerance</em> along the line segments of <em>geom</em></li> |
| <li>ST_FlipCoordinates(geom) Flips the X and Y coordinates of <em>geom</em></li> |
| <li>ST_Holes(geom) Returns the holes in <em>geom</em> (which may be a GEOMETRYCOLLECTION)</li> |
| <li>ST_Normalize(geom) Converts <em>geom</em> to normal form</li> |
| <li>ST_RemoveDuplicatedCoordinates(geom) Removes duplicated coordinates from <em>geom</em></li> |
| <li>ST_RemoveHoles(geom) Removes a <em>geom</em>’s holes</li> |
| <li>ST_RemovePoints(geom, poly) Removes all coordinates of <em>geom</em> located within <em>poly</em>; null if all coordinates are removed</li> |
| <li>ST_RemoveRepeatedPoints(geom, tolerance) Removes from <em>geom</em> all repeated points (or points within <em>tolerance</em> of another point)</li> |
| <li>ST_Reverse(geom) Reverses the vertex order of <em>geom</em></li> |
| </ul> |
| |
| <h4 id="geometry-editing-functions-3d">Geometry editing functions (3D)</h4> |
| |
| <p>The following functions modify 3D geometries.</p> |
| |
| <p>Not implemented:</p> |
| |
| <ul> |
| <li>ST_AddZ(geom, zToAdd) Adds <em>zToAdd</em> to the z-coordinate of <em>geom</em></li> |
| <li>ST_Interpolate3DLine(geom) Returns <em>geom</em> with an interpolation of z values, or null if it is not a line-string or MULTILINESTRING</li> |
| <li>ST_MultiplyZ(geom, zFactor) Returns <em>geom</em> with its z-values multiplied by <em>zFactor</em></li> |
| <li>ST_Reverse3DLine(geom [, sortOrder ]) Potentially reverses <em>geom</em> according to the z-values of its first and last coordinates</li> |
| <li>ST_UpdateZ(geom, newZ [, updateCondition ]) Updates the z-values of <em>geom</em></li> |
| <li>ST_ZUpdateLineExtremities(geom, startZ, endZ [, interpolate ]) Updates the start and end z-values of <em>geom</em></li> |
| </ul> |
| |
| <h4 id="geometry-measurement-functions-2d">Geometry measurement functions (2D)</h4> |
| |
| <p>Not implemented:</p> |
| |
| <ul> |
| <li>ST_Area(geom) Returns the area of <em>geom</em> (which may be a GEOMETRYCOLLECTION)</li> |
| <li>ST_ClosestCoordinate(geom, point) Returns the coordinate(s) of <em>geom</em> closest to <em>point</em></li> |
| <li>ST_ClosestPoint(geom1, geom2) Returns the point of <em>geom1</em> closest to <em>geom2</em></li> |
| <li>ST_FurthestCoordinate(geom, point) Returns the coordinate(s) of <em>geom</em> that are furthest from <em>point</em></li> |
| <li>ST_Length(lineString) Returns the length of <em>lineString</em></li> |
| <li>ST_LocateAlong(geom, segmentLengthFraction, offsetDistance) Returns a MULTIPOINT containing points along the line segments of <em>geom</em> at <em>segmentLengthFraction</em> and <em>offsetDistance</em></li> |
| <li>ST_LongestLine(geom1, geom2) Returns the 2-dimensional longest line-string between the points of <em>geom1</em> and <em>geom2</em></li> |
| <li>ST_MaxDistance(geom1, geom2) Computes the maximum distance between <em>geom1</em> and <em>geom2</em></li> |
| <li>ST_Perimeter(polygon) Returns the length of the perimeter of <em>polygon</em> (which may be a MULTIPOLYGON)</li> |
| <li>ST_ProjectPoint(point, lineString) Projects <em>point</em> onto a <em>lineString</em> (which may be a MULTILINESTRING)</li> |
| </ul> |
| |
| <h4 id="geometry-measurement-functions-3d">Geometry measurement functions (3D)</h4> |
| |
| <p>Not implemented:</p> |
| |
| <ul> |
| <li>ST_3DArea(geom) Return a polygon’s 3D area</li> |
| <li>ST_3DLength(geom) Returns the 3D length of a line-string</li> |
| <li>ST_3DPerimeter(geom) Returns the 3D perimeter of a polygon or MULTIPOLYGON</li> |
| <li>ST_SunPosition(point [, timestamp ]) Computes the sun position at <em>point</em> and <em>timestamp</em> (now by default)</li> |
| </ul> |
| |
| <h4 id="geometry-processing-functions-2d">Geometry processing functions (2D)</h4> |
| |
| <p>The following functions process geometries.</p> |
| |
| <p>Not implemented:</p> |
| |
| <ul> |
| <li>ST_LineIntersector(geom1, geom2) Splits <em>geom1</em> (a line-string) with <em>geom2</em></li> |
| <li>ST_LineMerge(geom) Merges a collection of linear components to form a line-string of maximal length</li> |
| <li>ST_MakeValid(geom [, preserveGeomDim [, preserveDuplicateCoord [, preserveCoordDim]]]) Makes <em>geom</em> valid</li> |
| <li>ST_Polygonize(geom) Creates a MULTIPOLYGON from edges of <em>geom</em></li> |
| <li>ST_PrecisionReducer(geom, n) Reduces <em>geom</em>’s precision to <em>n</em> decimal places</li> |
| <li>ST_RingSideBuffer(geom, distance, bufferCount [, endCapStyle [, doDifference]]) Computes a ring buffer on one side</li> |
| <li>ST_SideBuffer(geom, distance [, bufferStyle ]) Compute a single buffer on one side</li> |
| <li>ST_Simplify(geom, distance) Simplifies <em>geom</em> using the <a href="https://en.wikipedia.org/wiki/Ramer%E2%80%93Douglas%E2%80%93Peucker_algorithm">Douglas-Peuker algorithm</a> with a <em>distance</em> tolerance</li> |
| <li>ST_SimplifyPreserveTopology(geom) Simplifies <em>geom</em>, preserving its topology</li> |
| <li>ST_Snap(geom1, geom2, tolerance) Snaps <em>geom1</em> and <em>geom2</em> together</li> |
| <li>ST_Split(geom1, geom2 [, tolerance]) Splits <em>geom1</em> by <em>geom2</em> using <em>tolerance</em> (default 1E-6) to determine where the point splits the line</li> |
| </ul> |
| |
| <h4 id="geometry-projection-functions">Geometry projection functions</h4> |
| |
| <table> |
| <thead> |
| <tr> |
| <th style="text-align: left">C</th> |
| <th style="text-align: left">Operator syntax</th> |
| <th style="text-align: left">Description</th> |
| </tr> |
| </thead> |
| <tbody> |
| <tr> |
| <td style="text-align: left">o</td> |
| <td style="text-align: left">ST_SetSRID(geom, srid)</td> |
| <td style="text-align: left">Returns a copy of <em>geom</em> with a new SRID</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">o</td> |
| <td style="text-align: left">ST_Transform(geom, srid)</td> |
| <td style="text-align: left">Transforms <em>geom</em> from one coordinate reference system (CRS) to the CRS specified by <em>srid</em></td> |
| </tr> |
| </tbody> |
| </table> |
| |
| <h4 id="trigonometry-functions">Trigonometry functions</h4> |
| |
| <p>Not implemented:</p> |
| |
| <ul> |
| <li>ST_Azimuth(point1, point2) Return the azimuth of the segment from <em>point1</em> to <em>point2</em></li> |
| </ul> |
| |
| <h4 id="topography-functions">Topography functions</h4> |
| |
| <p>Not implemented:</p> |
| |
| <ul> |
| <li>ST_TriangleAspect(geom) Returns the aspect of a triangle</li> |
| <li>ST_TriangleContouring(query [, z1, z2, z3 ][, varArgs]*) Splits triangles into smaller triangles according to classes</li> |
| <li>ST_TriangleDirection(geom) Computes the direction of steepest ascent of a triangle and returns it as a line-string</li> |
| <li>ST_TriangleSlope(geom) Computes the slope of a triangle as a percentage</li> |
| <li>ST_Voronoi(geom [, outDimension [, envelopePolygon ]]) Creates a Voronoi diagram</li> |
| </ul> |
| |
| <h4 id="triangulation-functions">Triangulation functions</h4> |
| |
| <p>Not implemented:</p> |
| |
| <ul> |
| <li>ST_ConstrainedDelaunay(geom [, flag [, quality ]]) Computes a constrained Delaunay triangulation based on <em>geom</em></li> |
| <li>ST_Delaunay(geom [, flag [, quality ]]) Computes a Delaunay triangulation based on points</li> |
| <li>ST_Tessellate(polygon) Tessellates <em>polygon</em> (may be MULTIPOLYGON) with adaptive triangles</li> |
| </ul> |
| |
| <h4 id="geometry-aggregate-functions">Geometry aggregate functions</h4> |
| |
| <p>Not implemented:</p> |
| |
| <ul> |
| <li>ST_Accum(geom) Accumulates <em>geom</em> into a GEOMETRYCOLLECTION (or MULTIPOINT, MULTILINESTRING or MULTIPOLYGON if possible)</li> |
| <li>ST_Collect(geom) Alias for <code class="highlighter-rouge">ST_Accum</code></li> |
| <li>ST_Union(geom) Computes the union of geometries</li> |
| </ul> |
| |
| <h3 id="json-functions">JSON Functions</h3> |
| |
| <p>In the following:</p> |
| |
| <ul> |
| <li><em>jsonValue</em> is a character string containing a JSON value;</li> |
| <li><em>path</em> is a character string containing a JSON path expression; mode flag <code class="highlighter-rouge">strict</code> or <code class="highlighter-rouge">lax</code> should be specified in the beginning of <em>path</em>.</li> |
| </ul> |
| |
| <h4 id="query-functions">Query Functions</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">JSON_EXISTS(jsonValue, path [ { TRUE | FALSE | UNKNOWN | ERROR ) ON ERROR } )</td> |
| <td style="text-align: left">Whether a <em>jsonValue</em> satisfies a search criterion described using JSON path expression <em>path</em></td> |
| </tr> |
| <tr> |
| <td style="text-align: left">JSON_VALUE(jsonValue, path [ RETURNING type ] [ { ERROR | NULL | DEFAULT expr } ON EMPTY ] [ { ERROR | NULL | DEFAULT expr } ON ERROR ] )</td> |
| <td style="text-align: left">Extract an SQL scalar from a <em>jsonValue</em> using JSON path expression <em>path</em></td> |
| </tr> |
| <tr> |
| <td style="text-align: left">JSON_QUERY(jsonValue, path [ { WITHOUT [ ARRAY ] | WITH [ CONDITIONAL | UNCONDITIONAL ] [ ARRAY ] } WRAPPER ] [ { ERROR | NULL | EMPTY ARRAY | EMPTY OBJECT } ON EMPTY ] [ { ERROR | NULL | EMPTY ARRAY | EMPTY OBJECT } ON ERROR ] )</td> |
| <td style="text-align: left">Extract a JSON object or JSON array from <em>jsonValue</em> using the <em>path</em> JSON path expression</td> |
| </tr> |
| </tbody> |
| </table> |
| |
| <p>Note:</p> |
| |
| <ul> |
| <li>The <code class="highlighter-rouge">ON ERROR</code> and <code class="highlighter-rouge">ON EMPTY</code> clauses define the fallback |
| behavior of the function when an error is thrown or a null value |
| is about to be returned.</li> |
| <li>The <code class="highlighter-rouge">ARRAY WRAPPER</code> clause defines how to represent a JSON array result |
| in <code class="highlighter-rouge">JSON_QUERY</code> function. The following examples compare the wrapper |
| behaviors.</li> |
| </ul> |
| |
| <p>Example Data:</p> |
| |
| <pre><code class="language-JSON">{"a": "[1,2]", "b": [1,2], "c": "hi"} |
| </code></pre> |
| |
| <p>Comparison:</p> |
| |
| <table> |
| <thead> |
| <tr> |
| <th style="text-align: left">Operator</th> |
| <th style="text-align: left">$.a</th> |
| <th style="text-align: left">$.b</th> |
| <th style="text-align: left">$.c</th> |
| </tr> |
| </thead> |
| <tbody> |
| <tr> |
| <td style="text-align: left">JSON_VALUE</td> |
| <td style="text-align: left">[1, 2]</td> |
| <td style="text-align: left">error</td> |
| <td style="text-align: left">hi</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">JSON QUERY WITHOUT ARRAY WRAPPER</td> |
| <td style="text-align: left">error</td> |
| <td style="text-align: left">[1, 2]</td> |
| <td style="text-align: left">error</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">JSON QUERY WITH UNCONDITIONAL ARRAY WRAPPER</td> |
| <td style="text-align: left">[ “[1,2]” ]</td> |
| <td style="text-align: left">[ [1,2] ]</td> |
| <td style="text-align: left">[ “hi” ]</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">JSON QUERY WITH CONDITIONAL ARRAY WRAPPER</td> |
| <td style="text-align: left">[ “[1,2]” ]</td> |
| <td style="text-align: left">[1,2]</td> |
| <td style="text-align: left">[ “hi” ]</td> |
| </tr> |
| </tbody> |
| </table> |
| |
| <p>Not implemented:</p> |
| |
| <ul> |
| <li>JSON_TABLE</li> |
| </ul> |
| |
| <h4 id="constructor-functions">Constructor Functions</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">JSON_OBJECT( { [ KEY ] name VALUE value [ FORMAT JSON ] | name : value [ FORMAT JSON ] } * [ { NULL | ABSENT } ON NULL ] )</td> |
| <td style="text-align: left">Construct JSON object using a series of key (<em>name</em>) value (<em>value</em>) pairs</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">JSON_OBJECTAGG( { [ KEY ] name VALUE value [ FORMAT JSON ] | name : value [ FORMAT JSON ] } [ { NULL | ABSENT } ON NULL ] )</td> |
| <td style="text-align: left">Aggregate function to construct a JSON object using a key (<em>name</em>) value (<em>value</em>) pair</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">JSON_ARRAY( { value [ FORMAT JSON ] } * [ { NULL | ABSENT } ON NULL ] )</td> |
| <td style="text-align: left">Construct a JSON array using a series of values (<em>value</em>)</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">JSON_ARRAYAGG( value [ FORMAT JSON ] [ ORDER BY orderItem [, orderItem ]* ] [ { NULL | ABSENT } ON NULL ] )</td> |
| <td style="text-align: left">Aggregate function to construct a JSON array using a value (<em>value</em>)</td> |
| </tr> |
| </tbody> |
| </table> |
| |
| <p>Note:</p> |
| |
| <ul> |
| <li>The flag <code class="highlighter-rouge">FORMAT JSON</code> indicates the value is formatted as JSON |
| character string. When <code class="highlighter-rouge">FORMAT JSON</code> is used, the value should be |
| de-parse from JSON character string to a SQL structured value.</li> |
| <li><code class="highlighter-rouge">ON NULL</code> clause defines how the JSON output represents null |
| values. The default null behavior of <code class="highlighter-rouge">JSON_OBJECT</code> and |
| <code class="highlighter-rouge">JSON_OBJECTAGG</code> is <code class="highlighter-rouge">NULL ON NULL</code>, and for <code class="highlighter-rouge">JSON_ARRAY</code> and |
| <code class="highlighter-rouge">JSON_ARRAYAGG</code> it is <code class="highlighter-rouge">ABSENT ON NULL</code>.</li> |
| <li>If <code class="highlighter-rouge">ORDER BY</code> clause is provided, <code class="highlighter-rouge">JSON_ARRAYAGG</code> sorts the |
| input rows into the specified order before performing aggregation.</li> |
| </ul> |
| |
| <h4 id="comparison-operators-1">Comparison Operators</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">jsonValue IS JSON [ VALUE ]</td> |
| <td style="text-align: left">Whether <em>jsonValue</em> is a JSON value</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">jsonValue IS NOT JSON [ VALUE ]</td> |
| <td style="text-align: left">Whether <em>jsonValue</em> is not a JSON value</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">jsonValue IS JSON SCALAR</td> |
| <td style="text-align: left">Whether <em>jsonValue</em> is a JSON scalar value</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">jsonValue IS NOT JSON SCALAR</td> |
| <td style="text-align: left">Whether <em>jsonValue</em> is not a JSON scalar value</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">jsonValue IS JSON OBJECT</td> |
| <td style="text-align: left">Whether <em>jsonValue</em> is a JSON object</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">jsonValue IS NOT JSON OBJECT</td> |
| <td style="text-align: left">Whether <em>jsonValue</em> is not a JSON object</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">jsonValue IS JSON ARRAY</td> |
| <td style="text-align: left">Whether <em>jsonValue</em> is a JSON array</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">jsonValue IS NOT JSON ARRAY</td> |
| <td style="text-align: left">Whether <em>jsonValue</em> is not a JSON array</td> |
| </tr> |
| </tbody> |
| </table> |
| |
| <h3 id="dialect-specific-operators">Dialect-specific Operators</h3> |
| |
| <p>The following operators are not in the SQL standard, and are not enabled in |
| Calcite’s default operator table. They are only available for use in queries |
| if your session has enabled an extra operator table.</p> |
| |
| <p>To enable an operator table, set the |
| <a href="/docs/adapter.html#jdbc-connect-string-parameters">fun</a> |
| connect string parameter.</p> |
| |
| <p>The ‘C’ (compatibility) column contains value |
| ‘m’ for MySQL (‘fun=mysql’ in the connect string), |
| ‘o’ for Oracle (‘fun=oracle’ in the connect string), |
| ‘p’ for PostgreSQL (‘fun=postgresql’ in the connect string).</p> |
| |
| <p>One operator name may correspond to multiple SQL dialects, but with different |
| semantics.</p> |
| |
| <table> |
| <thead> |
| <tr> |
| <th style="text-align: left">C</th> |
| <th style="text-align: left">Operator syntax</th> |
| <th style="text-align: left">Description</th> |
| </tr> |
| </thead> |
| <tbody> |
| <tr> |
| <td style="text-align: left">p</td> |
| <td style="text-align: left">expr :: type</td> |
| <td style="text-align: left">Casts <em>expr</em> to <em>type</em></td> |
| </tr> |
| <tr> |
| <td style="text-align: left">o</td> |
| <td style="text-align: left">CHR(integer)</td> |
| <td style="text-align: left">Returns the character having the binary equivalent to <em>integer</em> as a CHAR value</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">o</td> |
| <td style="text-align: left">COSH(numeric)</td> |
| <td style="text-align: left">Returns the hyperbolic cosine of <em>numeric</em></td> |
| </tr> |
| <tr> |
| <td style="text-align: left">m o p</td> |
| <td style="text-align: left">CONCAT(string [, string ]*)</td> |
| <td style="text-align: left">Concatenates two or more strings</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">p</td> |
| <td style="text-align: left">CONVERT_TIMEZONE(tz1, tz2, datetime)</td> |
| <td style="text-align: left">Converts the timezone of <em>datetime</em> from <em>tz1</em> to <em>tz2</em></td> |
| </tr> |
| <tr> |
| <td style="text-align: left">m</td> |
| <td style="text-align: left">DAYNAME(datetime)</td> |
| <td style="text-align: left">Returns the name, in the connection’s locale, of the weekday in <em>datetime</em>; for example, it returns ‘星期日’ for both DATE ‘2020-02-10’ and TIMESTAMP ‘2020-02-10 10:10:10’</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">o</td> |
| <td style="text-align: left">DECODE(value, value1, result1 [, valueN, resultN ]* [, default ])</td> |
| <td style="text-align: left">Compares <em>value</em> to each <em>valueN</em> value one by one; if <em>value</em> is equal to a <em>valueN</em>, returns the corresponding <em>resultN</em>, else returns <em>default</em>, or NULL if <em>default</em> is not specified</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">p</td> |
| <td style="text-align: left">DIFFERENCE(string, string)</td> |
| <td style="text-align: left">Returns a measure of the similarity of two strings, namely the number of character positions that their <code class="highlighter-rouge">SOUNDEX</code> values have in common: 4 if the <code class="highlighter-rouge">SOUNDEX</code> values are same and 0 if the <code class="highlighter-rouge">SOUNDEX</code> values are totally different</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">o</td> |
| <td style="text-align: left">EXTRACT(xml, xpath, [, namespaces ])</td> |
| <td style="text-align: left">Returns the xml fragment of the element or elements matched by the XPath expression. The optional namespace value that specifies a default mapping or namespace mapping for prefixes, which is used when evaluating the XPath expression</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">o</td> |
| <td style="text-align: left">EXISTSNODE(xml, xpath, [, namespaces ])</td> |
| <td style="text-align: left">Determines whether traversal of a XML document using a specified xpath results in any nodes. Returns 0 if no nodes remain after applying the XPath traversal on the document fragment of the element or elements matched by the XPath expression. Returns 1 if any nodes remain. The optional namespace value that specifies a default mapping or namespace mapping for prefixes, which is used when evaluating the XPath expression.</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">m</td> |
| <td style="text-align: left">EXTRACTVALUE(xml, xpathExpr))</td> |
| <td style="text-align: left">Returns the text of the first text node which is a child of the element or elements matched by the XPath expression.</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">o</td> |
| <td style="text-align: left">GREATEST(expr [, expr ]*)</td> |
| <td style="text-align: left">Returns the greatest of the expressions</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">m</td> |
| <td style="text-align: left">JSON_TYPE(jsonValue)</td> |
| <td style="text-align: left">Returns a string value indicating the type of a <em>jsonValue</em></td> |
| </tr> |
| <tr> |
| <td style="text-align: left">m</td> |
| <td style="text-align: left">JSON_DEPTH(jsonValue)</td> |
| <td style="text-align: left">Returns an integer value indicating the depth of a <em>jsonValue</em></td> |
| </tr> |
| <tr> |
| <td style="text-align: left">m</td> |
| <td style="text-align: left">JSON_PRETTY(jsonValue)</td> |
| <td style="text-align: left">Returns a pretty-printing of <em>jsonValue</em></td> |
| </tr> |
| <tr> |
| <td style="text-align: left">m</td> |
| <td style="text-align: left">JSON_LENGTH(jsonValue [, path ])</td> |
| <td style="text-align: left">Returns a integer indicating the length of <em>jsonValue</em></td> |
| </tr> |
| <tr> |
| <td style="text-align: left">m</td> |
| <td style="text-align: left">JSON_KEYS(jsonValue [, path ])</td> |
| <td style="text-align: left">Returns a string indicating the keys of a JSON <em>jsonValue</em></td> |
| </tr> |
| <tr> |
| <td style="text-align: left">m</td> |
| <td style="text-align: left">JSON_REMOVE(jsonValue, path[, path])</td> |
| <td style="text-align: left">Removes data from <em>jsonValue</em> using a series of <em>path</em> expressions and returns the result</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">m</td> |
| <td style="text-align: left">JSON_STORAGE_SIZE(jsonValue)</td> |
| <td style="text-align: left">Returns the number of bytes used to store the binary representation of a <em>jsonValue</em></td> |
| </tr> |
| <tr> |
| <td style="text-align: left">o</td> |
| <td style="text-align: left">LEAST(expr [, expr ]* )</td> |
| <td style="text-align: left">Returns the least of the expressions</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">m p</td> |
| <td style="text-align: left">LEFT(string, length)</td> |
| <td style="text-align: left">Returns the leftmost <em>length</em> characters from the <em>string</em></td> |
| </tr> |
| <tr> |
| <td style="text-align: left">m</td> |
| <td style="text-align: left">TO_BASE64(string)</td> |
| <td style="text-align: left">Converts the <em>string</em> to base-64 encoded form and returns a encoded string</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">m</td> |
| <td style="text-align: left">FROM_BASE64(string)</td> |
| <td style="text-align: left">Returns the decoded result of a base-64 <em>string</em> as a string</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">o</td> |
| <td style="text-align: left">LTRIM(string)</td> |
| <td style="text-align: left">Returns <em>string</em> with all blanks removed from the start</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">m p</td> |
| <td style="text-align: left">MD5(string)</td> |
| <td style="text-align: left">Calculates an MD5 128-bit checksum of <em>string</em> and returns it as a hex string</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">m</td> |
| <td style="text-align: left">MONTHNAME(date)</td> |
| <td style="text-align: left">Returns the name, in the connection’s locale, of the month in <em>datetime</em>; for example, it returns ‘二月’ for both DATE ‘2020-02-10’ and TIMESTAMP ‘2020-02-10 10:10:10’</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">o</td> |
| <td style="text-align: left">NVL(value1, value2)</td> |
| <td style="text-align: left">Returns <em>value1</em> if <em>value1</em> is not null, otherwise <em>value2</em></td> |
| </tr> |
| <tr> |
| <td style="text-align: left">m o</td> |
| <td style="text-align: left">REGEXP_REPLACE(string, regexp, rep, [, pos [, occurrence [, matchType]]])</td> |
| <td style="text-align: left">Replaces all substrings of <em>string</em> that match <em>regexp</em> with <em>rep</em> at the starting <em>pos</em> in expr (if omitted, the default is 1), <em>occurrence</em> means which occurrence of a match to search for (if omitted, the default is 1), <em>matchType</em> specifies how to perform matching</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">m p</td> |
| <td style="text-align: left">REPEAT(string, integer)</td> |
| <td style="text-align: left">Returns a string consisting of <em>string</em> repeated of <em>integer</em> times; returns an empty string if <em>integer</em> is less than 1</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">m</td> |
| <td style="text-align: left">REVERSE(string)</td> |
| <td style="text-align: left">Returns <em>string</em> with the order of the characters reversed</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">m p</td> |
| <td style="text-align: left">RIGHT(string, length)</td> |
| <td style="text-align: left">Returns the rightmost <em>length</em> characters from the <em>string</em></td> |
| </tr> |
| <tr> |
| <td style="text-align: left">o</td> |
| <td style="text-align: left">RTRIM(string)</td> |
| <td style="text-align: left">Returns <em>string</em> with all blanks removed from the end</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">m p</td> |
| <td style="text-align: left">SHA1(string)</td> |
| <td style="text-align: left">Calculates a SHA-1 hash value of <em>string</em> and returns it as a hex string</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">m o p</td> |
| <td style="text-align: left">SOUNDEX(string)</td> |
| <td style="text-align: left">Returns the phonetic representation of <em>string</em>; throws if <em>string</em> is encoded with multi-byte encoding such as UTF-8</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">m</td> |
| <td style="text-align: left">SPACE(integer)</td> |
| <td style="text-align: left">Returns a string of <em>integer</em> spaces; returns an empty string if <em>integer</em> is less than 1</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">o</td> |
| <td style="text-align: left">SUBSTR(string, position [, substringLength ])</td> |
| <td style="text-align: left">Returns a portion of <em>string</em>, beginning at character <em>position</em>, <em>substringLength</em> characters long. SUBSTR calculates lengths using characters as defined by the input character set</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">o</td> |
| <td style="text-align: left">TANH(numeric)</td> |
| <td style="text-align: left">Returns the hyperbolic tangent of <em>numeric</em></td> |
| </tr> |
| <tr> |
| <td style="text-align: left">o p</td> |
| <td style="text-align: left">TO_DATE(string, format)</td> |
| <td style="text-align: left">Converts <em>string</em> to a date using the format <em>format</em></td> |
| </tr> |
| <tr> |
| <td style="text-align: left">o p</td> |
| <td style="text-align: left">TO_TIMESTAMP(string, format)</td> |
| <td style="text-align: left">Converts <em>string</em> to a timestamp using the format <em>format</em></td> |
| </tr> |
| <tr> |
| <td style="text-align: left">o p</td> |
| <td style="text-align: left">TRANSLATE(expr, fromString, toString)</td> |
| <td style="text-align: left">Returns <em>expr</em> with all occurrences of each character in <em>fromString</em> replaced by its corresponding character in <em>toString</em>. Characters in <em>expr</em> that are not in <em>fromString</em> are not replaced</td> |
| </tr> |
| <tr> |
| <td style="text-align: left">o</td> |
| <td style="text-align: left">XMLTRANSFORM(xml, xslt)</td> |
| <td style="text-align: left">Returns a string after applying xslt to supplied xml.</td> |
| </tr> |
| </tbody> |
| </table> |
| |
| <p>Note:</p> |
| |
| <ul> |
| <li><code class="highlighter-rouge">JSON_TYPE</code> / <code class="highlighter-rouge">JSON_DEPTH</code> / <code class="highlighter-rouge">JSON_PRETTY</code> / <code class="highlighter-rouge">JSON_STORAGE_SIZE</code> return null if the argument is null</li> |
| <li><code class="highlighter-rouge">JSON_LENGTH</code> / <code class="highlighter-rouge">JSON_KEYS</code> / <code class="highlighter-rouge">JSON_REMOVE</code> return null if the first argument is null</li> |
| <li><code class="highlighter-rouge">JSON_TYPE</code> generally returns an upper-case string flag indicating the type of the JSON input. Currently supported supported type flags are: |
| <ul> |
| <li>INTEGER</li> |
| <li>STRING</li> |
| <li>FLOAT</li> |
| <li>DOUBLE</li> |
| <li>LONG</li> |
| <li>BOOLEAN</li> |
| <li>DATE</li> |
| <li>OBJECT</li> |
| <li>ARRAY</li> |
| <li>NULL</li> |
| </ul> |
| </li> |
| <li><code class="highlighter-rouge">JSON_DEPTH</code> defines a JSON value’s depth as follows: |
| <ul> |
| <li>An empty array, empty object, or scalar value has depth 1;</li> |
| <li>A non-empty array containing only elements of depth 1 or non-empty object containing only member values of depth 1 has depth 2;</li> |
| <li>Otherwise, a JSON document has depth greater than 2.</li> |
| </ul> |
| </li> |
| <li><code class="highlighter-rouge">JSON_LENGTH</code> defines a JSON value’s length as follows: |
| <ul> |
| <li>A scalar value has length 1;</li> |
| <li>The length of array or object is the number of elements is contains.</li> |
| </ul> |
| </li> |
| </ul> |
| |
| <p>Usage Examples:</p> |
| |
| <h5 id="json_type-example">JSON_TYPE example</h5> |
| |
| <p>SQL</p> |
| |
| <pre><code class="language-SQL">SELECT JSON_TYPE(v) AS c1, |
| JSON_TYPE(JSON_VALUE(v, 'lax $.b' ERROR ON ERROR)) AS c2, |
| JSON_TYPE(JSON_VALUE(v, 'strict $.a[0]' ERROR ON ERROR)) AS c3, |
| JSON_TYPE(JSON_VALUE(v, 'strict $.a[1]' ERROR ON ERROR)) AS c4 |
| FROM (VALUES ('{"a": [10, true],"b": "[10, true]"}')) AS t(v) |
| LIMIT 10; |
| </code></pre> |
| |
| <p>Result</p> |
| |
| <table> |
| <thead> |
| <tr> |
| <th>c1</th> |
| <th>c2</th> |
| <th>c3</th> |
| <th>c4</th> |
| </tr> |
| </thead> |
| <tbody> |
| <tr> |
| <td>OBJECT</td> |
| <td>ARRAY</td> |
| <td>INTEGER</td> |
| <td>BOOLEAN</td> |
| </tr> |
| </tbody> |
| </table> |
| |
| <h5 id="json_depth-example">JSON_DEPTH example</h5> |
| |
| <p>SQL</p> |
| |
| <pre><code class="language-SQL">SELECT JSON_DEPTH(v) AS c1, |
| JSON_DEPTH(JSON_VALUE(v, 'lax $.b' ERROR ON ERROR)) AS c2, |
| JSON_DEPTH(JSON_VALUE(v, 'strict $.a[0]' ERROR ON ERROR)) AS c3, |
| JSON_DEPTH(JSON_VALUE(v, 'strict $.a[1]' ERROR ON ERROR)) AS c4 |
| FROM (VALUES ('{"a": [10, true],"b": "[10, true]"}')) AS t(v) |
| LIMIT 10; |
| </code></pre> |
| |
| <p>Result</p> |
| |
| <table> |
| <thead> |
| <tr> |
| <th>c1</th> |
| <th>c2</th> |
| <th>c3</th> |
| <th>c4</th> |
| </tr> |
| </thead> |
| <tbody> |
| <tr> |
| <td>3</td> |
| <td>2</td> |
| <td>1</td> |
| <td>1</td> |
| </tr> |
| </tbody> |
| </table> |
| |
| <h5 id="json_length-example">JSON_LENGTH example</h5> |
| |
| <p>SQL</p> |
| |
| <pre><code class="language-SQL">SELECT JSON_LENGTH(v) AS c1, |
| JSON_LENGTH(v, 'lax $.a') AS c2, |
| JSON_LENGTH(v, 'strict $.a[0]') AS c3, |
| JSON_LENGTH(v, 'strict $.a[1]') AS c4 |
| FROM (VALUES ('{"a": [10, true]}')) AS t(v) |
| LIMIT 10; |
| </code></pre> |
| |
| <p>Result</p> |
| |
| <table> |
| <thead> |
| <tr> |
| <th>c1</th> |
| <th>c2</th> |
| <th>c3</th> |
| <th>c4</th> |
| </tr> |
| </thead> |
| <tbody> |
| <tr> |
| <td>1</td> |
| <td>2</td> |
| <td>1</td> |
| <td>1</td> |
| </tr> |
| </tbody> |
| </table> |
| |
| <h5 id="json_keys-example">JSON_KEYS example</h5> |
| |
| <p>SQL</p> |
| |
| <pre><code class="language-SQL">SELECT JSON_KEYS(v) AS c1, |
| JSON_KEYS(v, 'lax $.a') AS c2, |
| JSON_KEYS(v, 'lax $.b') AS c2, |
| JSON_KEYS(v, 'strict $.a[0]') AS c3, |
| JSON_KEYS(v, 'strict $.a[1]') AS c4 |
| FROM (VALUES ('{"a": [10, true],"b": {"c": 30}}')) AS t(v) |
| LIMIT 10; |
| </code></pre> |
| |
| <p>Result</p> |
| |
| <table> |
| <thead> |
| <tr> |
| <th>c1</th> |
| <th>c2</th> |
| <th>c3</th> |
| <th>c4</th> |
| <th>c5</th> |
| </tr> |
| </thead> |
| <tbody> |
| <tr> |
| <td>[“a”, “b”]</td> |
| <td>NULL</td> |
| <td>[“c”]</td> |
| <td>NULL</td> |
| <td>NULL</td> |
| </tr> |
| </tbody> |
| </table> |
| |
| <h5 id="json_remove-example">JSON_REMOVE example</h5> |
| |
| <p>SQL</p> |
| |
| <pre><code class="language-SQL">SELECT JSON_REMOVE(v, '$[1]') AS c1 |
| FROM (VALUES ('["a", ["b", "c"], "d"]')) AS t(v) |
| LIMIT 10; |
| </code></pre> |
| |
| <p>Result</p> |
| |
| <table> |
| <thead> |
| <tr> |
| <th>c1</th> |
| </tr> |
| </thead> |
| <tbody> |
| <tr> |
| <td>[“a”, “d”]</td> |
| </tr> |
| </tbody> |
| </table> |
| |
| <h5 id="json_storage_size-example">JSON_STORAGE_SIZE example</h5> |
| |
| <p>SQL</p> |
| |
| <pre><code class="language-SQL">SELECT |
| JSON_STORAGE_SIZE('[100, \"sakila\", [1, 3, 5], 425.05]') AS c1, |
| JSON_STORAGE_SIZE('{\"a\": 10, \"b\": \"a\", \"c\": \"[1, 3, 5, 7]\"}') AS c2, |
| JSON_STORAGE_SIZE('{\"a\": 10, \"b\": \"xyz\", \"c\": \"[1, 3, 5, 7]\"}') AS c3, |
| JSON_STORAGE_SIZE('[100, \"json\", [[10, 20, 30], 3, 5], 425.05]') AS c4 |
| limit 10; |
| </code></pre> |
| |
| <p>Result</p> |
| |
| <table> |
| <thead> |
| <tr> |
| <th>c1</th> |
| <th>c2</th> |
| <th>c3</th> |
| <th>c4</th> |
| </tr> |
| </thead> |
| <tbody> |
| <tr> |
| <td>29</td> |
| <td>35</td> |
| <td>37</td> |
| <td>36</td> |
| </tr> |
| </tbody> |
| </table> |
| |
| <h4 id="decode-example">DECODE example</h4> |
| |
| <p>SQL</p> |
| |
| <pre><code class="language-SQL">SELECT DECODE(f1, 1, 'aa', 2, 'bb', 3, 'cc', 4, 'dd', 'ee') as c1, |
| DECODE(f2, 1, 'aa', 2, 'bb', 3, 'cc', 4, 'dd', 'ee') as c2, |
| DECODE(f3, 1, 'aa', 2, 'bb', 3, 'cc', 4, 'dd', 'ee') as c3, |
| DECODE(f4, 1, 'aa', 2, 'bb', 3, 'cc', 4, 'dd', 'ee') as c4, |
| DECODE(f5, 1, 'aa', 2, 'bb', 3, 'cc', 4, 'dd', 'ee') as c5 |
| FROM (VALUES (1, 2, 3, 4, 5)) AS t(f1, f2, f3, f4, f5); |
| |
| </code></pre> |
| <p>Result</p> |
| |
| <table> |
| <thead> |
| <tr> |
| <th>c1</th> |
| <th>c2</th> |
| <th>c3</th> |
| <th>c4</th> |
| <th>c5</th> |
| </tr> |
| </thead> |
| <tbody> |
| <tr> |
| <td>aa</td> |
| <td>bb</td> |
| <td>cc</td> |
| <td>dd</td> |
| <td>ee</td> |
| </tr> |
| </tbody> |
| </table> |
| |
| <h4 id="translate-example">TRANSLATE example</h4> |
| |
| <p>SQL</p> |
| |
| <pre><code class="language-SQL">SELECT TRANSLATE('Aa*Bb*Cc''D*d', ' */''%', '_') as c1, |
| TRANSLATE('Aa/Bb/Cc''D/d', ' */''%', '_') as c2, |
| TRANSLATE('Aa Bb Cc''D d', ' */''%', '_') as c3, |
| TRANSLATE('Aa%Bb%Cc''D%d', ' */''%', '_') as c4 |
| FROM (VALUES (true)) AS t(f0); |
| </code></pre> |
| |
| <p>Result</p> |
| |
| <table> |
| <thead> |
| <tr> |
| <th>c1</th> |
| <th>c2</th> |
| <th>c3</th> |
| <th>c4</th> |
| </tr> |
| </thead> |
| <tbody> |
| <tr> |
| <td>Aa_Bb_CcD_d</td> |
| <td>Aa_Bb_CcD_d</td> |
| <td>Aa_Bb_CcD_d</td> |
| <td>Aa_Bb_CcD_d</td> |
| </tr> |
| </tbody> |
| </table> |
| |
| <p>Not implemented:</p> |
| |
| <ul> |
| <li>JSON_INSERT</li> |
| <li>JSON_SET</li> |
| <li>JSON_REPLACE</li> |
| </ul> |
| |
| <h2 id="user-defined-functions">User-defined functions</h2> |
| |
| <p>Calcite is extensible. You can define each kind of function using user code. |
| For each kind of function there are often several ways to define a function, |
| varying from convenient to efficient.</p> |
| |
| <p>To implement a <em>scalar function</em>, there are 3 options:</p> |
| |
| <ul> |
| <li>Create a class with a public static <code class="highlighter-rouge">eval</code> method, |
| and register the class;</li> |
| <li>Create a class with a public non-static <code class="highlighter-rouge">eval</code> method, |
| and a public constructor with no arguments, |
| and register the class;</li> |
| <li>Create a class with one or more public static methods, |
| and register each class/method combination.</li> |
| </ul> |
| |
| <p>To implement an <em>aggregate function</em>, there are 2 options:</p> |
| |
| <ul> |
| <li>Create a class with public static <code class="highlighter-rouge">init</code>, <code class="highlighter-rouge">add</code> and <code class="highlighter-rouge">result</code> methods, |
| and register the class;</li> |
| <li>Create a class with public non-static <code class="highlighter-rouge">init</code>, <code class="highlighter-rouge">add</code> and <code class="highlighter-rouge">result</code> methods, |
| and a public constructor with no arguments, |
| and register the class.</li> |
| </ul> |
| |
| <p>Optionally, add a public <code class="highlighter-rouge">merge</code> method to the class; this allows Calcite to |
| generate code that merges sub-totals.</p> |
| |
| <p>Optionally, make your class implement the |
| <a href="/javadocAggregate/org/apache/calcite/sql/SqlSplittableAggFunction.html">SqlSplittableAggFunction</a> |
| interface; this allows Calcite to decompose the function across several stages |
| of aggregation, roll up from summary tables, and push it through joins.</p> |
| |
| <p>To implement a <em>table function</em>, there are 3 options:</p> |
| |
| <ul> |
| <li>Create a class with a static <code class="highlighter-rouge">eval</code> method that returns |
| <a href="/javadocAggregate/org/apache/calcite/schema/ScannableTable.html">ScannableTable</a> |
| or |
| <a href="/javadocAggregate/org/apache/calcite/schema/QueryableTable.html">QueryableTable</a>, |
| and register the class;</li> |
| <li>Create a class with a non-static <code class="highlighter-rouge">eval</code> method that returns |
| <a href="/javadocAggregate/org/apache/calcite/schema/ScannableTable.html">ScannableTable</a> |
| or |
| <a href="/javadocAggregate/org/apache/calcite/schema/QueryableTable.html">QueryableTable</a>, |
| and register the class;</li> |
| <li>Create a class with one or more public static methods that return |
| <a href="/javadocAggregate/org/apache/calcite/schema/ScannableTable.html">ScannableTable</a> |
| or |
| <a href="/javadocAggregate/org/apache/calcite/schema/QueryableTable.html">QueryableTable</a>, |
| and register each class/method combination.</li> |
| </ul> |
| |
| <p>To implement a <em>table macro</em>, there are 3 options:</p> |
| |
| <ul> |
| <li>Create a class with a static <code class="highlighter-rouge">eval</code> method that returns |
| <a href="/javadocAggregate/org/apache/calcite/schema/TranslatableTable.html">TranslatableTable</a>, |
| and register the class;</li> |
| <li>Create a class with a non-static <code class="highlighter-rouge">eval</code> method that returns |
| <a href="/javadocAggregate/org/apache/calcite/schema/TranslatableTable.html">TranslatableTable</a>, |
| and register the class;</li> |
| <li>Create a class with one or more public static methods that return |
| <a href="/javadocAggregate/org/apache/calcite/schema/TranslatableTable.html">TranslatableTable</a>, |
| and register each class/method combination.</li> |
| </ul> |
| |
| <p>Calcite deduces the parameter types and result type of a function from the |
| parameter and return types of the Java method that implements it. Further, you |
| can specify the name and optionality of each parameter using the |
| <a href="/javadocAggregate/org/apache/calcite/linq4j/function/Parameter.html">Parameter</a> |
| annotation.</p> |
| |
| <h3 id="calling-functions-with-named-and-optional-parameters">Calling functions with named and optional parameters</h3> |
| |
| <p>Usually when you call a function, you need to specify all of its parameters, |
| in order. But that can be a problem if a function has a lot of parameters, |
| and especially if you want to add more parameters over time.</p> |
| |
| <p>To solve this problem, the SQL standard allows you to pass parameters by name, |
| and to define parameters which are optional (that is, have a default value |
| that is used if they are not specified).</p> |
| |
| <p>Suppose you have a function <code class="highlighter-rouge">f</code>, declared as in the following pseudo syntax:</p> |
| |
| <figure class="highlight"><pre><code class="language-sql" data-lang="sql"><span class="k">FUNCTION</span> <span class="n">f</span><span class="p">(</span> |
| <span class="n">INTEGER</span> <span class="n">a</span><span class="p">,</span> |
| <span class="n">INTEGER</span> <span class="n">b</span> <span class="k">DEFAULT</span> <span class="k">NULL</span><span class="p">,</span> |
| <span class="n">INTEGER</span> <span class="k">c</span><span class="p">,</span> |
| <span class="n">INTEGER</span> <span class="n">d</span> <span class="k">DEFAULT</span> <span class="k">NULL</span><span class="p">,</span> |
| <span class="n">INTEGER</span> <span class="n">e</span> <span class="k">DEFAULT</span> <span class="k">NULL</span><span class="p">)</span> <span class="k">RETURNS</span> <span class="n">INTEGER</span></code></pre></figure> |
| |
| <p>All of the function’s parameters have names, and parameters <code class="highlighter-rouge">b</code>, <code class="highlighter-rouge">d</code> and <code class="highlighter-rouge">e</code> |
| have a default value of <code class="highlighter-rouge">NULL</code> and are therefore optional. |
| (In Calcite, <code class="highlighter-rouge">NULL</code> is the only allowable default value for optional parameters; |
| this may change |
| <a href="https://issues.apache.org/jira/browse/CALCITE-947">in future</a>.)</p> |
| |
| <p>When calling a function with optional parameters, |
| you can omit optional arguments at the end of the list, or use the <code class="highlighter-rouge">DEFAULT</code> |
| keyword for any optional arguments. |
| Here are some examples:</p> |
| |
| <ul> |
| <li><code class="highlighter-rouge">f(1, 2, 3, 4, 5)</code> provides a value to each parameter, in order;</li> |
| <li><code class="highlighter-rouge">f(1, 2, 3, 4)</code> omits <code class="highlighter-rouge">e</code>, which gets its default value, <code class="highlighter-rouge">NULL</code>;</li> |
| <li><code class="highlighter-rouge">f(1, DEFAULT, 3)</code> omits <code class="highlighter-rouge">d</code> and <code class="highlighter-rouge">e</code>, |
| and specifies to use the default value of <code class="highlighter-rouge">b</code>;</li> |
| <li><code class="highlighter-rouge">f(1, DEFAULT, 3, DEFAULT, DEFAULT)</code> has the same effect as the previous |
| example;</li> |
| <li><code class="highlighter-rouge">f(1, 2)</code> is not legal, because <code class="highlighter-rouge">c</code> is not optional;</li> |
| <li><code class="highlighter-rouge">f(1, 2, DEFAULT, 4)</code> is not legal, because <code class="highlighter-rouge">c</code> is not optional.</li> |
| </ul> |
| |
| <p>You can specify arguments by name using the <code class="highlighter-rouge">=></code> syntax. |
| If one argument is named, they all must be. |
| Arguments may be in any other, but must not specify any argument more than once, |
| and you need to provide a value for every parameter which is not optional. |
| Here are some examples:</p> |
| |
| <ul> |
| <li><code class="highlighter-rouge">f(c => 3, d => 1, a => 0)</code> is equivalent to <code class="highlighter-rouge">f(0, NULL, 3, 1, NULL)</code>;</li> |
| <li><code class="highlighter-rouge">f(c => 3, d => 1)</code> is not legal, because you have not specified a value for |
| <code class="highlighter-rouge">a</code> and <code class="highlighter-rouge">a</code> is not optional.</li> |
| </ul> |
| |
| <h3 id="sql-hints">SQL Hints</h3> |
| |
| <p>A hint is an instruction to the optimizer. When writing SQL, you may know information about |
| the data unknown to the optimizer. Hints enable you to make decisions normally made by the optimizer.</p> |
| |
| <ul> |
| <li>Planner enforcers: there’s no perfect planner, so it makes sense to implement hints to |
| allow user better control the execution. For instance: “never merge this subquery with others” (<code class="highlighter-rouge">/*+ no_merge */</code>); |
| “treat those tables as leading ones” (<code class="highlighter-rouge">/*+ leading */</code>) to affect join ordering, etc;</li> |
| <li>Append meta data/statistics: some statistics like “table index for scan” or “skew info of some shuffle keys” |
| are somehow dynamic for the query, it would be very convenient to config them with hints because |
| our planning metadata from the planner is very often not very accurate;</li> |
| <li>Operator resource constraints: for many cases, we would give a default resource configuration |
| for the execution operators, |
| i.e. min parallelism, memory (resource consuming UDF), special resource requirement (GPU or SSD disk) … |
| It would be very flexible to profile the resource with hints per query (not the Job).</li> |
| </ul> |
| |
| <h4 id="syntax">Syntax</h4> |
| |
| <p>Calcite supports basically two kinds of hints:</p> |
| |
| <ul> |
| <li>Query Hint: right after the <code class="highlighter-rouge">SELECT</code> keyword;</li> |
| <li>Table Hint: right after the referenced table name.</li> |
| </ul> |
| |
| <figure class="highlight"><pre><code class="language-sql" data-lang="sql"><span class="n">query</span> <span class="p">:</span> |
| <span class="k">SELECT</span> <span class="cm">/*+ hints */</span> |
| <span class="p">...</span> |
| <span class="k">from</span> |
| <span class="n">tableName</span> <span class="cm">/*+ hints */</span> |
| <span class="k">JOIN</span> |
| <span class="n">tableName</span> <span class="cm">/*+ hints */</span> |
| <span class="p">...</span> |
| |
| <span class="n">hints</span> <span class="p">:</span> |
| <span class="n">hintItem</span><span class="p">[,</span> <span class="n">hintItem</span> <span class="p">]</span><span class="o">*</span> |
| |
| <span class="n">hintItem</span> <span class="p">:</span> |
| <span class="n">hintName</span> |
| <span class="o">|</span> <span class="n">hintName</span><span class="p">(</span><span class="n">optionKey</span><span class="o">=</span><span class="n">optionVal</span><span class="p">[,</span> <span class="n">optionKey</span><span class="o">=</span><span class="n">optionVal</span> <span class="p">]</span><span class="o">*</span><span class="p">)</span> |
| <span class="o">|</span> <span class="n">hintName</span><span class="p">(</span><span class="n">hintOption</span> <span class="p">[,</span> <span class="n">hintOption</span> <span class="p">]</span><span class="o">*</span><span class="p">)</span> |
| |
| <span class="n">optionKey</span> <span class="p">:</span> |
| <span class="n">simpleIdentifier</span> |
| <span class="o">|</span> <span class="n">stringLiteral</span> |
| |
| <span class="n">optionVal</span> <span class="p">:</span> |
| <span class="n">stringLiteral</span> |
| |
| <span class="n">hintOption</span> <span class="p">:</span> |
| <span class="n">simpleIdentifier</span> |
| <span class="o">|</span> <span class="n">numericLiteral</span> |
| <span class="o">|</span> <span class="n">stringLiteral</span></code></pre></figure> |
| |
| <p>It is experimental in Calcite, and yet not fully implemented, what we have implemented are:</p> |
| |
| <ul> |
| <li>The parser support for the syntax above;</li> |
| <li><code class="highlighter-rouge">RelHint</code> to represent a hint item;</li> |
| <li>Mechanism to propagate the hints, during sql-to-rel conversion and planner planning.</li> |
| </ul> |
| |
| <p>We do not add any builtin hint items yet, would introduce more if we think the hints is stable enough.</p> |
| |
| <h3 id="match_recognize">MATCH_RECOGNIZE</h3> |
| |
| <p><code class="highlighter-rouge">MATCH_RECOGNIZE</code> is a SQL extension for recognizing sequences of |
| events in complex event processing (CEP).</p> |
| |
| <p>It is experimental in Calcite, and yet not fully implemented.</p> |
| |
| <h4 id="syntax-1">Syntax</h4> |
| |
| <figure class="highlight"><pre><code class="language-sql" data-lang="sql"><span class="n">matchRecognize</span><span class="p">:</span> |
| <span class="n">MATCH_RECOGNIZE</span> <span class="s1">'('</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="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">MEASURES</span> <span class="n">measureColumn</span> <span class="p">[,</span> <span class="n">measureColumn</span> <span class="p">]</span><span class="o">*</span> <span class="p">]</span> |
| <span class="p">[</span> <span class="n">ONE</span> <span class="k">ROW</span> <span class="n">PER</span> <span class="k">MATCH</span> <span class="o">|</span> <span class="k">ALL</span> <span class="k">ROWS</span> <span class="n">PER</span> <span class="k">MATCH</span> <span class="p">]</span> |
| <span class="p">[</span> <span class="k">AFTER</span> <span class="k">MATCH</span> |
| <span class="p">(</span> <span class="n">SKIP</span> <span class="k">TO</span> <span class="k">NEXT</span> <span class="k">ROW</span> |
| <span class="o">|</span> <span class="n">SKIP</span> <span class="n">PAST</span> <span class="k">LAST</span> <span class="k">ROW</span> |
| <span class="o">|</span> <span class="n">SKIP</span> <span class="k">TO</span> <span class="k">FIRST</span> <span class="k">variable</span> |
| <span class="o">|</span> <span class="n">SKIP</span> <span class="k">TO</span> <span class="k">LAST</span> <span class="k">variable</span> |
| <span class="o">|</span> <span class="n">SKIP</span> <span class="k">TO</span> <span class="k">variable</span> <span class="p">)</span> |
| <span class="p">]</span> |
| <span class="n">PATTERN</span> <span class="s1">'('</span> <span class="n">pattern</span> <span class="s1">')'</span> |
| <span class="p">[</span> <span class="n">WITHIN</span> <span class="n">intervalLiteral</span> <span class="p">]</span> |
| <span class="p">[</span> <span class="n">SUBSET</span> <span class="n">subsetItem</span> <span class="p">[,</span> <span class="n">subsetItem</span> <span class="p">]</span><span class="o">*</span> <span class="p">]</span> |
| <span class="n">DEFINE</span> <span class="k">variable</span> <span class="k">AS</span> <span class="n">condition</span> <span class="p">[,</span> <span class="k">variable</span> <span class="k">AS</span> <span class="n">condition</span> <span class="p">]</span><span class="o">*</span> |
| <span class="s1">')'</span> |
| |
| <span class="n">subsetItem</span><span class="p">:</span> |
| <span class="k">variable</span> <span class="o">=</span> <span class="s1">'('</span> <span class="k">variable</span> <span class="p">[,</span> <span class="k">variable</span> <span class="p">]</span><span class="o">*</span> <span class="s1">')'</span> |
| |
| <span class="n">measureColumn</span><span class="p">:</span> |
| <span class="n">expression</span> <span class="k">AS</span> <span class="k">alias</span> |
| |
| <span class="n">pattern</span><span class="p">:</span> |
| <span class="n">patternTerm</span> <span class="p">[</span> <span class="s1">'|'</span> <span class="n">patternTerm</span> <span class="p">]</span><span class="o">*</span> |
| |
| <span class="n">patternTerm</span><span class="p">:</span> |
| <span class="n">patternFactor</span> <span class="p">[</span> <span class="n">patternFactor</span> <span class="p">]</span><span class="o">*</span> |
| |
| <span class="n">patternFactor</span><span class="p">:</span> |
| <span class="n">patternPrimary</span> <span class="p">[</span> <span class="n">patternQuantifier</span> <span class="p">]</span> |
| |
| <span class="n">patternPrimary</span><span class="p">:</span> |
| <span class="k">variable</span> |
| <span class="o">|</span> <span class="s1">'$'</span> |
| <span class="o">|</span> <span class="s1">'^'</span> |
| <span class="o">|</span> <span class="s1">'('</span> <span class="p">[</span> <span class="n">pattern</span> <span class="p">]</span> <span class="s1">')'</span> |
| <span class="o">|</span> <span class="s1">'{-'</span> <span class="n">pattern</span> <span class="s1">'-}'</span> |
| <span class="o">|</span> <span class="n">PERMUTE</span> <span class="s1">'('</span> <span class="n">pattern</span> <span class="p">[,</span> <span class="n">pattern</span> <span class="p">]</span><span class="o">*</span> <span class="s1">')'</span> |
| |
| <span class="n">patternQuantifier</span><span class="p">:</span> |
| <span class="s1">'*'</span> |
| <span class="o">|</span> <span class="s1">'*?'</span> |
| <span class="o">|</span> <span class="s1">'+'</span> |
| <span class="o">|</span> <span class="s1">'+?'</span> |
| <span class="o">|</span> <span class="s1">'?'</span> |
| <span class="o">|</span> <span class="s1">'??'</span> |
| <span class="o">|</span> <span class="s1">'{'</span> <span class="err">{</span> <span class="p">[</span> <span class="n">minRepeat</span> <span class="p">],</span> <span class="p">[</span> <span class="n">maxRepeat</span> <span class="p">]</span> <span class="err">}</span> <span class="s1">'}'</span> <span class="p">[</span><span class="s1">'?'</span><span class="p">]</span> |
| <span class="o">|</span> <span class="s1">'{'</span> <span class="n">repeat</span> <span class="s1">'}'</span> |
| |
| <span class="n">intervalLiteral</span><span class="p">:</span> |
| <span class="n">INTERVAL</span> <span class="s1">'string'</span> <span class="n">timeUnit</span> <span class="p">[</span> <span class="k">TO</span> <span class="n">timeUnit</span> <span class="p">]</span></code></pre></figure> |
| |
| <p>In <em>patternQuantifier</em>, <em>repeat</em> is a positive integer, |
| and <em>minRepeat</em> and <em>maxRepeat</em> are non-negative integers.</p> |
| |
| <h3 id="ddl-extensions">DDL Extensions</h3> |
| |
| <p>DDL extensions are only available in the calcite-server module. |
| To enable, include <code class="highlighter-rouge">calcite-server.jar</code> in your class path, and add |
| <code class="highlighter-rouge">parserFactory=org.apache.calcite.sql.parser.ddl.SqlDdlParserImpl#FACTORY</code> |
| to the JDBC connect string (see connect string property |
| <a href="/javadocAggregate/org/apache/calcite/config/CalciteConnectionProperty.html#PARSER_FACTORY">parserFactory</a>).</p> |
| |
| <figure class="highlight"><pre><code class="language-sql" data-lang="sql"><span class="n">ddlStatement</span><span class="p">:</span> |
| <span class="n">createSchemaStatement</span> |
| <span class="o">|</span> <span class="n">createForeignSchemaStatement</span> |
| <span class="o">|</span> <span class="n">createTableStatement</span> |
| <span class="o">|</span> <span class="n">createViewStatement</span> |
| <span class="o">|</span> <span class="n">createMaterializedViewStatement</span> |
| <span class="o">|</span> <span class="n">createTypeStatement</span> |
| <span class="o">|</span> <span class="n">createFunctionStatement</span> |
| <span class="o">|</span> <span class="n">dropSchemaStatement</span> |
| <span class="o">|</span> <span class="n">dropForeignSchemaStatement</span> |
| <span class="o">|</span> <span class="n">dropTableStatement</span> |
| <span class="o">|</span> <span class="n">dropViewStatement</span> |
| <span class="o">|</span> <span class="n">dropMaterializedViewStatement</span> |
| <span class="o">|</span> <span class="n">dropTypeStatement</span> |
| <span class="o">|</span> <span class="n">dropFunctionStatement</span> |
| |
| <span class="n">createSchemaStatement</span><span class="p">:</span> |
| <span class="k">CREATE</span> <span class="p">[</span> <span class="k">OR</span> <span class="k">REPLACE</span> <span class="p">]</span> <span class="k">SCHEMA</span> <span class="p">[</span> <span class="n">IF</span> <span class="k">NOT</span> <span class="k">EXISTS</span> <span class="p">]</span> <span class="n">name</span> |
| |
| <span class="n">createForeignSchemaStatement</span><span class="p">:</span> |
| <span class="k">CREATE</span> <span class="p">[</span> <span class="k">OR</span> <span class="k">REPLACE</span> <span class="p">]</span> <span class="k">FOREIGN</span> <span class="k">SCHEMA</span> <span class="p">[</span> <span class="n">IF</span> <span class="k">NOT</span> <span class="k">EXISTS</span> <span class="p">]</span> <span class="n">name</span> |
| <span class="p">(</span> |
| <span class="k">TYPE</span> <span class="s1">'type'</span> |
| <span class="o">|</span> <span class="n">LIBRARY</span> <span class="s1">'com.example.calcite.ExampleSchemaFactory'</span> |
| <span class="p">)</span> |
| <span class="p">[</span> <span class="k">OPTIONS</span> <span class="s1">'('</span> <span class="k">option</span> <span class="p">[,</span> <span class="k">option</span> <span class="p">]</span><span class="o">*</span> <span class="s1">')'</span> <span class="p">]</span> |
| |
| <span class="k">option</span><span class="p">:</span> |
| <span class="n">name</span> <span class="n">literal</span> |
| |
| <span class="n">createTableStatement</span><span class="p">:</span> |
| <span class="k">CREATE</span> <span class="k">TABLE</span> <span class="p">[</span> <span class="n">IF</span> <span class="k">NOT</span> <span class="k">EXISTS</span> <span class="p">]</span> <span class="n">name</span> |
| <span class="p">[</span> <span class="s1">'('</span> <span class="n">tableElement</span> <span class="p">[,</span> <span class="n">tableElement</span> <span class="p">]</span><span class="o">*</span> <span class="s1">')'</span> <span class="p">]</span> |
| <span class="p">[</span> <span class="k">AS</span> <span class="n">query</span> <span class="p">]</span> |
| |
| <span class="n">createTypeStatement</span><span class="p">:</span> |
| <span class="k">CREATE</span> <span class="p">[</span> <span class="k">OR</span> <span class="k">REPLACE</span> <span class="p">]</span> <span class="k">TYPE</span> <span class="n">name</span> <span class="k">AS</span> |
| <span class="err">{</span> |
| <span class="n">baseType</span> |
| <span class="o">|</span> <span class="s1">'('</span> <span class="n">attributeDef</span> <span class="p">[,</span> <span class="n">attributeDef</span> <span class="p">]</span><span class="o">*</span> <span class="s1">')'</span> |
| <span class="err">}</span> |
| |
| <span class="n">attributeDef</span><span class="p">:</span> |
| <span class="n">attributeName</span> <span class="k">type</span> |
| <span class="p">[</span> <span class="k">COLLATE</span> <span class="k">collation</span> <span class="p">]</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="k">DEFAULT</span> <span class="n">expression</span> <span class="p">]</span> |
| |
| <span class="n">tableElement</span><span class="p">:</span> |
| <span class="n">columnName</span> <span class="k">type</span> <span class="p">[</span> <span class="n">columnGenerator</span> <span class="p">]</span> <span class="p">[</span> <span class="n">columnConstraint</span> <span class="p">]</span> |
| <span class="o">|</span> <span class="n">columnName</span> |
| <span class="o">|</span> <span class="n">tableConstraint</span> |
| |
| <span class="n">columnGenerator</span><span class="p">:</span> |
| <span class="k">DEFAULT</span> <span class="n">expression</span> |
| <span class="o">|</span> <span class="p">[</span> <span class="k">GENERATED</span> <span class="n">ALWAYS</span> <span class="p">]</span> <span class="k">AS</span> <span class="s1">'('</span> <span class="n">expression</span> <span class="s1">')'</span> |
| <span class="err">{</span> <span class="n">VIRTUAL</span> <span class="o">|</span> <span class="n">STORED</span> <span class="err">}</span> |
| |
| <span class="n">columnConstraint</span><span class="p">:</span> |
| <span class="p">[</span> <span class="k">CONSTRAINT</span> <span class="n">name</span> <span class="p">]</span> |
| <span class="p">[</span> <span class="k">NOT</span> <span class="p">]</span> <span class="k">NULL</span> |
| |
| <span class="n">tableConstraint</span><span class="p">:</span> |
| <span class="p">[</span> <span class="k">CONSTRAINT</span> <span class="n">name</span> <span class="p">]</span> |
| <span class="err">{</span> |
| <span class="k">CHECK</span> <span class="s1">'('</span> <span class="n">expression</span> <span class="s1">')'</span> |
| <span class="o">|</span> <span class="k">PRIMARY</span> <span class="k">KEY</span> <span class="s1">'('</span> <span class="n">columnName</span> <span class="p">[,</span> <span class="n">columnName</span> <span class="p">]</span><span class="o">*</span> <span class="s1">')'</span> |
| <span class="o">|</span> <span class="k">UNIQUE</span> <span class="s1">'('</span> <span class="n">columnName</span> <span class="p">[,</span> <span class="n">columnName</span> <span class="p">]</span><span class="o">*</span> <span class="s1">')'</span> |
| <span class="err">}</span> |
| |
| <span class="n">createViewStatement</span><span class="p">:</span> |
| <span class="k">CREATE</span> <span class="p">[</span> <span class="k">OR</span> <span class="k">REPLACE</span> <span class="p">]</span> <span class="k">VIEW</span> <span class="n">name</span> |
| <span class="p">[</span> <span class="s1">'('</span> <span class="n">columnName</span> <span class="p">[,</span> <span class="n">columnName</span> <span class="p">]</span><span class="o">*</span> <span class="s1">')'</span> <span class="p">]</span> |
| <span class="k">AS</span> <span class="n">query</span> |
| |
| <span class="n">createMaterializedViewStatement</span><span class="p">:</span> |
| <span class="k">CREATE</span> <span class="n">MATERIALIZED</span> <span class="k">VIEW</span> <span class="p">[</span> <span class="n">IF</span> <span class="k">NOT</span> <span class="k">EXISTS</span> <span class="p">]</span> <span class="n">name</span> |
| <span class="p">[</span> <span class="s1">'('</span> <span class="n">columnName</span> <span class="p">[,</span> <span class="n">columnName</span> <span class="p">]</span><span class="o">*</span> <span class="s1">')'</span> <span class="p">]</span> |
| <span class="k">AS</span> <span class="n">query</span> |
| |
| <span class="n">createFunctionStatement</span><span class="p">:</span> |
| <span class="k">CREATE</span> <span class="p">[</span> <span class="k">OR</span> <span class="k">REPLACE</span> <span class="p">]</span> <span class="k">FUNCTION</span> <span class="p">[</span> <span class="n">IF</span> <span class="k">NOT</span> <span class="k">EXISTS</span> <span class="p">]</span> <span class="n">name</span> |
| <span class="k">AS</span> <span class="n">classNameLiteral</span> |
| <span class="p">[</span> <span class="k">USING</span> <span class="n">usingFile</span> <span class="p">[,</span> <span class="n">usingFile</span> <span class="p">]</span><span class="o">*</span> <span class="p">]</span> |
| |
| <span class="n">usingFile</span><span class="p">:</span> |
| <span class="p">(</span> <span class="n">JAR</span> <span class="o">|</span> <span class="n">FILE</span> <span class="o">|</span> <span class="n">ARCHIVE</span> <span class="p">)</span> <span class="n">filePathLiteral</span> |
| |
| <span class="n">dropSchemaStatement</span><span class="p">:</span> |
| <span class="k">DROP</span> <span class="k">SCHEMA</span> <span class="p">[</span> <span class="n">IF</span> <span class="k">EXISTS</span> <span class="p">]</span> <span class="n">name</span> |
| |
| <span class="n">dropForeignSchemaStatement</span><span class="p">:</span> |
| <span class="k">DROP</span> <span class="k">FOREIGN</span> <span class="k">SCHEMA</span> <span class="p">[</span> <span class="n">IF</span> <span class="k">EXISTS</span> <span class="p">]</span> <span class="n">name</span> |
| |
| <span class="n">dropTableStatement</span><span class="p">:</span> |
| <span class="k">DROP</span> <span class="k">TABLE</span> <span class="p">[</span> <span class="n">IF</span> <span class="k">EXISTS</span> <span class="p">]</span> <span class="n">name</span> |
| |
| <span class="n">dropViewStatement</span><span class="p">:</span> |
| <span class="k">DROP</span> <span class="k">VIEW</span> <span class="p">[</span> <span class="n">IF</span> <span class="k">EXISTS</span> <span class="p">]</span> <span class="n">name</span> |
| |
| <span class="n">dropMaterializedViewStatement</span><span class="p">:</span> |
| <span class="k">DROP</span> <span class="n">MATERIALIZED</span> <span class="k">VIEW</span> <span class="p">[</span> <span class="n">IF</span> <span class="k">EXISTS</span> <span class="p">]</span> <span class="n">name</span> |
| |
| <span class="n">dropTypeStatement</span><span class="p">:</span> |
| <span class="k">DROP</span> <span class="k">TYPE</span> <span class="p">[</span> <span class="n">IF</span> <span class="k">EXISTS</span> <span class="p">]</span> <span class="n">name</span> |
| |
| <span class="n">dropFunctionStatement</span><span class="p">:</span> |
| <span class="k">DROP</span> <span class="k">FUNCTION</span> <span class="p">[</span> <span class="n">IF</span> <span class="k">EXISTS</span> <span class="p">]</span> <span class="n">name</span></code></pre></figure> |
| |
| <p>In <em>createTableStatement</em>, if you specify <em>AS query</em>, you may omit the list of |
| <em>tableElement</em>s, or you can omit the data type of any <em>tableElement</em>, in which |
| case it just renames the underlying column.</p> |
| |
| <p>In <em>columnGenerator</em>, if you do not specify <code class="highlighter-rouge">VIRTUAL</code> or <code class="highlighter-rouge">STORED</code> for a |
| generated column, <code class="highlighter-rouge">VIRTUAL</code> is the default.</p> |
| |
| <p>In <em>createFunctionStatement</em> and <em>usingFile</em>, <em>classNameLiteral</em> |
| and <em>filePathLiteral</em> are character literals.</p> |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| <div class="section-nav"> |
| <div class="left align-right"> |
| |
| |
| |
| <a href="/docs/avatica_protobuf_reference.html" class="prev">Previous</a> |
| |
| </div> |
| <div class="right align-left"> |
| |
| |
| |
| |
| |
| <a href="/docs/model.html" class="next">Next</a> |
| |
| </div> |
| </div> |
| <div class="clear"></div> |
| |
| |
| </article> |
| </div> |
| |
| <div class="unit one-fifth hide-on-mobiles"> |
| <aside> |
| |
| <h4>Overview</h4> |
| |
| |
| <ul> |
| |
| |
| |
| |
| |
| |
| |
| |
| <li class=""><a href="/docs/index.html">Background</a></li> |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| <li class=""><a href="/docs/tutorial.html">Tutorial</a></li> |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| <li class=""><a href="/docs/algebra.html">Algebra</a></li> |
| |
| |
| </ul> |
| |
| |
| <h4>Advanced</h4> |
| |
| |
| <ul> |
| |
| |
| |
| |
| |
| |
| |
| |
| <li class=""><a href="/docs/adapter.html">Adapters</a></li> |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| <li class=""><a href="/docs/spatial.html">Spatial</a></li> |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| <li class=""><a href="/docs/stream.html">Streaming</a></li> |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| <li class=""><a href="/docs/materialized_views.html">Materialized Views</a></li> |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| <li class=""><a href="/docs/lattice.html">Lattices</a></li> |
| |
| |
| </ul> |
| |
| |
| <h4>Avatica</h4> |
| |
| |
| <ul> |
| |
| |
| |
| |
| |
| |
| |
| |
| <li class=""><a href="/docs/avatica_overview.html">Overview</a></li> |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| <li class=""><a href="/docs/avatica_roadmap.html">Roadmap</a></li> |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| <li class=""><a href="/docs/avatica_json_reference.html">JSON Reference</a></li> |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| <li class=""><a href="/docs/avatica_protobuf_reference.html">Protobuf Reference</a></li> |
| |
| |
| </ul> |
| |
| |
| <h4>Reference</h4> |
| |
| |
| <ul> |
| |
| |
| |
| |
| |
| |
| |
| |
| <li class="current"><a href="/docs/reference.html">SQL language</a></li> |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| <li class=""><a href="/docs/model.html">JSON/YAML models</a></li> |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| <li class=""><a href="/docs/howto.html">HOWTO</a></li> |
| |
| |
| </ul> |
| |
| |
| <h4>Meta</h4> |
| |
| |
| <ul> |
| |
| |
| |
| |
| |
| |
| |
| |
| <li class=""><a href="/docs/history.html">History</a></li> |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| <li class=""><a href="/docs/powered_by.html">Powered by Calcite</a></li> |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| <li class=""><a href="/javadocAggregate">API</a></li> |
| |
| |
| </ul> |
| |
| |
| </aside> |
| </div> |
| |
| |
| <div class="clear"></div> |
| |
| </div> |
| </section> |
| |
| |
| <footer role="contentinfo"> |
| <div id="poweredby"> |
| <a href="http://www.apache.org/"> |
| <span class="sr-only">Apache</span> |
| <img src="/img/feather.png" width="190" height="77" alt="Apache Logo"></a> |
| </div> |
| <div id="copyright"> |
| <p>The contents of this website are Copyright © 2020 |
| <a href="https://www.apache.org/">Apache Software Foundation</a> |
| under the terms of |
| the <a href="https://www.apache.org/licenses/"> |
| Apache License v2</a>. Apache Calcite and its logo are |
| trademarks of the Apache Software Foundation.</p> |
| </div> |
| </footer> |
| |
| <script> |
| var anchorForId = function (id) { |
| var anchor = document.createElement("a"); |
| anchor.className = "header-link"; |
| anchor.href = "#" + id; |
| anchor.innerHTML = "<span class=\"sr-only\">Permalink</span><i class=\"fa fa-link\"></i>"; |
| anchor.title = "Permalink"; |
| return anchor; |
| }; |
| |
| var linkifyAnchors = function (level, containingElement) { |
| var headers = containingElement.getElementsByTagName("h" + level); |
| for (var h = 0; h < headers.length; h++) { |
| var header = headers[h]; |
| |
| if (typeof header.id !== "undefined" && header.id !== "") { |
| header.appendChild(anchorForId(header.id)); |
| } |
| } |
| }; |
| |
| document.onreadystatechange = function () { |
| if (this.readyState === "complete") { |
| var contentBlock = document.getElementsByClassName("docs")[0] || document.getElementsByClassName("news")[0]; |
| if (!contentBlock) { |
| return; |
| } |
| for (var level = 1; level <= 6; level++) { |
| linkifyAnchors(level, contentBlock); |
| } |
| } |
| }; |
| </script> |
| |
| |
| </body> |
| </html> |