|
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Iden [ Name Nothing "a" ] , Nothing ) ]
, qeFrom = [ TRSimple [ Name Nothing "t" ] ]
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
|
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Iden [ Name Nothing "a" ] , Nothing ) ]
, qeFrom =
[ TRFunction [ Name Nothing "f" ] [ Iden [ Name Nothing "a" ] ] ]
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
|
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Iden [ Name Nothing "a" ] , Nothing ) ]
, qeFrom =
[ TRSimple [ Name Nothing "t" ] , TRSimple [ Name Nothing "u" ] ]
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
|
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Iden [ Name Nothing "a" ] , Nothing ) ]
, qeFrom = [ TRSimple [ Name Nothing "s" , Name Nothing "t" ] ]
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
|
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Iden [ Name Nothing "a" ] , Nothing ) ]
, qeFrom = [ TRLateral (TRSimple [ Name Nothing "a" ]) ]
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
select a from lateral a,b
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Iden [ Name Nothing "a" ] , Nothing ) ]
, qeFrom =
[ TRLateral (TRSimple [ Name Nothing "a" ])
, TRSimple [ Name Nothing "b" ]
]
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
select a from a, lateral b
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Iden [ Name Nothing "a" ] , Nothing ) ]
, qeFrom =
[ TRSimple [ Name Nothing "a" ]
, TRLateral (TRSimple [ Name Nothing "b" ])
]
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
select a from a natural join lateral b
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Iden [ Name Nothing "a" ] , Nothing ) ]
, qeFrom =
[ TRJoin
(TRSimple [ Name Nothing "a" ])
True
JInner
(TRLateral (TRSimple [ Name Nothing "b" ]))
Nothing
]
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
select a from lateral a natural join lateral b
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Iden [ Name Nothing "a" ] , Nothing ) ]
, qeFrom =
[ TRJoin
(TRLateral (TRSimple [ Name Nothing "a" ]))
True
JInner
(TRLateral (TRSimple [ Name Nothing "b" ]))
Nothing
]
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
select a from t inner join u on expr
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Iden [ Name Nothing "a" ] , Nothing ) ]
, qeFrom =
[ TRJoin
(TRSimple [ Name Nothing "t" ])
False
JInner
(TRSimple [ Name Nothing "u" ])
(Just (JoinOn (Iden [ Name Nothing "expr" ])))
]
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
select a from t join u on expr
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Iden [ Name Nothing "a" ] , Nothing ) ]
, qeFrom =
[ TRJoin
(TRSimple [ Name Nothing "t" ])
False
JInner
(TRSimple [ Name Nothing "u" ])
(Just (JoinOn (Iden [ Name Nothing "expr" ])))
]
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
select a from t left join u on expr
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Iden [ Name Nothing "a" ] , Nothing ) ]
, qeFrom =
[ TRJoin
(TRSimple [ Name Nothing "t" ])
False
JLeft
(TRSimple [ Name Nothing "u" ])
(Just (JoinOn (Iden [ Name Nothing "expr" ])))
]
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
select a from t right join u on expr
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Iden [ Name Nothing "a" ] , Nothing ) ]
, qeFrom =
[ TRJoin
(TRSimple [ Name Nothing "t" ])
False
JRight
(TRSimple [ Name Nothing "u" ])
(Just (JoinOn (Iden [ Name Nothing "expr" ])))
]
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
select a from t full join u on expr
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Iden [ Name Nothing "a" ] , Nothing ) ]
, qeFrom =
[ TRJoin
(TRSimple [ Name Nothing "t" ])
False
JFull
(TRSimple [ Name Nothing "u" ])
(Just (JoinOn (Iden [ Name Nothing "expr" ])))
]
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
select a from t cross join u
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Iden [ Name Nothing "a" ] , Nothing ) ]
, qeFrom =
[ TRJoin
(TRSimple [ Name Nothing "t" ])
False
JCross
(TRSimple [ Name Nothing "u" ])
Nothing
]
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
select a from t natural inner join u
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Iden [ Name Nothing "a" ] , Nothing ) ]
, qeFrom =
[ TRJoin
(TRSimple [ Name Nothing "t" ])
True
JInner
(TRSimple [ Name Nothing "u" ])
Nothing
]
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
select a from t inner join u using(a,b)
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Iden [ Name Nothing "a" ] , Nothing ) ]
, qeFrom =
[ TRJoin
(TRSimple [ Name Nothing "t" ])
False
JInner
(TRSimple [ Name Nothing "u" ])
(Just (JoinUsing [ Name Nothing "a" , Name Nothing "b" ]))
]
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
select a from (select a from t)
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Iden [ Name Nothing "a" ] , Nothing ) ]
, qeFrom =
[ TRQueryExpr
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Iden [ Name Nothing "a" ] , Nothing ) ]
, qeFrom = [ TRSimple [ Name Nothing "t" ] ]
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
]
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
|
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Iden [ Name Nothing "a" ] , Nothing ) ]
, qeFrom =
[ TRAlias
(TRSimple [ Name Nothing "t" ]) (Alias (Name Nothing "u") Nothing)
]
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
|
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Iden [ Name Nothing "a" ] , Nothing ) ]
, qeFrom =
[ TRAlias
(TRSimple [ Name Nothing "t" ]) (Alias (Name Nothing "u") Nothing)
]
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
|
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Iden [ Name Nothing "a" ] , Nothing ) ]
, qeFrom =
[ TRAlias
(TRSimple [ Name Nothing "t" ])
(Alias (Name Nothing "u") (Just [ Name Nothing "b" ]))
]
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
select a from (t cross join u) as u
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Iden [ Name Nothing "a" ] , Nothing ) ]
, qeFrom =
[ TRAlias
(TRParens
(TRJoin
(TRSimple [ Name Nothing "t" ])
False
JCross
(TRSimple [ Name Nothing "u" ])
Nothing))
(Alias (Name Nothing "u") Nothing)
]
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
select a from t cross join u cross join v
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Iden [ Name Nothing "a" ] , Nothing ) ]
, qeFrom =
[ TRJoin
(TRJoin
(TRSimple [ Name Nothing "t" ])
False
JCross
(TRSimple [ Name Nothing "u" ])
Nothing)
False
JCross
(TRSimple [ Name Nothing "v" ])
Nothing
]
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
select * from t GROUP BY a
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Star , Nothing ) ]
, qeFrom = [ TRSimple [ Name Nothing "t" ] ]
, qeWhere = Nothing
, qeGroupBy = [ SimpleGroup (Iden [ Name Nothing "a" ]) ]
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
select * from t GROUP BY GROUPING SETS((a))
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Star , Nothing ) ]
, qeFrom = [ TRSimple [ Name Nothing "t" ] ]
, qeWhere = Nothing
, qeGroupBy =
[ GroupingSets
[ GroupingParens [ SimpleGroup (Iden [ Name Nothing "a" ]) ] ]
]
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
select * from t GROUP BY a,b,c
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Star , Nothing ) ]
, qeFrom = [ TRSimple [ Name Nothing "t" ] ]
, qeWhere = Nothing
, qeGroupBy =
[ SimpleGroup (Iden [ Name Nothing "a" ])
, SimpleGroup (Iden [ Name Nothing "b" ])
, SimpleGroup (Iden [ Name Nothing "c" ])
]
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
select * from t GROUP BY GROUPING SETS((a,b,c))
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Star , Nothing ) ]
, qeFrom = [ TRSimple [ Name Nothing "t" ] ]
, qeWhere = Nothing
, qeGroupBy =
[ GroupingSets
[ GroupingParens
[ SimpleGroup (Iden [ Name Nothing "a" ])
, SimpleGroup (Iden [ Name Nothing "b" ])
, SimpleGroup (Iden [ Name Nothing "c" ])
]
]
]
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
select * from t GROUP BY ROLLUP(a,b)
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Star , Nothing ) ]
, qeFrom = [ TRSimple [ Name Nothing "t" ] ]
, qeWhere = Nothing
, qeGroupBy =
[ Rollup
[ SimpleGroup (Iden [ Name Nothing "a" ])
, SimpleGroup (Iden [ Name Nothing "b" ])
]
]
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
select * from t GROUP BY GROUPING SETS((a,b),
(a),
() )
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Star , Nothing ) ]
, qeFrom = [ TRSimple [ Name Nothing "t" ] ]
, qeWhere = Nothing
, qeGroupBy =
[ GroupingSets
[ GroupingParens
[ SimpleGroup (Iden [ Name Nothing "a" ])
, SimpleGroup (Iden [ Name Nothing "b" ])
]
, GroupingParens [ SimpleGroup (Iden [ Name Nothing "a" ]) ]
, GroupingParens []
]
]
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
select * from t GROUP BY ROLLUP(b,a)
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Star , Nothing ) ]
, qeFrom = [ TRSimple [ Name Nothing "t" ] ]
, qeWhere = Nothing
, qeGroupBy =
[ Rollup
[ SimpleGroup (Iden [ Name Nothing "b" ])
, SimpleGroup (Iden [ Name Nothing "a" ])
]
]
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
select * from t GROUP BY GROUPING SETS((b,a),
(b),
() )
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Star , Nothing ) ]
, qeFrom = [ TRSimple [ Name Nothing "t" ] ]
, qeWhere = Nothing
, qeGroupBy =
[ GroupingSets
[ GroupingParens
[ SimpleGroup (Iden [ Name Nothing "b" ])
, SimpleGroup (Iden [ Name Nothing "a" ])
]
, GroupingParens [ SimpleGroup (Iden [ Name Nothing "b" ]) ]
, GroupingParens []
]
]
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
select * from t GROUP BY CUBE(a,b,c)
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Star , Nothing ) ]
, qeFrom = [ TRSimple [ Name Nothing "t" ] ]
, qeWhere = Nothing
, qeGroupBy =
[ Cube
[ SimpleGroup (Iden [ Name Nothing "a" ])
, SimpleGroup (Iden [ Name Nothing "b" ])
, SimpleGroup (Iden [ Name Nothing "c" ])
]
]
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
select * from t GROUP BY GROUPING SETS((a,b,c),
(a,b),
(a,c),
(b,c),
(a),
(b),
(c),
() )
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Star , Nothing ) ]
, qeFrom = [ TRSimple [ Name Nothing "t" ] ]
, qeWhere = Nothing
, qeGroupBy =
[ GroupingSets
[ GroupingParens
[ SimpleGroup (Iden [ Name Nothing "a" ])
, SimpleGroup (Iden [ Name Nothing "b" ])
, SimpleGroup (Iden [ Name Nothing "c" ])
]
, GroupingParens
[ SimpleGroup (Iden [ Name Nothing "a" ])
, SimpleGroup (Iden [ Name Nothing "b" ])
]
, GroupingParens
[ SimpleGroup (Iden [ Name Nothing "a" ])
, SimpleGroup (Iden [ Name Nothing "c" ])
]
, GroupingParens
[ SimpleGroup (Iden [ Name Nothing "b" ])
, SimpleGroup (Iden [ Name Nothing "c" ])
]
, GroupingParens [ SimpleGroup (Iden [ Name Nothing "a" ]) ]
, GroupingParens [ SimpleGroup (Iden [ Name Nothing "b" ]) ]
, GroupingParens [ SimpleGroup (Iden [ Name Nothing "c" ]) ]
, GroupingParens []
]
]
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
select * from t GROUP BY ROLLUP(Province, County, City)
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Star , Nothing ) ]
, qeFrom = [ TRSimple [ Name Nothing "t" ] ]
, qeWhere = Nothing
, qeGroupBy =
[ Rollup
[ SimpleGroup (Iden [ Name Nothing "Province" ])
, SimpleGroup (Iden [ Name Nothing "County" ])
, SimpleGroup (Iden [ Name Nothing "City" ])
]
]
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
select * from t GROUP BY ROLLUP(Province, (County, City))
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Star , Nothing ) ]
, qeFrom = [ TRSimple [ Name Nothing "t" ] ]
, qeWhere = Nothing
, qeGroupBy =
[ Rollup
[ SimpleGroup (Iden [ Name Nothing "Province" ])
, GroupingParens
[ SimpleGroup (Iden [ Name Nothing "County" ])
, SimpleGroup (Iden [ Name Nothing "City" ])
]
]
]
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
select * from t GROUP BY ROLLUP(Province, (County, City))
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Star , Nothing ) ]
, qeFrom = [ TRSimple [ Name Nothing "t" ] ]
, qeWhere = Nothing
, qeGroupBy =
[ Rollup
[ SimpleGroup (Iden [ Name Nothing "Province" ])
, GroupingParens
[ SimpleGroup (Iden [ Name Nothing "County" ])
, SimpleGroup (Iden [ Name Nothing "City" ])
]
]
]
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
select * from t GROUP BY GROUPING SETS((Province, County, City),
(Province),
() )
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Star , Nothing ) ]
, qeFrom = [ TRSimple [ Name Nothing "t" ] ]
, qeWhere = Nothing
, qeGroupBy =
[ GroupingSets
[ GroupingParens
[ SimpleGroup (Iden [ Name Nothing "Province" ])
, SimpleGroup (Iden [ Name Nothing "County" ])
, SimpleGroup (Iden [ Name Nothing "City" ])
]
, GroupingParens [ SimpleGroup (Iden [ Name Nothing "Province" ]) ]
, GroupingParens []
]
]
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
select * from t GROUP BY GROUPING SETS((Province, County, City),
(Province, County),
(Province),
() )
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Star , Nothing ) ]
, qeFrom = [ TRSimple [ Name Nothing "t" ] ]
, qeWhere = Nothing
, qeGroupBy =
[ GroupingSets
[ GroupingParens
[ SimpleGroup (Iden [ Name Nothing "Province" ])
, SimpleGroup (Iden [ Name Nothing "County" ])
, SimpleGroup (Iden [ Name Nothing "City" ])
]
, GroupingParens
[ SimpleGroup (Iden [ Name Nothing "Province" ])
, SimpleGroup (Iden [ Name Nothing "County" ])
]
, GroupingParens [ SimpleGroup (Iden [ Name Nothing "Province" ]) ]
, GroupingParens []
]
]
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
select * from t GROUP BY a, ROLLUP(b,c)
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Star , Nothing ) ]
, qeFrom = [ TRSimple [ Name Nothing "t" ] ]
, qeWhere = Nothing
, qeGroupBy =
[ SimpleGroup (Iden [ Name Nothing "a" ])
, Rollup
[ SimpleGroup (Iden [ Name Nothing "b" ])
, SimpleGroup (Iden [ Name Nothing "c" ])
]
]
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
select * from t GROUP BY GROUPING SETS((a,b,c),
(a,b),
(a) )
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Star , Nothing ) ]
, qeFrom = [ TRSimple [ Name Nothing "t" ] ]
, qeWhere = Nothing
, qeGroupBy =
[ GroupingSets
[ GroupingParens
[ SimpleGroup (Iden [ Name Nothing "a" ])
, SimpleGroup (Iden [ Name Nothing "b" ])
, SimpleGroup (Iden [ Name Nothing "c" ])
]
, GroupingParens
[ SimpleGroup (Iden [ Name Nothing "a" ])
, SimpleGroup (Iden [ Name Nothing "b" ])
]
, GroupingParens [ SimpleGroup (Iden [ Name Nothing "a" ]) ]
]
]
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
select * from t GROUP BY a, b, ROLLUP(c,d)
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Star , Nothing ) ]
, qeFrom = [ TRSimple [ Name Nothing "t" ] ]
, qeWhere = Nothing
, qeGroupBy =
[ SimpleGroup (Iden [ Name Nothing "a" ])
, SimpleGroup (Iden [ Name Nothing "b" ])
, Rollup
[ SimpleGroup (Iden [ Name Nothing "c" ])
, SimpleGroup (Iden [ Name Nothing "d" ])
]
]
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
select * from t GROUP BY GROUPING SETS((a,b,c,d),
(a,b,c),
(a,b) )
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Star , Nothing ) ]
, qeFrom = [ TRSimple [ Name Nothing "t" ] ]
, qeWhere = Nothing
, qeGroupBy =
[ GroupingSets
[ GroupingParens
[ SimpleGroup (Iden [ Name Nothing "a" ])
, SimpleGroup (Iden [ Name Nothing "b" ])
, SimpleGroup (Iden [ Name Nothing "c" ])
, SimpleGroup (Iden [ Name Nothing "d" ])
]
, GroupingParens
[ SimpleGroup (Iden [ Name Nothing "a" ])
, SimpleGroup (Iden [ Name Nothing "b" ])
, SimpleGroup (Iden [ Name Nothing "c" ])
]
, GroupingParens
[ SimpleGroup (Iden [ Name Nothing "a" ])
, SimpleGroup (Iden [ Name Nothing "b" ])
]
]
]
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
select * from t GROUP BY ROLLUP(a), ROLLUP(b,c)
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Star , Nothing ) ]
, qeFrom = [ TRSimple [ Name Nothing "t" ] ]
, qeWhere = Nothing
, qeGroupBy =
[ Rollup [ SimpleGroup (Iden [ Name Nothing "a" ]) ]
, Rollup
[ SimpleGroup (Iden [ Name Nothing "b" ])
, SimpleGroup (Iden [ Name Nothing "c" ])
]
]
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
select * from t GROUP BY GROUPING SETS((a,b,c),
(a,b),
(a),
(b,c),
(b),
() )
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Star , Nothing ) ]
, qeFrom = [ TRSimple [ Name Nothing "t" ] ]
, qeWhere = Nothing
, qeGroupBy =
[ GroupingSets
[ GroupingParens
[ SimpleGroup (Iden [ Name Nothing "a" ])
, SimpleGroup (Iden [ Name Nothing "b" ])
, SimpleGroup (Iden [ Name Nothing "c" ])
]
, GroupingParens
[ SimpleGroup (Iden [ Name Nothing "a" ])
, SimpleGroup (Iden [ Name Nothing "b" ])
]
, GroupingParens [ SimpleGroup (Iden [ Name Nothing "a" ]) ]
, GroupingParens
[ SimpleGroup (Iden [ Name Nothing "b" ])
, SimpleGroup (Iden [ Name Nothing "c" ])
]
, GroupingParens [ SimpleGroup (Iden [ Name Nothing "b" ]) ]
, GroupingParens []
]
]
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
select * from t GROUP BY ROLLUP(a), CUBE(b,c)
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Star , Nothing ) ]
, qeFrom = [ TRSimple [ Name Nothing "t" ] ]
, qeWhere = Nothing
, qeGroupBy =
[ Rollup [ SimpleGroup (Iden [ Name Nothing "a" ]) ]
, Cube
[ SimpleGroup (Iden [ Name Nothing "b" ])
, SimpleGroup (Iden [ Name Nothing "c" ])
]
]
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
select * from t GROUP BY GROUPING SETS((a,b,c),
(a,b),
(a,c),
(a),
(b,c),
(b),
(c),
() )
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Star , Nothing ) ]
, qeFrom = [ TRSimple [ Name Nothing "t" ] ]
, qeWhere = Nothing
, qeGroupBy =
[ GroupingSets
[ GroupingParens
[ SimpleGroup (Iden [ Name Nothing "a" ])
, SimpleGroup (Iden [ Name Nothing "b" ])
, SimpleGroup (Iden [ Name Nothing "c" ])
]
, GroupingParens
[ SimpleGroup (Iden [ Name Nothing "a" ])
, SimpleGroup (Iden [ Name Nothing "b" ])
]
, GroupingParens
[ SimpleGroup (Iden [ Name Nothing "a" ])
, SimpleGroup (Iden [ Name Nothing "c" ])
]
, GroupingParens [ SimpleGroup (Iden [ Name Nothing "a" ]) ]
, GroupingParens
[ SimpleGroup (Iden [ Name Nothing "b" ])
, SimpleGroup (Iden [ Name Nothing "c" ])
]
, GroupingParens [ SimpleGroup (Iden [ Name Nothing "b" ]) ]
, GroupingParens [ SimpleGroup (Iden [ Name Nothing "c" ]) ]
, GroupingParens []
]
]
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
select * from t GROUP BY CUBE(a,b), ROLLUP(c,d)
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Star , Nothing ) ]
, qeFrom = [ TRSimple [ Name Nothing "t" ] ]
, qeWhere = Nothing
, qeGroupBy =
[ Cube
[ SimpleGroup (Iden [ Name Nothing "a" ])
, SimpleGroup (Iden [ Name Nothing "b" ])
]
, Rollup
[ SimpleGroup (Iden [ Name Nothing "c" ])
, SimpleGroup (Iden [ Name Nothing "d" ])
]
]
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
select * from t GROUP BY GROUPING SETS((a,b,c,d),
(a,b,c),
(a,b),
(a,c,d),
(a,c),
(a),
(b,c,d),
(b,c),
(b),
(c,d),
(c),
() )
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Star , Nothing ) ]
, qeFrom = [ TRSimple [ Name Nothing "t" ] ]
, qeWhere = Nothing
, qeGroupBy =
[ GroupingSets
[ GroupingParens
[ SimpleGroup (Iden [ Name Nothing "a" ])
, SimpleGroup (Iden [ Name Nothing "b" ])
, SimpleGroup (Iden [ Name Nothing "c" ])
, SimpleGroup (Iden [ Name Nothing "d" ])
]
, GroupingParens
[ SimpleGroup (Iden [ Name Nothing "a" ])
, SimpleGroup (Iden [ Name Nothing "b" ])
, SimpleGroup (Iden [ Name Nothing "c" ])
]
, GroupingParens
[ SimpleGroup (Iden [ Name Nothing "a" ])
, SimpleGroup (Iden [ Name Nothing "b" ])
]
, GroupingParens
[ SimpleGroup (Iden [ Name Nothing "a" ])
, SimpleGroup (Iden [ Name Nothing "c" ])
, SimpleGroup (Iden [ Name Nothing "d" ])
]
, GroupingParens
[ SimpleGroup (Iden [ Name Nothing "a" ])
, SimpleGroup (Iden [ Name Nothing "c" ])
]
, GroupingParens [ SimpleGroup (Iden [ Name Nothing "a" ]) ]
, GroupingParens
[ SimpleGroup (Iden [ Name Nothing "b" ])
, SimpleGroup (Iden [ Name Nothing "c" ])
, SimpleGroup (Iden [ Name Nothing "d" ])
]
, GroupingParens
[ SimpleGroup (Iden [ Name Nothing "b" ])
, SimpleGroup (Iden [ Name Nothing "c" ])
]
, GroupingParens [ SimpleGroup (Iden [ Name Nothing "b" ]) ]
, GroupingParens
[ SimpleGroup (Iden [ Name Nothing "c" ])
, SimpleGroup (Iden [ Name Nothing "d" ])
]
, GroupingParens [ SimpleGroup (Iden [ Name Nothing "c" ]) ]
, GroupingParens []
]
]
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
select * from t GROUP BY a, ROLLUP(a,b)
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Star , Nothing ) ]
, qeFrom = [ TRSimple [ Name Nothing "t" ] ]
, qeWhere = Nothing
, qeGroupBy =
[ SimpleGroup (Iden [ Name Nothing "a" ])
, Rollup
[ SimpleGroup (Iden [ Name Nothing "a" ])
, SimpleGroup (Iden [ Name Nothing "b" ])
]
]
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
select * from t GROUP BY GROUPING SETS((a,b),
(a) )
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Star , Nothing ) ]
, qeFrom = [ TRSimple [ Name Nothing "t" ] ]
, qeWhere = Nothing
, qeGroupBy =
[ GroupingSets
[ GroupingParens
[ SimpleGroup (Iden [ Name Nothing "a" ])
, SimpleGroup (Iden [ Name Nothing "b" ])
]
, GroupingParens [ SimpleGroup (Iden [ Name Nothing "a" ]) ]
]
]
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
select * from t GROUP BY Region,
ROLLUP(Sales_Person, WEEK(Sales_Date)),
CUBE(YEAR(Sales_Date), MONTH (Sales_Date))
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Star , Nothing ) ]
, qeFrom = [ TRSimple [ Name Nothing "t" ] ]
, qeWhere = Nothing
, qeGroupBy =
[ SimpleGroup (Iden [ Name Nothing "Region" ])
, Rollup
[ SimpleGroup (Iden [ Name Nothing "Sales_Person" ])
, SimpleGroup
(App
[ Name Nothing "WEEK" ] [ Iden [ Name Nothing "Sales_Date" ] ])
]
, Cube
[ SimpleGroup
(App
[ Name Nothing "YEAR" ] [ Iden [ Name Nothing "Sales_Date" ] ])
, SimpleGroup
(App
[ Name Nothing "MONTH" ] [ Iden [ Name Nothing "Sales_Date" ] ])
]
]
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
select * from t GROUP BY ROLLUP (Region, Sales_Person, WEEK(Sales_Date),
YEAR(Sales_Date), MONTH(Sales_Date) )
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Star , Nothing ) ]
, qeFrom = [ TRSimple [ Name Nothing "t" ] ]
, qeWhere = Nothing
, qeGroupBy =
[ Rollup
[ SimpleGroup (Iden [ Name Nothing "Region" ])
, SimpleGroup (Iden [ Name Nothing "Sales_Person" ])
, SimpleGroup
(App
[ Name Nothing "WEEK" ] [ Iden [ Name Nothing "Sales_Date" ] ])
, SimpleGroup
(App
[ Name Nothing "YEAR" ] [ Iden [ Name Nothing "Sales_Date" ] ])
, SimpleGroup
(App
[ Name Nothing "MONTH" ] [ Iden [ Name Nothing "Sales_Date" ] ])
]
]
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
SELECT WEEK(SALES_DATE) AS WEEK,
DAYOFWEEK(SALES_DATE) AS DAY_WEEK,
SALES_PERSON, SUM(SALES) AS UNITS_SOLD
FROM SALES
WHERE WEEK(SALES_DATE) = 13
GROUP BY WEEK(SALES_DATE), DAYOFWEEK(SALES_DATE), SALES_PERSON
ORDER BY WEEK, DAY_WEEK, SALES_PERSON
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( App
[ Name Nothing "WEEK" ] [ Iden [ Name Nothing "SALES_DATE" ] ]
, Just (Name Nothing "WEEK")
)
, ( App
[ Name Nothing "DAYOFWEEK" ] [ Iden [ Name Nothing "SALES_DATE" ] ]
, Just (Name Nothing "DAY_WEEK")
)
, ( Iden [ Name Nothing "SALES_PERSON" ] , Nothing )
, ( App [ Name Nothing "SUM" ] [ Iden [ Name Nothing "SALES" ] ]
, Just (Name Nothing "UNITS_SOLD")
)
]
, qeFrom = [ TRSimple [ Name Nothing "SALES" ] ]
, qeWhere =
Just
(BinOp
(App
[ Name Nothing "WEEK" ] [ Iden [ Name Nothing "SALES_DATE" ] ])
[ Name Nothing "=" ]
(NumLit "13"))
, qeGroupBy =
[ SimpleGroup
(App
[ Name Nothing "WEEK" ] [ Iden [ Name Nothing "SALES_DATE" ] ])
, SimpleGroup
(App
[ Name Nothing "DAYOFWEEK" ]
[ Iden [ Name Nothing "SALES_DATE" ] ])
, SimpleGroup (Iden [ Name Nothing "SALES_PERSON" ])
]
, qeHaving = Nothing
, qeOrderBy =
[ SortSpec
(Iden [ Name Nothing "WEEK" ]) DirDefault NullsOrderDefault
, SortSpec
(Iden [ Name Nothing "DAY_WEEK" ]) DirDefault NullsOrderDefault
, SortSpec
(Iden [ Name Nothing "SALES_PERSON" ]) DirDefault NullsOrderDefault
]
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
SELECT WEEK(SALES_DATE) AS WEEK,
DAYOFWEEK(SALES_DATE) AS DAY_WEEK,
SALES_PERSON, SUM(SALES) AS UNITS_SOLD
FROM SALES
WHERE WEEK(SALES_DATE) = 13
GROUP BY GROUPING SETS ( (WEEK(SALES_DATE), SALES_PERSON),
(DAYOFWEEK(SALES_DATE), SALES_PERSON))
ORDER BY WEEK, DAY_WEEK, SALES_PERSON
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( App
[ Name Nothing "WEEK" ] [ Iden [ Name Nothing "SALES_DATE" ] ]
, Just (Name Nothing "WEEK")
)
, ( App
[ Name Nothing "DAYOFWEEK" ] [ Iden [ Name Nothing "SALES_DATE" ] ]
, Just (Name Nothing "DAY_WEEK")
)
, ( Iden [ Name Nothing "SALES_PERSON" ] , Nothing )
, ( App [ Name Nothing "SUM" ] [ Iden [ Name Nothing "SALES" ] ]
, Just (Name Nothing "UNITS_SOLD")
)
]
, qeFrom = [ TRSimple [ Name Nothing "SALES" ] ]
, qeWhere =
Just
(BinOp
(App
[ Name Nothing "WEEK" ] [ Iden [ Name Nothing "SALES_DATE" ] ])
[ Name Nothing "=" ]
(NumLit "13"))
, qeGroupBy =
[ GroupingSets
[ GroupingParens
[ SimpleGroup
(App
[ Name Nothing "WEEK" ] [ Iden [ Name Nothing "SALES_DATE" ] ])
, SimpleGroup (Iden [ Name Nothing "SALES_PERSON" ])
]
, GroupingParens
[ SimpleGroup
(App
[ Name Nothing "DAYOFWEEK" ]
[ Iden [ Name Nothing "SALES_DATE" ] ])
, SimpleGroup (Iden [ Name Nothing "SALES_PERSON" ])
]
]
]
, qeHaving = Nothing
, qeOrderBy =
[ SortSpec
(Iden [ Name Nothing "WEEK" ]) DirDefault NullsOrderDefault
, SortSpec
(Iden [ Name Nothing "DAY_WEEK" ]) DirDefault NullsOrderDefault
, SortSpec
(Iden [ Name Nothing "SALES_PERSON" ]) DirDefault NullsOrderDefault
]
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
SELECT WEEK(SALES_DATE) AS WEEK,
DAYOFWEEK(SALES_DATE) AS DAY_WEEK,
SALES_PERSON, SUM(SALES) AS UNITS_SOLD
FROM SALES
WHERE WEEK(SALES_DATE) = 13
GROUP BY ROLLUP ( WEEK(SALES_DATE), DAYOFWEEK(SALES_DATE), SALES_PERSON )
ORDER BY WEEK, DAY_WEEK, SALES_PERSON
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( App
[ Name Nothing "WEEK" ] [ Iden [ Name Nothing "SALES_DATE" ] ]
, Just (Name Nothing "WEEK")
)
, ( App
[ Name Nothing "DAYOFWEEK" ] [ Iden [ Name Nothing "SALES_DATE" ] ]
, Just (Name Nothing "DAY_WEEK")
)
, ( Iden [ Name Nothing "SALES_PERSON" ] , Nothing )
, ( App [ Name Nothing "SUM" ] [ Iden [ Name Nothing "SALES" ] ]
, Just (Name Nothing "UNITS_SOLD")
)
]
, qeFrom = [ TRSimple [ Name Nothing "SALES" ] ]
, qeWhere =
Just
(BinOp
(App
[ Name Nothing "WEEK" ] [ Iden [ Name Nothing "SALES_DATE" ] ])
[ Name Nothing "=" ]
(NumLit "13"))
, qeGroupBy =
[ Rollup
[ SimpleGroup
(App
[ Name Nothing "WEEK" ] [ Iden [ Name Nothing "SALES_DATE" ] ])
, SimpleGroup
(App
[ Name Nothing "DAYOFWEEK" ]
[ Iden [ Name Nothing "SALES_DATE" ] ])
, SimpleGroup (Iden [ Name Nothing "SALES_PERSON" ])
]
]
, qeHaving = Nothing
, qeOrderBy =
[ SortSpec
(Iden [ Name Nothing "WEEK" ]) DirDefault NullsOrderDefault
, SortSpec
(Iden [ Name Nothing "DAY_WEEK" ]) DirDefault NullsOrderDefault
, SortSpec
(Iden [ Name Nothing "SALES_PERSON" ]) DirDefault NullsOrderDefault
]
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
SELECT WEEK(SALES_DATE) AS WEEK,
DAYOFWEEK(SALES_DATE) AS DAY_WEEK,
SALES_PERSON, SUM(SALES) AS UNITS_SOLD
FROM SALES
WHERE WEEK(SALES_DATE) = 13
GROUP BY CUBE ( WEEK(SALES_DATE), DAYOFWEEK(SALES_DATE), SALES_PERSON )
ORDER BY WEEK, DAY_WEEK, SALES_PERSON
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( App
[ Name Nothing "WEEK" ] [ Iden [ Name Nothing "SALES_DATE" ] ]
, Just (Name Nothing "WEEK")
)
, ( App
[ Name Nothing "DAYOFWEEK" ] [ Iden [ Name Nothing "SALES_DATE" ] ]
, Just (Name Nothing "DAY_WEEK")
)
, ( Iden [ Name Nothing "SALES_PERSON" ] , Nothing )
, ( App [ Name Nothing "SUM" ] [ Iden [ Name Nothing "SALES" ] ]
, Just (Name Nothing "UNITS_SOLD")
)
]
, qeFrom = [ TRSimple [ Name Nothing "SALES" ] ]
, qeWhere =
Just
(BinOp
(App
[ Name Nothing "WEEK" ] [ Iden [ Name Nothing "SALES_DATE" ] ])
[ Name Nothing "=" ]
(NumLit "13"))
, qeGroupBy =
[ Cube
[ SimpleGroup
(App
[ Name Nothing "WEEK" ] [ Iden [ Name Nothing "SALES_DATE" ] ])
, SimpleGroup
(App
[ Name Nothing "DAYOFWEEK" ]
[ Iden [ Name Nothing "SALES_DATE" ] ])
, SimpleGroup (Iden [ Name Nothing "SALES_PERSON" ])
]
]
, qeHaving = Nothing
, qeOrderBy =
[ SortSpec
(Iden [ Name Nothing "WEEK" ]) DirDefault NullsOrderDefault
, SortSpec
(Iden [ Name Nothing "DAY_WEEK" ]) DirDefault NullsOrderDefault
, SortSpec
(Iden [ Name Nothing "SALES_PERSON" ]) DirDefault NullsOrderDefault
]
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
SELECT SALES_PERSON,
MONTH(SALES_DATE) AS MONTH,
SUM(SALES) AS UNITS_SOLD
FROM SALES
GROUP BY GROUPING SETS ( (SALES_PERSON, MONTH(SALES_DATE)),
()
)
ORDER BY SALES_PERSON, MONTH
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( Iden [ Name Nothing "SALES_PERSON" ] , Nothing )
, ( App
[ Name Nothing "MONTH" ] [ Iden [ Name Nothing "SALES_DATE" ] ]
, Just (Name Nothing "MONTH")
)
, ( App [ Name Nothing "SUM" ] [ Iden [ Name Nothing "SALES" ] ]
, Just (Name Nothing "UNITS_SOLD")
)
]
, qeFrom = [ TRSimple [ Name Nothing "SALES" ] ]
, qeWhere = Nothing
, qeGroupBy =
[ GroupingSets
[ GroupingParens
[ SimpleGroup (Iden [ Name Nothing "SALES_PERSON" ])
, SimpleGroup
(App
[ Name Nothing "MONTH" ] [ Iden [ Name Nothing "SALES_DATE" ] ])
]
, GroupingParens []
]
]
, qeHaving = Nothing
, qeOrderBy =
[ SortSpec
(Iden [ Name Nothing "SALES_PERSON" ]) DirDefault NullsOrderDefault
, SortSpec
(Iden [ Name Nothing "MONTH" ]) DirDefault NullsOrderDefault
]
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
SELECT WEEK(SALES_DATE) AS WEEK,
DAYOFWEEK(SALES_DATE) AS DAY_WEEK,
SUM(SALES) AS UNITS_SOLD
FROM SALES
GROUP BY ROLLUP ( WEEK(SALES_DATE), DAYOFWEEK(SALES_DATE) )
ORDER BY WEEK, DAY_WEEK
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( App
[ Name Nothing "WEEK" ] [ Iden [ Name Nothing "SALES_DATE" ] ]
, Just (Name Nothing "WEEK")
)
, ( App
[ Name Nothing "DAYOFWEEK" ] [ Iden [ Name Nothing "SALES_DATE" ] ]
, Just (Name Nothing "DAY_WEEK")
)
, ( App [ Name Nothing "SUM" ] [ Iden [ Name Nothing "SALES" ] ]
, Just (Name Nothing "UNITS_SOLD")
)
]
, qeFrom = [ TRSimple [ Name Nothing "SALES" ] ]
, qeWhere = Nothing
, qeGroupBy =
[ Rollup
[ SimpleGroup
(App
[ Name Nothing "WEEK" ] [ Iden [ Name Nothing "SALES_DATE" ] ])
, SimpleGroup
(App
[ Name Nothing "DAYOFWEEK" ]
[ Iden [ Name Nothing "SALES_DATE" ] ])
]
]
, qeHaving = Nothing
, qeOrderBy =
[ SortSpec
(Iden [ Name Nothing "WEEK" ]) DirDefault NullsOrderDefault
, SortSpec
(Iden [ Name Nothing "DAY_WEEK" ]) DirDefault NullsOrderDefault
]
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
SELECT MONTH(SALES_DATE) AS MONTH,
REGION,
SUM(SALES) AS UNITS_SOLD
FROM SALES
GROUP BY ROLLUP ( MONTH(SALES_DATE), REGION )
ORDER BY MONTH, REGION
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( App
[ Name Nothing "MONTH" ] [ Iden [ Name Nothing "SALES_DATE" ] ]
, Just (Name Nothing "MONTH")
)
, ( Iden [ Name Nothing "REGION" ] , Nothing )
, ( App [ Name Nothing "SUM" ] [ Iden [ Name Nothing "SALES" ] ]
, Just (Name Nothing "UNITS_SOLD")
)
]
, qeFrom = [ TRSimple [ Name Nothing "SALES" ] ]
, qeWhere = Nothing
, qeGroupBy =
[ Rollup
[ SimpleGroup
(App
[ Name Nothing "MONTH" ] [ Iden [ Name Nothing "SALES_DATE" ] ])
, SimpleGroup (Iden [ Name Nothing "REGION" ])
]
]
, qeHaving = Nothing
, qeOrderBy =
[ SortSpec
(Iden [ Name Nothing "MONTH" ]) DirDefault NullsOrderDefault
, SortSpec
(Iden [ Name Nothing "REGION" ]) DirDefault NullsOrderDefault
]
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
SELECT WEEK(SALES_DATE) AS WEEK,
DAYOFWEEK(SALES_DATE) AS DAY_WEEK,
MONTH(SALES_DATE) AS MONTH,
REGION,
SUM(SALES) AS UNITS_SOLD
FROM SALES
GROUP BY GROUPING SETS ( ROLLUP( WEEK(SALES_DATE), DAYOFWEEK(SALES_DATE) ),
ROLLUP( MONTH(SALES_DATE), REGION ) )
ORDER BY WEEK, DAY_WEEK, MONTH, REGION
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( App
[ Name Nothing "WEEK" ] [ Iden [ Name Nothing "SALES_DATE" ] ]
, Just (Name Nothing "WEEK")
)
, ( App
[ Name Nothing "DAYOFWEEK" ] [ Iden [ Name Nothing "SALES_DATE" ] ]
, Just (Name Nothing "DAY_WEEK")
)
, ( App
[ Name Nothing "MONTH" ] [ Iden [ Name Nothing "SALES_DATE" ] ]
, Just (Name Nothing "MONTH")
)
, ( Iden [ Name Nothing "REGION" ] , Nothing )
, ( App [ Name Nothing "SUM" ] [ Iden [ Name Nothing "SALES" ] ]
, Just (Name Nothing "UNITS_SOLD")
)
]
, qeFrom = [ TRSimple [ Name Nothing "SALES" ] ]
, qeWhere = Nothing
, qeGroupBy =
[ GroupingSets
[ Rollup
[ SimpleGroup
(App
[ Name Nothing "WEEK" ] [ Iden [ Name Nothing "SALES_DATE" ] ])
, SimpleGroup
(App
[ Name Nothing "DAYOFWEEK" ]
[ Iden [ Name Nothing "SALES_DATE" ] ])
]
, Rollup
[ SimpleGroup
(App
[ Name Nothing "MONTH" ] [ Iden [ Name Nothing "SALES_DATE" ] ])
, SimpleGroup (Iden [ Name Nothing "REGION" ])
]
]
]
, qeHaving = Nothing
, qeOrderBy =
[ SortSpec
(Iden [ Name Nothing "WEEK" ]) DirDefault NullsOrderDefault
, SortSpec
(Iden [ Name Nothing "DAY_WEEK" ]) DirDefault NullsOrderDefault
, SortSpec
(Iden [ Name Nothing "MONTH" ]) DirDefault NullsOrderDefault
, SortSpec
(Iden [ Name Nothing "REGION" ]) DirDefault NullsOrderDefault
]
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
SELECT R1, R2,
WEEK(SALES_DATE) AS WEEK,
DAYOFWEEK(SALES_DATE) AS DAY_WEEK,
MONTH(SALES_DATE) AS MONTH,
REGION, SUM(SALES) AS UNITS_SOLD
FROM SALES,(VALUES('GROUP 1','GROUP 2')) AS X(R1,R2)
GROUP BY GROUPING SETS ((R1, ROLLUP(WEEK(SALES_DATE),
DAYOFWEEK(SALES_DATE))),
(R2,ROLLUP( MONTH(SALES_DATE), REGION ) ))
ORDER BY WEEK, DAY_WEEK, MONTH, REGION
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( Iden [ Name Nothing "R1" ] , Nothing )
, ( Iden [ Name Nothing "R2" ] , Nothing )
, ( App
[ Name Nothing "WEEK" ] [ Iden [ Name Nothing "SALES_DATE" ] ]
, Just (Name Nothing "WEEK")
)
, ( App
[ Name Nothing "DAYOFWEEK" ] [ Iden [ Name Nothing "SALES_DATE" ] ]
, Just (Name Nothing "DAY_WEEK")
)
, ( App
[ Name Nothing "MONTH" ] [ Iden [ Name Nothing "SALES_DATE" ] ]
, Just (Name Nothing "MONTH")
)
, ( Iden [ Name Nothing "REGION" ] , Nothing )
, ( App [ Name Nothing "SUM" ] [ Iden [ Name Nothing "SALES" ] ]
, Just (Name Nothing "UNITS_SOLD")
)
]
, qeFrom =
[ TRSimple [ Name Nothing "SALES" ]
, TRAlias
(TRQueryExpr
(Values
[ [ StringLit "'" "'" "GROUP 1" , StringLit "'" "'" "GROUP 2" ] ]))
(Alias
(Name Nothing "X")
(Just [ Name Nothing "R1" , Name Nothing "R2" ]))
]
, qeWhere = Nothing
, qeGroupBy =
[ GroupingSets
[ GroupingParens
[ SimpleGroup (Iden [ Name Nothing "R1" ])
, Rollup
[ SimpleGroup
(App
[ Name Nothing "WEEK" ] [ Iden [ Name Nothing "SALES_DATE" ] ])
, SimpleGroup
(App
[ Name Nothing "DAYOFWEEK" ]
[ Iden [ Name Nothing "SALES_DATE" ] ])
]
]
, GroupingParens
[ SimpleGroup (Iden [ Name Nothing "R2" ])
, Rollup
[ SimpleGroup
(App
[ Name Nothing "MONTH" ] [ Iden [ Name Nothing "SALES_DATE" ] ])
, SimpleGroup (Iden [ Name Nothing "REGION" ])
]
]
]
]
, qeHaving = Nothing
, qeOrderBy =
[ SortSpec
(Iden [ Name Nothing "WEEK" ]) DirDefault NullsOrderDefault
, SortSpec
(Iden [ Name Nothing "DAY_WEEK" ]) DirDefault NullsOrderDefault
, SortSpec
(Iden [ Name Nothing "MONTH" ]) DirDefault NullsOrderDefault
, SortSpec
(Iden [ Name Nothing "REGION" ]) DirDefault NullsOrderDefault
]
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
SELECT MONTH(SALES_DATE) AS MONTH,
REGION,
SUM(SALES) AS UNITS_SOLD,
MAX(SALES) AS BEST_SALE,
CAST(ROUND(AVG(DECIMALx(SALES)),2) AS DECIMAL(5,2)) AS AVG_UNITS_SOLD
FROM SALES
GROUP BY CUBE(MONTH(SALES_DATE),REGION)
ORDER BY MONTH, REGION
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( App
[ Name Nothing "MONTH" ] [ Iden [ Name Nothing "SALES_DATE" ] ]
, Just (Name Nothing "MONTH")
)
, ( Iden [ Name Nothing "REGION" ] , Nothing )
, ( App [ Name Nothing "SUM" ] [ Iden [ Name Nothing "SALES" ] ]
, Just (Name Nothing "UNITS_SOLD")
)
, ( App [ Name Nothing "MAX" ] [ Iden [ Name Nothing "SALES" ] ]
, Just (Name Nothing "BEST_SALE")
)
, ( Cast
(App
[ Name Nothing "ROUND" ]
[ App
[ Name Nothing "AVG" ]
[ App [ Name Nothing "DECIMALx" ] [ Iden [ Name Nothing "SALES" ] ]
]
, NumLit "2"
])
(PrecScaleTypeName [ Name Nothing "decimal" ] 5 2)
, Just (Name Nothing "AVG_UNITS_SOLD")
)
]
, qeFrom = [ TRSimple [ Name Nothing "SALES" ] ]
, qeWhere = Nothing
, qeGroupBy =
[ Cube
[ SimpleGroup
(App
[ Name Nothing "MONTH" ] [ Iden [ Name Nothing "SALES_DATE" ] ])
, SimpleGroup (Iden [ Name Nothing "REGION" ])
]
]
, qeHaving = Nothing
, qeOrderBy =
[ SortSpec
(Iden [ Name Nothing "MONTH" ]) DirDefault NullsOrderDefault
, SortSpec
(Iden [ Name Nothing "REGION" ]) DirDefault NullsOrderDefault
]
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
SELECT name, (SELECT max(pop) FROM cities
WHERE cities.state = states.name)
FROM states;
|
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');
|
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;
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( App
[ Name Nothing "ROW" ] [ QStar [ Name Nothing "t" ] , 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;
|
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));
|
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');
|
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;
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( PostfixOp
[ Name Nothing "is null" ]
(App [ Name Nothing "ROW" ] [ QStar [ Name Nothing "tablex" ] ])
, Nothing
)
]
, qeFrom = [ TRSimple [ Name Nothing "tablex" ] ]
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
SELECT true OR somefunc();
|
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;
|
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;
|
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;
|
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);
|
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;
|
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;
|
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);
|
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;
|
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;
|
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';
|
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';
|
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;
|
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;
|
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;
|
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;
|
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;
|
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
);
|
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;
|
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;
|
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;
|
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
|
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)
|
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)
|
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)
|
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
|
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)
|
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
}
|
|
|
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;
|
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;
|
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;
|
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;
|
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';
|
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;
|
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
}
|
|
|
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
|
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
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( QStar [ Name Nothing "tbl1" ] , 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
|
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
|
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
|
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');
|
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';
|
QueryExprSetOp
{ qe0 =
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 =
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);
|
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;
|
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
|
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
|
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;
|
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;
|
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
|
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';
|
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;
|
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;
|
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%';
|
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
|
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;
|
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;
|
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;
|
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
}
|
|
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( BinOp (NumLit "2") [ Name Nothing "+" ] (NumLit "2")
, Nothing
)
]
, qeFrom = []
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
select
l_returnflag,
l_linestatus,
sum(l_quantity) as sum_qty,
sum(l_extendedprice) as sum_base_price,
sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
avg(l_quantity) as avg_qty,
avg(l_extendedprice) as avg_price,
avg(l_discount) as avg_disc,
count(*) as count_order
from
lineitem
where
l_shipdate <= date '1998-12-01' - interval '63' day (3)
group by
l_returnflag,
l_linestatus
order by
l_returnflag,
l_linestatus
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( Iden [ Name Nothing "l_returnflag" ] , Nothing )
, ( Iden [ Name Nothing "l_linestatus" ] , Nothing )
, ( App
[ Name Nothing "sum" ] [ Iden [ Name Nothing "l_quantity" ] ]
, Just (Name Nothing "sum_qty")
)
, ( App
[ Name Nothing "sum" ] [ Iden [ Name Nothing "l_extendedprice" ] ]
, Just (Name Nothing "sum_base_price")
)
, ( App
[ Name Nothing "sum" ]
[ BinOp
(Iden [ Name Nothing "l_extendedprice" ])
[ Name Nothing "*" ]
(Parens
(BinOp
(NumLit "1")
[ Name Nothing "-" ]
(Iden [ Name Nothing "l_discount" ])))
]
, Just (Name Nothing "sum_disc_price")
)
, ( App
[ Name Nothing "sum" ]
[ BinOp
(BinOp
(Iden [ Name Nothing "l_extendedprice" ])
[ Name Nothing "*" ]
(Parens
(BinOp
(NumLit "1")
[ Name Nothing "-" ]
(Iden [ Name Nothing "l_discount" ]))))
[ Name Nothing "*" ]
(Parens
(BinOp
(NumLit "1") [ Name Nothing "+" ] (Iden [ Name Nothing "l_tax" ])))
]
, Just (Name Nothing "sum_charge")
)
, ( App
[ Name Nothing "avg" ] [ Iden [ Name Nothing "l_quantity" ] ]
, Just (Name Nothing "avg_qty")
)
, ( App
[ Name Nothing "avg" ] [ Iden [ Name Nothing "l_extendedprice" ] ]
, Just (Name Nothing "avg_price")
)
, ( App
[ Name Nothing "avg" ] [ Iden [ Name Nothing "l_discount" ] ]
, Just (Name Nothing "avg_disc")
)
, ( App [ Name Nothing "count" ] [ Star ]
, Just (Name Nothing "count_order")
)
]
, qeFrom = [ TRSimple [ Name Nothing "lineitem" ] ]
, qeWhere =
Just
(BinOp
(Iden [ Name Nothing "l_shipdate" ])
[ Name Nothing "<=" ]
(BinOp
(TypedLit (TypeName [ Name Nothing "date" ]) "1998-12-01")
[ Name Nothing "-" ]
IntervalLit
{ ilSign = Nothing
, ilLiteral = "63"
, ilFrom = Itf "day" (Just ( 3 , Nothing ))
, ilTo = Nothing
}))
, qeGroupBy =
[ SimpleGroup (Iden [ Name Nothing "l_returnflag" ])
, SimpleGroup (Iden [ Name Nothing "l_linestatus" ])
]
, qeHaving = Nothing
, qeOrderBy =
[ SortSpec
(Iden [ Name Nothing "l_returnflag" ]) DirDefault NullsOrderDefault
, SortSpec
(Iden [ Name Nothing "l_linestatus" ]) DirDefault NullsOrderDefault
]
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
select
s_acctbal,
s_name,
n_name,
p_partkey,
p_mfgr,
s_address,
s_phone,
s_comment
from
part,
supplier,
partsupp,
nation,
region
where
p_partkey = ps_partkey
and s_suppkey = ps_suppkey
and p_size = 15
and p_type like '%BRASS'
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = 'EUROPE'
and ps_supplycost = (
select
min(ps_supplycost)
from
partsupp,
supplier,
nation,
region
where
p_partkey = ps_partkey
and s_suppkey = ps_suppkey
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = 'EUROPE'
)
order by
s_acctbal desc,
n_name,
s_name,
p_partkey
fetch first 100 rows only
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( Iden [ Name Nothing "s_acctbal" ] , Nothing )
, ( Iden [ Name Nothing "s_name" ] , Nothing )
, ( Iden [ Name Nothing "n_name" ] , Nothing )
, ( Iden [ Name Nothing "p_partkey" ] , Nothing )
, ( Iden [ Name Nothing "p_mfgr" ] , Nothing )
, ( Iden [ Name Nothing "s_address" ] , Nothing )
, ( Iden [ Name Nothing "s_phone" ] , Nothing )
, ( Iden [ Name Nothing "s_comment" ] , Nothing )
]
, qeFrom =
[ TRSimple [ Name Nothing "part" ]
, TRSimple [ Name Nothing "supplier" ]
, TRSimple [ Name Nothing "partsupp" ]
, TRSimple [ Name Nothing "nation" ]
, TRSimple [ Name Nothing "region" ]
]
, qeWhere =
Just
(BinOp
(BinOp
(BinOp
(BinOp
(BinOp
(BinOp
(BinOp
(BinOp
(Iden [ Name Nothing "p_partkey" ])
[ Name Nothing "=" ]
(Iden [ Name Nothing "ps_partkey" ]))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "s_suppkey" ])
[ Name Nothing "=" ]
(Iden [ Name Nothing "ps_suppkey" ])))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "p_size" ])
[ Name Nothing "=" ]
(NumLit "15")))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "p_type" ])
[ Name Nothing "like" ]
(StringLit "'" "'" "%BRASS")))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "s_nationkey" ])
[ Name Nothing "=" ]
(Iden [ Name Nothing "n_nationkey" ])))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "n_regionkey" ])
[ Name Nothing "=" ]
(Iden [ Name Nothing "r_regionkey" ])))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "r_name" ])
[ Name Nothing "=" ]
(StringLit "'" "'" "EUROPE")))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "ps_supplycost" ])
[ Name Nothing "=" ]
(SubQueryExpr
SqSq
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( App
[ Name Nothing "min" ] [ Iden [ Name Nothing "ps_supplycost" ] ]
, Nothing
)
]
, qeFrom =
[ TRSimple [ Name Nothing "partsupp" ]
, TRSimple [ Name Nothing "supplier" ]
, TRSimple [ Name Nothing "nation" ]
, TRSimple [ Name Nothing "region" ]
]
, qeWhere =
Just
(BinOp
(BinOp
(BinOp
(BinOp
(BinOp
(Iden [ Name Nothing "p_partkey" ])
[ Name Nothing "=" ]
(Iden [ Name Nothing "ps_partkey" ]))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "s_suppkey" ])
[ Name Nothing "=" ]
(Iden [ Name Nothing "ps_suppkey" ])))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "s_nationkey" ])
[ Name Nothing "=" ]
(Iden [ Name Nothing "n_nationkey" ])))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "n_regionkey" ])
[ Name Nothing "=" ]
(Iden [ Name Nothing "r_regionkey" ])))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "r_name" ])
[ Name Nothing "=" ]
(StringLit "'" "'" "EUROPE")))
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
})))
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy =
[ SortSpec
(Iden [ Name Nothing "s_acctbal" ]) Desc NullsOrderDefault
, SortSpec
(Iden [ Name Nothing "n_name" ]) DirDefault NullsOrderDefault
, SortSpec
(Iden [ Name Nothing "s_name" ]) DirDefault NullsOrderDefault
, SortSpec
(Iden [ Name Nothing "p_partkey" ]) DirDefault NullsOrderDefault
]
, qeOffset = Nothing
, qeFetchFirst = Just (NumLit "100")
}
|
select
l_orderkey,
sum(l_extendedprice * (1 - l_discount)) as revenue,
o_orderdate,
o_shippriority
from
customer,
orders,
lineitem
where
c_mktsegment = 'MACHINERY'
and c_custkey = o_custkey
and l_orderkey = o_orderkey
and o_orderdate < date '1995-03-21'
and l_shipdate > date '1995-03-21'
group by
l_orderkey,
o_orderdate,
o_shippriority
order by
revenue desc,
o_orderdate
fetch first 10 rows only
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( Iden [ Name Nothing "l_orderkey" ] , Nothing )
, ( App
[ Name Nothing "sum" ]
[ BinOp
(Iden [ Name Nothing "l_extendedprice" ])
[ Name Nothing "*" ]
(Parens
(BinOp
(NumLit "1")
[ Name Nothing "-" ]
(Iden [ Name Nothing "l_discount" ])))
]
, Just (Name Nothing "revenue")
)
, ( Iden [ Name Nothing "o_orderdate" ] , Nothing )
, ( Iden [ Name Nothing "o_shippriority" ] , Nothing )
]
, qeFrom =
[ TRSimple [ Name Nothing "customer" ]
, TRSimple [ Name Nothing "orders" ]
, TRSimple [ Name Nothing "lineitem" ]
]
, qeWhere =
Just
(BinOp
(BinOp
(BinOp
(BinOp
(BinOp
(Iden [ Name Nothing "c_mktsegment" ])
[ Name Nothing "=" ]
(StringLit "'" "'" "MACHINERY"))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "c_custkey" ])
[ Name Nothing "=" ]
(Iden [ Name Nothing "o_custkey" ])))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "l_orderkey" ])
[ Name Nothing "=" ]
(Iden [ Name Nothing "o_orderkey" ])))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "o_orderdate" ])
[ Name Nothing "<" ]
(TypedLit (TypeName [ Name Nothing "date" ]) "1995-03-21")))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "l_shipdate" ])
[ Name Nothing ">" ]
(TypedLit (TypeName [ Name Nothing "date" ]) "1995-03-21")))
, qeGroupBy =
[ SimpleGroup (Iden [ Name Nothing "l_orderkey" ])
, SimpleGroup (Iden [ Name Nothing "o_orderdate" ])
, SimpleGroup (Iden [ Name Nothing "o_shippriority" ])
]
, qeHaving = Nothing
, qeOrderBy =
[ SortSpec (Iden [ Name Nothing "revenue" ]) Desc NullsOrderDefault
, SortSpec
(Iden [ Name Nothing "o_orderdate" ]) DirDefault NullsOrderDefault
]
, qeOffset = Nothing
, qeFetchFirst = Just (NumLit "10")
}
|
select
o_orderpriority,
count(*) as order_count
from
orders
where
o_orderdate >= date '1996-03-01'
and o_orderdate < date '1996-03-01' + interval '3' month
and exists (
select
*
from
lineitem
where
l_orderkey = o_orderkey
and l_commitdate < l_receiptdate
)
group by
o_orderpriority
order by
o_orderpriority
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( Iden [ Name Nothing "o_orderpriority" ] , Nothing )
, ( App [ Name Nothing "count" ] [ Star ]
, Just (Name Nothing "order_count")
)
]
, qeFrom = [ TRSimple [ Name Nothing "orders" ] ]
, qeWhere =
Just
(BinOp
(BinOp
(BinOp
(Iden [ Name Nothing "o_orderdate" ])
[ Name Nothing ">=" ]
(TypedLit (TypeName [ Name Nothing "date" ]) "1996-03-01"))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "o_orderdate" ])
[ Name Nothing "<" ]
(BinOp
(TypedLit (TypeName [ Name Nothing "date" ]) "1996-03-01")
[ Name Nothing "+" ]
IntervalLit
{ ilSign = Nothing
, ilLiteral = "3"
, ilFrom = Itf "month" Nothing
, ilTo = Nothing
})))
[ Name Nothing "and" ]
(SubQueryExpr
SqExists
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Star , Nothing ) ]
, qeFrom = [ TRSimple [ Name Nothing "lineitem" ] ]
, qeWhere =
Just
(BinOp
(BinOp
(Iden [ Name Nothing "l_orderkey" ])
[ Name Nothing "=" ]
(Iden [ Name Nothing "o_orderkey" ]))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "l_commitdate" ])
[ Name Nothing "<" ]
(Iden [ Name Nothing "l_receiptdate" ])))
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}))
, qeGroupBy =
[ SimpleGroup (Iden [ Name Nothing "o_orderpriority" ]) ]
, qeHaving = Nothing
, qeOrderBy =
[ SortSpec
(Iden [ Name Nothing "o_orderpriority" ])
DirDefault
NullsOrderDefault
]
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
select
n_name,
sum(l_extendedprice * (1 - l_discount)) as revenue
from
customer,
orders,
lineitem,
supplier,
nation,
region
where
c_custkey = o_custkey
and l_orderkey = o_orderkey
and l_suppkey = s_suppkey
and c_nationkey = s_nationkey
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = 'EUROPE'
and o_orderdate >= date '1997-01-01'
and o_orderdate < date '1997-01-01' + interval '1' year
group by
n_name
order by
revenue desc
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( Iden [ Name Nothing "n_name" ] , Nothing )
, ( App
[ Name Nothing "sum" ]
[ BinOp
(Iden [ Name Nothing "l_extendedprice" ])
[ Name Nothing "*" ]
(Parens
(BinOp
(NumLit "1")
[ Name Nothing "-" ]
(Iden [ Name Nothing "l_discount" ])))
]
, Just (Name Nothing "revenue")
)
]
, qeFrom =
[ TRSimple [ Name Nothing "customer" ]
, TRSimple [ Name Nothing "orders" ]
, TRSimple [ Name Nothing "lineitem" ]
, TRSimple [ Name Nothing "supplier" ]
, TRSimple [ Name Nothing "nation" ]
, TRSimple [ Name Nothing "region" ]
]
, qeWhere =
Just
(BinOp
(BinOp
(BinOp
(BinOp
(BinOp
(BinOp
(BinOp
(BinOp
(BinOp
(Iden [ Name Nothing "c_custkey" ])
[ Name Nothing "=" ]
(Iden [ Name Nothing "o_custkey" ]))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "l_orderkey" ])
[ Name Nothing "=" ]
(Iden [ Name Nothing "o_orderkey" ])))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "l_suppkey" ])
[ Name Nothing "=" ]
(Iden [ Name Nothing "s_suppkey" ])))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "c_nationkey" ])
[ Name Nothing "=" ]
(Iden [ Name Nothing "s_nationkey" ])))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "s_nationkey" ])
[ Name Nothing "=" ]
(Iden [ Name Nothing "n_nationkey" ])))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "n_regionkey" ])
[ Name Nothing "=" ]
(Iden [ Name Nothing "r_regionkey" ])))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "r_name" ])
[ Name Nothing "=" ]
(StringLit "'" "'" "EUROPE")))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "o_orderdate" ])
[ Name Nothing ">=" ]
(TypedLit (TypeName [ Name Nothing "date" ]) "1997-01-01")))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "o_orderdate" ])
[ Name Nothing "<" ]
(BinOp
(TypedLit (TypeName [ Name Nothing "date" ]) "1997-01-01")
[ Name Nothing "+" ]
IntervalLit
{ ilSign = Nothing
, ilLiteral = "1"
, ilFrom = Itf "year" Nothing
, ilTo = Nothing
})))
, qeGroupBy = [ SimpleGroup (Iden [ Name Nothing "n_name" ]) ]
, qeHaving = Nothing
, qeOrderBy =
[ SortSpec (Iden [ Name Nothing "revenue" ]) Desc NullsOrderDefault
]
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
select
sum(l_extendedprice * l_discount) as revenue
from
lineitem
where
l_shipdate >= date '1997-01-01'
and l_shipdate < date '1997-01-01' + interval '1' year
and l_discount between 0.07 - 0.01 and 0.07 + 0.01
and l_quantity < 24
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( App
[ Name Nothing "sum" ]
[ BinOp
(Iden [ Name Nothing "l_extendedprice" ])
[ Name Nothing "*" ]
(Iden [ Name Nothing "l_discount" ])
]
, Just (Name Nothing "revenue")
)
]
, qeFrom = [ TRSimple [ Name Nothing "lineitem" ] ]
, qeWhere =
Just
(BinOp
(BinOp
(BinOp
(BinOp
(Iden [ Name Nothing "l_shipdate" ])
[ Name Nothing ">=" ]
(TypedLit (TypeName [ Name Nothing "date" ]) "1997-01-01"))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "l_shipdate" ])
[ Name Nothing "<" ]
(BinOp
(TypedLit (TypeName [ Name Nothing "date" ]) "1997-01-01")
[ Name Nothing "+" ]
IntervalLit
{ ilSign = Nothing
, ilLiteral = "1"
, ilFrom = Itf "year" Nothing
, ilTo = Nothing
})))
[ Name Nothing "and" ]
(SpecialOp
[ Name Nothing "between" ]
[ Iden [ Name Nothing "l_discount" ]
, BinOp (NumLit "0.07") [ Name Nothing "-" ] (NumLit "0.01")
, BinOp (NumLit "0.07") [ Name Nothing "+" ] (NumLit "0.01")
]))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "l_quantity" ])
[ Name Nothing "<" ]
(NumLit "24")))
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
select
supp_nation,
cust_nation,
l_year,
sum(volume) as revenue
from
(
select
n1.n_name as supp_nation,
n2.n_name as cust_nation,
extract(year from l_shipdate) as l_year,
l_extendedprice * (1 - l_discount) as volume
from
supplier,
lineitem,
orders,
customer,
nation n1,
nation n2
where
s_suppkey = l_suppkey
and o_orderkey = l_orderkey
and c_custkey = o_custkey
and s_nationkey = n1.n_nationkey
and c_nationkey = n2.n_nationkey
and (
(n1.n_name = 'PERU' and n2.n_name = 'IRAQ')
or (n1.n_name = 'IRAQ' and n2.n_name = 'PERU')
)
and l_shipdate between date '1995-01-01' and date '1996-12-31'
) as shipping
group by
supp_nation,
cust_nation,
l_year
order by
supp_nation,
cust_nation,
l_year
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( Iden [ Name Nothing "supp_nation" ] , Nothing )
, ( Iden [ Name Nothing "cust_nation" ] , Nothing )
, ( Iden [ Name Nothing "l_year" ] , Nothing )
, ( App [ Name Nothing "sum" ] [ Iden [ Name Nothing "volume" ] ]
, Just (Name Nothing "revenue")
)
]
, qeFrom =
[ TRAlias
(TRQueryExpr
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( Iden [ Name Nothing "n1" , Name Nothing "n_name" ]
, Just (Name Nothing "supp_nation")
)
, ( Iden [ Name Nothing "n2" , Name Nothing "n_name" ]
, Just (Name Nothing "cust_nation")
)
, ( SpecialOpK
[ Name Nothing "extract" ]
(Just (Iden [ Name Nothing "year" ]))
[ ( "from" , Iden [ Name Nothing "l_shipdate" ] ) ]
, Just (Name Nothing "l_year")
)
, ( BinOp
(Iden [ Name Nothing "l_extendedprice" ])
[ Name Nothing "*" ]
(Parens
(BinOp
(NumLit "1")
[ Name Nothing "-" ]
(Iden [ Name Nothing "l_discount" ])))
, Just (Name Nothing "volume")
)
]
, qeFrom =
[ TRSimple [ Name Nothing "supplier" ]
, TRSimple [ Name Nothing "lineitem" ]
, TRSimple [ Name Nothing "orders" ]
, TRSimple [ Name Nothing "customer" ]
, TRAlias
(TRSimple [ Name Nothing "nation" ])
(Alias (Name Nothing "n1") Nothing)
, TRAlias
(TRSimple [ Name Nothing "nation" ])
(Alias (Name Nothing "n2") Nothing)
]
, qeWhere =
Just
(BinOp
(BinOp
(BinOp
(BinOp
(BinOp
(BinOp
(BinOp
(Iden [ Name Nothing "s_suppkey" ])
[ Name Nothing "=" ]
(Iden [ Name Nothing "l_suppkey" ]))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "o_orderkey" ])
[ Name Nothing "=" ]
(Iden [ Name Nothing "l_orderkey" ])))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "c_custkey" ])
[ Name Nothing "=" ]
(Iden [ Name Nothing "o_custkey" ])))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "s_nationkey" ])
[ Name Nothing "=" ]
(Iden [ Name Nothing "n1" , Name Nothing "n_nationkey" ])))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "c_nationkey" ])
[ Name Nothing "=" ]
(Iden [ Name Nothing "n2" , Name Nothing "n_nationkey" ])))
[ Name Nothing "and" ]
(Parens
(BinOp
(Parens
(BinOp
(BinOp
(Iden [ Name Nothing "n1" , Name Nothing "n_name" ])
[ Name Nothing "=" ]
(StringLit "'" "'" "PERU"))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "n2" , Name Nothing "n_name" ])
[ Name Nothing "=" ]
(StringLit "'" "'" "IRAQ"))))
[ Name Nothing "or" ]
(Parens
(BinOp
(BinOp
(Iden [ Name Nothing "n1" , Name Nothing "n_name" ])
[ Name Nothing "=" ]
(StringLit "'" "'" "IRAQ"))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "n2" , Name Nothing "n_name" ])
[ Name Nothing "=" ]
(StringLit "'" "'" "PERU")))))))
[ Name Nothing "and" ]
(SpecialOp
[ Name Nothing "between" ]
[ Iden [ Name Nothing "l_shipdate" ]
, TypedLit (TypeName [ Name Nothing "date" ]) "1995-01-01"
, TypedLit (TypeName [ Name Nothing "date" ]) "1996-12-31"
]))
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
})
(Alias (Name Nothing "shipping") Nothing)
]
, qeWhere = Nothing
, qeGroupBy =
[ SimpleGroup (Iden [ Name Nothing "supp_nation" ])
, SimpleGroup (Iden [ Name Nothing "cust_nation" ])
, SimpleGroup (Iden [ Name Nothing "l_year" ])
]
, qeHaving = Nothing
, qeOrderBy =
[ SortSpec
(Iden [ Name Nothing "supp_nation" ]) DirDefault NullsOrderDefault
, SortSpec
(Iden [ Name Nothing "cust_nation" ]) DirDefault NullsOrderDefault
, SortSpec
(Iden [ Name Nothing "l_year" ]) DirDefault NullsOrderDefault
]
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
select
o_year,
sum(case
when nation = 'IRAQ' then volume
else 0
end) / sum(volume) as mkt_share
from
(
select
extract(year from o_orderdate) as o_year,
l_extendedprice * (1 - l_discount) as volume,
n2.n_name as nation
from
part,
supplier,
lineitem,
orders,
customer,
nation n1,
nation n2,
region
where
p_partkey = l_partkey
and s_suppkey = l_suppkey
and l_orderkey = o_orderkey
and o_custkey = c_custkey
and c_nationkey = n1.n_nationkey
and n1.n_regionkey = r_regionkey
and r_name = 'MIDDLE EAST'
and s_nationkey = n2.n_nationkey
and o_orderdate between date '1995-01-01' and date '1996-12-31'
and p_type = 'STANDARD ANODIZED BRASS'
) as all_nations
group by
o_year
order by
o_year
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( Iden [ Name Nothing "o_year" ] , Nothing )
, ( BinOp
(App
[ Name Nothing "sum" ]
[ Case
{ caseTest = Nothing
, caseWhens =
[ ( [ BinOp
(Iden [ Name Nothing "nation" ])
[ Name Nothing "=" ]
(StringLit "'" "'" "IRAQ")
]
, Iden [ Name Nothing "volume" ]
)
]
, caseElse = Just (NumLit "0")
}
])
[ Name Nothing "/" ]
(App [ Name Nothing "sum" ] [ Iden [ Name Nothing "volume" ] ])
, Just (Name Nothing "mkt_share")
)
]
, qeFrom =
[ TRAlias
(TRQueryExpr
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( SpecialOpK
[ Name Nothing "extract" ]
(Just (Iden [ Name Nothing "year" ]))
[ ( "from" , Iden [ Name Nothing "o_orderdate" ] ) ]
, Just (Name Nothing "o_year")
)
, ( BinOp
(Iden [ Name Nothing "l_extendedprice" ])
[ Name Nothing "*" ]
(Parens
(BinOp
(NumLit "1")
[ Name Nothing "-" ]
(Iden [ Name Nothing "l_discount" ])))
, Just (Name Nothing "volume")
)
, ( Iden [ Name Nothing "n2" , Name Nothing "n_name" ]
, Just (Name Nothing "nation")
)
]
, qeFrom =
[ TRSimple [ Name Nothing "part" ]
, TRSimple [ Name Nothing "supplier" ]
, TRSimple [ Name Nothing "lineitem" ]
, TRSimple [ Name Nothing "orders" ]
, TRSimple [ Name Nothing "customer" ]
, TRAlias
(TRSimple [ Name Nothing "nation" ])
(Alias (Name Nothing "n1") Nothing)
, TRAlias
(TRSimple [ Name Nothing "nation" ])
(Alias (Name Nothing "n2") Nothing)
, TRSimple [ Name Nothing "region" ]
]
, qeWhere =
Just
(BinOp
(BinOp
(BinOp
(BinOp
(BinOp
(BinOp
(BinOp
(BinOp
(BinOp
(BinOp
(Iden [ Name Nothing "p_partkey" ])
[ Name Nothing "=" ]
(Iden [ Name Nothing "l_partkey" ]))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "s_suppkey" ])
[ Name Nothing "=" ]
(Iden [ Name Nothing "l_suppkey" ])))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "l_orderkey" ])
[ Name Nothing "=" ]
(Iden [ Name Nothing "o_orderkey" ])))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "o_custkey" ])
[ Name Nothing "=" ]
(Iden [ Name Nothing "c_custkey" ])))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "c_nationkey" ])
[ Name Nothing "=" ]
(Iden
[ Name Nothing "n1" , Name Nothing "n_nationkey" ])))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "n1" , Name Nothing "n_regionkey" ])
[ Name Nothing "=" ]
(Iden [ Name Nothing "r_regionkey" ])))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "r_name" ])
[ Name Nothing "=" ]
(StringLit "'" "'" "MIDDLE EAST")))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "s_nationkey" ])
[ Name Nothing "=" ]
(Iden [ Name Nothing "n2" , Name Nothing "n_nationkey" ])))
[ Name Nothing "and" ]
(SpecialOp
[ Name Nothing "between" ]
[ Iden [ Name Nothing "o_orderdate" ]
, TypedLit (TypeName [ Name Nothing "date" ]) "1995-01-01"
, TypedLit (TypeName [ Name Nothing "date" ]) "1996-12-31"
]))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "p_type" ])
[ Name Nothing "=" ]
(StringLit "'" "'" "STANDARD ANODIZED BRASS")))
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
})
(Alias (Name Nothing "all_nations") Nothing)
]
, qeWhere = Nothing
, qeGroupBy = [ SimpleGroup (Iden [ Name Nothing "o_year" ]) ]
, qeHaving = Nothing
, qeOrderBy =
[ SortSpec
(Iden [ Name Nothing "o_year" ]) DirDefault NullsOrderDefault
]
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
select
nation,
o_year,
sum(amount) as sum_profit
from
(
select
n_name as nation,
extract(year from o_orderdate) as o_year,
l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount
from
part,
supplier,
lineitem,
partsupp,
orders,
nation
where
s_suppkey = l_suppkey
and ps_suppkey = l_suppkey
and ps_partkey = l_partkey
and p_partkey = l_partkey
and o_orderkey = l_orderkey
and s_nationkey = n_nationkey
and p_name like '%antique%'
) as profit
group by
nation,
o_year
order by
nation,
o_year desc
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( Iden [ Name Nothing "nation" ] , Nothing )
, ( Iden [ Name Nothing "o_year" ] , Nothing )
, ( App [ Name Nothing "sum" ] [ Iden [ Name Nothing "amount" ] ]
, Just (Name Nothing "sum_profit")
)
]
, qeFrom =
[ TRAlias
(TRQueryExpr
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( Iden [ Name Nothing "n_name" ] , Just (Name Nothing "nation") )
, ( SpecialOpK
[ Name Nothing "extract" ]
(Just (Iden [ Name Nothing "year" ]))
[ ( "from" , Iden [ Name Nothing "o_orderdate" ] ) ]
, Just (Name Nothing "o_year")
)
, ( BinOp
(BinOp
(Iden [ Name Nothing "l_extendedprice" ])
[ Name Nothing "*" ]
(Parens
(BinOp
(NumLit "1")
[ Name Nothing "-" ]
(Iden [ Name Nothing "l_discount" ]))))
[ Name Nothing "-" ]
(BinOp
(Iden [ Name Nothing "ps_supplycost" ])
[ Name Nothing "*" ]
(Iden [ Name Nothing "l_quantity" ]))
, Just (Name Nothing "amount")
)
]
, qeFrom =
[ TRSimple [ Name Nothing "part" ]
, TRSimple [ Name Nothing "supplier" ]
, TRSimple [ Name Nothing "lineitem" ]
, TRSimple [ Name Nothing "partsupp" ]
, TRSimple [ Name Nothing "orders" ]
, TRSimple [ Name Nothing "nation" ]
]
, qeWhere =
Just
(BinOp
(BinOp
(BinOp
(BinOp
(BinOp
(BinOp
(BinOp
(Iden [ Name Nothing "s_suppkey" ])
[ Name Nothing "=" ]
(Iden [ Name Nothing "l_suppkey" ]))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "ps_suppkey" ])
[ Name Nothing "=" ]
(Iden [ Name Nothing "l_suppkey" ])))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "ps_partkey" ])
[ Name Nothing "=" ]
(Iden [ Name Nothing "l_partkey" ])))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "p_partkey" ])
[ Name Nothing "=" ]
(Iden [ Name Nothing "l_partkey" ])))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "o_orderkey" ])
[ Name Nothing "=" ]
(Iden [ Name Nothing "l_orderkey" ])))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "s_nationkey" ])
[ Name Nothing "=" ]
(Iden [ Name Nothing "n_nationkey" ])))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "p_name" ])
[ Name Nothing "like" ]
(StringLit "'" "'" "%antique%")))
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
})
(Alias (Name Nothing "profit") Nothing)
]
, qeWhere = Nothing
, qeGroupBy =
[ SimpleGroup (Iden [ Name Nothing "nation" ])
, SimpleGroup (Iden [ Name Nothing "o_year" ])
]
, qeHaving = Nothing
, qeOrderBy =
[ SortSpec
(Iden [ Name Nothing "nation" ]) DirDefault NullsOrderDefault
, SortSpec (Iden [ Name Nothing "o_year" ]) Desc NullsOrderDefault
]
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
select
c_custkey,
c_name,
sum(l_extendedprice * (1 - l_discount)) as revenue,
c_acctbal,
n_name,
c_address,
c_phone,
c_comment
from
customer,
orders,
lineitem,
nation
where
c_custkey = o_custkey
and l_orderkey = o_orderkey
and o_orderdate >= date '1993-12-01'
and o_orderdate < date '1993-12-01' + interval '3' month
and l_returnflag = 'R'
and c_nationkey = n_nationkey
group by
c_custkey,
c_name,
c_acctbal,
c_phone,
n_name,
c_address,
c_comment
order by
revenue desc
fetch first 20 rows only
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( Iden [ Name Nothing "c_custkey" ] , Nothing )
, ( Iden [ Name Nothing "c_name" ] , Nothing )
, ( App
[ Name Nothing "sum" ]
[ BinOp
(Iden [ Name Nothing "l_extendedprice" ])
[ Name Nothing "*" ]
(Parens
(BinOp
(NumLit "1")
[ Name Nothing "-" ]
(Iden [ Name Nothing "l_discount" ])))
]
, Just (Name Nothing "revenue")
)
, ( Iden [ Name Nothing "c_acctbal" ] , Nothing )
, ( Iden [ Name Nothing "n_name" ] , Nothing )
, ( Iden [ Name Nothing "c_address" ] , Nothing )
, ( Iden [ Name Nothing "c_phone" ] , Nothing )
, ( Iden [ Name Nothing "c_comment" ] , Nothing )
]
, qeFrom =
[ TRSimple [ Name Nothing "customer" ]
, TRSimple [ Name Nothing "orders" ]
, TRSimple [ Name Nothing "lineitem" ]
, TRSimple [ Name Nothing "nation" ]
]
, qeWhere =
Just
(BinOp
(BinOp
(BinOp
(BinOp
(BinOp
(BinOp
(Iden [ Name Nothing "c_custkey" ])
[ Name Nothing "=" ]
(Iden [ Name Nothing "o_custkey" ]))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "l_orderkey" ])
[ Name Nothing "=" ]
(Iden [ Name Nothing "o_orderkey" ])))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "o_orderdate" ])
[ Name Nothing ">=" ]
(TypedLit (TypeName [ Name Nothing "date" ]) "1993-12-01")))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "o_orderdate" ])
[ Name Nothing "<" ]
(BinOp
(TypedLit (TypeName [ Name Nothing "date" ]) "1993-12-01")
[ Name Nothing "+" ]
IntervalLit
{ ilSign = Nothing
, ilLiteral = "3"
, ilFrom = Itf "month" Nothing
, ilTo = Nothing
})))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "l_returnflag" ])
[ Name Nothing "=" ]
(StringLit "'" "'" "R")))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "c_nationkey" ])
[ Name Nothing "=" ]
(Iden [ Name Nothing "n_nationkey" ])))
, qeGroupBy =
[ SimpleGroup (Iden [ Name Nothing "c_custkey" ])
, SimpleGroup (Iden [ Name Nothing "c_name" ])
, SimpleGroup (Iden [ Name Nothing "c_acctbal" ])
, SimpleGroup (Iden [ Name Nothing "c_phone" ])
, SimpleGroup (Iden [ Name Nothing "n_name" ])
, SimpleGroup (Iden [ Name Nothing "c_address" ])
, SimpleGroup (Iden [ Name Nothing "c_comment" ])
]
, qeHaving = Nothing
, qeOrderBy =
[ SortSpec (Iden [ Name Nothing "revenue" ]) Desc NullsOrderDefault
]
, qeOffset = Nothing
, qeFetchFirst = Just (NumLit "20")
}
|
select
ps_partkey,
sum(ps_supplycost * ps_availqty) as value
from
partsupp,
supplier,
nation
where
ps_suppkey = s_suppkey
and s_nationkey = n_nationkey
and n_name = 'CHINA'
group by
ps_partkey having
sum(ps_supplycost * ps_availqty) > (
select
sum(ps_supplycost * ps_availqty) * 0.0001000000
from
partsupp,
supplier,
nation
where
ps_suppkey = s_suppkey
and s_nationkey = n_nationkey
and n_name = 'CHINA'
)
order by
value desc
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( Iden [ Name Nothing "ps_partkey" ] , Nothing )
, ( App
[ Name Nothing "sum" ]
[ BinOp
(Iden [ Name Nothing "ps_supplycost" ])
[ Name Nothing "*" ]
(Iden [ Name Nothing "ps_availqty" ])
]
, Just (Name Nothing "value")
)
]
, qeFrom =
[ TRSimple [ Name Nothing "partsupp" ]
, TRSimple [ Name Nothing "supplier" ]
, TRSimple [ Name Nothing "nation" ]
]
, qeWhere =
Just
(BinOp
(BinOp
(BinOp
(Iden [ Name Nothing "ps_suppkey" ])
[ Name Nothing "=" ]
(Iden [ Name Nothing "s_suppkey" ]))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "s_nationkey" ])
[ Name Nothing "=" ]
(Iden [ Name Nothing "n_nationkey" ])))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "n_name" ])
[ Name Nothing "=" ]
(StringLit "'" "'" "CHINA")))
, qeGroupBy = [ SimpleGroup (Iden [ Name Nothing "ps_partkey" ]) ]
, qeHaving =
Just
(BinOp
(App
[ Name Nothing "sum" ]
[ BinOp
(Iden [ Name Nothing "ps_supplycost" ])
[ Name Nothing "*" ]
(Iden [ Name Nothing "ps_availqty" ])
])
[ Name Nothing ">" ]
(SubQueryExpr
SqSq
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( BinOp
(App
[ Name Nothing "sum" ]
[ BinOp
(Iden [ Name Nothing "ps_supplycost" ])
[ Name Nothing "*" ]
(Iden [ Name Nothing "ps_availqty" ])
])
[ Name Nothing "*" ]
(NumLit "0.0001000000")
, Nothing
)
]
, qeFrom =
[ TRSimple [ Name Nothing "partsupp" ]
, TRSimple [ Name Nothing "supplier" ]
, TRSimple [ Name Nothing "nation" ]
]
, qeWhere =
Just
(BinOp
(BinOp
(BinOp
(Iden [ Name Nothing "ps_suppkey" ])
[ Name Nothing "=" ]
(Iden [ Name Nothing "s_suppkey" ]))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "s_nationkey" ])
[ Name Nothing "=" ]
(Iden [ Name Nothing "n_nationkey" ])))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "n_name" ])
[ Name Nothing "=" ]
(StringLit "'" "'" "CHINA")))
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}))
, qeOrderBy =
[ SortSpec (Iden [ Name Nothing "value" ]) Desc NullsOrderDefault ]
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
select
l_shipmode,
sum(case
when o_orderpriority = '1-URGENT'
or o_orderpriority = '2-HIGH'
then 1
else 0
end) as high_line_count,
sum(case
when o_orderpriority <> '1-URGENT'
and o_orderpriority <> '2-HIGH'
then 1
else 0
end) as low_line_count
from
orders,
lineitem
where
o_orderkey = l_orderkey
and l_shipmode in ('AIR', 'RAIL')
and l_commitdate < l_receiptdate
and l_shipdate < l_commitdate
and l_receiptdate >= date '1994-01-01'
and l_receiptdate < date '1994-01-01' + interval '1' year
group by
l_shipmode
order by
l_shipmode
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( Iden [ Name Nothing "l_shipmode" ] , Nothing )
, ( App
[ Name Nothing "sum" ]
[ Case
{ caseTest = Nothing
, caseWhens =
[ ( [ BinOp
(BinOp
(Iden [ Name Nothing "o_orderpriority" ])
[ Name Nothing "=" ]
(StringLit "'" "'" "1-URGENT"))
[ Name Nothing "or" ]
(BinOp
(Iden [ Name Nothing "o_orderpriority" ])
[ Name Nothing "=" ]
(StringLit "'" "'" "2-HIGH"))
]
, NumLit "1"
)
]
, caseElse = Just (NumLit "0")
}
]
, Just (Name Nothing "high_line_count")
)
, ( App
[ Name Nothing "sum" ]
[ Case
{ caseTest = Nothing
, caseWhens =
[ ( [ BinOp
(BinOp
(Iden [ Name Nothing "o_orderpriority" ])
[ Name Nothing "<>" ]
(StringLit "'" "'" "1-URGENT"))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "o_orderpriority" ])
[ Name Nothing "<>" ]
(StringLit "'" "'" "2-HIGH"))
]
, NumLit "1"
)
]
, caseElse = Just (NumLit "0")
}
]
, Just (Name Nothing "low_line_count")
)
]
, qeFrom =
[ TRSimple [ Name Nothing "orders" ]
, TRSimple [ Name Nothing "lineitem" ]
]
, qeWhere =
Just
(BinOp
(BinOp
(BinOp
(BinOp
(BinOp
(BinOp
(Iden [ Name Nothing "o_orderkey" ])
[ Name Nothing "=" ]
(Iden [ Name Nothing "l_orderkey" ]))
[ Name Nothing "and" ]
(In
True
(Iden [ Name Nothing "l_shipmode" ])
(InList [ StringLit "'" "'" "AIR" , StringLit "'" "'" "RAIL" ])))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "l_commitdate" ])
[ Name Nothing "<" ]
(Iden [ Name Nothing "l_receiptdate" ])))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "l_shipdate" ])
[ Name Nothing "<" ]
(Iden [ Name Nothing "l_commitdate" ])))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "l_receiptdate" ])
[ Name Nothing ">=" ]
(TypedLit (TypeName [ Name Nothing "date" ]) "1994-01-01")))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "l_receiptdate" ])
[ Name Nothing "<" ]
(BinOp
(TypedLit (TypeName [ Name Nothing "date" ]) "1994-01-01")
[ Name Nothing "+" ]
IntervalLit
{ ilSign = Nothing
, ilLiteral = "1"
, ilFrom = Itf "year" Nothing
, ilTo = Nothing
})))
, qeGroupBy = [ SimpleGroup (Iden [ Name Nothing "l_shipmode" ]) ]
, qeHaving = Nothing
, qeOrderBy =
[ SortSpec
(Iden [ Name Nothing "l_shipmode" ]) DirDefault NullsOrderDefault
]
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
select
c_count,
count(*) as custdist
from
(
select
c_custkey,
count(o_orderkey)
from
customer left outer join orders on
c_custkey = o_custkey
and o_comment not like '%pending%requests%'
group by
c_custkey
) as c_orders (c_custkey, c_count)
group by
c_count
order by
custdist desc,
c_count desc
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( Iden [ Name Nothing "c_count" ] , Nothing )
, ( App [ Name Nothing "count" ] [ Star ]
, Just (Name Nothing "custdist")
)
]
, qeFrom =
[ TRAlias
(TRQueryExpr
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( Iden [ Name Nothing "c_custkey" ] , Nothing )
, ( App
[ Name Nothing "count" ] [ Iden [ Name Nothing "o_orderkey" ] ]
, Nothing
)
]
, qeFrom =
[ TRJoin
(TRSimple [ Name Nothing "customer" ])
False
JLeft
(TRSimple [ Name Nothing "orders" ])
(Just
(JoinOn
(BinOp
(BinOp
(Iden [ Name Nothing "c_custkey" ])
[ Name Nothing "=" ]
(Iden [ Name Nothing "o_custkey" ]))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "o_comment" ])
[ Name Nothing "not like" ]
(StringLit "'" "'" "%pending%requests%")))))
]
, qeWhere = Nothing
, qeGroupBy = [ SimpleGroup (Iden [ Name Nothing "c_custkey" ]) ]
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
})
(Alias
(Name Nothing "c_orders")
(Just [ Name Nothing "c_custkey" , Name Nothing "c_count" ]))
]
, qeWhere = Nothing
, qeGroupBy = [ SimpleGroup (Iden [ Name Nothing "c_count" ]) ]
, qeHaving = Nothing
, qeOrderBy =
[ SortSpec
(Iden [ Name Nothing "custdist" ]) Desc NullsOrderDefault
, SortSpec (Iden [ Name Nothing "c_count" ]) Desc NullsOrderDefault
]
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
select
100.00 * sum(case
when p_type like 'PROMO%'
then l_extendedprice * (1 - l_discount)
else 0
end) / sum(l_extendedprice * (1 - l_discount)) as promo_revenue
from
lineitem,
part
where
l_partkey = p_partkey
and l_shipdate >= date '1994-12-01'
and l_shipdate < date '1994-12-01' + interval '1' month
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( BinOp
(BinOp
(NumLit "100.00")
[ Name Nothing "*" ]
(App
[ Name Nothing "sum" ]
[ Case
{ caseTest = Nothing
, caseWhens =
[ ( [ BinOp
(Iden [ Name Nothing "p_type" ])
[ Name Nothing "like" ]
(StringLit "'" "'" "PROMO%")
]
, BinOp
(Iden [ Name Nothing "l_extendedprice" ])
[ Name Nothing "*" ]
(Parens
(BinOp
(NumLit "1")
[ Name Nothing "-" ]
(Iden [ Name Nothing "l_discount" ])))
)
]
, caseElse = Just (NumLit "0")
}
]))
[ Name Nothing "/" ]
(App
[ Name Nothing "sum" ]
[ BinOp
(Iden [ Name Nothing "l_extendedprice" ])
[ Name Nothing "*" ]
(Parens
(BinOp
(NumLit "1")
[ Name Nothing "-" ]
(Iden [ Name Nothing "l_discount" ])))
])
, Just (Name Nothing "promo_revenue")
)
]
, qeFrom =
[ TRSimple [ Name Nothing "lineitem" ]
, TRSimple [ Name Nothing "part" ]
]
, qeWhere =
Just
(BinOp
(BinOp
(BinOp
(Iden [ Name Nothing "l_partkey" ])
[ Name Nothing "=" ]
(Iden [ Name Nothing "p_partkey" ]))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "l_shipdate" ])
[ Name Nothing ">=" ]
(TypedLit (TypeName [ Name Nothing "date" ]) "1994-12-01")))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "l_shipdate" ])
[ Name Nothing "<" ]
(BinOp
(TypedLit (TypeName [ Name Nothing "date" ]) "1994-12-01")
[ Name Nothing "+" ]
IntervalLit
{ ilSign = Nothing
, ilLiteral = "1"
, ilFrom = Itf "month" Nothing
, ilTo = Nothing
})))
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
/*create view revenue0 (supplier_no, total_revenue) as
select
l_suppkey,
sum(l_extendedprice * (1 - l_discount))
from
lineitem
where
l_shipdate >= date '1995-06-01'
and l_shipdate < date '1995-06-01' + interval '3' month
group by
l_suppkey;*/
with
revenue0 as
(select
l_suppkey as supplier_no,
sum(l_extendedprice * (1 - l_discount)) as total_revenue
from
lineitem
where
l_shipdate >= date '1995-06-01'
and l_shipdate < date '1995-06-01' + interval '3' month
group by
l_suppkey)
select
s_suppkey,
s_name,
s_address,
s_phone,
total_revenue
from
supplier,
revenue0
where
s_suppkey = supplier_no
and total_revenue = (
select
max(total_revenue)
from
revenue0
)
order by
s_suppkey
|
With
{ qeWithRecursive = False
, qeViews =
[ ( Alias (Name Nothing "revenue0") Nothing
, Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( Iden [ Name Nothing "l_suppkey" ]
, Just (Name Nothing "supplier_no")
)
, ( App
[ Name Nothing "sum" ]
[ BinOp
(Iden [ Name Nothing "l_extendedprice" ])
[ Name Nothing "*" ]
(Parens
(BinOp
(NumLit "1")
[ Name Nothing "-" ]
(Iden [ Name Nothing "l_discount" ])))
]
, Just (Name Nothing "total_revenue")
)
]
, qeFrom = [ TRSimple [ Name Nothing "lineitem" ] ]
, qeWhere =
Just
(BinOp
(BinOp
(Iden [ Name Nothing "l_shipdate" ])
[ Name Nothing ">=" ]
(TypedLit (TypeName [ Name Nothing "date" ]) "1995-06-01"))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "l_shipdate" ])
[ Name Nothing "<" ]
(BinOp
(TypedLit (TypeName [ Name Nothing "date" ]) "1995-06-01")
[ Name Nothing "+" ]
IntervalLit
{ ilSign = Nothing
, ilLiteral = "3"
, ilFrom = Itf "month" Nothing
, ilTo = Nothing
})))
, qeGroupBy = [ SimpleGroup (Iden [ Name Nothing "l_suppkey" ]) ]
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
)
]
, qeQueryExpression =
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( Iden [ Name Nothing "s_suppkey" ] , Nothing )
, ( Iden [ Name Nothing "s_name" ] , Nothing )
, ( Iden [ Name Nothing "s_address" ] , Nothing )
, ( Iden [ Name Nothing "s_phone" ] , Nothing )
, ( Iden [ Name Nothing "total_revenue" ] , Nothing )
]
, qeFrom =
[ TRSimple [ Name Nothing "supplier" ]
, TRSimple [ Name Nothing "revenue0" ]
]
, qeWhere =
Just
(BinOp
(BinOp
(Iden [ Name Nothing "s_suppkey" ])
[ Name Nothing "=" ]
(Iden [ Name Nothing "supplier_no" ]))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "total_revenue" ])
[ Name Nothing "=" ]
(SubQueryExpr
SqSq
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( App
[ Name Nothing "max" ] [ Iden [ Name Nothing "total_revenue" ] ]
, Nothing
)
]
, qeFrom = [ TRSimple [ Name Nothing "revenue0" ] ]
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
})))
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy =
[ SortSpec
(Iden [ Name Nothing "s_suppkey" ]) DirDefault NullsOrderDefault
]
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
}
|
select
p_brand,
p_type,
p_size,
count(distinct ps_suppkey) as supplier_cnt
from
partsupp,
part
where
p_partkey = ps_partkey
and p_brand <> 'Brand#15'
and p_type not like 'MEDIUM BURNISHED%'
and p_size in (39, 26, 18, 45, 19, 1, 3, 9)
and ps_suppkey not in (
select
s_suppkey
from
supplier
where
s_comment like '%Customer%Complaints%'
)
group by
p_brand,
p_type,
p_size
order by
supplier_cnt desc,
p_brand,
p_type,
p_size
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( Iden [ Name Nothing "p_brand" ] , Nothing )
, ( Iden [ Name Nothing "p_type" ] , Nothing )
, ( Iden [ Name Nothing "p_size" ] , Nothing )
, ( AggregateApp
{ aggName = [ Name Nothing "count" ]
, aggDistinct = Distinct
, aggArgs = [ Iden [ Name Nothing "ps_suppkey" ] ]
, aggOrderBy = []
, aggFilter = Nothing
}
, Just (Name Nothing "supplier_cnt")
)
]
, qeFrom =
[ TRSimple [ Name Nothing "partsupp" ]
, TRSimple [ Name Nothing "part" ]
]
, qeWhere =
Just
(BinOp
(BinOp
(BinOp
(BinOp
(BinOp
(Iden [ Name Nothing "p_partkey" ])
[ Name Nothing "=" ]
(Iden [ Name Nothing "ps_partkey" ]))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "p_brand" ])
[ Name Nothing "<>" ]
(StringLit "'" "'" "Brand#15")))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "p_type" ])
[ Name Nothing "not like" ]
(StringLit "'" "'" "MEDIUM BURNISHED%")))
[ Name Nothing "and" ]
(In
True
(Iden [ Name Nothing "p_size" ])
(InList
[ NumLit "39"
, NumLit "26"
, NumLit "18"
, NumLit "45"
, NumLit "19"
, NumLit "1"
, NumLit "3"
, NumLit "9"
])))
[ Name Nothing "and" ]
(In
False
(Iden [ Name Nothing "ps_suppkey" ])
(InQueryExpr
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( Iden [ Name Nothing "s_suppkey" ] , Nothing ) ]
, qeFrom = [ TRSimple [ Name Nothing "supplier" ] ]
, qeWhere =
Just
(BinOp
(Iden [ Name Nothing "s_comment" ])
[ Name Nothing "like" ]
(StringLit "'" "'" "%Customer%Complaints%"))
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
})))
, qeGroupBy =
[ SimpleGroup (Iden [ Name Nothing "p_brand" ])
, SimpleGroup (Iden [ Name Nothing "p_type" ])
, SimpleGroup (Iden [ Name Nothing "p_size" ])
]
, qeHaving = Nothing
, qeOrderBy =
[ SortSpec
(Iden [ Name Nothing "supplier_cnt" ]) Desc NullsOrderDefault
, SortSpec
(Iden [ Name Nothing "p_brand" ]) DirDefault NullsOrderDefault
, SortSpec
(Iden [ Name Nothing "p_type" ]) DirDefault NullsOrderDefault
, SortSpec
(Iden [ Name Nothing "p_size" ]) DirDefault NullsOrderDefault
]
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
select
sum(l_extendedprice) / 7.0 as avg_yearly
from
lineitem,
part
where
p_partkey = l_partkey
and p_brand = 'Brand#52'
and p_container = 'JUMBO CAN'
and l_quantity < (
select
0.2 * avg(l_quantity)
from
lineitem
where
l_partkey = p_partkey
)
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( BinOp
(App
[ Name Nothing "sum" ] [ Iden [ Name Nothing "l_extendedprice" ] ])
[ Name Nothing "/" ]
(NumLit "7.0")
, Just (Name Nothing "avg_yearly")
)
]
, qeFrom =
[ TRSimple [ Name Nothing "lineitem" ]
, TRSimple [ Name Nothing "part" ]
]
, qeWhere =
Just
(BinOp
(BinOp
(BinOp
(BinOp
(Iden [ Name Nothing "p_partkey" ])
[ Name Nothing "=" ]
(Iden [ Name Nothing "l_partkey" ]))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "p_brand" ])
[ Name Nothing "=" ]
(StringLit "'" "'" "Brand#52")))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "p_container" ])
[ Name Nothing "=" ]
(StringLit "'" "'" "JUMBO CAN")))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "l_quantity" ])
[ Name Nothing "<" ]
(SubQueryExpr
SqSq
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( BinOp
(NumLit "0.2")
[ Name Nothing "*" ]
(App [ Name Nothing "avg" ] [ Iden [ Name Nothing "l_quantity" ] ])
, Nothing
)
]
, qeFrom = [ TRSimple [ Name Nothing "lineitem" ] ]
, qeWhere =
Just
(BinOp
(Iden [ Name Nothing "l_partkey" ])
[ Name Nothing "=" ]
(Iden [ Name Nothing "p_partkey" ]))
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
})))
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
select
c_name,
c_custkey,
o_orderkey,
o_orderdate,
o_totalprice,
sum(l_quantity)
from
customer,
orders,
lineitem
where
o_orderkey in (
select
l_orderkey
from
lineitem
group by
l_orderkey having
sum(l_quantity) > 313
)
and c_custkey = o_custkey
and o_orderkey = l_orderkey
group by
c_name,
c_custkey,
o_orderkey,
o_orderdate,
o_totalprice
order by
o_totalprice desc,
o_orderdate
fetch first 100 rows only
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( Iden [ Name Nothing "c_name" ] , Nothing )
, ( Iden [ Name Nothing "c_custkey" ] , Nothing )
, ( Iden [ Name Nothing "o_orderkey" ] , Nothing )
, ( Iden [ Name Nothing "o_orderdate" ] , Nothing )
, ( Iden [ Name Nothing "o_totalprice" ] , Nothing )
, ( App
[ Name Nothing "sum" ] [ Iden [ Name Nothing "l_quantity" ] ]
, Nothing
)
]
, qeFrom =
[ TRSimple [ Name Nothing "customer" ]
, TRSimple [ Name Nothing "orders" ]
, TRSimple [ Name Nothing "lineitem" ]
]
, qeWhere =
Just
(BinOp
(BinOp
(In
True
(Iden [ Name Nothing "o_orderkey" ])
(InQueryExpr
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( Iden [ Name Nothing "l_orderkey" ] , Nothing ) ]
, qeFrom = [ TRSimple [ Name Nothing "lineitem" ] ]
, qeWhere = Nothing
, qeGroupBy = [ SimpleGroup (Iden [ Name Nothing "l_orderkey" ]) ]
, qeHaving =
Just
(BinOp
(App [ Name Nothing "sum" ] [ Iden [ Name Nothing "l_quantity" ] ])
[ Name Nothing ">" ]
(NumLit "313"))
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "c_custkey" ])
[ Name Nothing "=" ]
(Iden [ Name Nothing "o_custkey" ])))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "o_orderkey" ])
[ Name Nothing "=" ]
(Iden [ Name Nothing "l_orderkey" ])))
, qeGroupBy =
[ SimpleGroup (Iden [ Name Nothing "c_name" ])
, SimpleGroup (Iden [ Name Nothing "c_custkey" ])
, SimpleGroup (Iden [ Name Nothing "o_orderkey" ])
, SimpleGroup (Iden [ Name Nothing "o_orderdate" ])
, SimpleGroup (Iden [ Name Nothing "o_totalprice" ])
]
, qeHaving = Nothing
, qeOrderBy =
[ SortSpec
(Iden [ Name Nothing "o_totalprice" ]) Desc NullsOrderDefault
, SortSpec
(Iden [ Name Nothing "o_orderdate" ]) DirDefault NullsOrderDefault
]
, qeOffset = Nothing
, qeFetchFirst = Just (NumLit "100")
}
|
select
sum(l_extendedprice* (1 - l_discount)) as revenue
from
lineitem,
part
where
(
p_partkey = l_partkey
and p_brand = 'Brand#43'
and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
and l_quantity >= 3 and l_quantity <= 3 + 10
and p_size between 1 and 5
and l_shipmode in ('AIR', 'AIR REG')
and l_shipinstruct = 'DELIVER IN PERSON'
)
or
(
p_partkey = l_partkey
and p_brand = 'Brand#25'
and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
and l_quantity >= 10 and l_quantity <= 10 + 10
and p_size between 1 and 10
and l_shipmode in ('AIR', 'AIR REG')
and l_shipinstruct = 'DELIVER IN PERSON'
)
or
(
p_partkey = l_partkey
and p_brand = 'Brand#24'
and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
and l_quantity >= 22 and l_quantity <= 22 + 10
and p_size between 1 and 15
and l_shipmode in ('AIR', 'AIR REG')
and l_shipinstruct = 'DELIVER IN PERSON'
)
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( App
[ Name Nothing "sum" ]
[ BinOp
(Iden [ Name Nothing "l_extendedprice" ])
[ Name Nothing "*" ]
(Parens
(BinOp
(NumLit "1")
[ Name Nothing "-" ]
(Iden [ Name Nothing "l_discount" ])))
]
, Just (Name Nothing "revenue")
)
]
, qeFrom =
[ TRSimple [ Name Nothing "lineitem" ]
, TRSimple [ Name Nothing "part" ]
]
, qeWhere =
Just
(BinOp
(BinOp
(Parens
(BinOp
(BinOp
(BinOp
(BinOp
(BinOp
(BinOp
(BinOp
(BinOp
(Iden [ Name Nothing "p_partkey" ])
[ Name Nothing "=" ]
(Iden [ Name Nothing "l_partkey" ]))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "p_brand" ])
[ Name Nothing "=" ]
(StringLit "'" "'" "Brand#43")))
[ Name Nothing "and" ]
(In
True
(Iden [ Name Nothing "p_container" ])
(InList
[ StringLit "'" "'" "SM CASE"
, StringLit "'" "'" "SM BOX"
, StringLit "'" "'" "SM PACK"
, StringLit "'" "'" "SM PKG"
])))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "l_quantity" ])
[ Name Nothing ">=" ]
(NumLit "3")))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "l_quantity" ])
[ Name Nothing "<=" ]
(BinOp (NumLit "3") [ Name Nothing "+" ] (NumLit "10"))))
[ Name Nothing "and" ]
(SpecialOp
[ Name Nothing "between" ]
[ Iden [ Name Nothing "p_size" ] , NumLit "1" , NumLit "5" ]))
[ Name Nothing "and" ]
(In
True
(Iden [ Name Nothing "l_shipmode" ])
(InList
[ StringLit "'" "'" "AIR" , StringLit "'" "'" "AIR REG" ])))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "l_shipinstruct" ])
[ Name Nothing "=" ]
(StringLit "'" "'" "DELIVER IN PERSON"))))
[ Name Nothing "or" ]
(Parens
(BinOp
(BinOp
(BinOp
(BinOp
(BinOp
(BinOp
(BinOp
(BinOp
(Iden [ Name Nothing "p_partkey" ])
[ Name Nothing "=" ]
(Iden [ Name Nothing "l_partkey" ]))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "p_brand" ])
[ Name Nothing "=" ]
(StringLit "'" "'" "Brand#25")))
[ Name Nothing "and" ]
(In
True
(Iden [ Name Nothing "p_container" ])
(InList
[ StringLit "'" "'" "MED BAG"
, StringLit "'" "'" "MED BOX"
, StringLit "'" "'" "MED PKG"
, StringLit "'" "'" "MED PACK"
])))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "l_quantity" ])
[ Name Nothing ">=" ]
(NumLit "10")))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "l_quantity" ])
[ Name Nothing "<=" ]
(BinOp (NumLit "10") [ Name Nothing "+" ] (NumLit "10"))))
[ Name Nothing "and" ]
(SpecialOp
[ Name Nothing "between" ]
[ Iden [ Name Nothing "p_size" ] , NumLit "1" , NumLit "10" ]))
[ Name Nothing "and" ]
(In
True
(Iden [ Name Nothing "l_shipmode" ])
(InList
[ StringLit "'" "'" "AIR" , StringLit "'" "'" "AIR REG" ])))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "l_shipinstruct" ])
[ Name Nothing "=" ]
(StringLit "'" "'" "DELIVER IN PERSON")))))
[ Name Nothing "or" ]
(Parens
(BinOp
(BinOp
(BinOp
(BinOp
(BinOp
(BinOp
(BinOp
(BinOp
(Iden [ Name Nothing "p_partkey" ])
[ Name Nothing "=" ]
(Iden [ Name Nothing "l_partkey" ]))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "p_brand" ])
[ Name Nothing "=" ]
(StringLit "'" "'" "Brand#24")))
[ Name Nothing "and" ]
(In
True
(Iden [ Name Nothing "p_container" ])
(InList
[ StringLit "'" "'" "LG CASE"
, StringLit "'" "'" "LG BOX"
, StringLit "'" "'" "LG PACK"
, StringLit "'" "'" "LG PKG"
])))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "l_quantity" ])
[ Name Nothing ">=" ]
(NumLit "22")))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "l_quantity" ])
[ Name Nothing "<=" ]
(BinOp (NumLit "22") [ Name Nothing "+" ] (NumLit "10"))))
[ Name Nothing "and" ]
(SpecialOp
[ Name Nothing "between" ]
[ Iden [ Name Nothing "p_size" ] , NumLit "1" , NumLit "15" ]))
[ Name Nothing "and" ]
(In
True
(Iden [ Name Nothing "l_shipmode" ])
(InList
[ StringLit "'" "'" "AIR" , StringLit "'" "'" "AIR REG" ])))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "l_shipinstruct" ])
[ Name Nothing "=" ]
(StringLit "'" "'" "DELIVER IN PERSON")))))
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
select
s_name,
s_address
from
supplier,
nation
where
s_suppkey in (
select
ps_suppkey
from
partsupp
where
ps_partkey in (
select
p_partkey
from
part
where
p_name like 'lime%'
)
and ps_availqty > (
select
0.5 * sum(l_quantity)
from
lineitem
where
l_partkey = ps_partkey
and l_suppkey = ps_suppkey
and l_shipdate >= date '1994-01-01'
and l_shipdate < date '1994-01-01' + interval '1' year
)
)
and s_nationkey = n_nationkey
and n_name = 'VIETNAM'
order by
s_name
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( Iden [ Name Nothing "s_name" ] , Nothing )
, ( Iden [ Name Nothing "s_address" ] , Nothing )
]
, qeFrom =
[ TRSimple [ Name Nothing "supplier" ]
, TRSimple [ Name Nothing "nation" ]
]
, qeWhere =
Just
(BinOp
(BinOp
(In
True
(Iden [ Name Nothing "s_suppkey" ])
(InQueryExpr
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( Iden [ Name Nothing "ps_suppkey" ] , Nothing ) ]
, qeFrom = [ TRSimple [ Name Nothing "partsupp" ] ]
, qeWhere =
Just
(BinOp
(In
True
(Iden [ Name Nothing "ps_partkey" ])
(InQueryExpr
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( Iden [ Name Nothing "p_partkey" ] , Nothing ) ]
, qeFrom = [ TRSimple [ Name Nothing "part" ] ]
, qeWhere =
Just
(BinOp
(Iden [ Name Nothing "p_name" ])
[ Name Nothing "like" ]
(StringLit "'" "'" "lime%"))
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "ps_availqty" ])
[ Name Nothing ">" ]
(SubQueryExpr
SqSq
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( BinOp
(NumLit "0.5")
[ Name Nothing "*" ]
(App
[ Name Nothing "sum" ]
[ Iden [ Name Nothing "l_quantity" ] ])
, Nothing
)
]
, qeFrom = [ TRSimple [ Name Nothing "lineitem" ] ]
, qeWhere =
Just
(BinOp
(BinOp
(BinOp
(BinOp
(Iden [ Name Nothing "l_partkey" ])
[ Name Nothing "=" ]
(Iden [ Name Nothing "ps_partkey" ]))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "l_suppkey" ])
[ Name Nothing "=" ]
(Iden [ Name Nothing "ps_suppkey" ])))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "l_shipdate" ])
[ Name Nothing ">=" ]
(TypedLit
(TypeName [ Name Nothing "date" ])
"1994-01-01")))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "l_shipdate" ])
[ Name Nothing "<" ]
(BinOp
(TypedLit
(TypeName [ Name Nothing "date" ])
"1994-01-01")
[ Name Nothing "+" ]
IntervalLit
{ ilSign = Nothing
, ilLiteral = "1"
, ilFrom = Itf "year" Nothing
, ilTo = Nothing
})))
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
})))
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "s_nationkey" ])
[ Name Nothing "=" ]
(Iden [ Name Nothing "n_nationkey" ])))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "n_name" ])
[ Name Nothing "=" ]
(StringLit "'" "'" "VIETNAM")))
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy =
[ SortSpec
(Iden [ Name Nothing "s_name" ]) DirDefault NullsOrderDefault
]
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
select
s_name,
count(*) as numwait
from
supplier,
lineitem l1,
orders,
nation
where
s_suppkey = l1.l_suppkey
and o_orderkey = l1.l_orderkey
and o_orderstatus = 'F'
and l1.l_receiptdate > l1.l_commitdate
and exists (
select
*
from
lineitem l2
where
l2.l_orderkey = l1.l_orderkey
and l2.l_suppkey <> l1.l_suppkey
)
and not exists (
select
*
from
lineitem l3
where
l3.l_orderkey = l1.l_orderkey
and l3.l_suppkey <> l1.l_suppkey
and l3.l_receiptdate > l3.l_commitdate
)
and s_nationkey = n_nationkey
and n_name = 'INDIA'
group by
s_name
order by
numwait desc,
s_name
fetch first 100 rows only
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( Iden [ Name Nothing "s_name" ] , Nothing )
, ( App [ Name Nothing "count" ] [ Star ]
, Just (Name Nothing "numwait")
)
]
, qeFrom =
[ TRSimple [ Name Nothing "supplier" ]
, TRAlias
(TRSimple [ Name Nothing "lineitem" ])
(Alias (Name Nothing "l1") Nothing)
, TRSimple [ Name Nothing "orders" ]
, TRSimple [ Name Nothing "nation" ]
]
, qeWhere =
Just
(BinOp
(BinOp
(BinOp
(BinOp
(BinOp
(BinOp
(BinOp
(BinOp
(Iden [ Name Nothing "s_suppkey" ])
[ Name Nothing "=" ]
(Iden [ Name Nothing "l1" , Name Nothing "l_suppkey" ]))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "o_orderkey" ])
[ Name Nothing "=" ]
(Iden [ Name Nothing "l1" , Name Nothing "l_orderkey" ])))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "o_orderstatus" ])
[ Name Nothing "=" ]
(StringLit "'" "'" "F")))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "l1" , Name Nothing "l_receiptdate" ])
[ Name Nothing ">" ]
(Iden [ Name Nothing "l1" , Name Nothing "l_commitdate" ])))
[ Name Nothing "and" ]
(SubQueryExpr
SqExists
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Star , Nothing ) ]
, qeFrom =
[ TRAlias
(TRSimple [ Name Nothing "lineitem" ])
(Alias (Name Nothing "l2") Nothing)
]
, qeWhere =
Just
(BinOp
(BinOp
(Iden [ Name Nothing "l2" , Name Nothing "l_orderkey" ])
[ Name Nothing "=" ]
(Iden [ Name Nothing "l1" , Name Nothing "l_orderkey" ]))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "l2" , Name Nothing "l_suppkey" ])
[ Name Nothing "<>" ]
(Iden [ Name Nothing "l1" , Name Nothing "l_suppkey" ])))
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}))
[ Name Nothing "and" ]
(PrefixOp
[ Name Nothing "not" ]
(SubQueryExpr
SqExists
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Star , Nothing ) ]
, qeFrom =
[ TRAlias
(TRSimple [ Name Nothing "lineitem" ])
(Alias (Name Nothing "l3") Nothing)
]
, qeWhere =
Just
(BinOp
(BinOp
(BinOp
(Iden [ Name Nothing "l3" , Name Nothing "l_orderkey" ])
[ Name Nothing "=" ]
(Iden [ Name Nothing "l1" , Name Nothing "l_orderkey" ]))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "l3" , Name Nothing "l_suppkey" ])
[ Name Nothing "<>" ]
(Iden [ Name Nothing "l1" , Name Nothing "l_suppkey" ])))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "l3" , Name Nothing "l_receiptdate" ])
[ Name Nothing ">" ]
(Iden [ Name Nothing "l3" , Name Nothing "l_commitdate" ])))
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
})))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "s_nationkey" ])
[ Name Nothing "=" ]
(Iden [ Name Nothing "n_nationkey" ])))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "n_name" ])
[ Name Nothing "=" ]
(StringLit "'" "'" "INDIA")))
, qeGroupBy = [ SimpleGroup (Iden [ Name Nothing "s_name" ]) ]
, qeHaving = Nothing
, qeOrderBy =
[ SortSpec (Iden [ Name Nothing "numwait" ]) Desc NullsOrderDefault
, SortSpec
(Iden [ Name Nothing "s_name" ]) DirDefault NullsOrderDefault
]
, qeOffset = Nothing
, qeFetchFirst = Just (NumLit "100")
}
|
select
cntrycode,
count(*) as numcust,
sum(c_acctbal) as totacctbal
from
(
select
substring(c_phone from 1 for 2) as cntrycode,
c_acctbal
from
customer
where
substring(c_phone from 1 for 2) in
('41', '28', '39', '21', '24', '29', '44')
and c_acctbal > (
select
avg(c_acctbal)
from
customer
where
c_acctbal > 0.00
and substring(c_phone from 1 for 2) in
('41', '28', '39', '21', '24', '29', '44')
)
and not exists (
select
*
from
orders
where
o_custkey = c_custkey
)
) as custsale
group by
cntrycode
order by
cntrycode
|
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( Iden [ Name Nothing "cntrycode" ] , Nothing )
, ( App [ Name Nothing "count" ] [ Star ]
, Just (Name Nothing "numcust")
)
, ( App
[ Name Nothing "sum" ] [ Iden [ Name Nothing "c_acctbal" ] ]
, Just (Name Nothing "totacctbal")
)
]
, qeFrom =
[ TRAlias
(TRQueryExpr
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( SpecialOpK
[ Name Nothing "substring" ]
(Just (Iden [ Name Nothing "c_phone" ]))
[ ( "from" , NumLit "1" ) , ( "for" , NumLit "2" ) ]
, Just (Name Nothing "cntrycode")
)
, ( Iden [ Name Nothing "c_acctbal" ] , Nothing )
]
, qeFrom = [ TRSimple [ Name Nothing "customer" ] ]
, qeWhere =
Just
(BinOp
(BinOp
(In
True
(SpecialOpK
[ Name Nothing "substring" ]
(Just (Iden [ Name Nothing "c_phone" ]))
[ ( "from" , NumLit "1" ) , ( "for" , NumLit "2" ) ])
(InList
[ StringLit "'" "'" "41"
, StringLit "'" "'" "28"
, StringLit "'" "'" "39"
, StringLit "'" "'" "21"
, StringLit "'" "'" "24"
, StringLit "'" "'" "29"
, StringLit "'" "'" "44"
]))
[ Name Nothing "and" ]
(BinOp
(Iden [ Name Nothing "c_acctbal" ])
[ Name Nothing ">" ]
(SubQueryExpr
SqSq
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( App
[ Name Nothing "avg" ]
[ Iden [ Name Nothing "c_acctbal" ] ]
, Nothing
)
]
, qeFrom = [ TRSimple [ Name Nothing "customer" ] ]
, qeWhere =
Just
(BinOp
(BinOp
(Iden [ Name Nothing "c_acctbal" ])
[ Name Nothing ">" ]
(NumLit "0.00"))
[ Name Nothing "and" ]
(In
True
(SpecialOpK
[ Name Nothing "substring" ]
(Just (Iden [ Name Nothing "c_phone" ]))
[ ( "from" , NumLit "1" )
, ( "for" , NumLit "2" )
])
(InList
[ StringLit "'" "'" "41"
, StringLit "'" "'" "28"
, StringLit "'" "'" "39"
, StringLit "'" "'" "21"
, StringLit "'" "'" "24"
, StringLit "'" "'" "29"
, StringLit "'" "'" "44"
])))
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
})))
[ Name Nothing "and" ]
(PrefixOp
[ Name Nothing "not" ]
(SubQueryExpr
SqExists
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Star , Nothing ) ]
, qeFrom = [ TRSimple [ Name Nothing "orders" ] ]
, qeWhere =
Just
(BinOp
(Iden [ Name Nothing "o_custkey" ])
[ Name Nothing "=" ]
(Iden [ Name Nothing "c_custkey" ]))
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
})))
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
})
(Alias (Name Nothing "custsale") Nothing)
]
, qeWhere = Nothing
, qeGroupBy = [ SimpleGroup (Iden [ Name Nothing "cntrycode" ]) ]
, qeHaving = Nothing
, qeOrderBy =
[ SortSpec
(Iden [ Name Nothing "cntrycode" ]) DirDefault NullsOrderDefault
]
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
|
|
|
App [ Name Nothing "count" ] [ Star ]
|
count(*) filter (where something > 5)
|
AggregateApp
{ aggName = [ Name Nothing "count" ]
, aggDistinct = SQDefault
, aggArgs = [ Star ]
, aggOrderBy = []
, aggFilter =
Just
(BinOp
(Iden [ Name Nothing "something" ])
[ Name Nothing ">" ]
(NumLit "5"))
}
|
|
|
App [ Name Nothing "count" ] [ Iden [ Name Nothing "a" ] ]
|
|
|
AggregateApp
{ aggName = [ Name Nothing "count" ]
, aggDistinct = Distinct
, aggArgs = [ Iden [ Name Nothing "a" ] ]
, aggOrderBy = []
, aggFilter = Nothing
}
|
|
|
AggregateApp
{ aggName = [ Name Nothing "count" ]
, aggDistinct = All
, aggArgs = [ Iden [ Name Nothing "a" ] ]
, aggOrderBy = []
, aggFilter = Nothing
}
|
count(all a) filter (where something > 5)
|
AggregateApp
{ aggName = [ Name Nothing "count" ]
, aggDistinct = All
, aggArgs = [ Iden [ Name Nothing "a" ] ]
, aggOrderBy = []
, aggFilter =
Just
(BinOp
(Iden [ Name Nothing "something" ])
[ Name Nothing ">" ]
(NumLit "5"))
}
|
|
|
App [ Name Nothing "avg" ] [ Iden [ Name Nothing "a" ] ]
|
|
|
AggregateApp
{ aggName = [ Name Nothing "avg" ]
, aggDistinct = Distinct
, aggArgs = [ Iden [ Name Nothing "a" ] ]
, aggOrderBy = []
, aggFilter = Nothing
}
|
|
|
App [ Name Nothing "max" ] [ Iden [ Name Nothing "a" ] ]
|
|
|
AggregateApp
{ aggName = [ Name Nothing "max" ]
, aggDistinct = Distinct
, aggArgs = [ Iden [ Name Nothing "a" ] ]
, aggOrderBy = []
, aggFilter = Nothing
}
|
|
|
App [ Name Nothing "min" ] [ Iden [ Name Nothing "a" ] ]
|
|
|
AggregateApp
{ aggName = [ Name Nothing "min" ]
, aggDistinct = Distinct
, aggArgs = [ Iden [ Name Nothing "a" ] ]
, aggOrderBy = []
, aggFilter = Nothing
}
|
|
|
App [ Name Nothing "sum" ] [ Iden [ Name Nothing "a" ] ]
|
|
|
AggregateApp
{ aggName = [ Name Nothing "sum" ]
, aggDistinct = Distinct
, aggArgs = [ Iden [ Name Nothing "a" ] ]
, aggOrderBy = []
, aggFilter = Nothing
}
|
|
|
App [ Name Nothing "every" ] [ Iden [ Name Nothing "a" ] ]
|
|
|
AggregateApp
{ aggName = [ Name Nothing "every" ]
, aggDistinct = Distinct
, aggArgs = [ Iden [ Name Nothing "a" ] ]
, aggOrderBy = []
, aggFilter = Nothing
}
|
|
|
App [ Name Nothing "any" ] [ Iden [ Name Nothing "a" ] ]
|
|
|
AggregateApp
{ aggName = [ Name Nothing "any" ]
, aggDistinct = Distinct
, aggArgs = [ Iden [ Name Nothing "a" ] ]
, aggOrderBy = []
, aggFilter = Nothing
}
|
|
|
App [ Name Nothing "some" ] [ Iden [ Name Nothing "a" ] ]
|
|
|
AggregateApp
{ aggName = [ Name Nothing "some" ]
, aggDistinct = Distinct
, aggArgs = [ Iden [ Name Nothing "a" ] ]
, aggOrderBy = []
, aggFilter = Nothing
}
|
|
|
App [ Name Nothing "stddev_pop" ] [ Iden [ Name Nothing "a" ] ]
|
|
|
AggregateApp
{ aggName = [ Name Nothing "stddev_pop" ]
, aggDistinct = Distinct
, aggArgs = [ Iden [ Name Nothing "a" ] ]
, aggOrderBy = []
, aggFilter = Nothing
}
|
|
|
App [ Name Nothing "stddev_samp" ] [ Iden [ Name Nothing "a" ] ]
|
|
|
AggregateApp
{ aggName = [ Name Nothing "stddev_samp" ]
, aggDistinct = Distinct
, aggArgs = [ Iden [ Name Nothing "a" ] ]
, aggOrderBy = []
, aggFilter = Nothing
}
|
|
|
App [ Name Nothing "var_samp" ] [ Iden [ Name Nothing "a" ] ]
|
|
|
AggregateApp
{ aggName = [ Name Nothing "var_samp" ]
, aggDistinct = Distinct
, aggArgs = [ Iden [ Name Nothing "a" ] ]
, aggOrderBy = []
, aggFilter = Nothing
}
|
|
|
App [ Name Nothing "var_pop" ] [ Iden [ Name Nothing "a" ] ]
|
|
|
AggregateApp
{ aggName = [ Name Nothing "var_pop" ]
, aggDistinct = Distinct
, aggArgs = [ Iden [ Name Nothing "a" ] ]
, aggOrderBy = []
, aggFilter = Nothing
}
|
|
|
App [ Name Nothing "collect" ] [ Iden [ Name Nothing "a" ] ]
|
|
|
AggregateApp
{ aggName = [ Name Nothing "collect" ]
, aggDistinct = Distinct
, aggArgs = [ Iden [ Name Nothing "a" ] ]
, aggOrderBy = []
, aggFilter = Nothing
}
|
|
|
App [ Name Nothing "fusion" ] [ Iden [ Name Nothing "a" ] ]
|
|
|
AggregateApp
{ aggName = [ Name Nothing "fusion" ]
, aggDistinct = Distinct
, aggArgs = [ Iden [ Name Nothing "a" ] ]
, aggOrderBy = []
, aggFilter = Nothing
}
|
|
|
App [ Name Nothing "intersection" ] [ Iden [ Name Nothing "a" ] ]
|
|
|
AggregateApp
{ aggName = [ Name Nothing "intersection" ]
, aggDistinct = Distinct
, aggArgs = [ Iden [ Name Nothing "a" ] ]
, aggOrderBy = []
, aggFilter = Nothing
}
|
|
|
App
[ Name Nothing "COVAR_POP" ]
[ Iden [ Name Nothing "a" ] , Iden [ Name Nothing "b" ] ]
|
COVAR_POP(a,b) filter (where something > 5)
|
AggregateApp
{ aggName = [ Name Nothing "COVAR_POP" ]
, aggDistinct = SQDefault
, aggArgs =
[ Iden [ Name Nothing "a" ] , Iden [ Name Nothing "b" ] ]
, aggOrderBy = []
, aggFilter =
Just
(BinOp
(Iden [ Name Nothing "something" ])
[ Name Nothing ">" ]
(NumLit "5"))
}
|
|
|
App
[ Name Nothing "COVAR_SAMP" ]
[ Iden [ Name Nothing "a" ] , Iden [ Name Nothing "b" ] ]
|
COVAR_SAMP(a,b) filter (where something > 5)
|
AggregateApp
{ aggName = [ Name Nothing "COVAR_SAMP" ]
, aggDistinct = SQDefault
, aggArgs =
[ Iden [ Name Nothing "a" ] , Iden [ Name Nothing "b" ] ]
, aggOrderBy = []
, aggFilter =
Just
(BinOp
(Iden [ Name Nothing "something" ])
[ Name Nothing ">" ]
(NumLit "5"))
}
|
|
|
App
[ Name Nothing "CORR" ]
[ Iden [ Name Nothing "a" ] , Iden [ Name Nothing "b" ] ]
|
CORR(a,b) filter (where something > 5)
|
AggregateApp
{ aggName = [ Name Nothing "CORR" ]
, aggDistinct = SQDefault
, aggArgs =
[ Iden [ Name Nothing "a" ] , Iden [ Name Nothing "b" ] ]
, aggOrderBy = []
, aggFilter =
Just
(BinOp
(Iden [ Name Nothing "something" ])
[ Name Nothing ">" ]
(NumLit "5"))
}
|
|
|
App
[ Name Nothing "REGR_SLOPE" ]
[ Iden [ Name Nothing "a" ] , Iden [ Name Nothing "b" ] ]
|
REGR_SLOPE(a,b) filter (where something > 5)
|
AggregateApp
{ aggName = [ Name Nothing "REGR_SLOPE" ]
, aggDistinct = SQDefault
, aggArgs =
[ Iden [ Name Nothing "a" ] , Iden [ Name Nothing "b" ] ]
, aggOrderBy = []
, aggFilter =
Just
(BinOp
(Iden [ Name Nothing "something" ])
[ Name Nothing ">" ]
(NumLit "5"))
}
|
|
|
App
[ Name Nothing "REGR_INTERCEPT" ]
[ Iden [ Name Nothing "a" ] , Iden [ Name Nothing "b" ] ]
|
REGR_INTERCEPT(a,b) filter (where something > 5)
|
AggregateApp
{ aggName = [ Name Nothing "REGR_INTERCEPT" ]
, aggDistinct = SQDefault
, aggArgs =
[ Iden [ Name Nothing "a" ] , Iden [ Name Nothing "b" ] ]
, aggOrderBy = []
, aggFilter =
Just
(BinOp
(Iden [ Name Nothing "something" ])
[ Name Nothing ">" ]
(NumLit "5"))
}
|
|
|
App
[ Name Nothing "REGR_COUNT" ]
[ Iden [ Name Nothing "a" ] , Iden [ Name Nothing "b" ] ]
|
REGR_COUNT(a,b) filter (where something > 5)
|
AggregateApp
{ aggName = [ Name Nothing "REGR_COUNT" ]
, aggDistinct = SQDefault
, aggArgs =
[ Iden [ Name Nothing "a" ] , Iden [ Name Nothing "b" ] ]
, aggOrderBy = []
, aggFilter =
Just
(BinOp
(Iden [ Name Nothing "something" ])
[ Name Nothing ">" ]
(NumLit "5"))
}
|
|
|
App
[ Name Nothing "REGR_R2" ]
[ Iden [ Name Nothing "a" ] , Iden [ Name Nothing "b" ] ]
|
REGR_R2(a,b) filter (where something > 5)
|
AggregateApp
{ aggName = [ Name Nothing "REGR_R2" ]
, aggDistinct = SQDefault
, aggArgs =
[ Iden [ Name Nothing "a" ] , Iden [ Name Nothing "b" ] ]
, aggOrderBy = []
, aggFilter =
Just
(BinOp
(Iden [ Name Nothing "something" ])
[ Name Nothing ">" ]
(NumLit "5"))
}
|
|
|
App
[ Name Nothing "REGR_AVGX" ]
[ Iden [ Name Nothing "a" ] , Iden [ Name Nothing "b" ] ]
|
REGR_AVGX(a,b) filter (where something > 5)
|
AggregateApp
{ aggName = [ Name Nothing "REGR_AVGX" ]
, aggDistinct = SQDefault
, aggArgs =
[ Iden [ Name Nothing "a" ] , Iden [ Name Nothing "b" ] ]
, aggOrderBy = []
, aggFilter =
Just
(BinOp
(Iden [ Name Nothing "something" ])
[ Name Nothing ">" ]
(NumLit "5"))
}
|
|
|
App
[ Name Nothing "REGR_AVGY" ]
[ Iden [ Name Nothing "a" ] , Iden [ Name Nothing "b" ] ]
|
REGR_AVGY(a,b) filter (where something > 5)
|
AggregateApp
{ aggName = [ Name Nothing "REGR_AVGY" ]
, aggDistinct = SQDefault
, aggArgs =
[ Iden [ Name Nothing "a" ] , Iden [ Name Nothing "b" ] ]
, aggOrderBy = []
, aggFilter =
Just
(BinOp
(Iden [ Name Nothing "something" ])
[ Name Nothing ">" ]
(NumLit "5"))
}
|
|
|
App
[ Name Nothing "REGR_SXX" ]
[ Iden [ Name Nothing "a" ] , Iden [ Name Nothing "b" ] ]
|
REGR_SXX(a,b) filter (where something > 5)
|
AggregateApp
{ aggName = [ Name Nothing "REGR_SXX" ]
, aggDistinct = SQDefault
, aggArgs =
[ Iden [ Name Nothing "a" ] , Iden [ Name Nothing "b" ] ]
, aggOrderBy = []
, aggFilter =
Just
(BinOp
(Iden [ Name Nothing "something" ])
[ Name Nothing ">" ]
(NumLit "5"))
}
|
|
|
App
[ Name Nothing "REGR_SYY" ]
[ Iden [ Name Nothing "a" ] , Iden [ Name Nothing "b" ] ]
|
REGR_SYY(a,b) filter (where something > 5)
|
AggregateApp
{ aggName = [ Name Nothing "REGR_SYY" ]
, aggDistinct = SQDefault
, aggArgs =
[ Iden [ Name Nothing "a" ] , Iden [ Name Nothing "b" ] ]
, aggOrderBy = []
, aggFilter =
Just
(BinOp
(Iden [ Name Nothing "something" ])
[ Name Nothing ">" ]
(NumLit "5"))
}
|
|
|
App
[ Name Nothing "REGR_SXY" ]
[ Iden [ Name Nothing "a" ] , Iden [ Name Nothing "b" ] ]
|
REGR_SXY(a,b) filter (where something > 5)
|
AggregateApp
{ aggName = [ Name Nothing "REGR_SXY" ]
, aggDistinct = SQDefault
, aggArgs =
[ Iden [ Name Nothing "a" ] , Iden [ Name Nothing "b" ] ]
, aggOrderBy = []
, aggFilter =
Just
(BinOp
(Iden [ Name Nothing "something" ])
[ Name Nothing ">" ]
(NumLit "5"))
}
|
rank(a,c) within group (order by b)
|
AggregateAppGroup
{ aggName = [ Name Nothing "rank" ]
, aggArgs =
[ Iden [ Name Nothing "a" ] , Iden [ Name Nothing "c" ] ]
, aggGroup =
[ SortSpec (Iden [ Name Nothing "b" ]) DirDefault NullsOrderDefault
]
}
|
dense_rank(a) within group (order by b)
|
AggregateAppGroup
{ aggName = [ Name Nothing "dense_rank" ]
, aggArgs = [ Iden [ Name Nothing "a" ] ]
, aggGroup =
[ SortSpec (Iden [ Name Nothing "b" ]) DirDefault NullsOrderDefault
]
}
|
percent_rank(a) within group (order by b)
|
AggregateAppGroup
{ aggName = [ Name Nothing "percent_rank" ]
, aggArgs = [ Iden [ Name Nothing "a" ] ]
, aggGroup =
[ SortSpec (Iden [ Name Nothing "b" ]) DirDefault NullsOrderDefault
]
}
|
cume_dist(a) within group (order by b)
|
AggregateAppGroup
{ aggName = [ Name Nothing "cume_dist" ]
, aggArgs = [ Iden [ Name Nothing "a" ] ]
, aggGroup =
[ SortSpec (Iden [ Name Nothing "b" ]) DirDefault NullsOrderDefault
]
}
|
percentile_cont(a) within group (order by b)
|
AggregateAppGroup
{ aggName = [ Name Nothing "percentile_cont" ]
, aggArgs = [ Iden [ Name Nothing "a" ] ]
, aggGroup =
[ SortSpec (Iden [ Name Nothing "b" ]) DirDefault NullsOrderDefault
]
}
|
percentile_disc(a) within group (order by b)
|
AggregateAppGroup
{ aggName = [ Name Nothing "percentile_disc" ]
, aggArgs = [ Iden [ Name Nothing "a" ] ]
, aggGroup =
[ SortSpec (Iden [ Name Nothing "b" ]) DirDefault NullsOrderDefault
]
}
|
|
|
App [ Name Nothing "array_agg" ] [ Iden [ Name Nothing "a" ] ]
|
|
|
AggregateApp
{ aggName = [ Name Nothing "array_agg" ]
, aggDistinct = SQDefault
, aggArgs = [ Iden [ Name Nothing "a" ] ]
, aggOrderBy =
[ SortSpec (Iden [ Name Nothing "z" ]) DirDefault NullsOrderDefault
]
, aggFilter = Nothing
}
|
|
|
CreateSchema [ Name Nothing "my_schema" ]
|
|
|
DropSchema [ Name Nothing "my_schema" ] DefaultDropBehaviour
|
drop schema my_schema cascade
|
DropSchema [ Name Nothing "my_schema" ] Cascade
|
drop schema my_schema restrict
|
DropSchema [ Name Nothing "my_schema" ] Restrict
|
create table t (a int, b int);
|
CreateTable
[ Name Nothing "t" ]
[ TableColumnDef
(ColumnDef
(Name Nothing "a") (Just (TypeName [ Name Nothing "int" ])) [])
, TableColumnDef
(ColumnDef
(Name Nothing "b") (Just (TypeName [ Name Nothing "int" ])) [])
]
False
|
create table t (a int not null);
|
CreateTable
[ Name Nothing "t" ]
[ TableColumnDef
(ColumnDef
(Name Nothing "a")
(Just (TypeName [ Name Nothing "int" ]))
[ ColConstraintDef Nothing ColNotNullConstraint ])
]
False
|
create table t (a int constraint a_not_null not null);
|
CreateTable
[ Name Nothing "t" ]
[ TableColumnDef
(ColumnDef
(Name Nothing "a")
(Just (TypeName [ Name Nothing "int" ]))
[ ColConstraintDef
(Just [ Name Nothing "a_not_null" ]) ColNotNullConstraint
])
]
False
|
create table t (a int unique);
|
CreateTable
[ Name Nothing "t" ]
[ TableColumnDef
(ColumnDef
(Name Nothing "a")
(Just (TypeName [ Name Nothing "int" ]))
[ ColConstraintDef Nothing ColUniqueConstraint ])
]
False
|
create table t (a int primary key);
|
CreateTable
[ Name Nothing "t" ]
[ TableColumnDef
(ColumnDef
(Name Nothing "a")
(Just (TypeName [ Name Nothing "int" ]))
[ ColConstraintDef Nothing (ColPrimaryKeyConstraint False) ])
]
False
|
create table t (a int primary key autoincrement);
|
CreateTable
[ Name Nothing "t" ]
[ TableColumnDef
(ColumnDef
(Name Nothing "a")
(Just (TypeName [ Name Nothing "int" ]))
[ ColConstraintDef Nothing (ColPrimaryKeyConstraint True) ])
]
False
|
create table t (a int references u);
|
CreateTable
[ Name Nothing "t" ]
[ TableColumnDef
(ColumnDef
(Name Nothing "a")
(Just (TypeName [ Name Nothing "int" ]))
[ ColConstraintDef
Nothing
(ColReferencesConstraint
[ Name Nothing "u" ]
Nothing
DefaultReferenceMatch
DefaultReferentialAction
DefaultReferentialAction)
])
]
False
|
create table t (a int references u(a));
|
CreateTable
[ Name Nothing "t" ]
[ TableColumnDef
(ColumnDef
(Name Nothing "a")
(Just (TypeName [ Name Nothing "int" ]))
[ ColConstraintDef
Nothing
(ColReferencesConstraint
[ Name Nothing "u" ]
(Just (Name Nothing "a"))
DefaultReferenceMatch
DefaultReferentialAction
DefaultReferentialAction)
])
]
False
|
create table t (a int references u match full);
|
CreateTable
[ Name Nothing "t" ]
[ TableColumnDef
(ColumnDef
(Name Nothing "a")
(Just (TypeName [ Name Nothing "int" ]))
[ ColConstraintDef
Nothing
(ColReferencesConstraint
[ Name Nothing "u" ]
Nothing
MatchFull
DefaultReferentialAction
DefaultReferentialAction)
])
]
False
|
create table t (a int references u match partial);
|
CreateTable
[ Name Nothing "t" ]
[ TableColumnDef
(ColumnDef
(Name Nothing "a")
(Just (TypeName [ Name Nothing "int" ]))
[ ColConstraintDef
Nothing
(ColReferencesConstraint
[ Name Nothing "u" ]
Nothing
MatchPartial
DefaultReferentialAction
DefaultReferentialAction)
])
]
False
|
create table t (a int references u match simple);
|
CreateTable
[ Name Nothing "t" ]
[ TableColumnDef
(ColumnDef
(Name Nothing "a")
(Just (TypeName [ Name Nothing "int" ]))
[ ColConstraintDef
Nothing
(ColReferencesConstraint
[ Name Nothing "u" ]
Nothing
MatchSimple
DefaultReferentialAction
DefaultReferentialAction)
])
]
False
|
create table t (a int references u on update cascade );
|
CreateTable
[ Name Nothing "t" ]
[ TableColumnDef
(ColumnDef
(Name Nothing "a")
(Just (TypeName [ Name Nothing "int" ]))
[ ColConstraintDef
Nothing
(ColReferencesConstraint
[ Name Nothing "u" ]
Nothing
DefaultReferenceMatch
RefCascade
DefaultReferentialAction)
])
]
False
|
create table t (a int references u on update set null );
|
CreateTable
[ Name Nothing "t" ]
[ TableColumnDef
(ColumnDef
(Name Nothing "a")
(Just (TypeName [ Name Nothing "int" ]))
[ ColConstraintDef
Nothing
(ColReferencesConstraint
[ Name Nothing "u" ]
Nothing
DefaultReferenceMatch
RefSetNull
DefaultReferentialAction)
])
]
False
|
create table t (a int references u on update set default );
|
CreateTable
[ Name Nothing "t" ]
[ TableColumnDef
(ColumnDef
(Name Nothing "a")
(Just (TypeName [ Name Nothing "int" ]))
[ ColConstraintDef
Nothing
(ColReferencesConstraint
[ Name Nothing "u" ]
Nothing
DefaultReferenceMatch
RefSetDefault
DefaultReferentialAction)
])
]
False
|
create table t (a int references u on update no action );
|
CreateTable
[ Name Nothing "t" ]
[ TableColumnDef
(ColumnDef
(Name Nothing "a")
(Just (TypeName [ Name Nothing "int" ]))
[ ColConstraintDef
Nothing
(ColReferencesConstraint
[ Name Nothing "u" ]
Nothing
DefaultReferenceMatch
RefNoAction
DefaultReferentialAction)
])
]
False
|
create table t (a int references u on delete cascade );
|
CreateTable
[ Name Nothing "t" ]
[ TableColumnDef
(ColumnDef
(Name Nothing "a")
(Just (TypeName [ Name Nothing "int" ]))
[ ColConstraintDef
Nothing
(ColReferencesConstraint
[ Name Nothing "u" ]
Nothing
DefaultReferenceMatch
DefaultReferentialAction
RefCascade)
])
]
False
|
create table t (a int references u on update cascade on delete restrict );
|
CreateTable
[ Name Nothing "t" ]
[ TableColumnDef
(ColumnDef
(Name Nothing "a")
(Just (TypeName [ Name Nothing "int" ]))
[ ColConstraintDef
Nothing
(ColReferencesConstraint
[ Name Nothing "u" ]
Nothing
DefaultReferenceMatch
RefCascade
RefRestrict)
])
]
False
|
create table t (a int references u on delete restrict on update cascade );
|
CreateTable
[ Name Nothing "t" ]
[ TableColumnDef
(ColumnDef
(Name Nothing "a")
(Just (TypeName [ Name Nothing "int" ]))
[ ColConstraintDef
Nothing
(ColReferencesConstraint
[ Name Nothing "u" ]
Nothing
DefaultReferenceMatch
RefCascade
RefRestrict)
])
]
False
|
create table t (a int check (a>5));
|
CreateTable
[ Name Nothing "t" ]
[ TableColumnDef
(ColumnDef
(Name Nothing "a")
(Just (TypeName [ Name Nothing "int" ]))
[ ColConstraintDef
Nothing
(ColCheckConstraint
(BinOp
(Iden [ Name Nothing "a" ]) [ Name Nothing ">" ] (NumLit "5")))
])
]
False
|
create table t (a int generated always as identity);
|
CreateTable
[ Name Nothing "t" ]
[ TableColumnDef
(ColumnDef
(Name Nothing "a")
(Just (TypeName [ Name Nothing "int" ]))
[ ColConstraintDef
Nothing (ColDefaultClause (IdentityColumnSpec GeneratedAlways []))
])
]
False
|
create table t (a int generated by default as identity);
|
CreateTable
[ Name Nothing "t" ]
[ TableColumnDef
(ColumnDef
(Name Nothing "a")
(Just (TypeName [ Name Nothing "int" ]))
[ ColConstraintDef
Nothing
(ColDefaultClause (IdentityColumnSpec GeneratedByDefault []))
])
]
False
|
create table t (a int generated always as identity
( start with 5 increment by 5 maxvalue 500 minvalue 5 cycle ));
|
CreateTable
[ Name Nothing "t" ]
[ TableColumnDef
(ColumnDef
(Name Nothing "a")
(Just (TypeName [ Name Nothing "int" ]))
[ ColConstraintDef
Nothing
(ColDefaultClause
(IdentityColumnSpec
GeneratedAlways
[ SGOStartWith 5
, SGOIncrementBy 5
, SGOMaxValue 500
, SGOMinValue 5
, SGOCycle
]))
])
]
False
|
create table t (a int generated always as identity
( start with -4 no maxvalue no minvalue no cycle ));
|
CreateTable
[ Name Nothing "t" ]
[ TableColumnDef
(ColumnDef
(Name Nothing "a")
(Just (TypeName [ Name Nothing "int" ]))
[ ColConstraintDef
Nothing
(ColDefaultClause
(IdentityColumnSpec
GeneratedAlways
[ SGOStartWith (-4)
, SGONoMaxValue
, SGONoMinValue
, SGONoCycle
]))
])
]
False
|
create table t (a int,
a2 int generated always as (a * 2));
|
CreateTable
[ Name Nothing "t" ]
[ TableColumnDef
(ColumnDef
(Name Nothing "a") (Just (TypeName [ Name Nothing "int" ])) [])
, TableColumnDef
(ColumnDef
(Name Nothing "a2")
(Just (TypeName [ Name Nothing "int" ]))
[ ColConstraintDef
Nothing
(ColDefaultClause
(GenerationClause
(BinOp
(Iden [ Name Nothing "a" ]) [ Name Nothing "*" ] (NumLit "2"))))
])
]
False
|
create table t (a int default 0);
|
CreateTable
[ Name Nothing "t" ]
[ TableColumnDef
(ColumnDef
(Name Nothing "a")
(Just (TypeName [ Name Nothing "int" ]))
[ ColConstraintDef
Nothing (ColDefaultClause (DefaultClause (NumLit "0")))
])
]
False
|
create table t (a int, unique (a));
|
CreateTable
[ Name Nothing "t" ]
[ TableColumnDef
(ColumnDef
(Name Nothing "a") (Just (TypeName [ Name Nothing "int" ])) [])
, TableConstraintDef
Nothing (TableUniqueConstraint [ Name Nothing "a" ])
]
False
|
create table t (a int, constraint a_unique unique (a));
|
CreateTable
[ Name Nothing "t" ]
[ TableColumnDef
(ColumnDef
(Name Nothing "a") (Just (TypeName [ Name Nothing "int" ])) [])
, TableConstraintDef
(Just [ Name Nothing "a_unique" ])
(TableUniqueConstraint [ Name Nothing "a" ])
]
False
|
create table t (a int, b int, unique (a,b));
|
CreateTable
[ Name Nothing "t" ]
[ TableColumnDef
(ColumnDef
(Name Nothing "a") (Just (TypeName [ Name Nothing "int" ])) [])
, TableColumnDef
(ColumnDef
(Name Nothing "b") (Just (TypeName [ Name Nothing "int" ])) [])
, TableConstraintDef
Nothing
(TableUniqueConstraint [ Name Nothing "a" , Name Nothing "b" ])
]
False
|
create table t (a int, b int, primary key (a,b));
|
CreateTable
[ Name Nothing "t" ]
[ TableColumnDef
(ColumnDef
(Name Nothing "a") (Just (TypeName [ Name Nothing "int" ])) [])
, TableColumnDef
(ColumnDef
(Name Nothing "b") (Just (TypeName [ Name Nothing "int" ])) [])
, TableConstraintDef
Nothing
(TablePrimaryKeyConstraint [ Name Nothing "a" , Name Nothing "b" ])
]
False
|
create table t (a int, b int,
foreign key (a,b) references u(c,d) match full on update cascade on delete restrict );
|
CreateTable
[ Name Nothing "t" ]
[ TableColumnDef
(ColumnDef
(Name Nothing "a") (Just (TypeName [ Name Nothing "int" ])) [])
, TableColumnDef
(ColumnDef
(Name Nothing "b") (Just (TypeName [ Name Nothing "int" ])) [])
, TableConstraintDef
Nothing
(TableReferencesConstraint
[ Name Nothing "a" , Name Nothing "b" ]
[ Name Nothing "u" ]
(Just [ Name Nothing "c" , Name Nothing "d" ])
MatchFull
RefCascade
RefRestrict)
]
False
|
create table t (a int,
constraint tfku1 foreign key (a) references u);
|
CreateTable
[ Name Nothing "t" ]
[ TableColumnDef
(ColumnDef
(Name Nothing "a") (Just (TypeName [ Name Nothing "int" ])) [])
, TableConstraintDef
(Just [ Name Nothing "tfku1" ])
(TableReferencesConstraint
[ Name Nothing "a" ]
[ Name Nothing "u" ]
Nothing
DefaultReferenceMatch
DefaultReferentialAction
DefaultReferentialAction)
]
False
|
create table t (a int, b int,
foreign key (a) references u(c)
foreign key (b) references v(d));
|
CreateTable
[ Name Nothing "t" ]
[ TableColumnDef
(ColumnDef
(Name Nothing "a") (Just (TypeName [ Name Nothing "int" ])) [])
, TableColumnDef
(ColumnDef
(Name Nothing "b") (Just (TypeName [ Name Nothing "int" ])) [])
, TableConstraintDef
Nothing
(TableReferencesConstraint
[ Name Nothing "a" ]
[ Name Nothing "u" ]
(Just [ Name Nothing "c" ])
DefaultReferenceMatch
DefaultReferentialAction
DefaultReferentialAction)
, TableConstraintDef
Nothing
(TableReferencesConstraint
[ Name Nothing "b" ]
[ Name Nothing "v" ]
(Just [ Name Nothing "d" ])
DefaultReferenceMatch
DefaultReferentialAction
DefaultReferentialAction)
]
False
|
create table t (a int) without rowid;
|
CreateTable
[ Name Nothing "t" ]
[ TableColumnDef
(ColumnDef
(Name Nothing "a") (Just (TypeName [ Name Nothing "int" ])) [])
]
True
|
|
|
CreateTable
[ Name Nothing "t" ]
[ TableColumnDef (ColumnDef (Name Nothing "a") Nothing [])
, TableColumnDef (ColumnDef (Name Nothing "b") Nothing [])
]
False
|
create table t (a int default 1 default 2);
|
CreateTable
[ Name Nothing "t" ]
[ TableColumnDef
(ColumnDef
(Name Nothing "a")
(Just (TypeName [ Name Nothing "int" ]))
[ ColConstraintDef
Nothing (ColDefaultClause (DefaultClause (NumLit "1")))
, ColConstraintDef
Nothing (ColDefaultClause (DefaultClause (NumLit "2")))
])
]
False
|
create table t (a int not null default 2);
|
CreateTable
[ Name Nothing "t" ]
[ TableColumnDef
(ColumnDef
(Name Nothing "a")
(Just (TypeName [ Name Nothing "int" ]))
[ ColConstraintDef Nothing ColNotNullConstraint
, ColConstraintDef
Nothing (ColDefaultClause (DefaultClause (NumLit "2")))
])
]
False
|
create table t (a int, b int,
check (a > b));
|
CreateTable
[ Name Nothing "t" ]
[ TableColumnDef
(ColumnDef
(Name Nothing "a") (Just (TypeName [ Name Nothing "int" ])) [])
, TableColumnDef
(ColumnDef
(Name Nothing "b") (Just (TypeName [ Name Nothing "int" ])) [])
, TableConstraintDef
Nothing
(TableCheckConstraint
(BinOp
(Iden [ Name Nothing "a" ])
[ Name Nothing ">" ]
(Iden [ Name Nothing "b" ])))
]
False
|
create table t (a int, b int,
constraint agtb check (a > b));
|
CreateTable
[ Name Nothing "t" ]
[ TableColumnDef
(ColumnDef
(Name Nothing "a") (Just (TypeName [ Name Nothing "int" ])) [])
, TableColumnDef
(ColumnDef
(Name Nothing "b") (Just (TypeName [ Name Nothing "int" ])) [])
, TableConstraintDef
(Just [ Name Nothing "agtb" ])
(TableCheckConstraint
(BinOp
(Iden [ Name Nothing "a" ])
[ Name Nothing ">" ]
(Iden [ Name Nothing "b" ])))
]
False
|
alter table t add column a int
|
AlterTable
[ Name Nothing "t" ]
(AddColumnDef
(ColumnDef
(Name Nothing "a") (Just (TypeName [ Name Nothing "int" ])) []))
|
alter table t alter column c set default 0
|
AlterTable
[ Name Nothing "t" ]
(AlterColumnSetDefault (Name Nothing "c") (NumLit "0"))
|
alter table t alter column c drop default
|
AlterTable
[ Name Nothing "t" ] (AlterColumnDropDefault (Name Nothing "c"))
|
alter table t alter column c set not null
|
AlterTable
[ Name Nothing "t" ] (AlterColumnSetNotNull (Name Nothing "c"))
|
alter table t alter column c drop not null
|
AlterTable
[ Name Nothing "t" ] (AlterColumnDropNotNull (Name Nothing "c"))
|
alter table t alter column c set data type int;
|
AlterTable
[ Name Nothing "t" ]
(AlterColumnSetDataType
(Name Nothing "c") (TypeName [ Name Nothing "int" ]))
|
alter table t drop column c
|
AlterTable
[ Name Nothing "t" ]
(DropColumn (Name Nothing "c") DefaultDropBehaviour)
|
alter table t drop c cascade
|
AlterTable
[ Name Nothing "t" ] (DropColumn (Name Nothing "c") Cascade)
|
alter table t drop c restrict
|
AlterTable
[ Name Nothing "t" ] (DropColumn (Name Nothing "c") Restrict)
|
alter table t add constraint c unique (a,b)
|
AlterTable
[ Name Nothing "t" ]
(AddTableConstraintDef
(Just [ Name Nothing "c" ])
(TableUniqueConstraint [ Name Nothing "a" , Name Nothing "b" ]))
|
alter table t add unique (a,b)
|
AlterTable
[ Name Nothing "t" ]
(AddTableConstraintDef
Nothing
(TableUniqueConstraint [ Name Nothing "a" , Name Nothing "b" ]))
|
alter table t drop constraint c
|
AlterTable
[ Name Nothing "t" ]
(DropTableConstraintDef [ Name Nothing "c" ] DefaultDropBehaviour)
|
alter table t drop constraint c restrict
|
AlterTable
[ Name Nothing "t" ]
(DropTableConstraintDef [ Name Nothing "c" ] Restrict)
|
|
|
DropTable [ Name Nothing "t" ] DefaultDropBehaviour
|
|
|
DropTable [ Name Nothing "t" ] Restrict
|
create view v as select * from t
|
CreateView
False
[ Name Nothing "v" ]
Nothing
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Star , Nothing ) ]
, qeFrom = [ TRSimple [ Name Nothing "t" ] ]
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
Nothing
|
create recursive view v as select * from t
|
CreateView
True
[ Name Nothing "v" ]
Nothing
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Star , Nothing ) ]
, qeFrom = [ TRSimple [ Name Nothing "t" ] ]
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
Nothing
|
create view v(a,b) as select * from t
|
CreateView
False
[ Name Nothing "v" ]
(Just [ Name Nothing "a" , Name Nothing "b" ])
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Star , Nothing ) ]
, qeFrom = [ TRSimple [ Name Nothing "t" ] ]
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
Nothing
|
create view v as select * from t with check option
|
CreateView
False
[ Name Nothing "v" ]
Nothing
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Star , Nothing ) ]
, qeFrom = [ TRSimple [ Name Nothing "t" ] ]
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
(Just DefaultCheckOption)
|
create view v as select * from t with cascaded check option
|
CreateView
False
[ Name Nothing "v" ]
Nothing
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Star , Nothing ) ]
, qeFrom = [ TRSimple [ Name Nothing "t" ] ]
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
(Just CascadedCheckOption)
|
create view v as select * from t with local check option
|
CreateView
False
[ Name Nothing "v" ]
Nothing
Select
{ qeSetQuantifier = SQDefault
, qeSelectList = [ ( Star , Nothing ) ]
, qeFrom = [ TRSimple [ Name Nothing "t" ] ]
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
}
(Just LocalCheckOption)
|
|
|
DropView [ Name Nothing "v" ] DefaultDropBehaviour
|
|
|
DropView [ Name Nothing "v" ] Cascade
|
|
|
CreateDomain
[ Name Nothing "my_int" ]
(TypeName [ Name Nothing "int" ])
Nothing
[]
|
create domain my_int as int
|
CreateDomain
[ Name Nothing "my_int" ]
(TypeName [ Name Nothing "int" ])
Nothing
[]
|
create domain my_int int default 0
|
CreateDomain
[ Name Nothing "my_int" ]
(TypeName [ Name Nothing "int" ])
(Just (NumLit "0"))
[]
|
create domain my_int int check (value > 5)
|
CreateDomain
[ Name Nothing "my_int" ]
(TypeName [ Name Nothing "int" ])
Nothing
[ ( Nothing
, BinOp
(Iden [ Name Nothing "value" ]) [ Name Nothing ">" ] (NumLit "5")
)
]
|
create domain my_int int constraint gt5 check (value > 5)
|
CreateDomain
[ Name Nothing "my_int" ]
(TypeName [ Name Nothing "int" ])
Nothing
[ ( Just [ Name Nothing "gt5" ]
, BinOp
(Iden [ Name Nothing "value" ]) [ Name Nothing ">" ] (NumLit "5")
)
]
|
alter domain my_int set default 0
|
AlterDomain [ Name Nothing "my_int" ] (ADSetDefault (NumLit "0"))
|
alter domain my_int drop default
|
AlterDomain [ Name Nothing "my_int" ] ADDropDefault
|
alter domain my_int add check (value > 6)
|
AlterDomain
[ Name Nothing "my_int" ]
(ADAddConstraint
Nothing
(BinOp
(Iden [ Name Nothing "value" ]) [ Name Nothing ">" ] (NumLit "6")))
|
alter domain my_int add constraint gt6 check (value > 6)
|
AlterDomain
[ Name Nothing "my_int" ]
(ADAddConstraint
(Just [ Name Nothing "gt6" ])
(BinOp
(Iden [ Name Nothing "value" ]) [ Name Nothing ">" ] (NumLit "6")))
|
alter domain my_int drop constraint gt6
|
AlterDomain
[ Name Nothing "my_int" ] (ADDropConstraint [ Name Nothing "gt6" ])
|
|
|
DropDomain [ Name Nothing "my_int" ] DefaultDropBehaviour
|
drop domain my_int cascade
|
DropDomain [ Name Nothing "my_int" ] Cascade
|
create assertion t1_not_empty CHECK ((select count(*) from t1) > 0);
|
CreateAssertion
[ Name Nothing "t1_not_empty" ]
(BinOp
(SubQueryExpr
SqSq
Select
{ qeSetQuantifier = SQDefault
, qeSelectList =
[ ( App [ Name Nothing "count" ] [ Star ] , Nothing ) ]
, qeFrom = [ TRSimple [ Name Nothing "t1" ] ]
, qeWhere = Nothing
, qeGroupBy = []
, qeHaving = Nothing
, qeOrderBy = []
, qeOffset = Nothing
, qeFetchFirst = Nothing
})
[ Name Nothing ">" ]
(NumLit "0"))
|
drop assertion t1_not_empty;
|
DropAssertion [ Name Nothing "t1_not_empty" ] DefaultDropBehaviour
|
drop assertion t1_not_empty cascade;
|
DropAssertion [ Name Nothing "t1_not_empty" ] Cascade
|
|
|
CreateSequence [ Name Nothing "seq" ] []
|
create sequence seq as bigint
|
CreateSequence
[ Name Nothing "seq" ]
[ SGODataType (TypeName [ Name Nothing "bigint" ]) ]
|
create sequence seq as bigint start with 5
|
CreateSequence
[ Name Nothing "seq" ]
[ SGOStartWith 5
, SGODataType (TypeName [ Name Nothing "bigint" ])
]
|
alter sequence seq restart
|
AlterSequence [ Name Nothing "seq" ] [ SGORestart Nothing ]
|
alter sequence seq restart with 5
|
AlterSequence [ Name Nothing "seq" ] [ SGORestart (Just 5) ]
|
alter sequence seq restart with 5 increment by 5
|
AlterSequence
[ Name Nothing "seq" ] [ SGORestart (Just 5) , SGOIncrementBy 5 ]
|
|
|
DropSequence [ Name Nothing "seq" ] DefaultDropBehaviour
|
drop sequence seq restrict
|
DropSequence [ Name Nothing "seq" ] Restrict
|