arch-dev.sgml

关系型数据库 Postgresql 6.5.2

single stage in detail.

\subsubsection{The Parser Stage}

The grammar rules of the {\it parser} defined in {\tt gram.y} did not
require any changes (i.e. the rules had already been prepared for the
{\it having clause}). The {\it operator tree} built up for the {\it having
clause} is attached to the field {\tt havingClause} of the {\tt
SelectStmt} node handed back by the {\it parser}. \\
\\
The {\it transformation} procedures applied to the tree handed back by
the {\it parser} transform the {\it operator tree} attached to the field
{\tt havingClause} using exactly the same functions used for the
transformation of the {\it operator tree} for the {\it where clause}. This
is possible because both trees are built up by the same grammar rules
of the {\it parser} and are therefore compatible. Additional checks
which make sure that the {\it having clause} involves at least one
{\it aggregate function} etc. are performed at a later point in time
in the {\it planner/optimizer} stage. \\
\\
The necessary changes have been applied to the following functions
included in the file {\tt
$\ldots$/src/backend/parser/analyze.c}. Note, that only the relevant
parts of the affected code are presented instead of the whole
functions. Every added source line will be marked by a {\tt '+'} at the
beginning of the line and every changed source line will be marked by
a {\tt '!'} throughout the following code listings. Whenever a part of
the code which is not relevant at the moment is skipped, three
vertical dots are inserted instead.
%
\pagebreak
%
\begin{itemize}
<step> {\tt transformInsertStmt()} \\
This function becomes is invoked every time a SQL {\tt insert}
statement involving a {\tt select} is used like the following example
illustrates:
%
\begin{verbatim}
  insert into t2
  select x, y
  from t1;
\end{verbatim}
%
Two statements have been added to this function. The first one
performs the transformation of the {\it operator tree} attached to the
field {\tt havingClause} using the function {\tt
transformWhereClause()} as done for the {\it where clause}. It is
possible to use the same function for both clauses, because they are
both built up by the same {\it grammar rules} given in {\tt gram.y}
and are therefore compatible.

The second statement makes sure, that {\it aggregate functions} are
involved in the query whenever a {\it having clause} is used,
otherwise the query could have been formulated using only a {\it where
clause}.
%
\begin{verbatim}
  static Query *
  transformInsertStmt(ParseState *pstate, 
                      InsertStmt *stmt)
  {
    /* make a new query tree */
    Query *qry = makeNode(Query);
                            .
                            .
                            .
    /* fix where clause */
    qry->qual = transformWhereClause(pstate, 
                                     stmt->whereClause);

+   /* The havingQual has a similar meaning as "qual" in 
+    * the where statement. So we can easily use the  
+    * code from the "where clause" with some additional
+    * traversals done in .../optimizer/plan/planner.c 
+    */
+   qry->havingQual = transformWhereClause(pstate, 
+                                   stmt->havingClause);     
                            .
                            .
                            .
+   /* If there is a havingQual but there are no 
+    * aggregates, then there is something wrong with 
+    * the query because having must contain aggregates 
+    * in its expressions! Otherwise the query could 
+    * have been formulated using the where clause.
+    */
+   if((qry->hasAggs == false) && 
+      (qry->havingQual != NULL))
+   {
+     elog(ERROR,"This is not a valid having query!");
+     return (Query *)NIL;
+   }
    return (Query *) qry;
  }
\end{verbatim}
%
<step> {\tt transformSelectStmt()} \\
Exactly the same statements added to the function {\tt
transformInsertStmt()} above have been added here as well.
%
\begin{verbatim}
  static Query *
  transformSelectStmt(ParseState *pstate, 
                      SelectStmt *stmt)
  {
    Query  *qry = makeNode(Query);

    qry->commandType = CMD_SELECT;
                            .
                            .
                            .
    qry->qual = transformWhereClause(pstate, 
                                     stmt->whereClause);

+   /* The havingQual has a similar meaning as "qual" in 
+    * the where statement. So we can easily use the  
+    * code from the "where clause" with some additional
+    * traversals done in .../optimizer/plan/planner.c 
+    */
+   qry->havingQual = transformWhereClause(pstate, 
+                                   stmt->havingClause);     
                            .
                            .
                            .
+   /* If there is a havingQual but there are no 
+    * aggregates, then there is something wrong with 
+    * the query because having must contain aggregates 
+    * in its expressions! Otherwise the query could 
+    * have been formulated using the where clause.
+    */
+   if((qry->hasAggs == false) && 
+      (qry->havingQual != NULL))
+   {
+     elog(ERROR,"This is not a valid having query!");
+     return (Query *)NIL;
+   }
    return (Query *) qry;
  }
\end{verbatim}
%
\end{itemize}


\subsubsection{The Rewrite System}

This section describes the changes to the {\it rewrite system} of
<productname>Postgres</productname> that have been necessary to support the use of {\it views}
within queries using a {\it having clause} and to support the
definition of {\it views} by queries using a {\it having clause}.

As described in section \ref{view_impl} {\it Techniques To Implement
Views} a query rewrite technique is used to implement {\it
views}. There are two cases to be handled within the {\it rewrite
system} as far as the {\it having clause} is concerned:
%
\pagebreak
%
\begin{itemize}
<step> The {\it view definition} does not contain a {\it having clause}
but the queries evaluated against this view may contain {\it having
clauses}.
<step> The {\it view definition} contains a {\it having clause}. In this
case queries evaluated against this view must meet some
restrictions as we will describe later.
\end{itemize}
%
\paragraph{No having clause in the view definition:} First we will
look at  the changes necessary to support queries using a
{\it having clause} against a {\it view} defined without
a {\it having clause}. \\
\\
Let the following view definition be given:
%
\begin{verbatim}
  create view test_view
  as select sno, pno
     from sells
     where sno > 2;
\end{verbatim}
%
and the following query made against <literal>test_view</literal>:
%
\begin{verbatim}
  select * 
  from testview
  where sno <> 5;
\end{verbatim}
%
The query will be rewritten to:
%
\begin{verbatim}
  select sno, pno
  from sells
  where sno > 2 and
        sno <> 5;
\end{verbatim}
%
The query given in the definition of the {\it view} <literal>test_view</literal>
is the {\it backbone} of the rewritten query. The {\it targetlist} is taken
from the user's query and also the {\it where qualification } of the
user's query is added to the {\it where qualification} of the new
query by using an {\tt AND} operation. \\
\\
Now consider the following query:
%
\begin{verbatim}
  select sno, count(pno)
  from testview
  where sno <> 5
  group by sno
  having count(pno) > 1;  
\end{verbatim}
%
From now on it is no longer sufficient to add just the {\it where
clause} and the {\it targetlist} of the user's query to the new query. The
{\it group clause} and the {\it having qualification} also have to be
added to the rewritten query:
%
\begin{verbatim}
  select sno, count(pno)
  from sells
  where sno > 2 and
        sno <> 5
  group by sno
  having count(pno) > 1;
\end{verbatim}
%
\pagebreak

\noindent Several changes that have already been applied to the {\it
targetlist} and the {\it where clause} also have to be applied to the
{\it having clause}. Here is a collection of all {\it additional} steps that
have to be performed in order to rewrite a query using a {\it having
clause} against a simple {\it view} (i.e. a {\it view} whose
definition does not use any {\it group} and {\it having clauses}):
%
\begin{itemize}
%
<step> Rewrite the subselects contained in the {\it having clause} if any are
present.
%
<step> Adapt the {\tt varno} and {\tt varattno} fields of all {\tt
VAR} nodes contained in the {\it operator tree} representing the {\it
having clause} in the same way as it has been done for the tree
representing the {\it where clause}. The {\tt varno} fields are changed
to use the {\it base tables} given in the {\it view definition} (which
have been inserted into the {\it range table entry list} in the
meantime) instead of the {\it virtual tables}. The positions of
the attributes used in the {\it view} may differ from the positions of
the corresponding attributes in the {\it base tables}. That's why the
{\tt varattno} fields also have to be adapted.
%
<step> Adapt the {\tt varno} and {\tt varattno} fields of all {\tt
VAR} nodes contained in the {\tt groupClause} of the user's query in
the way and for the reasons described above.
%
<step> Attach the tree representing the {\it having qualification} (which is
currently attached to the field {\tt havingClause} of the {\tt Query}
node for the user's query) to the field {\tt havingClause} of the {\tt
Query} node for the new (rewritten) query.
%
<step> Attach the list representing the {\it group clause} (currently
attached to the field {\tt groupClause} of the {\tt Query} node for
the user's query) to the field {\it group clause} of the node for the
new (rewritten) query.
%
\end{itemize} 

\paragraph{The view definition contains a having clause:}  Now we will
look at the problems that can arise using {\it views} that are
defined using a query involving a {\it having clause}. \\
\\
Let the following {\it view definition} be given:
%
\begin{verbatim}
  create view test_view
  as select sno, count(pno) as number
     from sells
     where sno > 2
     group by sno
     having count(pno) > 1;
\end{verbatim}
%
Simple queries against this {\it view} will not cause any troubles:
%
\begin{verbatim}
  select * 
  from test_view
  where sno <> 5;
\end{verbatim}
%
This query can easily be rewritten by adding the {\it where
qualification} of the user's query ({\tt sno $<>$ 5}) to the {\it
where qualification} of the {\it view definition's } query. \\
\\
The next query is also simple but it will cause troubles when
it is evaluated against the above given {\it view definition}:
%
\begin{verbatim}
  select * 
  from test_view
  where number > 1; /* count(pno) in the view def.
                     * is called  number here */
\end{verbatim}
%
\pagebreak
The currently implemented techniques for query rewriting will rewrite
the query to:
%
\begin{verbatim}
  select *
  from sells
  where sno > 2 and
        count(pno) > 1
  group by sno         
  having count(pno) > 1;
\end{verbatim}
%
which is an invalid query because an {\it aggregate function} appears
in the {\it where clause}. \\
\\
Also the next query will cause troubles:
%
\begin{verbatim}
  select pno, count(sno)
  from test_view
  group by pno;
\end{verbatim}
%
As you can see this query does neither involve a {\it where clause}
nor a {\it having clause} but it contains a {\it group clause} which
groups by the attribute {\tt pno}. The query in the definition of the
{\it view} also contains a {\it group clause} that groups by the
attribute {\tt sno}. The two {\it group clauses} are in conflict with
each other and therefore the query cannot be rewritten to a form that
would make sense.\\
\\
{\bf Note:} There is no solution to the above mentioned problems at the
moment and it does not make sense to put much effort into that because
the implementation of the support for queries like:
%
\begin{verbatim}
  select pno_count, count(sno) 
  from ( select sno, count(pno) as pno_count