Google Git
Sign in
apache / incubator-retired-quickstep / refs/heads/Test-Storage-Manager / . / parser / tests / Select.test
blob: e70ee5c4d7db83ca7ce4541e7f43b2f31930cc15 [file] [log] [blame]
# Copyright 2011-2015 Quickstep Technologies LLC.
# Copyright 2015 Pivotal Software, Inc.
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
# http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
SELECT * FROM test
--
SelectStatement
+-select_query=Select
+-select_clause=SelectStar
+-from_clause=
+-TableReference[table=test]
==
# If the error location is beyond the end of the SQL,
# we point the error to the place next to the last character.
SELECT *
--
ERROR: syntax error (1 : 9)
SELECT *
^
==
SELECT *;
--
ERROR: syntax error (1 : 9)
SELECT *;
^
==
SELECT $attr FROM test
--
ERROR: illegal character (1 : 9)
SELECT $attr FROM test
^
==
SELECT 1 "" FROM test
--
ERROR: Zero-length identifier (1 : 11)
SELECT 1 "" FROM test
^
==
SELECT 1 FROM test AS ""
--
ERROR: Zero-length identifier (1 : 24)
SELECT 1 FROM test AS ""
^
==
SELECT attr FROM $test
--
ERROR: illegal character (1 : 19)
SELECT attr FROM $test
^
==
SELECT FROM test
--
ERROR: syntax error (1 : 8)
SELECT FROM test
^
==
SELECT attr1
--
ERROR: syntax error (1 : 13)
SELECT attr1
^
==
SELECT 1, 2, 3, 4
--
ERROR: syntax error (1 : 18)
SELECT 1, 2, 3, 4
^
==
SELECT 1, 2, 1+1, 1-1, 1-1.2+1-2.3, attr1, attr2, attr1+1, attr1+1*2+attr2, attr1+1*(2+attr2) FROM test
--
SelectStatement
+-select_query=Select
+-select_clause=SelectList
| +-SelectListItem
| | +-Literal
| | +-NumericLiteral[numeric_string=1,float_like=false]
| +-SelectListItem
| | +-Literal
| | +-NumericLiteral[numeric_string=2,float_like=false]
| +-SelectListItem
| | +-Add
| | +-left_operand=Literal
| | | +-NumericLiteral[numeric_string=1,float_like=false]
| | +-right_operand=Literal
| | +-NumericLiteral[numeric_string=1,float_like=false]
| +-SelectListItem
| | +-Subtract
| | +-left_operand=Literal
| | | +-NumericLiteral[numeric_string=1,float_like=false]
| | +-right_operand=Literal
| | +-NumericLiteral[numeric_string=1,float_like=false]
| +-SelectListItem
| | +-Subtract
| | +-left_operand=Add
| | | +-left_operand=Subtract
| | | | +-left_operand=Literal
| | | | | +-NumericLiteral[numeric_string=1,float_like=false]
| | | | +-right_operand=Literal
| | | | +-NumericLiteral[numeric_string=1.2,float_like=true]
| | | +-right_operand=Literal
| | | +-NumericLiteral[numeric_string=1,float_like=false]
| | +-right_operand=Literal
| | +-NumericLiteral[numeric_string=2.3,float_like=true]
| +-SelectListItem
| | +-AttributeReference[attribute_name=attr1]
| +-SelectListItem
| | +-AttributeReference[attribute_name=attr2]
| +-SelectListItem
| | +-Add
| | +-left_operand=AttributeReference[attribute_name=attr1]
| | +-right_operand=Literal
| | +-NumericLiteral[numeric_string=1,float_like=false]
| +-SelectListItem
| | +-Add
| | +-left_operand=Add
| | | +-left_operand=AttributeReference[attribute_name=attr1]
| | | +-right_operand=Multiply
| | | +-left_operand=Literal
| | | | +-NumericLiteral[numeric_string=1,float_like=false]
| | | +-right_operand=Literal
| | | +-NumericLiteral[numeric_string=2,float_like=false]
| | +-right_operand=AttributeReference[attribute_name=attr2]
| +-SelectListItem
| +-Add
| +-left_operand=AttributeReference[attribute_name=attr1]
| +-right_operand=Multiply
| +-left_operand=Literal
| | +-NumericLiteral[numeric_string=1,float_like=false]
| +-right_operand=Add
| +-left_operand=Literal
| | +-NumericLiteral[numeric_string=2,float_like=false]
| +-right_operand=AttributeReference[attribute_name=attr2]
+-from_clause=
+-TableReference[table=test]
==
SELECT * FROM 123
--
ERROR: syntax error (1 : 15)
SELECT * FROM 123
^
==
# Alias
SELECT 1 AS a, 2+1 AS b, 3 a, 4 b FROM test AS a, test a
--
SelectStatement
+-select_query=Select
+-select_clause=SelectList
| +-SelectListItem[alias=a]
| | +-Literal
| | +-NumericLiteral[numeric_string=1,float_like=false]
| +-SelectListItem[alias=b]
| | +-Add
| | +-left_operand=Literal
| | | +-NumericLiteral[numeric_string=2,float_like=false]
| | +-right_operand=Literal
| | +-NumericLiteral[numeric_string=1,float_like=false]
| +-SelectListItem[alias=a]
| | +-Literal
| | +-NumericLiteral[numeric_string=3,float_like=false]
| +-SelectListItem[alias=b]
| +-Literal
| +-NumericLiteral[numeric_string=4,float_like=false]
+-from_clause=
+-TableReference[table=test]
| +-table_signature=TableSignature[table_alias=a]
+-TableReference[table=test]
+-table_signature=TableSignature[table_alias=a]
==
# Column list aliases in FROM.
SELECT 1 FROM test test_alias(attr, attr1, attr2, attr3), (SELECT * FROM test) AS subquery(attr1, attr1, attr4)
--
SelectStatement
+-select_query=Select
+-select_clause=SelectList
| +-SelectListItem
| +-Literal
| +-NumericLiteral[numeric_string=1,float_like=false]
+-from_clause=
+-TableReference[table=test]
| +-table_signature=TableSignature[table_alias=test_alias,
| columns=(attr, attr1, attr2, attr3)]
+-SubqueryTable
+-table_signature=TableSignature[table_alias=subquery,
| columns=(attr1, attr1, attr4)]
+-SubqueryExpression
+-Select
+-select_clause=SelectStar
+-from_clause=
+-TableReference[table=test]
==
# Table subquery must be named.
SELECT 1 FROM (select * FROM test)
--
ERROR: syntax error (1 : 35)
SELECT 1 FROM (select * FROM test)
^
==
SELECT 1 FROM test test_alias(attr+1)
--
ERROR: syntax error (1 : 35)
SELECT 1 FROM test test_alias(attr+1)
^
==
SELECT 1 FROM test AS
--
ERROR: syntax error (1 : 22)
SELECT 1 FROM test AS
^
==
SELECT 1 AS a+1 FROM test
--
ERROR: syntax error (1 : 14)
SELECT 1 AS a+1 FROM test
^
==
SELECT 1 FROM test AS a+1
--
ERROR: syntax error (1 : 24)
SELECT 1 FROM test AS a+1
^
==
SELECT 1 FROM test WHERE 1=1 AND attr1=1 AND attr+1>=1 AND attr-1<=1 AND attr+1>1 AND attr-1<1
--
SelectStatement
+-select_query=Select
+-select_clause=SelectList
| +-SelectListItem
| +-Literal
| +-NumericLiteral[numeric_string=1,float_like=false]
+-where_clause=And
| +-Equal
| | +-left_operand=Literal
| | | +-NumericLiteral[numeric_string=1,float_like=false]
| | +-right_operand=Literal
| | +-NumericLiteral[numeric_string=1,float_like=false]
| +-Equal
| | +-left_operand=AttributeReference[attribute_name=attr1]
| | +-right_operand=Literal
| | +-NumericLiteral[numeric_string=1,float_like=false]
| +-GreaterOrEqual
| | +-left_operand=Add
| | | +-left_operand=AttributeReference[attribute_name=attr]
| | | +-right_operand=Literal
| | | +-NumericLiteral[numeric_string=1,float_like=false]
| | +-right_operand=Literal
| | +-NumericLiteral[numeric_string=1,float_like=false]
| +-LessOrEqual
| | +-left_operand=Subtract
| | | +-left_operand=AttributeReference[attribute_name=attr]
| | | +-right_operand=Literal
| | | +-NumericLiteral[numeric_string=1,float_like=false]
| | +-right_operand=Literal
| | +-NumericLiteral[numeric_string=1,float_like=false]
| +-Greater
| | +-left_operand=Add
| | | +-left_operand=AttributeReference[attribute_name=attr]
| | | +-right_operand=Literal
| | | +-NumericLiteral[numeric_string=1,float_like=false]
| | +-right_operand=Literal
| | +-NumericLiteral[numeric_string=1,float_like=false]
| +-Less
| +-left_operand=Subtract
| | +-left_operand=AttributeReference[attribute_name=attr]
| | +-right_operand=Literal
| | +-NumericLiteral[numeric_string=1,float_like=false]
| +-right_operand=Literal
| +-NumericLiteral[numeric_string=1,float_like=false]
+-from_clause=
+-TableReference[table=test]
==
SELECT 1 FROM test WHERE attr BETWEEN 1 AND 2 AND attr BETWEEN 2 AND 1
--
SelectStatement
+-select_query=Select
+-select_clause=SelectList
| +-SelectListItem
| +-Literal
| +-NumericLiteral[numeric_string=1,float_like=false]
+-where_clause=And
| +-Between
| | +-check_operand=AttributeReference[attribute_name=attr]
| | +-lower_bound_operand=Literal
| | | +-NumericLiteral[numeric_string=1,float_like=false]
| | +-upper_bound_operand=Literal
| | +-NumericLiteral[numeric_string=2,float_like=false]
| +-Between
| +-check_operand=AttributeReference[attribute_name=attr]
| +-lower_bound_operand=Literal
| | +-NumericLiteral[numeric_string=2,float_like=false]
| +-upper_bound_operand=Literal
| +-NumericLiteral[numeric_string=1,float_like=false]
+-from_clause=
+-TableReference[table=test]
==
SELECT 1 FROM test WHERE attr NOT BETWEEN 1 AND 2
--
SelectStatement
+-select_query=Select
+-select_clause=SelectList
| +-SelectListItem
| +-Literal
| +-NumericLiteral[numeric_string=1,float_like=false]
+-where_clause=Not
| +-Between
| +-check_operand=AttributeReference[attribute_name=attr]
| +-lower_bound_operand=Literal
| | +-NumericLiteral[numeric_string=1,float_like=false]
| +-upper_bound_operand=Literal
| +-NumericLiteral[numeric_string=2,float_like=false]
+-from_clause=
+-TableReference[table=test]
==
SELECT 1 FROM test ORDER BY attr, 1
--
SelectStatement
+-select_query=Select
+-select_clause=SelectList
| +-SelectListItem
| +-Literal
| +-NumericLiteral[numeric_string=1,float_like=false]
+-order_by=OrderBy
| +-OrderByItem[is_asc=true,nulls_first=false]
| | +-AttributeReference[attribute_name=attr]
| +-OrderByItem[is_asc=true,nulls_first=false]
| +-Literal
| +-NumericLiteral[numeric_string=1,float_like=false]
+-from_clause=
+-TableReference[table=test]
==
SELECT fun(attr)+1 FROM test GROUP BY fun()+fun(attr), attr1+attr2 HAVING attr1>1 AND fun(*)>1
--
SelectStatement
+-select_query=Select
+-select_clause=SelectList
| +-SelectListItem
| +-Add
| +-left_operand=FunctionCall[name=fun]
| | +-AttributeReference[attribute_name=attr]
| +-right_operand=Literal
| +-NumericLiteral[numeric_string=1,float_like=false]
+-group_by=GroupBy
| +-Add
| | +-left_operand=FunctionCall[name=fun]
| | +-right_operand=FunctionCall[name=fun]
| | +-AttributeReference[attribute_name=attr]
| +-Add
| +-left_operand=AttributeReference[attribute_name=attr1]
| +-right_operand=AttributeReference[attribute_name=attr2]
+-having=HAVING
| +-And
| +-Greater
| | +-left_operand=AttributeReference[attribute_name=attr1]
| | +-right_operand=Literal
| | +-NumericLiteral[numeric_string=1,float_like=false]
| +-Greater
| +-left_operand=FunctionCall[name=fun,is_star=true]
| +-right_operand=Literal
| +-NumericLiteral[numeric_string=1,float_like=false]
+-from_clause=
+-TableReference[table=test]
==
# ORDER BY
SELECT 1 FROM test ORDER BY 1 ASC,
2 DESC,
3 ASC NULLS FIRST,
attr1 DESC NULLS LAST,
attr1+1 NULLS FIRST,
fun(attr)/2
--
SelectStatement
+-select_query=Select
+-select_clause=SelectList
| +-SelectListItem
| +-Literal
| +-NumericLiteral[numeric_string=1,float_like=false]
+-order_by=OrderBy
| +-OrderByItem[is_asc=true,nulls_first=false]
| | +-Literal
| | +-NumericLiteral[numeric_string=1,float_like=false]
| +-OrderByItem[is_asc=false,nulls_first=true]
| | +-Literal
| | +-NumericLiteral[numeric_string=2,float_like=false]
| +-OrderByItem[is_asc=true,nulls_first=true]
| | +-Literal
| | +-NumericLiteral[numeric_string=3,float_like=false]
| +-OrderByItem[is_asc=false,nulls_first=false]
| | +-AttributeReference[attribute_name=attr1]
| +-OrderByItem[is_asc=true,nulls_first=true]
| | +-Add
| | +-left_operand=AttributeReference[attribute_name=attr1]
| | +-right_operand=Literal
| | +-NumericLiteral[numeric_string=1,float_like=false]
| +-OrderByItem[is_asc=true,nulls_first=false]
| +-Divide
| +-left_operand=FunctionCall[name=fun]
| | +-AttributeReference[attribute_name=attr]
| +-right_operand=Literal
| +-NumericLiteral[numeric_string=2,float_like=false]
+-from_clause=
+-TableReference[table=test]
==
# Limit
SELECT 1 FROM test LIMIT 1
--
SelectStatement
+-select_query=Select
+-select_clause=SelectList
| +-SelectListItem
| +-Literal
| +-NumericLiteral[numeric_string=1,float_like=false]
+-limit=LIMIT
| +-NumericLiteral[numeric_string=1,float_like=false]
+-from_clause=
+-TableReference[table=test]
==
SELECT 1 FROM test LIMIT 1.1
--
ERROR: LIMIT value must be an integer (1 : 26)
SELECT 1 FROM test LIMIT 1.1
^
==
SELECT 1 FROM test LIMIT 0
--
ERROR: LIMIT value must be positive (1 : 26)
SELECT 1 FROM test LIMIT 0
^
==
SELECT 1 FROM test LIMIT -1
--
ERROR: syntax error (1 : 26)
SELECT 1 FROM test LIMIT -1
^
==
SELECT 1 FROM test LIMIT abc
--
ERROR: syntax error (1 : 26)
SELECT 1 FROM test LIMIT abc
^
==
#
# Subqueries
#
# Subqueries in the FROM clause.
SELECT * FROM test, (select * FROM test) AS a, (select * FROM test) a
--
SelectStatement
+-select_query=Select
+-select_clause=SelectStar
+-from_clause=
+-TableReference[table=test]
+-SubqueryTable
| +-table_signature=TableSignature[table_alias=a]
| +-SubqueryExpression
| +-Select
| +-select_clause=SelectStar
| +-from_clause=
| +-TableReference[table=test]
+-SubqueryTable
+-table_signature=TableSignature[table_alias=a]
+-SubqueryExpression
+-Select
+-select_clause=SelectStar
+-from_clause=
+-TableReference[table=test]
==
SELECT * FROM test, SELECT * FROM test
--
ERROR: syntax error (1 : 21)
SELECT * FROM test, SELECT * FROM test
^
==
SELECT * FROM (select * FROM (select * FROM (select * FROM test) a ) a ) a
--
SelectStatement
+-select_query=Select
+-select_clause=SelectStar
+-from_clause=
+-SubqueryTable
+-table_signature=TableSignature[table_alias=a]
+-SubqueryExpression
+-Select
+-select_clause=SelectStar
+-from_clause=
+-SubqueryTable
+-table_signature=TableSignature[table_alias=a]
+-SubqueryExpression
+-Select
+-select_clause=SelectStar
+-from_clause=
+-SubqueryTable
+-table_signature=TableSignature[table_alias=a]
+-SubqueryExpression
+-Select
+-select_clause=SelectStar
+-from_clause=
+-TableReference[table=test]
==
# Subqueries are not supported yet in clauses other than the FROM clause.
SELECT (select * FROM test) FROM test
--
SelectStatement
+-select_query=Select
+-select_clause=SelectList
| +-SelectListItem
| +-SubqueryExpression
| +-Select
| +-select_clause=SelectStar
| +-from_clause=
| +-TableReference[table=test]
+-from_clause=
+-TableReference[table=test]
==
SELECT 1 FROM test WHERE 1 > (select 1 FROM test)
--
SelectStatement
+-select_query=Select
+-select_clause=SelectList
| +-SelectListItem
| +-Literal
| +-NumericLiteral[numeric_string=1,float_like=false]
+-where_clause=Greater
| +-left_operand=Literal
| | +-NumericLiteral[numeric_string=1,float_like=false]
| +-right_operand=SubqueryExpression
| +-Select
| +-select_clause=SelectList
| | +-SelectListItem
| | +-Literal
| | +-NumericLiteral[numeric_string=1,float_like=false]
| +-from_clause=
| +-TableReference[table=test]
+-from_clause=
+-TableReference[table=test]
==
#
# WITH clause
#
WITH a (col) AS (SELECT 1 FROM test GROUP BY a ORDER BY b LIMIT 1),
b (col1, col2, col3) AS (SELECT * FROM (SELECT * FROM test) a),
c AS (SELECT 1 FROM test)
SELECT 1 FROM a, b, c, d
--
SelectStatement
+-select_query=Select
| +-select_clause=SelectList
| | +-SelectListItem
| | +-Literal
| | +-NumericLiteral[numeric_string=1,float_like=false]
| +-from_clause=
| +-TableReference[table=a]
| +-TableReference[table=b]
| +-TableReference[table=c]
| +-TableReference[table=d]
+-with_clause=
+-SubqueryTable
| +-table_signature=TableSignature[table_alias=a,columns=(col)]
| +-SubqueryExpression
| +-Select
| +-select_clause=SelectList
| | +-SelectListItem
| | +-Literal
| | +-NumericLiteral[numeric_string=1,float_like=false]
| +-group_by=GroupBy
| | +-AttributeReference[attribute_name=a]
| +-order_by=OrderBy
| | +-OrderByItem[is_asc=true,nulls_first=false]
| | +-AttributeReference[attribute_name=b]
| +-limit=LIMIT
| | +-NumericLiteral[numeric_string=1,float_like=false]
| +-from_clause=
| +-TableReference[table=test]
+-SubqueryTable
| +-table_signature=TableSignature[table_alias=b,columns=(col1, col2, col3)]
| +-SubqueryExpression
| +-Select
| +-select_clause=SelectStar
| +-from_clause=
| +-SubqueryTable
| +-table_signature=TableSignature[table_alias=a]
| +-SubqueryExpression
| +-Select
| +-select_clause=SelectStar
| +-from_clause=
| +-TableReference[table=test]
+-SubqueryTable
+-table_signature=TableSignature[table_alias=c]
+-SubqueryExpression
+-Select
+-select_clause=SelectList
| +-SelectListItem
| +-Literal
| +-NumericLiteral[numeric_string=1,float_like=false]
+-from_clause=
+-TableReference[table=test]
==
WITH a AS (SELECT 1 FROM test)
SELECT 1 FROM a
--
SelectStatement
+-select_query=Select
| +-select_clause=SelectList
| | +-SelectListItem
| | +-Literal
| | +-NumericLiteral[numeric_string=1,float_like=false]
| +-from_clause=
| +-TableReference[table=a]
+-with_clause=
+-SubqueryTable
+-table_signature=TableSignature[table_alias=a]
+-SubqueryExpression
+-Select
+-select_clause=SelectList
| +-SelectListItem
| +-Literal
| +-NumericLiteral[numeric_string=1,float_like=false]
+-from_clause=
+-TableReference[table=test]
==
# AS cannot be omitted.
WITH a (SELECT 1 FROM test)
SELECT 1 FROM a
--
ERROR: syntax error (1 : 9)
WITH a (SELECT 1 FROM test)
^
==
WITH a AS (SELECT 1 FROM test)
--
ERROR: syntax error (1 : 31)
WITH a AS (SELECT 1 FROM test)
^
==
# The WITH query cannot have WITH.
WITH a AS (with c SELECT 1 FROM test)
SELECT 1 FROM test
--
ERROR: syntax error (1 : 12)
WITH a AS (with c SELECT 1 FROM test)
^
==
WITH a AS (select 1 FROM test) b
SELECT 1 FROM test
--
ERROR: syntax error (1 : 32)
WITH a AS (select 1 FROM test) b
^
==
# Missing comma.
SELECT a AS (select 1 FROM test) b AS (SELECT 1 FROM test)
SELECT 1 FROM test
--
ERROR: syntax error (1 : 13)
SELECT a AS (select 1 FROM test) b AS (SELEC...
^
==
#
# Literal values and types.
#
# Bool literal value is not supported yet.
SELECT 1 FROM test WHERE TRUE
--
ERROR: syntax error (1 : 26)
SELECT 1 FROM test WHERE TRUE
^
==
SELECT 1 FROM test WHERE tb1 = TRUE
--
ERROR: syntax error (1 : 32)
SELECT 1 FROM test WHERE tb1 = TRUE
^
==
# FIXME(chasseur, qzeng): The result is wrong. Add support for hexadecimal literals.
SELECT 0xfff FROM test
--
SelectStatement
+-select_query=Select
+-select_clause=SelectList
| +-SelectListItem[alias=xfff]
| +-Literal
| +-NumericLiteral[numeric_string=0,float_like=false]
+-from_clause=
+-TableReference[table=test]
==
SELECT x'fff' FROM test
--
ERROR: syntax error (1 : 13)
SELECT x'fff' FROM test
^
==
SELECT 123e23, 123e-123, 123e-2+1, 123e-2*2, 123e-123,
1e100000000
FROM test
--
SelectStatement
+-select_query=Select
+-select_clause=SelectList
| +-SelectListItem
| | +-Literal
| | +-NumericLiteral[numeric_string=123e23,float_like=true]
| +-SelectListItem
| | +-Literal
| | +-NumericLiteral[numeric_string=123e-123,float_like=true]
| +-SelectListItem
| | +-Add
| | +-left_operand=Literal
| | | +-NumericLiteral[numeric_string=123e-2,float_like=true]
| | +-right_operand=Literal
| | +-NumericLiteral[numeric_string=1,float_like=false]
| +-SelectListItem
| | +-Multiply
| | +-left_operand=Literal
| | | +-NumericLiteral[numeric_string=123e-2,float_like=true]
| | +-right_operand=Literal
| | +-NumericLiteral[numeric_string=2,float_like=false]
| +-SelectListItem
| | +-Literal
| | +-NumericLiteral[numeric_string=123e-123,float_like=true]
| +-SelectListItem
| +-Literal
| +-NumericLiteral[numeric_string=1e100000000,float_like=true]
+-from_clause=
+-TableReference[table=test]
==
SELECT 9223372036854775805, 9223372036854775807, 9223372036854775809, -9223372036854775805,
-9223372036854775807, -9223372036854775809, 9223372036854775800.8, 1.123456789012345678901234567890,
-9223372036854775800.8, -1.123456789012345678901234567890
FROM test
--
SelectStatement
+-select_query=Select
+-select_clause=SelectList
| +-SelectListItem
| | +-Literal
| | +-NumericLiteral[numeric_string=9223372036854775805,float_like=false]
| +-SelectListItem
| | +-Literal
| | +-NumericLiteral[numeric_string=9223372036854775807,float_like=false]
| +-SelectListItem
| | +-Literal
| | +-NumericLiteral[numeric_string=9223372036854775809,float_like=false]
| +-SelectListItem
| | +-Literal
| | +-NumericLiteral[numeric_string=-9223372036854775805,float_like=false]
| +-SelectListItem
| | +-Literal
| | +-NumericLiteral[numeric_string=-9223372036854775807,float_like=false]
| +-SelectListItem
| | +-Literal
| | +-NumericLiteral[numeric_string=-9223372036854775809,float_like=false]
| +-SelectListItem
| | +-Literal
| | +-NumericLiteral[numeric_string=9223372036854775800.8,float_like=true]
| +-SelectListItem
| | +-Literal
| | +-NumericLiteral[numeric_string=1.123456789012345678901234567890,
| | float_like=true]
| +-SelectListItem
| | +-Literal
| | +-NumericLiteral[numeric_string=-9223372036854775800.8,float_like=true]
| +-SelectListItem
| +-Literal
| +-NumericLiteral[numeric_string=-1.123456789012345678901234567890,
| float_like=true]
+-from_clause=
+-TableReference[table=test]
==
# Escape characters.
SELECT 1, e'$asdfg\'\'\"\"\t\r\n' FROM test WHERE char_col = e'\'asdfg\''
--
SelectStatement
+-select_query=Select
+-select_clause=SelectList
| +-SelectListItem
| | +-Literal
| | +-NumericLiteral[numeric_string=1,float_like=false]
| +-SelectListItem
| +-Literal
| +-StringLiteral[value=$asdfg''""
]
+-where_clause=Equal
| +-left_operand=AttributeReference[attribute_name=char_col]
| +-right_operand=Literal
| +-StringLiteral[value='asdfg']
+-from_clause=
+-TableReference[table=test]
==
# Double-quoted strings are identifiers, which do not need to be escaped.
SELECT 1 "abc\n" FROM test
--
SelectStatement
+-select_query=Select
+-select_clause=SelectList
| +-SelectListItem[alias=abc\n]
| +-Literal
| +-NumericLiteral[numeric_string=1,float_like=false]
+-from_clause=
+-TableReference[table=test]
==
SELECT 1 "abc
def
" FROM test
--
SelectStatement
+-select_query=Select
+-select_clause=SelectList
| +-SelectListItem[alias=abc
def
]
| +-Literal
| +-NumericLiteral[numeric_string=1,float_like=false]
+-from_clause=
+-TableReference[table=test]
==
# Currently predicate is not treated as a regular scalar expression.
SELECT 1 FROM test WHERE (1>1)=(1<1)
--
ERROR: syntax error (1 : 31)
SELECT 1 FROM test WHERE (1>1)=(1<1)
^
==
SELECT 1 """this IS a double-quoted string""", 2 "this IS NOT a double-quoted string" FROM test
--
SelectStatement
+-select_query=Select
+-select_clause=SelectList
| +-SelectListItem[alias="this IS a double-quoted string"]
| | +-Literal
| | +-NumericLiteral[numeric_string=1,float_like=false]
| +-SelectListItem[alias=this IS NOT a double-quoted string]
| +-Literal
| +-NumericLiteral[numeric_string=2,float_like=false]
+-from_clause=
+-TableReference[table=test]
==
#
# DateTime and Interval
#
SELECT '1998-12-01',
DATE '1998-12-01'
FROM test
--
SelectStatement
+-select_query=Select
+-select_clause=SelectList
| +-SelectListItem
| | +-Literal
| | +-StringLiteral[value=1998-12-01]
| +-SelectListItem
| +-Literal
| +-StringLiteral[value=1998-12-01,explicit_type=Datetime]
+-from_clause=
+-TableReference[table=test]
==
# Year before 1970.
SELECT DATE '1960-12-12',
DATE '1901-12-14' FROM test
--
SelectStatement
+-select_query=Select
+-select_clause=SelectList
| +-SelectListItem
| | +-Literal
| | +-StringLiteral[value=1960-12-12,explicit_type=Datetime]
| +-SelectListItem
| +-Literal
| +-StringLiteral[value=1901-12-14,explicit_type=Datetime]
+-from_clause=
+-TableReference[table=test]
==
SELECT DATE '1998-2-12', DATE '1998-12-2' FROM test
--
SelectStatement
+-select_query=Select
+-select_clause=SelectList
| +-SelectListItem
| | +-Literal
| | +-StringLiteral[value=1998-2-12,explicit_type=Datetime]
| +-SelectListItem
| +-Literal
| +-StringLiteral[value=1998-12-2,explicit_type=Datetime]
+-from_clause=
+-TableReference[table=test]
==
SELECT DATE '+1921-12-12',
DATE '+10001-12-12'
FROM test
--
SelectStatement
+-select_query=Select
+-select_clause=SelectList
| +-SelectListItem
| | +-Literal
| | +-StringLiteral[value=+1921-12-12,explicit_type=Datetime]
| +-SelectListItem
| +-Literal
| +-StringLiteral[value=+10001-12-12,explicit_type=Datetime]
+-from_clause=
+-TableReference[table=test]
==
SELECT DATE 'a1998-12-12' FROM test
--
ERROR: Failed to parse literal as specified type (1 : 25)
SELECT DATE 'a1998-12-12' FROM test
^
==
SELECT DATE '1998-a12-b12' FROM test
--
ERROR: Failed to parse literal as specified type (1 : 26)
SELECT DATE '1998-a12-b12' FROM test
^
==
SELECT DATE '1998-+12-12' FROM test
--
ERROR: Failed to parse literal as specified type (1 : 25)
SELECT DATE '1998-+12-12' FROM test
^
==
SELECT DATE '1998-12-12abc' FROM test
--
ERROR: Failed to parse literal as specified type (1 : 27)
SELECT DATE '1998-12-12abc' FROM test
^
==
SELECT DATE '1998/12/12' FROM test
--
ERROR: Failed to parse literal as specified type (1 : 24)
SELECT DATE '1998/12/12' FROM test
^
==
SELECT DATE '1996-02-29',
DATE '1997-03-31',
DATE '1998-04-30' FROM test
--
SelectStatement
+-select_query=Select
+-select_clause=SelectList
| +-SelectListItem
| | +-Literal
| | +-StringLiteral[value=1996-02-29,explicit_type=Datetime]
| +-SelectListItem
| | +-Literal
| | +-StringLiteral[value=1997-03-31,explicit_type=Datetime]
| +-SelectListItem
| +-Literal
| +-StringLiteral[value=1998-04-30,explicit_type=Datetime]
+-from_clause=
+-TableReference[table=test]
==
SELECT DATE '1999-02-29' FROM test
--
ERROR: Failed to parse literal as specified type (1 : 24)
SELECT DATE '1999-02-29' FROM test
^
==
# Quickstep accepts time in the DATE type.
SELECT DATE '2007-05-08 12:35:29',
DATE '2007-05-08 12:35:29.010'
FROM test
--
SelectStatement
+-select_query=Select
+-select_clause=SelectList
| +-SelectListItem
| | +-Literal
| | +-StringLiteral[value=2007-05-08 12:35:29,explicit_type=Datetime]
| +-SelectListItem
| +-Literal
| +-StringLiteral[value=2007-05-08 12:35:29.010,explicit_type=Datetime]
+-from_clause=
+-TableReference[table=test]
==
SELECT DATE '1999-04-31' FROM test
--
ERROR: Failed to parse literal as specified type (1 : 24)
SELECT DATE '1999-04-31' FROM test
^
==
SELECT 1 FROM test WHERE attr_date <= DATE '1998-12-01' - INTERVAL '96 day'
--
SelectStatement
+-select_query=Select
+-select_clause=SelectList
| +-SelectListItem
| +-Literal
| +-NumericLiteral[numeric_string=1,float_like=false]
+-where_clause=LessOrEqual
| +-left_operand=AttributeReference[attribute_name=attr_date]
| +-right_operand=Subtract
| +-left_operand=Literal
| | +-StringLiteral[value=1998-12-01,explicit_type=Datetime]
| +-right_operand=Literal
| +-StringLiteral[value=96 day,explicit_type=DatetimeInterval]
+-from_clause=
+-TableReference[table=test]
==
SELECT INTERVAL '1 us', INTERVAL '1 ms', INTERVAL '1 s', INTERVAL '1 minute',
INTERVAL '1 h', INTERVAL '1 day', INTERVAL '1 week',
INTERVAL '1 month', INTERVAL '1 year', INTERVAL '1 decade',
INTERVAL '1 century', INTERVAL '1 millennium',
DATETIME INTERVAL '1 us', DATETIME INTERVAL '1 ms',
DATETIME INTERVAL '1 s', DATETIME INTERVAL '1 minute',
DATETIME INTERVAL '1 h', DATETIME INTERVAL '1 day',
DATETIME INTERVAL '1 week', YEARMONTH INTERVAL '1 month',
YEARMONTH INTERVAL '1 year', YEARMONTH INTERVAL '1 decade',
YEARMONTH INTERVAL '1 century', YEARMONTH INTERVAL '1 millennium'
FROM test
--
SelectStatement
+-select_query=Select
+-select_clause=SelectList
| +-SelectListItem
| | +-Literal
| | +-StringLiteral[value=1 us,explicit_type=DatetimeInterval]
| +-SelectListItem
| | +-Literal
| | +-StringLiteral[value=1 ms,explicit_type=DatetimeInterval]
| +-SelectListItem
| | +-Literal
| | +-StringLiteral[value=1 s,explicit_type=DatetimeInterval]
| +-SelectListItem
| | +-Literal
| | +-StringLiteral[value=1 minute,explicit_type=DatetimeInterval]
| +-SelectListItem
| | +-Literal
| | +-StringLiteral[value=1 h,explicit_type=DatetimeInterval]
| +-SelectListItem
| | +-Literal
| | +-StringLiteral[value=1 day,explicit_type=DatetimeInterval]
| +-SelectListItem
| | +-Literal
| | +-StringLiteral[value=1 week,explicit_type=DatetimeInterval]
| +-SelectListItem
| | +-Literal
| | +-StringLiteral[value=1 month,explicit_type=YearMonthInterval]
| +-SelectListItem
| | +-Literal
| | +-StringLiteral[value=1 year,explicit_type=YearMonthInterval]
| +-SelectListItem
| | +-Literal
| | +-StringLiteral[value=1 decade,explicit_type=YearMonthInterval]
| +-SelectListItem
| | +-Literal
| | +-StringLiteral[value=1 century,explicit_type=YearMonthInterval]
| +-SelectListItem
| | +-Literal
| | +-StringLiteral[value=1 millennium,explicit_type=YearMonthInterval]
| +-SelectListItem
| | +-Literal
| | +-StringLiteral[value=1 us,explicit_type=DatetimeInterval]
| +-SelectListItem
| | +-Literal
| | +-StringLiteral[value=1 ms,explicit_type=DatetimeInterval]
| +-SelectListItem
| | +-Literal
| | +-StringLiteral[value=1 s,explicit_type=DatetimeInterval]
| +-SelectListItem
| | +-Literal
| | +-StringLiteral[value=1 minute,explicit_type=DatetimeInterval]
| +-SelectListItem
| | +-Literal
| | +-StringLiteral[value=1 h,explicit_type=DatetimeInterval]
| +-SelectListItem
| | +-Literal
| | +-StringLiteral[value=1 day,explicit_type=DatetimeInterval]
| +-SelectListItem
| | +-Literal
| | +-StringLiteral[value=1 week,explicit_type=DatetimeInterval]
| +-SelectListItem
| | +-Literal
| | +-StringLiteral[value=1 month,explicit_type=YearMonthInterval]
| +-SelectListItem
| | +-Literal
| | +-StringLiteral[value=1 year,explicit_type=YearMonthInterval]
| +-SelectListItem
| | +-Literal
| | +-StringLiteral[value=1 decade,explicit_type=YearMonthInterval]
| +-SelectListItem
| | +-Literal
| | +-StringLiteral[value=1 century,explicit_type=YearMonthInterval]
| +-SelectListItem
| +-Literal
| +-StringLiteral[value=1 millennium,explicit_type=YearMonthInterval]
+-from_clause=
+-TableReference[table=test]
==
SELECT INTERVAL '4 day' (1)
FROM test
--
ERROR: syntax error (1 : 25)
SELECT INTERVAL '4 day' (1)
^
==
SELECT INTERVAL '2 moth' FROM test
--
ERROR: Failed to parse literal as specified type (1 : 24)
SELECT INTERVAL '2 moth' FROM test
^
==
SELECT INTERVAL '-3 year' FROM test
--
SelectStatement
+-select_query=Select
+-select_clause=SelectList
| +-SelectListItem
| +-Literal
| +-StringLiteral[value=-3 year,explicit_type=YearMonthInterval]
+-from_clause=
+-TableReference[table=test]
==
SELECT INTERVAL 'a 3 year' FROM test
--
ERROR: Failed to parse literal as specified type (1 : 26)
SELECT INTERVAL 'a 3 year' FROM test
^
==
SELECT INTERVAL '5-3 year to month' FROM test
--
ERROR: Failed to parse literal as specified type (1 : 35)
SELECT INTERVAL '5-3 year to month' FROM test
^
==
SELECT INTERVAL '5-3 day to second' FROM test
--
ERROR: Failed to parse literal as specified type (1 : 35)
SELECT INTERVAL '5-3 day to second' FROM test
^
==
#
# Minus sign in arithmetic expressions is parsed as the subtraction
# or negation operation, not as the notation of a negative value.
#
SELECT 1-1 FROM test
--
SelectStatement
+-select_query=Select
+-select_clause=SelectList
| +-SelectListItem
| +-Subtract
| +-left_operand=Literal
| | +-NumericLiteral[numeric_string=1,float_like=false]
| +-right_operand=Literal
| +-NumericLiteral[numeric_string=1,float_like=false]
+-from_clause=
+-TableReference[table=test]
==
SELECT 1 - 1 FROM test
--
[same as above]
==
SELECT 1--1 FROM test
--
ERROR: syntax error (1 : 22)
SELECT 1--1 FROM test
^
==
SELECT 1+-1 FROM test
--
SelectStatement
+-select_query=Select
+-select_clause=SelectList
| +-SelectListItem
| +-Add
| +-left_operand=Literal
| | +-NumericLiteral[numeric_string=1,float_like=false]
| +-right_operand=Literal
| +-NumericLiteral[numeric_string=-1,float_like=false]
+-from_clause=
+-TableReference[table=test]
==
SELECT 1-1+-1 FROM test
--
SelectStatement
+-select_query=Select
+-select_clause=SelectList
| +-SelectListItem
| +-Add
| +-left_operand=Subtract
| | +-left_operand=Literal
| | | +-NumericLiteral[numeric_string=1,float_like=false]
| | +-right_operand=Literal
| | +-NumericLiteral[numeric_string=1,float_like=false]
| +-right_operand=Literal
| +-NumericLiteral[numeric_string=-1,float_like=false]
+-from_clause=
+-TableReference[table=test]
==
SELECT 1+(-1) FROM test
--
SelectStatement
+-select_query=Select
+-select_clause=SelectList
| +-SelectListItem
| +-Add
| +-left_operand=Literal
| | +-NumericLiteral[numeric_string=1,float_like=false]
| +-right_operand=Literal
| +-NumericLiteral[numeric_string=-1,float_like=false]
+-from_clause=
+-TableReference[table=test]
==
# Quit statement
QUIT
--
QuitStatement
==
# Literals with various explicit types.
SELECT INT '1', LONG '1', FLOAT '1', DOUBLE '1', char(42) 'foo', varchar(42) 'bar' FROM test
--
SelectStatement
+-select_query=Select
+-select_clause=SelectList
| +-SelectListItem
| | +-Literal
| | +-StringLiteral[value=1,explicit_type=Int]
| +-SelectListItem
| | +-Literal
| | +-StringLiteral[value=1,explicit_type=Long]
| +-SelectListItem
| | +-Literal
| | +-StringLiteral[value=1,explicit_type=Float]
| +-SelectListItem
| | +-Literal
| | +-StringLiteral[value=1,explicit_type=Double]
| +-SelectListItem
| | +-Literal
| | +-StringLiteral[value=foo,explicit_type=Char(42)]
| +-SelectListItem
| +-Literal
| +-StringLiteral[value=bar,explicit_type=VarChar(42)]
+-from_clause=
+-TableReference[table=test]
==
# Value needs to be quoted when giving an explicit type.
SELECT INT 1 FROM test
--
ERROR: syntax error (1 : 12)
SELECT INT 1 FROM test
^
==
# Explicit type that can't parse the supplied string.
SELECT INT 'foo' FROM test
--
ERROR: Failed to parse literal as specified type (1 : 16)
SELECT INT 'foo' FROM test
^
==
# Various floats that, while not in the canonical print format, are still
# acceptable according to the SQL standard.
SELECT 1. FROM test
--
SelectStatement
+-select_query=Select
+-select_clause=SelectList
| +-SelectListItem
| +-Literal
| +-NumericLiteral[numeric_string=1.,float_like=true]
+-from_clause=
+-TableReference[table=test]
==
SELECT .1 FROM test
--
SelectStatement
+-select_query=Select
+-select_clause=SelectList
| +-SelectListItem
| +-Literal
| +-NumericLiteral[numeric_string=.1,float_like=true]
+-from_clause=
+-TableReference[table=test]
==
SELECT 1.e1 FROM test
--
SelectStatement
+-select_query=Select
+-select_clause=SelectList
| +-SelectListItem
| +-Literal
| +-NumericLiteral[numeric_string=1.e1,float_like=true]
+-from_clause=
+-TableReference[table=test]
==
SELECT .1e1 FROM test
--
SelectStatement
+-select_query=Select
+-select_clause=SelectList
| +-SelectListItem
| +-Literal
| +-NumericLiteral[numeric_string=.1e1,float_like=true]
+-from_clause=
+-TableReference[table=test]
==
# EXISTS subquery
SELECT i
FROM generate_series(0, 100, 3) AS gs1(i)
WHERE
EXISTS (
SELECT *
FROM generate_series(0, 100, 5) AS gs2(i)
WHERE gs1.i = gs2.i
)
AND NOT EXISTS (
SELECT *
FROM generate_series(0, 100, 10) AS gs3(i)
WHERE gs1.i = gs3.i
);
--
SelectStatement
+-select_query=Select
+-select_clause=SelectList
| +-SelectListItem
| +-AttributeReference[attribute_name=i]
+-where_clause=And
| +-Exists
| | +-subquery=SubqueryExpression
| | +-Select
| | +-select_clause=SelectStar
| | +-where_clause=Equal
| | | +-left_operand=AttributeReference[attribute_name=i,relation_name=gs1]
| | | +-right_operand=AttributeReference[attribute_name=i,
| | | relation_name=gs2]
| | +-from_clause=
| | +-TableGenerator
| | +-FunctionCall[name=generate_series]
| | +-Literal
| | | +-NumericLiteral[numeric_string=0,float_like=false]
| | +-Literal
| | | +-NumericLiteral[numeric_string=100,float_like=false]
| | +-Literal
| | +-NumericLiteral[numeric_string=5,float_like=false]
| +-Not
| +-Exists
| +-subquery=SubqueryExpression
| +-Select
| +-select_clause=SelectStar
| +-where_clause=Equal
| | +-left_operand=AttributeReference[attribute_name=i,
| | | relation_name=gs1]
| | +-right_operand=AttributeReference[attribute_name=i,
| | relation_name=gs3]
| +-from_clause=
| +-TableGenerator
| +-FunctionCall[name=generate_series]
| +-Literal
| | +-NumericLiteral[numeric_string=0,float_like=false]
| +-Literal
| | +-NumericLiteral[numeric_string=100,float_like=false]
| +-Literal
| +-NumericLiteral[numeric_string=10,float_like=false]
+-from_clause=
+-TableGenerator
+-FunctionCall[name=generate_series]
+-Literal
| +-NumericLiteral[numeric_string=0,float_like=false]
+-Literal
| +-NumericLiteral[numeric_string=100,float_like=false]
+-Literal
+-NumericLiteral[numeric_string=3,float_like=false]
==
# CASE expressions.
SELECT CASE col1%2
WHEN 1 THEN 'odd'
ELSE 'even'
END
FROM test;
--
SelectStatement
+-select_query=Select
+-select_clause=SelectList
| +-SelectListItem
| +-SimpleCaseExpression
| +-case_operand=Modulo
| | +-left_operand=AttributeReference[attribute_name=col1]
| | +-right_operand=Literal
| | +-NumericLiteral[numeric_string=2,float_like=false]
| +-else_result_expression=Literal
| | +-StringLiteral[value=even]
| +-when_clauses=
| +-SimpleWhenClause
| +-condition_operand=Literal
| | +-NumericLiteral[numeric_string=1,float_like=false]
| +-result_expression=Literal
| +-StringLiteral[value=odd]
+-from_clause=
+-TableReference[table=test]
==
SELECT *
FROM test
WHERE CASE WHEN col1 > col2 THEN col3
ELSE col4
END > 0;
--
SelectStatement
+-select_query=Select
+-select_clause=SelectStar
+-where_clause=Greater
| +-left_operand=SearchedCaseExpression
| | +-else_result_expression=AttributeReference[attribute_name=col4]
| | +-when_clauses=
| | +-SearchedWhenClause
| | +-condition_predicate=Greater
| | | +-left_operand=AttributeReference[attribute_name=col1]
| | | +-right_operand=AttributeReference[attribute_name=col2]
| | +-result_expression=AttributeReference[attribute_name=col3]
| +-right_operand=Literal
| +-NumericLiteral[numeric_string=0,float_like=false]
+-from_clause=
+-TableReference[table=test]
==
SELECT
FUN(CASE col1
WHEN col2 + FUN(
CASE WHEN col1 < col2 THEN 0
ELSE 1
END +
CASE WHEN col1 < col3 THEN 0
ELSE 1
END
) THEN 1
WHEN col3 THEN 2
ELSE 0
END + col4)
FROM test;
--
SelectStatement
+-select_query=Select
+-select_clause=SelectList
| +-SelectListItem
| +-FunctionCall[name=FUN]
| +-Add
| +-left_operand=SimpleCaseExpression
| | +-case_operand=AttributeReference[attribute_name=col1]
| | +-else_result_expression=Literal
| | | +-NumericLiteral[numeric_string=0,float_like=false]
| | +-when_clauses=
| | +-SimpleWhenClause
| | | +-condition_operand=Add
| | | | +-left_operand=AttributeReference[attribute_name=col2]
| | | | +-right_operand=FunctionCall[name=FUN]
| | | | +-Add
| | | | +-left_operand=SearchedCaseExpression
| | | | | +-else_result_expression=Literal
| | | | | | +-NumericLiteral[numeric_string=1,float_like=false]
| | | | | +-when_clauses=
| | | | | +-SearchedWhenClause
| | | | | +-condition_predicate=Less
| | | | | | +-left_operand=AttributeReference[
| | | | | | | attribute_name=col1]
| | | | | | +-right_operand=AttributeReference[
| | | | | | attribute_name=col2]
| | | | | +-result_expression=Literal
| | | | | +-NumericLiteral[numeric_string=0,float_like=false]
| | | | +-right_operand=SearchedCaseExpression
| | | | +-else_result_expression=Literal
| | | | | +-NumericLiteral[numeric_string=1,float_like=false]
| | | | +-when_clauses=
| | | | +-SearchedWhenClause
| | | | +-condition_predicate=Less
| | | | | +-left_operand=AttributeReference[
| | | | | | attribute_name=col1]
| | | | | +-right_operand=AttributeReference[
| | | | | attribute_name=col3]
| | | | +-result_expression=Literal
| | | | +-NumericLiteral[numeric_string=0,float_like=false]
| | | +-result_expression=Literal
| | | +-NumericLiteral[numeric_string=1,float_like=false]
| | +-SimpleWhenClause
| | +-condition_operand=AttributeReference[attribute_name=col3]
| | +-result_expression=Literal
| | +-NumericLiteral[numeric_string=2,float_like=false]
| +-right_operand=AttributeReference[attribute_name=col4]
+-from_clause=
+-TableReference[table=test]
==
# IN predicate
SELECT *
FROM test
WHERE col1 IN (1, 3, 5);
--
SelectStatement
+-select_query=Select
+-select_clause=SelectStar
+-where_clause=InValueList
| +-test_expression=AttributeReference[attribute_name=col1]
| +-value_list=
| +-Literal
| | +-NumericLiteral[numeric_string=1,float_like=false]
| +-Literal
| | +-NumericLiteral[numeric_string=3,float_like=false]
| +-Literal
| +-NumericLiteral[numeric_string=5,float_like=false]
+-from_clause=
+-TableReference[table=test]
==
SELECT *
FROM test
WHERE col1 IN (FUN(1),
col2+col3,
CASE WHEN col4 > 0 THEN col5 ELSE col6 END);
--
SelectStatement
+-select_query=Select
+-select_clause=SelectStar
+-where_clause=InValueList
| +-test_expression=AttributeReference[attribute_name=col1]
| +-value_list=
| +-FunctionCall[name=FUN]
| | +-Literal
| | +-NumericLiteral[numeric_string=1,float_like=false]
| +-Add
| | +-left_operand=AttributeReference[attribute_name=col2]
| | +-right_operand=AttributeReference[attribute_name=col3]
| +-SearchedCaseExpression
| +-else_result_expression=AttributeReference[attribute_name=col6]
| +-when_clauses=
| +-SearchedWhenClause
| +-condition_predicate=Greater
| | +-left_operand=AttributeReference[attribute_name=col4]
| | +-right_operand=Literal
| | +-NumericLiteral[numeric_string=0,float_like=false]
| +-result_expression=AttributeReference[attribute_name=col5]
+-from_clause=
+-TableReference[table=test]
==
SELECT *
FROM test
WHERE col1 NOT IN (col1, col2 + col3);
--
SelectStatement
+-select_query=Select
+-select_clause=SelectStar
+-where_clause=Not
| +-InValueList
| +-test_expression=AttributeReference[attribute_name=col1]
| +-value_list=
| +-AttributeReference[attribute_name=col1]
| +-Add
| +-left_operand=AttributeReference[attribute_name=col2]
| +-right_operand=AttributeReference[attribute_name=col3]
+-from_clause=
+-TableReference[table=test]
==
SELECT *
FROM test
WHERE col1 IN (
SELECT SUM(col2+col3)
FROM bar
GROUP BY col4
);
--
SelectStatement
+-select_query=Select
+-select_clause=SelectStar
+-where_clause=InTableQuery
| +-test_expression=AttributeReference[attribute_name=col1]
| +-table_query=SubqueryExpression
| +-Select
| +-select_clause=SelectList
| | +-SelectListItem
| | +-FunctionCall[name=SUM]
| | +-Add
| | +-left_operand=AttributeReference[attribute_name=col2]
| | +-right_operand=AttributeReference[attribute_name=col3]
| +-group_by=GroupBy
| | +-AttributeReference[attribute_name=col4]
| +-from_clause=
| +-TableReference[table=bar]
+-from_clause=
+-TableReference[table=test]
==
SELECT *
FROM test
WHERE col1 NOT IN (
SELECT col2
FROM bar
WHERE col3 IN (col4, col5)
);
--
SelectStatement
+-select_query=Select
+-select_clause=SelectStar
+-where_clause=Not
| +-InTableQuery
| +-test_expression=AttributeReference[attribute_name=col1]
| +-table_query=SubqueryExpression
| +-Select
| +-select_clause=SelectList
| | +-SelectListItem
| | +-AttributeReference[attribute_name=col2]
| +-where_clause=InValueList
| | +-test_expression=AttributeReference[attribute_name=col3]
| | +-value_list=
| | +-AttributeReference[attribute_name=col4]
| | +-AttributeReference[attribute_name=col5]
| +-from_clause=
| +-TableReference[table=bar]
+-from_clause=
+-TableReference[table=test]