InvalidSQLException While Unit Test
19 views
Skip to first unread message
Debapriya Patra
Aug 23, 2019, 12:02:36 AM8/23/19
to jOOQ User Group
Hi,
I am not able to figure out what really the cause of this error.
Is there a way to spit more details about the cause of the error while running unit test cases for the DAO classes ?
I have the select query and data in a file and when i am running the Mock unit test case, its breaking with the below exception.
20:54:30.301 [Test worker] DEBUG org.jooq.tools.jdbc.MockFileDatabase - Loaded SQL : select "latest_deck_version"."id", "latest_deck_version"."deleted", "latest_deck_version"."foreign_id", "latest_deck_version"."deck_type", "latest_deck_version"."title", "latest_deck_version"."confidential", "latest_deck_version"."certified", "latest_deck_version"."deck_id", "latest_deck_version"."original_created", "latest_deck_version"."original_updated", "latest_deck_version"."created", "latest_deck_version"."updated", "latest_deck_version"."num_cards", "latest_deck_version"."num_images", "latest_deck_version"."edition" from "public"."latest_deck_version"(cast('{"efb2d576-f2d3-4a8e-9715-dc95fdd14701","816d681e-18ca-4b61-938c-401b22c714f5"}' as uuid[]))20:54:30.418 [Test worker] DEBUG org.jooq.tools.jdbc.MockFileDatabase - Loaded Result : +------------------------------------+-------+----------+---------+---------+------------+---------+------------------------------------+-------------------+-------------------+-------------------+-------------------+---------+----------+--------------------------------+20:54:30.419 [Test worker] DEBUG org.jooq.tools.jdbc.MockFileDatabase - : |id |deleted|foreign_id|deck_type|title |confidential|certified|deck_id |original_created |original_updated |created |updated |num_cards|num_images|edition |20:54:30.419 [Test worker] DEBUG org.jooq.tools.jdbc.MockFileDatabase - : +------------------------------------+-------+----------+---------+---------+------------+---------+------------------------------------+-------------------+-------------------+-------------------+-------------------+---------+----------+--------------------------------+20:54:30.419 [Test worker] DEBUG org.jooq.tools.jdbc.MockFileDatabase - : |efb2d576-f2d3-4a8e-9715-dc95fdd14701|false |null |FINAL |titleTest|false |true |efb1d576-f2d3-4a8e-9715-dc95fdd14701|2018-10-19 17:44:50|2018-10-19 17:44:50|2018-10-19 17:44:50|2018-10-19 17:44:50|4 |0 |012a631c94215f2ad86cb66d0e7d3043|20:54:30.419 [Test worker] DEBUG org.jooq.tools.jdbc.MockFileDatabase - : |816d681e-18ca-4b61-938c-401b22c714f5|true |null |FINAL |titleDemo|true |true |efb6d576-f2d3-4a8e-9715-dc95fdd14701|2018-10-19 17:44:50|2018-10-19 17:44:50|2018-10-19 17:44:50|2018-10-19 17:44:50|7 |0 |03a3724203d4f758dc0a567cb58dab17|20:54:30.419 [Test worker] DEBUG org.jooq.tools.jdbc.MockFileDatabase - : +------------------------------------+-------+----------+---------+---------+------------+---------+------------------------------------+-------------------+-------------------+-------------------+-------------------+---------+----------+--------------------------------+20:54:30.633 [Test worker] INFO org.jooq.Constants - @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ @@ @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ @@@@@@@@@@@@@@@@@@@@@@@@@@ @@ @@ @@@@@@@@@@@@@@@@@@@@ @@@@ @@ @@ @@@@@@@@@@@@@@@@@@@@ @@ @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ @@ @@@@@@@@@@@@@@@@@@@@ @@ @@ @@@@ @@@@@@@@@@@@@@@@@@@@ @@ @@ @@@@ @@@@@@@@@@@@@@@@@@@@ @@ @ @ @@@@@@@@@@@@@@@@@@@@ @@ @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ Thank you for using jOOQ 3.11.8 20:54:30.656 [Test worker] DEBUG org.jooq.tools.LoggerListener - Executing query : select "latest_deck_version"."id", "latest_deck_version"."deleted", "latest_deck_version"."foreign_id", "latest_deck_version"."deck_type", "latest_deck_version"."title", "latest_deck_version"."confidential", "latest_deck_version"."certified", "latest_deck_version"."deck_id", "latest_deck_version"."original_created", "latest_deck_version"."original_updated", "latest_deck_version"."created", "latest_deck_version"."updated", "latest_deck_version"."num_cards", "latest_deck_version"."num_images", "latest_deck_version"."edition" from "public"."latest_deck_version"(?::uuid[])20:54:30.663 [Test worker] DEBUG org.jooq.tools.LoggerListener - -> with bind values : select "latest_deck_version"."id", "latest_deck_version"."deleted", "latest_deck_version"."foreign_id", "latest_deck_version"."deck_type", "latest_deck_version"."title", "latest_deck_version"."confidential", "latest_deck_version"."certified", "latest_deck_version"."deck_id", "latest_deck_version"."original_created", "latest_deck_version"."original_updated", "latest_deck_version"."created", "latest_deck_version"."updated", "latest_deck_version"."num_cards", "latest_deck_version"."num_images", "latest_deck_version"."edition" from "public"."latest_deck_version"(cast('{"efb2d576-f2d3-4a8e-9715-dc95fdd14701","816d681e-18ca-4b61-938c-401b22c714f5"}' as uuid[]))20:54:30.697 [Test worker] DEBUG org.jooq.tools.LoggerListener - Exception org.jooq.exception.DataAccessException: SQL [select "latest_deck_version"."id", "latest_deck_version"."deleted", "latest_deck_version"."foreign_id", "latest_deck_version"."deck_type", "latest_deck_version"."title", "latest_deck_version"."confidential", "latest_deck_version"."certified", "latest_deck_version"."deck_id", "latest_deck_version"."original_created", "latest_deck_version"."original_updated", "latest_deck_version"."created", "latest_deck_version"."updated", "latest_deck_version"."num_cards", "latest_deck_version"."num_images", "latest_deck_version"."edition" from "public"."latest_deck_version"(?::uuid[])]; Invalid SQL: select "latest_deck_version"."id", "latest_deck_version"."deleted", "latest_deck_version"."foreign_id", "latest_deck_version"."deck_type", "latest_deck_version"."title", "latest_deck_version"."confidential", "latest_deck_version"."certified", "latest_deck_version"."deck_id", "latest_deck_version"."original_created", "latest_deck_version"."original_updated", "latest_deck_version"."created", "latest_deck_version"."updated", "latest_deck_version"."num_cards", "latest_deck_version"."num_images", "latest_deck_version"."edition" from "public"."latest_deck_version"(?::uuid[]) at org.jooq_3.11.8.POSTGRES_10.debug(Unknown Source) at org.jooq.impl.Tools.translate(Tools.java:2384) at org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:822) at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:364) at org.jooq.impl.AbstractResultQuery.fetchLazy(AbstractResultQuery.java:393) at org.jooq.impl.AbstractResultQuery.fetchLazy(AbstractResultQuery.java:380) at org.jooq.impl.AbstractResultQuery.fetchResultSet(AbstractResultQuery.java:329) at org.jooq.impl.SelectImpl.fetchResultSet(SelectImpl.java:2705) at com.chegg.deck.service.dao.DeckRepository.getDecksWithoutCardByIds(DeckRepository.java:103) at com.chegg.deck.service.dao.DeckWithoutCardsByIdsTest.testGetDecksWithoutCardByIds(DeckWithoutCardsByIdsTest.java:38) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.base/java.lang.reflect.Method.invoke(Method.java:564) at org.junit.platform.commons.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:515) at org.junit.jupiter.engine.execution.ExecutableInvoker.invoke(ExecutableInvoker.java:115) at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.lambda$invokeTestMethod$6(TestMethodTestDescriptor.java:170) at org.junit.jupiter.engine.execution.ThrowableCollector.execute(ThrowableCollector.java:40) at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.invokeTestMethod(TestMethodTestDescriptor.java:166) at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:113) at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:58) at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$5(NodeTestTask.java:105) at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:72) at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:95) at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:71) at java.base/java.util.ArrayList.forEach(ArrayList.java:1378) at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:38) at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$5(NodeTestTask.java:110) at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:72) at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:95) at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:71) at java.base/java.util.ArrayList.forEach(ArrayList.java:1378) at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:38) at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$5(NodeTestTask.java:110) at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:72) at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:95) at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:71) at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.submit(SameThreadHierarchicalTestExecutorService.java:32) at org.junit.platform.engine.support.hierarchical.HierarchicalTestExecutor.execute(HierarchicalTestExecutor.java:57) at org.junit.platform.engine.support.hierarchical.HierarchicalTestEngine.execute(HierarchicalTestEngine.java:51) at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:170) at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:154) at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:90) at org.gradle.api.internal.tasks.testing.junitplatform.JUnitPlatformTestClassProcessor$CollectAllTestClassesExecutor.processAllTestClasses(JUnitPlatformTestClassProcessor.java:92) at org.gradle.api.internal.tasks.testing.junitplatform.JUnitPlatformTestClassProcessor$CollectAllTestClassesExecutor.access$100(JUnitPlatformTestClassProcessor.java:77) at org.gradle.api.internal.tasks.testing.junitplatform.JUnitPlatformTestClassProcessor.stop(JUnitPlatformTestClassProcessor.java:73) at org.gradle.api.internal.tasks.testing.SuiteTestClassProcessor.stop(SuiteTestClassProcessor.java:61) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.base/java.lang.reflect.Method.invoke(Method.java:564) at org.gradle.internal.dispatch.ReflectionDispatch.dispatch(ReflectionDispatch.java:35) at org.gradle.internal.dispatch.ReflectionDispatch.dispatch(ReflectionDispatch.java:24) at org.gradle.internal.dispatch.ContextClassLoaderDispatch.dispatch(ContextClassLoaderDispatch.java:32) at org.gradle.internal.dispatch.ProxyDispatchAdapter$DispatchingInvocationHandler.invoke(ProxyDispatchAdapter.java:93) at com.sun.proxy.$Proxy2.stop(Unknown Source) at org.gradle.api.internal.tasks.testing.worker.TestWorker.stop(TestWorker.java:131) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.base/java.lang.reflect.Method.invoke(Method.java:564) at org.gradle.internal.dispatch.ReflectionDispatch.dispatch(ReflectionDispatch.java:35) at org.gradle.internal.dispatch.ReflectionDispatch.dispatch(ReflectionDispatch.java:24) at org.gradle.internal.remote.internal.hub.MessageHubBackedObjectConnection$DispatchWrapper.dispatch(MessageHubBackedObjectConnection.java:155) at org.gradle.internal.remote.internal.hub.MessageHubBackedObjectConnection$DispatchWrapper.dispatch(MessageHubBackedObjectConnection.java:137) at org.gradle.internal.remote.internal.hub.MessageHub$Handler.run(MessageHub.java:404) at org.gradle.internal.concurrent.ExecutorPolicy$CatchAndRecordFailures.onExecute(ExecutorPolicy.java:63) at org.gradle.internal.concurrent.ManagedExecutorImpl$1.run(ManagedExecutorImpl.java:46) at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1135) at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635) at org.gradle.internal.concurrent.ThreadFactoryImpl$ManagedThreadRunnable.run(ThreadFactoryImpl.java:55) at java.base/java.lang.Thread.run(Thread.java:844)Caused by: java.sql.SQLException: Invalid SQL: select "latest_deck_version"."id", "latest_deck_version"."deleted", "latest_deck_version"."foreign_id", "latest_deck_version"."deck_type", "latest_deck_version"."title", "latest_deck_version"."confidential", "latest_deck_version"."certified", "latest_deck_version"."deck_id", "latest_deck_version"."original_created", "latest_deck_version"."original_updated", "latest_deck_version"."created", "latest_deck_version"."updated", "latest_deck_version"."num_cards", "latest_deck_version"."num_images", "latest_deck_version"."edition" from "public"."latest_deck_version"(?::uuid[]) at org.jooq.tools.jdbc.MockFileDatabase.execute(MockFileDatabase.java:364) at org.jooq.tools.jdbc.MockStatement.execute0(MockStatement.java:187) at org.jooq.tools.jdbc.MockStatement.execute(MockStatement.java:250) at org.jooq.tools.jdbc.MockStatement.execute(MockStatement.java:245) at org.jooq.tools.jdbc.DefaultPreparedStatement.execute(DefaultPreparedStatement.java:209) at org.jooq.impl.Tools.executeStatementAndGetFirstResultSet(Tools.java:3494) at org.jooq.impl.AbstractResultQuery.execute(AbstractResultQuery.java:268) at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:350) ... 68 common frames omittedI am not able to figure out what really the cause of this error.
Basically in my experience, any error it just throw java.sql.SQLException: Invalid SQL.
I am not able to figure out what really the cause of this error. Can someone help me understand what could be the issue or is there a way i can get more info from JOOQ about the error ?
I am attaching my Mock data file which i am using to test my DAO method.
Thanks,
Deba
Knut Wannheden
Aug 23, 2019, 5:31:02 AM8/23/19
to jooq...@googlegroups.com
Hi Deba,
I tested your query using jOOQ 3.11.11 and could not find any problem. Can you show your actual test code? Even better would be a MCVE (see https://github.com/jOOQ/jOOQ-mcve).
Regards,
Knut
--
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.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/097bb76b-098e-411e-b639-1d6b455fe223%40googlegroups.com.
Debapriya Patra
Aug 23, 2019, 9:24:46 AM8/23/19
to jOOQ User Group
Hi Kunt,
Test Method :
Thanks,
I am using JOOQ 3.11.8.
DAO Method:
public List<DeckWithoutCard> getDecksWithoutCardByIds(List<UUID> deckIds){
UUID[] ids = deckIds.stream().toArray(UUID[]::new);
LatestDeckVersion latestDeckVersion = Routines.latestDeckVersion(ids);
SelectWhereStep<LatestDeckVersionRecord> latestDeckVersionRecords = dsl.selectFrom(latestDeckVersion);
ResultSet rs = latestDeckVersionRecords.fetchResultSet();
return transformToDeckWithoutCardList(rs);
}
LatestDeckVersion is basically a function written in psql.
create or replace function public.latest_deck_version(deck_id_array uuid[])
returns table(
id uuid,
deleted boolean,
foreign_id text,
deck_type deck_type,
title text,
confidential boolean,
certified boolean,
deck_id uuid,
original_created timestamp without time zone,
original_updated timestamp without time zone,
created timestamp with time zone, updated
timestamp with time zone,
num_cards bigint,
num_images bigint,
edition text)
language sql
as $function$
with
cte0 as (
select
t.idx,
t.id
from unnest($1) with ordinality as t(id, idx)),
cte1 as (
select
lower(sys_period) as t,
false as deleted,
d.id,
foreign_id,
title,
deck_type,
confidential,
certified,
deck_id,
original_created,
original_updated,
coalesce((select min(lower(sys_period)) from core.deck_version where id = d.id), lower(sys_period)) as created,
lower(sys_period) as updated,
(select count (1) from core.card_deck where deck_id = d.id) as num_cards,
(select image_count from deck_image_count where deck_id = d.id) as num_images,
(select edition from deck_edition where deck_id = d.id)
from core.deck d
join cte0 on cte0.id::uuid = d.id
union all
select
lower(sys_period) as t,
true as deleted,
d.id,
foreign_id,
title,
deck_type,
confidential,
certified,
deck_id,
original_created,
original_updated,
(select min(lower(sys_period)) from core.deck_version where id = d.id) as created,
(select max(upper(sys_period)) from core.deck_version where id = d.id) as updated,
(select count (1) as count from core.card_deck where deck_id = d.id) num_cards,
(select image_count from deck_image_count where deck_id = d.id) num_images,
(select edition from deck_edition where deck_id = d.id)
from core.deck_version d
join cte0 on cte0.id::uuid = d.id),
cte2 as (
select *
from cte1
order by id, t desc),
cte3 as (
select distinct on (id)
cte2.id,
deleted,
foreign_id,
deck_type,
title,
confidential,
certified,
deck_id,
original_created,
original_updated,
created,
updated,
num_cards,
num_images,
edition
from cte2)
select
cte3.id,
deleted,
foreign_id,
deck_type,
title,
confidential,
certified,
deck_id,
original_created,
original_updated,
created,
updated,
num_cards,
num_images,
edition
from cte3
join cte0 on cte0.id::uuid = cte3.id
order by idx asc;
$function$;
Test Method :
import com.chegg.deck.service.model.DeckWithoutCard;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import org.mockito.InjectMocks;
import org.mockito.MockitoAnnotations;
import java.io.IOException;
import java.net.URISyntaxException;
import java.util.Arrays;
import java.util.List;
import java.util.UUID;
import static org.junit.jupiter.api.Assertions.assertEquals;
import static org.junit.jupiter.api.Assertions.assertNotNull;
public class DeckWithoutCardsByIdsTest extends JooqBaseTest {
@InjectMocks
private DeckRepository deckRepository;
@Override
public String getResourcePath(){
return "/db/get_deck_history.txt";
}
@BeforeEach
public void init() throws IOException, URISyntaxException {
MockitoAnnotations.initMocks(this);
super.init();
deckRepository.setDsl(dslContext);
}
@Test
public void testGetDecksWithoutCardByIds(){
List<UUID> deckIds = Arrays.asList(UUID.fromString("efb2d576-f2d3-4a8e-9715-dc95fdd14701"), UUID.fromString("816d681e-18ca-4b61-938c-401b22c714f5"));
List<DeckWithoutCard> decksWithoutCardByIds = deckRepository.getDecksWithoutCardByIds(deckIds);
assertNotNull(decksWithoutCardByIds);
assertEquals(2, decksWithoutCardByIds.size());
}
}
Thanks,
Deba
To unsubscribe from this group and stop receiving emails from it, send an email to jooq...@googlegroups.com.
Lukas Eder
Aug 26, 2019, 4:13:47 AM8/26/19
to jOOQ User Group
Hi Deba,
The "Invalid SQL: " message happens because MockFileDatabase does not find any matching SQL statement in your file that would match the one you ran. The SQL string that you (via jOOQ) sent to the MockFileDatabase is printed there, now you will have to check your file for the statement you intended to match here.
In your file, you used inline values / constant literals: cast('{"efb2d576-f2d3-4a8e-9715-dc95fdd14701","816d681e-18ca-4b61-938c-401b22c714f5"}' as uuid[])
... but what's being sent to the server is a bind variable placeholder: ?::uuid[]
I have created a feature request to improve this error message and explain the possible mistakes that could have happened:
I hope this helps,
Lukas
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/d0006fac-506f-475d-95ff-d14c893dd0cd%40googlegroups.com.
Reply all
Reply to author
Forward
0 new messages