public static ITable SelectAnyAllNonCorrelated(this ITable table, ObjectName[] leftColumns, SqlExpressionType op, ITable rightTable)
{
if (rightTable.TableInfo.ColumnCount != leftColumns.Length) {
throw new ArgumentException(String.Format("The right table has {0} columns that is different from the specified column names ({1})",
rightTable.TableInfo.ColumnCount, leftColumns.Length));
}
// Handle trivial case of no entries to select from
if (table.RowCount == 0)
return table;
// Resolve the vars in the left table and check the references are
// compatible.
var sz = leftColumns.Length;
var leftColMap = new int[sz];
var rightColMap = new int[sz];
for (int i = 0; i < sz; ++i) {
leftColMap[i] = table.FindColumn(leftColumns[i]);
rightColMap[i] = i;
if (leftColMap[i] == -1)
throw new Exception("Invalid reference: " + leftColumns[i]);
var leftType = table.TableInfo[leftColMap[i]].ColumnType;
var rightType = rightTable.TableInfo[i].ColumnType;
if (!leftType.IsComparable(rightType)) {
throw new ArgumentException(String.Format("The type of the sub-query expression {0}({1}) " +
"is not compatible with the sub-query type {2}.",
leftColumns[i], leftType, rightType));
}
}
IEnumerable<int> rows;
if (!op.IsSubQuery())
throw new ArgumentException(String.Format("The operator {0} is not a sub-query form.", op));
if (op.IsAll()) {
// ----- ALL operation -----
// We work out as follows:
// For >, >= type ALL we find the highest value in 'table' and
// select from 'source' all the rows that are >, >= than the
// highest value.
// For <, <= type ALL we find the lowest value in 'table' and
// select from 'source' all the rows that are <, <= than the
// lowest value.
// For = type ALL we see if 'table' contains a single value. If it
// does we select all from 'source' that equals the value, otherwise an
// empty table.
// For <> type ALL we use the 'not in' algorithm.
if (op == SqlExpressionType.AllGreaterThan ||
op == SqlExpressionType.AllGreaterOrEqualThan) {
// Select the last from the set (the highest value),
var highestCells = rightTable.GetLastValues(rightColMap);
// Select from the source table all rows that are > or >= to the
// highest cell,
rows = table.SelectRows(leftColMap, op, highestCells);
} else if (op == SqlExpressionType.AllSmallerThan ||
op == SqlExpressionType.AllSmallerOrEqualThan) {
// Select the first from the set (the lowest value),
var lowestCells = rightTable.GetFirstValues(rightColMap);
// Select from the source table all rows that are < or <= to the
// lowest cell,
rows = table.SelectRows(leftColMap, op, lowestCells);
} else if (op == SqlExpressionType.AllEqual) {
// Select the single value from the set (if there is one).
var singleCell = rightTable.GetSingleValues(rightColMap);
if (singleCell != null) {
// Select all from source_table all values that = this cell
rows = table.SelectRows(leftColMap, op, singleCell);
} else {
// No single value so return empty set (no value in LHS will equal
// a value in RHS).
return table.EmptySelect();
}
} else if (op == SqlExpressionType.AllNotEqual) {
// Equiv. to NOT IN
rows = table.SelectRowsNotIn(rightTable, leftColMap, rightColMap);
} else {
throw new ArgumentException(String.Format("Operator of type {0} is not valid in ALL functions.", op.SubQueryPlainType()));
}
} else {
// ----- ANY operation -----
// We work out as follows:
// For >, >= type ANY we find the lowest value in 'table' and
// select from 'source' all the rows that are >, >= than the
// lowest value.
// For <, <= type ANY we find the highest value in 'table' and
// select from 'source' all the rows that are <, <= than the
// highest value.
// For = type ANY we use same method from INHelper.
// For <> type ANY we iterate through 'source' only including those
// rows that a <> query on 'table' returns size() != 0.
if (op == SqlExpressionType.AnyGreaterThan ||
op == SqlExpressionType.AnyGreaterOrEqualThan) {
// Select the first from the set (the lowest value),
var lowestCells = rightTable.GetFirstValues(rightColMap);
// Select from the source table all rows that are > or >= to the
// lowest cell,
rows = table.SelectRows(leftColMap, op, lowestCells);
} else if (op == SqlExpressionType.AnySmallerThan ||
op == SqlExpressionType.AnySmallerOrEqualThan) {
// Select the last from the set (the highest value),
var highestCells = rightTable.GetLastValues(rightColMap);
// Select from the source table all rows that are < or <= to the
// highest cell,
rows = table.SelectRows(leftColMap, op, highestCells);
} else if (op == SqlExpressionType.AnyEqual) {
// Equiv. to IN
rows = table.SelectRowsIn(rightTable, leftColMap, rightColMap);
} else if (op == SqlExpressionType.AnyNotEqual) {
// Select the value that is the same of the entire column
var cells = rightTable.GetSingleValues(rightColMap);
if (cells != null) {
// All values from 'source_table' that are <> than the given cell.
rows = table.SelectRows(leftColMap, op, cells);
} else {
// No, this means there are different values in the given set so the
// query evaluates to the entire table.
return table;
}
} else {
throw new ArgumentException(String.Format("Operator of type {0} is not valid in ANY functions.", op.SubQueryPlainType()));
}
}
return new VirtualTable(table, rows.ToArray());
}