knex/test/integration/query/aggregate.js

754 lines
18 KiB
JavaScript
Raw Permalink Normal View History

'use strict';
const { isMysql, isPostgreSQL } = require('../../util/db-helpers');
const { dropTables, createAccounts } = require('../../util/tableCreatorHelper');
const { insertAccounts } = require('../../util/dataInsertHelper');
2021-03-08 07:16:07 -05:00
2020-04-19 00:40:23 +02:00
module.exports = function (knex) {
describe('Aggregate', function () {
before(async () => {
await dropTables(knex);
await createAccounts(knex);
await insertAccounts(knex);
});
2020-04-19 00:40:23 +02:00
it('has a sum', function () {
return knex('accounts')
.sum('logins')
2020-04-19 00:40:23 +02:00
.testSql(function (tester) {
2013-12-27 14:44:21 -05:00
tester(
'mysql',
'select sum(`logins`) from `accounts`',
[],
[
{
'sum(`logins`)': 10,
},
]
2013-12-27 14:44:21 -05:00
);
tester(
'mysql2',
'select sum(`logins`) from `accounts`',
[],
[
{
'sum(`logins`)': '10',
},
]
);
2013-12-27 14:44:21 -05:00
tester(
'pg',
2013-12-27 14:44:21 -05:00
'select sum("logins") from "accounts"',
[],
[
{
sum: '10',
},
]
2013-12-27 14:44:21 -05:00
);
Add redshift support without changing cli or package.json (#2233) * Add a Redshift dialect that inherits from Postgres. * Turn .index() and .dropIndex() into no-ops with warnings in the Redshift dialect. * Update the Redshift dialect to be compatible with master. * Update package.json * Disable liftoff cli * Remove the CLI * Add lib to the repo * Allow the escaping of named bindings. * Update dist * Update the Redshift dialect’s instantiation of the query and column compilers. * Update the distribution * Fix a merge conflict * Take lib back out * Trying to bring back in line with tgreisser/knex * Add npm 5 package-lock * Bring cli.js back in line * Bring cli.js back in line * Progress commit on redshift integration tests * Revert "Progress commit on redshift integration tests" This reverts commit 207e31635c638853dec54ce0580d34559ba5a54c. * Progress commit * Working not null on primary columns in createTable * Working redshift unit tests * Working unit and integration tests, still need to fix migration tests * Brought datatypes more in line with what redshift actually supports * Added query compiler unit tests * Add a hacky returning clause for redshift ugh * Working migration integration tests * Working insert integration tests * Allow multiple insert returning values * Working select integration tests * Working join integration tests * Working aggregate integration tests * All integration suite tests working * Put docker index for reconnect tests back * Redshift does not support insert...returning, there does not seem to be a way around that, therefore accept it and test accordingly * Leave redshift integration tests in place, but do not run them by default * Fix mysql order by test * Fix more tests * Change test db name to knex_test for consistency * Address PR comments
2018-02-03 08:33:02 -05:00
tester(
'pg-redshift',
'select sum("logins") from "accounts"',
[],
[
{
sum: '10',
},
]
Add redshift support without changing cli or package.json (#2233) * Add a Redshift dialect that inherits from Postgres. * Turn .index() and .dropIndex() into no-ops with warnings in the Redshift dialect. * Update the Redshift dialect to be compatible with master. * Update package.json * Disable liftoff cli * Remove the CLI * Add lib to the repo * Allow the escaping of named bindings. * Update dist * Update the Redshift dialect’s instantiation of the query and column compilers. * Update the distribution * Fix a merge conflict * Take lib back out * Trying to bring back in line with tgreisser/knex * Add npm 5 package-lock * Bring cli.js back in line * Bring cli.js back in line * Progress commit on redshift integration tests * Revert "Progress commit on redshift integration tests" This reverts commit 207e31635c638853dec54ce0580d34559ba5a54c. * Progress commit * Working not null on primary columns in createTable * Working redshift unit tests * Working unit and integration tests, still need to fix migration tests * Brought datatypes more in line with what redshift actually supports * Added query compiler unit tests * Add a hacky returning clause for redshift ugh * Working migration integration tests * Working insert integration tests * Allow multiple insert returning values * Working select integration tests * Working join integration tests * Working aggregate integration tests * All integration suite tests working * Put docker index for reconnect tests back * Redshift does not support insert...returning, there does not seem to be a way around that, therefore accept it and test accordingly * Leave redshift integration tests in place, but do not run them by default * Fix mysql order by test * Fix more tests * Change test db name to knex_test for consistency * Address PR comments
2018-02-03 08:33:02 -05:00
);
2013-12-27 14:44:21 -05:00
tester(
'sqlite3',
'select sum(`logins`) from `accounts`',
2013-12-27 14:44:21 -05:00
[],
[
{
'sum(`logins`)': 10,
},
]
2013-12-27 14:44:21 -05:00
);
tester(
'oracledb',
'select sum("logins") from "accounts"',
[],
[
{
'SUM("LOGINS")': 10,
},
]
);
tester(
'mssql',
'select sum([logins]) from [accounts]',
[],
[
{
'': 10,
},
]
);
});
});
2020-04-19 00:40:23 +02:00
it('supports sum with an alias', function () {
return knex('accounts')
2019-07-23 18:00:13 +02:00
.sum('logins', { as: 'login_sum' })
2020-04-19 00:40:23 +02:00
.testSql(function (tester) {
tester(
'mysql',
'select sum(`logins`) as `login_sum` from `accounts`',
[],
[
{
login_sum: 10,
},
]
);
tester(
'mysql2',
'select sum(`logins`) as `login_sum` from `accounts`',
[],
[
{
login_sum: '10',
},
]
);
tester(
'pg',
'select sum("logins") as "login_sum" from "accounts"',
[],
[
{
login_sum: '10',
},
]
);
tester(
'pg-redshift',
'select sum("logins") as "login_sum" from "accounts"',
[],
[
{
login_sum: '10',
},
]
);
tester(
'sqlite3',
'select sum(`logins`) as `login_sum` from `accounts`',
[],
[
{
login_sum: 10,
},
]
);
tester(
'oracledb',
'select sum("logins") "login_sum" from "accounts"',
[],
[
{
login_sum: 10,
},
]
);
tester(
'mssql',
'select sum([logins]) as [login_sum] from [accounts]',
[],
[
{
login_sum: 10,
},
]
);
});
});
2020-04-19 00:40:23 +02:00
it('supports sum through object containing multiple aliases', function () {
return knex('accounts')
2019-07-23 18:00:13 +02:00
.sum({ login_sum: 'logins', balance_sum: 'balance' })
2020-04-19 00:40:23 +02:00
.testSql(function (tester) {
tester(
'mysql',
'select sum(`logins`) as `login_sum`, sum(`balance`) as `balance_sum` from `accounts`',
[],
[
{
balance_sum: 0,
login_sum: 10,
},
]
);
tester(
'mysql2',
'select sum(`logins`) as `login_sum`, sum(`balance`) as `balance_sum` from `accounts`',
[],
[
{
balance_sum: 0,
login_sum: '10',
},
]
);
tester(
'pg',
'select sum("logins") as "login_sum", sum("balance") as "balance_sum" from "accounts"',
[],
[
{
balance_sum: 0,
login_sum: '10',
},
]
);
tester(
'pg-redshift',
'select sum("logins") as "login_sum", sum("balance") as "balance_sum" from "accounts"',
[],
[
{
balance_sum: '0',
login_sum: '10',
},
]
);
tester(
'sqlite3',
'select sum(`logins`) as `login_sum`, sum(`balance`) as `balance_sum` from `accounts`',
[],
[
{
balance_sum: 0,
login_sum: 10,
},
]
);
tester(
'oracledb',
'select sum("logins") "login_sum", sum("balance") "balance_sum" from "accounts"',
[],
[
{
balance_sum: 0,
login_sum: 10,
},
]
);
tester(
'mssql',
'select sum([logins]) as [login_sum], sum([balance]) as [balance_sum] from [accounts]',
[],
[
{
balance_sum: 0,
login_sum: 10,
},
]
);
});
});
2020-04-19 00:40:23 +02:00
it('has an avg', function () {
return knex('accounts')
.avg('logins')
2020-04-19 00:40:23 +02:00
.testSql(function (tester) {
function checkResRange(key, resp) {
return Math.abs(10 / 6 - +resp[0][key]) < 0.001;
}
function checkResRangeMssql(key, resp) {
return +resp[0][key] === 1;
}
// mysql: 1.6667
tester(
'mysql',
'select avg(`logins`) from `accounts`',
[],
checkResRange.bind(null, 'avg(`logins`)')
);
// sqlite: 1.6666666666666667
tester(
'sqlite3',
'select avg(`logins`) from `accounts`',
[],
checkResRange.bind(null, 'avg(`logins`)')
);
// pg: '1.6666666666666667'
tester(
'pg',
'select avg("logins") from "accounts"',
[],
checkResRange.bind(null, 'avg')
);
// pg-redshift: '1.6666666666666667'
tester(
'pg-redshift',
'select avg("logins") from "accounts"',
[],
checkResRangeMssql.bind(null, 'avg')
);
// oracle: 1.66666666666667
tester(
'oracledb',
'select avg("logins") from "accounts"',
[],
checkResRange.bind(null, 'AVG("LOGINS")')
);
// mssql: 1
tester(
'mssql',
'select avg([logins]) from [accounts]',
[],
checkResRangeMssql.bind(null, '')
);
});
});
2020-04-19 00:40:23 +02:00
it('has a count', function () {
return knex('accounts')
.count('id')
2020-04-19 00:40:23 +02:00
.testSql(function (tester) {
tester(
'mysql',
'select count(`id`) from `accounts`',
[],
[
{
'count(`id`)': 6,
},
]
);
tester(
'pg',
'select count("id") from "accounts"',
[],
[
{
count: '6',
},
]
);
tester(
'pg-redshift',
'select count("id") from "accounts"',
[],
[
{
count: '6',
},
]
);
tester(
'sqlite3',
'select count(`id`) from `accounts`',
[],
[
{
'count(`id`)': 6,
},
]
);
tester(
'oracledb',
'select count("id") from "accounts"',
[],
[
{
'COUNT("ID")': 6,
},
]
);
tester(
'mssql',
'select count([id]) from [accounts]',
[],
[
{
'': 6,
},
]
);
});
});
2020-04-19 00:40:23 +02:00
it('supports multiple aggregate functions', function () {
return knex('accounts')
.count('id')
.max('logins')
.min('logins')
2020-04-19 00:40:23 +02:00
.testSql(function (tester) {
tester(
'mysql',
'select count(`id`), max(`logins`), min(`logins`) from `accounts`',
[],
[
{
'count(`id`)': 6,
'max(`logins`)': 2,
'min(`logins`)': 1,
},
]
);
tester(
'pg',
'select count("id"), max("logins"), min("logins") from "accounts"',
[],
[
{
count: '6',
max: 2,
min: 1,
},
]
);
tester(
'pg-redshift',
'select count("id"), max("logins"), min("logins") from "accounts"',
[],
[
{
count: '6',
max: 2,
min: 1,
},
]
);
tester(
'sqlite3',
'select count(`id`), max(`logins`), min(`logins`) from `accounts`',
[],
[
{
'count(`id`)': 6,
'max(`logins`)': 2,
'min(`logins`)': 1,
},
]
);
tester(
'oracledb',
'select count("id"), max("logins"), min("logins") from "accounts"',
[],
[
{
'COUNT("ID")': 6,
'MAX("LOGINS")': 2,
'MIN("LOGINS")': 1,
},
]
);
tester(
'mssql',
'select count([id]), max([logins]), min([logins]) from [accounts]',
[],
[
{
'': [6, 2, 1],
},
]
);
});
});
2020-04-19 00:40:23 +02:00
it('has distinct modifier for aggregates', function () {
return knex('accounts')
.countDistinct('id')
.sumDistinct('logins')
.avgDistinct('logins')
2020-04-19 00:40:23 +02:00
.testSql(function (tester) {
tester(
'mysql',
'select count(distinct `id`), sum(distinct `logins`), avg(distinct `logins`) from `accounts`',
[],
[
{
'count(distinct `id`)': 6,
'sum(distinct `logins`)': 3,
'avg(distinct `logins`)': 1.5,
},
]
);
tester(
'pg',
'select count(distinct "id"), sum(distinct "logins"), avg(distinct "logins") from "accounts"',
[],
[
{
count: '6',
sum: '3',
avg: '1.5000000000000000',
},
]
);
tester(
Add redshift support without changing cli or package.json (#2233) * Add a Redshift dialect that inherits from Postgres. * Turn .index() and .dropIndex() into no-ops with warnings in the Redshift dialect. * Update the Redshift dialect to be compatible with master. * Update package.json * Disable liftoff cli * Remove the CLI * Add lib to the repo * Allow the escaping of named bindings. * Update dist * Update the Redshift dialect’s instantiation of the query and column compilers. * Update the distribution * Fix a merge conflict * Take lib back out * Trying to bring back in line with tgreisser/knex * Add npm 5 package-lock * Bring cli.js back in line * Bring cli.js back in line * Progress commit on redshift integration tests * Revert "Progress commit on redshift integration tests" This reverts commit 207e31635c638853dec54ce0580d34559ba5a54c. * Progress commit * Working not null on primary columns in createTable * Working redshift unit tests * Working unit and integration tests, still need to fix migration tests * Brought datatypes more in line with what redshift actually supports * Added query compiler unit tests * Add a hacky returning clause for redshift ugh * Working migration integration tests * Working insert integration tests * Allow multiple insert returning values * Working select integration tests * Working join integration tests * Working aggregate integration tests * All integration suite tests working * Put docker index for reconnect tests back * Redshift does not support insert...returning, there does not seem to be a way around that, therefore accept it and test accordingly * Leave redshift integration tests in place, but do not run them by default * Fix mysql order by test * Fix more tests * Change test db name to knex_test for consistency * Address PR comments
2018-02-03 08:33:02 -05:00
'pg-redshift',
'select count(distinct "id"), sum(distinct "logins"), avg(distinct "logins") from "accounts"',
[],
[
{
count: '6',
sum: '3',
avg: '1',
},
]
);
tester(
'sqlite3',
'select count(distinct `id`), sum(distinct `logins`), avg(distinct `logins`) from `accounts`',
[],
[
{
'count(distinct `id`)': 6,
'sum(distinct `logins`)': 3,
'avg(distinct `logins`)': 1.5,
},
]
);
tester(
'oracledb',
'select count(distinct "id"), sum(distinct "logins"), avg(distinct "logins") from "accounts"',
[],
[
{
'COUNT(DISTINCT"ID")': 6,
'SUM(DISTINCT"LOGINS")': 3,
'AVG(DISTINCT"LOGINS")': 1.5,
},
]
);
tester(
'mssql',
'select count(distinct [id]), sum(distinct [logins]), avg(distinct [logins]) from [accounts]',
[],
[
{
'': [6, 3, 1],
},
]
);
});
2013-11-23 12:10:01 -05:00
});
2021-03-08 07:16:07 -05:00
const testWithMultipleColumns = isMysql(knex) || isPostgreSQL(knex);
2020-04-19 00:40:23 +02:00
it('supports countDistinct with multiple columns', function () {
if (!testWithMultipleColumns) {
return this.skip();
}
return knex('accounts')
.countDistinct('id', 'logins')
2020-04-19 00:40:23 +02:00
.testSql(function (tester) {
tester(
'mysql',
'select count(distinct `id`, `logins`) from `accounts`',
[],
[
{
'count(distinct `id`, `logins`)': 6,
},
]
);
tester(
'pg',
'select count(distinct("id", "logins")) from "accounts"',
[],
[
{
count: '6',
},
]
);
});
});
2020-04-19 00:40:23 +02:00
it('supports countDistinct with multiple columns with alias', function () {
if (!testWithMultipleColumns) {
return this.skip();
}
return knex('accounts')
.countDistinct({ count: ['id', 'logins'] })
2020-04-19 00:40:23 +02:00
.testSql(function (tester) {
tester(
'mysql',
'select count(distinct `id`, `logins`) as `count` from `accounts`',
[],
[
{
count: 6,
},
]
);
tester(
'pg',
'select count(distinct("id", "logins")) as "count" from "accounts"',
[],
[
{
count: '6',
},
]
);
});
});
2020-04-19 00:40:23 +02:00
it('support the groupBy function', function () {
return knex('accounts')
.count('id')
.groupBy('logins')
.orderBy('logins', 'asc')
2020-04-19 00:40:23 +02:00
.testSql(function (tester) {
2013-12-27 14:44:21 -05:00
tester(
'mysql',
'select count(`id`) from `accounts` group by `logins` order by `logins` asc',
2013-12-27 14:44:21 -05:00
[],
[
{
'count(`id`)': 2,
},
{
'count(`id`)': 4,
},
]
2013-12-27 14:44:21 -05:00
);
tester(
'pg',
'select count("id") from "accounts" group by "logins" order by "logins" asc',
2013-12-27 14:44:21 -05:00
[],
[
{
count: '2',
},
{
count: '4',
},
]
2013-12-27 14:44:21 -05:00
);
Add redshift support without changing cli or package.json (#2233) * Add a Redshift dialect that inherits from Postgres. * Turn .index() and .dropIndex() into no-ops with warnings in the Redshift dialect. * Update the Redshift dialect to be compatible with master. * Update package.json * Disable liftoff cli * Remove the CLI * Add lib to the repo * Allow the escaping of named bindings. * Update dist * Update the Redshift dialect’s instantiation of the query and column compilers. * Update the distribution * Fix a merge conflict * Take lib back out * Trying to bring back in line with tgreisser/knex * Add npm 5 package-lock * Bring cli.js back in line * Bring cli.js back in line * Progress commit on redshift integration tests * Revert "Progress commit on redshift integration tests" This reverts commit 207e31635c638853dec54ce0580d34559ba5a54c. * Progress commit * Working not null on primary columns in createTable * Working redshift unit tests * Working unit and integration tests, still need to fix migration tests * Brought datatypes more in line with what redshift actually supports * Added query compiler unit tests * Add a hacky returning clause for redshift ugh * Working migration integration tests * Working insert integration tests * Allow multiple insert returning values * Working select integration tests * Working join integration tests * Working aggregate integration tests * All integration suite tests working * Put docker index for reconnect tests back * Redshift does not support insert...returning, there does not seem to be a way around that, therefore accept it and test accordingly * Leave redshift integration tests in place, but do not run them by default * Fix mysql order by test * Fix more tests * Change test db name to knex_test for consistency * Address PR comments
2018-02-03 08:33:02 -05:00
tester(
'pg-redshift',
'select count("id") from "accounts" group by "logins" order by "logins" asc',
Add redshift support without changing cli or package.json (#2233) * Add a Redshift dialect that inherits from Postgres. * Turn .index() and .dropIndex() into no-ops with warnings in the Redshift dialect. * Update the Redshift dialect to be compatible with master. * Update package.json * Disable liftoff cli * Remove the CLI * Add lib to the repo * Allow the escaping of named bindings. * Update dist * Update the Redshift dialect’s instantiation of the query and column compilers. * Update the distribution * Fix a merge conflict * Take lib back out * Trying to bring back in line with tgreisser/knex * Add npm 5 package-lock * Bring cli.js back in line * Bring cli.js back in line * Progress commit on redshift integration tests * Revert "Progress commit on redshift integration tests" This reverts commit 207e31635c638853dec54ce0580d34559ba5a54c. * Progress commit * Working not null on primary columns in createTable * Working redshift unit tests * Working unit and integration tests, still need to fix migration tests * Brought datatypes more in line with what redshift actually supports * Added query compiler unit tests * Add a hacky returning clause for redshift ugh * Working migration integration tests * Working insert integration tests * Allow multiple insert returning values * Working select integration tests * Working join integration tests * Working aggregate integration tests * All integration suite tests working * Put docker index for reconnect tests back * Redshift does not support insert...returning, there does not seem to be a way around that, therefore accept it and test accordingly * Leave redshift integration tests in place, but do not run them by default * Fix mysql order by test * Fix more tests * Change test db name to knex_test for consistency * Address PR comments
2018-02-03 08:33:02 -05:00
[],
[
{
count: '2',
},
{
count: '4',
},
]
Add redshift support without changing cli or package.json (#2233) * Add a Redshift dialect that inherits from Postgres. * Turn .index() and .dropIndex() into no-ops with warnings in the Redshift dialect. * Update the Redshift dialect to be compatible with master. * Update package.json * Disable liftoff cli * Remove the CLI * Add lib to the repo * Allow the escaping of named bindings. * Update dist * Update the Redshift dialect’s instantiation of the query and column compilers. * Update the distribution * Fix a merge conflict * Take lib back out * Trying to bring back in line with tgreisser/knex * Add npm 5 package-lock * Bring cli.js back in line * Bring cli.js back in line * Progress commit on redshift integration tests * Revert "Progress commit on redshift integration tests" This reverts commit 207e31635c638853dec54ce0580d34559ba5a54c. * Progress commit * Working not null on primary columns in createTable * Working redshift unit tests * Working unit and integration tests, still need to fix migration tests * Brought datatypes more in line with what redshift actually supports * Added query compiler unit tests * Add a hacky returning clause for redshift ugh * Working migration integration tests * Working insert integration tests * Allow multiple insert returning values * Working select integration tests * Working join integration tests * Working aggregate integration tests * All integration suite tests working * Put docker index for reconnect tests back * Redshift does not support insert...returning, there does not seem to be a way around that, therefore accept it and test accordingly * Leave redshift integration tests in place, but do not run them by default * Fix mysql order by test * Fix more tests * Change test db name to knex_test for consistency * Address PR comments
2018-02-03 08:33:02 -05:00
);
2013-12-27 14:44:21 -05:00
tester(
'sqlite3',
'select count(`id`) from `accounts` group by `logins` order by `logins` asc',
2013-12-27 14:44:21 -05:00
[],
[
{
'count(`id`)': 2,
},
{
'count(`id`)': 4,
},
]
2013-12-27 14:44:21 -05:00
);
tester(
'oracledb',
'select count("id") from "accounts" group by "logins" order by "logins" asc',
[],
[
{
'COUNT("ID")': 2,
},
{
'COUNT("ID")': 4,
},
]
);
tester(
'mssql',
'select count([id]) from [accounts] group by [logins] order by [logins] asc',
[],
[
{
'': 2,
},
{
'': 4,
},
]
);
})
2020-04-19 00:40:23 +02:00
.then(function () {
return knex('accounts')
.count('id')
.groupBy('first_name')
2020-04-19 00:40:23 +02:00
.testSql(function (tester) {
tester(
'mysql',
'select count(`id`) from `accounts` group by `first_name`',
[],
[
{
'count(`id`)': 6,
},
]
);
tester(
'pg',
'select count("id") from "accounts" group by "first_name"',
[],
[
{
count: '6',
},
]
);
tester(
'pg-redshift',
'select count("id") from "accounts" group by "first_name"',
[],
[
{
count: '6',
},
]
);
tester(
'sqlite3',
'select count(`id`) from `accounts` group by `first_name`',
[],
[
{
'count(`id`)': 6,
},
]
);
tester(
'oracledb',
'select count("id") from "accounts" group by "first_name"',
[],
[
{
'COUNT("ID")': 6,
},
]
);
tester(
'mssql',
'select count([id]) from [accounts] group by [first_name]',
[],
[
{
'': 6,
},
]
);
});
});
});
});
};