Hi, Thanks a lot! I could simplify the test to just this:
drop table test; create table test(id int, name varchar); insert into test values (2, 'a'), (1, 'a'); create index on test(name); explain select id from test where name = 'a' order by id; The root cause seems to be a recent change in version 1.4.186: "Make the planner use indexes for sorting when doing a GROUP BY where all of the GROUP BY columns are not mentioned in the select. Patch by Frederico ( zepfred)." I think I will just undo that change. If somebody is interested to fix the problem, please submit a pull request. This time I will be more cautious about accepting changes that I don't understand. For example the method "isDisjunctive" needs a much better explanation or name (even thought the bug is probably not there). Regards, Thomas On Wednesday, June 17, 2015, David Walend <[email protected]> wrote: > The best I'm getting out of Slick for insert statements is: > > insert into "topics" > ("id","name","description","createdBy","createDate","state","changedBy","changeDate") > values (?,?,?,?,?,?,?,?) > > five times. > > From the H2 logs I see > > insert into \"topics\" > (\"id\",\"name\",\"description\",\"createdBy\",\"createDate\",\"state\",\"changedBy\",\"changeDate\") > values (?,?,?,?,?,?,?,?) {1: 1, 2: 'UncontroversialKidneys', 3: 'Study > kidneys without controversy', 4: 'ben', 5: 1434573100466, 6: 'Pending', 7: > 'ben', 8: 1434573100466}; > insert into \"userTopic\" > (\"researcher\",\"topicId\",\"state\",\"changedBy\",\"changeDate\") values > (?,?,?,?,?) {1: 'ben', 2: 1, 3: 'Approved', 4: 'ben', 5: 1434573100466}; > insert into \"topics\" > (\"id\",\"name\",\"description\",\"createdBy\",\"createDate\",\"state\",\"changedBy\",\"changeDate\") > values (?,?,?,?,?,?,?,?) {1: 2, 2: 'slightlyControversial', 3: 'who > cares?', 4: 'ben', 5: 1434573100575, 6: 'Pending', 7: 'ben', 8: > 1434573100575}; > insert into \"userTopic\" > (\"researcher\",\"topicId\",\"state\",\"changedBy\",\"changeDate\") values > (?,?,?,?,?) {1: 'ben', 2: 2, 3: 'Approved', 4: 'ben', 5: 1434573100575}; > insert into \"topics\" > (\"id\",\"name\",\"description\",\"createdBy\",\"createDate\",\"state\",\"changedBy\",\"changeDate\") > values (?,?,?,?,?,?,?,?) {1: 3, 2: 'controversial', 3: 'controversial', 4: > 'ben', 5: 1434573100582, 6: 'Pending', 7: 'ben', 8: 1434573100582}; > > I've attached the H2 log for both 1.4.185 and 1.4.186 if that helps. > Nothing meaningful to me in them, but it does show exactly what Slick is > doing. > > Did any of the changes between 1.4.185 and 1.4.186 touch sorting code or > that query-in-a-query? > > Thanks again, > > David > > On Wednesday, June 17, 2015 at 1:46:41 AM UTC-4, Thomas Mueller wrote: >> >> 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 >>> >> -- > 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.
