support parenthesized relation

nooberfsh · Nov 30, 2021 · Dec 8, 2021 · Jan 19, 2022 · Dec 22, 2021 · Dec 28, 2021
commit 4714584ad69cf4d761ece67b54b83f1efd5c454c
diff --git a/dialects/hive/src/Database/Sql/Hive/Parser.hs b/dialects/hive/src/Database/Sql/Hive/Parser.hs
@@ -1223,6 +1223,10 @@ tablishToTableAlias (TablishSubQuery _ aliases _) = case aliases of
     TablishAliasesNone -> error "shouldn't happen in hive"
     TablishAliasesT (TableAlias _ name _) -> S.singleton name
     TablishAliasesTC _ _ -> error "shouldn't happen in hive"
+tablishToTableAlias (TablishParenthesizedRelation _ aliases relation) = case aliases of
+    TablishAliasesNone -> tablishToTableAlias relation
+    TablishAliasesT _ -> error "shouldn't happen in hive"
+    TablishAliasesTC _ _ -> error "shouldn't happen in hive"
 tablishToTableAlias (TablishLateralView _ LateralView{..} _) = case lateralViewAliases of
     TablishAliasesNone -> error "shouldn't happen in hive"
     TablishAliasesT (TableAlias _ name _) -> S.singleton name

diff --git a/dialects/presto/src/Database/Sql/Presto/Parser.hs b/dialects/presto/src/Database/Sql/Presto/Parser.hs
@@ -272,6 +272,10 @@ tableAliases from =
         TablishAliasesNone -> S.empty
         TablishAliasesT (TableAlias _ name _) -> S.singleton name
         TablishAliasesTC (TableAlias _ name _) _ -> S.singleton name
+    tablishToTableAlias (TablishParenthesizedRelation _ aliases _) = case aliases of
+        TablishAliasesNone -> S.empty
+        TablishAliasesT (TableAlias _ name _) -> S.singleton name
+        TablishAliasesTC (TableAlias _ name _) _ -> S.singleton name
     tablishToTableAlias (TablishLateralView _ LateralView{..} _) = case lateralViewAliases of
         TablishAliasesNone -> S.empty
         TablishAliasesT (TableAlias _ name _) -> S.singleton name
@@ -359,7 +363,9 @@ sampledRelationP = do
                   return $ TablishLateralView lateralViewInfo LateralView{..} Nothing
 
             , P.between Tok.openP Tok.closeP $ choice
-                [ relationP
+                [ try $ do
+                      r <- relationP
+                      return $ TablishParenthesizedRelation (getInfo r) placeholder r
                 , do
                       q <- queryP
                       return $ TablishSubQuery (getInfo q) placeholder q
@@ -371,6 +377,7 @@ sampledRelationP = do
                 TablishSubQuery info _ query -> TablishSubQuery info as query
                 TablishJoin _ _ _ _ _ -> error "shouldn't happen"
                 TablishLateralView info LateralView{..} lhs -> TablishLateralView info LateralView{lateralViewAliases = as, ..} lhs
+                TablishParenthesizedRelation info _ relation -> TablishParenthesizedRelation info as relation
         return withAliases
 
     tablishAliasesP :: Parser (TablishAliases Range)

diff --git a/src/Database/Sql/Info.hs b/src/Database/Sql/Info.hs
@@ -350,6 +350,7 @@ instance HasInfo (Tablish r a) where
     type Info (Tablish r a) = a
     getInfo (TablishTable info _ _) = info
     getInfo (TablishSubQuery info _ _) = info
+    getInfo (TablishParenthesizedRelation info _ _) = info
     getInfo (TablishJoin info _ _ _ _) = info
     getInfo (TablishLateralView info _ _) = info
 

diff --git a/src/Database/Sql/Type/Query.hs b/src/Database/Sql/Type/Query.hs
@@ -156,6 +156,7 @@ data Tablish r a
     | TablishJoin a (JoinType a) (JoinCondition r a)
             (Tablish r a) (Tablish r a)
     | TablishLateralView a (LateralView r a) (Maybe (Tablish r a))
+    | TablishParenthesizedRelation a (TablishAliases a) (Tablish r a)
 
 deriving instance (ConstrainSNames Data r a, Data r) => Data (Tablish r a)
 deriving instance Generic (Tablish r a)
@@ -1227,6 +1228,13 @@ instance ConstrainSNames ToJSON r a => ToJSON (Tablish r a) where
         , "query" .= query
         ]
 
+    toJSON (TablishParenthesizedRelation info alias relation) = object
+        [ "tag" .= String "TablishParenthesizedRelation"
+        , "info" .= info
+        , "alias" .= alias
+        , "realtion" .= relation
+        ]
+
     toJSON (TablishJoin info join condition outer inner) = object
         [ "tag" .= String "TablishJoin"
         , "info" .= info

diff --git a/src/Database/Sql/Util/Columns.hs b/src/Database/Sql/Util/Columns.hs
@@ -38,7 +38,7 @@ import           Control.Monad.Reader
 import           Control.Monad.Writer
 
 import           Database.Sql.Type
-import           Database.Sql.Util.Scope (queryColumnNames)
+import           Database.Sql.Util.Scope (queryColumnNames, tablishColumnNames)
 
 type Clause = Text  -- SELECT, WHERE, GROUPBY, etc... for nested clauses,
                     -- report the innermost clause.
@@ -358,6 +358,16 @@ instance HasColumns (Tablish ResolvedNames a) where
             TablishAliasesTC _ cAliases ->
                 tell $ zipWith aliasObservation cAliases (queryColumnDeps query)
 
+    goColumns (TablishParenthesizedRelation _ tablishAliases relation) = do
+        goColumns relation
+
+        case tablishAliases of
+            TablishAliasesNone -> return ()
+            TablishAliasesT _ -> return ()
+            TablishAliasesTC _ cAliases ->
+                let cRefSets = map S.singleton $ tablishColumnNames relation
+                 in tell $ zipWith aliasObservation cAliases cRefSets
+
     goColumns (TablishJoin _ _ cond lhs rhs) = do
         bindClause "JOIN" $ goColumns cond
         goColumns lhs

diff --git a/src/Database/Sql/Util/Eval.hs b/src/Database/Sql/Util/Eval.hs
@@ -215,6 +215,7 @@ instance Evaluation e => Evaluate e (Tablish ResolvedNames Range) where
         Just result -> pure result
 
     eval p (TablishSubQuery _ _ query) = eval p query
+    eval p (TablishParenthesizedRelation _ _ relation) = eval p relation
     eval p (TablishJoin _ joinType cond lhs rhs) = do
         x <- eval p lhs
         y <- eval p rhs

diff --git a/src/Database/Sql/Util/Joins.hs b/src/Database/Sql/Util/Joins.hs
@@ -354,6 +354,7 @@ getJoinsTablish (TablishLateralView _ LateralView{..} lhs) = do
     mapM_ getJoinsExpr lateralViewExprs
 
 getJoinsTablish (TablishSubQuery _ _ query) = getJoinsQuery query
+getJoinsTablish (TablishParenthesizedRelation _ _ relation) = getJoinsTablish relation
 getJoinsTablish (TablishJoin _ _ (JoinNatural _ (RNaturalColumns columns)) lhs rhs) = do
     getJoinsTablish lhs
     getJoinsTablish rhs

diff --git a/src/Database/Sql/Util/Scope.hs b/src/Database/Sql/Util/Scope.hs
@@ -26,7 +26,7 @@
 module Database.Sql.Util.Scope
     ( runResolverWarn, runResolverWError, runResolverNoWarn
     , WithColumns (..)
-    , queryColumnNames
+    , queryColumnNames, tablishColumnNames
     , resolveStatement, resolveQuery, resolveQueryWithColumns, resolveSelectAndOrders, resolveCTE, resolveInsert
     , resolveInsertValues, resolveDefaultExpr, resolveDelete, resolveTruncate
     , resolveCreateTable, resolveTableDefinition, resolveColumnOrConstraint
@@ -237,6 +237,37 @@ queryColumnNames (QueryOrder _ _ query) = queryColumnNames query
 queryColumnNames (QueryLimit _ _ query) = queryColumnNames query
 queryColumnNames (QueryOffset _ _ query) = queryColumnNames query
 
+
+tablishColumnNames :: Tablish ResolvedNames a -> [RColumnRef a]
+tablishColumnNames (TablishTable _ tablishAliases tableRef) =
+    case tablishAliases of
+        TablishAliasesNone -> getColumnList tableRef
+        TablishAliasesT _ -> getColumnList tableRef
+        TablishAliasesTC _ cAliases -> map RColumnAlias cAliases
+
+tablishColumnNames (TablishSubQuery _ tablishAliases query) =
+    case tablishAliases of
+        TablishAliasesNone -> queryColumnNames query
+        TablishAliasesT _ -> queryColumnNames query
+        TablishAliasesTC _ cAliases -> map RColumnAlias cAliases
+
+tablishColumnNames (TablishParenthesizedRelation _ tablishAliases relation) =
+    case tablishAliases of
+        TablishAliasesNone -> tablishColumnNames relation
+        TablishAliasesT _ -> tablishColumnNames relation
+        TablishAliasesTC _ cAliases -> map RColumnAlias cAliases
+
+tablishColumnNames (TablishJoin _ _ _ lhs rhs) =
+    tablishColumnNames lhs ++ tablishColumnNames rhs
+
+tablishColumnNames (TablishLateralView _ LateralView{..} lhs) =
+    let cols = maybe [] tablishColumnNames lhs in
+    case lateralViewAliases of
+        TablishAliasesNone -> cols
+        TablishAliasesT _ -> cols
+        TablishAliasesTC _ cAliases -> cols ++ map RColumnAlias cAliases
+
+
 resolveSelectAndOrders :: Select RawNames a -> [Order RawNames a] -> Resolver (WithColumnsAndOrders (Select ResolvedNames)) a
 resolveSelectAndOrders Select{..} orders = do
     (selectFrom', columns) <- traverse resolveSelectFrom selectFrom >>= \case
@@ -689,6 +720,16 @@ resolveTablish (TablishSubQuery info aliases query) = do
 
     pure $ WithColumns (TablishSubQuery info aliases query') [(tAlias, cAliases)]
 
+resolveTablish (TablishParenthesizedRelation info aliases relation) = do
+    WithColumns relation' columns <- resolveTablish relation
+    let colRefs = concatMap snd columns
+        columns' = case aliases of
+            TablishAliasesNone -> columns
+            TablishAliasesT t -> map (first $ const $ Just $ RTableAlias t colRefs) columns
+            TablishAliasesTC t cs -> [(Just $ RTableAlias t colRefs, map RColumnAlias cs)]
+
+    pure $ WithColumns (TablishParenthesizedRelation info aliases relation') columns'
+
 resolveTablish (TablishJoin info joinType cond lhs rhs) = do
     WithColumns lhs' lcolumns <- resolveTablish lhs
 

diff --git a/src/Database/Sql/Util/Tables.hs b/src/Database/Sql/Util/Tables.hs
@@ -269,6 +269,7 @@ instance HasTables (Tablish ResolvedNames a) where
     goTables (TablishTable _ _ (RTableRef fqtn _)) = emitTable fqtn
     goTables (TablishTable _ _ (RTableAlias _ _)) = return ()
     goTables (TablishSubQuery _ _ query) = goTables query
+    goTables (TablishParenthesizedRelation _ _ relation) = goTables relation
     goTables (TablishLateralView _ LateralView{..} lhs) = goTables lhs >> mapM_ goTables lateralViewExprs
     goTables (TablishJoin _ _ cond outer inner) = do
         case cond of

diff --git a/test/Database/Sql/Presto/Parser/Test.hs b/test/Database/Sql/Presto/Parser/Test.hs
@@ -327,6 +327,12 @@ testParser = test
         -- test DROP VIEW
         , "DROP VIEW foo;"
         , "DROP VIEW IF EXISTS foo;"
+
+        -- test parenthesized relation
+        , "SELECT * FROM (foo CROSS JOIN bar);"
+        , "SELECT a,b FROM (foo CROSS JOIN bar);"
+        , "SELECT a,b FROM (foo CROSS JOIN bar) a;"
+        , "SELECT a,b FROM (foo CROSS JOIN bar) a (a,b);"
        ]
 
     , "Exclude some broken examples" ~: map  (TestCase . parsesUnsuccessfully)

diff --git a/test/Database/Sql/Util/Columns/Test.hs b/test/Database/Sql/Util/Columns/Test.hs
@@ -84,6 +84,37 @@ testColumnAccesses = test
               , (FullyQualifiedColumnName "default_db" "public" "bar" "b", "SELECT")
               ]
           )
+        , testPresto "SELECT * FROM (bar);" defaultTestCatalog
+          ((@=?) $ S.fromList
+              [ (FullyQualifiedColumnName "default_db" "public" "bar" "a", "SELECT")
+              , (FullyQualifiedColumnName "default_db" "public" "bar" "b", "SELECT")
+              ]
+          )
+        , testPresto "SELECT * FROM (bar) t(x,y);" defaultTestCatalog
+          ((@=?) $ S.fromList
+              [ (FullyQualifiedColumnName "default_db" "public" "bar" "a", "SELECT")
+              , (FullyQualifiedColumnName "default_db" "public" "bar" "b", "SELECT")
+              ]
+          )
+        , testPresto "SELECT * FROM (SELECT * FROM bar) t(x,y);" defaultTestCatalog
+          ((@=?) $ S.fromList
+              [ (FullyQualifiedColumnName "default_db" "public" "bar" "a", "SELECT")
+              , (FullyQualifiedColumnName "default_db" "public" "bar" "b", "SELECT")
+              ]
+          )
+        , testPresto "SELECT * FROM (foo CROSS JOIN bar) t(x,y,z);" defaultTestCatalog
+          ((@=?) $ S.fromList
+              [ (FullyQualifiedColumnName "default_db" "public" "foo" "a", "SELECT")
+              , (FullyQualifiedColumnName "default_db" "public" "bar" "a", "SELECT")
+              , (FullyQualifiedColumnName "default_db" "public" "bar" "b", "SELECT")
+              ]
+          )
+        , testPresto "SELECT * FROM (bar CROSS JOIN UNNEST(ARRAY[1,2]) AS t1(x)) t;" defaultTestCatalog
+          ((@=?) $ S.fromList
+              [ (FullyQualifiedColumnName "default_db" "public" "bar" "a", "SELECT")
+              , (FullyQualifiedColumnName "default_db" "public" "bar" "b", "SELECT")
+              ]
+          )
 
         -- FROM
         , testAll "SELECT 1 FROM foo JOIN bar ON foo.a = bar.a;" defaultTestCatalog
@@ -108,6 +139,12 @@ testColumnAccesses = test
           ((@=?) $ S.singleton
               (FullyQualifiedColumnName "default_db" "public" "bar" "a", "SELECT")
           )
+        , testPresto "SELECT 1 FROM (foo JOIN bar ON foo.a = bar.a);" defaultTestCatalog
+          ((@=?) $ S.fromList
+              [ (FullyQualifiedColumnName "default_db" "public" "foo" "a", "JOIN")
+              , (FullyQualifiedColumnName "default_db" "public" "bar" "a", "JOIN")
+              ]
+          )
 
         -- WHERE
         , testAll "SELECT 1 FROM foo WHERE a IS NOT NULL;" defaultTestCatalog
@@ -187,6 +224,12 @@ testColumnAccesses = test
               , (FullyQualifiedColumnName "default_db" "public" "foo" "a", "ORDER")
               ]
           )
+        , testPresto "SELECT cAlias FROM (foo) AS tAlias (cAlias) ORDER BY cAlias;" defaultTestCatalog
+          ((@=?) $ S.fromList
+              [ (FullyQualifiedColumnName "default_db" "public" "foo" "a", "SELECT")
+              , (FullyQualifiedColumnName "default_db" "public" "foo" "a", "ORDER")
+              ]
+          )
         , let query = TL.unlines
                 [ "SELECT arrayVal"
                 , "FROM foo AS fooAlias (arrayCol)"