Weird Problem with a count query (always return 1)

[petri.ka...@gmail.com]

I ran into a really weird problem when I tried to implement a count query with jOOQ:

It doesn't matter how many rows the todos table has. My count query always returns 1. The cleaned up version of my repository looks as follows:


import org.jooq.Condition;
import org.jooq.DSLContext;

import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import static net.petrikainulainen.spring.jooq.todo.db.tables.Todos.TODOS;

@Repository
public class JOOQTodoRepository implements TodoRepository {

    private final DSLContext jooq;
   
    //Removed constructor for the sake of clarity

    @Transactional(readOnly = true)
    @Override
    public long findCountBySearchTerm(String searchTerm) {
        String likeExpression = "%" + searchTerm + "%";

           return jooq.selectCount()
               .from(TODOS)
               .where(
                       TODOS.DESCRIPTION.likeIgnoreCase(likeExpression)
                        .or(TODOS.TITLE.likeIgnoreCase(likeExpression)
                )
               .fetchCount();
    }
}

I am using jOOQ 3.3.2 and H2 version 1.3.174. All help is appreciated :)

[Lukas Eder]

What your query effectively does is this:

SELECT COUNT(*) FROM (

    SELECT COUNT(*)

    FROM TODOS

    WHERE ...

)

So, you've successfully created a "value 1 generator", as that's indeed the only value that can ever come out of that query. :-)

But I can easily see how this fetchCount() method is confusing. It was intended as a convenience for writing

jooq.fetchCount(select().from(TODOS).where(...));

... in case of which the semantics is clear. But since there is also a possibility to write

List<Integer> counts =

jooq.select(count())

    .from(TODOS)

    .where(...)

    .fetch(count());

... the confusion is perfect.

Thanks for reporting! I guess we'll have to remove this Select.fetchCount() query again. We'll deprecate it for future versions of jOOQ:

https://github.com/jOOQ/jOOQ/issues/3356

It's quite contrary to "ordinary jOOQ intuition". I'm sorry for the inconvenience this has caused.

Best,

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.

[petri.ka...@gmail.com]

So, you've successfully created a "value 1 generator", as that's indeed the only value that can ever come out of that query. :-)

I wonder if I can patent this... Anyway, thank you for your answer!

[Lukas Eder]

Hmm, I think the idea of having such a value generator has been published before:

http://search.dilbert.com/comic/Random%20Number%20Generator

You're welcome. Again, sorry for the confusion. From an API user perspective, do you maybe have an idea how this could be improved? While the concrete execution of this functionality is confusing, the general idea is still useful. It would be great to have your feedback on the issue, if you have any ideas:

https://github.com/jOOQ/jOOQ/issues/3356

Best,

Lukas

2014-06-25 23:26 GMT+02:00 <petri.ka...@gmail.com>:

So, you've successfully created a "value 1 generator", as that's indeed the only value that can ever come out of that query. :-)

I wonder if I can patent this... Anyway, thank you for your answer!

--

[petri.ka...@gmail.com]

Sorry that I was a bit slow to answer to this.

You're welcome. Again, sorry for the confusion.

I think that this was a stupid developer problem (at least partially). For some reason I missed the created SQL query even though I read the SQL queries from my log file.
 

From an API user perspective, do you maybe have an idea how this could be improved? While the concrete execution of this functionality is confusing, the general idea is still useful. It would be great to have your feedback on the issue, if you have any ideas:

https://github.com/jOOQ/jOOQ/issues/3356

Well, I am sure if the current is necessarily bad. The reason why I originally used it in the wrong way was that I thought that

1. I need to call the selectCount() so that I can create a count query (like I have to call select() so that I can create a SELECT query).
2. fetchCount() and fetchOne() works in the same way (I need to call one of them to obtain the query results).
   

Also, for some reason I couldn't find a count query example from the reference manual. Does it have one?

 

[Lukas Eder]

Hello Petri,

2014-07-01 9:25 GMT+02:00 <petri.ka...@gmail.com>:

Sorry that I was a bit slow to answer to this.

You're welcome. Again, sorry for the confusion.

I think that this was a stupid developer problem (at least partially). For some reason I missed the created SQL query even though I read the SQL queries from my log file.

OK. Shame on us both ;-)

 

 

From an API user perspective, do you maybe have an idea how this could be improved? While the concrete execution of this functionality is confusing, the general idea is still useful. It would be great to have your feedback on the issue, if you have any ideas:

https://github.com/jOOQ/jOOQ/issues/3356

Well, I am sure if the current is necessarily bad. The reason why I originally used it in the wrong way was that I thought that

1. I need to call the selectCount() so that I can create a count query (like I have to call select() so that I can create a SELECT query).
2. fetchCount() and fetchOne() works in the same way (I need to call one of them to obtain the query results).
   

That's true, but fetchOne() (as well as fetchAny(), fetchMaps(), fetchArray(), etc.) are just convenience methods for fetchLazy() and then wrap the Cursor in some behaviour. fetchCount() is completely different in that it does not actually execute the query it operates upon, but wraps that query in another query. I think that reusing the word "fetch" here is not optimal...

Also, for some reason I couldn't find a count query example from the reference manual. Does it have one?

The spring examples have some DSLContext.fetchCount(Table) examples:

http://www.jooq.org/doc/3.4/manual/getting-started/tutorials/jooq-with-spring/#N104FF

But that's not a really good clue :-)

Not all features are documented in the manual. But all of them are documented in the Javadoc... Maybe, this particular feature deserves a section in the manual, though:

https://github.com/jOOQ/jOOQ/issues/3368

Cheers

Lukas