dynamobi/dynamobi — Issue #127: [LDB-195] Uncorrelated subquery in UPDATE statement results in AssertionError
Author: dynamobi-build
Created: 2011-12-30 02:15:17+00:00
State: open
Labels: LDB, Optimizer

[reporter="rhyskeepence", created="Fri, 28 Aug 2009 04:09:03 -0500 (GMT-05:00)"]
create schema reporting;

create table reporting.customer(

ID int not null primary key,

NAME varchar(255),

DELETED_ON date);

create schema customer_updates;

create table customer_updates.customer(

ID int not null primary key,

NAME varchar(255));

insert into reporting.customer values (1, 'Fred', null);

update reporting.customer reporting_customer

set deleted_on = current_date

where not exists (

select 1 from customer_updates.customer customer_updates

where customer_updates.id = reporting_customer.id);

Error reported:

28-Aug-2009 10:00:52 net.sf.farrago.jdbc.FarragoJdbcUtil newSqlException

SEVERE: Internal error: while converting LCS_RID(`SYS$SRC`.`SYS$ANON1`) = LCS_RID(`REPORTING_CUSTOMER`.`ID`) AND (NOT EXISTS (SELECT 1

FROM `LOCALDB`.`CUSTOMER_UPDATES`.`CUSTOMER` AS `CUSTOMER_UPDATES`

WHERE `CUSTOMER_UPDATES`.`ID` = `REPORTING_CUSTOMER`.`ID`))

28-Aug-2009 10:00:52 net.sf.farrago.jdbc.FarragoJdbcUtil newSqlException

SEVERE: null

28-Aug-2009 10:00:52 net.sf.farrago.jdbc.FarragoJdbcUtil newSqlException

SEVERE: Internal error: while converting NOT EXISTS SELECT 1

FROM `LOCALDB`.`CUSTOMER_UPDATES`.`CUSTOMER` AS `CUSTOMER_UPDATES`

WHERE `CUSTOMER_UPDATES`.`ID` = `REPORTING_CUSTOMER`.`ID`

Trace.log:

28-Aug-2009 10:00:52 net.sf.farrago.jdbc.FarragoJdbcUtil newSqlException

SEVERE: Internal error: while converting LCS_RID(`SYS$SRC`.`SYS$ANON1`) = LCS_RID(`REPORTING_CUSTOMER`.`ID`) AND (NOT EXISTS (SELECT 1

FROM `LOCALDB`.`CUSTOMER_UPDATES`.`CUSTOMER` AS `CUSTOMER_UPDATES`

WHERE `CUSTOMER_UPDATES`.`ID` = `REPORTING_CUSTOMER`.`ID`))

28-Aug-2009 10:00:52 net.sf.farrago.jdbc.FarragoJdbcUtil newSqlException

SEVERE: null

28-Aug-2009 10:00:52 net.sf.farrago.jdbc.FarragoJdbcUtil newSqlException

SEVERE: Internal error: while converting NOT EXISTS SELECT 1

FROM `LOCALDB`.`CUSTOMER_UPDATES`.`CUSTOMER` AS `CUSTOMER_UPDATES`

WHERE `CUSTOMER_UPDATES`.`ID` = `REPORTING_CUSTOMER`.`ID`