Problem with subqueries returning TIMESTAMP's

[Former Member]

I've got problems with some subqueries returning TIMESTAMP values.

I've build a case for easy reproduction, with this query:

SELECT iil.iil_lot_num ,

(SELECT max ( lot_data_validade )

FROM lot

WHERE lot.lot_mat_cod = iil.iil_mat_cod AND

lot.lot_num = iil.iil_lot_num ) as validade

FROM iil

WHERE ( iil.iil_inv_serie = 109 ) and

( iil. iil_inv_num = 16 ) and

( iil.iil_mat_cod = 111 )

When I run it, it gives the error:

General error;-9999 POS(1) System error: invalid_indexorder

knldiag also gives:

2009-07-28 15:03:18 9943 ERR 51080 SYSERROR -9999 invalid_indexorder

I'm running on 7.6.06.03.

Previous versions were giving another message:

SQLCODE: -9999 System error: Otherwise unknown errorcode

If I change max ( lot_data_validade ) to max ( year (lot_data_validade) ), it works. The problem is specifically with fetching that timestamp value from the subquery.

Backup for reproduction of the problem is on:

http://www.tecnova.com.br/maxdb/backup_problem

(i've isolated only the tables involved in the problem, so the backup is tiny -> 4.3MB)

[Former Member]

Hi,

correction will be included in 7.6.6.07

Bye,

Elke

[Former Member]

Hi,

good news:

The reason for this astinishing behaviour has been found and will be corrected.

Unfortunately I cannot assure, that it will be fixed in 7.6.06.

I mean, the error -9999 and the funny result of date(min(...)). Both do have the same reason:

A Date/Time/Timestamp-value converted to its external layout will be converted to its external layout

(in the assumption the input is the internal layout) for the second time. And then the input is too long (-9999)

or the result looks like mice having danced on the keyboard.

To overcome the problem, I would recommend to use

CHR( MAX (...), ISO) instead of year(MAX(...) ) || MONTH (MAX (...) ) || ...

which wil cost more internal work because of the MAX done several times.

Thank you for reporting the problem and nice to see that you like MaxDB (despite this effect).

Elke

[Former Member]

Hi,

ok, there are two topics: one to find the reason for that problem and one to help you to get rid of it.

May I ask for the two explains: one with select max (..) and the other with max (year (..) )

I assume that for the first, an index on the corresponding column will be used, in the second (working)

case it will not be used. And further, I assume, that this index, for some reason is corrupt.

Did you check (Check data or check table or something like this, I am not quite sure, which one to take)

the correctness of that index?

Please provide the table definition and the indexes on the table lot.

If you want to get rid of the problem fast(and use the backup for finding the reason), a drop and re-creation

of this index should help.

But perhaps other people will answer with some better ideas how to check the corrupt one before.

Elke

[lbreddemann]

HI Elke,

I had the same idea at first but was largely disappointed to find that this 'corrupt index' is indeed a temp. resultset:

--> max() query
OWNER         TABLENAME           COLUMN_OR_INDEX     STRATEGY                                  PAGECOUNT
DBA           IIL                                     RANGE CONDITION FOR KEY                           61
                                  IIL_INV_SERIE            (USED KEY COLUMN)                              
                                  IIL_INV_NUM              (USED KEY COLUMN)                              
                                  IIL_MAT_COD              (USED KEY COLUMN)                              
INTERNAL      TEMPORARY RESULT                        EQUAL CONDITION FOR KEY                            1
                                  IIL_INV_SERIE            (USED KEY COLUMN)                              
DBA           LOT                                     NO STRATEGY NOW (ONLY AT EXECUTION TIME)            
INTERNAL      TEMPORARY RESULT                        TABLE SCAN                                         1
                                                           RESULT IS COPIED   , COSTVALUE IS       > 2 E10

The cost expectation is really awesome here...

															   
--> max(year()) query
OWNER     TABLENAME         COLUMN_OR_INDEX  STRATEGY                                  PAGECOUNT
DBA       IIL                                RANGE CONDITION FOR KEY                           61
                            IIL_INV_SERIE         (USED KEY COLUMN)                    
                            IIL_INV_NUM           (USED KEY COLUMN)                    
                            IIL_MAT_COD           (USED KEY COLUMN)                    
INTERNAL  TEMPORARY RESULT                   EQUAL CONDITION FOR KEY                            1
                            IIL_INV_SERIE         (USED KEY COLUMN)                    
DBA       LOT                                NO STRATEGY NOW (ONLY AT EXECUTION TIME)  
                                                  RESULT IS COPIED   , COSTVALUE IS    
												

This time the optimizer ran out of words for the expected effort for this query execution...

The problem really seems to be the subquery-select as we can strip down the example to

select (select max(lot_data_validade) maxval
from lot
where lot.lot_mat_cod=111) as maxval
from dual

and still see the error...

regards,

Lars

[lbreddemann]

Hi all!

Sorry - the post looked OK in the PREVIEW.

I'm giving up on this manually fixing the failures of this forum software now.

Hopefully the format was OK in the notification emails so that the subscribers of this thread can read it properly.

@Elke: the whole thing can easily be reproduced just like that:

create table tstest (id integer, mydata timestamp, primary key(id))
//
insert into tstest values ( 1, now())
//
select (select max(mydata) maxval
from tstest) as maxval
from dual

General error;-9999 POS(1) System error: invalid_indexorder

Lars

[Former Member]

Additional fact - this query works:

select (select year (min (mydata)) maxval

from tstest) as maxval

from dual

And execution plan is the same.

In fact, I typed wrong in the first post. The actual workaround we're using in the application is substituting:

max (lot_data_validade)

for:

year (max (lot_data_validade))

(actually, we bring chr (year ()), chr (month ()), chr (day ()), and concatenate them all to get the date in a string form)

I initially posted max (year (lot_data_validade)), that lead to a different execution plan. My observation is that i'ts not related to the execution plan, but to fetching the timestamp values. If we convert them to other datatypes using functions, it suceeds. And in the original version, the result set is described but not fetched (the columns appear on SQL Studio, differently from other kinds of query errors).

Discovered another thing:

select (select date (min (mydata)) maxval

from tstest) as maxval

from dual

gives another behavior: this time, a row is fetched and a blank value appears on SQL Studio... but if I double-click on it, the "Zoom into field" box shows:

[SAP AG][SQLOD32 DLL] Datetime field overflow

Indeed, with this query:

select 'test column', (select date (min (mydata)) maxval

from tstest) as maxval

from dual

it shows a row with the correct value in the first column, but a blank field in the "maxval" column (which, clicked, exibits the same error message).

Thanks for Lars and Elke for the attention! SAPDB/MaxDB is a great product, and I'll be happy if I can somehow contribute in this community.