Repository: knex/knex
Pull request #4780 (2021-10-29 09:53:35)

Fix overzealous warning on use of whereNot with "in" or "between"
I ran into this with a `whereNot('id', 'in')` clause producing a warning despite it only intending to occur when 'in' is an operator, not a value.

Please let me know if the test format isn't right, I'd be happy to change it!


Patch:
diff --git a/lib/dialects/mssql/query/mssql-querycompiler.js b/lib/dialects/mssql/query/mssql-querycompiler.js
index a905d6dd..b323714b 100644
--- a/lib/dialects/mssql/query/mssql-querycompiler.js
+++ b/lib/dialects/mssql/query/mssql-querycompiler.js
@@ -535,6 +535,24 @@ class QueryCompiler_MSSQL extends QueryCompiler {
     }
     return offset;
   }
+
+  whereLike(statement) {
+    return `${this._columnClause(
+      statement
+    )} collate SQL_Latin1_General_CP1_CS_AS ${this._not(
+      statement,
+      'like '
+    )}${this._valueClause(statement)}`;
+  }
+
+  whereILike(statement) {
+    return `${this._columnClause(
+      statement
+    )} collate SQL_Latin1_General_CP1_CI_AS ${this._not(
+      statement,
+      'like '
+    )}${this._valueClause(statement)}`;
+  }
 }
 
 // Set the QueryBuilder & QueryCompiler on the client object,
diff --git a/lib/dialects/mysql/query/mysql-querycompiler.js b/lib/dialects/mysql/query/mysql-querycompiler.js
index 4edf7196..313f5191 100644
--- a/lib/dialects/mysql/query/mysql-querycompiler.js
+++ b/lib/dialects/mysql/query/mysql-querycompiler.js
@@ -153,6 +153,20 @@ class QueryCompiler_MySQL extends QueryCompiler {
           );
     return `limit ${limit}`;
   }
+
+  whereLike(statement) {
+    return `${this._columnClause(statement)} ${this._not(
+      statement,
+      'like '
+    )}${this._valueClause(statement)} COLLATE utf8_bin`;
+  }
+
+  whereILike(statement) {
+    return `${this._columnClause(statement)} ${this._not(
+      statement,
+      'like '
+    )}${this._valueClause(statement)}`;
+  }
 }
 
 // Set the QueryBuilder & QueryCompiler on the client object,
diff --git a/lib/dialects/postgres/schema/pg-columncompiler.js b/lib/dialects/postgres/schema/pg-columncompiler.js
index cc49f883..5fb7fa25 100644
--- a/lib/dialects/postgres/schema/pg-columncompiler.js
+++ b/lib/dialects/postgres/schema/pg-columncompiler.js
@@ -72,22 +72,16 @@ class ColumnCompiler_PG extends ColumnCompiler {
       useTz = !withoutTz;
     }
     useTz = typeof useTz === 'boolean' ? useTz : true;
-    precision = precision ? '(' + precision + ')' : '';
+    precision =
+      precision !== undefined && precision !== null
+        ? '(' + precision + ')'
+        : '';
 
     return `${useTz ? 'timestamptz' : 'timestamp'}${precision}`;
   }
 
   timestamp(withoutTz = false, precision) {
-    let useTz;
-    if (isObject(withoutTz)) {
-      ({ useTz, precision } = withoutTz);
-    } else {
-      useTz = !withoutTz;
-    }
-    useTz = typeof useTz === 'boolean' ? useTz : true;
-    precision = precision ? '(' + precision + ')' : '';
-
-    return `${useTz ? 'timestamptz' : 'timestamp'}${precision}`;
+    return this.datetime(withoutTz, precision);
   }
 
   // Modifiers:
diff --git a/lib/query/method-constants.js b/lib/query/method-constants.js
index 2b939d5b..babab89e 100644
--- a/lib/query/method-constants.js
+++ b/lib/query/method-constants.js
@@ -9,6 +9,7 @@ module.exports = [
   'column',
   'from',
   'fromJS',
+  'fromRaw',
   'into',
   'withSchema',
   'table',
@@ -24,6 +25,8 @@ module.exports = [
   'fullOuterJoin',
   'crossJoin',
   'where',
+  'whereLike',
+  'whereILike',
   'andWhere',
   'orWhere',
   'whereNot',
diff --git a/lib/query/querybuilder.js b/lib/query/querybuilder.js
index 31737900..50c76c1a 100644
--- a/lib/query/querybuilder.js
+++ b/lib/query/querybuilder.js
@@ -429,7 +429,7 @@ class Builder extends EventEmitter {
 
   // Adds an `not where` clause to the query.
   whereNot(column, ...args) {
-    if (args.length >= 1) {
+    if (args.length >= 2) {
       if (args[0] === 'in' || args[0] === 'between') {
         this.client.logger.warn(
           'whereNot is not suitable for "in" and "between" type subqueries. You should use "not in" and "not between" instead.'
@@ -611,6 +611,29 @@ class Builder extends EventEmitter {
     return this._bool('or').whereNotBetween(column, values);
   }
 
+  _whereLike(type, column, value) {
+    this._statements.push({
+      grouping: 'where',
+      type: type,
+      column,
+      value: value,
+      not: this._not(),
+      bool: this._bool(),
+      asColumn: this._asColumnFlag,
+    });
+    return this;
+  }
+
+  // Adds a `where like` clause to the query.
+  whereLike(column, value) {
+    return this._whereLike('whereLike', column, value);
+  }
+
+  // Adds a `where ilike` clause to the query.
+  whereILike(column, value) {
+    return this._whereLike('whereILike', column, value);
+  }
+
   // Adds a `group by` clause to the query.
   groupBy(item) {
     if (item && item.isRawInstance) {
@@ -1266,6 +1289,11 @@ class Builder extends EventEmitter {
     return this;
   }
 
+  fromRaw(sql, bindings) {
+    const raw = sql.isRawInstance ? sql : this.client.raw(sql, bindings);
+    return this.from(raw);
+  }
+
   // Passes query to provided callback function, useful for e.g. composing
   // domain-specific helpers
   modify(callback) {
diff --git a/lib/query/querycompiler.js b/lib/query/querycompiler.js
index 33ec6187..6e908448 100644
--- a/lib/query/querycompiler.js
+++ b/lib/query/querycompiler.js
@@ -882,7 +882,23 @@ class QueryCompiler {
   // Where Clause
   // ------
 
-  whereIn(statement) {
+  _valueClause(statement) {
+    return statement.asColumn
+      ? wrap_(
+          statement.value,
+          undefined,
+          this.builder,
+          this.client,
+          this.bindingsHolder
+        )
+      : this.client.parameter(
+          statement.value,
+          this.builder,
+          this.bindingsHolder
+        );
+  }
+
+  _columnClause(statement) {
     let columns;
     if (Array.isArray(statement.column)) {
       columns = `(${columnize_(
@@ -900,13 +916,33 @@ class QueryCompiler {
         this.bindingsHolder
       );
     }
+    return columns;
+  }
 
+  whereIn(statement) {
     const values = this.client.values(
       statement.value,
       this.builder,
       this.bindingsHolder
     );
-    return `${columns} ${this._not(statement, 'in ')}${values}`;
+    return `${this._columnClause(statement)} ${this._not(
+      statement,
+      'in '
+    )}${values}`;
+  }
+
+  whereLike(statement) {
+    return `${this._columnClause(statement)} ${this._not(
+      statement,
+      'like '
+    )}${this._valueClause(statement)}`;
+  }
+
+  whereILike(statement) {
+    return `${this._columnClause(statement)} ${this._not(
+      statement,
+      'ilike '
+    )}${this._valueClause(statement)}`;
   }
 
   whereNull(statement) {
@@ -942,19 +978,7 @@ class QueryCompiler {
         this.bindingsHolder
       ) +
       ' ' +
-      (statement.asColumn
-        ? wrap_(
-            statement.value,
-            undefined,
-            this.builder,
-            this.client,
-            this.bindingsHolder
-          )
-        : this.client.parameter(
-            statement.value,
-            this.builder,
-            this.bindingsHolder
-          ))
+      this._valueClause(statement)
     );
   }
 
diff --git a/types/index.d.ts b/types/index.d.ts
index a726ed3f..47c78170 100644
--- a/types/index.d.ts
+++ b/types/index.d.ts
@@ -491,6 +491,7 @@ export declare namespace Knex {
     column: Select<TRecord, TResult>;
     hintComment: HintComment<TRecord, TResult>;
     from: Table<TRecord, TResult>;
+    fromRaw: Table<TRecord, TResult>;
     into: Table<TRecord, TResult>;
     table: Table<TRecord, TResult>;
     distinct: Distinct<TRecord, TResult>;
@@ -535,6 +536,8 @@ export declare namespace Knex {
     orWhereIn: WhereIn<TRecord, TResult>;
     whereNotIn: WhereIn<TRecord, TResult>;
     orWhereNotIn: WhereIn<TRecord, TResult>;
+    whereLike: Where<TRecord, TResult>;
+    whereIlike: Where<TRecord, TResult>;
     whereNull: WhereNull<TRecord, TResult>;
     orWhereNull: WhereNull<TRecord, TResult>;
     whereNotNull: WhereNull<TRecord, TResult>;


Test patch:
diff --git a/test/integration2/migrate/migration-integration.spec.js b/test/integration2/migrate/migration-integration.spec.js
index d7dcd960..ac410572 100644
--- a/test/integration2/migrate/migration-integration.spec.js
+++ b/test/integration2/migrate/migration-integration.spec.js
@@ -913,7 +913,22 @@ describe('Migrations', function () {
           });
         }
 
-        it('is not able to run two migrations in parallel when transactions are disabled', function () {
+        /** TODO : fix me and enabled it.
+         * Fail randomly with (mostly with PostgreSQL, PgNative, CockroachDb):
+         *  knex.migrate.latest in parallel
+         *    is not able to run two migrations in parallel when transactions are disabled:
+
+         *    AssertionError: expected false to equal true
+         *    + expected - actual
+
+         *    -false
+         *    +true
+
+         *    at /home/runner/work/knex/knex/test/integration2/migrate/migration-integration.spec.js:944:37
+         *    at runMicrotasks (<anonymous>)
+         *    at processTicksAndRejections (internal/process/task_queues.js:95:5)
+         */
+        it.skip('is not able to run two migrations in parallel when transactions are disabled', function () {
           const migrations = [
             knex.migrate
               .latest({
diff --git a/test/integration2/query/select/selects.spec.js b/test/integration2/query/select/selects.spec.js
index 55395288..29416b51 100644
--- a/test/integration2/query/select/selects.spec.js
+++ b/test/integration2/query/select/selects.spec.js
@@ -5,7 +5,6 @@ const { expect } = require('chai');
 const assert = require('assert');
 const Runner = require('../../../../lib/execution/runner');
 
-const { TEST_TIMESTAMP } = require('../../../util/constants');
 const {
   isMysql,
   isPostgreSQL,
@@ -681,457 +680,6 @@ describe('Selects', function () {
         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')
@@ -1257,328 +805,6 @@ describe('Selects', function () {
         });
       });
 
-      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');
@@ -1627,45 +853,6 @@ describe('Selects', function () {
         });
       });
 
-      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) {
diff --git a/test/integration2/query/select/where.spec.js b/test/integration2/query/select/where.spec.js
new file mode 100644
index 00000000..cc04b6de
--- /dev/null
+++ b/test/integration2/query/select/where.spec.js
@@ -0,0 +1,612 @@
+'use strict';
+
+const { expect } = require('chai');
+
+require('assert');
+require('../../../../lib/execution/runner');
+
+const {
+  isMysql,
+  isPostgreSQL,
+  isMssql,
+  isSQLite,
+  isOracle,
+} = require('../../../util/db-helpers');
+const {
+  createUsers,
+  createAccounts,
+  createCompositeKeyTable,
+  createTestTableTwo,
+  dropTables,
+  createDefaultTable,
+} = require('../../../util/tableCreatorHelper');
+const { insertAccounts } = require('../../../util/dataInsertHelper');
+
+const {
+  getAllDbs,
+  getKnexForDb,
+} = require('../../util/knex-instance-provider');
+const logger = require('../../../integration/logger');
+const { TEST_TIMESTAMP } = require('../../../util/constants.js');
+
+describe('Where', 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();
+      });
+
+      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', 'sqlite3'],
+                'select `first_name`, `last_name` from `accounts` where `id` = ?',
+                [1],
+                [
+                  {
+                    first_name: 'Test',
+                    last_name: 'User',
+                  },
+                ]
+              );
+              tester(
+                ['pg', 'pgnative', 'pg-redshift', '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', 'sqlite3'],
+                'select `first_name`, `last_name` from `accounts` where `id` = ?',
+                [1],
+                [
+                  {
+                    first_name: 'Test',
+                    last_name: 'User',
+                  },
+                ]
+              );
+              tester(
+                ['pg', 'pgnative', 'pg-redshift', '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', 'sqlite3'],
+                'select `email`, `logins` from `accounts` where `id` > ?',
+                [1]
+              );
+              tester(
+                ['pg', 'pgnative', 'pg-redshift', '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', '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(
+                ['pg', 'pgnative', 'pg-redshift', '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', 'sqlite3'],
+                'select `first_name`, `email` from `accounts` where `id` is null',
+                [],
+                []
+              );
+              tester(
+                ['pg', 'pgnative', 'pg-redshift', 'oracledb', 'cockroachdb'],
+                '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', 'sqlite3'],
+                'select * from `accounts` where `id` = ?',
+                [0],
+                []
+              );
+              tester(
+                ['pg', 'pgnative', 'pg-redshift', 'oracledb', 'cockroachdb'],
+                'select * from "accounts" where "id" = ?',
+                [0],
+                []
+              );
+              tester(
+                'mssql',
+                'select * from [accounts] where [id] = ?',
+                [0],
+                []
+              );
+            });
+        });
+      });
+
+      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(
+                '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,
+                  },
+                ]
+              );
+              tester(
+                ['pg', 'pgnative', 'pg-redshift', '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(
+                'cockroachdb',
+                '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,
+                  },
+                ]
+              );
+            });
+        }
+      });
+
+      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', 'pgnative', 'pg-redshift', '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,
+                  },
+                ]
+              );
+              tester(
+                'cockroachdb',
+                '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');
+      });
+
+      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');
+        }
+      });
+
+      describe('where like', function () {
+        beforeEach(function () {
+          if (!(isPostgreSQL(knex) || isMssql(knex) || isMysql(knex))) {
+            return this.skip();
+          }
+        });
+
+        it('finds data using whereILike', async () => {
+          const result = await knex('accounts')
+            .select('*')
+            .whereILike('email', 'test1%');
+          expect(result[0].email).to.equal('test1@example.com');
+        });
+
+        it('finds data using whereILike when different case sensitivity', async () => {
+          const result = await knex('accounts').whereILike('email', 'TEST1%');
+          expect(result[0].email).to.equal('test1@example.com');
+        });
+
+        it('finds data using whereLike', async () => {
+          const result = await knex('accounts')
+            .select('*')
+            .whereLike('email', 'test1%');
+          expect(result[0].email).to.equal('test1@example.com');
+        });
+
+        it("doesn't find data using whereLike when different case sensitivity", async () => {
+          const result = await knex('accounts').whereLike('email', 'Test1%');
+          expect(result).to.deep.equal([]);
+        });
+      });
+
+      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);
+      });
+    });
+  });
+});
diff --git a/test/unit/query/builder.js b/test/unit/query/builder.js
index 99655e95..9c32f063 100644
--- a/test/unit/query/builder.js
+++ b/test/unit/query/builder.js
@@ -846,6 +846,40 @@ describe('QueryBuilder', () => {
     });
   });
 
+  it('uses whereLike, #2265', () => {
+    testsql(qb().select('*').from('users').whereLike('name', 'luk%'), {
+      mysql: {
+        sql: 'select * from `users` where `name` like ? COLLATE utf8_bin',
+        bindings: ['luk%'],
+      },
+      pg: {
+        sql: 'select * from "users" where "name" like ?',
+        bindings: ['luk%'],
+      },
+      mssql: {
+        sql: 'select * from [users] where [name] collate SQL_Latin1_General_CP1_CS_AS like ?',
+        bindings: ['luk%'],
+      },
+    });
+  });
+
+  it('uses whereILike, #2265', () => {
+    testsql(qb().select('*').from('users').whereILike('name', 'luk%'), {
+      mysql: {
+        sql: 'select * from `users` where `name` like ?',
+        bindings: ['luk%'],
+      },
+      pg: {
+        sql: 'select * from "users" where "name" ilike ?',
+        bindings: ['luk%'],
+      },
+      mssql: {
+        sql: 'select * from [users] where [name] collate SQL_Latin1_General_CP1_CI_AS like ?',
+        bindings: ['luk%'],
+      },
+    });
+  });
+
   it('whereColumn', () => {
     testsql(
       qb()
@@ -1050,6 +1084,36 @@ describe('QueryBuilder', () => {
     }
   });
 
+  it('where not should not throw warning when used with "in" or "between" as equality', function () {
+    testquery(
+      clientsWithCustomLoggerForTestWarnings.pg
+        .queryBuilder()
+        .select('*')
+        .from('users')
+        .whereNot('id', 'in'),
+      {
+        mysql: "select * from `users` where not `id` = 'in'",
+        pg: 'select * from "users" where not "id" = \'in\'',
+        'pg-redshift': 'select * from "users" where not "id" = \'in\'',
+        mssql: "select * from [users] where not [id] = 'in'",
+      }
+    );
+
+    testquery(
+      clientsWithCustomLoggerForTestWarnings.pg
+        .queryBuilder()
+        .select('*')
+        .from('users')
+        .whereNot('id', 'between'),
+      {
+        mysql: "select * from `users` where not `id` = 'between'",
+        pg: 'select * from "users" where not "id" = \'between\'',
+        'pg-redshift': 'select * from "users" where not "id" = \'between\'',
+        mssql: "select * from [users] where not [id] = 'between'",
+      }
+    );
+  });
+
   it('where bool', () => {
     testquery(qb().select('*').from('users').where(true), {
       mysql: 'select * from `users` where 1 = 1',
@@ -8421,6 +8485,15 @@ describe('QueryBuilder', () => {
     );
   });
 
+  it('uses fromRaw api, #1767', () => {
+    testsql(qb().select('*').fromRaw('(select * from users where age > 18)'), {
+      mysql: 'select * from (select * from users where age > 18)',
+      mssql: 'select * from (select * from users where age > 18)',
+      pg: 'select * from (select * from users where age > 18)',
+      'pg-redshift': 'select * from (select * from users where age > 18)',
+    });
+  });
+
   it('has a modify method which accepts a function that can modify the query', () => {
     // arbitrary number of arguments can be passed to `.modify(queryBuilder, ...)`,
     // builder is bound to `this`
diff --git a/test/unit/schema-builder/postgres.js b/test/unit/schema-builder/postgres.js
index 48ca1996..2caee2ac 100644
--- a/test/unit/schema-builder/postgres.js
+++ b/test/unit/schema-builder/postgres.js
@@ -1528,6 +1528,19 @@ describe('PostgreSQL SchemaBuilder', function () {
     );
   });
 
+  it('adding timestamp with options object but precision 0 - #4784', () => {
+    tableSql = client
+      .schemaBuilder()
+      .table('users', (table) => {
+        table.timestamp('foo', { useTz: false, precision: 0 });
+      })
+      .toSQL();
+    equal(1, tableSql.length);
+    expect(tableSql[0].sql).to.equal(
+      'alter table "users" add column "foo" timestamp(0)'
+    );
+  });
+
   it('adding datetime with options object', () => {
     tableSql = client
       .schemaBuilder()
@@ -1567,6 +1580,19 @@ describe('PostgreSQL SchemaBuilder', function () {
     );
   });
 
+  it('adding datetime with options object but precision 0 - #4784', () => {
+    tableSql = client
+      .schemaBuilder()
+      .table('users', (table) => {
+        table.datetime('foo', { useTz: false, precision: 0 });
+      })
+      .toSQL();
+    equal(1, tableSql.length);
+    expect(tableSql[0].sql).to.equal(
+      'alter table "users" add column "foo" timestamp(0)'
+    );
+  });
+
   it('adding timestamps', () => {
     tableSql = client
       .schemaBuilder()