From 59e93930256422bd6417a692abc70c25c525a724 Mon Sep 17 00:00:00 2001 From: rakeshkky Date: Tue, 13 Nov 2018 19:43:52 +0530 Subject: [PATCH 1/3] add statistical aggregate operations and count on columns, close #1028 --- .../src-lib/Hasura/GraphQL/Resolve/Insert.hs | 7 +- .../src-lib/Hasura/GraphQL/Resolve/Select.hs | 32 +- server/src-lib/Hasura/GraphQL/Schema.hs | 90 ++++-- server/src-lib/Hasura/RQL/DML/Count.hs | 2 +- server/src-lib/Hasura/RQL/DML/Returning.hs | 2 +- .../src-lib/Hasura/RQL/DML/Select/Internal.hs | 44 ++- server/src-lib/Hasura/SQL/DML.hs | 18 ++ server/src-lib/Hasura/SQL/Rewrite.hs | 1 + .../aggregations/article_agg_where.yaml | 274 ++++++++++++------ 9 files changed, 338 insertions(+), 132 deletions(-) diff --git a/server/src-lib/Hasura/GraphQL/Resolve/Insert.hs b/server/src-lib/Hasura/GraphQL/Resolve/Insert.hs index d967e51f414a4..968b2bda62cfe 100644 --- a/server/src-lib/Hasura/GraphQL/Resolve/Insert.hs +++ b/server/src-lib/Hasura/GraphQL/Resolve/Insert.hs @@ -154,7 +154,7 @@ parseOnConflict inpCols val = withPathK "on_conflict" $ flip withObject val $ \_ obj -> do actionM <- forM (OMap.lookup "action" obj) parseAction constraint <- parseConstraint obj - updColsM <- forM (OMap.lookup "update_columns" obj) parseUpdCols + updColsM <- forM (OMap.lookup "update_columns" obj) parseColumns -- consider "action" if "update_columns" is not mentioned return $ mkConflictClause $ case (updColsM, actionM) of (Just [], _) -> RI.CCDoNothing $ Just constraint @@ -177,11 +177,6 @@ parseOnConflict inpCols val = withPathK "on_conflict" $ (_, enumVal) <- asEnumVal v return $ ConstraintName $ G.unName $ G.unEnumValue enumVal - parseUpdCols v = flip withArray v $ \_ enumVals -> - forM enumVals $ \eVal -> do - (_, ev) <- asEnumVal eVal - return $ PGCol $ G.unName $ G.unEnumValue ev - mkConflictClause (RI.CCDoNothing constrM) = RI.CP1DoNothing $ fmap RI.Constraint constrM mkConflictClause (RI.CCUpdate constr updCols) = diff --git a/server/src-lib/Hasura/GraphQL/Resolve/Select.hs b/server/src-lib/Hasura/GraphQL/Resolve/Select.hs index 78cd307210d92..22fa3c6564c26 100644 --- a/server/src-lib/Hasura/GraphQL/Resolve/Select.hs +++ b/server/src-lib/Hasura/GraphQL/Resolve/Select.hs @@ -10,6 +10,7 @@ module Hasura.GraphQL.Resolve.Select ( convertSelect , convertSelectByPKey , convertAggSelect + , parseColumns , withSelSet , fromSelSet , fieldAsPath @@ -199,6 +200,29 @@ convertSelectByPKey qt permFilter fld = do return $ RS.selectP2 True (selData, prepArgs) -- agg select related +parseColumns :: MonadError QErr m => AnnGValue -> m [PGCol] +parseColumns val = + flip withArray val $ \_ vals -> + forM vals $ \v -> do + (_, enumVal) <- asEnumVal v + return $ PGCol $ G.unName $ G.unEnumValue enumVal + +convertCount :: MonadError QErr m => ArgsMap -> m S.CountType +convertCount args = do + columnsM <- withArgM args "columns" parseColumns + isDistinct <- or <$> withArgM args "distinct" parseDistinct + maybe (return S.CTStar) (mkCType isDistinct) columnsM + where + parseDistinct v = do + (_, val) <- asPGColVal v + case val of + PGValBoolean b -> return b + _ -> + throwVE "expecting Boolean for \"distinct\"" + + mkCType isDistinct cols = return $ + bool (S.CTSimple cols) (S.CTDistinct cols) isDistinct + convertColFlds :: Monad m => G.NamedType -> SelSet -> m RS.ColFlds convertColFlds ty selSet = @@ -216,12 +240,16 @@ convertAggFld ty selSet = fSelSet = _fSelSet fld case _fName fld of "__typename" -> return $ RS.AFExp $ G.unName $ G.unNamedType ty - "count" -> return RS.AFCount + "count" -> RS.AFCount <$> convertCount (_fArguments fld) "sum" -> RS.AFSum <$> convertColFlds fType fSelSet "avg" -> RS.AFAvg <$> convertColFlds fType fSelSet + "stddev" -> RS.AFStddev <$> convertColFlds fType fSelSet + "stddev_pop" -> RS.AFStddevPop <$> convertColFlds fType fSelSet + "variance" -> RS.AFVariance <$> convertColFlds fType fSelSet + "var_pop" -> RS.AFVarPop <$> convertColFlds fType fSelSet "max" -> RS.AFMax <$> convertColFlds fType fSelSet "min" -> RS.AFMin <$> convertColFlds fType fSelSet - G.Name t -> throw500 $ "unexpected field in _agg node: " <> t + G.Name t -> throw500 $ "unexpected field in _aggregate node: " <> t fromAggField :: (MonadError QErr m, MonadReader r m, Has FieldMap r, Has OrdByCtx r) diff --git a/server/src-lib/Hasura/GraphQL/Schema.hs b/server/src-lib/Hasura/GraphQL/Schema.hs index fa7cf7d9b774c..f820092d7d166 100644 --- a/server/src-lib/Hasura/GraphQL/Schema.hs +++ b/server/src-lib/Hasura/GraphQL/Schema.hs @@ -378,7 +378,14 @@ mkTableAggObj tn = {- type table_aggregate_fields{ count: Int - sum: table_num_fields + sum: table_sum_fields + avg: table_avg_fields + stddev: table_stddev_fields + stddev_pop: table_stddev_pop_fields + variance: table_variance_fields + var_pop: table_var_pop_fields + max: table_max_fields + min: table_min_fields } -} mkTableAggFldsObj @@ -390,14 +397,32 @@ mkTableAggFldsObj tn numCols compCols = desc = G.Description $ "aggregate fields of " <>> tn - countFld = ObjFldInfo Nothing "count" Map.empty $ G.toGT $ + countFld = ObjFldInfo Nothing "count" countParams $ G.toGT $ mkScalarTy PGInteger - numFlds = bool [sumFld, avgFld] [] $ null numCols + countParams = fromInpValL [countColInpVal, distinctInpVal] + + countColInpVal = InpValInfo Nothing "columns" $ G.toGT $ + G.toLT $ G.toNT $ mkSelColumnInpTy tn + distinctInpVal = InpValInfo Nothing "distinct" $ G.toGT $ + mkScalarTy PGBoolean + + numFlds = bool [ sumFld + , avgFld + , stddevFld + , stddevPopFld + , varianceFld + , varPopFld + ] [] $ null numCols compFlds = bool [maxFld, minFld] [] $ null compCols sumFld = mkColOpFld "sum" avgFld = mkColOpFld "avg" + stddevFld = mkColOpFld "stddev" + stddevPopFld = mkColOpFld "stddev_pop" + varianceFld = mkColOpFld "variance" + varPopFld = mkColOpFld "var_pop" + maxFld = mkColOpFld "max" minFld = mkColOpFld "min" @@ -405,7 +430,7 @@ mkTableAggFldsObj tn numCols compCols = mkTableColAggFldsTy op tn {- -type table_sum_fields{ +type table__fields{ num_col: Int . . . . @@ -840,10 +865,15 @@ mkConstraintInpTy :: QualifiedTable -> G.NamedType mkConstraintInpTy tn = G.NamedType $ qualTableToName tn <> "_constraint" --- table_column -mkColumnInpTy :: QualifiedTable -> G.NamedType -mkColumnInpTy tn = - G.NamedType $ qualTableToName tn <> "_column" +-- table_update_column +mkUpdColumnInpTy :: QualifiedTable -> G.NamedType +mkUpdColumnInpTy tn = + G.NamedType $ qualTableToName tn <> "_update_column" + +--table_select_column +mkSelColumnInpTy :: QualifiedTable -> G.NamedType +mkSelColumnInpTy tn = + G.NamedType $ qualTableToName tn <> "_select_column" {- input table_obj_rel_insert_input { data: table_insert_input! @@ -946,7 +976,7 @@ mkOnConflictInp tn = G.toGT $ G.toNT $ mkConstraintInpTy tn updateColumnsInpVal = InpValInfo Nothing (G.Name "update_columns") $ - G.toGT $ G.toLT $ G.toNT $ mkColumnInpTy tn + G.toGT $ G.toLT $ G.toNT $ mkUpdColumnInpTy tn {- insert_table( @@ -991,17 +1021,27 @@ mkConstriantTy tn cons = enumTyInfo EnumValInfo (Just "unique or primary key constraint") (G.EnumValue $ G.Name n) False -mkColumnTy :: QualifiedTable -> [PGCol] -> EnumTyInfo -mkColumnTy tn cols = enumTyInfo +mkColumnEnumVal :: PGCol -> EnumValInfo +mkColumnEnumVal (PGCol col) = + EnumValInfo (Just "column name") (G.EnumValue $ G.Name col) False + +mkUpdColumnTy :: QualifiedTable -> [PGCol] -> EnumTyInfo +mkUpdColumnTy tn cols = enumTyInfo where - enumTyInfo = EnumTyInfo (Just desc) (mkColumnInpTy tn) $ + enumTyInfo = EnumTyInfo (Just desc) (mkUpdColumnInpTy tn) $ mapFromL _eviVal $ map mkColumnEnumVal cols desc = G.Description $ - "columns of table " <>> tn + "update columns of table " <>> tn - mkColumnEnumVal (PGCol col) = - EnumValInfo (Just "column name") (G.EnumValue $ G.Name col) False +mkSelColumnTy :: QualifiedTable -> [PGCol] -> EnumTyInfo +mkSelColumnTy tn cols = enumTyInfo + where + enumTyInfo = EnumTyInfo (Just desc) (mkSelColumnInpTy tn) $ + mapFromL _eviVal $ map mkColumnEnumVal cols + + desc = G.Description $ + "select columns of table " <>> tn mkConflictActionTy :: EnumTyInfo mkConflictActionTy = EnumTyInfo (Just desc) ty $ mapFromL _eviVal @@ -1108,7 +1148,7 @@ mkOnConflictTypes tn c cols = where tyInfos = [ TIEnum mkConflictActionTy , TIEnum $ mkConstriantTy tn constraints - , TIEnum $ mkColumnTy tn cols + , TIEnum $ mkUpdColumnTy tn cols , TIInpObj $ mkOnConflictInp tn ] constraints = filter isUniqueOrPrimary c @@ -1159,6 +1199,7 @@ mkGCtxRole' tn insPermM selPermM updColsM delPermM pkeyCols constraints viM allC , TIInpObj <$> mutHelper viIsUpdatable updSetInpObjM , TIInpObj <$> mutHelper viIsUpdatable updIncInpObjM , TIObj <$> mutRespObjM + , TIEnum <$> selColInpTyM ] mutHelper f objM = bool Nothing objM $ isMutable f viM @@ -1190,6 +1231,8 @@ mkGCtxRole' tn insPermM selPermM updColsM delPermM pkeyCols constraints viM allC updSetInpObjFldsM = mkColFldMap (mkUpdSetTy tn) <$> updColsM selFldsM = snd <$> selPermM + selColsM = (map pgiName . lefts) <$> selFldsM + selColInpTyM = mkSelColumnTy tn <$> selColsM -- boolexp input type boolExpInpObjM = case selFldsM of Just selFlds -> Just $ mkBoolExpInp tn selFlds @@ -1240,14 +1283,25 @@ mkGCtxRole' tn insPermM selPermM updColsM delPermM pkeyCols constraints viM allC _ -> [] getNumCols = onlyNumCols . lefts getCompCols = onlyComparableCols . lefts + onlyFloat = const $ mkScalarTy PGFloat mkColAggFldsObjs flds = let numCols = getNumCols flds compCols = getCompCols flds sumFldsObj = mkTableColAggFldsObj tn "sum" mkScalarTy numCols - avgFldsObj = mkTableColAggFldsObj tn "avg" (const $ mkScalarTy PGFloat) numCols + avgFldsObj = mkTableColAggFldsObj tn "avg" onlyFloat numCols + stddevFldsObj = mkTableColAggFldsObj tn "stddev" onlyFloat numCols + stddevPopFldsObj = mkTableColAggFldsObj tn "stddev_pop" onlyFloat numCols + varianceFldsObj = mkTableColAggFldsObj tn "variance" onlyFloat numCols + varPopFldsObj = mkTableColAggFldsObj tn "var_pop" onlyFloat numCols maxFldsObj = mkTableColAggFldsObj tn "max" mkScalarTy compCols minFldsObj = mkTableColAggFldsObj tn "min" mkScalarTy compCols - numFldsObjs = bool [sumFldsObj, avgFldsObj] [] $ null numCols + numFldsObjs = bool [ sumFldsObj + , avgFldsObj + , stddevFldsObj + , stddevPopFldsObj + , varianceFldsObj + , varPopFldsObj + ] [] $ null numCols compFldsObjs = bool [maxFldsObj, minFldsObj] [] $ null compCols in numFldsObjs <> compFldsObjs -- the fields used in table object diff --git a/server/src-lib/Hasura/RQL/DML/Count.hs b/server/src-lib/Hasura/RQL/DML/Count.hs index a95a543e61dfd..5fcc703cb4e45 100644 --- a/server/src-lib/Hasura/RQL/DML/Count.hs +++ b/server/src-lib/Hasura/RQL/DML/Count.hs @@ -41,7 +41,7 @@ mkSQLCount :: CountQueryP1 -> S.Select mkSQLCount (CountQueryP1 tn (permFltr, mWc) mDistCols) = S.mkSelect - { S.selExtr = [S.Extractor (S.SEFnApp "count" [S.SEStar] Nothing) Nothing] + { S.selExtr = [S.Extractor S.countStar Nothing] , S.selFrom = Just $ S.FromExp [S.mkSelFromExp False innerSel $ TableName "r"] } diff --git a/server/src-lib/Hasura/RQL/DML/Returning.hs b/server/src-lib/Hasura/RQL/DML/Returning.hs index b98df324f4442..ecb3733b8b0dc 100644 --- a/server/src-lib/Hasura/RQL/DML/Returning.hs +++ b/server/src-lib/Hasura/RQL/DML/Returning.hs @@ -54,7 +54,7 @@ mkMutFldExp :: QualifiedTable -> Bool -> MutFld -> S.SQLExp mkMutFldExp qt singleObj = \case MCount -> S.SESelect $ S.mkSelect - { S.selExtr = [S.Extractor (S.SEUnsafe "count(*)") Nothing] + { S.selExtr = [S.Extractor S.countStar Nothing] , S.selFrom = Just $ S.FromExp $ pure frmItem } MExp t -> S.SELit t diff --git a/server/src-lib/Hasura/RQL/DML/Select/Internal.hs b/server/src-lib/Hasura/RQL/DML/Select/Internal.hs index 8ac8da95f138d..827866ffcd1dd 100644 --- a/server/src-lib/Hasura/RQL/DML/Select/Internal.hs +++ b/server/src-lib/Hasura/RQL/DML/Select/Internal.hs @@ -102,9 +102,13 @@ data PGColFld type ColFlds = [(T.Text, PGColFld)] data AggFld - = AFCount + = AFCount !S.CountType | AFSum !ColFlds | AFAvg !ColFlds + | AFStddev !ColFlds + | AFStddevPop !ColFlds + | AFVariance !ColFlds + | AFVarPop !ColFlds | AFMax !ColFlds | AFMin !ColFlds | AFExp !T.Text @@ -165,12 +169,16 @@ aggFldToExp aggFlds = jsonRow jsonRow = S.applyJsonBuildObj (concatMap aggToFlds aggFlds) withAls fldName sqlExp = [S.SELit fldName, sqlExp] aggToFlds (t, fld) = withAls t $ case fld of - AFCount -> S.SEUnsafe "count(*)" - AFSum sumFlds -> colFldsToObj "sum" sumFlds - AFAvg avgFlds -> colFldsToObj "avg" avgFlds - AFMax maxFlds -> colFldsToObj "max" maxFlds - AFMin minFlds -> colFldsToObj "min" minFlds - AFExp e -> S.SELit e + AFCount cty -> S.SECount cty + AFSum sumFlds -> colFldsToObj "sum" sumFlds + AFAvg avgFlds -> colFldsToObj "avg" avgFlds + AFStddev stddevFlds -> colFldsToObj "stddev" stddevFlds + AFStddevPop stddevPopFlds -> colFldsToObj "stddev_pop" stddevPopFlds + AFVariance varFlds -> colFldsToObj "variance" varFlds + AFVarPop varPopFlds -> colFldsToObj "var_pop" varPopFlds + AFMax maxFlds -> colFldsToObj "max" maxFlds + AFMin minFlds -> colFldsToObj "min" minFlds + AFExp e -> S.SELit e colFldsToObj op flds = S.applyJsonBuildObj $ concatMap (colFldsToExtr op) flds @@ -442,12 +450,22 @@ mkBaseNode pfx fldAls annSelFlds tableFrom tablePerm tableArgs = ) TAFExp _ -> (HM.fromList obExtrs, HM.empty, HM.empty, HM.empty) - fetchExtrFromAggFld AFCount = [] - fetchExtrFromAggFld (AFSum sumFlds) = colFldsToExps sumFlds - fetchExtrFromAggFld (AFAvg avgFlds) = colFldsToExps avgFlds - fetchExtrFromAggFld (AFMax maxFlds) = colFldsToExps maxFlds - fetchExtrFromAggFld (AFMin minFlds) = colFldsToExps minFlds - fetchExtrFromAggFld (AFExp _) = [] + fetchExtrFromAggFld (AFCount cty) = countTyToExps cty + fetchExtrFromAggFld (AFSum sumFlds) = colFldsToExps sumFlds + fetchExtrFromAggFld (AFAvg avgFlds) = colFldsToExps avgFlds + fetchExtrFromAggFld (AFStddev stddevFlds) = colFldsToExps stddevFlds + fetchExtrFromAggFld (AFStddevPop stddevPopFlds) = colFldsToExps stddevPopFlds + fetchExtrFromAggFld (AFVariance varFlds) = colFldsToExps varFlds + fetchExtrFromAggFld (AFVarPop varPopFlds) = colFldsToExps varPopFlds + fetchExtrFromAggFld (AFMax maxFlds) = colFldsToExps maxFlds + fetchExtrFromAggFld (AFMin minFlds) = colFldsToExps minFlds + fetchExtrFromAggFld (AFExp _) = [] + + countTyToExps S.CTStar = [] + countTyToExps (S.CTSimple cols) = colsToExps cols + countTyToExps (S.CTDistinct cols) = colsToExps cols + + colsToExps = mapMaybe (mkColExp . PCFCol) colFldsToExps = mapMaybe (mkColExp . snd) diff --git a/server/src-lib/Hasura/SQL/DML.hs b/server/src-lib/Hasura/SQL/DML.hs index 59490ec37bbc9..8264662432141 100644 --- a/server/src-lib/Hasura/SQL/DML.hs +++ b/server/src-lib/Hasura/SQL/DML.hs @@ -238,6 +238,19 @@ jsonType = AnnType "json" jsonbType :: AnnType jsonbType = AnnType "jsonb" +data CountType + = CTStar + | CTSimple ![PGCol] + | CTDistinct ![PGCol] + deriving(Show, Eq) + +instance ToSQL CountType where + toSQL CTStar = "*" + toSQL (CTSimple cols) = + paren $ ", " <+> cols + toSQL (CTDistinct cols) = + "DISTINCT" <-> paren (", " <+> cols) + data SQLExp = SEPrep !Int | SELit !T.Text @@ -255,6 +268,7 @@ data SQLExp | SEBool !BoolExp | SEExcluded !T.Text | SEArray ![SQLExp] + | SECount !CountType deriving (Show, Eq) newtype Alias @@ -270,6 +284,9 @@ instance ToSQL Alias where toAlias :: (IsIden a) => a -> Alias toAlias = Alias . toIden +countStar :: SQLExp +countStar = SECount CTStar + instance ToSQL SQLExp where toSQL (SEPrep argNumber) = TB.char '$' <> fromString (show argNumber) @@ -304,6 +321,7 @@ instance ToSQL SQLExp where <> toSQL (PGCol t) toSQL (SEArray exps) = "ARRAY" <> TB.char '[' <> (", " <+> exps) <> TB.char ']' + toSQL (SECount ty) = "COUNT" <> paren (toSQL ty) intToSQLExp :: Int -> SQLExp intToSQLExp = diff --git a/server/src-lib/Hasura/SQL/Rewrite.hs b/server/src-lib/Hasura/SQL/Rewrite.hs index 51d7de8b83e34..54852ec12630e 100644 --- a/server/src-lib/Hasura/SQL/Rewrite.hs +++ b/server/src-lib/Hasura/SQL/Rewrite.hs @@ -171,6 +171,7 @@ uSqlExp = restoringIdens . \case S.SEExcluded <$> return t S.SEArray l -> S.SEArray <$> mapM uSqlExp l + S.SECount cty -> return $ S.SECount cty where uQual = \case S.QualIden iden -> S.QualIden <$> getIden iden diff --git a/server/tests-py/queries/graphql_query/aggregations/article_agg_where.yaml b/server/tests-py/queries/graphql_query/aggregations/article_agg_where.yaml index de06ad1f6572e..2e0a05dbe8eb1 100644 --- a/server/tests-py/queries/graphql_query/aggregations/article_agg_where.yaml +++ b/server/tests-py/queries/graphql_query/aggregations/article_agg_where.yaml @@ -6,6 +6,8 @@ response: article_aggregate: aggregate: count: 2 + count_columns_id: 2 + count_columns_id_distinct: 2 sum: id: 5 id_sum: 5 @@ -26,6 +28,46 @@ response: id_avg: 2.5 author_id: 1.5 author_id_avg: 1.5 + stddev: + id: 0.7071067811865476 + id_stddev: 0.7071067811865476 + author_id: 0.7071067811865476 + author_id_stddev: 0.7071067811865476 + stddev_fields: + id: 0.7071067811865476 + id_stddev: 0.7071067811865476 + author_id: 0.7071067811865476 + author_id_stddev: 0.7071067811865476 + stddev_pop: + id: 0.5 + id_stddev_pop: 0.5 + author_id: 0.5 + author_id_stddev_pop: 0.5 + stddev_pop_fields: + id: 0.5 + id_stddev_pop: 0.5 + author_id: 0.5 + author_id_stddev_pop: 0.5 + variance: + id: 0.5 + id_variance: 0.5 + author_id: 0.5 + author_id_variance: 0.5 + variance_fields: + id: 0.5 + id_variance: 0.5 + author_id: 0.5 + author_id_variance: 0.5 + var_pop: + id: 0.25 + id_var_pop: 0.25 + author_id: 0.25 + author_id_var_pop: 0.25 + var_pop_fields: + id: 0.25 + id_var_pop: 0.25 + author_id: 0.25 + author_id_var_pop: 0.25 max: id: 3 id_max: 3 @@ -95,94 +137,144 @@ response: query: query: | - query{ - article_aggregate(where: {id: {_gt: 1}}){ - aggregate{ - count - sum{ - id - id_sum: id - author_id - author_id_sum: author_id - } - sum_fields: sum{ - id - id_sum: id - author_id - author_id_sum: author_id - } - avg{ - id - id_avg: id - author_id - author_id_avg: author_id - } - avg_fields: avg{ - id - id_avg: id - author_id - author_id_avg: author_id - } - max{ - id - id_max: id - title - title_max: title - content - content_max: content - author_id - author_id_max: author_id - } - max_fields: max{ - id - id_max: id - title - title_max: title - content - content_max: content - author_id - author_id_max: author_id - } - min{ - id - id_min: id - title - title_min: title - content - content_min: content - author_id - author_id_min: author_id - } - min_fields: min{ - id - id_min: id - title - title_min: title - content - content_min: content - author_id - author_id_min: author_id - } - } - nodes{ - id - title - content - is_published - author{ - id - name - } - } - articles: nodes{ - id - title - content - is_published - author{ - id - name - } - } - } - } + query { + article_aggregate(where: {id: {_gt: 1}}) { + aggregate { + count + count_columns_id: count(columns: [id]) + count_columns_id_distinct: count(columns: [id], distinct: true) + sum { + id + id_sum: id + author_id + author_id_sum: author_id + } + sum_fields: sum { + id + id_sum: id + author_id + author_id_sum: author_id + } + avg { + id + id_avg: id + author_id + author_id_avg: author_id + } + avg_fields: avg { + id + id_avg: id + author_id + author_id_avg: author_id + } + stddev { + id + id_stddev: id + author_id + author_id_stddev: author_id + } + stddev_fields: stddev { + id + id_stddev: id + author_id + author_id_stddev: author_id + } + stddev_pop { + id + id_stddev_pop: id + author_id + author_id_stddev_pop: author_id + } + stddev_pop_fields: stddev_pop { + id + id_stddev_pop: id + author_id + author_id_stddev_pop: author_id + } + variance { + id + id_variance: id + author_id + author_id_variance: author_id + } + variance_fields: variance { + id + id_variance: id + author_id + author_id_variance: author_id + } + var_pop { + id + id_var_pop: id + author_id + author_id_var_pop: author_id + } + var_pop_fields: var_pop { + id + id_var_pop: id + author_id + author_id_var_pop: author_id + } + max { + id + id_max: id + title + title_max: title + content + content_max: content + author_id + author_id_max: author_id + } + max_fields: max { + id + id_max: id + title + title_max: title + content + content_max: content + author_id + author_id_max: author_id + } + min { + id + id_min: id + title + title_min: title + content + content_min: content + author_id + author_id_min: author_id + } + min_fields: min { + id + id_min: id + title + title_min: title + content + content_min: content + author_id + author_id_min: author_id + } + } + nodes { + id + title + content + is_published + author { + id + name + } + } + articles: nodes { + id + title + content + is_published + author { + id + name + } + } + } + } From 9d1e057995fb04c5a13669c8d3c1a2090c4fa35b Mon Sep 17 00:00:00 2001 From: rakeshkky Date: Tue, 13 Nov 2018 20:12:27 +0530 Subject: [PATCH 2/3] throw500 if input type for 'distinct' is not Boolean --- server/src-lib/Hasura/GraphQL/Resolve/Select.hs | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/server/src-lib/Hasura/GraphQL/Resolve/Select.hs b/server/src-lib/Hasura/GraphQL/Resolve/Select.hs index 22fa3c6564c26..f507649299b62 100644 --- a/server/src-lib/Hasura/GraphQL/Resolve/Select.hs +++ b/server/src-lib/Hasura/GraphQL/Resolve/Select.hs @@ -218,7 +218,7 @@ convertCount args = do case val of PGValBoolean b -> return b _ -> - throwVE "expecting Boolean for \"distinct\"" + throw500 "expecting Boolean for \"distinct\"" mkCType isDistinct cols = return $ bool (S.CTSimple cols) (S.CTDistinct cols) isDistinct From 607145ea44c23cddc04585734f2f0127f592846b Mon Sep 17 00:00:00 2001 From: rakeshkky Date: Wed, 14 Nov 2018 15:05:28 +0530 Subject: [PATCH 3/3] add 'stddev_samp' and 'var_samp' fields, refactor aggregate code --- .../src-lib/Hasura/GraphQL/Resolve/Select.hs | 17 +++--- server/src-lib/Hasura/GraphQL/Schema.hs | 56 ++++++++----------- .../src-lib/Hasura/RQL/DML/Select/Internal.hs | 47 ++++++---------- .../aggregations/article_agg_where.yaml | 44 +++++++++++++++ 4 files changed, 90 insertions(+), 74 deletions(-) diff --git a/server/src-lib/Hasura/GraphQL/Resolve/Select.hs b/server/src-lib/Hasura/GraphQL/Resolve/Select.hs index f507649299b62..38949d1cbe31c 100644 --- a/server/src-lib/Hasura/GraphQL/Resolve/Select.hs +++ b/server/src-lib/Hasura/GraphQL/Resolve/Select.hs @@ -33,6 +33,7 @@ import qualified Hasura.SQL.DML as S import Hasura.GraphQL.Resolve.BoolExp import Hasura.GraphQL.Resolve.Context import Hasura.GraphQL.Resolve.InputValue +import Hasura.GraphQL.Schema (isAggFld) import Hasura.GraphQL.Validate.Field import Hasura.GraphQL.Validate.Types import Hasura.RQL.DML.Internal (onlyPositiveInt) @@ -241,15 +242,13 @@ convertAggFld ty selSet = case _fName fld of "__typename" -> return $ RS.AFExp $ G.unName $ G.unNamedType ty "count" -> RS.AFCount <$> convertCount (_fArguments fld) - "sum" -> RS.AFSum <$> convertColFlds fType fSelSet - "avg" -> RS.AFAvg <$> convertColFlds fType fSelSet - "stddev" -> RS.AFStddev <$> convertColFlds fType fSelSet - "stddev_pop" -> RS.AFStddevPop <$> convertColFlds fType fSelSet - "variance" -> RS.AFVariance <$> convertColFlds fType fSelSet - "var_pop" -> RS.AFVarPop <$> convertColFlds fType fSelSet - "max" -> RS.AFMax <$> convertColFlds fType fSelSet - "min" -> RS.AFMin <$> convertColFlds fType fSelSet - G.Name t -> throw500 $ "unexpected field in _aggregate node: " <> t + n -> do + colFlds <- convertColFlds fType fSelSet + unless (isAggFld n) $ throwInvalidFld n + return $ RS.AFOp $ RS.AggOp (G.unName n) colFlds + where + throwInvalidFld (G.Name t) = + throw500 $ "unexpected field in _aggregate node: " <> t fromAggField :: (MonadError QErr m, MonadReader r m, Has FieldMap r, Has OrdByCtx r) diff --git a/server/src-lib/Hasura/GraphQL/Schema.hs b/server/src-lib/Hasura/GraphQL/Schema.hs index f820092d7d166..6aea90db905e7 100644 --- a/server/src-lib/Hasura/GraphQL/Schema.hs +++ b/server/src-lib/Hasura/GraphQL/Schema.hs @@ -17,6 +17,7 @@ module Hasura.GraphQL.Schema , InsCtx(..) , InsCtxMap , RelationInfoMap + , isAggFld ) where import Data.Has @@ -153,6 +154,17 @@ isRelNullable fim ri = isNullable lColInfos = getColInfos lCols allCols isNullable = any pgiIsNullable lColInfos +numAggOps :: [G.Name] +numAggOps = [ "sum", "avg", "stddev", "stddev_samp", "stddev_pop" + , "variance", "var_samp", "var_pop" + ] + +compAggOps :: [G.Name] +compAggOps = ["max", "min"] + +isAggFld :: G.Name -> Bool +isAggFld = flip elem (numAggOps <> compAggOps) + mkColName :: PGCol -> G.Name mkColName (PGCol n) = G.Name n @@ -407,24 +419,8 @@ mkTableAggFldsObj tn numCols compCols = distinctInpVal = InpValInfo Nothing "distinct" $ G.toGT $ mkScalarTy PGBoolean - numFlds = bool [ sumFld - , avgFld - , stddevFld - , stddevPopFld - , varianceFld - , varPopFld - ] [] $ null numCols - compFlds = bool [maxFld, minFld] [] $ null compCols - - sumFld = mkColOpFld "sum" - avgFld = mkColOpFld "avg" - stddevFld = mkColOpFld "stddev" - stddevPopFld = mkColOpFld "stddev_pop" - varianceFld = mkColOpFld "variance" - varPopFld = mkColOpFld "var_pop" - - maxFld = mkColOpFld "max" - minFld = mkColOpFld "min" + numFlds = bool (map mkColOpFld numAggOps) [] $ null numCols + compFlds = bool (map mkColOpFld compAggOps) [] $ null compCols mkColOpFld op = ObjFldInfo Nothing op Map.empty $ G.toGT $ mkTableColAggFldsTy op tn @@ -1284,25 +1280,17 @@ mkGCtxRole' tn insPermM selPermM updColsM delPermM pkeyCols constraints viM allC getNumCols = onlyNumCols . lefts getCompCols = onlyComparableCols . lefts onlyFloat = const $ mkScalarTy PGFloat + + mkTypeMaker "sum" = mkScalarTy + mkTypeMaker _ = onlyFloat + mkColAggFldsObjs flds = let numCols = getNumCols flds compCols = getCompCols flds - sumFldsObj = mkTableColAggFldsObj tn "sum" mkScalarTy numCols - avgFldsObj = mkTableColAggFldsObj tn "avg" onlyFloat numCols - stddevFldsObj = mkTableColAggFldsObj tn "stddev" onlyFloat numCols - stddevPopFldsObj = mkTableColAggFldsObj tn "stddev_pop" onlyFloat numCols - varianceFldsObj = mkTableColAggFldsObj tn "variance" onlyFloat numCols - varPopFldsObj = mkTableColAggFldsObj tn "var_pop" onlyFloat numCols - maxFldsObj = mkTableColAggFldsObj tn "max" mkScalarTy compCols - minFldsObj = mkTableColAggFldsObj tn "min" mkScalarTy compCols - numFldsObjs = bool [ sumFldsObj - , avgFldsObj - , stddevFldsObj - , stddevPopFldsObj - , varianceFldsObj - , varPopFldsObj - ] [] $ null numCols - compFldsObjs = bool [maxFldsObj, minFldsObj] [] $ null compCols + mkNumObjFld n = mkTableColAggFldsObj tn n (mkTypeMaker n) numCols + mkCompObjFld n = mkTableColAggFldsObj tn n mkScalarTy compCols + numFldsObjs = bool (map mkNumObjFld numAggOps) [] $ null numCols + compFldsObjs = bool (map mkCompObjFld compAggOps) [] $ null compCols in numFldsObjs <> compFldsObjs -- the fields used in table object selObjFldsM = mkFldMap (mkTableTy tn) <$> selFldsM diff --git a/server/src-lib/Hasura/RQL/DML/Select/Internal.hs b/server/src-lib/Hasura/RQL/DML/Select/Internal.hs index 827866ffcd1dd..954ee0cdb9e20 100644 --- a/server/src-lib/Hasura/RQL/DML/Select/Internal.hs +++ b/server/src-lib/Hasura/RQL/DML/Select/Internal.hs @@ -101,16 +101,15 @@ data PGColFld type ColFlds = [(T.Text, PGColFld)] +data AggOp + = AggOp + { _aoOp :: !T.Text + , _aoFlds :: !ColFlds + } deriving (Show, Eq) + data AggFld = AFCount !S.CountType - | AFSum !ColFlds - | AFAvg !ColFlds - | AFStddev !ColFlds - | AFStddevPop !ColFlds - | AFVariance !ColFlds - | AFVarPop !ColFlds - | AFMax !ColFlds - | AFMin !ColFlds + | AFOp !AggOp | AFExp !T.Text deriving (Show, Eq) @@ -169,18 +168,11 @@ aggFldToExp aggFlds = jsonRow jsonRow = S.applyJsonBuildObj (concatMap aggToFlds aggFlds) withAls fldName sqlExp = [S.SELit fldName, sqlExp] aggToFlds (t, fld) = withAls t $ case fld of - AFCount cty -> S.SECount cty - AFSum sumFlds -> colFldsToObj "sum" sumFlds - AFAvg avgFlds -> colFldsToObj "avg" avgFlds - AFStddev stddevFlds -> colFldsToObj "stddev" stddevFlds - AFStddevPop stddevPopFlds -> colFldsToObj "stddev_pop" stddevPopFlds - AFVariance varFlds -> colFldsToObj "variance" varFlds - AFVarPop varPopFlds -> colFldsToObj "var_pop" varPopFlds - AFMax maxFlds -> colFldsToObj "max" maxFlds - AFMin minFlds -> colFldsToObj "min" minFlds - AFExp e -> S.SELit e - - colFldsToObj op flds = + AFCount cty -> S.SECount cty + AFOp aggOp -> aggOpToObj aggOp + AFExp e -> S.SELit e + + aggOpToObj (AggOp op flds) = S.applyJsonBuildObj $ concatMap (colFldsToExtr op) flds colFldsToExtr op (t, PCFCol col) = @@ -450,16 +442,9 @@ mkBaseNode pfx fldAls annSelFlds tableFrom tablePerm tableArgs = ) TAFExp _ -> (HM.fromList obExtrs, HM.empty, HM.empty, HM.empty) - fetchExtrFromAggFld (AFCount cty) = countTyToExps cty - fetchExtrFromAggFld (AFSum sumFlds) = colFldsToExps sumFlds - fetchExtrFromAggFld (AFAvg avgFlds) = colFldsToExps avgFlds - fetchExtrFromAggFld (AFStddev stddevFlds) = colFldsToExps stddevFlds - fetchExtrFromAggFld (AFStddevPop stddevPopFlds) = colFldsToExps stddevPopFlds - fetchExtrFromAggFld (AFVariance varFlds) = colFldsToExps varFlds - fetchExtrFromAggFld (AFVarPop varPopFlds) = colFldsToExps varPopFlds - fetchExtrFromAggFld (AFMax maxFlds) = colFldsToExps maxFlds - fetchExtrFromAggFld (AFMin minFlds) = colFldsToExps minFlds - fetchExtrFromAggFld (AFExp _) = [] + fetchExtrFromAggFld (AFCount cty) = countTyToExps cty + fetchExtrFromAggFld (AFOp aggOp) = aggOpToExps aggOp + fetchExtrFromAggFld (AFExp _) = [] countTyToExps S.CTStar = [] countTyToExps (S.CTSimple cols) = colsToExps cols @@ -467,7 +452,7 @@ mkBaseNode pfx fldAls annSelFlds tableFrom tablePerm tableArgs = colsToExps = mapMaybe (mkColExp . PCFCol) - colFldsToExps = mapMaybe (mkColExp . snd) + aggOpToExps = mapMaybe (mkColExp . snd) . _aoFlds mkColExp (PCFCol c) = let qualCol = S.mkQIdenExp (mkBaseTableAls pfx) (toIden c) diff --git a/server/tests-py/queries/graphql_query/aggregations/article_agg_where.yaml b/server/tests-py/queries/graphql_query/aggregations/article_agg_where.yaml index 2e0a05dbe8eb1..5c44ca2943082 100644 --- a/server/tests-py/queries/graphql_query/aggregations/article_agg_where.yaml +++ b/server/tests-py/queries/graphql_query/aggregations/article_agg_where.yaml @@ -38,6 +38,16 @@ response: id_stddev: 0.7071067811865476 author_id: 0.7071067811865476 author_id_stddev: 0.7071067811865476 + stddev_samp: + id: 0.7071067811865476 + id_stddev_samp: 0.7071067811865476 + author_id: 0.7071067811865476 + author_id_stddev_samp: 0.7071067811865476 + stddev_samp_fields: + id: 0.7071067811865476 + id_stddev_samp: 0.7071067811865476 + author_id: 0.7071067811865476 + author_id_stddev_samp: 0.7071067811865476 stddev_pop: id: 0.5 id_stddev_pop: 0.5 @@ -58,6 +68,16 @@ response: id_variance: 0.5 author_id: 0.5 author_id_variance: 0.5 + var_samp: + id: 0.5 + id_var_samp: 0.5 + author_id: 0.5 + author_id_var_samp: 0.5 + var_samp_fields: + id: 0.25 + id_var_pop: 0.25 + author_id: 0.25 + author_id_var_pop: 0.25 var_pop: id: 0.25 id_var_pop: 0.25 @@ -179,6 +199,18 @@ query: author_id author_id_stddev: author_id } + stddev_samp { + id + id_stddev_samp: id + author_id + author_id_stddev_samp: author_id + } + stddev_samp_fields: stddev_samp { + id + id_stddev_samp: id + author_id + author_id_stddev_samp: author_id + } stddev_pop { id id_stddev_pop: id @@ -203,6 +235,18 @@ query: author_id author_id_variance: author_id } + var_samp { + id + id_var_samp: id + author_id + author_id_var_samp: author_id + } + var_samp_fields: var_pop { + id + id_var_pop: id + author_id + author_id_var_pop: author_id + } var_pop { id id_var_pop: id