Clone
James Capps <james.capps@hp.com>
committed
on 27 May 15
Fix bug 1323826 - SELECT with long IN predicate causes core file
Actually, this check-in does not completely fix the problem, but
it does al… Show more
Fix bug 1323826 - SELECT with long IN predicate causes core file

Actually, this check-in does not completely fix the problem, but

it does allow IN predicates (and NOT IN predicates) to have a list

with as many as 3100 items in the list.

NOTE: There are many places in the SQL Compiler code that use recursion.

The changes in this check-in address the issue for long IN lists

and, to some extent, for INSERT statements that attempt to insert

many rows with a single INSERT statement.  However, it is still possible

for someone to try a list that is too long.  As you make the lists

longer, you find more recursive routines that have the same type of

problem(s) that are being fixed for certain routines by this check-in.

This check-in also fixes a couple of minor problems in the logic used to

debug Native Expressions code.  These problems were in

.../sql/generator/Generator.cpp  and

.../sql/exp/ExpPCodeOptsNativeExpr.cpp

There were 3 different techniques used to reduce the stack space usage of

various recursive routines that get invoked as a result of long IN lists

or NOT IN lists:

1) Move variables from the stack to heap.

2) Recode the recursive routine to pull out sections of code (not needed

  during the recursion) and put those in their own routine.  This cuts

  the stack space usage because it enables the C++ compiler to generate

  code for the recursive routine that needs significantly less stack

  space.

3) Declare variables of type ARRAY on the stack (where the ARRAY

  overhead is allocated from stack, but the contents come from heap)

  to hold certain pieces of data where each recursive level of calling

  needs its own value for the variable AND then change the code to use a

  'while' loop to process the nodes in the node tree in the same order

  that the original recursive routine would have processed the nodes.

Files changed for reducing stack space usage:

sql/optimizer/ItemCache.cpp    - use method 2 on ItemExpr::generateCacheKey()

sql/optimizer/NormItemExpr.cpp - use method 2 on ItemExpr::normalizeNode()

              and method 1 on BiLogic::predicateEliminatesNullAugmentedRows()

sql/generator/GenPreCode.cpp - use method 2 on

                              ItemExpr::replaceVEGExpressions()

sql/optimizer/ItemExpr.cpp   - use method 2 on ItemExpr::unparsed()

                              AND ItemExpr::ItemExpr::synthTypeAndValueId()

sql/optimizer/OptRange.cpp   - use method 3 on OptRangeSpec::buildRange()

sql/optimizer/BindItemExpr.cpp - use method 3 on

                                ItemExpr::convertToValueIdSet()

sql/optimizer/NormRelExpr.cpp  - use method 3 on

                                Scan::applyAssociativityAndCommutativity()

sql/optimizer/ItemExpr.h - declare new methods that were created

sql/optimizer/ItemLog.h  - declare new methods that were created

Finally, this check-in changes the default value for a CQD named

PCODE_MAX_OPT_BRANCH_CNT from 19000 to 12000.  This was to fix a problem

where we used too much *heap* space when we tried to optimize a PCODE

Expression that had too many separate blocks of PCODE instructions (such

as results from a very long NOT IN list.)  With this change, we will

choose to run with unoptimized PCODE if trying to optimize the PCODE

would result in overflowing the heap space available.

Change-Id: Ie8ddbab07de2a40095a80adac7873db8c5cb74ac

Show less

default + 8 more