SELECT name, (SELECT max(pop) FROM cities
WHERE cities.state = states.name)
FROM states;
|
Right
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( Iden [ Name Nothing "name" ] , Nothing )
, ( SubQueryExpr
SqSq
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( App [ Name Nothing "max" ] [ Iden [ Name Nothing "pop" ] ]
, Nothing
)
]
, qeFrom = [ TRSimple [ Name Nothing "cities" ] ]
, qeWhere =
Just
(BinOp
(Iden [ Name Nothing "cities" , Name Nothing "state" ])
[ Name Nothing "=" ]
(Iden [ Name Nothing "states" , Name Nothing "name" ]))
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
, Nothing
)
]
, qeFrom = [ TRSimple [ Name Nothing "states" ] ]
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
SELECT ROW(1,2.5,'this is a test');
|
Right
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( App
[ Name Nothing "ROW" ]
[ NumLit "1" , NumLit "2.5" , StringLit "'" "'" "this is a test" ]
, Nothing
)
]
, qeFrom = []
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
SELECT ROW(t.*, 42) FROM t;
|
Right
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( App
[ Name Nothing "ROW" ]
[ BinOp (Iden [ Name Nothing "t" ]) [ Name Nothing "." ] Star
, NumLit "42"
]
, Nothing
)
]
, qeFrom = [ TRSimple [ Name Nothing "t" ] ]
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
SELECT ROW(t.f1, t.f2, 42) FROM t;
|
Right
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( App
[ Name Nothing "ROW" ]
[ Iden [ Name Nothing "t" , Name Nothing "f1" ]
, Iden [ Name Nothing "t" , Name Nothing "f2" ]
, NumLit "42"
]
, Nothing
)
]
, qeFrom = [ TRSimple [ Name Nothing "t" ] ]
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
SELECT getf1(CAST(ROW(11,'this is a test',2.5) AS myrowtype));
|
Right
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( App
[ Name Nothing "getf1" ]
[ Cast
(App
[ Name Nothing "ROW" ]
[ NumLit "11"
, StringLit "'" "'" "this is a test"
, NumLit "2.5"
])
(TypeName [ Name Nothing "myrowtype" ])
]
, Nothing
)
]
, qeFrom = []
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
SELECT ROW(1,2.5,'this is a test') = ROW(1, 3, 'not the same');
|
Right
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( BinOp
(App
[ Name Nothing "ROW" ]
[ NumLit "1" , NumLit "2.5" , StringLit "'" "'" "this is a test" ])
[ Name Nothing "=" ]
(App
[ Name Nothing "ROW" ]
[ NumLit "1" , NumLit "3" , StringLit "'" "'" "not the same" ])
, Nothing
)
]
, qeFrom = []
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
SELECT ROW(tablex.*) IS NULL FROM tablex;
|
Right
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( PostfixOp
[ Name Nothing "is null" ]
(App
[ Name Nothing "ROW" ]
[ BinOp (Iden [ Name Nothing "tablex" ]) [ Name Nothing "." ] Star
])
, Nothing
)
]
, qeFrom = [ TRSimple [ Name Nothing "tablex" ] ]
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
SELECT true OR somefunc();
|
Right
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( BinOp
(Iden [ Name Nothing "true" ])
[ Name Nothing "or" ]
(App [ Name Nothing "somefunc" ] [])
, Nothing
)
]
, qeFrom = []
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
SELECT somefunc() OR true;
|
Right
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( BinOp
(App [ Name Nothing "somefunc" ] [])
[ Name Nothing "or" ]
(Iden [ Name Nothing "true" ])
, Nothing
)
]
, qeFrom = []
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
SELECT * FROM t1 CROSS JOIN t2;
|
Right
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Star , Nothing ) ]
, qeFrom =
[ TRJoin
(TRSimple [ Name Nothing "t1" ])
False
JCross
(TRSimple [ Name Nothing "t2" ])
Nothing
]
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
SELECT * FROM t1 INNER JOIN t2 ON t1.num = t2.num;
|
Right
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Star , Nothing ) ]
, qeFrom =
[ TRJoin
(TRSimple [ Name Nothing "t1" ])
False
JInner
(TRSimple [ Name Nothing "t2" ])
(Just
(JoinOn
(BinOp
(Iden [ Name Nothing "t1" , Name Nothing "num" ])
[ Name Nothing "=" ]
(Iden [ Name Nothing "t2" , Name Nothing "num" ]))))
]
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
SELECT * FROM t1 INNER JOIN t2 USING (num);
|
Right
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Star , Nothing ) ]
, qeFrom =
[ TRJoin
(TRSimple [ Name Nothing "t1" ])
False
JInner
(TRSimple [ Name Nothing "t2" ])
(Just (JoinUsing [ Name Nothing "num" ]))
]
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
SELECT * FROM t1 NATURAL INNER JOIN t2;
|
Right
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Star , Nothing ) ]
, qeFrom =
[ TRJoin
(TRSimple [ Name Nothing "t1" ])
True
JInner
(TRSimple [ Name Nothing "t2" ])
Nothing
]
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num;
|
Right
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Star , Nothing ) ]
, qeFrom =
[ TRJoin
(TRSimple [ Name Nothing "t1" ])
False
JLeft
(TRSimple [ Name Nothing "t2" ])
(Just
(JoinOn
(BinOp
(Iden [ Name Nothing "t1" , Name Nothing "num" ])
[ Name Nothing "=" ]
(Iden [ Name Nothing "t2" , Name Nothing "num" ]))))
]
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
SELECT * FROM t1 LEFT JOIN t2 USING (num);
|
Right
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Star , Nothing ) ]
, qeFrom =
[ TRJoin
(TRSimple [ Name Nothing "t1" ])
False
JLeft
(TRSimple [ Name Nothing "t2" ])
(Just (JoinUsing [ Name Nothing "num" ]))
]
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
SELECT * FROM t1 RIGHT JOIN t2 ON t1.num = t2.num;
|
Right
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Star , Nothing ) ]
, qeFrom =
[ TRJoin
(TRSimple [ Name Nothing "t1" ])
False
JRight
(TRSimple [ Name Nothing "t2" ])
(Just
(JoinOn
(BinOp
(Iden [ Name Nothing "t1" , Name Nothing "num" ])
[ Name Nothing "=" ]
(Iden [ Name Nothing "t2" , Name Nothing "num" ]))))
]
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
SELECT * FROM t1 FULL JOIN t2 ON t1.num = t2.num;
|
Right
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Star , Nothing ) ]
, qeFrom =
[ TRJoin
(TRSimple [ Name Nothing "t1" ])
False
JFull
(TRSimple [ Name Nothing "t2" ])
(Just
(JoinOn
(BinOp
(Iden [ Name Nothing "t1" , Name Nothing "num" ])
[ Name Nothing "=" ]
(Iden [ Name Nothing "t2" , Name Nothing "num" ]))))
]
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num AND t2.value = 'xxx';
|
Right
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Star , Nothing ) ]
, qeFrom =
[ TRJoin
(TRSimple [ Name Nothing "t1" ])
False
JLeft
(TRSimple [ Name Nothing "t2" ])
(Just
(JoinOn
(BinOp
(BinOp
(Iden [ Name Nothing "t1" , Name Nothing "num" ])
[ Name Nothing "=" ]
(Iden [ Name Nothing "t2" , Name Nothing "num" ]))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "t2" , Name Nothing "value" ])
[ Name Nothing "=" ]
(StringLit "'" "'" "xxx")))))
]
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num WHERE t2.value = 'xxx';
|
Right
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Star , Nothing ) ]
, qeFrom =
[ TRJoin
(TRSimple [ Name Nothing "t1" ])
False
JLeft
(TRSimple [ Name Nothing "t2" ])
(Just
(JoinOn
(BinOp
(Iden [ Name Nothing "t1" , Name Nothing "num" ])
[ Name Nothing "=" ]
(Iden [ Name Nothing "t2" , Name Nothing "num" ]))))
]
, qeWhere =
Just
(BinOp
(Iden [ Name Nothing "t2" , Name Nothing "value" ])
[ Name Nothing "=" ]
(StringLit "'" "'" "xxx"))
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
SELECT * FROM some_very_long_table_name s
JOIN another_fairly_long_name a ON s.id = a.num;
|
Right
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Star , Nothing ) ]
, qeFrom =
[ TRJoin
(TRAlias
(TRSimple [ Name Nothing "some_very_long_table_name" ])
(Alias (Name Nothing "s") Nothing))
False
JInner
(TRAlias
(TRSimple [ Name Nothing "another_fairly_long_name" ])
(Alias (Name Nothing "a") Nothing))
(Just
(JoinOn
(BinOp
(Iden [ Name Nothing "s" , Name Nothing "id" ])
[ Name Nothing "=" ]
(Iden [ Name Nothing "a" , Name Nothing "num" ]))))
]
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
SELECT * FROM people AS mother JOIN people AS child
ON mother.id = child.mother_id;
|
Right
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Star , Nothing ) ]
, qeFrom =
[ TRJoin
(TRAlias
(TRSimple [ Name Nothing "people" ])
(Alias (Name Nothing "mother") Nothing))
False
JInner
(TRAlias
(TRSimple [ Name Nothing "people" ])
(Alias (Name Nothing "child") Nothing))
(Just
(JoinOn
(BinOp
(Iden [ Name Nothing "mother" , Name Nothing "id" ])
[ Name Nothing "=" ]
(Iden [ Name Nothing "child" , Name Nothing "mother_id" ]))))
]
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
SELECT * FROM my_table AS a CROSS JOIN my_table AS b;
|
Right
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Star , Nothing ) ]
, qeFrom =
[ TRJoin
(TRAlias
(TRSimple [ Name Nothing "my_table" ])
(Alias (Name Nothing "a") Nothing))
False
JCross
(TRAlias
(TRSimple [ Name Nothing "my_table" ])
(Alias (Name Nothing "b") Nothing))
Nothing
]
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
SELECT * FROM (my_table AS a CROSS JOIN my_table) AS b;
|
Right
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Star , Nothing ) ]
, qeFrom =
[ TRAlias
(TRParens
(TRJoin
(TRAlias
(TRSimple [ Name Nothing "my_table" ])
(Alias (Name Nothing "a") Nothing))
False
JCross
(TRSimple [ Name Nothing "my_table" ])
Nothing))
(Alias (Name Nothing "b") Nothing)
]
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
SELECT * FROM getfoo(1) AS t1;
|
Right
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Star , Nothing ) ]
, qeFrom =
[ TRAlias
(TRFunction [ Name Nothing "getfoo" ] [ NumLit "1" ])
(Alias (Name Nothing "t1") Nothing)
]
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
SELECT * FROM foo
WHERE foosubid IN (
SELECT foosubid
FROM getfoo(foo.fooid) z
WHERE z.fooid = foo.fooid
);
|
Right
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Star , Nothing ) ]
, qeFrom = [ TRSimple [ Name Nothing "foo" ] ]
, qeWhere =
Just
(In
True
(Iden [ Name Nothing "foosubid" ])
(InQueryExpr
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Iden [ Name Nothing "foosubid" ] , Nothing ) ]
, qeFrom =
[ TRAlias
(TRFunction
[ Name Nothing "getfoo" ]
[ Iden [ Name Nothing "foo" , Name Nothing "fooid" ] ])
(Alias (Name Nothing "z") Nothing)
]
, qeWhere =
Just
(BinOp
(Iden [ Name Nothing "z" , Name Nothing "fooid" ])
[ Name Nothing "=" ]
(Iden [ Name Nothing "foo" , Name Nothing "fooid" ]))
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}))
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
SELECT * FROM foo, LATERAL (SELECT * FROM bar WHERE bar.id = foo.bar_id) ss;
|
Right
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Star , Nothing ) ]
, qeFrom =
[ TRSimple [ Name Nothing "foo" ]
, TRLateral
(TRAlias
(TRQueryExpr
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Star , Nothing ) ]
, qeFrom = [ TRSimple [ Name Nothing "bar" ] ]
, qeWhere =
Just
(BinOp
(Iden [ Name Nothing "bar" , Name Nothing "id" ])
[ Name Nothing "=" ]
(Iden [ Name Nothing "foo" , Name Nothing "bar_id" ]))
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
})
(Alias (Name Nothing "ss") Nothing))
]
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
SELECT * FROM foo, bar WHERE bar.id = foo.bar_id;
|
Right
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Star , Nothing ) ]
, qeFrom =
[ TRSimple [ Name Nothing "foo" ]
, TRSimple [ Name Nothing "bar" ]
]
, qeWhere =
Just
(BinOp
(Iden [ Name Nothing "bar" , Name Nothing "id" ])
[ Name Nothing "=" ]
(Iden [ Name Nothing "foo" , Name Nothing "bar_id" ]))
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
SELECT m.name
FROM manufacturers m LEFT JOIN LATERAL get_product_names(m.id) pname ON true
WHERE pname IS NULL;
|
Right
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( Iden [ Name Nothing "m" , Name Nothing "name" ] , Nothing ) ]
, qeFrom =
[ TRJoin
(TRAlias
(TRSimple [ Name Nothing "manufacturers" ])
(Alias (Name Nothing "m") Nothing))
False
JLeft
(TRLateral
(TRAlias
(TRFunction
[ Name Nothing "get_product_names" ]
[ Iden [ Name Nothing "m" , Name Nothing "id" ] ])
(Alias (Name Nothing "pname") Nothing)))
(Just (JoinOn (Iden [ Name Nothing "true" ])))
]
, qeWhere =
Just
(PostfixOp
[ Name Nothing "is null" ] (Iden [ Name Nothing "pname" ]))
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
SELECT * FROM fdt WHERE c1 > 5
|
Right
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Star , Nothing ) ]
, qeFrom = [ TRSimple [ Name Nothing "fdt" ] ]
, qeWhere =
Just
(BinOp
(Iden [ Name Nothing "c1" ]) [ Name Nothing ">" ] (NumLit "5"))
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
SELECT * FROM fdt WHERE c1 IN (1, 2, 3)
|
Right
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Star , Nothing ) ]
, qeFrom = [ TRSimple [ Name Nothing "fdt" ] ]
, qeWhere =
Just
(In
True
(Iden [ Name Nothing "c1" ])
(InList [ NumLit "1" , NumLit "2" , NumLit "3" ]))
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
SELECT * FROM fdt WHERE c1 IN (SELECT c1 FROM t2)
|
Right
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Star , Nothing ) ]
, qeFrom = [ TRSimple [ Name Nothing "fdt" ] ]
, qeWhere =
Just
(In
True
(Iden [ Name Nothing "c1" ])
(InQueryExpr
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Iden [ Name Nothing "c1" ] , Nothing ) ]
, qeFrom = [ TRSimple [ Name Nothing "t2" ] ]
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}))
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
SELECT * FROM fdt WHERE c1 IN (SELECT c3 FROM t2 WHERE c2 = fdt.c1 + 10)
|
Right
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Star , Nothing ) ]
, qeFrom = [ TRSimple [ Name Nothing "fdt" ] ]
, qeWhere =
Just
(In
True
(Iden [ Name Nothing "c1" ])
(InQueryExpr
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Iden [ Name Nothing "c3" ] , Nothing ) ]
, qeFrom = [ TRSimple [ Name Nothing "t2" ] ]
, qeWhere =
Just
(BinOp
(Iden [ Name Nothing "c2" ])
[ Name Nothing "=" ]
(BinOp
(Iden [ Name Nothing "fdt" , Name Nothing "c1" ])
[ Name Nothing "+" ]
(NumLit "10")))
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}))
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
SELECT * FROM fdt WHERE c1 BETWEEN
(SELECT c3 FROM t2 WHERE c2 = fdt.c1 + 10) AND 100
|
Right
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Star , Nothing ) ]
, qeFrom = [ TRSimple [ Name Nothing "fdt" ] ]
, qeWhere =
Just
(SpecialOp
[ Name Nothing "between" ]
[ Iden [ Name Nothing "c1" ]
, SubQueryExpr
SqSq
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Iden [ Name Nothing "c3" ] , Nothing ) ]
, qeFrom = [ TRSimple [ Name Nothing "t2" ] ]
, qeWhere =
Just
(BinOp
(Iden [ Name Nothing "c2" ])
[ Name Nothing "=" ]
(BinOp
(Iden [ Name Nothing "fdt" , Name Nothing "c1" ])
[ Name Nothing "+" ]
(NumLit "10")))
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
, NumLit "100"
])
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
SELECT * FROM fdt WHERE EXISTS (SELECT c1 FROM t2 WHERE c2 > fdt.c1)
|
Right
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Star , Nothing ) ]
, qeFrom = [ TRSimple [ Name Nothing "fdt" ] ]
, qeWhere =
Just
(SubQueryExpr
SqExists
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Iden [ Name Nothing "c1" ] , Nothing ) ]
, qeFrom = [ TRSimple [ Name Nothing "t2" ] ]
, qeWhere =
Just
(BinOp
(Iden [ Name Nothing "c2" ])
[ Name Nothing ">" ]
(Iden [ Name Nothing "fdt" , Name Nothing "c1" ]))
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
})
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
|
Right
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Star , Nothing ) ]
, qeFrom = [ TRSimple [ Name Nothing "test1" ] ]
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
SELECT x FROM test1 GROUP BY x;
|
Right
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Iden [ Name Nothing "x" ] , Nothing ) ]
, qeFrom = [ TRSimple [ Name Nothing "test1" ] ]
, qeWhere = Nothing
, qeGroupBy = [ SimpleGroup (Iden [ Name Nothing "x" ]) ]
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
SELECT x, sum(y) FROM test1 GROUP BY x;
|
Right
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( Iden [ Name Nothing "x" ] , Nothing )
, ( App [ Name Nothing "sum" ] [ Iden [ Name Nothing "y" ] ]
, Nothing
)
]
, qeFrom = [ TRSimple [ Name Nothing "test1" ] ]
, qeWhere = Nothing
, qeGroupBy = [ SimpleGroup (Iden [ Name Nothing "x" ]) ]
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
SELECT product_id, p.name, (sum(s.units) * p.price) AS sales
FROM products p LEFT JOIN sales s USING (product_id)
GROUP BY product_id, p.name, p.price;
|
Right
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( Iden [ Name Nothing "product_id" ] , Nothing )
, ( Iden [ Name Nothing "p" , Name Nothing "name" ] , Nothing )
, ( Parens
(BinOp
(App
[ Name Nothing "sum" ]
[ Iden [ Name Nothing "s" , Name Nothing "units" ] ])
[ Name Nothing "*" ]
(Iden [ Name Nothing "p" , Name Nothing "price" ]))
, Just (Name Nothing "sales")
)
]
, qeFrom =
[ TRJoin
(TRAlias
(TRSimple [ Name Nothing "products" ])
(Alias (Name Nothing "p") Nothing))
False
JLeft
(TRAlias
(TRSimple [ Name Nothing "sales" ])
(Alias (Name Nothing "s") Nothing))
(Just (JoinUsing [ Name Nothing "product_id" ]))
]
, qeWhere = Nothing
, qeGroupBy =
[ SimpleGroup (Iden [ Name Nothing "product_id" ])
, SimpleGroup (Iden [ Name Nothing "p" , Name Nothing "name" ])
, SimpleGroup (Iden [ Name Nothing "p" , Name Nothing "price" ])
]
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
SELECT x, sum(y) FROM test1 GROUP BY x HAVING sum(y) > 3;
|
Right
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( Iden [ Name Nothing "x" ] , Nothing )
, ( App [ Name Nothing "sum" ] [ Iden [ Name Nothing "y" ] ]
, Nothing
)
]
, qeFrom = [ TRSimple [ Name Nothing "test1" ] ]
, qeWhere = Nothing
, qeGroupBy = [ SimpleGroup (Iden [ Name Nothing "x" ]) ]
, qeHaving =
Just
(BinOp
(App [ Name Nothing "sum" ] [ Iden [ Name Nothing "y" ] ])
[ Name Nothing ">" ]
(NumLit "3"))
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
SELECT x, sum(y) FROM test1 GROUP BY x HAVING x < 'c';
|
Right
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( Iden [ Name Nothing "x" ] , Nothing )
, ( App [ Name Nothing "sum" ] [ Iden [ Name Nothing "y" ] ]
, Nothing
)
]
, qeFrom = [ TRSimple [ Name Nothing "test1" ] ]
, qeWhere = Nothing
, qeGroupBy = [ SimpleGroup (Iden [ Name Nothing "x" ]) ]
, qeHaving =
Just
(BinOp
(Iden [ Name Nothing "x" ])
[ Name Nothing "<" ]
(StringLit "'" "'" "c"))
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
SELECT product_id, p.name, (sum(s.units) * (p.price - p.cost)) AS profit
FROM products p LEFT JOIN sales s USING (product_id)
WHERE s.datex > CURRENT_DATE - INTERVAL '4 weeks'
GROUP BY product_id, p.name, p.price, p.cost
HAVING sum(p.price * s.units) > 5000;
|
Right
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( Iden [ Name Nothing "product_id" ] , Nothing )
, ( Iden [ Name Nothing "p" , Name Nothing "name" ] , Nothing )
, ( Parens
(BinOp
(App
[ Name Nothing "sum" ]
[ Iden [ Name Nothing "s" , Name Nothing "units" ] ])
[ Name Nothing "*" ]
(Parens
(BinOp
(Iden [ Name Nothing "p" , Name Nothing "price" ])
[ Name Nothing "-" ]
(Iden [ Name Nothing "p" , Name Nothing "cost" ]))))
, Just (Name Nothing "profit")
)
]
, qeFrom =
[ TRJoin
(TRAlias
(TRSimple [ Name Nothing "products" ])
(Alias (Name Nothing "p") Nothing))
False
JLeft
(TRAlias
(TRSimple [ Name Nothing "sales" ])
(Alias (Name Nothing "s") Nothing))
(Just (JoinUsing [ Name Nothing "product_id" ]))
]
, qeWhere =
Just
(BinOp
(Iden [ Name Nothing "s" , Name Nothing "datex" ])
[ Name Nothing ">" ]
(BinOp
(Iden [ Name Nothing "CURRENT_DATE" ])
[ Name Nothing "-" ]
(TypedLit (TypeName [ Name Nothing "interval" ]) "4 weeks")))
, qeGroupBy =
[ SimpleGroup (Iden [ Name Nothing "product_id" ])
, SimpleGroup (Iden [ Name Nothing "p" , Name Nothing "name" ])
, SimpleGroup (Iden [ Name Nothing "p" , Name Nothing "price" ])
, SimpleGroup (Iden [ Name Nothing "p" , Name Nothing "cost" ])
]
, qeHaving =
Just
(BinOp
(App
[ Name Nothing "sum" ]
[ BinOp
(Iden [ Name Nothing "p" , Name Nothing "price" ])
[ Name Nothing "*" ]
(Iden [ Name Nothing "s" , Name Nothing "units" ])
])
[ Name Nothing ">" ]
(NumLit "5000"))
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
|
Right
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( Iden [ Name Nothing "a" ] , Nothing )
, ( Iden [ Name Nothing "b" ] , Nothing )
, ( Iden [ Name Nothing "c" ] , Nothing )
]
, qeFrom = [ TRSimple [ Name Nothing "t" ] ]
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
SELECT tbl1.a, tbl2.a, tbl1.b FROM t
|
Right
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( Iden [ Name Nothing "tbl1" , Name Nothing "a" ] , Nothing )
, ( Iden [ Name Nothing "tbl2" , Name Nothing "a" ] , Nothing )
, ( Iden [ Name Nothing "tbl1" , Name Nothing "b" ] , Nothing )
]
, qeFrom = [ TRSimple [ Name Nothing "t" ] ]
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
SELECT tbl1.*, tbl2.a FROM t
|
Right
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( BinOp (Iden [ Name Nothing "tbl1" ]) [ Name Nothing "." ] Star
, Nothing
)
, ( Iden [ Name Nothing "tbl2" , Name Nothing "a" ] , Nothing )
]
, qeFrom = [ TRSimple [ Name Nothing "t" ] ]
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
SELECT a AS value, b + c AS sum FROM t
|
Right
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( Iden [ Name Nothing "a" ] , Just (Name Nothing "value") )
, ( BinOp
(Iden [ Name Nothing "b" ])
[ Name Nothing "+" ]
(Iden [ Name Nothing "c" ])
, Just (Name Nothing "sum")
)
]
, qeFrom = [ TRSimple [ Name Nothing "t" ] ]
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
SELECT a "value", b + c AS sum FROM t
|
Right
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( Iden [ Name Nothing "a" ]
, Just (Name (Just ( "\"" , "\"" )) "value")
)
, ( BinOp
(Iden [ Name Nothing "b" ])
[ Name Nothing "+" ]
(Iden [ Name Nothing "c" ])
, Just (Name Nothing "sum")
)
]
, qeFrom = [ TRSimple [ Name Nothing "t" ] ]
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
SELECT DISTINCT select_list t
|
Right
Select
{ qeSetQuantifier = Distinct
, qeSelectList =
[ ( Iden [ Name Nothing "select_list" ] , Just (Name Nothing "t") )
]
, qeFrom = []
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
VALUES (1, 'one'), (2, 'two'), (3, 'three');
|
Right
(Values
[ [ NumLit "1" , StringLit "'" "'" "one" ]
, [ NumLit "2" , StringLit "'" "'" "two" ]
, [ NumLit "3" , StringLit "'" "'" "three" ]
])
|
SELECT 1 AS column1, 'one' AS column2
UNION ALL
SELECT 2, 'two'
UNION ALL
SELECT 3, 'three';
|
Right
QueryExprSetOp
{ qe0 =
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( NumLit "1" , Just (Name Nothing "column1") )
, ( StringLit "'" "'" "one" , Just (Name Nothing "column2") )
]
, qeFrom = []
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
, qeCombOp = Union
, qeSetQuantifier = All
, qeCorresponding = Respectively
, qe1 =
QueryExprSetOp
{ qe0 =
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( NumLit "2" , Nothing )
, ( StringLit "'" "'" "two" , Nothing )
]
, qeFrom = []
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
, qeCombOp = Union
, qeSetQuantifier = All
, qeCorresponding = Respectively
, qe1 =
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( NumLit "3" , Nothing )
, ( StringLit "'" "'" "three" , Nothing )
]
, qeFrom = []
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
}
}
|
SELECT * FROM (VALUES (1, 'one'), (2, 'two'), (3, 'three')) AS t (num,letter);
|
Right
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Star , Nothing ) ]
, qeFrom =
[ TRAlias
(TRQueryExpr
(Values
[ [ NumLit "1" , StringLit "'" "'" "one" ]
, [ NumLit "2" , StringLit "'" "'" "two" ]
, [ NumLit "3" , StringLit "'" "'" "three" ]
]))
(Alias
(Name Nothing "t")
(Just [ Name Nothing "num" , Name Nothing "letter" ]))
]
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
WITH regional_sales AS (
SELECT region, SUM(amount) AS total_sales
FROM orders
GROUP BY region
), top_regions AS (
SELECT region
FROM regional_sales
WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
)
SELECT region,
product,
SUM(quantity) AS product_units,
SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;
|
Right
With
{ qeWithRecursive = False
, qeViews =
[ ( Alias (Name Nothing "regional_sales") Nothing
, Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( Iden [ Name Nothing "region" ] , Nothing )
, ( App [ Name Nothing "SUM" ] [ Iden [ Name Nothing "amount" ] ]
, Just (Name Nothing "total_sales")
)
]
, qeFrom = [ TRSimple [ Name Nothing "orders" ] ]
, qeWhere = Nothing
, qeGroupBy = [ SimpleGroup (Iden [ Name Nothing "region" ]) ]
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
)
, ( Alias (Name Nothing "top_regions") Nothing
, Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Iden [ Name Nothing "region" ] , Nothing ) ]
, qeFrom = [ TRSimple [ Name Nothing "regional_sales" ] ]
, qeWhere =
Just
(BinOp
(Iden [ Name Nothing "total_sales" ])
[ Name Nothing ">" ]
(SubQueryExpr
SqSq
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( BinOp
(App
[ Name Nothing "SUM" ]
[ Iden [ Name Nothing "total_sales" ] ])
[ Name Nothing "/" ]
(NumLit "10")
, Nothing
)
]
, qeFrom = [ TRSimple [ Name Nothing "regional_sales" ] ]
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}))
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
)
]
, qeQueryExpression =
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( Iden [ Name Nothing "region" ] , Nothing )
, ( Iden [ Name Nothing "product" ] , Nothing )
, ( App [ Name Nothing "SUM" ] [ Iden [ Name Nothing "quantity" ] ]
, Just (Name Nothing "product_units")
)
, ( App [ Name Nothing "SUM" ] [ Iden [ Name Nothing "amount" ] ]
, Just (Name Nothing "product_sales")
)
]
, qeFrom = [ TRSimple [ Name Nothing "orders" ] ]
, qeWhere =
Just
(In
True
(Iden [ Name Nothing "region" ])
(InQueryExpr
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Iden [ Name Nothing "region" ] , Nothing ) ]
, qeFrom = [ TRSimple [ Name Nothing "top_regions" ] ]
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}))
, qeGroupBy =
[ SimpleGroup (Iden [ Name Nothing "region" ])
, SimpleGroup (Iden [ Name Nothing "product" ])
]
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
}
|
WITH RECURSIVE t(n) AS (
VALUES (1)
UNION ALL
SELECT n+1 FROM t WHERE n < 100
)
SELECT sum(n) FROM t
|
Right
With
{ qeWithRecursive = True
, qeViews =
[ ( Alias (Name Nothing "t") (Just [ Name Nothing "n" ])
, QueryExprSetOp
{ qe0 = Values [ [ NumLit "1" ] ]
, qeCombOp = Union
, qeSetQuantifier = All
, qeCorresponding = Respectively
, qe1 =
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( BinOp
(Iden [ Name Nothing "n" ]) [ Name Nothing "+" ] (NumLit "1")
, Nothing
)
]
, qeFrom = [ TRSimple [ Name Nothing "t" ] ]
, qeWhere =
Just
(BinOp
(Iden [ Name Nothing "n" ]) [ Name Nothing "<" ] (NumLit "100"))
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
}
)
]
, qeQueryExpression =
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( App [ Name Nothing "sum" ] [ Iden [ Name Nothing "n" ] ]
, Nothing
)
]
, qeFrom = [ TRSimple [ Name Nothing "t" ] ]
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
}
|
WITH RECURSIVE included_parts(sub_part, part, quantity) AS (
SELECT sub_part, part, quantity FROM parts WHERE part = 'our_product'
UNION ALL
SELECT p.sub_part, p.part, p.quantity
FROM included_parts pr, parts p
WHERE p.part = pr.sub_part
)
SELECT sub_part, SUM(quantity) as total_quantity
FROM included_parts
GROUP BY sub_part
|
Right
With
{ qeWithRecursive = True
, qeViews =
[ ( Alias
(Name Nothing "included_parts")
(Just
[ Name Nothing "sub_part"
, Name Nothing "part"
, Name Nothing "quantity"
])
, QueryExprSetOp
{ qe0 =
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( Iden [ Name Nothing "sub_part" ] , Nothing )
, ( Iden [ Name Nothing "part" ] , Nothing )
, ( Iden [ Name Nothing "quantity" ] , Nothing )
]
, qeFrom = [ TRSimple [ Name Nothing "parts" ] ]
, qeWhere =
Just
(BinOp
(Iden [ Name Nothing "part" ])
[ Name Nothing "=" ]
(StringLit "'" "'" "our_product"))
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
, qeCombOp = Union
, qeSetQuantifier = All
, qeCorresponding = Respectively
, qe1 =
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( Iden [ Name Nothing "p" , Name Nothing "sub_part" ] , Nothing )
, ( Iden [ Name Nothing "p" , Name Nothing "part" ] , Nothing )
, ( Iden [ Name Nothing "p" , Name Nothing "quantity" ] , Nothing )
]
, qeFrom =
[ TRAlias
(TRSimple [ Name Nothing "included_parts" ])
(Alias (Name Nothing "pr") Nothing)
, TRAlias
(TRSimple [ Name Nothing "parts" ])
(Alias (Name Nothing "p") Nothing)
]
, qeWhere =
Just
(BinOp
(Iden [ Name Nothing "p" , Name Nothing "part" ])
[ Name Nothing "=" ]
(Iden [ Name Nothing "pr" , Name Nothing "sub_part" ]))
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
}
)
]
, qeQueryExpression =
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( Iden [ Name Nothing "sub_part" ] , Nothing )
, ( App [ Name Nothing "SUM" ] [ Iden [ Name Nothing "quantity" ] ]
, Just (Name Nothing "total_quantity")
)
]
, qeFrom = [ TRSimple [ Name Nothing "included_parts" ] ]
, qeWhere = Nothing
, qeGroupBy = [ SimpleGroup (Iden [ Name Nothing "sub_part" ]) ]
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
}
|
WITH RECURSIVE search_graph(id, link, data, depth) AS (
SELECT g.id, g.link, g.data, 1
FROM graph g
UNION ALL
SELECT g.id, g.link, g.data, sg.depth + 1
FROM graph g, search_graph sg
WHERE g.id = sg.link
)
SELECT * FROM search_graph;
|
Right
With
{ qeWithRecursive = True
, qeViews =
[ ( Alias
(Name Nothing "search_graph")
(Just
[ Name Nothing "id"
, Name Nothing "link"
, Name Nothing "data"
, Name Nothing "depth"
])
, QueryExprSetOp
{ qe0 =
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( Iden [ Name Nothing "g" , Name Nothing "id" ] , Nothing )
, ( Iden [ Name Nothing "g" , Name Nothing "link" ] , Nothing )
, ( Iden [ Name Nothing "g" , Name Nothing "data" ] , Nothing )
, ( NumLit "1" , Nothing )
]
, qeFrom =
[ TRAlias
(TRSimple [ Name Nothing "graph" ])
(Alias (Name Nothing "g") Nothing)
]
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
, qeCombOp = Union
, qeSetQuantifier = All
, qeCorresponding = Respectively
, qe1 =
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( Iden [ Name Nothing "g" , Name Nothing "id" ] , Nothing )
, ( Iden [ Name Nothing "g" , Name Nothing "link" ] , Nothing )
, ( Iden [ Name Nothing "g" , Name Nothing "data" ] , Nothing )
, ( BinOp
(Iden [ Name Nothing "sg" , Name Nothing "depth" ])
[ Name Nothing "+" ]
(NumLit "1")
, Nothing
)
]
, qeFrom =
[ TRAlias
(TRSimple [ Name Nothing "graph" ])
(Alias (Name Nothing "g") Nothing)
, TRAlias
(TRSimple [ Name Nothing "search_graph" ])
(Alias (Name Nothing "sg") Nothing)
]
, qeWhere =
Just
(BinOp
(Iden [ Name Nothing "g" , Name Nothing "id" ])
[ Name Nothing "=" ]
(Iden [ Name Nothing "sg" , Name Nothing "link" ]))
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
}
)
]
, qeQueryExpression =
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Star , Nothing ) ]
, qeFrom = [ TRSimple [ Name Nothing "search_graph" ] ]
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
}
|
WITH RECURSIVE t(n) AS (
SELECT 1
UNION ALL
SELECT n+1 FROM t
)
SELECT n FROM t --LIMIT 100;
|
Right
With
{ qeWithRecursive = True
, qeViews =
[ ( Alias (Name Nothing "t") (Just [ Name Nothing "n" ])
, QueryExprSetOp
{ qe0 =
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( NumLit "1" , Nothing ) ]
, qeFrom = []
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
, qeCombOp = Union
, qeSetQuantifier = All
, qeCorresponding = Respectively
, qe1 =
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( BinOp
(Iden [ Name Nothing "n" ]) [ Name Nothing "+" ] (NumLit "1")
, Nothing
)
]
, qeFrom = [ TRSimple [ Name Nothing "t" ] ]
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
}
)
]
, qeQueryExpression =
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Iden [ Name Nothing "n" ] , Nothing ) ]
, qeFrom = [ TRSimple [ Name Nothing "t" ] ]
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
}
|
SELECT f.title, f.did, d.name, f.date_prod, f.kind
FROM distributors d, films f
WHERE f.did = d.did
|
Right
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( Iden [ Name Nothing "f" , Name Nothing "title" ] , Nothing )
, ( Iden [ Name Nothing "f" , Name Nothing "did" ] , Nothing )
, ( Iden [ Name Nothing "d" , Name Nothing "name" ] , Nothing )
, ( Iden [ Name Nothing "f" , Name Nothing "date_prod" ]
, Nothing
)
, ( Iden [ Name Nothing "f" , Name Nothing "kind" ] , Nothing )
]
, qeFrom =
[ TRAlias
(TRSimple [ Name Nothing "distributors" ])
(Alias (Name Nothing "d") Nothing)
, TRAlias
(TRSimple [ Name Nothing "films" ])
(Alias (Name Nothing "f") Nothing)
]
, qeWhere =
Just
(BinOp
(Iden [ Name Nothing "f" , Name Nothing "did" ])
[ Name Nothing "=" ]
(Iden [ Name Nothing "d" , Name Nothing "did" ]))
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
SELECT kind, sum(len) AS total
FROM films
GROUP BY kind
HAVING sum(len) < interval '5 hours';
|
Right
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( Iden [ Name Nothing "kind" ] , Nothing )
, ( App [ Name Nothing "sum" ] [ Iden [ Name Nothing "len" ] ]
, Just (Name Nothing "total")
)
]
, qeFrom = [ TRSimple [ Name Nothing "films" ] ]
, qeWhere = Nothing
, qeGroupBy = [ SimpleGroup (Iden [ Name Nothing "kind" ]) ]
, qeHaving =
Just
(BinOp
(App [ Name Nothing "sum" ] [ Iden [ Name Nothing "len" ] ])
[ Name Nothing "<" ]
(TypedLit (TypeName [ Name Nothing "interval" ]) "5 hours"))
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
SELECT * FROM distributors ORDER BY name;
|
Right
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Star , Nothing ) ]
, qeFrom = [ TRSimple [ Name Nothing "distributors" ] ]
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy =
[ SortSpec
(Iden [ Name Nothing "name" ]) DirDefault NullsOrderDefault
]
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
SELECT * FROM distributors ORDER BY 2;
|
Right
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Star , Nothing ) ]
, qeFrom = [ TRSimple [ Name Nothing "distributors" ] ]
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy =
[ SortSpec (NumLit "2") DirDefault NullsOrderDefault ]
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
SELECT distributors.name
FROM distributors
WHERE distributors.name LIKE 'W%'
UNION
SELECT actors.name
FROM actors
WHERE actors.name LIKE 'W%';
|
Right
QueryExprSetOp
{ qe0 =
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( Iden [ Name Nothing "distributors" , Name Nothing "name" ]
, Nothing
)
]
, qeFrom = [ TRSimple [ Name Nothing "distributors" ] ]
, qeWhere =
Just
(BinOp
(Iden [ Name Nothing "distributors" , Name Nothing "name" ])
[ Name Nothing "like" ]
(StringLit "'" "'" "W%"))
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
, qeCombOp = Union
, qeSetQuantifier = SQDefault
, qeCorresponding = Respectively
, qe1 =
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( Iden [ Name Nothing "actors" , Name Nothing "name" ]
, Nothing
)
]
, qeFrom = [ TRSimple [ Name Nothing "actors" ] ]
, qeWhere =
Just
(BinOp
(Iden [ Name Nothing "actors" , Name Nothing "name" ])
[ Name Nothing "like" ]
(StringLit "'" "'" "W%"))
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
}
|
WITH t AS (
SELECT random() as x FROM generate_series(1, 3)
)
SELECT * FROM t
UNION ALL
SELECT * FROM t
|
Right
With
{ qeWithRecursive = False
, qeViews =
[ ( Alias (Name Nothing "t") Nothing
, Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( App [ Name Nothing "random" ] [] , Just (Name Nothing "x") ) ]
, qeFrom =
[ TRFunction
[ Name Nothing "generate_series" ] [ NumLit "1" , NumLit "3" ]
]
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
)
]
, qeQueryExpression =
QueryExprSetOp
{ qe0 =
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Star , Nothing ) ]
, qeFrom = [ TRSimple [ Name Nothing "t" ] ]
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
, qeCombOp = Union
, qeSetQuantifier = All
, qeCorresponding = Respectively
, qe1 =
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Star , Nothing ) ]
, qeFrom = [ TRSimple [ Name Nothing "t" ] ]
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
}
}
|
WITH RECURSIVE employee_recursive(distance, employee_name, manager_name) AS (
SELECT 1, employee_name, manager_name
FROM employee
WHERE manager_name = 'Mary'
UNION ALL
SELECT er.distance + 1, e.employee_name, e.manager_name
FROM employee_recursive er, employee e
WHERE er.employee_name = e.manager_name
)
SELECT distance, employee_name FROM employee_recursive;
|
Right
With
{ qeWithRecursive = True
, qeViews =
[ ( Alias
(Name Nothing "employee_recursive")
(Just
[ Name Nothing "distance"
, Name Nothing "employee_name"
, Name Nothing "manager_name"
])
, QueryExprSetOp
{ qe0 =
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( NumLit "1" , Nothing )
, ( Iden [ Name Nothing "employee_name" ] , Nothing )
, ( Iden [ Name Nothing "manager_name" ] , Nothing )
]
, qeFrom = [ TRSimple [ Name Nothing "employee" ] ]
, qeWhere =
Just
(BinOp
(Iden [ Name Nothing "manager_name" ])
[ Name Nothing "=" ]
(StringLit "'" "'" "Mary"))
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
, qeCombOp = Union
, qeSetQuantifier = All
, qeCorresponding = Respectively
, qe1 =
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( BinOp
(Iden [ Name Nothing "er" , Name Nothing "distance" ])
[ Name Nothing "+" ]
(NumLit "1")
, Nothing
)
, ( Iden [ Name Nothing "e" , Name Nothing "employee_name" ]
, Nothing
)
, ( Iden [ Name Nothing "e" , Name Nothing "manager_name" ]
, Nothing
)
]
, qeFrom =
[ TRAlias
(TRSimple [ Name Nothing "employee_recursive" ])
(Alias (Name Nothing "er") Nothing)
, TRAlias
(TRSimple [ Name Nothing "employee" ])
(Alias (Name Nothing "e") Nothing)
]
, qeWhere =
Just
(BinOp
(Iden [ Name Nothing "er" , Name Nothing "employee_name" ])
[ Name Nothing "=" ]
(Iden [ Name Nothing "e" , Name Nothing "manager_name" ]))
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
}
)
]
, qeQueryExpression =
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( Iden [ Name Nothing "distance" ] , Nothing )
, ( Iden [ Name Nothing "employee_name" ] , Nothing )
]
, qeFrom = [ TRSimple [ Name Nothing "employee_recursive" ] ]
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
}
|
SELECT m.name AS mname, pname
FROM manufacturers m, LATERAL get_product_names(m.id) pname;
|
Right
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( Iden [ Name Nothing "m" , Name Nothing "name" ]
, Just (Name Nothing "mname")
)
, ( Iden [ Name Nothing "pname" ] , Nothing )
]
, qeFrom =
[ TRAlias
(TRSimple [ Name Nothing "manufacturers" ])
(Alias (Name Nothing "m") Nothing)
, TRLateral
(TRAlias
(TRFunction
[ Name Nothing "get_product_names" ]
[ Iden [ Name Nothing "m" , Name Nothing "id" ] ])
(Alias (Name Nothing "pname") Nothing))
]
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
SELECT m.name AS mname, pname
FROM manufacturers m LEFT JOIN LATERAL get_product_names(m.id) pname ON true;
|
Right
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( Iden [ Name Nothing "m" , Name Nothing "name" ]
, Just (Name Nothing "mname")
)
, ( Iden [ Name Nothing "pname" ] , Nothing )
]
, qeFrom =
[ TRJoin
(TRAlias
(TRSimple [ Name Nothing "manufacturers" ])
(Alias (Name Nothing "m") Nothing))
False
JLeft
(TRLateral
(TRAlias
(TRFunction
[ Name Nothing "get_product_names" ]
[ Iden [ Name Nothing "m" , Name Nothing "id" ] ])
(Alias (Name Nothing "pname") Nothing)))
(Just (JoinOn (Iden [ Name Nothing "true" ])))
]
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
|
Right
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( BinOp (NumLit "2") [ Name Nothing "+" ] (NumLit "2")
, Nothing
)
]
, qeFrom = []
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|