Error at Select with Limit and Offset (7.6.03.15)

[thomasschulz2]

Hi List,

when executing a query like "select * from testtable limit 1,1", I always get the error "[-9045]: System error: BD Root check".

I can easily reproduce it with creating a table with one field, insert three rows and then select with limit 1,1. Without offset everything works fine.

In the PTS system I found no entry for this behavior.

MaxDB version: 7.6.03.15 on 64bit linux.

A test with the older version 7.6.00.37 using the old syntax (without comma) leads to the same error.

Regards,

Thomas

[Former Member · Accepted solution]

Hi,

now I can tell you, that starting with version 7.6.06.01 (please do not ask, when you will be able to download it) the error -9045 can be avoided even without FOR REUSE.

Sorry for any inconveniences.

I hope you will not struggle over further problems in this context.

With our tests after the first version of change we had to find out, that -9206 arose in sqlcli when using FOR REUSE. Hopefully your application will not handle the interface as sqlcli does.

Elke

[Former Member]

Hi,

oops, it IS the newest version available. Mhm, bad luck.

On the other hand, even a newer one will not help in all cases, because with sqlcli the -9045-problem can be reproduced even in the current development-version.

Now I am stuck.

Rowno's behaviour differs if combined with ORDER BY.

But adding the option FOR REUSE seems to help.

Is this a possible solution in your case?

Elke

[thomasschulz2]

Hi Elke,

OK - that works. Thanks for the solution.

Do you know, from which version the limit feature will work as documented (without "for reuse")? Maybe this is a little bit to early for this question.

Regards,

Thomas

[Former Member]

Hi,

with 7.6.03.16 a bug was fixed, describing exactly your -108-problem when having an index and using

order by for this indexed column.

For the -9045-problem there was no exact match in all the bugfixes.

Could you use a newer MaxDB-version and tell us, if not only the -108, but the -9045 as well has vanished?

With my very new version none of your examples cause any problem.

Elke

[thomasschulz2]

Hi Elke,

cause I'm no SAP customer the question is, how I can get a newer version than the actual community version 7.6.03.15?

Regards,

Thomas

[lbreddemann]

Hi Thomas,

I tried to reproduce this with MaxDB 7.6.04 Build 11 - but didn't succeed.

Anyhow the LIMIT clause does not seem to work via the JDBC connection of the DB Studio correctly.

With SQL Studio I was able to use the LIMIT clause correctly without any problems.

Perhabs it's still an issue with your table.

Please post the DDL for the table and Indexes (if any) and the data you put into.

KR Lars

[thomasschulz2]

Hi Lars,

I tried the same queries with sqlcli fom command line and get the same results. Therefore this seems not to be a JDBC problem?

OK - here is my table definition and the queries for putting data into it:

create table testtab (f1 int)

insert into testtab values (1)

insert into testtab values (2)

insert into testtab values (3)

If I try to select data with:

select * from testtab limit 1

everythings works fine (result: 1).

This select:

select * from testtab limit 1,1

gives this error "-9045: System error: BD Root check".

If I try this one:

select * from testtab order by f1 limit 1,1

I get the correct result! (result: 2)

If creating additionally an index:

create unique index i1 on testtab (f1 asc)

I get the error "[-108]: Table or index dropped or content deleted" when selecting with order by, limit and offset. Deleting the index lets work the "order by" version again.

All tests are run with 7.6.03.15.

Regards,

Thomas

[lbreddemann]

Hi Thomas,

I was now able to reproduce this behaviour.

Still it always works with the SQL Studio.

I will give the developers a hint on that.

BTW: I was not able to reproduce the create index problem...

KR Lars

Edited by: Lars Breddemann on Jun 4, 2008 1:19 PM