Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

empty update statement causes SQL Syntax error #1241

Closed
AriaMoradi opened this issue May 14, 2021 · 2 comments
Closed

empty update statement causes SQL Syntax error #1241

AriaMoradi opened this issue May 14, 2021 · 2 comments

Comments

@AriaMoradi
Copy link

I have this Table:

object ChapterTable : IntIdTable() {
    val url = varchar("url", 2048)
    val name = varchar("name", 512)
    val date_upload = long("date_upload").default(0)
    val chapter_number = float("chapter_number").default(-1f)
    val scanlator = varchar("scanlator", 128).nullable()

    val isRead = bool("read").default(false)
    val isBookmarked = bool("bookmark").default(false)
    val lastPageRead = integer("last_page_read").default(0)

    // index is reserved by a function
    val chapterIndex = integer("index")

    val manga = reference("manga", MangaTable)
}

and I have some records with chapterIndex from 1 to N

calling this with H2 fails

ChapterTable.update({ (ChapterTable.manga eq mangaId) and (ChapterTable.chapterIndex less chapterIndex) }) {
    it[ChapterTable.isRead] = markPrevRead
}

here's the stack trace:

17:25:12.234 [qtp1446291553-39] WARN  io.javalin.Javalin - Uncaught exception
org.jetbrains.exposed.exceptions.ExposedSQLException: org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "UPDATE CHAPTER SET  WHERE[*] (CHAPTER.MANGA = ?) AND (CHAPTER.INDEX = ?)"; expected "identifier"; SQL statement:
UPDATE CHAPTER SET  WHERE (CHAPTER.MANGA = ?) AND (CHAPTER.INDEX = ?) [42001-200]
	at org.jetbrains.exposed.sql.statements.Statement.executeIn$exposed_core(Statement.kt:49)
	at org.jetbrains.exposed.sql.Transaction.exec(Transaction.kt:129)
	at org.jetbrains.exposed.sql.Transaction.exec(Transaction.kt:115)
	at org.jetbrains.exposed.sql.statements.Statement.execute(Statement.kt:28)
	at org.jetbrains.exposed.sql.QueriesKt.update(Queries.kt:178)
	at org.jetbrains.exposed.sql.QueriesKt.update$default(Queries.kt:175)
	at ir.armor.tachidesk.impl.Chapter$modifyChapter$1.invoke(Chapter.kt:170)
	at ir.armor.tachidesk.impl.Chapter$modifyChapter$1.invoke(Chapter.kt:27)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt$inTopLevelTransaction$1.invoke(ThreadLocalTransactionManager.kt:173)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt$inTopLevelTransaction$2.invoke(ThreadLocalTransactionManager.kt:194)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.keepAndRestoreTransactionRefAfterRun(ThreadLocalTransactionManager.kt:202)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.inTopLevelTransaction(ThreadLocalTransactionManager.kt:193)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt$transaction$1.invoke(ThreadLocalTransactionManager.kt:151)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.keepAndRestoreTransactionRefAfterRun(ThreadLocalTransactionManager.kt:202)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.transaction(ThreadLocalTransactionManager.kt:123)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.transaction(ThreadLocalTransactionManager.kt:121)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.transaction$default(ThreadLocalTransactionManager.kt:120)
	at ir.armor.tachidesk.impl.Chapter.modifyChapter(Chapter.kt:169)
	at ir.armor.tachidesk.server.JavalinSetup$javalinSetup$22.handle(JavalinSetup.kt:277)
	at io.javalin.core.security.SecurityUtil.noopAccessManager(SecurityUtil.kt:23)
	at io.javalin.http.JavalinServlet$addHandler$protectedHandler$1.handle(JavalinServlet.kt:128)
	at io.javalin.http.JavalinServlet$service$2$1.invoke(JavalinServlet.kt:45)
	at io.javalin.http.JavalinServlet$service$2$1.invoke(JavalinServlet.kt:24)
	at io.javalin.http.JavalinServlet$service$1.invoke(JavalinServlet.kt:136)
	at io.javalin.http.JavalinServlet$service$2.invoke(JavalinServlet.kt:40)
	at io.javalin.http.JavalinServlet.service(JavalinServlet.kt:81)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
	at io.javalin.websocket.JavalinWsServlet.service(JavalinWsServlet.kt:51)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
	at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:791)
	at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:550)
	at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:233)
	at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:1624)
	at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:233)
	at io.javalin.core.JavalinServer$start$wsAndHttpHandler$1.doHandle(JavalinServer.kt:49)
	at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:188)
	at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:501)
	at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:1594)
	at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:186)
	at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1350)
	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)
	at org.eclipse.jetty.server.handler.StatisticsHandler.handle(StatisticsHandler.java:179)
	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127)
	at org.eclipse.jetty.server.Server.handle(Server.java:516)
	at org.eclipse.jetty.server.HttpChannel.lambda$handle$1(HttpChannel.java:388)
	at org.eclipse.jetty.server.HttpChannel.dispatch(HttpChannel.java:633)
	at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:380)
	at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:279)
	at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:311)
	at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:105)
	at org.eclipse.jetty.io.ChannelEndPoint$1.run(ChannelEndPoint.java:104)
	at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.runTask(EatWhatYouKill.java:336)
	at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.doProduce(EatWhatYouKill.java:313)
	at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.tryProduce(EatWhatYouKill.java:171)
	at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.run(EatWhatYouKill.java:129)
	at org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:383)
	at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:882)
	at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.run(QueuedThreadPool.java:1036)
	at java.base/java.lang.Thread.run(Thread.java:834)
Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "UPDATE CHAPTER SET  WHERE[*] (CHAPTER.MANGA = ?) AND (CHAPTER.INDEX = ?)"; expected "identifier"; SQL statement:
UPDATE CHAPTER SET  WHERE (CHAPTER.MANGA = ?) AND (CHAPTER.INDEX = ?) [42001-200]
	at org.h2.message.DbException.getJdbcSQLException(DbException.java:453)
	at org.h2.message.DbException.getJdbcSQLException(DbException.java:429)
	at org.h2.message.DbException.getSyntaxError(DbException.java:243)
	at org.h2.command.Parser.readColumnIdentifier(Parser.java:4976)
	at org.h2.command.Parser.readTableColumn(Parser.java:1236)
	at org.h2.command.Parser.parseUpdateSetClause(Parser.java:1325)
	at org.h2.command.Parser.parseUpdate(Parser.java:1290)
	at org.h2.command.Parser.parsePrepared(Parser.java:1005)
	at org.h2.command.Parser.parse(Parser.java:843)
	at org.h2.command.Parser.parse(Parser.java:815)
	at org.h2.command.Parser.prepareCommand(Parser.java:738)
	at org.h2.engine.Session.prepareLocal(Session.java:657)
	at org.h2.engine.Session.prepareCommand(Session.java:595)
	at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1235)
	at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:76)
	at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:1154)
	at org.jetbrains.exposed.sql.statements.jdbc.JdbcConnectionImpl.prepareStatement(JdbcConnectionImpl.kt:58)
	at org.jetbrains.exposed.sql.statements.Statement.prepared(Statement.kt:24)
	at org.jetbrains.exposed.sql.statements.Statement.executeIn$exposed_core(Statement.kt:47)
	... 58 common frames omitted

here's my code for reference: https://github.com/Suwayomi/Tachidesk/blob/da6a95309902a701b5aa24d8dc8da00855885477/server/src/main/kotlin/ir/armor/tachidesk/impl/Chapter.kt#L183
@AriaMoradi AriaMoradi changed the title Update faling Update failing where statement using less than on H2 May 14, 2021
@AriaMoradi
Copy link
Author

Here's what I think is causing the error:

in my error instance isRead and isBookmarked and lastPageRead are all null, so the first update block is empty.
I think the first update statement being empty generates a syntactically wrong sql in the second update.

       transaction {
            ChapterTable.update({ (ChapterTable.manga eq mangaId) and (ChapterTable.chapterIndex eq chapterIndex) }) { update ->
                isRead?.also {
                    update[ChapterTable.isRead] = it
                }
                isBookmarked?.also {
                    update[ChapterTable.isBookmarked] = it
                }
                lastPageRead?.also {
                    update[ChapterTable.lastPageRead] = it
                }
            }

            markPrevRead?.let {
                ChapterTable.update({ (ChapterTable.manga eq mangaId) and (ChapterTable.chapterIndex less chapterIndex) }) {
                    it[ChapterTable.isRead] = markPrevRead
                }
            }
        }
    }

@AriaMoradi
Copy link
Author

AriaMoradi commented May 14, 2021 •
edited
Loading 

        transaction {
            if (listOf(isRead, isBookmarked, lastPageRead).any { it != null }) {
                ChapterTable.update({ (ChapterTable.manga eq mangaId) and (ChapterTable.chapterIndex eq chapterIndex) }) { update ->
                    isRead?.also {
                        update[ChapterTable.isRead] = it
                    }
                    isBookmarked?.also {
                        update[ChapterTable.isBookmarked] = it
                    }
                    lastPageRead?.also {
                        update[ChapterTable.lastPageRead] = it
                    }
                }
            }

            markPrevRead?.let {
                ChapterTable.update({ (ChapterTable.manga eq mangaId) and (ChapterTable.chapterIndex less chapterIndex) }) {
                    it[ChapterTable.isRead] = markPrevRead
                }
            }
        }

This fixed the error.

@AriaMoradi AriaMoradi changed the title Update failing where statement using less than on H2 empty update statement causes SQL Syntax error May 14, 2021
Tapac added a commit that referenced this issue Aug 17, 2021
@Tapac
empty update statement causes SQL Syntax error #1241
bd7f3b3
@Tapac Tapac closed this as completed Aug 17, 2021
SchweinchenFuntik pushed a commit to SchweinchenFuntik/Exposed that referenced this issue Oct 23, 2021
@Tapac @SchweinchenFuntik
empty update statement causes SQL Syntax error JetBrains#1241
5677c69
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@Tapac @AriaMoradi