A boolean still reaches SQL as a value on a booleanType 'none' dialect: all() in having:, and a boolean primary_key #3171

Follow-up to #3147. About 190 Malloy shapes were run through the translator on the lang test dialect _mssql_ (booleanType: 'none'), with SQL generated for anything that translated clean. Every example below is against this model:

source: m is _mssql_.table('aTable') extend {
  dimension: big is ai > 5
  measure: n is count()
  measure: anyn is n > 1
  join_one: o is _mssql_.table('aTable') extend { dimension: big is ai > 5 } on o.ai = ai
}

Booleans that still reach SQL as a value

An ungrouped boolean measure as a condition. As a result column all(anyn) is refused; as a having: condition it is not, and the ungrouped aggregate wraps the condition in MAX(...) OVER. A filtered boolean measure does the same.

run: m -> { group_by: ai; aggregate: n; having: all(anyn) }

SELECT base."ai" as "ai", COUNT(1) as "n" FROM aTable as base
GROUP BY base."ai"
HAVING MAX(((COUNT(1))>1)) OVER ()

Fix site: expr-ungroup.ts, refuse a boolean operand, hint "ungroup the value and test it: all(n) > 1".

A boolean primary key. Nothing checks the key's type, so symmetric aggregates hash the condition, pk.count() counts it, and with compares it.

source: pk is _mssql_.table('aTable') extend {
  dimension: big is ai > 5
  primary_key: big
}
source: pkj is _mssql_.table('aTable') extend {
  dimension: big is ai > 5
  join_one: pk on pk.ai = ai
  join_one: w is pk with big
}
run: pkj -> { aggregate: x is pk.ai.sum() }
run: pkj -> { aggregate: x is pk.count() }
run: pkj -> { aggregate: x is w.ai.sum() }

SUM(DISTINCT CAST(CAST(HASHBYTES('MD5', CAST((pk_0."ai">5) AS NVARCHAR(MAX))) AS BIGINT) ...
COUNT(DISTINCT (pk_0."ai">5))
LEFT JOIN aTable AS w_0 ON (w_0."ai">5)=(base."ai">5)

Fix site: the key resolution in refined-source.ts; with a boolean key refused, the with case falls out as the existing primary-key type mismatch.

One mistake, two errors

A join on with an error-typed expression. fixupJoinOn in join.ts tests type !== 'boolean'; skipping an error type is the same fix as filters.ts.

source: s2 is m extend { join_one: q is m on -astr }

'unary minus' Can't use type string
join conditions must be boolean expressions

Time truncation of a boolean. The truncation logs and then returns the operand's type, so the result column is refused again. Same class as ExprMinus before 80ff7df on #3147.

run: m -> { group_by: x is big.day }

Cannot do time truncation on type 'boolean'
'sqlserver' does not support a boolean as a result column; write the value, pick 'yes' when big.day else 'no'

A range. One refusal for each of the two comparisons it desugars to.

run: m -> { where: big ? 1 to 10; aggregate: n }

'sqlserver' does not support a boolean compared with >=; ...
'sqlserver' does not support a boolean compared with <; ...

A scalar boolean in aggregate:.

run: m -> { aggregate: t is true }

Cannot use a scalar field in an aggregate operation, ...
'sqlserver' does not support a boolean as a result column; ...

Binary arithmetic on an error operand still infers number, so the filter complains again. Same on main; this is the error-type-inference design, not a regression.

run: m -> { where: nothing * 1; aggregate: n }

'nothing' is not defined
Filter expression must have boolean value

Pre-existing, not about booleans

Each of these translates clean and then throws in the compiler.

run: m -> { aggregate: x is ai.count() }
-- Error: 'ai' is not a source or join.

run: m -> { group_by: ai; aggregate: n; having: exclude(n, ai) > 1 }
-- Internal Error, cannot find groupset with key exclude/2:ai

run: m -> { group_by: ai; calculate: r is rank() { partition_by: astr } }
-- partition_by on a dimension that is not an output field (noted last round)

Ruled out

Each of these produced clean SQL or a single error at the right place.

• Literals and sql_boolean as values, refused: group_by: t is true, group_by: x is sql_boolean("""ai > 1""").
• Boolean parameters: pm(p is false) -> { aggregate: n } renders WHERE (1=0); no value use is reachable.
• Conditions, clean SQL: where: big, having: anyn, drill: big, join_one: q is m on big, extend { where: big }, n { where: big }; where: not big and != take the CASE form.
• pick and case in every role, refused: pick big when ai > 1 else false as a condition, pick big when big else big, case big when true then 1 end.
• Partials and alternations, refused: ai ? big, ad ? big, big ? big, big ? (= true), ai ? > 1 & big, ai ? > 1 | big, ai ? 1 | true.
• Operators, generic type errors: big + 1, -big, big ~ 'x', big ~ r'x', big ? @2020 for 1 day, day(big), big + 1 day.
• Casts, refused: big::number, big::boolean, ai:::boolean, 'true'::boolean.
• Null tests and coalesce, refused: big is null, big != null, null ?? big, big ?? false.
• Aggregates and windows, a type error, an overload error, or the refusal: count(big), sum(big), max(big), string_agg(big), lag(big), lead(big, 1, false), first_value(big), avg_moving(big, 2), exclude(anyn, ai) as a result.
• Functions, overload error or refusal: ifnull(big, false), nullif(big, true), coalesce(big, false), greatest(big, big), length(big), round(big), concat(astr, big).
• Wildcards leave the boolean out with the warning, an explicit one is refused: select: *, select: o.*, select: * { except: big }, select: *, o.big.
• Pipelines and query sources, refused at the first stage: m -> { group_by: ai, big } -> { select: ai }, source: qs is m -> { select: ai, big }.
• Source-level shapes: rename: d is big then group_by: d refused; accept: and except: fine; order_by: big is "not in output".
• nest: of a boolean, refused, and the dialect has no nesting in any case.
• Record and array literals, {x is big} and [big]: the dialect has neither; on a none dialect that did, the elements are not checked.
• sql_number("""${big}""") renders (base."ai">5) as "x". Raw SQL is the author's.
• Twenty wrapper kinds around (big ?? false) log exactly one error: not, and, =, +, unary -, ::string, is null, ? true, pick, case, concat(), .day, ~ 'x', count(), lag(), [...], {y is ...}, group_by:, where:, n { where: ... }.

Catching this at a higher level

Every value comes through one funnel, ExpressionDef.getExpression, with 68 call sites, while the condition consumers number about ten: where, having, drill, join on, filters, and / or / not, pick and case when, filtered aggregates. Inverting the default would make a forgotten site a refusal instead of silent SQL: getValue(fs) refuses a boolean on a none dialect, getCondition(fs) is the opt-in. A cheaper backstop is an assertion at SQL generation that a boolean-typed expression is never emitted outside a condition context on a none dialect, so a miss is an internal error rather than a 4145 from the server.