DBFit Postgres - "<" Less than signs causing error in |Query| or |InspectQuery| but ">" works fine?

73 views
Skip to first unread message

Brian Wong

unread,
Sep 29, 2017, 2:26:59 AM9/29/17
to dbfit
Hi,
Apologies if this has been raised before and answered, as I couldn't find it in the search in this group.

I created this query to illustrate the point

!|InspectQuery|!-
SELECT
CASE WHEN 1 < 2 THEN TRUE ELSE FALSE END AS TEST_RESULT
FROM
SDS.FCT_CI_RECAL_FIN_PRT_BASE_SNAPSHOT
WHERE SNAPSHOT_DATE_KEY >= 20150701
limit 1;
-!|

Error message:
java.sql.SQLException: [Amazon](500310) Invalid operation: syntax error at or near "limit";
at com.amazon.redshift.client.messages.inbound.ErrorResponse.toErrorException(ErrorResponse.java:1830)
at com.amazon.redshift.client.PGMessagingContext.handleErrorResponse(PGMessagingContext.java:804)
at com.amazon.redshift.client.PGMessagingContext.handleMessage(PGMessagingContext.java:642)
at com.amazon.jdbc.communications.InboundMessagesPipeline.getNextMessageOfClass(InboundMessagesPipeline.java:312)
at com.amazon.redshift.client.PGMessagingContext.doMoveToNextClass(PGMessagingContext.java:1062)
at com.amazon.redshift.client.PGMessagingContext.getParameterDescription(PGMessagingContext.java:978)
at com.amazon.redshift.client.PGClient.prepareStatement(PGClient.java:1844)
at com.amazon.redshift.dataengine.PGQueryExecutor.(PGQueryExecutor.java:106)
at com.amazon.redshift.dataengine.PGDataEngine.prepare(PGDataEngine.java:211)
at com.amazon.jdbc.common.SPreparedStatement.(Unknown Source)
at com.amazon.jdbc.jdbc41.S41PreparedStatement.(Unknown Source)
at com.amazon.redshift.core.jdbc41.PGJDBC41PreparedStatement.(PGJDBC41PreparedStatement.java:49)
at com.amazon.redshift.core.jdbc41.PGJDBC41ObjectFactory.createPreparedStatement(PGJDBC41ObjectFactory.java:119)
at com.amazon.jdbc.common.SConnection.prepareStatement(Unknown Source)
at dbfit.api.AbstractDbEnvironment.createStatementWithBoundFixtureSymbols(AbstractDbEnvironment.java:108)
at dbfit.fixture.Inspect.inspectQuery(Inspect.java:79)
at dbfit.fixture.Inspect.doTable(Inspect.java:47)
at fitlibrary.traverse.AlienTraverseHandler.doTable(AlienTraverseHandler.java:21)
at fitlibrary.traverse.workflow.DoTraverseInterpreter.interpretWholeTable(DoTraverseInterpreter.java:104)
at fitlibrary.traverse.workflow.DoTraverseInterpreter.interpretWholeTable(DoTraverseInterpreter.java:89)
at fitlibrary.DoFixture.interpretWholeTable(DoFixture.java:73)
at fitlibrary.suite.InFlowPageRunner.run(InFlowPageRunner.java:27)
at fitlibrary.DoFixture.interpretTables(DoFixture.java:42)
at dbfit.DatabaseTest.interpretTables(DatabaseTest.java:26)
at fit.Fixture.doTables(Fixture.java:81)
at fit.FitServer.process(FitServer.java:81)
at fit.FitServer.run(FitServer.java:56)
Caused by: com.amazon.support.exceptions.ErrorException: [Amazon](500310) Invalid operation: syntax error at or near "limit";
... 27 more

If I switch to greater than ">" it runs fine:

InspectQuery SELECT CASE WHEN 2 > 1 THEN TRUE ELSE FALSE END AS TEST_RESULT FROM SDS.FCT_CI_RECAL_FIN_PRT_BASE_SNAPSHOT WHERE SNAPSHOT_DATE_KEY >= 20150701 limit 1;
test_result
true

Anyone can give me some insight why this is happening? and how to resolve this?
Thanks!

Yavor Nikolov

unread,
Sep 29, 2017, 4:09:06 AM9/29/17
to dbfit
There is something in the underlying libraries which treats <, > as tag delimiters and causes problems. You should be able to workaround the problem by not wrapping your query in !- -! (so you should fit it in single line).

A separate not-related note: maybe you don't need to terminate your query with ";" at the end.

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

Brian Wong

unread,
Oct 3, 2017, 3:22:15 AM10/3/17
to dbfit
Thanks Yavor.

I will test it without wrapping in !- -! but it does make the test cases really hard to read. 
Is there a way to escape the tags?

I'm using the 
To unsubscribe from this group and stop receiving emails from it, send an email to dbfit+un...@googlegroups.com.

Yavor Nikolov

unread,
Oct 3, 2017, 3:56:28 AM10/3/17
to dbfit
You may try using !- -! in DbFit standalone mode. I'm not quite sure but I think it worked in that way. There are some other differences and limitations in standalone mode though.

Basically, DbFit is using FitLibrary which has an embedded old version of Fit doing this processing of tags. FitNesse itself has a newer version of Fit where we managed to implement some fix for <,>. However in Flow Mode seems that the parsing of commands is still using the old embedded Fit.

To unsubscribe from this group and stop receiving emails from it, send an email to dbfit+unsubscribe@googlegroups.com.
Reply all
Reply to author
Forward
0 new messages