Bug in SQLJet Parser in combination with triggers? (sqljet 1.1.18)

[matze...@cable.vol.at]

Hey guys,

I think I am currently facing a bug in the SQLParser of SqlJet (newest version 1.1.18) as I am trying to create a trigger with an obviously correct creation query.
Our problem is that on our embedded target system the SELECT COUNT(*) stuff takes an enormous amount of time with tables as large
as one million rows.

We figured out a workaround by maintaining the number of rows by ourselves. Thus we want to use a trigger which decrements the number
of rows on every delete. But when testing this on my PC (using sqljet) I ran into the following parser error:

Exception in thread "main" org.tmatesoft.sqljet.core.internal.lang.SqlJetParserException: [line 1:123] mismatched input 'WHERE' expecting DOT
CREATE TRIGGER "trgDataDel" AFTER DELETE ON "tblData" BEGIN UPDATE "tblStat" SET "value" = ((SELECT "value" FROM "tblStat" WHERE "key"='NR') - 1) WHERE "key"='NR'; END;
    at org.tmatesoft.sqljet.core.internal.lang.SqlParser.displayRecognitionError(SqlParser.java:305)
    at org.antlr.runtime.BaseRecognizer.reportError(BaseRecognizer.java:186)
    at org.tmatesoft.sqljet.core.internal.lang.SqlParser.single_source(SqlParser.java:9739)
    at org.tmatesoft.sqljet.core.internal.lang.SqlParser.join_source(SqlParser.java:9190)
    at org.tmatesoft.sqljet.core.internal.lang.SqlParser.select_core(SqlParser.java:8642)
    at org.tmatesoft.sqljet.core.internal.lang.SqlParser.select_list(SqlParser.java:8273)
    at org.tmatesoft.sqljet.core.internal.lang.SqlParser.select_stmt(SqlParser.java:7976)
    at org.tmatesoft.sqljet.core.internal.lang.SqlParser.atom_expr(SqlParser.java:4685)
    at org.tmatesoft.sqljet.core.internal.lang.SqlParser.unary_subexpr(SqlParser.java:4076)
    at org.tmatesoft.sqljet.core.internal.lang.SqlParser.con_subexpr(SqlParser.java:3901)
    at org.tmatesoft.sqljet.core.internal.lang.SqlParser.mul_subexpr(SqlParser.java:3615)
    at org.tmatesoft.sqljet.core.internal.lang.SqlParser.add_subexpr(SqlParser.java:3506)
    at org.tmatesoft.sqljet.core.internal.lang.SqlParser.bit_subexpr(SqlParser.java:3397)
    at org.tmatesoft.sqljet.core.internal.lang.SqlParser.neq_subexpr(SqlParser.java:3288)
    at org.tmatesoft.sqljet.core.internal.lang.SqlParser.eq_subexpr(SqlParser.java:3179)
    at org.tmatesoft.sqljet.core.internal.lang.SqlParser.and_subexpr(SqlParser.java:1946)
    at org.tmatesoft.sqljet.core.internal.lang.SqlParser.or_subexpr(SqlParser.java:1855)
    at org.tmatesoft.sqljet.core.internal.lang.SqlParser.expr(SqlParser.java:1762)
    at org.tmatesoft.sqljet.core.internal.lang.SqlParser.atom_expr(SqlParser.java:4578)
    at org.tmatesoft.sqljet.core.internal.lang.SqlParser.unary_subexpr(SqlParser.java:4076)
    at org.tmatesoft.sqljet.core.internal.lang.SqlParser.con_subexpr(SqlParser.java:3901)
    at org.tmatesoft.sqljet.core.internal.lang.SqlParser.mul_subexpr(SqlParser.java:3615)
    at org.tmatesoft.sqljet.core.internal.lang.SqlParser.add_subexpr(SqlParser.java:3506)
    at org.tmatesoft.sqljet.core.internal.lang.SqlParser.bit_subexpr(SqlParser.java:3397)
    at org.tmatesoft.sqljet.core.internal.lang.SqlParser.neq_subexpr(SqlParser.java:3288)
    at org.tmatesoft.sqljet.core.internal.lang.SqlParser.eq_subexpr(SqlParser.java:3179)
    at org.tmatesoft.sqljet.core.internal.lang.SqlParser.and_subexpr(SqlParser.java:1946)
    at org.tmatesoft.sqljet.core.internal.lang.SqlParser.or_subexpr(SqlParser.java:1855)
    at org.tmatesoft.sqljet.core.internal.lang.SqlParser.expr(SqlParser.java:1762)
    at org.tmatesoft.sqljet.core.internal.lang.SqlParser.update_set(SqlParser.java:11028)
    at org.tmatesoft.sqljet.core.internal.lang.SqlParser.update_stmt(SqlParser.java:10854)
    at org.tmatesoft.sqljet.core.internal.lang.SqlParser.create_trigger_stmt(SqlParser.java:18848)
    at org.tmatesoft.sqljet.core.internal.schema.SqlJetSchema.parseTrigger(SqlJetSchema.java:434)
    at org.tmatesoft.sqljet.core.internal.schema.SqlJetSchema.createTriggerSafe(SqlJetSchema.java:1597)
    at org.tmatesoft.sqljet.core.internal.schema.SqlJetSchema.createTrigger(SqlJetSchema.java:1590)
    at org.tmatesoft.sqljet.core.table.SqlJetDb$14.run(SqlJetDb.java:412)
    at org.tmatesoft.sqljet.core.table.SqlJetDb$3.run(SqlJetDb.java:240)
    at org.tmatesoft.sqljet.core.table.engine.SqlJetEngine$12.runSynchronized(SqlJetEngine.java:533)
    at org.tmatesoft.sqljet.core.table.engine.SqlJetEngine.runSynchronized(SqlJetEngine.java:217)
    at org.tmatesoft.sqljet.core.table.engine.SqlJetEngine.runEngineTransaction(SqlJetEngine.java:529)
    at org.tmatesoft.sqljet.core.table.SqlJetDb.runTransaction(SqlJetDb.java:238)
    at org.tmatesoft.sqljet.core.table.SqlJetDb.runWriteTransaction(SqlJetDb.java:211)
    at org.tmatesoft.sqljet.core.table.SqlJetDb.createTrigger(SqlJetDb.java:410)
    at TriggerBug.main(TriggerBug.java:37)
Caused by: MismatchedTokenException(235!=67)
    at org.tmatesoft.sqljet.core.internal.lang.SqlParser.recoverFromMismatchedToken(SqlParser.java:288)
    at org.antlr.runtime.BaseRecognizer.match(BaseRecognizer.java:115)
    at org.tmatesoft.sqljet.core.internal.lang.SqlParser.single_source(SqlParser.java:9417)
    ... 41 more

If I entered the queries into an SQLite browser it all works fine. This is why I'm quite sure that it is syntactically correct.

What do you think? Should I file a bug report? Any suggestions for a workaround in the meantime?

Cheers,
Matthias

[Sergey Scherbina]

Thank you, Matthias.

--
With regards,
Sergey Scherbina

2013/11/25 <matze...@cable.vol.at>

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

[matze...@cable.vol.at]

Hi Sergey,

no problem. I really enjoyed using the rather "high-level API" you've created for SQLite.
Unfortunately the thing with the triggers does not work at all.

I now have managed to create a much simpler trigger, like this one below, but I had to find out
that sqljet does not "execute" the triggers.

    private static final String TRG_DATA_DEL = "CREATE TRIGGER \"trgDataDel\"" +
            " AFTER DELETE ON \"tblData\" BEGIN" +
                " UPDATE \"tblStat\"" +
                    " SET value=5;" +
            " END;";

This trigger should be executed every time i delete one row from tblData, but it never runs.
Looking at the source code it seems that this feature is not implemented at all. What would you say?

Regards,
Matthias

[Sergey Scherbina]

Hi, Matthias !

It's by design :) So far SqlJet isn't supposed to execute SQL in runtime, so triggers also aren't executed.

It's not very good of course, but we have just limited SqlJet for good SQLite data format implementation.

SQL run-time implementation so far is out of scope for our tasks, but of course that would be good feature at all.

We just want avoid complicated implementation of SQL interpretator in favor of simple API which is more predictable in run-time.

For our tasks API is more efficient and we are more confident in API implementation.

SQL interpretator is not so easy task and could cause a lot of new bugs so we have decided to limit SQLJet to much simpler API.

That causes additional work for manual translation of SQL-statements to API calls but for us it is more preferable so far.

We have the will to implement full SQL support in some future, but it's definitely not yet in nearest plans.

--
With regards,
Sergey Scherbina

2013/11/26 <matze...@cable.vol.at>