Bug #14577 Error with the length of the query
Submitted: 2 Nov 2005 15:33 Modified: 10 Nov 2007 18:49
Reporter: [ name withheld ] Email Updates:
Status: No Feedback Impact on me:
None 
Category:MaxDB Severity:S2 (Serious)
Version:7.6.00.10 OS:Linux (Linux Slackware 10)
Assigned to: CPU Architecture:Any

[2 Nov 2005 15:33] [ name withheld ]
Description:
Hello.
 I received maxdb error "-2000 POS(8914) Row too long [I2000]" with the query
"SELECT PRODUCTS.PRODUCTID AS ID, PRODUCTS.TITLE AS T, DESCRIPTION, INSERTDATE, PRODUCTTYPES.TITLE AS TT FROM PRODUCTS, PRTRANSLATIONS, PRODUCTDESCR, PRODUCTTYPES WHERE PRTRANSLATIONS.LANGID=2 AND PRODUCTS.PRODUCTID=PRTRANSLATIONS.PRODUCTID AND PRODUCTS.PRODUCTID=PRODUCTDESCR.PRODUCTID AND PRODUCTS.PRODUCTTYPEID=PRODUCTTYPES.PRODUCTTYPEID AND (UPPER(PRODUCTS.TITLE) LIKE '%TRY%' OR UPPER(DESCRIPTION) LIKE '%TRY%') AND (UPPER(PRODUCTS.TITLE) LIKE '%TO%' OR UPPER(DESCRIPTION) LIKE '%TO%') AND (UPPER(PRODUCTS.TITLE) LIKE '%SEARCH%' OR UPPER(DESCRIPTION) LIKE '%SEARCH%') AND (UPPER(PRODUCTS.TITLE) LIKE '%SOME%' OR UPPER(DESCRIPTION) LIKE '%SOME%') AND (UPPER(PRODUCTS.TITLE) LIKE '%LONG%' OR UPPER(DESCRIPTION) LIKE '%LONG%') AND (UPPER(PRODUCTS.TITLE) LIKE '%TEXT%' OR UPPER(DESCRIPTION) LIKE '%TEXT%') AND (UPPER(PRODUCTS.TITLE) LIKE '%WITH%' OR UPPER(DESCRIPTION) LIKE '%WITH%') AND (UPPER(PRODUCTS.TITLE) LIKE '%MAXDB%' OR UPPER(DESCRIPTION) LIKE '%MAXDB%')"
 I believe thet it is long query, but not longer that 8088 bytes. What's a problem with it.

How to repeat:
Just try to execute same query.
[2 Nov 2005 16:24] Ulf Wendel
Hi Vano,

please provide the exact version number (7.6.xx) and the necessary catalog (database tables) so that we can reproduce the problem.

Regards,
Ulf
[3 Nov 2005 7:15] [ name withheld ]
Hi.
Thank you for answer. This is critical query for our work.
MaxDB version is 7.6.00.10.
There is our tables:
Create Table "PRODUCTS" (
	"PRODUCTID" Serial NOT NULL ,
	"PRODUCTTYPEID" Integer NOT NULL ,
	"AUTHORID" Integer NOT NULL ,
	"TITLE" Char(100),
	"PRICE" Float NOT NULL ,
	"INSERTDATE" Date NOT NULL ,
	"PREVIEWCOUNT" Integer NOT NULL  Default 0,
	"FILEPATH" Char(100) NOT NULL ,
	"PREVIEWPATH" Char(100),
	"APPROVED" Boolean NOT NULL  Default FALSE,
Primary Key ("PRODUCTID")
)
//

Create Table "PRTRANSLATIONS" (
	"PRTRANSLATIONID" Serial NOT NULL ,
	"PRODUCTID" Integer NOT NULL ,
	"LANGID" Integer NOT NULL ,
Primary Key ("PRTRANSLATIONID")
)
//

Create Table "PRODUCTTYPES" (
	"PRODUCTTYPEID" Serial NOT NULL ,
	"TITLE" Char(50) NOT NULL ,
	"DEFAULTPRICE" Float NOT NULL  Default 0,
Primary Key ("PRODUCTTYPEID")
)
//

Create Table "PRODUCTDESCR" (
	"PRODUCTDESCRID" Serial NOT NULL ,
	"PRODUCTID" Integer NOT NULL ,
	"DESCRIPTION" Char(1000),
Primary Key ("PRODUCTDESCRID")
)
//

Alter Table "PRODUCTGROUPS" add Foreign Key  ("PRODUCTID") references "PRODUCTS" ("PRODUCTID")  on delete cascade
//
Alter Table "PRTRANSLATIONS" add Foreign Key  ("PRODUCTID") references "PRODUCTS" ("PRODUCTID")  on delete cascade
//
Alter Table "PRODUCTORDERS" add Foreign Key  ("PRODUCTID") references "PRODUCTS" ("PRODUCTID")  on delete cascade
//
Alter Table "PRODUCTDESCR" add Foreign Key  ("PRODUCTID") references "PRODUCTS" ("PRODUCTID")  on delete cascade
//
Alter Table "PRODUCTS" add Foreign Key  ("PRODUCTTYPEID") references "PRODUCTTYPES" ("PRODUCTTYPEID")  on delete cascade
//
[7 Nov 2005 8:24] [ name withheld ]
Can anybody tell me what is wrong with this query????
[11 Nov 2005 22:45] C.J. Adams-Collier
Heya Vano,

I'm repro'ing the bug.  I've run th CREATE TABLE sql you gave me and got these errors:

* -4004: POS(14) Unknown table name:PRODUCTGROUPS SQLSTATE: 42000
* -4004: POS(14) Unknown table name:PRODUCTORDERS SQLSTATE: 42000

Please either update the schema to include definitions of these tables or let me know that these foreign keys are not important.

Also, I'll need some test data in order to reproduce and regress this error.

You can grab the tools I'm using to reproduce the error here:

http://colliertech.org/svn/mysql/maxdb/createDB/

Check out the code using svn (or wget) and run the following command:

./createDB.pl -d bug14577 -s bug14577.sql

This will create the database instance and create the tables in bug14577.sql (the schema you provided)

Thanks!

C.J.
[14 Nov 2005 8:16] [ name withheld ]
Hello, CJ.

Thank you for answer. I believe that you will understand the source of problem.

Foreign key for productgroups is not important, as it doesn't use in this query. Also foreign key for productorders not important.
[14 Nov 2005 8:18] [ name withheld ]
For test data look to the added file.
[14 Nov 2005 18:46] C.J. Adams-Collier
Vano,

I executed the query you suggested and received the same result.  After a bit of digging on Google, it looks like the error may be related to this:

http://www.sourcekeg.co.uk/www.mysql.com/doc/maxdb/en/39/d291b404f411d2a96c00a0c9449261/co...

I will do a bit more looking and see if I can find a work-around that will suffice.

Cheers,

C.J.
[18 Sep 2007 12:27] Burkhard Diesing
Hi all,
I have tried to reproduce this bug but the ddl command you send are incomplete and the links from C.J doesn't works any more. If your are still interested on a solution please post it here again or send it to the mailing list. I will open a new message and try to find a solution.

Regards,
Burkhard

---
Burkhard Diesing
Development Manager
MaxDB&liveCache
SAP AG
mailto:burkhard.diesing@sap.com
www.sap.com
Sitz der Gesellschaft/Registered Office: Walldorf, Germany
Vorstand/SAP Executive Board: Henning Kagermann (Sprecher/CEO), Shai
Agassi, Léo Apotheker, Werner Brandt, Claus Heinrich, Gerhard Oswald,
Peter Zencke
Vorsitzender des Aufsichtsrats/Chairperson of the SAP Supervisory
Board: Hasso Plattner 
Registergericht/Commercial Register Mannheim No HRB 350269
[10 Oct 2007 18:49] MySQL Verification Team
Changing to feedback for last comment:

[18 Sep 14:27] Burkhard Diesing
[11 Nov 2007 0:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".