pgsql-5018

Version:

8.4.0

Links:

bug report: http://archives.postgresql.org/pgsql-bugs/2009-08/msg00307.php

Patch: http://archives.postgresql.org/pgsql-committers/2009-08/msg00294.php

Symptom:

SELECT lead(foo) OVER(ORDER BY foo) AS foo FROM (VALUES(0)) bar(foo);
ERROR:  window functions not allowed in window definition

How it is diagnosed:

Reproduced.

Root cause:
The parsing logic of the query statement was not correct. In particular, the field: ((Node*)expr)->type should not be set as T_WindowFunc.

   779 static Query *

   780 transformSelectStmt(ParseState *pstate, SelectStmt *stmt)

   781 {

                … ...

   886     if (pstate->p_hasWindowFuncs)

   887         parseCheckWindowFuncs(pstate, qry);

              }

parse_agg.c:

parseCheckWindowFuncs (ParseState *pstate, Query *qry) {

    foreach(l, qry->windowClause)

    {

        WindowClause *wc = (WindowClause *) lfirst(l);

        ListCell   *l2;

        foreach(l2, wc->orderClause)

        {

            SortGroupClause *grpcl = (SortGroupClause *) lfirst(l2);

            Node       *expr;

            expr = get_sortgroupclause_expr(grpcl, qry->targetList);

    /* Here, the returned ‘expr’: ((Node*)expr)->type == T_WindowFunc. In the fixed version, it should be T_Var. checkExprHasWindowFuncs essentially checks whether expr->type is of type: T_WindowFunc.*/

            if (checkExprHasWindowFuncs(expr))

                ereport(ERROR,

                        (errcode(ERRCODE_WINDOWING_ERROR),

                 errmsg("window functions not allowed in window definition"),

                         parser_errposition(pstate,

                                            locate_windowfunc(expr))));

        }

    }

}

---- Below are details on how ‘checkExprHasWindowFuncs’ performs the check:

rewriteManip.c:

    186 /*

    187  * checkExprHasWindowFuncs -

    188  *  Check if an expression contains a window function call of the

    189  *  current query level.

    190  */

    191 bool

    192 checkExprHasWindowFuncs(Node *node)

    193 {

    194     /*

    195      * Must be prepared to start with a Query or a bare expression tree; if

    196      * it's a Query, we don't want to increment sublevels_up.

    197      */

    198     return query_or_expression_tree_walker(node,

    199                                            contain_windowfuncs_walker,

    200                                            NULL,

    201                                            0);

    202 }

nodeFuncs.c

   2140 /*

   2141  * query_or_expression_tree_walker --- hybrid form

   2142  *

   2143  * This routine will invoke query_tree_walker if called on a Query node,

   2144  * else will invoke the walker directly.  This is a useful way of starting

   2145  * the recursion when the walker's normal change of state is not appropriate

   2146  * for the outermost Query node.

   2147  */

   2148 bool

   2149 query_or_expression_tree_walker(Node *node,

   2150                                 bool (*walker) (),

   2151                                 void *context,

   2152                                 int flags)

   2153 {

   2154     if (node && IsA(node, Query))

   2155         return query_tree_walker((Query *) node,

   2156                                  walker,

   2157                                  context,

   2158                                  flags);

   2159     else

   2160         return walker(node, context);

   2161 }

rewriteManip.c:

    204 static bool

    205 contain_windowfuncs_walker(Node *node, void *context)

    206 {

    207     if (node == NULL)

    208         return false;

    209     if (IsA(node, WindowFunc)) /* ((Node*)(node))->type==T_WindowFunc*/

    210         return true;            /* abort the tree traversal and return true */

    211     /* Mustn't recurse into subselects */

    212     return expression_tree_walker(node, contain_windowfuncs_walker,

    213                                   (void *) context);

    214 }

    451 #define IsA(nodeptr,_type_)     (nodeTag(nodeptr) == T_##_type_)

    405 #define nodeTag(nodeptr)        (((Node*)(nodeptr))->type)

tlist.c:

    234 /*

    235  * get_sortgroupclause_expr

    236  *      Find the targetlist entry matching the given SortGroupClause

    237  *      by ressortgroupref, and return its expression.

    238  */

    239 Node *

    240 get_sortgroupclause_expr(SortGroupClause *sgClause, List *targetList)

    241 {

    242     TargetEntry *tle = get_sortgroupclause_tle(sgClause, targetList);

    243

    244     return (Node *) tle->expr;

    245 }

    227 TargetEntry *

    228 get_sortgroupclause_tle(SortGroupClause *sgClause,

    229                         List *targetList)

    230 {

    231     return get_sortgroupref_tle(sgClause->tleSortGroupRef, targetList);

    232 }

    200 /*

    201  * get_sortgroupref_tle

    202  *      Find the targetlist entry matching the given SortGroupRef index,

    203  *      and return it.

    204  */

    205 TargetEntry *

    206 get_sortgroupref_tle(Index sortref, List *targetList)

    207 {

    208     ListCell   *l;

    209

    210     foreach(l, targetList)

    211     {

    212         TargetEntry *tle = (TargetEntry *) lfirst(l);

    213

    214         if (tle->ressortgroupref == sortref)

    215             return tle;

    216     }

    217

    218     elog(ERROR, "ORDER/GROUP BY expression not found in targetlist");

    219     return NULL;                /* keep compiler quiet */

    220 }

#0  parseCheckWindowFuncs (pstate=0x80f7778, qry=0x80f7890) at parse_agg.c:411

#1  0x00000000004f7929 in transformSelectStmt (pstate=0x80f7778, stmt=0x80f7600) at analyze.c:887

#2  0x00000000004f629f in transformStmt (pstate=0x80f7778, parseTree=0x80f7600) at analyze.c:183

#3  0x00000000004f608b in parse_analyze (parseTree=0x80f7600,

    sourceText=0x80f6488 "SELECT lead(foo) OVER(ORDER BY foo) AS foo FROM (VALUES(0)) bar(foo);",

    paramTypes=0x0, numParams=0) at analyze.c:91

#4  0x000000000069563e in pg_analyze_and_rewrite (parsetree=0x80f7600,

    query_string=0x80f6488 "SELECT lead(foo) OVER(ORDER BY foo) AS foo FROM (VALUES(0)) bar(foo);",

    paramTypes=0x0, numParams=0) at postgres.c:606

Is there any log message?

Yes.

Can we automatically anticipate?

Yes. Well, it falls into input check. And we can learn the frequent pattern!

checkExprHasWindowFuncs’s return value!

It has been called 11 times, all of them would print error msg if returned false!

Sufficiency:

The error message itself offers great amount of hints. If we enhance the log message with tools such as LogEnhancer, we can further capture key information explaining why the execution reached the log message (such as the two Window function names and how they are combined together), thus sufficient to diagnose the failure.