ORA-00923: FROM keyword not found where expected on migrate

[gabriofr]

Environment

Knex version: 0.95.14
Database + version: Oracle 19c EE
OS: OS X + Windows + Linux

@atiertant

Bug

1. Explain what kind of behaviour you are getting and how you think it should do

The resulting SQL when running eg. knex migrate:up or knex migrate:latest is not valid SQL as the from keyword is missing, it works fine if you rollback to knex v0.95.13 'v0.95.0`

Here's the debug output, the bug is right after the bind var:

  method: 'insert',
  options: {},
  timeout: false,
  cancelOnTimeout: false,
  bindings: [ 0 ],
  __knexQueryUid: 'rmTUdvIclHOrzEcalFRiU',
  sql: 'insert into "knex_migrations_lock" ("is_locked") select ? where not exists (select * from "knex_migrations_lock")'
}
insert into "knex_migrations_lock" ("is_locked") select :1 where not exists (select * from "knex_migrations_lock") - ORA-00923: FROM keyword not found where expected
Error: insert into "knex_migrations_lock" ("is_locked") select :1 where not exists (select * from "knex_migrations_lock") - ORA-00923: FROM keyword not found where expected

2. Error message
   Error: insert into "knex_migrations_lock" ("is_locked") select :1 where not exists (select * from "knex_migrations_lock") - ORA-00923: FROM keyword not found where expected

3. Reduced test code
   Just run a migration on any project that uses knex 0.9.14 and oracledb

[OlivierCavadenti]

Maybe could be the same problem here : #4835

[OlivierCavadenti]

As I can see select without from is not standard, so could be the problem here.
https://modern-sql.com/use-case/select-without-from

[gabriofr]

Maybe could be the same problem here : #4835

Jupp, can confirm the bugs are related. If I roll back to 0.95.11 and run the migration it will succeed, after that I can go back to 0.95.14 and migrations will work again..

[OlivierCavadenti]

I assume that fix 'this.select(trxOrKnex.raw('?', [0]))' in _insertLockRowIfNeeded will be enough.

[OlivierCavadenti]

Resolved by #4865

[kibertoad]

Released in 0.95.15