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.