-- Tests for parsing scalar expressions {-# LANGUAGE OverloadedStrings #-} module Language.SQL.SimpleSQL.ScalarExprs (scalarExprTests) where import Language.SQL.SimpleSQL.TestTypes import Language.SQL.SimpleSQL.Syntax import Language.SQL.SimpleSQL.TestRunners import Data.Text (Text) scalarExprTests :: TestItem scalarExprTests = Group "scalarExprTests" [literals ,identifiers ,star ,parameter ,dots ,app ,caseexp ,convertfun ,operators ,parens ,subqueries ,aggregates ,windowFunctions ,functionsWithReservedNames ] t :: HasCallStack => Text -> ScalarExpr -> TestItem t src ast = testScalarExpr ansi2011 src ast td :: HasCallStack => Dialect -> Text -> ScalarExpr -> TestItem td d src ast = testScalarExpr d src ast literals :: TestItem literals = Group "literals" [t "3" $ NumLit "3" ,t "3." $ NumLit "3." ,t "3.3" $ NumLit "3.3" ,t ".3" $ NumLit ".3" ,t "3.e3" $ NumLit "3.e3" ,t "3.3e3" $ NumLit "3.3e3" ,t ".3e3" $ NumLit ".3e3" ,t "3e3" $ NumLit "3e3" ,t "3e+3" $ NumLit "3e+3" ,t "3e-3" $ NumLit "3e-3" ,t "'string'" $ StringLit "'" "'" "string" ,t "'string with a '' quote'" $ StringLit "'" "'" "string with a '' quote" ,t "'1'" $ StringLit "'" "'" "1" ,t "interval '3' day" $ IntervalLit Nothing "3" (Itf "day" Nothing) Nothing ,t "interval '3' day (3)" $ IntervalLit Nothing "3" (Itf "day" $ Just (3,Nothing)) Nothing ,t "interval '3 weeks'" $ TypedLit (TypeName [Name Nothing "interval"]) "3 weeks" ] identifiers :: TestItem identifiers = Group "identifiers" [t "iden1" $ Iden [Name Nothing "iden1"] --,("t.a", Iden2 "t" "a") ,t "\"quoted identifier\"" $ Iden [Name (Just ("\"","\"")) "quoted identifier"] ,t "\"from\"" $ Iden [Name (Just ("\"","\"")) "from"] ] star :: TestItem star = Group "star" [t "count(*)" $ App [Name Nothing "count"] [Star] ,t "ROW(t.*,42)" $ App [Name Nothing "ROW"] [QStar [Name Nothing "t"], NumLit "42"] ] parameter :: TestItem parameter = Group "parameter" [td ansi2011 "?" Parameter ,td postgres "$13" $ PositionalArg 13] dots :: TestItem dots = Group "dot" [t "t.a" $ Iden [Name Nothing "t",Name Nothing "a"] ,t "a.b.c" $ Iden [Name Nothing "a",Name Nothing "b",Name Nothing "c"] ,t "ROW(t.*,42)" $ App [Name Nothing "ROW"] [QStar [Name Nothing "t"], NumLit "42"] ] app :: TestItem app = Group "app" [t "f()" $ App [Name Nothing "f"] [] ,t "f(a)" $ App [Name Nothing "f"] [Iden [Name Nothing "a"]] ,t "f(a,b)" $ App [Name Nothing "f"] [Iden [Name Nothing "a"], Iden [Name Nothing "b"]] ] caseexp :: TestItem caseexp = Group "caseexp" [t "case a when 1 then 2 end" $ Case (Just $ Iden [Name Nothing "a"]) [([NumLit "1"] ,NumLit "2")] Nothing ,t "case a when 1 then 2 when 3 then 4 end" $ Case (Just $ Iden [Name Nothing "a"]) [([NumLit "1"], NumLit "2") ,([NumLit "3"], NumLit "4")] Nothing ,t "case a when 1 then 2 when 3 then 4 else 5 end" $ Case (Just $ Iden [Name Nothing "a"]) [([NumLit "1"], NumLit "2") ,([NumLit "3"], NumLit "4")] (Just $ NumLit "5") ,t "case when a=1 then 2 when a=3 then 4 else 5 end" $ Case Nothing [([BinOp (Iden [Name Nothing "a"]) [Name Nothing "="] (NumLit "1")], NumLit "2") ,([BinOp (Iden [Name Nothing "a"]) [Name Nothing "="] (NumLit "3")], NumLit "4")] (Just $ NumLit "5") ,t "case a when 1,2 then 10 when 3,4 then 20 end" $ Case (Just $ Iden [Name Nothing "a"]) [([NumLit "1",NumLit "2"] ,NumLit "10") ,([NumLit "3",NumLit "4"] ,NumLit "20")] Nothing ] convertfun :: TestItem convertfun = Group "convert" [td sqlserver "CONVERT(varchar, 25.65)" $ Convert (TypeName [Name Nothing "varchar"]) (NumLit "25.65") Nothing ,td sqlserver "CONVERT(datetime, '2017-08-25')" $ Convert (TypeName [Name Nothing "datetime"]) (StringLit "'" "'" "2017-08-25") Nothing ,td sqlserver "CONVERT(varchar, '2017-08-25', 101)" $ Convert (TypeName [Name Nothing "varchar"]) (StringLit "'" "'" "2017-08-25") (Just 101) ] operators :: TestItem operators = Group "operators" [binaryOperators ,unaryOperators ,casts ,miscOps] binaryOperators :: TestItem binaryOperators = Group "binaryOperators" [t "a + b" $ BinOp (Iden [Name Nothing "a"]) [Name Nothing "+"] (Iden [Name Nothing "b"]) -- sanity check fixities -- todo: add more fixity checking ,t "a + b * c" $ BinOp (Iden [Name Nothing "a"]) [Name Nothing "+"] (BinOp (Iden [Name Nothing "b"]) [Name Nothing "*"] (Iden [Name Nothing "c"])) ,t "a * b + c" $ BinOp (BinOp (Iden [Name Nothing "a"]) [Name Nothing "*"] (Iden [Name Nothing "b"])) [Name Nothing "+"] (Iden [Name Nothing "c"]) ] unaryOperators :: TestItem unaryOperators = Group "unaryOperators" [t "not a" $ PrefixOp [Name Nothing "not"] $ Iden [Name Nothing "a"] ,t "not not a" $ PrefixOp [Name Nothing "not"] $ PrefixOp [Name Nothing "not"] $ Iden [Name Nothing "a"] ,t "+a" $ PrefixOp [Name Nothing "+"] $ Iden [Name Nothing "a"] ,t "-a" $ PrefixOp [Name Nothing "-"] $ Iden [Name Nothing "a"] ] casts :: TestItem casts = Group "operators" [t "cast('1' as int)" $ Cast (StringLit "'" "'" "1") $ TypeName [Name Nothing "int"] ,t "int '3'" $ TypedLit (TypeName [Name Nothing "int"]) "3" ,t "cast('1' as double precision)" $ Cast (StringLit "'" "'" "1") $ TypeName [Name Nothing "double precision"] ,t "cast('1' as float(8))" $ Cast (StringLit "'" "'" "1") $ PrecTypeName [Name Nothing "float"] 8 ,t "cast('1' as decimal(15,2))" $ Cast (StringLit "'" "'" "1") $ PrecScaleTypeName [Name Nothing "decimal"] 15 2 ,t "double precision '3'" $ TypedLit (TypeName [Name Nothing "double precision"]) "3" ] subqueries :: TestItem subqueries = Group "unaryOperators" [t "exists (select a from t)" $ SubQueryExpr SqExists ms ,t "(select a from t)" $ SubQueryExpr SqSq ms ,t "a in (select a from t)" $ In True (Iden [Name Nothing "a"]) (InQueryExpr ms) ,t "a not in (select a from t)" $ In False (Iden [Name Nothing "a"]) (InQueryExpr ms) ,t "a > all (select a from t)" $ QuantifiedComparison (Iden [Name Nothing "a"]) [Name Nothing ">"] CPAll ms ,t "a = some (select a from t)" $ QuantifiedComparison (Iden [Name Nothing "a"]) [Name Nothing "="] CPSome ms ,t "a <= any (select a from t)" $ QuantifiedComparison (Iden [Name Nothing "a"]) [Name Nothing "<="] CPAny ms ] where ms = toQueryExpr $ makeSelect {msSelectList = [(Iden [Name Nothing "a"],Nothing)] ,msFrom = [TRSimple [Name Nothing "t"]] } miscOps :: TestItem miscOps = Group "unaryOperators" [t "a in (1,2,3)" $ In True (Iden [Name Nothing "a"]) $ InList $ map NumLit ["1","2","3"] ,t "a is null" $ PostfixOp [Name Nothing "is null"] (Iden [Name Nothing "a"]) ,t "a is not null" $ PostfixOp [Name Nothing "is not null"] (Iden [Name Nothing "a"]) ,t "a is true" $ PostfixOp [Name Nothing "is true"] (Iden [Name Nothing "a"]) ,t "a is not true" $ PostfixOp [Name Nothing "is not true"] (Iden [Name Nothing "a"]) ,t "a is false" $ PostfixOp [Name Nothing "is false"] (Iden [Name Nothing "a"]) ,t "a is not false" $ PostfixOp [Name Nothing "is not false"] (Iden [Name Nothing "a"]) ,t "a is unknown" $ PostfixOp [Name Nothing "is unknown"] (Iden [Name Nothing "a"]) ,t "a is not unknown" $ PostfixOp [Name Nothing "is not unknown"] (Iden [Name Nothing "a"]) ,t "a is distinct from b" $ BinOp (Iden [Name Nothing "a"]) [Name Nothing "is distinct from"] (Iden [Name Nothing "b"]) ,t "a is not distinct from b" $ BinOp (Iden [Name Nothing "a"]) [Name Nothing "is not distinct from"] (Iden [Name Nothing "b"]) ,t "a like b" $ BinOp (Iden [Name Nothing "a"]) [Name Nothing "like"] (Iden [Name Nothing "b"]) ,t "a not like b" $ BinOp (Iden [Name Nothing "a"]) [Name Nothing "not like"] (Iden [Name Nothing "b"]) ,t "a is similar to b"$ BinOp (Iden [Name Nothing "a"]) [Name Nothing "is similar to"] (Iden [Name Nothing "b"]) ,t "a is not similar to b" $ BinOp (Iden [Name Nothing "a"]) [Name Nothing "is not similar to"] (Iden [Name Nothing "b"]) ,t "a overlaps b" $ BinOp (Iden [Name Nothing "a"]) [Name Nothing "overlaps"] (Iden [Name Nothing "b"]) -- special operators ,t "a between b and c" $ SpecialOp [Name Nothing "between"] [Iden [Name Nothing "a"] ,Iden [Name Nothing "b"] ,Iden [Name Nothing "c"]] ,t "a not between b and c" $ SpecialOp [Name Nothing "not between"] [Iden [Name Nothing "a"] ,Iden [Name Nothing "b"] ,Iden [Name Nothing "c"]] ,t "(1,2)" $ SpecialOp [Name Nothing "rowctor"] [NumLit "1", NumLit "2"] -- keyword special operators ,t "extract(day from t)" $ SpecialOpK [Name Nothing "extract"] (Just $ Iden [Name Nothing "day"]) [("from", Iden [Name Nothing "t"])] ,t "substring(x from 1 for 2)" $ SpecialOpK [Name Nothing "substring"] (Just $ Iden [Name Nothing "x"]) [("from", NumLit "1") ,("for", NumLit "2")] ,t "substring(x from 1)" $ SpecialOpK [Name Nothing "substring"] (Just $ Iden [Name Nothing "x"]) [("from", NumLit "1")] ,t "substring(x for 2)" $ SpecialOpK [Name Nothing "substring"] (Just $ Iden [Name Nothing "x"]) [("for", NumLit "2")] ,t "substring(x from 1 for 2 collate C)" $ SpecialOpK [Name Nothing "substring"] (Just $ Iden [Name Nothing "x"]) [("from", NumLit "1") ,("for", Collate (NumLit "2") [Name Nothing "C"])] -- this doesn't work because of a overlap in the 'in' parser ,t "POSITION( string1 IN string2 )" $ SpecialOpK [Name Nothing "position"] (Just $ Iden [Name Nothing "string1"]) [("in", Iden [Name Nothing "string2"])] ,t "CONVERT(char_value USING conversion_char_name)" $ SpecialOpK [Name Nothing "convert"] (Just $ Iden [Name Nothing "char_value"]) [("using", Iden [Name Nothing "conversion_char_name"])] ,t "TRANSLATE(char_value USING translation_name)" $ SpecialOpK [Name Nothing "translate"] (Just $ Iden [Name Nothing "char_value"]) [("using", Iden [Name Nothing "translation_name"])] {- OVERLAY(string PLACING embedded_string FROM start [FOR length]) -} ,t "OVERLAY(string PLACING embedded_string FROM start)" $ SpecialOpK [Name Nothing "overlay"] (Just $ Iden [Name Nothing "string"]) [("placing", Iden [Name Nothing "embedded_string"]) ,("from", Iden [Name Nothing "start"])] ,t "OVERLAY(string PLACING embedded_string FROM start FOR length)" $ SpecialOpK [Name Nothing "overlay"] (Just $ Iden [Name Nothing "string"]) [("placing", Iden [Name Nothing "embedded_string"]) ,("from", Iden [Name Nothing "start"]) ,("for", Iden [Name Nothing "length"])] {- TRIM( [ [{LEADING | TRAILING | BOTH}] [removal_char] FROM ] target_string [COLLATE collation_name] ) -} ,t "trim(from target_string)" $ SpecialOpK [Name Nothing "trim"] Nothing [("both", StringLit "'" "'" " ") ,("from", Iden [Name Nothing "target_string"])] ,t "trim(leading from target_string)" $ SpecialOpK [Name Nothing "trim"] Nothing [("leading", StringLit "'" "'" " ") ,("from", Iden [Name Nothing "target_string"])] ,t "trim(trailing from target_string)" $ SpecialOpK [Name Nothing "trim"] Nothing [("trailing", StringLit "'" "'" " ") ,("from", Iden [Name Nothing "target_string"])] ,t "trim(both from target_string)" $ SpecialOpK [Name Nothing "trim"] Nothing [("both", StringLit "'" "'" " ") ,("from", Iden [Name Nothing "target_string"])] ,t "trim(leading 'x' from target_string)" $ SpecialOpK [Name Nothing "trim"] Nothing [("leading", StringLit "'" "'" "x") ,("from", Iden [Name Nothing "target_string"])] ,t "trim(trailing 'y' from target_string)" $ SpecialOpK [Name Nothing "trim"] Nothing [("trailing", StringLit "'" "'" "y") ,("from", Iden [Name Nothing "target_string"])] ,t "trim(both 'z' from target_string collate C)" $ SpecialOpK [Name Nothing "trim"] Nothing [("both", StringLit "'" "'" "z") ,("from", Collate (Iden [Name Nothing "target_string"]) [Name Nothing "C"])] ,t "trim(leading from target_string)" $ SpecialOpK [Name Nothing "trim"] Nothing [("leading", StringLit "'" "'" " ") ,("from", Iden [Name Nothing "target_string"])] ] aggregates :: TestItem aggregates = Group "aggregates" [t "count(*)" $ App [Name Nothing "count"] [Star] ,t "sum(a order by a)" $ AggregateApp [Name Nothing "sum"] SQDefault [Iden [Name Nothing "a"]] [SortSpec (Iden [Name Nothing "a"]) DirDefault NullsOrderDefault] Nothing ,t "sum(all a)" $ AggregateApp [Name Nothing "sum"] All [Iden [Name Nothing "a"]] [] Nothing ,t "count(distinct a)" $ AggregateApp [Name Nothing "count"] Distinct [Iden [Name Nothing "a"]] [] Nothing ] windowFunctions :: TestItem windowFunctions = Group "windowFunctions" [t "max(a) over ()" $ WindowApp [Name Nothing "max"] [Iden [Name Nothing "a"]] [] [] Nothing ,t "count(*) over ()" $ WindowApp [Name Nothing "count"] [Star] [] [] Nothing ,t "max(a) over (partition by b)" $ WindowApp [Name Nothing "max"] [Iden [Name Nothing "a"]] [Iden [Name Nothing "b"]] [] Nothing ,t "max(a) over (partition by b,c)" $ WindowApp [Name Nothing "max"] [Iden [Name Nothing "a"]] [Iden [Name Nothing "b"],Iden [Name Nothing "c"]] [] Nothing ,t "sum(a) over (order by b)" $ WindowApp [Name Nothing "sum"] [Iden [Name Nothing "a"]] [] [SortSpec (Iden [Name Nothing "b"]) DirDefault NullsOrderDefault] Nothing ,t "sum(a) over (order by b desc,c)" $ WindowApp [Name Nothing "sum"] [Iden [Name Nothing "a"]] [] [SortSpec (Iden [Name Nothing "b"]) Desc NullsOrderDefault ,SortSpec (Iden [Name Nothing "c"]) DirDefault NullsOrderDefault] Nothing ,t "sum(a) over (partition by b order by c)" $ WindowApp [Name Nothing "sum"] [Iden [Name Nothing "a"]] [Iden [Name Nothing "b"]] [SortSpec (Iden [Name Nothing "c"]) DirDefault NullsOrderDefault] Nothing ,t "sum(a) over (partition by b order by c range unbounded preceding)" $ WindowApp [Name Nothing "sum"] [Iden [Name Nothing "a"]] [Iden [Name Nothing "b"]] [SortSpec (Iden [Name Nothing "c"]) DirDefault NullsOrderDefault] $ Just $ FrameFrom FrameRange UnboundedPreceding ,t "sum(a) over (partition by b order by c range 5 preceding)" $ WindowApp [Name Nothing "sum"] [Iden [Name Nothing "a"]] [Iden [Name Nothing "b"]] [SortSpec (Iden [Name Nothing "c"]) DirDefault NullsOrderDefault] $ Just $ FrameFrom FrameRange $ Preceding (NumLit "5") ,t "sum(a) over (partition by b order by c range current row)" $ WindowApp [Name Nothing "sum"] [Iden [Name Nothing "a"]] [Iden [Name Nothing "b"]] [SortSpec (Iden [Name Nothing "c"]) DirDefault NullsOrderDefault] $ Just $ FrameFrom FrameRange Current ,t "sum(a) over (partition by b order by c rows 5 following)" $ WindowApp [Name Nothing "sum"] [Iden [Name Nothing "a"]] [Iden [Name Nothing "b"]] [SortSpec (Iden [Name Nothing "c"]) DirDefault NullsOrderDefault] $ Just $ FrameFrom FrameRows $ Following (NumLit "5") ,t "sum(a) over (partition by b order by c range unbounded following)" $ WindowApp [Name Nothing "sum"] [Iden [Name Nothing "a"]] [Iden [Name Nothing "b"]] [SortSpec (Iden [Name Nothing "c"]) DirDefault NullsOrderDefault] $ Just $ FrameFrom FrameRange UnboundedFollowing ,t "sum(a) over (partition by b order by c \n\ \range between 5 preceding and 5 following)" $ WindowApp [Name Nothing "sum"] [Iden [Name Nothing "a"]] [Iden [Name Nothing "b"]] [SortSpec (Iden [Name Nothing "c"]) DirDefault NullsOrderDefault] $ Just $ FrameBetween FrameRange (Preceding (NumLit "5")) (Following (NumLit "5")) ] parens :: TestItem parens = Group "parens" [t "(a)" $ Parens (Iden [Name Nothing "a"]) ,t "(a + b)" $ Parens (BinOp (Iden [Name Nothing "a"]) [Name Nothing "+"] (Iden [Name Nothing "b"])) ] functionsWithReservedNames :: TestItem functionsWithReservedNames = Group "functionsWithReservedNames" $ map f ["abs" ,"char_length" ] where f fn = t (fn <> "(a)") $ App [Name Nothing fn] [Iden [Name Nothing "a"]]