import { jest, expect, beforeAll, afterAll } from '@jest/globals'; import { randomBytes } from 'crypto'; import { get } from 'env-var'; import { Client as PgClient } from 'pg'; import { BaseDriver } from '@cubejs-backend/base-driver'; import cubejs, { CubeApi } from '@cubejs-client/core'; import { sign } from 'jsonwebtoken'; import { Environment } from '../types/Environment'; import { getFixtures, getCreateQueries, getRefreshQueries, getDriver, runEnvironment, buildPreaggs, } from '../helpers'; import { incrementalSchemaLoadingSuite } from './testIncrementalSchemaLoading'; import { redshiftExternalSchemasSuite } from './testExternalSchemas'; type TestQueriesOptions = { includeIncrementalSchemaSuite?: boolean, includeHLLSuite?: boolean, includeInDatabasePreAggregationSuite?: boolean, extendedEnv?: string externalSchemaTests?: boolean, }; export const delay = (ms: number) => new Promise((resolve) => setTimeout(resolve, ms)); const OP_DELAY = 1000; export function testQueries(type: string, { includeIncrementalSchemaSuite, extendedEnv, includeHLLSuite, includeInDatabasePreAggregationSuite, externalSchemaTests }: TestQueriesOptions = {}): void { describe(`Queries with the @cubejs-backend/${type}-driver${extendedEnv ? ` ${extendedEnv}` : ''}`, () => { if (type.includes('redshift')) { jest.setTimeout(60 * 15 * 1000); } else { jest.setTimeout(60 * 5 * 1000); } const isTesseractEnv = get('DRIVERS_TESTS_CUBEJS_TESSERACT_SQL_PLANNER').default('false').asBool(); const fixtures = getFixtures(type, extendedEnv); let client: CubeApi; let driver: BaseDriver; let queries: string[]; let env: Environment; let connectionId = 0; async function createPostgresClient(user: string = 'admin', password: string = 'admin_password', pgPort: number | undefined = env.cube.pgPort) { if (!pgPort) { throw new Error('port must be defined'); } connectionId++; const currentConnId = connectionId; console.debug(`[pg] new connection ${currentConnId}`); const conn = new PgClient({ database: 'db', port: pgPort, host: '127.0.0.1', user, password, ssl: false, }); conn.on('error', (err) => { console.log(`[pg] #${currentConnId}`, err); }); conn.on('end', () => { console.debug(`[pg] #${currentConnId} end`); }); await conn.connect(); return conn; } function execute(name: string, test: () => Promise) { if (!isTesseractEnv && fixtures.skip && fixtures.skip.indexOf(name) >= 0) { it.skip(name, test); } else if (isTesseractEnv && fixtures.tesseractSkip && fixtures.tesseractSkip.indexOf(name) >= 0) { it.skip(name, test); } else { it(name, test); } } function executePg(name: string, test: (connection: PgClient) => Promise) { if (!isTesseractEnv && fixtures.skip && fixtures.skip.indexOf(name) >= 0) { it.skip(name, () => { // nothing to do }); } else if (isTesseractEnv && fixtures.tesseractSkip && fixtures.tesseractSkip.indexOf(name) >= 0) { it.skip(name, () => { // nothing to do }); } else { it(name, async () => { const connection = await createPostgresClient(); try { await test(connection); } finally { await connection.end(); if (type.includes('redshift')) { await delay(OP_DELAY); } } }); } } const apiToken = sign({}, 'mysupersecret'); // Pinot uses a fixed suffix so the model lines up with the committed // `_pinot` resources; every other driver isolates runs with random hex. const suffix = type === 'pinot' ? 'pinot' : randomBytes(8).toString('hex'); const tables = Object .keys(fixtures.tables) .map((key: string) => `${fixtures.tables[key]}_${suffix}`); beforeAll(async () => { env = await runEnvironment(type, suffix, { extendedEnv }); process.env.CUBEJS_REFRESH_WORKER = 'true'; process.env.CUBEJS_CUBESTORE_HOST = '127.0.0.1'; process.env.CUBEJS_CUBESTORE_PORT = `${env.store.port}`; process.env.CUBEJS_CUBESTORE_USER = 'root'; process.env.CUBEJS_CUBESTORE_PASS = 'root'; process.env.CUBEJS_CACHE_AND_QUEUE_DRIVER = 'cubestore'; // memory if (env.data) { process.env.CUBEJS_DB_HOST = type === 'pinot' ? 'http://127.0.0.1' : '127.0.0.1'; process.env.CUBEJS_DB_PORT = `${env.data.port}`; } client = cubejs(apiToken, { apiUrl: `http://127.0.0.1:${env.cube.port}/cubejs-api/v1`, }); driver = (await getDriver(type)).source; // Pinot has no SQL DDL — runEnvironment already ingested the fixture tables // via the controller. Every other driver seeds via CREATE TABLE here. if (type !== 'pinot') { queries = getCreateQueries(type, suffix); console.log(`Creating ${queries.length} fixture tables`); try { for (const q of queries) { await driver.createTableRaw(q); if (type.includes('redshift')) { await delay(10 * OP_DELAY); } } // CrateDB is eventually consistent: make the freshly loaded rows visible // before any queries run against the fixture tables. if (type === 'crate') { for (const q of getRefreshQueries(type, suffix)) { await driver.query(q); } } console.log(`Creating ${queries.length} fixture tables completed`); } catch (e: any) { console.log('Error creating fixtures', e.stack); throw e; } } }); afterAll(async () => { try { // Pinot has no dropTable; the cluster is torn down with the environment. if (type !== 'pinot') { console.log(`Dropping ${tables.length} fixture tables`); for (const t of tables) { await driver.dropTable(t); } console.log(`Dropping ${tables.length} fixture tables completed`); } } finally { await driver.release(); await env.stop(); } }); // MUST be the first test in the list! execute('must built pre-aggregations', async () => { await buildPreaggs(env.cube.port, apiToken, { timezones: ['UTC'], preAggregations: ['Customers.RAExternal'], contexts: [{ securityContext: { tenant: 't1' } }], }); await delay(OP_DELAY); await buildPreaggs(env.cube.port, apiToken, { timezones: ['UTC'], preAggregations: ['ECommerce.SAExternal'], contexts: [{ securityContext: { tenant: 't1' } }], }); await delay(OP_DELAY); await buildPreaggs(env.cube.port, apiToken, { timezones: ['UTC'], preAggregations: ['ECommerce.TAExternal'], contexts: [{ securityContext: { tenant: 't1' } }], }); await delay(OP_DELAY); await buildPreaggs(env.cube.port, apiToken, { timezones: ['UTC'], preAggregations: ['BigECommerce.TAExternal'], contexts: [{ securityContext: { tenant: 't1' } }], }); await delay(OP_DELAY); await buildPreaggs(env.cube.port, apiToken, { timezones: ['UTC'], preAggregations: ['BigECommerce.MultiTimeDimForCountExternal'], contexts: [{ securityContext: { tenant: 't1' } }], }); await delay(OP_DELAY); if (type !== 'clickhouse') { // ClickHouse cannot build this non-partitioned rollup; the // corresponding test is skipped in both skip lists for clickhouse. await buildPreaggs(env.cube.port, apiToken, { timezones: ['UTC'], preAggregations: ['BigECommerce.CategoryFlatExternal'], contexts: [{ securityContext: { tenant: 't1' } }], }); await delay(OP_DELAY); } if (includeHLLSuite) { await buildPreaggs(env.cube.port, apiToken, { timezones: ['UTC'], preAggregations: ['BigECommerce.CountByProductExternal'], contexts: [{ securityContext: { tenant: 't1' } }], }); await delay(OP_DELAY); } // Stores calendar-shifted measures, whose build runs the shifted joins. // Only the native planner serves a multi-stage measure from a rollup, and // only the fixtures that declare this rollup build it. if (isTesseractEnv && fixtures.preAggregations?.BigECommerce?.some((pa) => pa.name === 'RetailPriorPeriodsByWeek')) { await buildPreaggs(env.cube.port, apiToken, { timezones: ['UTC'], preAggregations: ['BigECommerce.RetailPriorPeriodsByWeekExternal'], contexts: [{ securityContext: { tenant: 't1' } }], }); await delay(OP_DELAY); } // Stores a named calendar shift by product and day for queries that // roll it up to the category. if (isTesseractEnv && fixtures.preAggregations?.BigECommerce?.some((pa) => pa.name === 'RetailPriorMonthByProductDay')) { await buildPreaggs(env.cube.port, apiToken, { timezones: ['UTC'], preAggregations: ['BigECommerce.RetailPriorMonthByProductDayExternal'], contexts: [{ securityContext: { tenant: 't1' } }], }); await delay(OP_DELAY); } // Exercise pre-aggregation build with a custom granularity. The // granularity name `build_only_half_year` is unique to this rollup — no // query test references it, so the rollup cannot match any test query and // only the build path is exercised. // QuestDB is skipped: its dialect has no date_bin implementation, so // custom time-dimension granularities cannot be materialized (the // corresponding query cases are skipped in fixtures/questdb.json too). if (type !== 'questdb') { await buildPreaggs(env.cube.port, apiToken, { timezones: ['UTC'], preAggregations: ['ECommerce.TBuildOnlyHalfYearExternal'], contexts: [{ securityContext: { tenant: 't1' } }], }); await delay(OP_DELAY); } }); execute('must not fetch a hidden cube', async () => { const meta = await client.meta(); expect(meta.cubes.find(cube => cube.name === 'HiddenECommerce')).toBe(undefined); }); execute('must throw if a hidden member was requested', async () => { const promise = async () => { await client.load({ measures: [ 'ECommerce.hiddenSum' ] }); }; promise().catch(e => { expect(e.toString()).toMatch(/hidden/); }); }); if (includeInDatabasePreAggregationSuite) { // Queries keep reading the external copies of these rollups, so the rows built in the source // database are read back with the raw driver and checked against the query results. execute('must build an in-database pre-aggregation', async () => { await buildPreaggs(env.cube.port, apiToken, { timezones: ['UTC'], preAggregations: ['ECommerce.SAInternal', 'ECommerce.TAInternal'], contexts: [{ securityContext: { tenant: 't1' } }], }); const schema = fixtures.cube.environment.CUBEJS_PRE_AGGREGATIONS_SCHEMA; const schemaTables = (await driver.getTablesQuery(schema)).map(t => t.table_name || t.TABLE_NAME); // Sums the quantity per product over every table (partition) built for the rollup. const builtQuantities = async (marker: string) => { const builtTables = schemaTables.filter(name => name?.includes(marker)); const quantities: Record = {}; for (const table of builtTables) { const rows = await driver.query>(`SELECT * FROM ${schema}.${table}`); for (const row of rows) { const column = (ending: string) => Object.entries(row).find(([k]) => k.toLowerCase().endsWith(ending))?.[1]; const product = String(column('__product_name')); quantities[product] = (quantities[product] ?? 0) + Number(column('__total_quantity')); } } return { tableCount: builtTables.length, quantities }; }; const plain = await builtQuantities('__s_a_internal_'); expect(plain.tableCount).toBe(1); const partitioned = await builtQuantities('__t_a_internal'); expect(partitioned.tableCount).toBeGreaterThan(1); const response = await client.load({ dimensions: ['ECommerce.productName'], measures: ['ECommerce.totalQuantity'], }); const expected = Object.fromEntries(response.rawData().map(row => [ row['ECommerce.productName'], Number(row['ECommerce.totalQuantity']), ])); expect(Object.keys(expected).length).toBeGreaterThan(0); expect(plain.quantities).toEqual(expected); expect(partitioned.quantities).toEqual(expected); }); } // https://github.com/cube-js/cube/issues/10601 execute('querying ECommerce: dimensions + filter-only by TD to get empty results', async () => { const response = await client.load({ dimensions: [ 'ECommerce.city', ], timeDimensions: [{ dimension: 'ECommerce.orderDate', dateRange: ['2099-01-01', '2099-12-31'], }], }); expect(response.rawData()).toEqual([]); }); execute('querying Customers: dimensions', async () => { const response = await client.load({ dimensions: [ 'Customers.customerId', 'Customers.customerName' ], }); expect(response.rawData()).toMatchSnapshot(); }); execute('querying Customers: dimensions + order', async () => { const response = await client.load({ dimensions: [ 'Customers.customerId', 'Customers.customerName' ], order: { 'Customers.customerId': 'asc', } }); expect(response.rawData()).toMatchSnapshot(); }); execute('querying Customers: dimensions + limit', async () => { const response = await client.load({ dimensions: [ 'Customers.customerId', 'Customers.customerName' ], limit: 10 }); expect(response.rawData()).toMatchSnapshot(); expect(response.rawData().length).toEqual(10); }); execute('querying Customers: dimensions + total', async () => { const response = await client.load({ dimensions: [ 'Customers.customerId', 'Customers.customerName' ], total: true }); expect(response.rawData()).toMatchSnapshot(); expect( response.serialize().loadResponse.results[0].total ).toEqual(41); }); execute('querying Customers: dimensions + order + limit + total', async () => { const response = await client.load({ dimensions: [ 'Customers.customerId', 'Customers.customerName' ], order: { 'Customers.customerName': 'asc' }, limit: 10, total: true }); expect(response.rawData()).toMatchSnapshot(); expect(response.rawData().length).toEqual(10); expect( response.serialize().loadResponse.results[0].total ).toEqual(41); }); execute('querying Customers: dimensions + order + total + offset', async () => { const response = await client.load({ dimensions: [ 'Customers.customerId', 'Customers.customerName' ], order: { 'Customers.customerName': 'asc' }, total: true, offset: 40 }); expect(response.rawData()).toMatchSnapshot(); expect(response.rawData().length).toEqual(1); expect( response.serialize().loadResponse.results[0].total ).toEqual(41); }); execute('querying Customers: dimensions + order + limit + total + offset', async () => { const response = await client.load({ dimensions: [ 'Customers.customerId', 'Customers.customerName' ], order: { 'Customers.customerName': 'asc' }, limit: 10, total: true, offset: 10 }); expect(response.rawData()).toMatchSnapshot(); expect(response.rawData().length).toEqual(10); expect( response.serialize().loadResponse.results[0].total ).toEqual(41); }); execute('filtering Customers: contains + dimensions, first', async () => { const response = await client.load({ dimensions: [ 'Customers.customerId', 'Customers.customerName' ], filters: [ { member: 'Customers.customerName', operator: 'contains', values: ['tom'], }, ], }); expect(response.rawData()).toMatchSnapshot(); }); execute('filtering Customers: contains + dimensions, second', async () => { const response = await client.load({ dimensions: [ 'Customers.customerId', 'Customers.customerName' ], filters: [ { member: 'Customers.customerName', operator: 'contains', values: ['us', 'om'], }, ], }); expect(response.rawData()).toMatchSnapshot(); }); execute('filtering Customers: contains + dimensions, third', async () => { const response = await client.load({ dimensions: [ 'Customers.customerId', 'Customers.customerName' ], filters: [ { member: 'Customers.customerName', operator: 'contains', values: ['non'], }, ], }); expect(response.rawData()).toMatchSnapshot(); }); execute('filtering Products: contains with special chars + dimensions', async () => { const response = await client.load({ dimensions: [ 'Products.productName' ], filters: [ { member: 'Products.productName', operator: 'contains', values: ['di_Novo'], }, ], }); expect(response.rawData()).toMatchSnapshot(); }); // A `%` or `_` typed by a user is a literal, not a LIKE wildcard. Each // dialect gets there differently - some rely on backslash being the default // escape character, others have to emit an explicit ESCAPE clause - so this // only means anything when it runs against the real engine. // // The pairs below run the same filter twice: once against a cube with no // pre-aggregations, so it reaches the database, and once through a query the // ECommerce `SA` rollup serves, so the filter SQL is generated for the // rollup store instead. Those are two different escaping paths, and the // rollup one is where this last broke in production - a filter that was // correct against the source database changed meaning once a rollup started // answering it. // // These assert exact result sets rather than snapshots deliberately: the // wrong answer here is a *superset*, and a snapshot would have recorded that // superset as expected without anyone noticing. // // Each case also pins which engine answered. Without that the pairing is // only an intention: `contains '%'` returns nothing on both paths, so if the // rollup ever stopped matching, the pre-aggregated cases would keep passing // as duplicates of the ones above and the rollup-store escaping path would // quietly lose its only coverage here. // // `external` is the field that carries this - it is true only when a // pre-aggregation in the rollup store served the query. (`usedPreAggregations` // names the pre-aggregations behind a result, but says nothing about which // engine answered: an internal rollup, which never reaches the rollup store, // shows up there too.) function servedByRollupStore(response: any): boolean { // `loadResponse` is not part of the public ResultSet type. const [result] = response.loadResponse?.results ?? []; return Boolean(result?.external); } execute('filtering Products: contains a literal percent sign (no pre-aggregation)', async () => { const response = await client.load({ dimensions: [ 'Products.productName' ], filters: [ { member: 'Products.productName', operator: 'contains', values: ['%'], }, ], }); // No product name contains a percent sign. An unescaped `%` would make the // pattern `%%%` and match every row. expect(response.rawData()).toEqual([]); expect(servedByRollupStore(response)).toBe(false); }); execute('filtering Products: contains a literal underscore (no pre-aggregation)', async () => { const response = await client.load({ dimensions: [ 'Products.productName' ], filters: [ { member: 'Products.productName', operator: 'contains', values: ['_'], }, ], }); // An unescaped `_` would make the pattern `%_%` and match every non-empty // name; exactly one product has a literal underscore. expect( response.rawData().map((row: any) => row['Products.productName']) ).toEqual(['Logitech di_Novo Edge Keyboard']); expect(servedByRollupStore(response)).toBe(false); }); execute('filtering Products: notContains a literal percent sign (no pre-aggregation)', async () => { const [filtered, all] = await Promise.all([ client.load({ dimensions: ['Products.productName'], filters: [ { member: 'Products.productName', operator: 'notContains', values: ['%'], }, ], }), client.load({ dimensions: ['Products.productName'] }), ]); // The mirror image: an unescaped `%` would exclude every row instead of // none. Compared against the unfiltered query so this does not depend on // the row count of the fixture. expect(filtered.rawData().length).toEqual(all.rawData().length); expect(filtered.rawData().length).toBeGreaterThan(0); expect(servedByRollupStore(filtered)).toBe(false); }); execute('filtering Products: startsWith a literal percent sign (no pre-aggregation)', async () => { const response = await client.load({ dimensions: [ 'Products.productName' ], filters: [ { member: 'Products.productName', operator: 'startsWith', values: ['%'], }, ], }); // `startsWith` only appends the trailing wildcard, so an unescaped `%` // leaves the pattern `%%` and matches every row rather than none. expect(response.rawData()).toEqual([]); expect(servedByRollupStore(response)).toBe(false); }); execute('filtering Products: endsWith a literal percent sign (no pre-aggregation)', async () => { const response = await client.load({ dimensions: [ 'Products.productName' ], filters: [ { member: 'Products.productName', operator: 'endsWith', values: ['%'], }, ], }); // The leading-wildcard mirror of the case above. expect(response.rawData()).toEqual([]); expect(servedByRollupStore(response)).toBe(false); }); execute('filtering ECommerce: notContains a literal percent sign (pre-aggregated)', async () => { const [filtered, all] = await Promise.all([ client.load({ measures: ['ECommerce.totalQuantity'], dimensions: ['ECommerce.productName'], filters: [ { member: 'ECommerce.productName', operator: 'notContains', values: ['%'], }, ], }), client.load({ measures: ['ECommerce.totalQuantity'], dimensions: ['ECommerce.productName'], }), ]); // Same mirror image as the Products case, but rendered by the Cube Store // dialect because the rollup answers it. expect(filtered.rawData().length).toEqual(all.rawData().length); expect(filtered.rawData().length).toBeGreaterThan(0); expect(servedByRollupStore(filtered)).toBe(true); }); execute('filtering ECommerce: contains a literal percent sign (pre-aggregated)', async () => { const response = await client.load({ measures: [ 'ECommerce.totalQuantity' ], dimensions: [ 'ECommerce.productName' ], filters: [ { member: 'ECommerce.productName', operator: 'contains', values: ['%'], }, ], }); expect(response.rawData()).toEqual([]); expect(servedByRollupStore(response)).toBe(true); }); execute('filtering ECommerce: contains a literal underscore (pre-aggregated)', async () => { const response = await client.load({ measures: [ 'ECommerce.totalQuantity' ], dimensions: [ 'ECommerce.productName' ], filters: [ { member: 'ECommerce.productName', operator: 'contains', values: ['_'], }, ], }); expect( response.rawData().map((row: any) => row['ECommerce.productName']) ).toEqual(['Logitech di_Novo Edge Keyboard']); expect(servedByRollupStore(response)).toBe(true); }); execute('filtering Customers: endsWith filter + dimensions, first', async () => { const response = await client.load({ dimensions: [ 'Customers.customerId', 'Customers.customerName' ], filters: [ { member: 'Customers.customerId', operator: 'endsWith', values: ['0'], }, ], }); expect(response.rawData()).toMatchSnapshot(); }); execute('filtering Customers: endsWith filter + dimensions, second', async () => { const response = await client.load({ dimensions: [ 'Customers.customerId', 'Customers.customerName' ], filters: [ { member: 'Customers.customerId', operator: 'endsWith', values: ['0', '5'], }, ], }); expect(response.rawData()).toMatchSnapshot(); }); execute('filtering Customers: endsWith filter + dimensions, third', async () => { const response = await client.load({ dimensions: [ 'Customers.customerId', 'Customers.customerName' ], filters: [ { member: 'Customers.customerId', operator: 'endsWith', values: ['9'], }, ], }); expect(response.rawData()).toMatchSnapshot(); }); execute('filtering Customers: notEndsWith filter + dimensions, first', async () => { const response = await client.load({ dimensions: [ 'Customers.customerId', 'Customers.customerName' ], filters: [ { member: 'Customers.customerId', operator: 'notEndsWith', values: ['0'], }, ], }); expect(response.rawData()).toMatchSnapshot(); }); execute('filtering Customers: notEndsWith filter + dimensions, second', async () => { const response = await client.load({ dimensions: [ 'Customers.customerId', 'Customers.customerName' ], filters: [ { member: 'Customers.customerId', operator: 'notEndsWith', values: ['0', '5'], }, ], }); expect(response.rawData()).toMatchSnapshot(); }); execute('filtering Customers: notEndsWith filter + dimensions, third', async () => { const response = await client.load({ dimensions: [ 'Customers.customerId', 'Customers.customerName' ], filters: [ { member: 'Customers.customerId', operator: 'notEndsWith', values: ['9'], }, ], }); expect(response.rawData()).toMatchSnapshot(); }); execute('filtering Customers: startsWith + dimensions, first', async () => { const response = await client.load({ dimensions: [ 'Customers.customerId', 'Customers.customerName' ], filters: [ { member: 'Customers.customerId', operator: 'startsWith', values: ['A'], }, ], }); expect(response.rawData()).toMatchSnapshot(); }); execute('filtering Customers: startsWith + dimensions, second', async () => { const response = await client.load({ dimensions: [ 'Customers.customerId', 'Customers.customerName' ], filters: [ { member: 'Customers.customerId', operator: 'startsWith', values: ['A', 'B'], }, ], }); expect(response.rawData()).toMatchSnapshot(); }); execute('filtering Customers: startsWith + dimensions, third', async () => { const response = await client.load({ dimensions: [ 'Customers.customerId', 'Customers.customerName' ], filters: [ { member: 'Customers.customerId', operator: 'startsWith', values: ['Z'], }, ], }); expect(response.rawData()).toMatchSnapshot(); }); execute('filtering Customers: notStartsWith + dimensions, first', async () => { const response = await client.load({ dimensions: [ 'Customers.customerId', 'Customers.customerName' ], filters: [ { member: 'Customers.customerId', operator: 'notStartsWith', values: ['A'], }, ], }); expect(response.rawData()).toMatchSnapshot(); }); execute('filtering Customers: notStartsWith + dimensions, second', async () => { const response = await client.load({ dimensions: [ 'Customers.customerId', 'Customers.customerName' ], filters: [ { member: 'Customers.customerId', operator: 'notStartsWith', values: ['A', 'B'], }, ], }); expect(response.rawData()).toMatchSnapshot(); }); execute('filtering Customers: notStartsWith + dimensions, third', async () => { const response = await client.load({ dimensions: [ 'Customers.customerId', 'Customers.customerName' ], filters: [ { member: 'Customers.customerId', operator: 'notStartsWith', values: ['Z'], }, ], }); expect(response.rawData()).toMatchSnapshot(); }); execute('querying Products: dimensions -- doesn\'t work wo ordering', async () => { const response = await client.load({ dimensions: [ 'Products.category', 'Products.subCategory', 'Products.productName' ] }); expect(response.rawData()).toMatchSnapshot(); }); execute('querying Products: dimensions + order', async () => { const response = await client.load({ dimensions: [ 'Products.category', 'Products.subCategory', 'Products.productName' ], order: { 'Products.category': 'asc', 'Products.subCategory': 'asc', 'Products.productName': 'asc' } }); expect(response.rawData()).toMatchSnapshot(); }); execute('querying Products: dimensions + order + limit', async () => { const response = await client.load({ dimensions: [ 'Products.category', 'Products.subCategory', 'Products.productName' ], order: { 'Products.category': 'asc', 'Products.subCategory': 'asc', 'Products.productName': 'asc' }, limit: 10 }); expect(response.rawData()).toMatchSnapshot(); expect(response.rawData().length).toEqual(10); }); execute('querying Products: dimensions + order + total', async () => { const response = await client.load({ dimensions: [ 'Products.category', 'Products.subCategory', 'Products.productName' ], order: { 'Products.category': 'asc', 'Products.subCategory': 'asc', 'Products.productName': 'asc' }, total: true }); expect(response.rawData()).toMatchSnapshot(); expect( response.serialize().loadResponse.results[0].total ).toEqual(28); }); execute('querying Products: dimensions + order + limit + total', async () => { const response = await client.load({ dimensions: [ 'Products.category', 'Products.subCategory', 'Products.productName' ], order: { 'Products.category': 'asc', 'Products.subCategory': 'asc', 'Products.productName': 'asc' }, limit: 10, total: true }); expect(response.rawData()).toMatchSnapshot(); expect(response.rawData().length).toEqual(10); expect( response.serialize().loadResponse.results[0].total ).toEqual(28); }); execute('filtering Products: contains + dimensions + order, first', async () => { const response = await client.load({ dimensions: [ 'Products.category', 'Products.subCategory', 'Products.productName' ], order: { 'Products.category': 'asc', 'Products.subCategory': 'asc', 'Products.productName': 'asc' }, filters: [ { member: 'Products.subCategory', operator: 'contains', values: ['able'], }, ], }); expect(response.rawData()).toMatchSnapshot(); }); execute('filtering Products: contains + dimensions + order, second', async () => { const response = await client.load({ dimensions: [ 'Products.category', 'Products.subCategory', 'Products.productName' ], order: { 'Products.category': 'asc', 'Products.subCategory': 'asc', 'Products.productName': 'asc' }, filters: [ { member: 'Products.subCategory', operator: 'contains', values: ['able', 'urn'], }, ], }); expect(response.rawData()).toMatchSnapshot(); }); execute('filtering Products: contains + dimensions + order, third', async () => { const response = await client.load({ dimensions: [ 'Products.category', 'Products.subCategory', 'Products.productName' ], order: { 'Products.category': 'asc', 'Products.subCategory': 'asc', 'Products.productName': 'asc' }, filters: [ { member: 'Products.subCategory', operator: 'contains', values: ['notexist'], }, ], }); expect(response.rawData()).toMatchSnapshot(); }); execute('filtering Products: startsWith filter + dimensions + order, first', async () => { const response = await client.load({ dimensions: [ 'Products.category', 'Products.subCategory', 'Products.productName' ], order: { 'Products.category': 'asc', 'Products.subCategory': 'asc', 'Products.productName': 'asc' }, filters: [ { member: 'Products.productName', operator: 'startsWith', values: ['O'], }, ], }); expect(response.rawData()).toMatchSnapshot(); }); execute('filtering Products: startsWith filter + dimensions + order, second', async () => { const response = await client.load({ dimensions: [ 'Products.category', 'Products.subCategory', 'Products.productName' ], order: { 'Products.category': 'asc', 'Products.subCategory': 'asc', 'Products.productName': 'asc' }, filters: [ { member: 'Products.productName', operator: 'startsWith', values: ['O', 'K'], }, ], }); expect(response.rawData()).toMatchSnapshot(); }); execute('filtering Products: startsWith filter + dimensions + order, third', async () => { const response = await client.load({ dimensions: [ 'Products.category', 'Products.subCategory', 'Products.productName' ], order: { 'Products.category': 'asc', 'Products.subCategory': 'asc', 'Products.productName': 'asc' }, filters: [ { member: 'Products.productName', operator: 'startsWith', values: ['noneexist'], }, ], }); expect(response.rawData()).toMatchSnapshot(); }); execute('filtering Products: endsWith filter + dimensions + order, first', async () => { const response = await client.load({ dimensions: [ 'Products.category', 'Products.subCategory', 'Products.productName' ], order: { 'Products.category': 'asc', 'Products.subCategory': 'asc', 'Products.productName': 'asc' }, filters: [ { member: 'Products.subCategory', operator: 'endsWith', values: ['es'], }, ], }); expect(response.rawData()).toMatchSnapshot(); }); execute('filtering Products: endsWith filter + dimensions + order, second', async () => { const response = await client.load({ dimensions: [ 'Products.category', 'Products.subCategory', 'Products.productName' ], order: { 'Products.category': 'asc', 'Products.subCategory': 'asc', 'Products.productName': 'asc' }, filters: [ { member: 'Products.subCategory', operator: 'endsWith', values: ['es', 'gs'], }, ], }); expect(response.rawData()).toMatchSnapshot(); }); execute('filtering Products: endsWith filter + dimensions + order, third', async () => { const response = await client.load({ dimensions: [ 'Products.category', 'Products.subCategory', 'Products.productName' ], order: { 'Products.category': 'asc', 'Products.subCategory': 'asc', 'Products.productName': 'asc' }, filters: [ { member: 'Products.subCategory', operator: 'endsWith', values: ['noneexist'], }, ], }); expect(response.rawData()).toMatchSnapshot(); }); execute('querying ECommerce: dimensions', async () => { const response = await client.load({ dimensions: [ 'ECommerce.rowId', 'ECommerce.orderId', 'ECommerce.orderDate', 'ECommerce.customerId', 'ECommerce.customerName', 'ECommerce.city', 'ECommerce.category', 'ECommerce.subCategory', 'ECommerce.productName', 'ECommerce.sales', 'ECommerce.quantity', 'ECommerce.discount', 'ECommerce.profit' ] }); expect(response.rawData()).toMatchSnapshot(); }); execute('querying ECommerce: dimensions + order', async () => { const response = await client.load({ dimensions: [ 'ECommerce.rowId', 'ECommerce.orderId', 'ECommerce.orderDate', 'ECommerce.customerId', 'ECommerce.customerName', 'ECommerce.city', 'ECommerce.category', 'ECommerce.subCategory', 'ECommerce.productName', 'ECommerce.sales', 'ECommerce.quantity', 'ECommerce.discount', 'ECommerce.profit' ], order: { 'ECommerce.rowId': 'asc' } }); expect(response.rawData()).toMatchSnapshot(); }); execute('querying ECommerce: dimensions + limit', async () => { const response = await client.load({ dimensions: [ 'ECommerce.rowId', 'ECommerce.orderId', 'ECommerce.orderDate', 'ECommerce.customerId', 'ECommerce.customerName', 'ECommerce.city', 'ECommerce.category', 'ECommerce.subCategory', 'ECommerce.productName', 'ECommerce.sales', 'ECommerce.quantity', 'ECommerce.discount', 'ECommerce.profit' ], limit: 10 }); expect(response.rawData()).toMatchSnapshot(); expect(response.rawData().length).toEqual(10); }); execute('querying ECommerce: dimensions + total', async () => { const response = await client.load({ dimensions: [ 'ECommerce.rowId', 'ECommerce.orderId', 'ECommerce.orderDate', 'ECommerce.customerId', 'ECommerce.customerName', 'ECommerce.city', 'ECommerce.category', 'ECommerce.subCategory', 'ECommerce.productName', 'ECommerce.sales', 'ECommerce.quantity', 'ECommerce.discount', 'ECommerce.profit' ], total: true }); expect(response.rawData()).toMatchSnapshot(); expect( response.serialize().loadResponse.results[0].total ).toEqual(44); }); execute('querying ECommerce: dimensions + order + limit + total', async () => { const response = await client.load({ dimensions: [ 'ECommerce.rowId', 'ECommerce.orderId', 'ECommerce.orderDate', 'ECommerce.customerId', 'ECommerce.customerName', 'ECommerce.city', 'ECommerce.category', 'ECommerce.subCategory', 'ECommerce.productName', 'ECommerce.sales', 'ECommerce.quantity', 'ECommerce.discount', 'ECommerce.profit' ], order: { 'ECommerce.rowId': 'asc' }, limit: 10, total: true }); expect(response.rawData()).toMatchSnapshot(); expect(response.rawData().length).toEqual(10); expect( response.serialize().loadResponse.results[0].total ).toEqual(44); }); execute('querying ECommerce: dimensions + order + total + offset', async () => { const response = await client.load({ dimensions: [ 'ECommerce.rowId', 'ECommerce.orderId', 'ECommerce.orderDate', 'ECommerce.customerId', 'ECommerce.customerName', 'ECommerce.city', 'ECommerce.category', 'ECommerce.subCategory', 'ECommerce.productName', 'ECommerce.sales', 'ECommerce.quantity', 'ECommerce.discount', 'ECommerce.profit' ], order: { 'ECommerce.rowId': 'asc' }, total: true, offset: 43 }); expect(response.rawData()).toMatchSnapshot(); expect(response.rawData().length).toEqual(1); expect( response.serialize().loadResponse.results[0].total ).toEqual(44); }); execute('querying ECommerce: dimensions + order + limit + total + offset', async () => { const response = await client.load({ dimensions: [ 'ECommerce.rowId', 'ECommerce.orderId', 'ECommerce.orderDate', 'ECommerce.customerId', 'ECommerce.customerName', 'ECommerce.city', 'ECommerce.category', 'ECommerce.subCategory', 'ECommerce.productName', 'ECommerce.sales', 'ECommerce.quantity', 'ECommerce.discount', 'ECommerce.profit' ], order: { 'ECommerce.rowId': 'asc' }, limit: 10, total: true, offset: 10 }); expect(response.rawData()).toMatchSnapshot(); expect(response.rawData().length).toEqual(10); expect( response.serialize().loadResponse.results[0].total ).toEqual(44); }); execute('querying ECommerce: count by cities + order', async () => { const response = await client.load({ dimensions: [ 'ECommerce.city' ], measures: [ 'ECommerce.count' ], order: { 'ECommerce.count': 'desc', 'ECommerce.city': 'asc', }, }); expect(response.rawData()).toMatchSnapshot(); }); execute('querying ECommerce: total quantity, avg discount, total sales, total profit by product + order + total -- rounding in athena', async () => { const response = await client.load({ dimensions: [ 'ECommerce.productName' ], measures: [ 'ECommerce.totalQuantity', 'ECommerce.avgDiscount', 'ECommerce.totalSales', 'ECommerce.totalProfit' ], order: { 'ECommerce.totalProfit': 'desc', 'ECommerce.productName': 'asc' }, total: true }); expect(response.rawData()).toMatchSnapshot(); }); execute('querying ECommerce: total sales, total profit by month + order (date) + total -- doesn\'t work with the BigQuery', async () => { const response = await client.load({ timeDimensions: [{ dimension: 'ECommerce.orderDate', granularity: 'month' }], measures: [ 'ECommerce.totalSales', 'ECommerce.totalProfit' ], order: { 'ECommerce.orderDate': 'asc' }, total: true }); expect(response.rawData()).toMatchSnapshot(); }); execute('querying ECommerce: count by month + order with non-UTC timezone (Asia/Kolkata)', async () => { const response = await client.load({ measures: [ 'ECommerce.count', ], timeDimensions: [{ dimension: 'ECommerce.customOrderDateNoPreAgg', granularity: 'month', dateRange: ['2020-01-01', '2020-12-31'], }], order: { 'ECommerce.customOrderDateNoPreAgg': 'asc' }, timezone: 'Asia/Kolkata', }); expect(response.rawData()).toMatchSnapshot(); }); execute('filtering ECommerce: contains dimensions, first', async () => { const response = await client.load({ dimensions: [ 'ECommerce.rowId', 'ECommerce.orderId', 'ECommerce.orderDate', 'ECommerce.customerId', 'ECommerce.customerName', 'ECommerce.city', 'ECommerce.category', 'ECommerce.subCategory', 'ECommerce.productName', 'ECommerce.sales', 'ECommerce.quantity', 'ECommerce.discount', 'ECommerce.profit' ], filters: [ { member: 'ECommerce.subCategory', operator: 'contains', values: ['able'], }, ], }); expect(response.rawData()).toMatchSnapshot(); }); execute('filtering ECommerce: contains dimensions, second', async () => { const response = await client.load({ dimensions: [ 'ECommerce.rowId', 'ECommerce.orderId', 'ECommerce.orderDate', 'ECommerce.customerId', 'ECommerce.customerName', 'ECommerce.city', 'ECommerce.category', 'ECommerce.subCategory', 'ECommerce.productName', 'ECommerce.sales', 'ECommerce.quantity', 'ECommerce.discount', 'ECommerce.profit' ], filters: [ { member: 'ECommerce.subCategory', operator: 'contains', values: ['able', 'urn'], }, ], }); expect(response.rawData()).toMatchSnapshot(); }); execute('filtering ECommerce: contains dimensions, third', async () => { const response = await client.load({ dimensions: [ 'ECommerce.rowId', 'ECommerce.orderId', 'ECommerce.orderDate', 'ECommerce.customerId', 'ECommerce.customerName', 'ECommerce.city', 'ECommerce.category', 'ECommerce.subCategory', 'ECommerce.productName', 'ECommerce.sales', 'ECommerce.quantity', 'ECommerce.discount', 'ECommerce.profit' ], filters: [ { member: 'ECommerce.subCategory', operator: 'contains', values: ['notexist'], }, ], }); expect(response.rawData()).toMatchSnapshot(); }); execute('filtering ECommerce: startsWith + dimensions, first', async () => { const response = await client.load({ dimensions: [ 'ECommerce.rowId', 'ECommerce.orderId', 'ECommerce.orderDate', 'ECommerce.customerId', 'ECommerce.customerName', 'ECommerce.city', 'ECommerce.category', 'ECommerce.subCategory', 'ECommerce.productName', 'ECommerce.sales', 'ECommerce.quantity', 'ECommerce.discount', 'ECommerce.profit' ], filters: [ { member: 'ECommerce.customerId', operator: 'startsWith', values: ['A'], }, ], }); expect(response.rawData()).toMatchSnapshot(); }); execute('filtering ECommerce: startsWith + dimensions, second', async () => { const response = await client.load({ dimensions: [ 'ECommerce.rowId', 'ECommerce.orderId', 'ECommerce.orderDate', 'ECommerce.customerId', 'ECommerce.customerName', 'ECommerce.city', 'ECommerce.category', 'ECommerce.subCategory', 'ECommerce.productName', 'ECommerce.sales', 'ECommerce.quantity', 'ECommerce.discount', 'ECommerce.profit' ], filters: [ { member: 'ECommerce.customerId', operator: 'startsWith', values: ['A', 'B'], }, ], }); expect(response.rawData()).toMatchSnapshot(); }); execute('filtering ECommerce: startsWith + dimensions, third', async () => { const response = await client.load({ dimensions: [ 'ECommerce.rowId', 'ECommerce.orderId', 'ECommerce.orderDate', 'ECommerce.customerId', 'ECommerce.customerName', 'ECommerce.city', 'ECommerce.category', 'ECommerce.subCategory', 'ECommerce.productName', 'ECommerce.sales', 'ECommerce.quantity', 'ECommerce.discount', 'ECommerce.profit' ], filters: [ { member: 'ECommerce.customerId', operator: 'startsWith', values: ['Z'], }, ], }); expect(response.rawData()).toMatchSnapshot(); }); execute('filtering ECommerce: endsWith + dimensions, first', async () => { const response = await client.load({ dimensions: [ 'ECommerce.rowId', 'ECommerce.orderId', 'ECommerce.orderDate', 'ECommerce.customerId', 'ECommerce.customerName', 'ECommerce.city', 'ECommerce.category', 'ECommerce.subCategory', 'ECommerce.productName', 'ECommerce.sales', 'ECommerce.quantity', 'ECommerce.discount', 'ECommerce.profit' ], filters: [ { member: 'ECommerce.orderId', operator: 'endsWith', values: ['0'], }, ], }); expect(response.rawData()).toMatchSnapshot(); }); execute('filtering ECommerce: endsWith + dimensions, second', async () => { const response = await client.load({ dimensions: [ 'ECommerce.rowId', 'ECommerce.orderId', 'ECommerce.orderDate', 'ECommerce.customerId', 'ECommerce.customerName', 'ECommerce.city', 'ECommerce.category', 'ECommerce.subCategory', 'ECommerce.productName', 'ECommerce.sales', 'ECommerce.quantity', 'ECommerce.discount', 'ECommerce.profit' ], filters: [ { member: 'ECommerce.orderId', operator: 'endsWith', values: ['1', '2'], }, ], }); expect(response.rawData()).toMatchSnapshot(); }); execute('filtering ECommerce: endsWith + dimensions, third', async () => { const response = await client.load({ dimensions: [ 'ECommerce.rowId', 'ECommerce.orderId', 'ECommerce.orderDate', 'ECommerce.customerId', 'ECommerce.customerName', 'ECommerce.city', 'ECommerce.category', 'ECommerce.subCategory', 'ECommerce.productName', 'ECommerce.sales', 'ECommerce.quantity', 'ECommerce.discount', 'ECommerce.profit' ], filters: [ { member: 'ECommerce.orderId', operator: 'endsWith', values: ['Z'], }, ], }); expect(response.rawData()).toMatchSnapshot(); }); execute('pre-aggregations Customers: running total without time dimension', async () => { const response = await client.load({ measures: [ 'Customers.runningTotal' ] }); expect(response.rawData()).toMatchSnapshot(); }); execute('querying ECommerce: total quantity, avg discount, total sales, total profit by product + order + total -- noisy test', async () => { const promise = async () => { await client.load({ dimensions: [ 'ECommerce.productName' ], measures: [ 'ECommerce.totalQuantity', 'ECommerce.avgDiscount', 'ECommerce.totalSales', 'ECommerce.totalProfit' ], order: { 'ECommerce.totalProfit': 'desc', 'ECommerce.productName': 'asc' }, total: true }); }; promise().catch(e => { expect(e.toString()).toMatch(/error/); }); }); execute('querying ECommerce: partitioned pre-agg', async () => { const response = await client.load({ dimensions: [ 'ECommerce.productName' ], measures: [ 'ECommerce.totalQuantity', ], timeDimensions: [{ dimension: 'ECommerce.orderDate', granularity: 'month' }], order: { 'ECommerce.orderDate': 'asc', 'ECommerce.totalProfit': 'desc', 'ECommerce.productName': 'asc' }, total: true }); expect(response.rawData()).toMatchSnapshot(); }); execute('querying ECommerce: partitioned pre-agg higher granularity', async () => { const response = await client.load({ dimensions: [ 'ECommerce.productName' ], measures: [ 'ECommerce.totalQuantity', ], timeDimensions: [{ dimension: 'ECommerce.orderDate', granularity: 'year' }], order: { 'ECommerce.orderDate': 'asc', 'ECommerce.totalProfit': 'desc', 'ECommerce.productName': 'asc' }, total: true }); expect(response.rawData()).toMatchSnapshot(); }); execute('querying BigECommerce: partitioned pre-agg with multi time dimension', async () => { const response = await client.load({ dimensions: [], measures: [ 'BigECommerce.count', ], timeDimensions: [ { dimension: 'BigECommerce.completedDate', granularity: 'day' }, { dimension: 'BigECommerce.orderDate', granularity: 'day' } ], order: { 'BigECommerce.completedDate': 'asc', 'BigECommerce.orderDate': 'asc', 'BigECommerce.count': 'asc' } }); expect(response.rawData()).toMatchSnapshot(); }); execute('querying BigECommerce: partitioned pre-agg', async () => { const response = await client.load({ dimensions: [ 'BigECommerce.productName' ], measures: [ 'BigECommerce.totalQuantity', ], timeDimensions: [{ dimension: 'BigECommerce.orderDate', granularity: 'month' }], order: { 'BigECommerce.orderDate': 'asc', 'BigECommerce.totalProfit': 'desc', 'BigECommerce.productName': 'asc' } }); expect(response.rawData()).toMatchSnapshot(); }); execute('querying BigECommerce: time series in rolling window', async () => { const response = await client.load({ measures: [ 'BigECommerce.customersCountPrev1Month', ], timeDimensions: [{ dimension: 'BigECommerce.orderDate', granularity: 'month', dateRange: ['2020-01-01', '2020-12-31'], }], order: { 'BigECommerce.orderDate': 'asc', } }); expect(response.rawData()).toMatchSnapshot(); }); execute('querying BigECommerce: null sum', async () => { const response = await client.load({ measures: [ 'BigECommerce.totalSales', ], filters: [{ member: 'BigECommerce.id', operator: 'equals', values: ['8958'] }] }); expect(response.rawData()).toMatchSnapshot(); }); execute('querying BigECommerce: null boolean', async () => { const response = await client.load({ dimensions: [ 'BigECommerce.returning', ], filters: [{ member: 'BigECommerce.id', operator: 'equals', values: ['8958'] }] }); expect(response.rawData()).toMatchSnapshot(); }); execute('querying BigECommerce: rolling window by 2 day', async () => { const response = await client.load({ measures: [ 'BigECommerce.rollingCountBy2Day', ], timeDimensions: [{ dimension: 'BigECommerce.orderDate', granularity: 'month', dateRange: ['2020-01-01', '2020-12-31'], }], }); expect(response.rawData()).toMatchSnapshot(); }); execute('querying BigECommerce: rolling window by 2 day without date range', async () => { const response = await client.load({ measures: [ 'BigECommerce.rollingCountBy2Day', ], timeDimensions: [{ dimension: 'BigECommerce.orderDate', granularity: 'month', }], }); expect(response.rawData()).toMatchSnapshot(); }); execute('querying BigECommerce: multi-stage group by time dimension', async () => { const response = await client.load({ measures: [ 'BigECommerce.totalProfit', 'BigECommerce.totalProfitForQuarter' ], timeDimensions: [ { dimension: 'BigECommerce.orderDate', granularity: 'quarter', }, { dimension: 'BigECommerce.orderDate', granularity: 'month', dateRange: ['2020-01-01', '2020-12-31'], }], order: [ ['BigECommerce.orderDate', 'asc'], ], }); expect(response.rawData()).toMatchSnapshot(); }); execute('querying BigECommerce: rolling window by 2 week', async () => { const response = await client.load({ measures: [ 'BigECommerce.rollingCountBy2Week', ], timeDimensions: [{ dimension: 'BigECommerce.orderDate', granularity: 'month', dateRange: ['2020-01-01', '2020-12-31'], }], }); expect(response.rawData()).toMatchSnapshot(); }); execute('querying BigECommerce: rolling window by 2 month', async () => { const response = await client.load({ measures: [ 'BigECommerce.rollingCountBy2Month', ], timeDimensions: [{ dimension: 'BigECommerce.orderDate', granularity: 'month', dateRange: ['2020-01-01', '2020-12-31'], }], }); expect(response.rawData()).toMatchSnapshot(); }); execute('querying BigECommerce: rolling window by 2 month without date range', async () => { const response = await client.load({ measures: [ 'BigECommerce.rollingCountBy2Month', ], timeDimensions: [{ dimension: 'BigECommerce.orderDate', granularity: 'month', }], }); expect(response.rawData()).toMatchSnapshot(); }); execute('querying BigECommerce: rolling window YTD (month)', async () => { const response = await client.load({ measures: [ 'BigECommerce.rollingCountYTD', ], timeDimensions: [{ dimension: 'BigECommerce.orderDate', granularity: 'month', dateRange: ['2020-01-01', '2020-12-31'], }], order: [ ['BigECommerce.orderDate', 'asc'], ], }); expect(response.rawData()).toMatchSnapshot(); }); execute('querying BigECommerce: rolling window YTD (month + week)', async () => { const response = await client.load({ measures: [ 'BigECommerce.rollingCountYTD', ], timeDimensions: [{ dimension: 'BigECommerce.orderDate', granularity: 'month', dateRange: ['2020-01-01', '2020-12-31'], }, { dimension: 'BigECommerce.orderDate', granularity: 'week', dateRange: ['2020-01-01', '2020-12-31'], }], order: [ ['BigECommerce.orderDate', 'asc'], ], }); expect(response.rawData()).toMatchSnapshot(); }); execute('querying BigECommerce: rolling window YTD (month + week + no gran)', async () => { const response = await client.load({ measures: [ 'BigECommerce.rollingCountYTD', ], timeDimensions: [{ dimension: 'BigECommerce.orderDate', granularity: 'month', dateRange: ['2020-01-01', '2020-12-31'], }, { dimension: 'BigECommerce.orderDate', granularity: 'week', dateRange: ['2020-01-01', '2020-12-31'], }, { dimension: 'BigECommerce.orderDate', dateRange: ['2020-01-01', '2020-12-31'], }], order: [ ['BigECommerce.orderDate', 'asc'], ], }); expect(response.rawData()).toMatchSnapshot(); }); execute('querying BigECommerce: rolling window YTD (month + week + day)', async () => { const response = await client.load({ measures: [ 'BigECommerce.rollingCountYTD', ], timeDimensions: [{ dimension: 'BigECommerce.orderDate', granularity: 'month', dateRange: ['2020-01-01', '2020-03-01'], }, { dimension: 'BigECommerce.orderDate', granularity: 'week', dateRange: ['2020-01-01', '2020-03-01'], }, { dimension: 'BigECommerce.orderDate', granularity: 'day', dateRange: ['2020-01-01', '2020-03-01'], }], order: [ ['BigECommerce.orderDate', 'asc'], ], }); expect(response.rawData()).toMatchSnapshot(); }); execute('querying BigECommerce: rolling window YTD (month + week + day + no gran)', async () => { const response = await client.load({ measures: [ 'BigECommerce.rollingCountYTD', ], timeDimensions: [{ dimension: 'BigECommerce.orderDate', granularity: 'month', dateRange: ['2020-01-01', '2020-03-01'], }, { dimension: 'BigECommerce.orderDate', granularity: 'week', dateRange: ['2020-01-01', '2020-03-01'], }, { dimension: 'BigECommerce.orderDate', granularity: 'day', dateRange: ['2020-01-01', '2020-03-01'], }, { dimension: 'BigECommerce.orderDate', dateRange: ['2020-01-01', '2020-03-01'], }], order: [ ['BigECommerce.orderDate', 'asc'], ], }); expect(response.rawData()).toMatchSnapshot(); }); execute('querying BigECommerce: rolling window YTD without date range', async () => { const response = await client.load({ measures: [ 'BigECommerce.rollingCountYTD', ], timeDimensions: [{ dimension: 'BigECommerce.orderDate', granularity: 'month', }], }); expect(response.rawData()).toMatchSnapshot(); }); execute('querying BigECommerce: rolling window YTD without granularity', async () => { const response = await client.load({ measures: [ 'BigECommerce.rollingCountYTD', ], timeDimensions: [{ dimension: 'BigECommerce.orderDate', dateRange: ['2020-01-01', '2020-03-01'], }], }); expect(response.rawData()).toMatchSnapshot(); }); if (includeHLLSuite) { execute('querying BigECommerce: rolling count_distinct_approx window by 2 day', async () => { const response = await client.load({ measures: [ 'BigECommerce.rollingCountApproxBy2Day', ], timeDimensions: [{ dimension: 'BigECommerce.orderDate', granularity: 'month', dateRange: ['2020-01-01', '2020-12-31'], }], }); expect(response.rawData()).toMatchSnapshot(); }); execute('querying BigECommerce: rolling count_distinct_approx window by 2 week', async () => { const response = await client.load({ measures: [ 'BigECommerce.rollingCountApproxBy2Week', ], timeDimensions: [{ dimension: 'BigECommerce.orderDate', granularity: 'month', dateRange: ['2020-01-01', '2020-12-31'], }], }); expect(response.rawData()).toMatchSnapshot(); }); execute('querying BigECommerce: rolling count_distinct_approx window by 2 month', async () => { const response = await client.load({ measures: [ 'BigECommerce.rollingCountApproxBy2Month', ], timeDimensions: [{ dimension: 'BigECommerce.orderDate', granularity: 'month', dateRange: ['2020-01-01', '2020-12-31'], }], }); expect(response.rawData()).toMatchSnapshot(); }); } execute('querying BigECommerce: totalProfitYearAgo', async () => { const response = await client.load({ measures: [ 'BigECommerce.totalProfitYearAgo', ], timeDimensions: [{ dimension: 'BigECommerce.orderDate', granularity: 'month', dateRange: ['2020-01-01', '2020-12-31'], }], }); expect(response.rawData()).toMatchSnapshot(); }); execute('querying BigECommerce: base measure plus multi-stage over non-partitioned pre-aggregation', async () => { const response = await client.load({ measures: [ 'BigECommerce.totalProfit', 'BigECommerce.profitInCategory', ], dimensions: [ 'BigECommerce.category', 'BigECommerce.productName', ], order: [ ['BigECommerce.totalProfit', 'desc'], ['BigECommerce.category', 'asc'], ['BigECommerce.productName', 'asc'], ], }); expect(response.rawData()).toMatchSnapshot(); }); execute('querying BigECommerce: two multi-stage branches sharing one pre-aggregation', async () => { const response = await client.load({ measures: [ 'BigECommerce.totalProfitNoId', 'BigECommerce.totalProfitNoIdPct', ], timeDimensions: [{ dimension: 'BigECommerce.orderDate', granularity: 'month', dateRange: ['2020-01-01', '2020-12-31'], }], order: [ ['BigECommerce.orderDate', 'asc'], ], }); expect(response.rawData()).toMatchSnapshot(); }); execute('querying BigECommerce: SeveralMultiStageMeasures', async () => { const response = await client.load({ measures: [ 'BigECommerce.totalProfitYearAgo', 'BigECommerce.percentageOfTotalForStatus', 'BigECommerce.totalCountRetailMonthAgo', 'BigECommerce.count', ], timeDimensions: [{ dimension: 'BigECommerce.orderDate', granularity: 'month', dateRange: ['2020-01-01', '2020-12-31'], }], }); expect(response.rawData()).toMatchSnapshot(); }); // `city` is in no rollup, so the source database itself combines the two // multi-stage subqueries, which are computed at different grains. execute('querying BigECommerce: multi-stage measures combined over dimensions', async () => { const response = await client.load({ measures: [ 'BigECommerce.totalProfitForStatus', 'BigECommerce.profitInCategory', ], dimensions: [ 'BigECommerce.category', 'BigECommerce.city', ], order: [ ['BigECommerce.category', 'asc'], ['BigECommerce.city', 'asc'], ], }); expect(response.rawData()).toMatchSnapshot(); }); execute('querying BigECommerce: filtering with possible casts', async () => { const response = await client.load({ measures: [ 'BigECommerce.totalSales', ], filters: [ { values: ['10'], member: 'BigECommerce.sales', operator: 'gte' }, { values: ['true'], member: 'BigECommerce.returning', operator: 'equals' } ], timeDimensions: [{ dimension: 'BigECommerce.orderDate', granularity: 'month', dateRange: ['2020-01-01', '2020-12-31'], }], order: { 'BigECommerce.orderDate': 'asc', } }); expect(response.rawData()).toMatchSnapshot(); }); execute('querying BigECommerce with Retail Calendar: totalCountRetailYearAgo', async () => { const response = await client.load({ measures: [ 'BigECommerce.count', 'BigECommerce.totalCountRetailYearAgo', ], timeDimensions: [{ dimension: 'RetailCalendar.retail_date', granularity: 'year', dateRange: ['2020-02-02', '2021-02-01'], }], order: { 'RetailCalendar.retail_date': 'asc', } }); expect(response.rawData()).toMatchSnapshot(); }); // Grouped by a retail week: a calendar column, not arithmetic on the date. // The prior month carries the comparison: retail months are 4 or 5 weeks // long, and unlike the sparse prior year it has rows in almost every week. const priorPeriodsByWeek = (variant: '' | 'NoPreAgg') => ({ measures: [ 'BigECommerce.count', `BigECommerce.totalCountRetailYearAgo${variant}`, `BigECommerce.totalCountRetailMonthAgo${variant}`, ], timeDimensions: [{ dimension: 'RetailCalendar.retail_date', granularity: 'week', dateRange: ['2020-02-02', '2021-01-30'], }], order: { 'RetailCalendar.retail_date': 'asc', }, } as const); execute('querying BigECommerce with Retail Calendar: prior year and month by week (no pre-aggregation)', async () => { const response = await client.load(priorPeriodsByWeek('NoPreAgg')); expect(servedByRollupStore(response)).toBe(false); expect(response.rawData()).toMatchSnapshot(); }); execute('querying BigECommerce with Retail Calendar: prior year and month by week (pre-aggregation in CubeStore)', async () => { const [rollup, source] = await Promise.all([ client.load(priorPeriodsByWeek('')), client.load(priorPeriodsByWeek('NoPreAgg')), ]); expect(servedByRollupStore(rollup)).toBe(true); expect(servedByRollupStore(source)).toBe(false); const sourceRows = source.rawData().map(({ 'BigECommerce.totalCountRetailYearAgoNoPreAgg': yearAgo, 'BigECommerce.totalCountRetailMonthAgoNoPreAgg': monthAgo, ...row }: any) => ({ ...row, 'BigECommerce.totalCountRetailYearAgo': yearAgo, 'BigECommerce.totalCountRetailMonthAgo': monthAgo, })); // Agreeing proves little unless the prior month is mostly populated and // is not simply the current period again, which is what a rollup that // dropped the shift would return. const monthAgo = sourceRows.filter((row: any) => row['BigECommerce.totalCountRetailMonthAgo'] !== null); expect(monthAgo.length).toBeGreaterThan(sourceRows.length / 2); expect(monthAgo.some((row: any) => row['BigECommerce.totalCountRetailMonthAgo'] !== row['BigECommerce.count'])).toBe(true); expect(rollup.rawData()).toEqual(sourceRows); expect(rollup.rawData()).toMatchSnapshot(); }); // Rollup stores the named prior retail month by product and day; queries read it by category. // It's the calendar mapping, not an interval: 2020-12-14 maps to 2020-11-16, not the empty 2020-11-14. const priorMonthByCategory = (variant: '' | 'NoPreAgg', dateRange: [string, string]) => ({ measures: [ 'RetailOrders.retailOrderCount', `RetailOrders.retailOrderCountPriorMonth${variant}`, ], dimensions: ['RetailOrders.category'], timeDimensions: [{ dimension: 'RetailOrders.retail_date', dateRange, }], order: { 'RetailOrders.category': 'asc', }, } as const); const asRollupRows = (rows: any[]) => rows.map(({ 'RetailOrders.retailOrderCountPriorMonthNoPreAgg': priorMonth, ...row }: any) => ({ ...row, 'RetailOrders.retailOrderCountPriorMonth': priorMonth, })); // The SQL API renders `retail_date = ''` as a range over one instant. execute('querying RetailOrders: named prior month by category for one day (pre-aggregation in CubeStore)', async () => { const day: [string, string] = ['2020-12-14T00:00:00.000', '2020-12-14T00:00:00.000']; const [rollup, source] = await Promise.all([ client.load(priorMonthByCategory('', day)), client.load(priorMonthByCategory('NoPreAgg', day)), ]); expect(servedByRollupStore(rollup)).toBe(true); expect(servedByRollupStore(source)).toBe(false); expect(rollup.rawData()).toEqual(asRollupRows(source.rawData())); const technology = rollup.rawData().find((row: any) => row['RetailOrders.category'] === 'Technology'); expect(technology).toMatchObject({ 'RetailOrders.retailOrderCount': '2', 'RetailOrders.retailOrderCountPriorMonth': '1', }); expect(rollup.rawData()).toMatchSnapshot(); }); execute('querying RetailOrders: named prior month by category over two months (pre-aggregation in CubeStore)', async () => { const months: [string, string] = ['2020-11-01', '2020-12-31']; const [rollup, source] = await Promise.all([ client.load(priorMonthByCategory('', months)), client.load(priorMonthByCategory('NoPreAgg', months)), ]); expect(servedByRollupStore(rollup)).toBe(true); expect(servedByRollupStore(source)).toBe(false); expect(rollup.rawData()).toEqual(asRollupRows(source.rawData())); expect(rollup.rawData().some((row: any) => row['RetailOrders.retailOrderCountPriorMonth'] !== null && row['RetailOrders.retailOrderCountPriorMonth'] !== row['RetailOrders.retailOrderCount'])).toBe(true); expect(rollup.rawData()).toMatchSnapshot(); }); execute('querying BigECommerce with Retail Calendar: totalCountRetailMonthAgo', async () => { const response = await client.load({ measures: [ 'BigECommerce.count', 'BigECommerce.totalCountRetailMonthAgo', ], timeDimensions: [{ dimension: 'RetailCalendar.retail_date', granularity: 'month', dateRange: ['2020-02-02', '2021-02-01'], }], order: { 'RetailCalendar.retail_date': 'asc', } }); expect(response.rawData()).toMatchSnapshot(); }); execute('Tesseract: querying BigECommerce with Retail Calendar: totalCountRetailMonthAgo', async () => { const response = await client.load({ measures: [ 'BigECommerce.count', 'BigECommerce.totalCountRetailMonthAgo', ], timeDimensions: [{ dimension: 'RetailCalendar.retail_date', granularity: 'month', dateRange: ['2020-02-02', '2021-02-01'], }], order: { 'RetailCalendar.retail_date': 'asc', } }); expect(response.rawData()).toMatchSnapshot(); }); execute('querying BigECommerce with Retail Calendar: totalCountRetailWeekAgo', async () => { const response = await client.load({ measures: [ 'BigECommerce.count', 'BigECommerce.totalCountRetailWeekAgo', ], timeDimensions: [{ dimension: 'RetailCalendar.retail_date', granularity: 'week', dateRange: ['2020-02-02', '2021-03-07'], }], order: { 'RetailCalendar.retail_date': 'asc', } }); expect(response.rawData()).toMatchSnapshot(); }); execute('Tesseract: querying BigECommerce with Retail Calendar: totalCountRetailWeekAgo', async () => { const response = await client.load({ measures: [ 'BigECommerce.count', 'BigECommerce.totalCountRetailWeekAgo', ], timeDimensions: [{ dimension: 'RetailCalendar.retail_date', granularity: 'week', dateRange: ['2020-02-02', '2021-03-07'], }], order: { 'RetailCalendar.retail_date': 'asc', } }); expect(response.rawData()).toMatchSnapshot(); }); execute('querying custom granularities ECommerce: count by half_year + no dimension', async () => { const response = await client.load({ measures: [ 'ECommerce.count', ], timeDimensions: [{ dimension: 'ECommerce.customOrderDateNoPreAgg', granularity: 'half_year', dateRange: ['2020-01-01', '2020-12-31'], }], }); expect(response.rawData()).toMatchSnapshot(); }); execute('querying custom granularities ECommerce: count by half_year_by_1st_april + no dimension', async () => { const response = await client.load({ measures: [ 'ECommerce.count', ], timeDimensions: [{ dimension: 'ECommerce.customOrderDateNoPreAgg', granularity: 'half_year_by_1st_april', dateRange: ['2020-01-01', '2020-12-31'], }], }); expect(response.rawData()).toMatchSnapshot(); }); execute('querying custom granularities ECommerce: count by fiscal_year_by_1st_april + no dimension', async () => { const response = await client.load({ measures: [ 'ECommerce.count', ], timeDimensions: [{ dimension: 'ECommerce.customOrderDateNoPreAgg', granularity: 'fiscal_year_by_1st_april', dateRange: ['2020-01-01', '2020-12-31'], }], }); expect(response.rawData()).toMatchSnapshot(); }); execute('querying custom granularities ECommerce: count by fiscal_year_by_15th_april + no dimension', async () => { const response = await client.load({ measures: [ 'ECommerce.count', ], timeDimensions: [{ dimension: 'ECommerce.customOrderDateNoPreAgg', granularity: 'fiscal_year_by_15th_april', dateRange: ['2020-01-01', '2020-12-31'], }], }); expect(response.rawData()).toMatchSnapshot(); }); execute('querying custom granularities ECommerce: count by monthly_from_15th + no dimension', async () => { const response = await client.load({ measures: [ 'ECommerce.count', ], timeDimensions: [{ dimension: 'ECommerce.customOrderDateNoPreAgg', granularity: 'monthly_from_15th', dateRange: ['2020-01-01', '2020-12-31'], }], }); expect(response.rawData()).toMatchSnapshot(); }); execute('querying custom granularities ECommerce: count by three_months_by_march + no dimension', async () => { const response = await client.load({ measures: [ 'ECommerce.count', ], timeDimensions: [{ dimension: 'ECommerce.customOrderDateNoPreAgg', granularity: 'three_months_by_march', dateRange: ['2020-01-01', '2020-12-31'], }], }); expect(response.rawData()).toMatchSnapshot(); }); execute('querying custom granularities ECommerce: count by half_year + dimension', async () => { const response = await client.load({ measures: [ 'ECommerce.count', ], timeDimensions: [{ dimension: 'ECommerce.customOrderDateNoPreAgg', granularity: 'half_year', dateRange: ['2020-01-01', '2020-12-31'], }], dimensions: ['ECommerce.city'], order: [ ['ECommerce.customOrderDateNoPreAgg', 'asc'], ['ECommerce.city', 'asc'] ], }); expect(response.rawData()).toMatchSnapshot(); }); execute('querying custom granularities ECommerce: count by half_year_by_1st_april + dimension', async () => { const response = await client.load({ measures: [ 'ECommerce.count', ], timeDimensions: [{ dimension: 'ECommerce.customOrderDateNoPreAgg', granularity: 'half_year_by_1st_april', dateRange: ['2020-01-01', '2020-12-31'], }], dimensions: ['ECommerce.city'], order: [ ['ECommerce.customOrderDateNoPreAgg', 'asc'], ['ECommerce.city', 'asc'] ], }); expect(response.rawData()).toMatchSnapshot(); }); execute('querying custom granularities ECommerce: count by three_months_by_march + dimension', async () => { const response = await client.load({ measures: [ 'ECommerce.count', ], timeDimensions: [{ dimension: 'ECommerce.customOrderDateNoPreAgg', granularity: 'three_months_by_march', dateRange: ['2020-01-01', '2020-12-31'], }], dimensions: ['ECommerce.city'], order: [ ['ECommerce.customOrderDateNoPreAgg', 'asc'], ['ECommerce.city', 'asc'] ], }); expect(response.rawData()).toMatchSnapshot(); }); execute('querying custom granularities ECommerce: count by two_mo_by_feb + no dimension + rollingCountByUnbounded', async () => { const response = await client.load({ measures: [ 'ECommerce.rollingCountByUnbounded', ], timeDimensions: [{ dimension: 'ECommerce.customOrderDateNoPreAgg', granularity: 'two_mo_by_feb', dateRange: ['2020-01-01', '2020-12-31'], }], }); expect(response.rawData()).toMatchSnapshot(); }); execute('querying custom granularities ECommerce: count by two_mo_by_feb + no dimension + rollingCountByTrailing', async () => { const response = await client.load({ measures: [ 'ECommerce.rollingCountByTrailing', ], timeDimensions: [{ dimension: 'ECommerce.customOrderDateNoPreAgg', granularity: 'two_mo_by_feb', dateRange: ['2020-01-01', '2020-12-31'], }], }); expect(response.rawData()).toMatchSnapshot(); }); execute('querying custom granularities ECommerce: count by two_mo_by_feb + no dimension + rollingCountByLeading', async () => { const response = await client.load({ measures: [ 'ECommerce.rollingCountByLeading', ], timeDimensions: [{ dimension: 'ECommerce.customOrderDateNoPreAgg', granularity: 'two_mo_by_feb', dateRange: ['2020-01-01', '2020-12-31'], }], }); expect(response.rawData()).toMatchSnapshot(); }); execute('querying custom granularities ECommerce: count by two_mo_by_feb + no dimension + rollingCountByLeading without date range', async () => { const response = await client.load({ measures: [ 'ECommerce.rollingCountByLeading', ], timeDimensions: [{ dimension: 'ECommerce.customOrderDateNoPreAgg', granularity: 'two_mo_by_feb', }], }); expect(response.rawData()).toMatchSnapshot(); }); execute('querying custom granularities (with preaggregation) ECommerce: totalQuantity by half_year + no dimension', async () => { const response = await client.load({ measures: [ 'ECommerce.totalQuantity', ], timeDimensions: [{ dimension: 'ECommerce.orderDate', granularity: 'half_year', dateRange: ['2020-01-01', '2020-12-31'], }], }); expect(response.rawData()).toMatchSnapshot(); }); execute('querying custom granularities (with preaggregation) ECommerce: totalQuantity by half_year + dimension', async () => { const response = await client.load({ measures: [ 'ECommerce.totalQuantity', ], timeDimensions: [{ dimension: 'ECommerce.orderDate', granularity: 'half_year', dateRange: ['2020-01-01', '2020-12-31'], }], dimensions: ['ECommerce.productName'], order: [ ['ECommerce.orderDate', 'asc'], ['ECommerce.productName', 'asc'] ], }); expect(response.rawData()).toMatchSnapshot(); }); execute('querying SwitchSourceTest: simple cross join', async () => { const response = await client.load({ dimensions: [ 'SwitchSourceTest.city' ], measures: [ 'SwitchSourceTest.totalSalesA', ], timeDimensions: [{ dimension: 'SwitchSourceTest.orderDate', granularity: 'month', dateRange: ['2020-01-01', '2020-04-01'], }], order: { 'SwitchSourceTest.orderDate': 'asc', 'SwitchSourceTest.city': 'asc' } }); expect(response.rawData()).toMatchSnapshot(); }); execute('querying SwitchSourceTest: full cross join', async () => { const response = await client.load({ dimensions: [ 'SwitchSourceTest.city' ], measures: [ 'SwitchSourceTest.totalSales', ], timeDimensions: [{ dimension: 'SwitchSourceTest.orderDate', granularity: 'month', dateRange: ['2020-01-01', '2020-04-01'], }], order: { 'SwitchSourceTest.orderDate': 'asc', 'SwitchSourceTest.city': 'asc' } }); expect(response.rawData()).toMatchSnapshot(); }); execute('querying SwitchSourceTest: filter by switch dimensions', async () => { const response = await client.load({ dimensions: [ 'SwitchSourceTest.city' ], measures: [ 'SwitchSourceTest.totalSales', ], filters: [ { values: ['com'], member: 'SwitchSourceTest.source', operator: 'equals' }, { values: ['curr_a'], member: 'SwitchSourceTest.curr', operator: 'equals' } ], timeDimensions: [{ dimension: 'SwitchSourceTest.orderDate', granularity: 'month', dateRange: ['2020-01-01', '2020-04-01'], }], order: { 'SwitchSourceTest.orderDate': 'asc', 'SwitchSourceTest.city': 'asc' } }); expect(response.rawData()).toMatchSnapshot(); }); // A BI tool selecting separate days sends ISO-8601 values. Three of them, as some // databases rewrite three or more `=` on one column into IN. The member has no // pre-aggregation, so the filter reaches the data source. const selectedDays = ['2020-01-23T00:00:00.000Z', '2020-06-25T00:00:00.000Z', '2020-10-19T00:00:00.000Z']; const selectedDayRowIds = [523, 1995, 3059, 3060, 9584]; const rowIdsFor = async (filters: any[]) => { const response = await client.load({ dimensions: ['ECommerce.rowId'], filters, order: { 'ECommerce.rowId': 'asc' }, }); return response.rawData().map((row: any) => Number(row['ECommerce.rowId'])); }; execute('filtering ECommerce: equals several ISO days of a time dimension', async () => { expect(await rowIdsFor([{ member: 'ECommerce.customOrderDateNoPreAgg', operator: 'equals', values: selectedDays, }])).toEqual(selectedDayRowIds); }); execute('filtering ECommerce: notEquals several ISO days of a time dimension', async () => { const [excluded, all] = await Promise.all([ rowIdsFor([{ member: 'ECommerce.customOrderDateNoPreAgg', operator: 'notEquals', values: selectedDays, }]), rowIdsFor([]), ]); expect(excluded).toEqual(all.filter(id => !selectedDayRowIds.includes(id))); }); execute('filtering ECommerce: an or group of single ISO days of a time dimension', async () => { expect(await rowIdsFor([{ or: selectedDays.map(day => ({ member: 'ECommerce.customOrderDateNoPreAgg', operator: 'equals', values: [day], })), }])).toEqual(selectedDayRowIds); }); // bigRowId is row_id + 2^60, where doubles are 256 apart: nearby rows such as 3059 // and 3060 collapse into one double, so a filter compared as a float matches them all. const bigRowId3060 = '1152921504606850036'; execute('filtering ECommerce: equals a whole number beyond double precision', async () => { expect(await rowIdsFor([{ member: 'ECommerce.bigRowId', operator: 'equals', values: [bigRowId3060], }])).toEqual([3060]); }); execute('filtering ECommerce: equals several whole numbers beyond double precision', async () => { expect(await rowIdsFor([{ member: 'ECommerce.bigRowId', operator: 'equals', values: [bigRowId3060, '1152921504606847499'], }])).toEqual([523, 3060]); }); execute('filtering ECommerce: gt a fractional number on a whole-number column', async () => { const [gtFraction, gteWhole] = await Promise.all([ rowIdsFor([{ member: 'ECommerce.quantity', operator: 'gt', values: ['4.5'] }]), rowIdsFor([{ member: 'ECommerce.quantity', operator: 'gte', values: ['5'] }]), ]); expect(gtFraction.length).toBeGreaterThan(0); expect(gtFraction).toEqual(gteWhole); }); // An INT64 cast rejects these values, so they must stay FLOAT64 for BigQuery to accept them. execute('filtering ECommerce: lt a whole number beyond INT64', async () => { const [below, all] = await Promise.all([ rowIdsFor([{ member: 'ECommerce.rowId', operator: 'lt', values: ['99999999999999999999'] }]), rowIdsFor([]), ]); expect(below).toEqual(all); }); execute('filtering ECommerce: gte a number in exponent notation', async () => { const [atLeast, all] = await Promise.all([ rowIdsFor([{ member: 'ECommerce.rowId', operator: 'gte', values: ['1e3'] }]), rowIdsFor([]), ]); expect(atLeast).toEqual(all.filter(id => id >= 1000)); }); execute('filtering ECommerce: equals a whole number with leading zeros', async () => { expect(await rowIdsFor([{ member: 'ECommerce.rowId', operator: 'equals', values: ['0523'], }])).toEqual([523]); }); if (includeIncrementalSchemaSuite) { describe(`Incremental schema loading with @cubejs-backend/${type}-driver`, () => { incrementalSchemaLoadingSuite(execute, () => driver, tables); }); } if (externalSchemaTests) { describe(`External schema retrospection with @cubejs-backend/${type}-driver`, () => { redshiftExternalSchemasSuite(execute, () => driver); }); } executePg('SQL API: powerbi min max push down', async (connection) => { const res = await connection.query(` select max("rows"."orderDate") as "a0", min("rows"."orderDate") as "a1" from ( select "orderDate" from "public"."ECommerce" "$Table" ) "rows" `); expect(res.rows).toMatchSnapshot('powerbi_min_max_push_down'); }); executePg('SQL API: powerbi min max ungrouped flag', async (connection) => { const res = await connection.query(` select count(distinct("rows"."totalSales")) + max( case when "rows"."totalSales" is null then 1 else 0 end ) as "a0", min("rows"."totalSales") as "a1", max("rows"."totalSales") as "a2" from ( select "totalSales" from "public"."ECommerce" "$Table" ) "rows" `); expect(res.rows).toMatchSnapshot('powerbi_min_max_ungrouped_flag'); }); executePg('SQL API: named prior month by category for one day', async (connection) => { const priorMonth = (measure: string) => connection.query(` select category, MEASURE(retailOrderCount) as order_count, MEASURE(${measure}) as prior_month from "public"."RetailOrders" where retail_date = '2020-12-14' group by 1 order by 1 `); const [rollup, source] = await Promise.all([ priorMonth('retailOrderCountPriorMonth'), priorMonth('retailOrderCountPriorMonthNoPreAgg'), ]); expect(rollup.rows).toEqual(source.rows); expect(rollup.rows).toMatchSnapshot(); }); executePg('SQL API: ungrouped pre-agg', async (connection) => { const res = await connection.query(` select "productName", "totalSales" from "public"."BigECommerce" "$Table" order by 2 desc, 1 asc `); expect(res.rows).toMatchSnapshot('ungrouped_pre_agg'); }); executePg('SQL API: post-aggregate percentage of total', async (connection) => { const res = await connection.query(` select sum("BigECommerce"."percentageOfTotalForStatus") from "public"."BigECommerce" "BigECommerce" `); expect(res.rows).toMatchSnapshot(); }); executePg('SQL API: reuse params', async (connection) => { const res = await connection.query(` select date_trunc('year', "orderDate") as "c0", round(sum("ECommerce"."totalSales")) as "m0" from "ECommerce" as "ECommerce" where date_trunc('year', "orderDate") in ( CAST('2019-01-01 00:00:00.0' AS timestamp), CAST('2020-01-01 00:00:00.0' AS timestamp), CAST('2021-01-01 00:00:00.0' AS timestamp), CAST('2022-01-01 00:00:00.0' AS timestamp), CAST('2023-01-01 00:00:00.0' AS timestamp) ) group by date_trunc('year', "orderDate") `); expect(res.rows).toMatchSnapshot('reuse_params'); }); executePg('SQL API: Simple Rollup', async (connection) => { const res = await connection.query(` select rowId, orderId, orderDate, sum(count) from "ECommerce" as "ECommerce" group by ROLLUP(1, 2, 3) order by 1, 2, 3 `); expect(res.rows).toMatchSnapshot(); }); executePg('SQL API: Complex Rollup', async (connection) => { const res = await connection.query(` select rowId, orderId, orderDate, city, sum(count) from "ECommerce" as "ECommerce" group by ROLLUP(1, 2), 3, ROLLUP(4) order by 1, 2, 3, 4 `); expect(res.rows).toMatchSnapshot(); }); executePg('SQL API: Rollup with aliases', async (connection) => { const res = await connection.query(` select rowId as "row", orderId as "order", orderDate as "orderData", city as "city", sum(count) from "ECommerce" as "ECommerce" group by ROLLUP(rowId, 2), 3, ROLLUP(4) order by 1, 2, 3, 4 `); expect(res.rows).toMatchSnapshot(); }); executePg('SQL API: Rollup over exprs', async (connection) => { const res = await connection.query(` select rowId + sales * 2 as "order", orderDate as "orderData", city as "city", sum(count) from "ECommerce" as "ECommerce" group by ROLLUP(1, 2, 3) order by 1, 2, 3 `); expect(res.rows).toMatchSnapshot(); }); executePg('SQL API: Nested Rollup', async (connection) => { const res = await connection.query(` select rowId, orderId, orderDate, sum(cnt) from ( select rowId, orderId, orderDate, sum(count) as cnt from "ECommerce" as "ECommerce" group by 1, 2, 3 ) a group by ROLLUP(1, 2, 3) order by 1, 2, 3 `); expect(res.rows).toMatchSnapshot(); }); executePg('SQL API: Nested Rollup with aliases', async (connection) => { const res = await connection.query(` select rowId as "row", orderId as "order", orderDate as "date", sum(cnt) from ( select rowId, orderId, orderDate, sum(count) as cnt from "ECommerce" as "ECommerce" group by 1, 2, 3 ) a group by ROLLUP(1, 2, 3) order by 1, 2, 3 `); expect(res.rows).toMatchSnapshot(); }); executePg('SQL API: Nested Rollup over asterisk', async (connection) => { const res = await connection.query(` select rowId as "row", orderId as "order", orderDate as "date", sum(count) from ( select * from "ECommerce" as "ECommerce" ) a group by ROLLUP(1, 2, 3) order by 1, 2, 3 `); expect(res.rows).toMatchSnapshot(); }); executePg('SQL API: Extended nested Rollup over asterisk', async (connection) => { const res = await connection.query(` select * from ( select * from ( select rowId as "row", orderId as "order", sum(count) from ( select * from "ECommerce" as "ECommerce" ) a group by ROLLUP(row, order) ORDER BY "rowId" ASC NULLS FIRST, "orderId" ASC NULLS FIRST OFFSET 0 ROWS FETCH FIRST 100 ROWS ONLY ) q1 ) q2 ORDER BY q2.order, q2.row DESC limit 100 `); expect(res.rows).toMatchSnapshot(); }); executePg('SQL API: metabase count cast to float32 from push down', async (connection) => { const res = await connection.query(` select cast(count(*) as float) as "a0" from "Customers" `); expect(res.rows).toMatchSnapshot('metabase_count_cast_to_float32_from_push_down'); }); executePg('SQL API: NULLS FIRST/LAST SQL push down', async (connection) => { const res = await connection.query(` SELECT CASE WHEN "category" > 'G' THEN "category" ELSE NULL END AS "category" FROM "Products" WHERE LOWER("category") != 'invalid' GROUP BY 1 ORDER BY 1 ASC NULLS FIRST LIMIT 100 `); expect(res.rows).toMatchSnapshot('nulls_first_last_sql_push_down'); }); executePg('SQL API: APPROX_DISTINCT push down', async (connection) => { const res = await connection.query(` SELECT APPROX_DISTINCT("category") AS "categories" FROM "Products" `); expect(res.rows.map(row => Number(row.categories))).toEqual([3]); }); executePg('SQL API: Timeshift measure from cube', async (connection) => { const res = await connection.query(` SELECT DATE_TRUNC('month', orderDate) AS "orderDate", MEASURE(totalQuantity) AS "totalQuantity", MEASURE(totalQuantityPriorMonth) AS "totalQuantityPriorMonth" FROM "ECommerce" WHERE orderDate >= CAST('2020-01-01' AS DATE) AND orderDate < CAST('2021-01-01' AS DATE) GROUP BY 1 ORDER BY 1 ASC NULLS FIRST; `); expect(res.rows).toMatchSnapshot(); }); executePg('Tesseract: SQL API: Timeshift measure from cube', async (connection) => { const res = await connection.query(` SELECT DATE_TRUNC('month', orderDate) AS "orderDate", MEASURE(totalQuantity) AS "totalQuantity", MEASURE(totalQuantityPriorMonth) AS "totalQuantityPriorMonth" FROM "ECommerce" WHERE orderDate >= CAST('2020-01-01' AS DATE) AND orderDate < CAST('2021-01-01' AS DATE) GROUP BY 1 ORDER BY 1 ASC NULLS FIRST; `); expect(res.rows).toMatchSnapshot(); }); executePg('SQL API: Rolling Window YTD (year + month + day + date_trunc equal)', async (connection) => { // It's important to use day granularity - it tests for ambiguous names const res = await connection.query(` SELECT DATE_TRUNC('year', orderDate) AS "orderDateY", DATE_TRUNC('month', orderDate) AS "orderDateM", DATE_TRUNC('day', orderDate) AS "orderDateD", MEASURE(rollingCountYTD) AS "rollingCountYTD" FROM "BigECommerce" WHERE DATE_TRUNC('year', orderDate) = CAST('2020-01-01' AS DATE) GROUP BY 1, 2, 3 ORDER BY 3 ASC NULLS FIRST; `); expect(res.rows).toMatchSnapshot(); }); executePg('SQL API: Rolling Window YTD (year + month + day + date_trunc IN)', async (connection) => { // It's important to use day granularity - it tests for ambiguous names const res = await connection.query(` SELECT DATE_TRUNC('year', orderDate) AS "orderDateY", DATE_TRUNC('month', orderDate) AS "orderDateM", DATE_TRUNC('day', orderDate) AS "orderDateD", MEASURE(rollingCountYTD) AS "rollingCountYTD" FROM "BigECommerce" WHERE DATE_TRUNC('year', orderDate) IN (CAST('2020-01-01' AS DATE)) GROUP BY 1, 2, 3 ORDER BY 3 ASC NULLS FIRST; `); expect(res.rows).toMatchSnapshot(); }); executePg('SQL API: SQL push down push to cube quoted alias', async (connection) => { const res = await connection.query(` SELECT (NOT ("t0"."$temp1_output" IS NULL)) AS "result" FROM "public"."ECommerce" "ECommerce" LEFT JOIN ( SELECT CAST("ECommerce"."customerName" AS TEXT) AS "customerName", 1 AS "$temp1_output", MEASURE("ECommerce"."totalQuantity") AS "$__alias__0" FROM "public"."ECommerce" "ECommerce" GROUP BY 1 ORDER BY 3 DESC NULLS LAST, 1 ASC NULLS FIRST LIMIT 3 ) "t0" ON ( CAST("ECommerce"."customerName" AS TEXT) IS NOT DISTINCT FROM "t0"."customerName" ) GROUP BY 1 `); expect(res.rows).toMatchSnapshot(); }); executePg('SQL API: Date/time comparison with SQL push down', async (connection) => { const res = await connection.query(` SELECT MEASURE(BigECommerce.rollingCountBy2Day) FROM BigECommerce WHERE BigECommerce.orderDate < CAST('2021-01-01' AS TIMESTAMP) AND LOWER("city") = 'columbus' `); expect(res.rows).toMatchSnapshot(); }); executePg('SQL API: Date/time comparison with date_trunc with SQL push down', async (connection) => { const res = await connection.query(` SELECT MEASURE(BigECommerce.rollingCountBy2Week) FROM BigECommerce WHERE date_trunc('day', BigECommerce.orderDate) < CAST('2021-01-01' AS TIMESTAMP) AND LOWER("city") = 'columbus' `); expect(res.rows).toMatchSnapshot(); }); // The category filter keeps the grouped query off the rollups: with Tesseract, pushed-down // SQL would read the internal rollup copy, which this suite doesn't build executePg('SQL API: Window function over measure (running total)', async (connection) => { const res = await connection.query(` SELECT DATE_TRUNC('quarter', orderDate) AS "orderDateQ", MEASURE(count) AS "count", SUM(MEASURE(count)) OVER ( ORDER BY DATE_TRUNC('quarter', orderDate) ROWS UNBOUNDED PRECEDING ) AS "runningTotal" FROM "BigECommerce" WHERE DATE_TRUNC('year', orderDate) = CAST('2020-01-01' AS DATE) AND category = 'Furniture' GROUP BY 1 ORDER BY 1 ASC NULLS FIRST; `); expect(res.rows).toMatchSnapshot(); }); // A union whose queries reach one data source is pushed down as a single set operation, // bounded by the row cap. Order across the queries of a union is not defined, so rows // are compared sorted. const sortedRows = (rows: Record[]) => rows .map(row => JSON.stringify(row)) .sort(); executePg('SQL API: union all push down', async (connection) => { const res = await connection.query(` SELECT 'products' AS source, category FROM Products GROUP BY 1, 2 UNION ALL SELECT 'orders' AS source, category FROM ECommerce GROUP BY 1, 2 `); expect(sortedRows(res.rows)).toEqual(sortedRows([ { source: 'orders', category: 'Furniture' }, { source: 'orders', category: 'Office Supplies' }, { source: 'orders', category: 'Technology' }, { source: 'products', category: 'Furniture' }, { source: 'products', category: 'Office Supplies' }, { source: 'products', category: 'Technology' }, ])); }); executePg('SQL API: union distinct push down', async (connection) => { const res = await connection.query(` SELECT category FROM Products GROUP BY 1 UNION SELECT category FROM ECommerce GROUP BY 1 `); expect(sortedRows(res.rows)).toEqual(sortedRows([ { category: 'Furniture' }, { category: 'Office Supplies' }, { category: 'Technology' }, ])); }); executePg('SQL API: union with a limited query push down', async (connection) => { // Which two categories the limited query reads is not defined without an order, // which would keep the union out of push down; how many it reads is const res = await connection.query(` (SELECT category FROM Products GROUP BY 1 LIMIT 2) UNION ALL (SELECT category FROM ECommerce GROUP BY 1) `); expect(res.rows.length).toEqual(5); expect(new Set(res.rows.map(row => row.category))).toEqual( new Set(['Furniture', 'Office Supplies', 'Technology']) ); }); executePg('SQL API: EXTRACT DOW from column reference in SQL push down', async (connection) => { const res = await connection.query(` SELECT MEASURE(BigECommerce.totalQuantity) as qty FROM BigECommerce WHERE EXTRACT(DOW FROM BigECommerce.orderDate) = 3 `); expect(res.rows).toEqual([{ qty: 35 }]); }); executePg('SQL API: filter a time dimension by a list of separate days', async (connection) => { const res = await connection.query(` SELECT "rowId" FROM ECommerce WHERE "customOrderDateNoPreAgg" IN ( CAST('2020-01-23T00:00:00.000Z' AS TIMESTAMP), CAST('2020-06-25T00:00:00.000Z' AS TIMESTAMP), CAST('2020-10-19T00:00:00.000Z' AS TIMESTAMP) ) GROUP BY 1 ORDER BY 1 `); expect(res.rows.map(row => Number(row.rowId))).toEqual([523, 1995, 3059, 3060, 9584]); }); }); }