sortBy and take/drop resulting in wrong SQL

[schmitch]

Hello when running a sortBy(_.id.desc).take(1) the resulting sql will be totally wrong.
It will be a SELECT columns FROM (SUBQUERY) instead of the wished SELECT columns FROM table ORDER BY x LIMIT 1;

[cvogt]

does it produce different results? what is the sub query?

[schmitch]

Currently the Query that will be generated is:

select x2."id", x2."oa_nr", x2."data", x2."old_id", x2."username" from (select x3."oa_nr" as "oa_nr", x3."username" as "username", x3."data" as "data", x3."id" as "id", x3."old_id" as "old_id" from "article_article" x3 limit 1) x2 order by x2."id" desc

The result is correct it's just that the Query is way slower due to the subquery..

[cvogt]

That's a known issue when using Slick with MySQL. Neither Slicks nor MySQL's optimizers currently optimize this away. Works well with other dbs AFAIK. Slick 3.1 will come with an improved optimizer that produces the SQL you expected.

[cvogt]

#1133

[cvogt]

"wrong" isn't really the case by the way. the produced query is semantically correct. SQL doesn't have execution semantics in the language, that's for the database to decide. The MySQL optimizer however didn't chose a good execution plan for this case and it is indeed possible to affect that by changing the structure of the query. but that is db and optimizer dependent and could even depend on runtime profiling data.

[schmitch]

Good that I use PostgreSQL and still having a wrong query.
Semantically it's wrong. It's only right that they will result in the same value. If you run EXPLAIN ANALYZE you will see that a subquery with an order query will take a huge amount of time. especially when your data is not zero.

[cvogt]

whats the performance difference?

[cvogt]

I guess we disagree on the definition of semantics :). Anyways fixed in upcoming 3.1 or should already be in recent builds from master if I am not mistaken