SQLite не может найти существующий столбец в SELECT через JDBC и jOOQ

Я получаю странные результаты при работе с SQLite и JDBC (фактически через JOOQ, но эту проблему можно воспроизвести, выполнив строку запроса вручную через JDBC). Моя база данных состоит из трех таблиц, включая отношения «многие ко многим» и «один ко многим». Я пытаюсь выбрать все значения «основной» таблицы и объединить все необходимые значения из таблиц отношений:

SELECT location.name, 
       world.world, 
       player.player 
FROM   location 
       JOIN world 
         ON location."world-id" = world."world-id" 
       LEFT OUTER JOIN (location2player 
                         JOIN player 
                           ON location2player."player-id" = player."player-id") 
                    ON location."location-id" = location2player."location-id"

В JDBC этот запрос не выполняется:

java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (no such column: player.player)

Когда я выполняю запрос во внешнем редакторе SQLite, таком как SQLite Manager для Firefox, он работает, как и ожидалось.

Я работаю с sqlite-jdbc-3.7.2, который я не могу изменить. Для справки, запрос JOOQ:

create.select(LOCATION.NAME,WORLD.WORLD_,PLAYER.PLAYER_)
            .from(LOCATION
                .join(WORLD)
                    .on(LOCATION.WORLD_ID.eq(WORLD.WORLD_ID)
                )
                .leftOuterJoin(LOCATION2PLAYER
                    .join(PLAYER)
                        .onKey()
                    )
                    .on(LOCATION.LOCATION_ID.eq(LOCATION2PLAYER.LOCATION_ID)
                )
            .fetch()

Почему этот запрос не работает в JDBC и как я должен это исправить?


java sql sqlite jdbc jooq
person thee    schedule 30.05.2014    source источник
comment
Какую версию SQLite использует драйвер JDBC?   -  person CL.    schedule 31.05.2014

Ответы (2)


arrow_upward
1
arrow_downward

Хотя я думаю, что вы написали действительный ANSI SQL, вполне возможно, что SQLite интерпретирует ваше утверждение немного по-другому. Но вам действительно не нужно вкладывать соединения так, как вы это делаете. Попробуйте это:

SELECT location.name, 
       world.world, 
       player.player 
FROM   location 
       JOIN world 
         ON location."world-id" = world."world-id" 
       LEFT OUTER JOIN location2player 
         ON location."location-id" = location2player."location-id" 
       LEFT OUTER JOIN player 
         ON location2player."player-id" = player."player-id"
person Lukas Eder    schedule 31.05.2014

arrow_upward
1
arrow_downward

Я смог воссоздать вашу проблему в sqlite-jdbc-3.7.2, используя

sql = 
        "SELECT location.name, " +
            "world.world, " + 
            "player.player " + 
        "FROM " +
            "location " + 
            "JOIN world " + 
                "ON location.\"world-id\" = world.\"world-id\" " + 
            "LEFT OUTER JOIN (location2player " + 
                "JOIN " +
                "player " + 
                    "ON location2player.\"player-id\" = player.\"player-id\") " + 
                "ON location.\"location-id\" = location2player.\"location-id\"";

Проблема заключается в том, что таблицы location2player и player "спрятаны" внутри круглых скобок () подсоединения и недоступны для начального списка столбцов и конечного предложения ON. Следующий оператор позволяет избежать этой проблемы, задавая подзапросу псевдоним и используя имя псевдонима в этих двух местах:

sql = 
        "SELECT " +
            "location.name, " +
            "world.world, " + 
            "playerlocation.player " + 
        "FROM " +
            "location " + 
            "JOIN " +
            "world " + 
                "ON location.\"world-id\" = world.\"world-id\" " + 
            "LEFT OUTER JOIN " +
            "( " +
                "SELECT location2player.\"location-id\", player.player " +
                "FROM " +
                    "location2player " + 
                    "JOIN " +
                    "player " + 
                        "ON location2player.\"player-id\" = player.\"player-id\"" +
            ") AS playerlocation " + 
                "ON location.\"location-id\" = playerlocation.\"location-id\"";
person Gord Thompson    schedule 31.05.2014
comment
Это работает, спасибо. Теперь вопрос может заключаться в том, как мне правильно перенести это обратно в логику JOOQ - возможно, у @lukaseder есть идея? Использование трех отдельных предложений .as("playerlocation") кажется немного уродливым. - person thee; 31.05.2014
comment
@thee: Так это действительно ограничение SQLite? Я бы подумал, что исходное утверждение было действительным ANSI-SQL. Думаю, просто перепишите свой запрос на этот - person Lukas Eder; 01.06.2014
comment
@LukasEder Как бы то ни было, Mimer SQL-92 Validator говорит, что исходный SQL-запрос является переходным SQL-92. - person Gord Thompson; 01.06.2014
comment
Спасибо за ответ. Да, я так и думал. Вложенные соединения в круглых скобках используются редко, но они должны работать так же, как и невложенные соединения. Область, в которой определяются таблицы, не должна изменяться, как это происходит при использовании производной таблицы (как в вашем обходном пути). - person Lukas Eder; 01.06.2014