我有一个（复杂的）查询，该查询使用子选择和窗口函数（SQLite确实支持：https://www.sqlitetutorial.net/sqlite-window-functions/）。在数据库工具（在本例中为dbeaver）中运行此查询时，此查询正确执行，在Java中运行时，出现SQLite错误： [SQLITE_ERROR] SQL error or missing database (near "(": syntax error)

两者都使用相同的jar：sqlite-jdbc-3.27.2.1.jar。我尝试修剪Java中的sql字符串，删除所有不需要的空格。这没有帮助。

查询：

SELECT *,(SELECT story.name FROM story WHERE story.id = story_id) AS story_name,round((STRFTIME('%s',last_reg) - STRFTIME('%s',first_reg)) / (60.0),2) AS minutes,first_reg)) / (60.0 * 60.0),2) AS hours
  FROM (
    SELECT *,ROW_NUMber() OVER (
             PARTITION BY story_id,reg_date
             ORDER BY reg_time DESC
           ) reg_number,FIRST_VALUE(reg_time) OVER (
             PARTITION BY story_id,reg_date
             ORDER BY reg_time
           ) first_reg,LAST_VALUE(reg_time) OVER (
             PARTITION BY story_id,reg_date
             ORDER BY reg_time 
           ) last_reg
      FROM work_registration
     ORDER BY reg_date,reg_time
  );

DDL的完整性：

CREATE TABLE IF NOT EXISTS story
(   id          integer PRIMARY KEY
    name        text    NOT NULL
);

CREATE TABLE IF NOT EXISTS work_registration
(   reg_date    text    NOT NULL,reg_time    text    NOT NULL,description text,story_id    integer NOT NULL,FOREIGN KEY (story_id) REFERENCES story (id)
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_work_registration_date_time_story
ON work_registration (reg_date,reg_time,story_id);

Java的重要部分：

public class HoursDatabaseService {

    private static final String URL = "jdbc:sqlite:mf-plugin-hours.db";

    private Connection connect() {
        Connection conn = null;
        try {
            conn = DriverManager.getconnection(URL);
        } catch (SQLException e) {
            LOG.error(e.getMessage());
        }
        return conn;
    }

    public void selectAll() {
        String sql = " ... "; // <- here goes the query

        try (Connection conn = this.connect();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            // loop through the result set
            while (rs.next()) {
                 // stuff
            }
        } catch (SQLException e) {
            LOG.error(e.getMessage());
        }
    }

    public static void main(String[] args) {
        HoursDatabaseService service = new HoursDatabaseService();
        service.selectAll();
    }

}

当数据库工具正确运行此工具时，Java为什么会导致： [SQLITE_ERROR] SQL error or missing database (near "(": syntax error)？

我在过程中注意到的晦涩之处：如果使用此查询创建视图，则Java无法启动，因为它发现格式错误的视图：[SQLITE_CORrupT] The database disk image is malformed (malformed database schema (vw_story_registrations) - near "(": syntax error)。有趣的是，在数据库工具中查询视图可以正常工作...

为什么它在Java中不起作用？