Actually, I have written a small scalar function for what I had in my mind, but I think it does not work as I expected. When using the invariant at the top level, it gives an error message, while if we use the invariant function as part of the FROM statement, the error is ignored at the top level. Although in tests, both queries fail as expected.

D SELECT denom, INVARIANT(denom != 0, 'denominator must not be zero')
> FROM ( 
> SELECT 1 AS denom
> UNION ALL
> SELECT 0 AS denom
> UNION ALL
> SELECT 1 AS denom
> );
Error: Invalid Error: denominator must not be zero
D SELECT 42/denom FROM (
> SELECT denom, INVARIANT(denom != 0, 'denominator must not be zero')
> FROM ( 
> SELECT 1 AS denom
> UNION ALL
> SELECT 0 AS denom
> UNION ALL
> SELECT 1 AS denom
> ));
┌──────────────┐
│ (42 / denom) │
│    int32     │
├──────────────┤
│           42 │
│              │
│           42 │
└──────────────┘
D 

Actually looking at this again, the reason this happens is because of projection pushdown.
The results of INVARIANT are not used, so it's stripped from the optimized plan, which we can see when we add EXPLAIN to your query:

physical_plan   
┌───────────────────────────┐                                                          
│         PROJECTION        │                                                          
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │                                                          
│        (42 / denom)       │                                                          
└─────────────┬─────────────┘                                                                                       
┌─────────────┴─────────────┐                                                          
│           UNION           ├───────────────────────────────────────────┐              
└─────────────┬─────────────┘                                           │                                           
┌─────────────┴─────────────┐                             ┌─────────────┴─────────────┐
│           UNION           │                             │         PROJECTION        │
│                           ├──────────────┐              │   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│                           │              │              │           denom           │
└─────────────┬─────────────┘              │              └─────────────┬─────────────┘                             
┌─────────────┴─────────────┐┌─────────────┴─────────────┐┌─────────────┴─────────────┐
│         PROJECTION        ││         PROJECTION        ││         DUMMY_SCAN        │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   ││   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   ││                           │
│           denom           ││           denom           ││                           │
└─────────────┬─────────────┘└─────────────┬─────────────┘└───────────────────────────┘                             
┌─────────────┴─────────────┐┌─────────────┴─────────────┐                             
│         DUMMY_SCAN        ││         DUMMY_SCAN        │                             
└───────────────────────────┘└───────────────────────────┘ 

When we disable the RemoveUnusedColumns optimizer pass, we get the expected behavior.
(optimizer.cpp:122)

	// removes unused columns
	RunOptimizer(OptimizerType::UNUSED_COLUMNS, [&]() {
		RemoveUnusedColumns unused(binder, context, true);
		unused.VisitOperator(*plan);
	});

You likely need to make certain optimizer rules (starting with RemoveUnusedColumns) aware of the INVARIANT function and don't let them act on columns created by it

Thanks @Tishj I really appreciate your help in here and I think this explanation totally makes sense to me, only one question I have, why does the test case return an error for the same query while the query through the console client does not?

I would assume the execution graph for the test case perhaps is not optimized, could it be the reason?

For the solution suggestions,

I see three options,

1. Trying to make the optimizer aware of the invariant function so that it is not stripped as you suggested.
2. Evolve it similar to the ERROR function @danfrankj points out. BigQuery also does not have any guarantee for it to be evaluated, looking at the examples, I think, it is likely for similar reasons.
3. Evolve it to return some expression such that user might be using for the further.

// name can be decided later, this returns the 3rd parameter if the first condition is not correct or throws an error.
IF_INVARIANT(denom != 0, 'error message', denom) as denom

I think, first option is a bit less flexible for two reasons, first, besides the unused, other rules can also discard the INVARIANT function, also it is possible the changes we made in the optimizer code can complicate the optimizer code without a strong use case.

Second option, although it is not ideal, having a similar function in BigQuery is a good indicator what we do is not uncommon, we could also evolve to ERROR function as @danfrankj points.

In the third option, users can use the output of the function for the parent query so that the function is not stripped, but there is still no guarantee it will be stripped for different reasons.

What I suggest is to go with the ERROR function in option 2, and document (not sure if these functions have documentations) the explain statement to check by the user if the ERROR will be discarded or not.

Please let me know what you think.

I think 3 sounds like a good idea, especially with the addition of the new . syntax for chaining operations
function(input.ASSERT(x -> x != NULL)) looks like nice syntax
(it would essentially be shorthand for: function(ASSERT(input, x -> x != NULL)), see #6725)

I don't spot any glaring mistakes, I'm not that familiar with our UnaryExecutor and BinaryExecutor inner workings though.

And this could also be due to default null handling, now that I think about it. 🤔

Do scalar functions have a null handling option? This should likely be SPECIAL_HANDLING if so

Edit: they do, and I think it's relevant here