AW: Insert statement cause kernel to die

Michael MacIntyre wrote:

> 
> Hello.
> There are no constraints - meaning, nothing defined/evident under the
> constraints tab in SQL Studio.  Here is the definition:
> CREATE TABLE "ESOL"."W_INVENTORY"
> (
>     "INVENTORY_ID"               Integer    NOT NULL,
>     "CONTACTS_ID"               Integer    NOT NULL,
>     "PRODUCT_ID"               Integer    NOT NULL,
>     "QTY_IN_STOCK"               Fixed (5,0)    DEFAULT      0,
>     "UNIT_ID"               Integer,
>     "QTY_IN_STOCK_DELIVERED"               Fixed (5,0)    DEFAULT      0,
>     PRIMARY KEY ("INVENTORY_ID"),
>     FOREIGN KEY "FKSHIPPINGUNIT"    ("UNIT_ID") REFERENCES
> "ESOL"."E_UNIT" ("UNIT_ID") ON DELETE  SET NULL,
>     FOREIGN KEY "CONTACTS"    ("CONTACTS_ID") REFERENCES
> "ESOL"."W_CONTACTS" ("CONTACTS_ID") ON DELETE  RESTRICT,
>     FOREIGN KEY "FK_PRODUCT"    ("PRODUCT_ID") REFERENCES
> "ESOL"."W_PRODUCT" ("PRODUCT_ID") ON DELETE  CASCADE
> )
> 
> The odd thing is (perhaps only to me...) , is that each statement will
> execute without error if executed individually.  Meaning, this works:
> 
> insert into
> w_inventory(inventory_id,contacts_id,product_id,unit_id,qty_in_stock,
> qty_in_stock_delivered)
> select seq_inventory.nextval,7597,68,1,1.0,0  from dual
> 
> as does this:
> select seq_inventory.nextval,7597,68,1,1.0,0  from dual where not exists
> ( select 1 from w_inventory i where contacts_id=7597 and product_id=68
> and unit_id=1)
> 
> Prosit Neujahr!
> Thank you.
> Michael MacIntyre
> 

Grrrmpfff, bug was found, one of those I do not like...

Explanation:
Exactly if an insert...select uses a subquery which does not return a value,
either an illegal record is included into the table (if no constraint or the 
like is to be checked for the new record) or the kernel crashes (as in your 
case) because a check for NOT NULL, smallint/integer (having some implicit 
range compared to the corresponding fixed(5)/fixed(10)) or for a user-defined 
constraint is done.

Will be fixed with the next version, but as new versions of 7.4.3 are not open 
source any more, you will have to switch to 7.5.00 / 7.6 or use a workaround.

Sorry for any inconveniences and thank you for reporting
Nevertheless: Happy New Year for you

Elke
SAP Labs Berlin

> 
> 
> Zabach, Elke wrote:
> 
> >Hi,
> >
> >May I ask for some more info?
> >The table definition inclusive the constraints defined for the table
> w_inventory and
> >The view-definition in case w_inventory is a view would be helpful.
> >
> >There is some check to be done for the record to be inserted and that
> check crashes the kernel. Therefore no crash/correct behaviour if no
> insert is to be done. And therefore my hope to receive more info and then
> being able to see the problem.
> >
> >Thank you
> >
> >Elke
> >SAP Labs berlin
> >
> >
> >
> >>-----Ursprüngliche Nachricht-----
> >>Von: Michael MacIntyre [mailto:mmacintyre@xxxxxx]
> >>Gesendet: Donnerstag, 23. Dezember 2004 18:17
> >>An: maxdb@xxxxxxxxxxxxxxx
> >>Betreff: Insert statement cause kernel to die
> >>
> >>Hi.
> >>I am trying to execute an Sql statement similar to this one:
> >>insert into
> >>w_inventory(inventory_id,contacts_id,product_id,unit_id,qty_in_stock,qty
> _i
> >>n_stock_delivered)
> >>select seq_inventory.nextval,7597,68,1,1.0,0  from dual where not exists
> >>( select 1 from w_inventory i where contacts_id=7597 and product_id=68
> >>and unit_id=1)
> >>as a prepared jdbc statement executed as a batch.  The jist of it being
> >>that if the row for doesn't exist, insert it.  If the subselect returns
> >>a row - meaning it does exist, nothing happens (as expected).  If the
> >>row does not exist and returns null (I assume null), the kernel dies.  I
> >>see a dos window flash open,  then a dialog from Microsoft offering
> >>their assistance to send my troubles back to Redmond.
> >>This statement has failed while being ran as a prepared statement in
> >>java and as the above in Sql Studio.
> >>
> >>I am using SAPDB 7.4.32 on Windows XP
> >>This is a snippet from the knldiagerr file:
> >>2004-12-23 11:52:59      0xC74 ERR 19999 BTRACE     kernel!s10mv8 + 109
> >>bytes
> >>2004-12-23 11:52:59      0xC74 ERR 19999 BTRACE         SFrame:
> >>IP:006bddbd RA:005f1c85 FP:04549d74 SP:04549d7c
> >>2004-12-23 11:52:59      0xC74 ERR 19999 BTRACE         Params:
> >>01:007d0000 02:00000014 03:04553cec 04:00000002
> >>2004-12-23 11:52:59      0xC74 ERR 19999 BTRACE         Source:
> >>vsp10c.c ( Line: 450 )
> >>2004-12-23 11:52:59      0xC74 ERR 19999 BTRACE     kernel!kb71op_bool +
> >>869 bytes
> >>2004-12-23 11:52:59      0xC74 ERR 19999 BTRACE         SFrame:
> >>IP:005f1c85 RA:005f3037 FP:04549de4 SP:04549dec
> >>2004-12-23 11:52:59      0xC74 ERR 19999 BTRACE         Params:
> >>01:0456f6ec 02:04549fac 03:7f88a110 04:7f880800
> >>2004-12-23 11:52:59      0xC74 ERR 19999 BTRACE         Source:  vkb71.c
> >>( Line: 5388 )
> >>2004-12-23 11:52:59      0xC74 ERR 19999 BTRACE
> >>kernel!k71qual_handling + 983 bytes
> >>2004-12-23 11:52:59      0xC74 ERR 19999 BTRACE         SFrame:
> >>IP:005f3037 RA:005f3a34 FP:04549e60 SP:04549e68
> >>2004-12-23 11:52:59      0xC74 ERR 19999 BTRACE         Params:
> >>01:0456f6ec 02:01549fac 03:00000000 04:00000001
> >>2004-12-23 11:52:59      0xC74 ERR 19999 BTRACE         Source:  vkb71.c
> >>( Line: 9168 )
> >>2004-12-23 11:52:59      0xC74 ERR 19999 BTRACE
> >>kernel!k71sel_qualification_test + 132 bytes
> >>2004-12-23 11:52:59      0xC74 ERR 19999 BTRACE         SFrame:
> >>IP:005f3a34 RA:005f4315 FP:04549f90 SP:04549f98
> >>2004-12-23 11:52:59      0xC74 ERR 19999 BTRACE         Params:
> >>01:0456ea90 02:04549fac 03:00000001 04:7f88a110
> >>2004-12-23 11:52:59      0xC74 ERR 19999 BTRACE         Source:  vkb71.c
> >>( Line: 2950 )
> >>2004-12-23 11:52:59      0xC74 ERR 19999 BTRACE
> >>kernel!k71qualification_test + 181 bytes
> >>2004-12-23 11:52:59      0xC74 ERR 19999 BTRACE         SFrame:
> >>IP:005f4315 RA:0062a66a FP:0454a884 SP:0454a88c
> >>2004-12-23 11:52:59      0xC74 ERR 19999 BTRACE         Params:
> >>01:0456ea90 02:00000000 03:00000000 04:00000001
> >>2004-12-23 11:52:59      0xC74 ERR 19999 BTRACE         Source:  vkb71.c
> >>( Line: 2916 )
> >>2004-12-23 11:52:59      0xC74 ERR 19999 BTRACE
> >>kernel!kb61check_new_rec + 138 bytes
> >>2004-12-23 11:52:59      0xC74 ERR 19999 BTRACE         SFrame:
> >>IP:0062a66a RA:0062b6ab FP:0454a8b4 SP:0454a8bc
> >>2004-12-23 11:52:59      0xC74 ERR 19999 BTRACE         Params:
> >>01:0456ea90 02:04553ce4 03:00000000 04:045560ac
> >>2004-12-23 11:52:59      0xC74 ERR 19999 BTRACE         Source:  vkb61.c
> >>( Line: 2374 )
> >>2004-12-23 11:52:59      0xC74 ERR 19999 BTRACE     kernel!k61ins_select
> >>+ 267 bytes
> >>2004-12-23 11:52:59      0xC74 ERR 19999 BTRACE         SFrame:
> >>IP:0062b6ab RA:006106b5 FP:0454acdc SP:0454ace4
> >>2004-12-23 11:52:59      0xC74 ERR 19999 BTRACE         Params:
> >>01:0056ea90 02:04553ce4 03:00000000 04:00000000
> >>2004-12-23 11:52:59      0xC74 ERR 19999 BTRACE         Source:  vkb61.c
> >>( Line: 2164 )
> >>2004-12-23 11:52:59      0xC74 ERR 19999 BTRACE
> >>kernel!kb721result_handle + 725 bytes
> >>2004-12-23 11:52:59      0xC74 ERR 19999 BTRACE         SFrame:
---snipped---

> >>
> >>Thanks for any help you can offer.
> >>Michael MacIntyre
> >>
> >>--
> >>MaxDB Discussion Mailing List
> >>For list archives: http://lists.mysql.com/maxdb
> >>To unsubscribe:
> http://lists.mysql.com/maxdb?unsub=elke.zabach@xxxxxxx
> >>
> >>
> >
> >
> >
> >
> >

--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe:    http://lists.mysql.com/maxdb?unsub=gcdm-maxdb@xxxxxxxxxxx