Hi, You forgot to post data (insert statements). Without those, nobody can reproduce your problem. What we need is a complete script that we can run that that exhibits the problem. For example (this doesn't show the problem):
drop table topics; create table topics (id INTEGER NOT NULL,name VARCHAR(254) NOT NULL, description TEXT NOT NULL,createdBy VARCHAR(254) NOT NULL, createDate BIGINT NOT NULL,state VARCHAR(254) NOT NULL, changedBy VARCHAR(254) NOT NULL,changeDate BIGINT NOT NULL); create index changeDateIndex on topics (changeDate); create index changedByIndex on topics (changedBy); create index createDateIndex on topics (createDate); create index createdByIndex on topics (createdBy); create index idIndex on topics (id); create index stateIndex on topics (state); create index topicNameIndex on topics (name); insert into topics values (1, 'f', 'x', 'ben', 100, 10, 'ben', 1), (2, 'e', 'x', 'joe', 300, 20, 'joe', 2), (3, 'd', 'x', 'tom', 300, 30, 'tom', 3), (4, 'c', 'x', 'ben', 400, 40, 'ben', 4), (5, 'b', 'x', 'joe', 500, 50, 'joe', 5), (6, 'a', 'x', 'tom', 600, 60, 'tom', 6); select x2.id, x2.name, x2.description, x2.createdBy, x2.createDate, x2.state, x2.changedBy, x2.changeDate from topics x2 where (not exists(select * from topics x3 where (x3.id = x2.id) and (x3.changeDate > x2.changeDate))) and (x2.createdBy = 'ben') order by x2.name; Regards, Thomas On Monday, June 15, 2015, David Walend <[email protected]> wrote: > It's hard to get to simple, but I can make it complete. Slick 3.0 is > generating this (surprisingly not too cryptic) SQL for me: > > select x2."id", x2."name", x2."description", x2."createdBy", > x2."createDate", x2."state", x2."changedBy", x2."changeDate" from "topics" > x2 where (not exists(select x3."createDate", x3."description", x3."state", > x3."changeDate", x3."changedBy", x3."id", x3."createdBy", x3."name" from > "topics" x3 where (x3."id" = x2."id") and (x3."changeDate" > > x2."changeDate"))) and (x2."createdBy" = 'ben') order by x2."name" > > > The internal "where (not exists(select x3 ...)) business is getting the > most recent entry for a topic record in the table. > > > The topics table ddl is: > > > create table `topics` (`id` INTEGER NOT NULL,`name` VARCHAR(254) NOT > NULL,`description` TEXT NOT NULL,`createdBy` VARCHAR(254) NOT > NULL,`createDate` BIGINT NOT NULL,`state` VARCHAR(254) NOT NULL,`changedBy` > VARCHAR(254) NOT NULL,`changeDate` BIGINT NOT NULL); > > > with lots of indexing (I hope this part doesn't matter): > > > create index `changeDateIndex` on `topics` (`changeDate`); > > create index `changedByIndex` on `topics` (`changedBy`); > > create index `createDateIndex` on `topics` (`createDate`); > > create index `createdByIndex` on `topics` (`createdBy`); > > create index `idIndex` on `topics` (`id`); > > create index `stateIndex` on `topics` (`state`); > > create index `topicNameIndex` on `topics` (`name`); > > Again, 1.4.185 is fine, but 1.4.186 ignores the final "order by" clause. > > Thanks for looking into it, > > David > > On Monday, June 15, 2015 at 7:58:57 AM UTC-4, Thomas Mueller wrote: >> >> Hi, >> >> I can't reproduce this problem. Could you post a simple test case please? >> >> Regards, >> Thomas >> >> On Fri, Jun 12, 2015 at 8:39 PM, David Walend <[email protected]> wrote: >> >>> I'm seeing a similar problem, but not in all cases. 1.4.185 is fine. >>> 1.4.186 and 1.4.187 fail a specific test, but not all tests. >>> >>> Did you ever resolve this issue? If so, what was the fix? >>> >>> Thanks, >>> >>> David >>> >>> >>> On Monday, April 6, 2015 at 11:39:22 PM UTC-4, Serge Korn wrote: >>>> >>>> Hi, >>>> >>>> Since upgrading to the latest beta release I noticed that "order by" is >>>> ignored when used in combination with "where" clause. >>>> When I downgraded to 1.4.184 the same query works just fine. >>>> >>>> Be aware, >>>> Serge >>>> >>> -- >>> You received this message because you are subscribed to the Google >>> Groups "H2 Database" group. >>> To unsubscribe from this group and stop receiving emails from it, send >>> an email to [email protected]. >>> To post to this group, send email to [email protected]. >>> Visit this group at http://groups.google.com/group/h2-database. >>> For more options, visit https://groups.google.com/d/optout. >>> >> >> -- > You received this message because you are subscribed to the Google Groups > "H2 Database" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected] > <javascript:_e(%7B%7D,'cvml','h2-database%[email protected]');> > . > To post to this group, send email to [email protected] > <javascript:_e(%7B%7D,'cvml','[email protected]');>. > Visit this group at http://groups.google.com/group/h2-database. > For more options, visit https://groups.google.com/d/optout. > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
