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.
various lp and other fixes, details below. -- added support for self referencing constraints -- limit clause can now be specified as a param (select * from t limit ?) -- lp 1448261. alter table add identity col is not allowed and now returns an error -- error is returned if a specified constraint in an alter/create statement exists on any table -- lp 1447343. cannot have more than one identity columns. -- embedded compiler is now used to get priv info during invoke/showddl. -- auth info is is not reread if already initialized -- sequence value function is now cacheable -- lp 1448257. inserts in volatile table with identity column now work -- lp 1447346. inserts with identity col default now work if inserted in a salted table. -- only one compiler is now needed to process ddl operations with or without authorization enabled -- query cache in embedded compiler is now cleared if user id changes -- pre-created default schema 'SEABASE' can no longer be dropped -- default schema 'SCH' is automatically created if running regressions and it doesn't exist. -- improvements in regressions run. -- regressions run no longer call a script from another sqlci session to init auth, create default schema and insert into defaults table before every regr script -- switched the order of regression runs -- updates from review comments.