Error -1104(KB-stack overflow) with 7.7.6.9. Works with 7.6.3.15

[Former Member]

Hello,

I encounter an error [-1104] (at 4096): Too complicated SQL statement (KB-stack overflow) with MaxDB 7.7.6.9

The same query works with MaxDB 7.6.3.15. Are there more strict requirements for 7.7 ?

To reproduce, I create a new instance, load tutorial data, then create a table with

create table "MONA"."TEST_SO"(
	"ACCOUNTID" VARCHAR (20) UNICODE,
	"OWNER" VARCHAR (50) UNICODE,
	"OWNERFILTER" VARCHAR (120) UNICODE,
	"VALIDOWNERID" VARCHAR (128) UNICODE,
	"IDAPPROVER1O" VARCHAR (120) UNICODE,
	"IDAPPROVER2O" VARCHAR (120) UNICODE,
	"IDAPPROVER3O" VARCHAR (120) UNICODE,
	"STATE" INTEGER,
	"VALIDATIONSTATE" INTEGER,
	"PRIORITY" INTEGER default           0,
	"SUB_DATE_TIME" TIMESTAMP,
	"PREVIEWMESSAGE" INTEGER,
    "PREVIEWVISIBLE" INTEGER,
	"DELETED" SMALLINT,
	"MSN" INTEGER not null,
constraint SYSPRIMARYKEY primary key ("MSN"))
sample 20000 rows

The query is

SELECT * FROM "MONA"."TEST_SO" where
AccountID = 'MYID' AND ((UPPER(OWNER) = UPPER('OWNER') AND OWNERFILTER LIKE 'A,B,C%' AND AccountID = 'MYID') OR (UPPER(OWNER) = UPPER('OWNER') 
AND OWNERFILTER = 'A,B,C,D' AND AccountID = 'MYID') OR (VALIDOWNERID = 'E,F,G,H' AND AccountID = 'MYID') OR 
(UPPER(OWNER) = UPPER('OWNER') AND OWNERFILTER = 'A,B,C,D' AND AccountID = 'MYID') OR (VALIDOWNERID = 'E,F,G,H' 
AND AccountID = 'MYID') OR (IDAPPROVER1o = 'A,B,C,D' OR IDAPPROVER2o = 'A,B,C,D' OR 
IDAPPROVER3o = 'A,B,C,D')) AND State = 70 AND (ValidationState IS NULL  OR ValidationState = 0) AND Priority != 0 AND Sub_date_time >= '2010-03-04 00:00:00'
 AND AccountID = 'MYID' AND (PreviewMessage IS NULL  OR PreviewMessage = 0 OR PreviewVisible = 1) AND State != 300 AND (Deleted IS NULL  OR Deleted != 1) 
 ORDER BY AccountID ASC, Sub_date_time DESC

I understand that the query is not optimal. It is generated by several applications layers each adding requirements, and thus difficult to change. If simplified (removal by hand of redundant statements), there is no error. But I wonder why it works in 7.6 and not in 7.7. Is there a database parameter that changed between these two versions, that could be reverted to make it work ?

[holger_becker · Accepted solution]

Hi,

the -1104 is probably a result of a new enabled QueryRewrite rule in version 7.7.

With this feature we try to adapt the query to the needs of the optimizer.

In your case the qualification is transformed to a disjunctive normal form.

Unfortunatly this results in a larger internal command representation and in the end to the error message.

But you could adjust the space used for internal command representation by increasing the parameter InternalOrderStackSize.

I would suggest to set it to 131072 this should be enough for most commands.

I hope this helps.

Kind regards

Holger