{"push_id":2384930754,"size":2,"distinct_size":2,"ref":"refs/heads/fix-ppcderive","head":"3409be274f5bd86aabc68ab74d0101229536cbed","before":"4d70abdeaba64a4826c4f1894ff55968c8109924","commits":[{"sha":"9fb3acaf33d1e5e20d542161a82e4e80033bcefa","author":{"name":"Bhuvnesh Chaudhary","email":"34313b8cbbfe2bdbe5fc7d5d6a411e06acd38cd7@pivotal.io"},"message":"WIP","distinct":true,"url":"https://api.github.com/repos/bhuvnesh2703/gporca/commits/9fb3acaf33d1e5e20d542161a82e4e80033bcefa"},{"sha":"3409be274f5bd86aabc68ab74d0101229536cbed","author":{"name":"Bhuvnesh Chaudhary","email":"34313b8cbbfe2bdbe5fc7d5d6a411e06acd38cd7@pivotal.io"},"message":"Extract Constraint under Negation correctly\n\nWhile deriving the constraint properties for Aggregates, we attempt to\nextract the constraint only on the columns which are used in the\ngrouping column.  However, in case of negation constraint we cannot\nfurther simplify the constraint.\n\nFor instance if the query is of the form:\nexplain select distinct a from t1 where not (a = b);\n\nFor the predicate not (a = b), the constraint is of the form:\nNOT ({\"a\" (0), ranges: (-inf, inf) } AND {\"b\" (1), ranges: (-inf, inf) }))\n\nWhile deriving constraint for Agg as the grouping / distinct column is\na, we cannot break the constraint to NOT ({\"a\" (0), ranges: (-inf, inf) }) as that is equivalent to NOT (a)\nCurrently, the implementation recurses down the constraints and returns\n NOT ({\"a\" (0), ranges: (-inf, inf) }) resulting in wrong results.\n\nThe returned constraint is further used down during calculation of max\ncardinality, which may falsely report that the aggregation will not\nreturn any results and convert the query to a constant table get with a\nfalse filter.\n\nConsider the below case for wrong results:\n\n```sql\ncreate table t1 (a int, b int);\ninsert into t1 values (1, 1);\ninsert into t1 values (1, 2);\nexplain select distinct a from t2 where a is not null and not a = b;\n                QUERY PLAN\n------------------------------------------\n Result  (cost=0.00..0.00 rows=0 width=8)\n   One-Time Filter: false\n Optimizer: PQO version 2.55.4\n```\nInput to Expression Preprocessor:\n\n```\nAlgebrized query:\n+--CLogicalGbAgg( Global )\n   |--CLogicalSelect\n   |  |--CLogicalGet \"t1\"\n   |  +--CScalarBoolOp (EboolopAnd)\n   |     |--CScalarBoolOp (EboolopNot)\n   |     |  +--CScalarNullTest\n   |     |     +--CScalarIdent \"a\"\n   |     +--CScalarBoolOp (EboolopNot)\n   |        +--CScalarCmp (=)\n   |           |--CScalarIdent \"a\"\n   |           +--CScalarIdent \"b\"\n   +--CScalarProjectList\n```\n\nOutput of Expression Preprocessor:\n```\n+--CLogicalConstTableGet Columns: [\"a\" (0)] Values: []\n```\n\nAs we see above, preprocessing converts the input query to a constant\ntable get with false filter which shouldn't be done.\n1. During preprocessing, we derive the Property Constraint of\nCLogicalGbAgg by calling `CLogicalGbAgg::PpcDeriveConstraint` ->\n`PpcDeriveConstraintRestrict`\n2. PpcDeriveConstraintRestrict derives constraint only on the input\ncolumn set. In case of GbAgg, it derives the constraint on the grouping\ncolumn.\n\n```\nCLogical::PpcDeriveConstraintRestrict\n\t(\n\tIMemoryPool *pmp,\n\tCExpressionHandle &exprhdl,\n\tCColRefSet *pcrsOutput ==> grouping column\n\t)\n```\n3. In the repro query, a is the grouping column (i.e the distinct\nclause), so it tries to determine the constraint on `column a` and\nreturns the constraint as `NOT (a)`\n\n```\n   |     +--CScalarBoolOp (EboolopNot)\n   |        +--CScalarCmp (=)\n   |           |--CScalarIdent \"a\" (0)\n   |           +--CScalarIdent \"b\" (1)\n```\n4. The constraint derived is as : `(NOT {\"a\" (0), ranges: (-inf, inf)\n})` which means that its unbounded as the NOT flag is set and the range\nis (-inf, -inf)\n\n5. Then while deriving the MaxCard property for the global agg, if the\noperator produces no rows then MaxCard = 0\n\n```sql\nCLogicalGbAgg::Maxcard\n..\n..\n..\n        // contradictions produce no rows\n\tif (CDrvdPropRelational::Pdprel(exprhdl.Pdp())->Ppc()->FContradiction())\n\t{\n\t\treturn CMaxCard(0 /*ull*/);\n\t}\n```\n6. Since the MaxCard returns 0 rows, `PexprPruneEmptySubtrees(pmp,\npexprWithPreds);` step in expression preprocessing prunes the tree and\nreplaces that with a const table get with false filter\n\n7. Thus, wrong results.\n\nThis PR fixes the issue and returns the complete constraint on the\ncolumn a if there is a negation, i.e\nNOT ({\"a\" (0), ranges: (-inf, inf) } AND {\"b\" (1), ranges: (-inf, inf) }))\n\nSigned-off-by: Sambitesh Dash <sdash@pivotal.io>","distinct":true,"url":"https://api.github.com/repos/bhuvnesh2703/gporca/commits/3409be274f5bd86aabc68ab74d0101229536cbed"}]}
{"actor":{"display_login":"bhuvnesh2703"}}