40select.js

/*
//
// Select run-time part for Alasql.js
// Date: 03.11.2014
// (c) 2014, Andrey Gershun
//
*/

//
// Main part of SELECT procedure
//

/* global yy */

yy.Select = class Select {
	constructor(params) {
		Object.assign(this, params);
	}

	toString() {
		var s;
		s = '';
		if (this.explain) {
			s += 'EXPLAIN ';
		}
		s += 'SELECT ';
		if (this.modifier) {
			s += this.modifier + ' ';
		}
		if (this.distinct) {
			s += 'DISTINCT ';
		}
		if (this.top) {
			s += 'TOP ' + this.top.value + ' ';
			if (this.percent) {
				s += 'PERCENT ';
			}
		}
		s += this.columns
			.map(function (col) {
				var s;
				s = col.toString();
				if (typeof col.as !== 'undefined') {
					s += ' AS ' + col.as;
				}
				return s;
			})
			.join(', ');
		if (this.from) {
			s +=
				' FROM ' +
				this.from
					.map(function (f) {
						var ss;
						ss = f.toString();
						if (f.as) {
							ss += ' AS ' + f.as;
						}
						return ss;
					})
					.join(',');
		}
		if (this.joins) {
			s += this.joins
				.map(function (jn) {
					var ss;
					ss = ' ';
					if (jn.joinmode) {
						ss += jn.joinmode + ' ';
					}
					if (jn.table) {
						ss += 'JOIN ' + jn.table.toString();
					} else if (jn.select) {
						ss += 'JOIN (' + jn.select.toString() + ')';
					} else if (jn instanceof alasql.yy.Apply) {
						ss += jn.toString();
					} else {
						throw new Error('Wrong type in JOIN mode');
					}
					if (jn.as) {
						ss += ' AS ' + jn.as;
					}
					if (jn.using) {
						ss += ' USING ' + jn.using.toString();
					}
					if (jn.on) {
						ss += ' ON ' + jn.on.toString();
					}
					return ss;
				})
				.join('');
		}
		if (this.where) {
			s += ' WHERE ' + this.where.toString();
		}
		if (this.group && this.group.length > 0) {
			s +=
				' GROUP BY ' +
				this.group
					.map(function (grp) {
						return grp.toString();
					})
					.join(', ');
		}
		if (this.having) {
			s += ' HAVING ' + this.having.toString();
		}
		if (this.order && this.order.length > 0) {
			s +=
				' ORDER BY ' +
				this.order
					.map(function (ord) {
						return ord.toString();
					})
					.join(', ');
		}
		if (this.limit) {
			s += ' LIMIT ' + this.limit.value;
		}
		if (this.offset) {
			s += ' OFFSET ' + this.offset.value;
		}
		if (this.union) {
			s += ' UNION ' + (this.corresponding ? 'CORRESPONDING ' : '') + this.union.toString();
		}
		if (this.unionall) {
			s += ' UNION ALL ' + (this.corresponding ? 'CORRESPONDING ' : '') + this.unionall.toString();
		}
		if (this.except) {
			s += ' EXCEPT ' + (this.corresponding ? 'CORRESPONDING ' : '') + this.except.toString();
		}
		if (this.intersect) {
			s += ' INTERSECT ' + (this.corresponding ? 'CORRESPONDING ' : '') + this.intersect.toString();
		}
		return s;
	}

	/**
	 Select statement in expression
	 */
	toJS(context) {
		var s =
			'alasql.utils.flatArray(this.queriesfn[' +
			(this.queriesidx - 1) +
			'](this.params,null,' +
			context +
			'))[0]';

		//	var s = '(ee=alasql.utils.flatArray(this.queriesfn['+(this.queriesidx-1)+'](this.params,null,'+context+')),console.log(999,ee),ee[0])';
		return s;
	}

	// Compile SELECT statement
	compile(databaseid, params) {
		var db = alasql.databases[databaseid];
		// Create variable for query
		var query = new Query();

		// Array with columns to be removed
		query.removeKeys = [];
		query.aggrKeys = [];

		query.explain = this.explain; // Explain
		query.explaination = [];
		query.explid = 1;
		query.modifier = this.modifier;

		query.database = db;
		// 0. Precompile whereexists
		this.compileWhereExists(query);

		// 0. Precompile queries for IN, NOT IN, ANY and ALL operators
		this.compileQueries(query);

		query.defcols = this.compileDefCols(query, databaseid);

		// 1. Compile FROM clause
		query.fromfn = this.compileFrom(query);

		// 2. Compile JOIN clauses
		if (this.joins) {
			this.compileJoins(query);
		}

		// todo?: 3. Compile SELECT clause
		// For ROWNUM()
		query.rownums = [];
		query.grouprownums = [];
		query.windowaggrs = []; // For window aggregate functions (COUNT/MAX/MIN/SUM/AVG with OVER)

		// Check if INTO OBJECT() is used - this affects how arrow expressions are compiled
		if (this.into instanceof yy.FuncValue && this.into.funcid.toUpperCase() === 'OBJECT') {
			query.intoObject = true;
		}

		this.compileSelectGroup0(query);

		if (this.group || query.selectGroup.length > 0) {
			query.selectgfns = this.compileSelectGroup1(query);
		} else {
			query.selectfns = this.compileSelect1(query, params);
		}

		// Remove columns clause
		this.compileRemoveColumns(query);

		// 5. Optimize WHERE and JOINS
		if (this.where) {
			this.compileWhereJoins(query);
		}

		// 4. Compile WHERE clause
		query.wherefn = this.compileWhere(query);

		// 6. Compile GROUP BY
		if (this.group || query.selectGroup.length > 0) {
			query.groupfn = this.compileGroup(query);
		}

		// 6. Compile HAVING
		if (this.having) {
			query.havingfn = this.compileHaving(query);
		}

		// 8. Compile ORDER BY clause
		if (this.order) {
			query.orderfn = this.compileOrder(query, params);
			// Copy orderColumns to query for union handling
			query.orderColumns = this.orderColumns;
		}

		if (this.group || query.selectGroup.length > 0) {
			query.selectgfn = this.compileSelectGroup2(query);
		} else {
			query.selectfn = this.compileSelect2(query, params);
		}

		// 7. Compile DISTINCT, LIMIT and OFFSET
		query.distinct = this.distinct;

		// 9. Compile PIVOT clause
		if (this.pivot) query.pivotfn = this.compilePivot(query);
		if (this.unpivot) query.pivotfn = this.compileUnpivot(query);

		// 10. Compile TOP/LIMIT/OFFSET/FETCH clause
		if (this.top) {
			query.limit = this.top.value;
		} else if (this.limit) {
			query.limit = this.limit.value;
			if (this.offset) {
				query.offset = this.offset.value;
			}
		}

		query.percent = this.percent;

		// 9. Compile ordering function for UNION and UNIONALL
		query.corresponding = this.corresponding; // If CORRESPONDING flag exists
		if (this.union) {
			query.unionfn = this.union.compile(databaseid);
			// ORDER BY is now at the top level, not in the union clause
			if (!query.orderfn && this.union.order) {
				query.orderfn = this.union.compileOrder(query, params);
			}
		} else if (this.unionall) {
			query.unionallfn = this.unionall.compile(databaseid);
			// ORDER BY is now at the top level, not in the unionall clause
			if (!query.orderfn && this.unionall.order) {
				query.orderfn = this.unionall.compileOrder(query, params);
			}
		} else if (this.except) {
			query.exceptfn = this.except.compile(databaseid);
			// ORDER BY is now at the top level, not in the except clause
			if (!query.orderfn && this.except.order) {
				query.orderfn = this.except.compileOrder(query, params);
			}
		} else if (this.intersect) {
			query.intersectfn = this.intersect.compile(databaseid);
			// ORDER BY is now at the top level, not in the intersect clause
			if (!query.orderfn && this.intersect.order) {
				query.orderfn = this.intersect.compileOrder(query, params);
			}
		}

		// SELECT INTO
		if (this.into) {
			if (this.into instanceof yy.Table) {
				// Save into the table in database
				if (
					alasql.options.autocommit &&
					alasql.databases[this.into.databaseid || databaseid].engineid
				) {
					// For external database when AUTOCOMMIT is ONs
					query.intoallfns = `return alasql
								.engines[${JSON.stringify(alasql.databases[this.into.databaseid || databaseid].engineid)}]
								.intoTable(
									${JSON.stringify(this.into.databaseid || databaseid)},
									${JSON.stringify(this.into.tableid)},
									this.data,
									columns,
									cb
								);`;
				} else {
					// Into AlaSQL tables
					query.intofns = `alasql
							.databases[${JSON.stringify(this.into.databaseid || databaseid)}]
							.tables[${JSON.stringify(this.into.tableid)}]
							.data.push(r);
						`;
				}
			} else if (this.into instanceof yy.VarValue) {
				//
				// Save into local variable
				// SELECT * INTO @VAR1 FROM ?
				//
				query.intoallfns = `
					alasql.vars[${JSON.stringify(this.into.variable)}]=this.data;
					res=this.data.length;
					if(cb) res = cb(res);
					return res;
				`;
			} else if (this.into instanceof yy.FuncValue) {
				//
				// If this is INTO() function, then call it
				// with one or two parameters
				//
				var funcid = this.into.funcid.toUpperCase();
				var qs = 'return alasql.into[' + JSON.stringify(funcid) + '](';
				if (this.into.args && this.into.args.length > 0) {
					qs += this.into.args[0].toJS() + ',';
					if (this.into.args.length > 1) {
						qs += this.into.args[1].toJS() + ',';
					} else {
						qs += 'undefined,';
					}
				} else {
					qs += 'undefined, undefined,';
				}
				query.intoallfns = qs + 'this.data,columns,cb)';
				// Mark that OBJECT should preserve array results
				if (funcid === 'OBJECT') {
					query.preserveArrayResult = true;
				}
			} else if (this.into instanceof yy.ParamValue) {
				//
				// Save data into parameters array
				// like alasql('SELECT * INTO ? FROM ?',[outdata,srcdata]);
				// or SELECT * INTO $variable FROM ?
				//
				// Distinguish between ? (numeric param - push to array) and $variable (string param - replace array)
				if (typeof this.into.param === 'string') {
					// $variable syntax - replace the array
					query.intoallfns = `
						if(!params[${JSON.stringify(this.into.param)}]) params[${JSON.stringify(this.into.param)}]=[];
						params[${JSON.stringify(this.into.param)}]=this.data;
						res=this.data.length;
						if(cb) res = cb(res);
						return res;
					`;
				} else {
					// ? syntax - push to existing array
					query.intofns = `params[${JSON.stringify(this.into.param)}].push(r)`;
				}
			}

			if (query.intofns) {
				// Create intofn function
				query.intofn = new Function('r,i,params,alasql', 'var y;' + query.intofns);
			} else if (query.intoallfns) {
				// Create intoallfn function
				query.intoallfn = new Function('columns,cb,params,alasql', 'var y;' + query.intoallfns);
			}
		}
		// Now, compile all togeather into one function with query object in scope
		var statement = function (params, cb, oldscope) {
			query.params = params;
			// Note the callback function has the data and error reversed due to existing code in promiseExec which has the
			// err and data swapped.  This trickles down into alasql.exec and further. Rather than risk breaking the whole thing,
			// the (data, err) standard is maintained here.
			var res1 = queryfn(query, oldscope, function (res, err) {
				if (err) {
					if (cb) {
						return cb(null, err);
					}
					throw err;
				}
				if (query.rownums.length > 0) {
					for (var i = 0, ilen = res.length; i < ilen; i++) {
						for (var j = 0, jlen = query.rownums.length; j < jlen; j++) {
							res[i][query.rownums[j]] = i + 1;
						}
					}
				}

				// Handle GROUP_ROW_NUMBER() and ROW_NUMBER() OVER (PARTITION BY ...) - restart numbering when grouping column(s) change
				if (query.grouprownums && query.grouprownums.length > 0) {
					for (var j = 0, jlen = query.grouprownums.length; j < jlen; j++) {
						var config = query.grouprownums[j];
						var partitionColumns;

						// Determine which columns to partition by
						if (config.partitionColumns && config.partitionColumns.length > 0) {
							// Use explicit PARTITION BY columns
							partitionColumns = config.partitionColumns;
						} else {
							// Fall back to first column for GROUP_ROW_NUMBER()
							var columnKeys = Object.keys(res[0] || {});
							partitionColumns = [columnKeys[0]];
						}

						var prevValues = null;
						var rowNum = 0;

						for (var i = 0, ilen = res.length; i < ilen; i++) {
							// Get current partition key (combination of all partition columns)
							var currentValues = partitionColumns
								.map(function (col) {
									return res[i][col];
								})
								.join('|');

							// Reset counter when partition changes
							if (i === 0 || currentValues !== prevValues) {
								rowNum = 1;
							} else {
								rowNum++;
							}

							res[i][config.as] = rowNum;
							prevValues = currentValues;
						}
					}
				}

				// Window offset functions: LEAD/LAG/FIRST_VALUE/LAST_VALUE
				// Scans results linearly to compute values based on relative row positions
				if (query.windowFuncs && query.windowFuncs.length > 0) {
					for (var j = 0; j < query.windowFuncs.length; j++) {
						var wf = query.windowFuncs[j];
						var partCols = wf.partitionColumns || [];
						var exprCol = wf.args[0] && wf.args[0].columnid;

						// Parse offset and default value arguments (handles negative literals like -1)
						var getArg = function (a) {
							if (!a) return undefined;
							if (a.value !== undefined) return a.value;
							if (a.op === '-' && a.right && a.right.value !== undefined) return -a.right.value;
							return undefined;
						};
						var offset = getArg(wf.args[1]);
						if (offset === undefined) offset = 1;
						var defVal = getArg(wf.args[2]);
						if (defVal === undefined) defVal = null;

						// Track partition boundaries as we scan
						var prevPart = null;
						var partStart = 0;

						// Scan rows, processing each partition when boundaries change
						for (var i = 0; i <= res.length; i++) {
							var currPart =
								i < res.length && partCols.length > 0
									? partCols
											.map(function (c) {
												return res[i][c];
											})
											.join('|')
									: null;

							// When partition ends, compute window function for all rows in partition
							if (i === res.length || (prevPart !== null && currPart !== prevPart)) {
								for (var k = partStart; k < i; k++) {
									var targetIdx;
									if (wf.funcid === 'LEAD') {
										targetIdx = k + offset;
										res[k][wf.as] = targetIdx < i && exprCol ? res[targetIdx][exprCol] : defVal;
									} else if (wf.funcid === 'LAG') {
										targetIdx = k - offset;
										res[k][wf.as] =
											targetIdx >= partStart && exprCol ? res[targetIdx][exprCol] : defVal;
									} else if (wf.funcid === 'FIRST_VALUE') {
										res[k][wf.as] = exprCol ? res[partStart][exprCol] : null;
									} else if (wf.funcid === 'LAST_VALUE') {
										res[k][wf.as] = exprCol ? res[i - 1][exprCol] : null;
									}
								}
								partStart = i;
							}
							prevPart = currPart;
						}
					}
				}

				// Handle window aggregate functions - COUNT/MAX/MIN/SUM/AVG with OVER (PARTITION BY ...)
				if (query.windowaggrs && query.windowaggrs.length > 0) {
					for (var j = 0, jlen = query.windowaggrs.length; j < jlen; j++) {
						var config = query.windowaggrs[j];
						var partitions = {};

						// Group rows by partition
						for (var i = 0, ilen = res.length; i < ilen; i++) {
							var partitionKey =
								config.partitionColumns && config.partitionColumns.length > 0
									? config.partitionColumns
											.map(function (col) {
												return res[i][col];
											})
											.join('|')
									: '__all__';

							if (!partitions[partitionKey]) partitions[partitionKey] = [];
							partitions[partitionKey].push(i);
						}

						// Calculate and assign aggregate for each partition
						for (var partitionKey in partitions) {
							var rowIndices = partitions[partitionKey];
							var values = [];
							var colId = config.expression && config.expression.columnid;

							// Collect values from partition rows
							if (config.aggregatorid !== 'COUNT' || (colId && colId !== '*')) {
								for (var k = 0; k < rowIndices.length; k++) {
									var val = res[rowIndices[k]][colId];
									if (val != null) values.push(val);
								}
							}

							// Calculate aggregate
							var aggregateValue;
							switch (config.aggregatorid) {
								case 'COUNT':
									aggregateValue = colId && colId !== '*' ? values.length : rowIndices.length;
									break;
								case 'SUM':
									aggregateValue = values.reduce(function (sum, v) {
										return sum + v;
									}, 0);
									break;
								case 'AVG':
									aggregateValue =
										values.length > 0
											? values.reduce(function (sum, v) {
													return sum + v;
												}, 0) / values.length
											: null;
									break;
								case 'MAX':
									aggregateValue = values.length > 0 ? Math.max.apply(null, values) : null;
									break;
								case 'MIN':
									aggregateValue = values.length > 0 ? Math.min.apply(null, values) : null;
									break;
							}

							// Assign aggregate value to all rows in partition
							for (var k = 0; k < rowIndices.length; k++) {
								res[rowIndices[k]][config.as] = aggregateValue;
							}
						}
					}
				}

				var res2 = modify(query, res);

				if (cb) {
					cb(res2);
				}
				return res2;
			});
			return res1;
		};

		statement.query = query;
		return statement;
	}

	execute(databaseid, params, cb) {
		return this.compile(databaseid)(params, cb);
		//	throw new Error('Insert statement is should be compiled')
	}

	compileWhereExists(query) {
		if (!this.exists) return;
		query.existsfn = this.exists.map(function (ex) {
			var nq = ex.compile(query.database.databaseid);
			nq.query.modifier = 'RECORDSET';
			return nq;
		});
	}

	compileQueries(query) {
		if (!this.queries) return;

		// Helper function to detect if a subquery might be correlated
		// A subquery is correlated if it references tables not in its own FROM clause
		const isCorrelated = (subquery, outerQuery) => {
			if (!subquery.from) return false;

			// Get table names from subquery's FROM clause
			const subqueryTables = new Set();
			subquery.from.forEach(f => {
				if (f.tableid) subqueryTables.add(f.tableid);
				if (f.as) subqueryTables.add(f.as);
			});

			// Check if WHERE clause references tables not in subquery's FROM
			const referencesExternal = node => {
				if (!node) return false;

				// Check Column nodes for tableid using instanceof
				if (node instanceof yy.Column) {
					if (node.tableid && !subqueryTables.has(node.tableid)) {
						return true;
					}
				}

				// Recursively check own properties only (not inherited)
				for (let key of Object.keys(node)) {
					if (node[key] && typeof node[key] === 'object') {
						if (referencesExternal(node[key])) return true;
					}
				}
				return false;
			};

			return referencesExternal(subquery.where) || referencesExternal(subquery.columns);
		};

		query.queriesfn = this.queries.map(function (q, idx) {
			var nq = q.compile(query.database.databaseid);
			nq.query.modifier = 'RECORDSET';

			// Mark as correlated if it references external tables
			nq.query.isCorrelated = isCorrelated(q, query);

			// If the nested query has its own queries, ensure they're compiled too
			// This handles nested subqueries properly
			if (q.queries && q.queries.length > 0) {
				nq.query.queriesfn = q.queries.map(function (qq) {
					var nnq = qq.compile(query.database.databaseid);
					nnq.query.modifier = 'RECORDSET';
					return nnq;
				});
			}
			return nq;
		});
	}
};

/**
 Modify res according modifier
 @function
 @param {object} query Query object
 @param res {object|number|string|boolean} res Data to be converted
 */
function modify(query, res) {
	// jshint ignore:line

	/* If source is a primitive value then return it */
	if (
		typeof res === 'undefined' ||
		typeof res === 'number' ||
		typeof res === 'string' ||
		typeof res === 'boolean'
	) {
		return res;
	}

	var modifier = query.modifier || alasql.options.modifier;
	var columns = query.columns;

	// If dirtyColumns is true, we need to merge columns from data with existing columns
	// This happens when SELECT * is used with dynamic data sources (like parameters)
	if (query.dirtyColumns && res.length > 0) {
		var allcol = {};
		// First, scan the data to find all column names
		for (var i = Math.min(res.length, alasql.options.columnlookup || 10) - 1; 0 <= i; i--) {
			for (var key in res[i]) {
				allcol[key] = true;
			}
		}

		// Create columns from data
		var dataColumns = Object.keys(allcol).map(function (columnid) {
			return {columnid: columnid};
		});

		// If we don't have any columns yet, just use the data columns
		if (!columns || columns.length === 0) {
			columns = dataColumns;
		} else {
			// We have some columns (e.g., from explicit column expressions),
			// merge them with data columns, avoiding duplicates
			var existingColumnIds = {};
			columns.forEach(function (col) {
				existingColumnIds[col.columnid] = true;
			});

			// Add data columns that aren't already in the list
			dataColumns.forEach(function (col) {
				if (!existingColumnIds[col.columnid]) {
					columns.push(col);
				}
			});
		}
	} else if (typeof columns === 'undefined' || columns.length === 0) {
		// Try to create columns
		if (res.length > 0) {
			var allcol = {};
			for (var i = Math.min(res.length, alasql.options.columnlookup || 10) - 1; 0 <= i; i--) {
				for (var key in res[i]) {
					allcol[key] = true;
				}
			}

			columns = Object.keys(allcol).map(function (columnid) {
				return {columnid: columnid};
			});
		} else {
			// Cannot recognize columns
			columns = [];
			if (query && query.sources) {
				query.sources.forEach(source => {
					if (source && source.columns && Array.isArray(source.columns)) {
						columns = columns.concat(source.columns);
					}
				});
			}
		}
	}

	switch (modifier) {
		case 'VALUE':
			if (res.length === 0) return undefined;
			const keyValue = columns && columns.length > 0 ? columns[0].columnid : Object.keys(res[0])[0];
			return res[0][keyValue];

		case 'ROW':
			if (res.length === 0) return undefined;
			return Object.values(res[0]);

		case 'COLUMN':
			if (res.length === 0) return [];

			let key;
			if (columns && columns.length > 0) {
				key = columns[0].columnid;
			} else {
				key = Object.keys(res[0])[0];
			}

			let ar = [];
			for (var i = 0, ilen = res.length; i < ilen; i++) {
				ar.push(res[i][key]);
			}

			// Apply DISTINCT if specified
			if (query.distinct) {
				ar = alasql.utils.distinctArray(ar);
			}

			return ar;

		case 'MATRIX':
			if (res.length === 0) return undefined;
			return res.map(row => columns.map(col => row[col.columnid]));

		case 'INDEX':
			if (res.length === 0) return undefined;
			const keyIndex = columns && columns.length > 0 ? columns[0].columnid : Object.keys(res[0])[0];
			const valIndex = columns && columns.length > 1 ? columns[1].columnid : Object.keys(res[0])[1];
			return res.reduce((acc, row) => ({...acc, [row[keyIndex]]: row[valIndex]}), {});

		case 'RECORDSET':
			// Assuming alasql.Recordset is available in the scope
			return new alasql.Recordset({columns: columns, data: res});

		case 'TEXTSTRING':
			if (res.length === 0) return undefined;
			const keyTextString =
				columns && columns.length > 0 ? columns[0].columnid : Object.keys(res[0])[0];
			return res.map(row => row[keyTextString]).join('\n');

		case 'ALASQL_DETAILS':
			// Returns both data and column metadata in a structured format
			// Useful for internal operations that need both data and column info in one call
			return {
				data: res,
				columns: columns,
				length: res.length,
			};
	}
	return res;
}