'use strict'; const { expect } = require('chai'); const assert = require('assert'); const Runner = require('../../../../lib/execution/runner'); const { TEST_TIMESTAMP } = require('../../../util/constants'); const { isMysql, isPostgreSQL, isPgNative, isMssql, isSQLite, isOracle, isPgBased, isCockroachDB, } = require('../../../util/db-helpers'); const { createUsers, createAccounts, createCompositeKeyTable, createTestTableTwo, dropTables, createDefaultTable, } = require('../../../util/tableCreatorHelper'); const { insertAccounts } = require('../../../util/dataInsertHelper'); const { assertNumberArrayStrict } = require('../../../util/assertHelper'); const { getAllDbs, getKnexForDb, } = require('../../util/knex-instance-provider'); const logger = require('../../../integration/logger'); describe('Selects', function () { getAllDbs().forEach((db) => { describe(db, () => { let knex; before(async () => { knex = logger(getKnexForDb(db)); await dropTables(knex); await createUsers(knex); await createAccounts(knex); await createCompositeKeyTable(knex); await createTestTableTwo(knex); await createDefaultTable(knex); await createDefaultTable(knex, true); }); beforeEach(async () => { await knex('accounts').truncate(); await insertAccounts(knex); }); after(async () => { return knex.destroy(); // ToDo we can do this after other tests are fixed // await dropTables(knex); }); it('runs with no conditions', function () { return knex('accounts').select(); }); it('returns an array of a single column with `pluck`', async () => { return knex .pluck('id') .orderBy('id') .from('accounts') .testSql(function (tester) { tester( 'mysql', 'select `id` from `accounts` order by `id` asc', [], [1, 2, 3, 4, 5, 6] ); tester( 'pg', 'select "id" from "accounts" order by "id" asc', [], ['1', '2', '3', '4', '5', '6'] ); tester( 'pgnative', 'select "id" from "accounts" order by "id" asc', [], ['1', '2', '3', '4', '5', '6'] ); tester( 'pg-redshift', 'select "id" from "accounts" order by "id" asc', [], ['1', '2', '3', '4', '5', '6'] ); tester( 'sqlite3', 'select `id` from `accounts` order by `id` asc', [], [1, 2, 3, 4, 5, 6] ); tester( 'oracledb', 'select "id" from "accounts" order by "id" asc', [], [1, 2, 3, 4, 5, 6] ); tester( 'mssql', 'select [id] from [accounts] order by [id] asc', [], ['1', '2', '3', '4', '5', '6'] ); }); }); it('can pluck a qualified column name, #1619', function () { return knex .pluck('accounts.id') .from('accounts') .orderBy('accounts.id') .testSql(function (tester) { tester( 'mysql', 'select `accounts`.`id` from `accounts` order by `accounts`.`id` asc', [], [1, 2, 3, 4, 5, 6] ); tester( 'pg', 'select "accounts"."id" from "accounts" order by "accounts"."id" asc', [], ['1', '2', '3', '4', '5', '6'] ); tester( 'pgnative', 'select "accounts"."id" from "accounts" order by "accounts"."id" asc', [], ['1', '2', '3', '4', '5', '6'] ); tester( 'pg-redshift', 'select "accounts"."id" from "accounts" order by "accounts"."id" asc', [], ['1', '2', '3', '4', '5', '6'] ); tester( 'sqlite3', 'select `accounts`.`id` from `accounts` order by `accounts`.`id` asc', [], [1, 2, 3, 4, 5, 6] ); tester( 'oracledb', 'select "accounts"."id" from "accounts" order by "accounts"."id" asc', [], [1, 2, 3, 4, 5, 6] ); tester( 'mssql', 'select [accounts].[id] from [accounts] order by [accounts].[id] asc', [], ['1', '2', '3', '4', '5', '6'] ); }); }); it('starts selecting at offset', function () { return knex .pluck('id') .orderBy('id') .from('accounts') .offset(2) .testSql(function (tester) { tester( 'mysql', 'select `id` from `accounts` order by `id` asc limit 18446744073709551615 offset ?', [2], [3, 4, 5, 6] ); tester( 'pg', 'select "id" from "accounts" order by "id" asc offset ?', [2], ['3', '4', '5', '6'] ); tester( 'pgnative', 'select "id" from "accounts" order by "id" asc offset ?', [2], ['3', '4', '5', '6'] ); tester( 'pg-redshift', 'select "id" from "accounts" order by "id" asc offset ?', [2], ['3', '4', '5', '6'] ); tester( 'sqlite3', 'select `id` from `accounts` order by `id` asc limit ? offset ?', [-1, 2], [3, 4, 5, 6] ); tester( 'oracledb', 'select * from (select row_.*, ROWNUM rownum_ from (select "id" from "accounts" order by "id" asc) row_ where rownum <= ?) where rownum_ > ?', [10000000000002, 2], [3, 4, 5, 6] ); tester( 'mssql', 'select [id] from [accounts] order by [id] asc offset ? rows', [2], ['3', '4', '5', '6'] ); }); }); it('#4335 - should throw an error when negative offset provided', function (ok) { try { knex.from('accounts').limit(20).offset(-20); throw new Error('no error was thrown for negative offset!'); } catch (error) { if ( error.message === 'A non-negative integer must be provided to offset.' ) { ok(); } else { throw error; } } }); it('#4199 - adheres to hint comments', async function () { const expectedErrors = { mysql: { code: 'ER_QUERY_TIMEOUT', errno: 3024, sqlMessage: 'Query execution was interrupted, maximum statement execution time exceeded', }, mysql2: { errno: 3024, sqlMessage: 'Query execution was interrupted, maximum statement execution time exceeded', }, }; if (!expectedErrors[knex.client.driverName]) { return this.skip(); } const baseQuery = knex('accounts') .select('id', knex.raw('sleep(0.1)')) .limit(2); await expect( baseQuery.clone() ).to.eventually.be.fulfilled.and.to.have.lengthOf(2); await expect( baseQuery.clone().hintComment('max_execution_time(10)') ).to.eventually.be.rejected.and.to.deep.include( expectedErrors[knex.client.driverName] ); }); it('#4199 - ignores invalid hint comments', async function () { return knex .select('id') .orderBy('id') .from('accounts') .hintComment('invalid()') .testSql(function (tester) { tester( 'mysql', 'select /*+ invalid() */ `id` from `accounts` order by `id` asc', [], [{ id: 1 }, { id: 2 }, { id: 3 }, { id: 4 }, { id: 5 }, { id: 6 }] ); tester( 'pg', 'select /*+ invalid() */ "id" from "accounts" order by "id" asc', [], [ { id: '1' }, { id: '2' }, { id: '3' }, { id: '4' }, { id: '5' }, { id: '6' }, ] ); tester( 'pgnative', 'select /*+ invalid() */ "id" from "accounts" order by "id" asc', [], [ { id: '1' }, { id: '2' }, { id: '3' }, { id: '4' }, { id: '5' }, { id: '6' }, ] ); tester( 'pg-redshift', 'select /*+ invalid() */ "id" from "accounts" order by "id" asc', [], [ { id: '1' }, { id: '2' }, { id: '3' }, { id: '4' }, { id: '5' }, { id: '6' }, ] ); tester( 'sqlite3', 'select /*+ invalid() */ `id` from `accounts` order by `id` asc', [], [{ id: 1 }, { id: 2 }, { id: 3 }, { id: 4 }, { id: 5 }, { id: 6 }] ); tester( 'oracledb', 'select /*+ invalid() */ "id" from "accounts" order by "id" asc', [], [{ id: 1 }, { id: 2 }, { id: 3 }, { id: 4 }, { id: 5 }, { id: 6 }] ); tester( 'mssql', 'select /*+ invalid() */ [id] from [accounts] order by [id] asc', [], [ { id: '1' }, { id: '2' }, { id: '3' }, { id: '4' }, { id: '5' }, { id: '6' }, ] ); }); }); it('returns a single entry with first', function () { return knex .first('id', 'first_name') .orderBy('id') .from('accounts') .testSql(function (tester) { tester( 'mysql', 'select `id`, `first_name` from `accounts` order by `id` asc limit ?', [1], { id: 1, first_name: 'Test' } ); tester( 'pg', 'select "id", "first_name" from "accounts" order by "id" asc limit ?', [1], { id: '1', first_name: 'Test' } ); tester( 'pgnative', 'select "id", "first_name" from "accounts" order by "id" asc limit ?', [1], { id: '1', first_name: 'Test' } ); tester( 'pg-redshift', 'select "id", "first_name" from "accounts" order by "id" asc limit ?', [1], { id: '1', first_name: 'Test' } ); tester( 'sqlite3', 'select `id`, `first_name` from `accounts` order by `id` asc limit ?', [1], { id: 1, first_name: 'Test' } ); tester( 'oracledb', 'select * from (select "id", "first_name" from "accounts" order by "id" asc) where rownum <= ?', [1], { id: 1, first_name: 'Test' } ); tester( 'mssql', 'select top (?) [id], [first_name] from [accounts] order by [id] asc', [1], { id: '1', first_name: 'Test' } ); }); }); it('allows you to stream', function () { if (isPgNative(knex)) { return this.skip(); } let count = 0; return knex('accounts') .stream(function (rowStream) { rowStream.on('data', function () { count++; }); }) .then(function () { assert(count === 6, 'Six rows should have been streamed'); }); }); it('returns a stream if not passed a function', function (done) { if (isPgNative(knex)) { return this.skip(); } let count = 0; const stream = knex('accounts').stream(); stream.on('data', function () { count++; if (count === 6) done(); }); }); it('allows you to stream with mysql dialect options', function () { if (!isMysql(knex)) { return this.skip(); } const rows = []; return knex('accounts') .options({ typeCast(field, next) { let val; if (field.type === 'VAR_STRING') { val = field.string(); return val == null ? val : val.toUpperCase(); } return next(); }, }) .stream(function (rowStream) { rowStream.on('data', function (row) { rows.push(row); }); }) .then(function () { expect(rows).to.have.lengthOf(6); rows.forEach((row) => { ['first_name', 'last_name', 'email'].forEach((field) => expect(row[field]).to.equal(row[field].toUpperCase()) ); }); }); }); it('emits error on the stream, if not passed a function, and connecting fails', function () { const expected = new Error(); const original = Runner.prototype.ensureConnection; Runner.prototype.ensureConnection = function () { return Promise.reject(expected); }; const restore = () => { Runner.prototype.ensureConnection = original; }; const promise = new Promise((resolve, reject) => { const timeout = setTimeout(() => { reject(new Error('Timeout')); }, 5000); const stream = knex('accounts').stream(); stream.on('error', function (actual) { clearTimeout(timeout); if (actual === expected) { resolve(); } else { reject(new Error('Stream emitted unexpected error')); } }); }); promise.then(restore, restore); return promise; }); it('emits error on the stream, if not passed a function, and query fails', function (done) { const stream = knex('accounts').select('invalid_field').stream(); stream.on('error', function (err) { assert(err instanceof Error); done(); }); }); it('emits error if not passed a function and the query has wrong bindings', function (done) { const stream = knex('accounts') .whereRaw('id = ? and first_name = ?', ['2']) .stream(); stream.on('error', function (err) { assert(err instanceof Error); done(); }); }); it('properly escapes postgres queries on streaming', async function () { const result = await knex('accounts').select(); let count = 0; await knex('accounts') .where('id', result[0].id) .stream(function (rowStream) { rowStream.on('data', function () { count++; }); }); assert(count === 1, 'One row should have been streamed'); }); it('throws errors on the asCallback if uncaught in the last block', function (ok) { const listeners = process.listeners('uncaughtException'); process.removeAllListeners('uncaughtException'); process.on('uncaughtException', function () { process.removeAllListeners('uncaughtException'); for (let i = 0, l = listeners.length; i < l; i++) { process.on('uncaughtException', listeners[i]); } ok(); }); knex('accounts') .select() .asCallback(function () { this.undefinedVar.test; }); }); it('uses "orderBy"', function () { return knex('accounts') .pluck('id') .orderBy('id', 'desc') .testSql(function (tester) { tester( 'oracledb', 'select "id" from "accounts" order by "id" desc', [], [6, 5, 4, 3, 2, 1] ); tester( 'mssql', 'select [id] from [accounts] order by [id] desc', [], ['6', '5', '4', '3', '2', '1'] ); }); }); it('order by with null', async () => { await knex.schema .dropTableIfExists('OrderByNullTest') .createTable('OrderByNullTest', function (table) { table.increments('id').primary(); table.string('null_col').nullable().defaultTo(null); }); await knex('OrderByNullTest').insert([ { null_col: 'test', }, { null_col: 'test2', }, { null_col: null, }, { null_col: null, }, ]); await knex('OrderByNullTest') .pluck('id') .orderBy('null_col', 'asc', 'first') .testSql(function (tester) { tester( 'mysql', 'select `id` from `OrderByNullTest` order by (`null_col` is not null) asc', [], [3, 4, 1, 2] ); tester( 'pg', 'select "id" from "OrderByNullTest" order by ("null_col" is not null) asc', [], [3, 4, 1, 2] ); tester( 'pgnative', 'select "id" from "OrderByNullTest" order by ("null_col" is not null) asc', [], [3, 4, 1, 2] ); tester( 'pg-redshift', 'select "id" from "OrderByNullTest" order by ("null_col" is not null) asc', [], ['3', '4', '1', '2'] ); tester( 'sqlite3', 'select `id` from `OrderByNullTest` order by (`null_col` is not null) asc', [], [3, 4, 1, 2] ); tester( 'oracledb', 'select "id" from "OrderByNullTest" order by ("null_col" is not null) asc', [], [3, 4, 1, 2] ); tester( 'mssql', 'select [id] from [OrderByNullTest] order by IIF([null_col] is null,0,1) asc', [], [3, 4, 1, 2] ); }); await knex('OrderByNullTest') .pluck('id') .orderBy('null_col', 'asc', 'last') .testSql(function (tester) { tester( 'mysql', 'select `id` from `OrderByNullTest` order by (`null_col` is null) asc', [], [1, 2, 3, 4] ); tester( 'pg', 'select "id" from "OrderByNullTest" order by ("null_col" is null) asc', [], [1, 2, 3, 4] ); tester( 'pgnative', 'select "id" from "OrderByNullTest" order by ("null_col" is null) asc', [], [1, 2, 3, 4] ); tester( 'pg-redshift', 'select "id" from "OrderByNullTest" order by ("null_col" is null) asc', [], ['1', '2', '3', '4'] ); tester( 'sqlite3', 'select `id` from `OrderByNullTest` order by (`null_col` is null) asc', [], [1, 2, 3, 4] ); tester( 'oracledb', 'select "id" from "OrderByNullTest" order by ("null_col" is null) asc', [], [1, 2, 3, 4] ); tester( 'mssql', 'select [id] from [OrderByNullTest] order by IIF([null_col] is null,1,0) asc', [], [1, 2, 3, 4] ); }); await knex.schema.dropTable('OrderByNullTest'); }); describe('simple "where" cases', function () { it('allows key, value', function () { return knex('accounts') .where('id', 1) .select('first_name', 'last_name') .testSql(function (tester) { tester( 'mysql', 'select `first_name`, `last_name` from `accounts` where `id` = ?', [1], [ { first_name: 'Test', last_name: 'User', }, ] ); tester( 'pg', 'select "first_name", "last_name" from "accounts" where "id" = ?', [1], [ { first_name: 'Test', last_name: 'User', }, ] ); tester( 'pgnative', 'select "first_name", "last_name" from "accounts" where "id" = ?', [1], [ { first_name: 'Test', last_name: 'User', }, ] ); tester( 'pg-redshift', 'select "first_name", "last_name" from "accounts" where "id" = ?', [1], [ { first_name: 'Test', last_name: 'User', }, ] ); tester( 'sqlite3', 'select `first_name`, `last_name` from `accounts` where `id` = ?', [1], [ { first_name: 'Test', last_name: 'User', }, ] ); tester( 'oracledb', 'select "first_name", "last_name" from "accounts" where "id" = ?', [1], [ { first_name: 'Test', last_name: 'User', }, ] ); tester( 'mssql', 'select [first_name], [last_name] from [accounts] where [id] = ?', [1], [ { first_name: 'Test', last_name: 'User', }, ] ); }); }); it('allows key, operator, value', function () { return knex('accounts') .where('id', 1) .select('first_name', 'last_name') .testSql(function (tester) { tester( 'mysql', 'select `first_name`, `last_name` from `accounts` where `id` = ?', [1], [ { first_name: 'Test', last_name: 'User', }, ] ); tester( 'pg', 'select "first_name", "last_name" from "accounts" where "id" = ?', [1], [ { first_name: 'Test', last_name: 'User', }, ] ); tester( 'pgnative', 'select "first_name", "last_name" from "accounts" where "id" = ?', [1], [ { first_name: 'Test', last_name: 'User', }, ] ); tester( 'pg-redshift', 'select "first_name", "last_name" from "accounts" where "id" = ?', [1], [ { first_name: 'Test', last_name: 'User', }, ] ); tester( 'sqlite3', 'select `first_name`, `last_name` from `accounts` where `id` = ?', [1], [ { first_name: 'Test', last_name: 'User', }, ] ); tester( 'oracledb', 'select "first_name", "last_name" from "accounts" where "id" = ?', [1], [ { first_name: 'Test', last_name: 'User', }, ] ); tester( 'mssql', 'select [first_name], [last_name] from [accounts] where [id] = ?', [1], [ { first_name: 'Test', last_name: 'User', }, ] ); }); }); it('allows selecting columns with an array', function () { return knex('accounts') .where('id', '>', 1) .select(['email', 'logins']) .testSql(function (tester) { tester( 'mysql', 'select `email`, `logins` from `accounts` where `id` > ?', [1] ); tester( 'pg', 'select "email", "logins" from "accounts" where "id" > ?', [1] ); tester( 'pgnative', 'select "email", "logins" from "accounts" where "id" > ?', [1] ); tester( 'pg-redshift', 'select "email", "logins" from "accounts" where "id" > ?', [1] ); tester( 'sqlite3', 'select `email`, `logins` from `accounts` where `id` > ?', [1] ); tester( 'oracledb', 'select "email", "logins" from "accounts" where "id" > ?', [1] ); tester( 'mssql', 'select [email], [logins] from [accounts] where [id] > ?', [1] ); }); }); it('allows a hash of where attrs', function () { return knex('accounts') .where({ id: 1 }) .select('*') .testSql(function (tester) { tester( 'mysql', 'select * from `accounts` where `id` = ?', [1], [ { id: 1, first_name: 'Test', last_name: 'User', email: 'test1@example.com', logins: 1, balance: 0, about: 'Lorem ipsum Dolore labore incididunt enim.', created_at: TEST_TIMESTAMP, updated_at: TEST_TIMESTAMP, phone: null, }, ] ); tester( 'pg', 'select * from "accounts" where "id" = ?', [1], [ { id: '1', first_name: 'Test', last_name: 'User', email: 'test1@example.com', logins: 1, balance: 0, about: 'Lorem ipsum Dolore labore incididunt enim.', created_at: TEST_TIMESTAMP, updated_at: TEST_TIMESTAMP, phone: null, }, ] ); tester( 'pgnative', 'select * from "accounts" where "id" = ?', [1], [ { id: '1', first_name: 'Test', last_name: 'User', email: 'test1@example.com', logins: 1, balance: 0, about: 'Lorem ipsum Dolore labore incididunt enim.', created_at: TEST_TIMESTAMP, updated_at: TEST_TIMESTAMP, phone: null, }, ] ); tester( 'pg-redshift', 'select * from "accounts" where "id" = ?', [1], [ { id: '1', first_name: 'Test', last_name: 'User', email: 'test1@example.com', logins: 1, balance: 0, about: 'Lorem ipsum Dolore labore incididunt enim.', created_at: TEST_TIMESTAMP, updated_at: TEST_TIMESTAMP, phone: null, }, ] ); tester( 'sqlite3', 'select * from `accounts` where `id` = ?', [1], [ { id: 1, first_name: 'Test', last_name: 'User', email: 'test1@example.com', logins: 1, balance: 0, about: 'Lorem ipsum Dolore labore incididunt enim.', created_at: TEST_TIMESTAMP, updated_at: TEST_TIMESTAMP, phone: null, }, ] ); tester( 'oracledb', 'select * from "accounts" where "id" = ?', [1], [ { id: 1, first_name: 'Test', last_name: 'User', email: 'test1@example.com', logins: 1, balance: 0, about: 'Lorem ipsum Dolore labore incididunt enim.', created_at: TEST_TIMESTAMP, updated_at: TEST_TIMESTAMP, phone: null, }, ] ); tester( 'mssql', 'select * from [accounts] where [id] = ?', [1], [ { id: '1', first_name: 'Test', last_name: 'User', email: 'test1@example.com', logins: 1, balance: 0, about: 'Lorem ipsum Dolore labore incididunt enim.', created_at: TEST_TIMESTAMP, updated_at: TEST_TIMESTAMP, phone: null, }, ] ); }); }); it('allows where id: undefined or id: null as a where null clause', function () { return knex('accounts') .where({ id: null }) .select('first_name', 'email') .testSql(function (tester) { tester( 'mysql', 'select `first_name`, `email` from `accounts` where `id` is null', [], [] ); tester( 'pg', 'select "first_name", "email" from "accounts" where "id" is null', [], [] ); tester( 'pgnative', 'select "first_name", "email" from "accounts" where "id" is null', [], [] ); tester( 'pg-redshift', 'select "first_name", "email" from "accounts" where "id" is null', [], [] ); tester( 'sqlite3', 'select `first_name`, `email` from `accounts` where `id` is null', [], [] ); tester( 'oracledb', 'select "first_name", "email" from "accounts" where "id" is null', [], [] ); tester( 'mssql', 'select [first_name], [email] from [accounts] where [id] is null', [], [] ); }); }); it('allows where id = 0', function () { return knex('accounts') .where({ id: 0 }) .select() .testSql(function (tester) { tester( 'mysql', 'select * from `accounts` where `id` = ?', [0], [] ); tester('pg', 'select * from "accounts" where "id" = ?', [0], []); tester( 'pgnative', 'select * from "accounts" where "id" = ?', [0], [] ); tester( 'pg-redshift', 'select * from "accounts" where "id" = ?', [0], [] ); tester( 'sqlite3', 'select * from `accounts` where `id` = ?', [0], [] ); tester( 'oracledb', 'select * from "accounts" where "id" = ?', [0], [] ); tester( 'mssql', 'select * from [accounts] where [id] = ?', [0], [] ); }); }); }); it('#1276 - Dates NULL should be returned as NULL, not as new Date(null)', function () { return knex.schema .dropTableIfExists('DatesTest') .createTable('DatesTest', function (table) { table.increments('id').primary(); table.dateTime('dateTimeCol'); table.timestamp('timeStampCol').nullable().defaultTo(null); // MySQL defaults TIMESTAMP columns to current timestamp table.date('dateCol'); table.time('timeCol'); }) .then(function () { return knex('DatesTest').insert([ { dateTimeCol: null, timeStampCol: null, dateCol: null, timeCol: null, }, ]); }) .then(function () { return knex('DatesTest').select(); }) .then(function (rows) { expect(rows[0].dateTimeCol).to.equal(null); expect(rows[0].timeStampCol).to.equal(null); expect(rows[0].dateCol).to.equal(null); expect(rows[0].timeCol).to.equal(null); }); }); it('has a "distinct" clause', function () { return Promise.all([ knex('accounts') .select() .distinct('email') .where('logins', 2) .orderBy('email'), knex('accounts').distinct('email').select().orderBy('email'), ]); }); it('supports "distinct on"', async function () { const builder = knex('accounts') .select('email', 'logins') .distinctOn('id') .orderBy('id'); if (!isPgBased(knex)) { let error; try { await builder; } catch (e) { error = e; } expect(error.message).to.eql( '.distinctOn() is currently only supported on PostgreSQL' ); return; } return builder.testSql(function (tester) { tester( 'pg', 'select distinct on ("id") "email", "logins" from "accounts" order by "id" asc', [], [ { email: 'test1@example.com', logins: 1, }, { email: 'test2@example.com', logins: 1, }, { email: 'test3@example.com', logins: 2, }, { email: 'test4@example.com', logins: 2, }, { email: 'test5@example.com', logins: 2, }, { email: 'test6@example.com', logins: 2, }, ] ); tester( 'pgnative', 'select distinct on ("id") "email", "logins" from "accounts" order by "id" asc', [], [ { email: 'test1@example.com', logins: 1, }, { email: 'test2@example.com', logins: 1, }, { email: 'test3@example.com', logins: 2, }, { email: 'test4@example.com', logins: 2, }, { email: 'test5@example.com', logins: 2, }, { email: 'test6@example.com', logins: 2, }, ] ); }); }); it('does "orWhere" cases', function () { return knex('accounts') .where('id', 1) .orWhere('id', '>', 2) .select('first_name', 'last_name'); }); it('does "andWhere" cases', function () { return knex('accounts') .select('first_name', 'last_name', 'about') .where('id', 1) .andWhere('email', 'test1@example.com'); }); it('takes a function to wrap nested where statements', function () { return Promise.all([ knex('accounts') .where(function () { this.where('id', 2); this.orWhere('id', 3); }) .select('*'), ]); }); it('handles "where in" cases', function () { return Promise.all([ knex('accounts').whereIn('id', [1, 2, 3]).select(), ]); }); it('handles "or where in" cases', function () { return knex('accounts') .where('email', 'test1@example.com') .orWhereIn('id', [2, 3, 4]) .select(); }); it('handles multi-column "where in" cases', async function () { await knex('composite_key_test').insert([ { column_a: 1, column_b: 1, details: 'One, One, One', status: 1, }, { column_a: 1, column_b: 2, details: 'One, Two, Zero', status: 0, }, { column_a: 2, column_b: 2, details: 'Two, Two, Zero', status: 0, }, ]); if (!isMssql(knex)) { await knex('composite_key_test') .whereIn( ['column_a', 'column_b'], [ [1, 1], [1, 2], ] ) .orderBy('status', 'desc') .select() .testSql(function (tester) { tester( 'mysql', 'select * from `composite_key_test` where (`column_a`, `column_b`) in ((?, ?), (?, ?)) order by `status` desc', [1, 1, 1, 2], [ { column_a: 1, column_b: 1, details: 'One, One, One', status: 1, }, { column_a: 1, column_b: 2, details: 'One, Two, Zero', status: 0, }, ] ); tester( 'pg', 'select * from "composite_key_test" where ("column_a", "column_b") in ((?, ?), (?, ?)) order by "status" desc', [1, 1, 1, 2], [ { column_a: 1, column_b: 1, details: 'One, One, One', status: 1, }, { column_a: 1, column_b: 2, details: 'One, Two, Zero', status: 0, }, ] ); tester( 'pgnative', 'select * from "composite_key_test" where ("column_a", "column_b") in ((?, ?), (?, ?)) order by "status" desc', [1, 1, 1, 2], [ { column_a: 1, column_b: 1, details: 'One, One, One', status: 1, }, { column_a: 1, column_b: 2, details: 'One, Two, Zero', status: 0, }, ] ); tester( 'pg-redshift', 'select * from "composite_key_test" where ("column_a", "column_b") in ((?, ?), (?, ?)) order by "status" desc', [1, 1, 1, 2], [ { column_a: 1, column_b: 1, details: 'One, One, One', status: 1, }, { column_a: 1, column_b: 2, details: 'One, Two, Zero', status: 0, }, ] ); tester( 'oracledb', 'select * from "composite_key_test" where ("column_a", "column_b") in ((?, ?), (?, ?)) order by "status" desc', [1, 1, 1, 2], [ { column_a: 1, column_b: 1, details: 'One, One, One', status: 1, }, { column_a: 1, column_b: 2, details: 'One, Two, Zero', status: 0, }, ] ); tester( 'sqlite3', 'select * from `composite_key_test` where (`column_a`, `column_b`) in ( values (?, ?), (?, ?)) order by `status` desc', [1, 1, 1, 2], [ { column_a: 1, column_b: 1, details: 'One, One, One', status: 1, }, { column_a: 1, column_b: 2, details: 'One, Two, Zero', status: 0, }, ] ); }); } }); it('handles multi-column "where in" cases with where', function () { if (!isSQLite(knex) && !isMssql(knex)) { return knex('composite_key_test') .where('status', 1) .whereIn( ['column_a', 'column_b'], [ [1, 1], [1, 2], ] ) .select() .testSql(function (tester) { tester( 'mysql', 'select * from `composite_key_test` where `status` = ? and (`column_a`, `column_b`) in ((?, ?), (?, ?))', [1, 1, 1, 1, 2], [ { column_a: 1, column_b: 1, details: 'One, One, One', status: 1, }, ] ); tester( 'pg', 'select * from "composite_key_test" where "status" = ? and ("column_a", "column_b") in ((?, ?), (?, ?))', [1, 1, 1, 1, 2], [ { column_a: 1, column_b: 1, details: 'One, One, One', status: 1, }, ] ); tester( 'pgnative', 'select * from "composite_key_test" where "status" = ? and ("column_a", "column_b") in ((?, ?), (?, ?))', [1, 1, 1, 1, 2], [ { column_a: 1, column_b: 1, details: 'One, One, One', status: 1, }, ] ); tester( 'pg-redshift', 'select * from "composite_key_test" where "status" = ? and ("column_a", "column_b") in ((?, ?), (?, ?))', [1, 1, 1, 1, 2], [ { column_a: 1, column_b: 1, details: 'One, One, One', status: 1, }, ] ); tester( 'oracledb', 'select * from "composite_key_test" where "status" = ? and ("column_a", "column_b") in ((?, ?), (?, ?))', [1, 1, 1, 1, 2], [ { column_a: 1, column_b: 1, details: 'One, One, One', status: 1, }, ] ); }); } }); it('handles "where exists"', function () { return knex('accounts') .whereExists(function () { this.select('id').from('test_table_two').where({ id: 1 }); }) .select(); }); it('handles "where between"', function () { return knex('accounts').whereBetween('id', [1, 100]).select(); }); it('handles "or where between"', function () { return knex('accounts') .whereBetween('id', [1, 100]) .orWhereBetween('id', [200, 300]) .select(); }); it('does where(raw)', function () { if (isOracle(knex)) { // special case for oracle return knex('accounts') .whereExists(function () { this.select(knex.raw(1)) .from('test_table_two') .where( knex.raw('"test_table_two"."account_id" = "accounts"."id"') ); }) .select(); } else { return knex('accounts') .whereExists(function () { this.select(knex.raw(1)) .from('test_table_two') .where(knex.raw('test_table_two.account_id = accounts.id')); }) .select(); } }); it('does sub-selects', function () { return knex('accounts') .whereIn('id', function () { this.select('account_id').from('test_table_two').where('status', 1); }) .select('first_name', 'last_name'); }); describe('recursive CTE support', function () { before(async function () { await knex.schema.dropTableIfExists('rcte'); await knex.schema.createTable('rcte', (table) => { table.string('name'); table.string('parentName').nullable(); }); // We will check later that this name was found by chaining up parentId using an rCTE. await knex('rcte').insert({ name: 'parent' }); let parentName = 'parent'; for (const name of ['child', 'grandchild']) { await knex('rcte').insert({ name, parentName }); parentName = name; } // We will check later that this name is not returned. await knex('rcte').insert({ name: 'nope' }); }); it('supports recursive CTEs', async function () { const results = await knex .withRecursive('family', ['name', 'parentName'], (qb) => { qb.select('name', 'parentName') .from('rcte') .where({ name: 'grandchild' }) .unionAll((qb) => qb .select('rcte.name', 'rcte.parentName') .from('rcte') .join( 'family', knex.ref('family.parentName'), knex.ref('rcte.name') ) ); }) .select('name') .from('family'); const names = results.map(({ name }) => name); expect(names).to.have.length( 'parent child grandchild'.split(' ').length ); expect(names).to.contain('parent'); expect(names).not.to.contain('nope'); }); }); it('supports the <> operator', function () { return knex('accounts').where('id', '<>', 2).select('email', 'logins'); }); it('Allows for knex.Raw passed to the `where` clause', function () { if (isOracle(knex)) { return knex('accounts') .where(knex.raw('"id" = 2')) .select('email', 'logins'); } else { return knex('accounts') .where(knex.raw('id = 2')) .select('email', 'logins'); } }); it('Retains array bindings, #228', function () { const raw = knex.raw( 'select * from table t where t.id = ANY( ?::int[] )', [[1, 2, 3]] ); const raw2 = knex.raw('select "stored_procedure"(?, ?, ?)', [ 1, 2, ['a', 'b', 'c'], ]); const expected1 = [[1, 2, 3]]; const expected2 = [1, 2, ['a', 'b', 'c']]; expect(raw.toSQL().bindings).to.eql( knex.client.prepBindings(expected1) ); expect(raw2.toSQL().bindings).to.eql( knex.client.prepBindings(expected2) ); //Also expect raw's bindings to not have been modified by calling .toSQL() (preserving original bindings) expect(raw.bindings).to.eql(expected1); expect(raw2.bindings).to.eql(expected2); }); it('always returns the response object from raw', function () { if (isPostgreSQL(knex)) { return knex.raw('select id from accounts').then(function (resp) { assert(Array.isArray(resp.rows) === true); }); } }); it('properly escapes identifiers, #737', function () { if (isPostgreSQL(knex)) { const query = knex.select('id","name').from('test').toSQL(); assert(query.sql === 'select "id"",""name" from "test"'); } }); it('knex.ref() as column in .select()', async function () { const result = await knex('accounts').select(); const row = await knex('accounts') .select([knex.ref('accounts.id').as('userid')]) .select(['accounts.id']) .where(knex.ref('accounts.id'), result[0].id) .first(); expect(String(row.userid)).to.equal(String(result[0].id)); }); it.skip('select forUpdate().first() bug in oracle (--------- TODO: FIX)', function () { return knex('accounts').where('id', 1).forUpdate().first(); }); it('select for update locks selected row', function () { if (isSQLite(knex)) { return this.skip(); } return knex('test_default_table') .insert({ string: 'making sure there is a row to lock' }) .then(() => { return knex .transaction((trx) => { // select all from test table and lock return trx('test_default_table') .forUpdate() .then((res) => { // try to select stuff from table in other connection should just hang... return knex('test_default_table').forUpdate().timeout(100); }); }) .then((res) => { expect('Second query should have timed out').to.be.false; }) .catch((err) => { expect(err.message).to.be.contain( 'Defined query timeout of 100ms exceeded when running query' ); }); }); }); it('select for update locks only some tables, #2834', function () { if (!isPostgreSQL(knex)) { return this.skip(); } return knex('test_default_table') .insert({ string: 'making sure there is a row to lock', tinyint: 1 }) .then(() => { return knex('test_default_table2') .insert({ string: 'making sure there is a row to lock', tinyint: 1, }) .then(() => { return knex .transaction((trx) => { // select all from two test tables and lock only one table return trx('test_default_table') .innerJoin( 'test_default_table2', 'test_default_table.tinyint', 'test_default_table2.tinyint' ) .forUpdate('test_default_table') .then((res) => { // try to select stuff from unlocked table should not hang... return knex('test_default_table2') .forUpdate() .timeout(150); }) .then((res) => { // try to select stuff from table in other connection should just hang... return knex('test_default_table') .forUpdate() .timeout(100); }); }) .then((res) => { expect('Second query should have timed out').to.be.false; }) .catch((err) => { expect(err.message).to.be.contain( 'Defined query timeout of 100ms exceeded when running query' ); }); }); }); }); it('select for share prevents updating in other transaction', function () { // Query cancellation is not yet implemented for CockroachDB if (isSQLite(knex) || isOracle(knex) || isCockroachDB(knex)) { return this.skip(); } return knex('test_default_table') .insert({ string: 'making sure there is a row to lock' }) .then(() => { return knex .transaction((trx) => { // select all from test table and lock return trx('test_default_table') .forShare() .then((res) => { // try to update row that was selected for share should just hang... return knex.transaction((trx2) => { return trx2('test_default_table') .update({ string: 'foo' }) .timeout(100); }); }); }) .then((res) => { expect('Second query should have timed out').to.be.false; }) .catch((err) => { // mssql fails because it tries to rollback at the same time when update query is running // hopefully for share really works though... if (isMssql(knex)) { expect(err.message).to.be.contain( "Can't rollback transaction. There is a request in progress" ); } else { expect(err.message).to.be.contain( 'Defined query timeout of 100ms exceeded when running query' ); } }); }); }); it('forUpdate().skipLocked() with order by should return the first non-locked row', async function () { // Note: this test doesn't work properly on MySQL - see https://bugs.mysql.com/bug.php?id=67745 if (!isPostgreSQL(knex)) { return this.skip(); } const rowName = 'row for skipLocked() test #1'; await knex('test_default_table').delete().where({ string: rowName }); await knex('test_default_table').insert([ { string: rowName, tinyint: 1 }, { string: rowName, tinyint: 2 }, ]); const res = await knex.transaction(async (trx) => { // lock the first row in the test await trx('test_default_table') .where({ string: rowName }) .orderBy('tinyint', 'asc') .forUpdate() .first(); // try to lock the next available row from outside of the transaction return await knex('test_default_table') .where({ string: rowName }) .orderBy('tinyint', 'asc') .forUpdate() .skipLocked() .first(); }); // assert that we got the second row because the first one was locked expect(res.tinyint).to.equal(2); }); it('forUpdate().skipLocked() should return an empty set when all rows are locked', async function () { if (!isPostgreSQL(knex) && !isMysql(knex)) { return this.skip(); } const rowName = 'row for skipLocked() test #2'; await knex('test_default_table').delete().where({ string: rowName }); await knex('test_default_table').insert([ { string: rowName, tinyint: 1 }, { string: rowName, tinyint: 2 }, ]); const res = await knex.transaction(async (trx) => { // lock all of the test rows await trx('test_default_table') .where({ string: rowName }) .forUpdate(); // try to aquire the lock on one more row (which isn't available) from another transaction return await knex('test_default_table') .where({ string: rowName }) .forUpdate() .skipLocked() .first(); }); expect(res).to.be.undefined; }); it('forUpdate().noWait() should throw an error immediately when a row is locked', async function () { if (!isPostgreSQL(knex) && !isMysql(knex)) { return this.skip(); } const rowName = 'row for noWait() test'; await knex('test_default_table').delete().where({ string: rowName }); await knex('test_default_table').insert([ { string: rowName, tinyint: 1 }, { string: rowName, tinyint: 2 }, ]); try { await knex.transaction(async (trx) => { // select and lock the first row from this test // note: MySQL may lock both rows depending on how the results are fetched await trx('test_default_table') .where({ string: rowName }) .orderBy('tinyint', 'asc') .forUpdate() .first(); // try to lock it again (it should fail here) await knex('test_default_table') .where({ string: rowName }) .orderBy('tinyint', 'asc') .forUpdate() .noWait() .first(); }); // fail the test if the query finishes with no errors throw new Error( 'The query should have been cancelled when trying to select a locked row with .noWait()' ); } catch (err) { // check if we got the correct error from each db if (isPostgreSQL(knex)) { expect(err.message).to.contain('could not obtain lock on row'); } else if (isMysql(knex)) { // mysql expect(err.message).to.contain( 'lock(s) could not be acquired immediately' ); // mariadb // TODO: detect if test is being run on mysql or mariadb to check for the correct error message // expect(err.message).to.contain('Lock wait timeout exceeded'); } else { // unsupported database throw err; } } }); it('select from subquery', async function () { const result = await knex('accounts').select().orderBy('id'); const subquery = knex .from('accounts') .whereBetween('id', [result[0].id, result[2].id]); return knex .pluck('id') .orderBy('id') .from(subquery) .then( (rows) => { expect(knex.client.driverName).to.oneOf([ 'sqlite3', 'oracledb', 'cockroachdb', ]); if (knex.client.driverName !== 'cockroachdb') { assertNumberArrayStrict(knex, rows, [ result[0].id, result[1].id, result[2].id, ]); } else { expect(rows.length).to.equal(3); } }, (e) => { if (isMysql(knex)) { expect(e.errno).to.equal(1248); } else if (isPostgreSQL(knex)) { expect(e.message).to.contain('must have an alias'); } else if (isMssql(knex)) { expect(e.message).to.contain( "Incorrect syntax near the keyword 'order'" ); } else { throw e; } } ); }); }); }); });