Re: SQL_CALC_FOUND_ROWS support in jooq

[Lukas Eder]

That's quite an interesting MySQL feature. Looks much like an Oracle
hint to me. In Oracle, you would write:

SELECT /*+INDEX(...)*/ * FROM t WHERE ...

So if this SQL_CALC_FOUND_ROWS option is to be put between the SELECT
(DISTINCT) keyword(s) and the SELECT column expression list, you could
use jOOQ's .hint() method for that:
http://www.jooq.org/javadoc/latest/org/jooq/SelectFromStep.html#hint(java.lang.String)

An example is given in the manual:
http://www.jooq.org/doc/2.6/manual/sql-building/sql-statements/select-statement/oracle-hints/

Let me know if this works for you. If it does, I'll add some more
documentation to the hint() method, then

Cheers
Lukas

2012/11/11 <hirsch...@gmail.com>:
> Is it possible to add/render the "SQL_CALC_FOUND_ROWS" which is necessary
> for the by MySql FOUND_ROWS() function to the Jooq-Select Statement to
> retrieve the total of the result set not limit by the limit statement ?
>
> MySql Reference: FOUND_ROWS()
>
> My workaround would be to add "SQL_CALC_FOUND_ROWS" as a Jooq-Field but jooq
> will render the comma ',' after the "SQL_CALC_FOUND_ROWS" which has to be
> removed after rendering. Not that nice workaround which I'd like to avoid.
>
> Thanks.

[Lukas Eder]

> Actually I want to figure out the total in a column of the select before
> limiting the result.
> In Oracle I would do it probably by selecting the data within an inner
> select and a field with the window function "COUNT(*) OVER()" which is also
> provided by Jooq and works for me on Oracle, SQL Server. Unfortunately MySql
> does not support this functionality and you have to do it with the nice but
> different "FOUND_ROWS()" function.

Yes, I'm afraid there is no simple way to implement this uniformly
with MySQL lacking window function support.

> H2 not support it yet, but might upcoming with the 1.4.X verison.
> As you probably know because I saw that you requested some window function
> for H2 in the H2 community :).

True, I have been frequently trying to raise interest in this SQL
standard feature in open source communities. I can recognise a couple
of my own feature requests on the H2 roadmap:
http://www.h2database.com/html/roadmap.html

Feel free to suggest adding window function support once more on the
H2 user group.

CUBRID, another fine open source database has recently released
version 9.0 with partial window function support:
http://www.cubrid.org/blog/news/announcing-cubrid-9-0-with-3x-performance-increase-and-sharding-support/

Cheers
Lukas

[Michael Hirsch]

Thanks for the help Lukas,

Actually Jooqs "hint" would work to add the "SQL_CALC_FOUND_ROWS" to the select statement and Jooq will render it correctly, thanks for that!

Unfortunately it's only possible to add the hint on a SelectFromStep (http://www.jooq.org/javadoc/latest/org/jooq/SelectFromStep.html#hint(java.lang.String) ) 

This is what is not working in my case in the code because I don't have the SelectFromStep anymore :).

Is it possible to add it afterwards when fetching the select statement to get called by a render mechanism to add this later on?

P.S. I've been so far very happy with Jooq and the functionality, looking forward for upcomming releases and features :).

Thanks for any help :).

Cheers 

Michael

[Lukas Eder]

Hello,

> This is what is not working in my case in the code because I don't have the
> SelectFromStep anymore :).

An unsafe way to solve this is to cast your "too-far-advanced" Step
class to SelectFromStep. This might not work in the future, though.

> Is it possible to add it afterwards when fetching the select statement to
> get called by a render mechanism to add this later on?

Yes. At any time, you can access the DSL API's underlying "classic"
SelectQuery object. An example is given here:
http://www.jooq.org/doc/2.6/manual/sql-building/sql-statements/dsl-and-non-dsl/#N106B7

Using getQuery().addHint(...), you can then modify the query being
created. See the relevant Javadoc here:
http://www.jooq.org/javadoc/latest/org/jooq/SelectQuery.html#addHint(java.lang.String)

> P.S. I've been so far very happy with Jooq and the functionality, looking
> forward for upcomming releases and features :).

Yes, me too! I'm curious how type-safe row value expressions will be
adopted by the community

Cheers
Lukas

[Michael Hirsch]

"Using getQuery().addHint(...)" this saved my day! 

Thanks a lot for the quick and awesome response and feedback and help! :). 

Like Jooq day by day more and more :).

[Lukas Eder]

> "Using getQuery().addHint(...)" this saved my day!

Great, good to know it worked. I'll add some Javadoc and manual documentation :
https://github.com/jOOQ/jOOQ/issues/1967

> Like Jooq day by day more and more :).

Feel free to write a success story in your blog, etc... :-)

Cheers
Lukas

[jayson...@gmail.com]

In current JOOQ, this doesn't work for me, FOUND_ROWS() returns 1 every time.  It is like some other query is executing in between.  

11:40:48.592 [main] DEBUG org.jooq.tools.LoggerListener - Executing query          : select SQL_CALC_FOUND_ROWS `OrgUserRole`.`guid`, `OrgUserRole`.`orgGuid`, `OrgUserRole`.`userGuid`, `OrgUserRole`.`orgUserRoleType`, `OrgUserRole`.`dtCreated`, `OrgUserRole`.`dtUpdated` from `OrgUserRole` where `OrgUserRole`.`userGuid` = ? order by cast(`OrgUserRole`.`orgGuid` as binary) asc limit ?

11:40:48.592 [main] DEBUG org.jooq.tools.LoggerListener - -> with bind values      : select SQL_CALC_FOUND_ROWS `OrgUserRole`.`guid`, `OrgUserRole`.`orgGuid`, `OrgUserRole`.`userGuid`, `OrgUserRole`.`orgUserRoleType`, `OrgUserRole`.`dtCreated`, `OrgUserRole`.`dtUpdated` from `OrgUserRole` where `OrgUserRole`.`userGuid` = 'Ie8qOLrWXo2XRr2Cosozzw' order by cast(`OrgUserRole`.`orgGuid` as binary) asc limit 10

11:40:48.598 [main] DEBUG org.jooq.tools.StopWatch - Query executed           : Total: 6.918ms

11:40:48.598 [main] DEBUG org.jooq.tools.LoggerListener - Fetched result           : +----------------------+----------------------+----------------------+---------------+-----------------------+-----------------------+

11:40:48.598 [main] DEBUG org.jooq.tools.LoggerListener -                          : |guid                  |orgGuid               |userGuid              |orgUserRoleType|dtCreated              |dtUpdated              |

11:40:48.599 [main] DEBUG org.jooq.tools.LoggerListener -                          : +----------------------+----------------------+----------------------+---------------+-----------------------+-----------------------+

11:40:48.599 [main] DEBUG org.jooq.tools.LoggerListener -                          : |AufS0_TaiKiFQZurMts9KA|00AZZ_WIMqkuSH3WI9LhJw|Ie8qOLrWXo2XRr2Cosozzw|Owner          |2016-04-07 11:40:46.625|2016-04-07 11:40:46.625|

11:40:48.599 [main] DEBUG org.jooq.tools.LoggerListener -                          : |UAYdanK9cYFoQTqBeVz6AQ|0wBdUOhLOZe6QfdZZrOQkw|Ie8qOLrWXo2XRr2Cosozzw|Owner          |2016-04-07 11:40:47.318|2016-04-07 11:40:47.318|

11:40:48.599 [main] DEBUG org.jooq.tools.LoggerListener -                          : |XdLw8BSEVIa0SIyrdHBz0Q|1-cdqaqInq19QDwcyHRemg|Ie8qOLrWXo2XRr2Cosozzw|Owner          |2016-04-07 11:40:46.381|2016-04-07 11:40:46.381|

11:40:48.599 [main] DEBUG org.jooq.tools.LoggerListener -                          : |SMIGsWFAXKVQQSK1x4cKIg|17TdJt9fwZj5TqcCz04ZjA|Ie8qOLrWXo2XRr2Cosozzw|Owner          |2016-04-07 11:40:46.913|2016-04-07 11:40:46.913|

11:40:48.599 [main] DEBUG org.jooq.tools.LoggerListener -                          : |xgppTJDF66mfRK2N-Burjg|1UQmbt3IqJLuR3NZzrzeWQ|Ie8qOLrWXo2XRr2Cosozzw|Owner          |2016-04-07 11:40:46.479|2016-04-07 11:40:46.479|

11:40:48.599 [main] DEBUG org.jooq.tools.LoggerListener -                          : +----------------------+----------------------+----------------------+---------------+-----------------------+-----------------------+

11:40:48.599 [main] DEBUG org.jooq.tools.LoggerListener -                          : |...5 record(s) truncated...

11:40:48.599 [main] DEBUG org.jooq.tools.StopWatch - Finishing                : Total: 8.366ms, +1.448ms

11:40:48.600 [main] DEBUG org.jooq.tools.LoggerListener - Executing query          : select FOUND_ROWS()

11:40:48.601 [main] DEBUG org.jooq.tools.StopWatch - Query executed           : Total: 0.791ms

11:40:48.609 [main] DEBUG org.jooq.tools.LoggerListener - Fetched result           : +------------+

11:40:48.609 [main] DEBUG org.jooq.tools.LoggerListener -                          : |FOUND_ROWS()|

11:40:48.609 [main] DEBUG org.jooq.tools.LoggerListener -                          : +------------+

11:40:48.609 [main] DEBUG org.jooq.tools.LoggerListener -                          : |           1|

11:40:48.609 [main] DEBUG org.jooq.tools.LoggerListener -                          : +------------+

running the queries in SQL workbench...

use integration_test;
select SQL_CALC_FOUND_ROWS `OrgUserRole`.`guid`, `OrgUserRole`.`orgGuid`, `OrgUserRole`.`userGuid`, `OrgUserRole`.`orgUserRoleType`, `OrgUserRole`.`dtCreated`, `OrgUserRole`.`dtUpdated` from `OrgUserRole` where `OrgUserRole`.`userGuid` = 'Ie8qOLrWXo2XRr2Cosozzw' order by cast(`OrgUserRole`.`orgGuid` as binary) asc limit 10;
Select FOUND_ROWS();

returns the expected results (100 in this case)

My queries above are running in the same transaction (using JOOQ transaction closure)

[Lukas Eder]

Hi Jayson,

Hmm, interesting finding. A quick search on google has shown that some JDBC API calls can cause such effects, e.g. when calling metaData.getColumnDisplaySize():

https://bugs.mysql.com/bug.php?id=15117

jOOQ by default fetches all SQLWarnings. You can turn this off via Settings.fetchWarnings. Perhaps that's the reason why you get 1, because the Statement.getWarnings() call runs another statement...?

What happens if you run the SELECT FOUND_ROWS() statement inside of an ExecuteListener (directly with JDBC), e.g. in the executeEnd() method?

Best Regards,

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Jayson Minard]

It was me actually, I accidentally used a top level context instead of the one from the transaction so it basically executed the `FOUND_ROWS()` outside of the transaction which caused it to get a value of 0 or 1.

--
You received this message because you are subscribed to a topic in the Google Groups "jOOQ User Group" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/jooq-user/F60V--1QbRw/unsubscribe.
To unsubscribe from this group and all its topics, send an email to jooq-user+...@googlegroups.com.

[Lukas Eder]

That does explain it :) Thanks for the feedback!