Issue filed http://code.google.com/p/h2database/issues/detail?id=67
On Feb 24, 12:22 pm, Thomas Mueller <[email protected]> wrote: > Hi, > > > Just a ping to see if this is queued for a fix anytime. I just > > noticed an official list of "issues" associated with this forum. Does > > it need to be there to be tracked? > > Sorry I couldn't find the problem yet. I still hope I can solve it > within a few days, but I'm not sure. > > You don't need to create an issue, but you can if you want - it would > help others that run into the same problem. > > Regards, > Thomas > > > > > On Feb 11, 11:33 am, Weston Clark <[email protected]> wrote: > >> We'll wait for the eventual fix. > > >> On Wed, Feb 11, 2009 at 10:53 AM, Thomas Mueller < > > >> [email protected]> wrote: > > >> > Hi, > > >> > > Any progress to report on this? > > >> > Yes and no. I found a workaround: disable the optimization for > >> > IN(SELECT...) queries. To do that, set the system property > >> > h2.optimizeInJoin to false (or using version 1.0.79). However I > >> > couldn't solve the problem yet. > > >> > Regards, > >> > Thomas > > >> > > On Feb 5, 1:46 pm, Wes Clark <[email protected]> wrote: > >> > >> Great. We'd like to move to the new version. > > >> > >> On Feb 5, 1:42 pm, Thomas Mueller <[email protected]> > >> > >> wrote: > > >> > >> > Hi, > > >> > >> > I will have a look what the problem is. The query seems to be > >> > >> > correct. > >> > >> > Here is a simpler test case: > > >> > >> > select 1 from dual a where 1 in(select 1 from dual b where 1 > >> > >> > in(select > >> > >> > 1 from dual c where a.x=1)); > > >> > >> > Regards, > >> > >> > Thomas > > >> > >> > On Mon, Feb 2, 2009 at 7:23 PM, Wes Clark <[email protected]> > >> > wrote: > > >> > >> > > Here are the create table and create index statements that will > >> > >> > > reproduce the problem: > >> > >> > > create table cc_check (Status integer NOT NULL, CheckInstructions > >> > >> > > integer NULL, UpdateTime datetime NOT NULL, MailToAddress > >> > varchar(255) > >> > >> > > NULL, PendEscalationForBulk bit NULL, InvoiceNumber varchar(255) > >> > NULL, > >> > >> > > CreateTime datetime NOT NULL, DeliveryMethod integer NULL, > >> > >> > > CheckBatching integer NULL, BeanVersion integer NULL, CheckSetID > >> > >> > > integer NOT NULL, GroupID integer NULL, Retired integer NOT NULL, > >> > >> > > LoadCommandID integer NULL, extractready integer NULL, IssueDate > >> > >> > > datetime NULL, CheckNumber varchar(255) NULL, CreateUserID integer > >> > >> > > NULL, ScheduledSendDate datetime NULL, EnteredTime datetime NULL, > >> > >> > > DateOfService datetime NULL, PayToDenorm varchar(255) NULL, > >> > >> > > ClaimContactID integer NULL, PublicID varchar(20) NOT NULL, > >> > >> > > ServicePdEnd datetime NULL, ClaimID integer NOT NULL, BankAccount > >> > >> > > integer NULL, PaymentMethod integer NULL, Memo varchar(255) NULL, > >> > >> > > PortionID integer NULL, DeductionType integer NULL, > >> > >> > > BulkInvoiceItemInfoID integer NULL, CheckType integer NOT NULL, > >> > PayTo > >> > >> > > varchar(255) NULL, MailTo varchar(255) NULL, Comments varchar(255) > >> > >> > > NULL, ReportableAmount decimal(18,2) NULL, RecurringCheckID > >> > >> > > integer > >> > >> > > NULL, UpdateUserID integer NULL, Reportability integer NULL, > >> > >> > > ServicePdStart datetime NULL, ID integer NOT NULL); > >> > >> > > ALTER TABLE cc_check ADD PRIMARY KEY (ID); > >> > >> > > CREATE INDEX cc000000h9N0 ON cc_check (ScheduledSendDate); > >> > >> > > CREATE INDEX cc000000h9N1 ON cc_check (extractready); > >> > >> > > CREATE UNIQUE INDEX cc000000h9U2 ON cc_check (Status, Retired, ID, > >> > >> > > ScheduledSendDate); > >> > >> > > CREATE UNIQUE INDEX cc000000h9U3 ON cc_check (Status, Retired, > >> > >> > > EnteredTime, ID); > >> > >> > > CREATE UNIQUE INDEX cc000000h9U4 ON cc_check (CheckNumber, > >> > >> > > Retired, > >> > >> > > ClaimID, ID); > >> > >> > > CREATE UNIQUE INDEX cc000000h9U5 ON cc_check (InvoiceNumber, > >> > Retired, > >> > >> > > ClaimID, ID); > >> > >> > > CREATE UNIQUE INDEX cc000000h9U6 ON cc_check (CreateUserID, > >> > >> > > EnteredTime, Retired, ClaimID, PayToDenorm, Status, ID); > >> > >> > > CREATE UNIQUE INDEX cc000000h9U7 ON cc_check (GroupID, Retired, > >> > >> > > ID); > >> > >> > > CREATE UNIQUE INDEX cc000000h9U8 ON cc_check (ID, Retired, > >> > >> > > EnteredTime, ClaimID); > >> > >> > > CREATE UNIQUE INDEX cc000000h9U9 ON cc_check (PublicID, Retired); > >> > >> > > CREATE INDEX cc000000h9N10 ON cc_check (CheckSetID, Retired); > >> > >> > > CREATE INDEX cc000000h9N11 ON cc_check (ClaimContactID, Retired); > >> > >> > > CREATE INDEX cc000000h9N12 ON cc_check (ClaimID, Retired); > >> > >> > > CREATE INDEX cc000000h9N13 ON cc_check (PortionID, Retired); > >> > >> > > CREATE INDEX cc000000h9N14 ON cc_check (BulkInvoiceItemInfoID, > >> > >> > > Retired); > >> > >> > > CREATE INDEX cc000000h9N15 ON cc_check (RecurringCheckID, > >> > >> > > Retired); > >> > >> > > CREATE UNIQUE INDEX cc000000h9U16 ON cc_check (ID, extractready); > > >> > >> > > create table cc_transaction (Status integer NOT NULL, PaymentType > >> > >> > > integer NULL, CheckID integer NULL, UpdateTime datetime NOT NULL, > >> > >> > > CreateTime datetime NOT NULL, Matter integer NULL, BeanVersion > >> > integer > >> > >> > > NULL, TransToClaimExchangeRate integer NULL, Currency integer NOT > >> > >> > > NULL, LoadCommandID integer NULL, Retired integer NOT NULL, > >> > >> > > extractready integer NULL, CostCategory integer NOT NULL, > >> > >> > > DoesNotErodeReserves bit NULL, TransactionSetID integer NOT NULL, > >> > >> > > CreateUserID integer NULL, ClaimContactID integer NULL, CostType > >> > >> > > integer NOT NULL, ClaimID integer NOT NULL, PublicID varchar(20) > >> > >> > > NOT > >> > >> > > NULL, ClaimToReportingExchangeRate integer NULL, ExposureID > >> > >> > > integer > >> > >> > > NULL, RecoveryCategory integer NULL, LifeCycleState integer NOT > >> > NULL, > >> > >> > > Subtype integer NOT NULL, ReserveLineID integer NOT NULL, > >> > >> > > SubmitDate > >> > >> > > datetime NULL, PayerDenormID integer NULL, CloseClaim bit NULL, > >> > >> > > Comments varchar(255) NULL, CloseExposure bit NULL, UpdateUserID > >> > >> > > integer NULL, ID integer NOT NULL); > >> > >> > > ALTER TABLE cc_transaction ADD PRIMARY KEY (ID); > >> > >> > > CREATE INDEX cc000000hvN0 ON cc_transaction (extractready); > >> > >> > > CREATE INDEX cc000000hvN1 ON cc_transaction (TransactionSetID, > >> > >> > > ClaimID); > >> > >> > > CREATE INDEX cc000000hvN2 ON cc_transaction (ExposureID, ClaimID); > >> > >> > > CREATE INDEX cc000000hvN3 ON cc_transaction (ExposureID, Subtype, > >> > >> > > Status); > >> > >> > > CREATE INDEX cc000000hvN4 ON cc_transaction (ClaimID, Retired, > >> > >> > > UpdateTime); > >> > >> > > CREATE UNIQUE INDEX cc000000hvU5 ON cc_transaction (CreateUserID, > >> > >> > > Retired, Subtype, ClaimID, ID); > >> > >> > > CREATE INDEX cc000000hvN6 ON cc_transaction (Subtype, > >> > >> > > CreateUserID, > >> > >> > > Retired, CreateTime, ClaimID); > >> > >> > > CREATE UNIQUE INDEX cc000000hvU7 ON cc_transaction > >> > >> > > (LifeCycleState, > >> > >> > > Subtype, Retired, ID); > >> > >> > > CREATE UNIQUE INDEX cc000000hvU8 ON cc_transaction (PublicID, > >> > >> > > Retired); > >> > >> > > CREATE UNIQUE INDEX cc000000hvU9 ON cc_transaction (PayerDenormID, > >> > >> > > Retired, Subtype, CreateTime, ClaimID, ID); > >> > >> > > CREATE INDEX cc000000hvN10 ON cc_transaction (CheckID, Retired); > >> > >> > > CREATE INDEX cc000000hvN11 ON cc_transaction (Matter, Retired); > >> > >> > > CREATE INDEX cc000000hvN12 ON cc_transaction > >> > >> > > (TransToClaimExchangeRate, Retired); > >> > >> > > CREATE INDEX cc000000hvN13 ON cc_transaction (TransactionSetID, > >> > >> > > Retired); > >> > >> > > CREATE INDEX cc000000hvN14 ON cc_transaction (ClaimContactID, > >> > >> > > Retired); > >> > >> > > CREATE INDEX cc000000hvN15 ON cc_transaction > >> > >> > > (ClaimToReportingExchangeRate, Retired); > >> > >> > > CREATE INDEX cc000000hvN16 ON cc_transaction (ExposureID, > >> > >> > > Retired); > >> > >> > > CREATE INDEX cc000000hvN17 ON cc_transaction (ReserveLineID, > >> > Retired); > >> > >> > > CREATE UNIQUE INDEX cc000000hvU18 ON cc_transaction (ID, > >> > >> > > extractready); > > >> > >> > > create table cc_transactionoffsetonset (extractready integer NULL, > >> > >> > > OnsetID integer NULL, OffsetID integer NOT NULL, PublicID > >> > varchar(20) > >> > >> > > NOT NULL, BeanVersion integer NULL, TransactionID integer NOT > >> > >> > > NULL, > >> > ID > >> > >> > > integer NOT NULL, LoadCommandID integer NULL); > >> > >> > > ALTER TABLE cc_transactionoffsetonset ADD PRIMARY KEY (ID); > >> > >> > > CREATE UNIQUE INDEX cc000000j0U0 ON cc_transactionoffsetonset > >> > >> > > (OffsetID); > >> > >> > > CREATE UNIQUE INDEX cc000000j0U1 ON cc_transactionoffsetonset > >> > >> > > (PublicID); > >> > >> > > CREATE INDEX cc000000j0N2 ON cc_transactionoffsetonset (OnsetID); > >> > >> > > CREATE INDEX cc000000j0N3 ON cc_transactionoffsetonset > >> > (extractready); > >> > >> > > CREATE UNIQUE INDEX cc000000j0U4 ON cc_transactionoffsetonset > >> > >> > > (TransactionID); > >> > >> > > CREATE UNIQUE INDEX cc000000j0U5 ON cc_transactionoffsetonset > >> > >> > > (TransactionID, OffsetID, OnsetID); > >> > >> > > CREATE UNIQUE INDEX cc000000j0U6 ON cc_transactionoffsetonset > >> > >> > > (OffsetID, OnsetID); > >> > >> > > CREATE UNIQUE INDEX cc000000j0U7 ON cc_transactionoffsetonset (ID, > >> > >> > > extractready); > > >> > >> > > On Jan 28, 11:02 pm, Thomas Mueller <[email protected]> > >> > >> > > wrote: > >> > >> > >> Hi, > > >> > >> > >> Could you post your CREATE TABLE and CREATE INDEX statements as > >> > well please? > > >> > >> > >> Regards, > >> > >> > >> Thomas > > >> > >> > >> On Tue, Jan 27, 2009 at 7:40 AM, Wes Clark <[email protected]> > >> > wrote: > > >> > >> > >> > Could this be another manifestation of the problem with nested > >> > outer > >> > >> > >> > joins reported in > > >> >http://groups.google.com/group/h2-database/browse_thread/thread/af436... > > --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/h2-database?hl=en -~----------~----~----~----~------~----~------~--~---
