Mocking issue with fields

[osor...@gmail.com]

Hi!

I've been trying to write a mocked test that "proves" the correctness of a query.

The query looks like this:

dslContext.select(TBL_TASK.STATE) //
        .from(TBL_TASK) //
        .where(TBL_TASK.ID.eq(taskId)) //
        .fetchOne(TBL_TASK.STATE);

And the test looks like this:

int taskState = 5;
int taskId = 8;

MockDataProvider provider = context -> {
    DSLContext create = DSL.using(SQLDialect.MYSQL);
    MockResult[] mock = new MockResult[1];

    Result<Record1<Integer>> result = create.newResult(TBL_TASK.STATE);
    result.add(create.newRecord(TBL_TASK.STATE).values(taskState));
    mock[0] = new MockResult(1, result);
    return mock;
};

Connection connection = new MockConnection(provider);
DSLContext create = DSL.using(connection, SQLDialect.MYSQL);
Assert.assertEquals(taskState, getTaskState(create, taskId));

This seems to work fine when you try for example try to "fetchOne(TBL_TASK.ID)" or try to "select(TBL_TASK.ID)"

But this:

dslContext.select(TBL_TASK.ID) //
        .from(TBL_TASK) //
        .where(TBL_TASK.ID.eq(taskId)) //
        .fetchOne(TBL_TASK.ID);

Also works fine for some reason, even though the mock returns a record containing a state-field? Maybe I'm doing something wrong or have the wrong expectations about how mocking works, but it looks like the fetchOne-function doesn't care about the field name?

Like I said, the test can prove that the query itself is written wrong (selecting state but fetching id), but it can't prove that you aren't trying to correctly access a completely different column (selecting and fetching id-column when mock returns state-column).

[Lukas Eder]

Hello

2017-11-29 12:21 GMT+01:00 <osor...@gmail.com>:

Also works fine for some reason, even though the mock returns a record containing a state-field? Maybe I'm doing something wrong or have the wrong expectations about how mocking works, but it looks like the fetchOne-function doesn't care about the field name?

The fetchOne() method doesn't influence how the query is executed in any way, it simply specifies how the result should be processed, once it is available (i.e. it fetches at most one record and in your case, only one column from that record).

So, the interesting part is the select() clause, which tells jOOQ that the query expects exactly one column of a given type. Now that the row type is known to jOOQ (and jOOQ expects JDBC drivers to produce something reasonable, and this includes your MockDataProvider), jOOQ doesn't have to rely on any column name but can access the result values by index - which is much faster in most JDBC drivers.

If you didn't provide any columns in the select() clause and the from() clause also didn't provide the entirety of all columns (e.g. because there is plain SQL involved), then jOOQ would detect reported column names from the JDBC driver through ResultSetMetaData.

 

Like I said, the test can prove that the query itself is written wrong (selecting state but fetching id), but it can't prove that you aren't trying to correctly access a completely different column (selecting and fetching id-column when mock returns state-column).

You got the "wrong" part wrong. The query isn't written wrong. It's just that your MockDataProvider will always return that particular 1x1 result and since jOOQ already knows the resulting rowtype in advance (you provided it), it doesn't check ResultSetMetaData for what the *actual* column names and types might be.

I hope this helps,

Lukas

[osor...@gmail.com]

Thank you, that helps somewhat. You're basically saying that me specifying a column (fetchOne(state)) just fetches the first index instead, yes? I verified this behavior by mocking a bad result where two columns switched places (select(id, state) returns result "state,id").

I am still a bit unsure about the correct approach to writing a test that verifies that the query is selecting the correct column name (ignoring the topic of whether there is any real point to doing this in the first place). Maybe it's more effort than it's actually worth?

It's not a huge deal really, but as a jOOQ-rookie I figured I should try to figure out if it is doable!

[Lukas Eder]

2017-11-29 15:30 GMT+01:00 <osor...@gmail.com>:

Thank you, that helps somewhat. You're basically saying that me specifying a column (fetchOne(state)) just fetches the first index instead, yes? I verified this behavior by mocking a bad result where two columns switched places (select(id, state) returns result "state,id").

No, be careful, that's not what I was saying. YOU specified that STATE was the column at the first index by *putting it there* in your select() clause. Again, fetchOne() has nothing to do with this. Here's the workflow:

1. select(STATE).fetchXYZ() runs a query expecting a STATE column at index 1 ... 1-based ;)

2. Because all the resulting rowtype information is known at compile time (because you put that column there), there's no need to look up whatever columns are returned by the database. We *expect* STATE to be the column at index 1.

3. Your MockDataProvider happens to produce a row with exactly 1 column. That might as well be a coincidence.

4. fetchLazy() (called internally by fetchOne()) now ignores column names and fetches only one column from index 1, again, because YOU said so.

5. fetchOne(STATE) is simply convenience API to map the Result<Record1<Integer>> result to a more convenient List<Integer> type by looking up the STATE column from the result of 4.

Step 3 is important! MockDataProvider mocks your entire database for *ALL* the queries that are sent through the MockConnection. Try it with JDBC directly:

try (Connection c = new MockConnection(...);

    Statement s = c.createStatement();

    ResultSet rs = s.executeQuery("blah")) {

    while (rs.next())

        System.out.println(rs.getString(1));

}

Now, it really doesn't matter if you replace "blah" by "SELECT STATE" .. or by "SELECT ID". Since you're selecting only one column (index 1), whatever is returned at index 1 will be placed in that column.

Why don't you just step through the library with a debugger (specifically the MockDataProvider part). You'll see it will all make sense.

I am still a bit unsure about the correct approach to writing a test that verifies that the query is selecting the correct column name (ignoring the topic of whether there is any real point to doing this in the first place). Maybe it's more effort than it's actually worth?

I don't know what your use-case is. In general: I doubt that mocking is the correct approach to testing jOOQ and/or the database. An integration test with an actual database and an actual table might be simpler...

It's not a huge deal really, but as a jOOQ-rookie I figured I should try to figure out if it is doable

Well, using one of the more advanced lower level SPIs might be a bit more challenging a task for a jOOQ-rookie, so you might have simply not chosen the easiest challenge to start working with jOOQ with :)

[osor...@gmail.com]

Right, thank you! Your clarification is basically what I meant, but I didn't express it too well I guess :D

As for my use-case, I suppose it was just an idea to implement an extra check on the SQL query. jOOQ already generates classes based on the actual tables, but when writing code there is nothing really stopping me from writing a query that selects a set of columns but then tries to fetch the result from a completely different column that was never queried for in the first place.

Example:

dslContext.select(TBL_TASK.ID) //
        .from(TBL_TASK) //
        .where(TBL_TASK.ID.eq(taskId)) //

        .fetchOne(TBL_TASK.STATE); // Not part of select!

Results in

IllegalArgumentException: Field ("db"."tbl_task"."state") is not contained in Row ("db"."tbl_task"."id")

The mock-tests catches stuff like this, but I noticed that it didn't catch me using the wrong column name altogether. It's a minor thing, but I was curious to see if it would be possible to catch this scenario as well.

Anyway, thanks for the help so far, I've learned about some possible pitfalls at least!

[Lukas Eder]

Sorry for the confusion. Just wanted to be sure there wasn't a misunderstanding.

2017-11-29 16:24 GMT+01:00 <osor...@gmail.com>:

As for my use-case, I suppose it was just an idea to implement an extra check on the SQL query. jOOQ already generates classes based on the actual tables, but when writing code there is nothing really stopping me from writing a query that selects a set of columns but then tries to fetch the result from a completely different column that was never queried for in the first place.

Yeah, that sort of type safety can't really be guaranteed by the jOOQ API, unfortunately, as Java doesn't support anonymous record types (yet). One option would be to call Record1.value1(), in case of which you can be sure of the field to exist, but you're back to referencing values by index, which is certainly less readable.

 

The mock-tests catches stuff like this, but I noticed that it didn't catch me using the wrong column name altogether. It's a minor thing, but I was curious to see if it would be possible to catch this scenario as well.

I don't think it is. You'd have to get deep into jOOQ and mock Records themselves, which is currently not possible, although it might be possible once we implement this:

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

Essentially, a jOOQ Record is a Map<Field<?>, ?>...

Thanks,

Lukas