Repository: TobikoData/sqlmesh
Pull request #2256 (2024-03-12 20:42:17)

Feat: disable bigquery comment registration table update metadata comparison
An "etag" is BQ versioning metadata that changes when an object is updated/modified. 

BQ's `update_table` method compares the etags of the table object passed to it and the remote table being updated, erroring if the etags don't match. That ensures the structure of the update aligns with the current structure of the table. 

A customer project encountered this error because the remote table's etag changed between when comment registration pulled the table object with `get_table` and updated it with `update_table`. 

It's unclear how this could occur - sqlmesh created the table in the same process just prior to comment registration, so no other process could even know the name of the newly created physical table. The most probable cause is latency between table creation and etag registration, such that the etag creation and registration process had not completed when we called `get_table` and had completed when we called `update_table`. 

Disabling this check by setting the local etag to None is "safe" because we assume that it is impossible for another actor to **intentionally** update the object between the comment registration `get_table` and `update_table` calls. Therefore, a different etag cannot reflect changes to the table structure.

One edge case is if two sqlmesh plans run on the same project and simultaneously act on the environment views. Those view names are consistent over time, so multiple actors know their names.


Patch:
diff --git a/sqlmesh/core/audit/builtin.py b/sqlmesh/core/audit/builtin.py
index e17b29ba..2c51599f 100644
--- a/sqlmesh/core/audit/builtin.py
+++ b/sqlmesh/core/audit/builtin.py
@@ -7,15 +7,19 @@ from sqlmesh.core.audit.definition import ModelAudit
 # not_null(columns=(column_1, column_2))
 not_null_audit = ModelAudit(
     name="not_null",
+    defaults={"condition": exp.null()},
     query="""
 SELECT *
 FROM @this_model
-WHERE @REDUCE(
-  @EACH(
-    @columns,
-    c -> c IS NULL
+WHERE @AND(
+  @REDUCE(
+    @EACH(
+      @columns,
+      c -> c IS NULL
+    ),
+    (l, r) -> l OR r
   ),
-  (l, r) -> l OR r
+  @condition,
 )
     """,
 )
@@ -23,6 +27,7 @@ WHERE @REDUCE(
 # unique_values(columns=(column_1, column_2))
 unique_values_audit = ModelAudit(
     name="unique_values",
+    defaults={"condition": exp.null()},
     query="""
 SELECT *
 FROM (
@@ -32,6 +37,7 @@ FROM (
       c -> row_number() OVER (PARTITION BY c ORDER BY c) AS rank_@c
     )
   FROM @this_model
+  @WHERE(@condition) @condition
 )
 WHERE @REDUCE(
   @EACH(
@@ -46,22 +52,25 @@ WHERE @REDUCE(
 # accepted_values(column=column_name, is_in=(1, 2, 3))
 accepted_values_audit = ModelAudit(
     name="accepted_values",
+    defaults={"condition": exp.null()},
     query="""
 SELECT *
 FROM @this_model
-WHERE @column NOT IN @is_in
+WHERE @AND(@column NOT IN @is_in, @condition)
 """,
 )
 
 # number_of_rows(threshold=100)
 number_of_rows_audit = ModelAudit(
     name="number_of_rows",
+    defaults={"condition": exp.null()},
     query="""
 SELECT COUNT(*)
 FROM (
-   SELECT 1
-   FROM @this_model
-   LIMIT @threshold + 1
+  SELECT 1
+  FROM @this_model
+  @WHERE(@condition) @condition
+  LIMIT @threshold + 1
 )
 HAVING COUNT(*) <= @threshold
     """,
@@ -92,25 +101,36 @@ WHERE @REDUCE(
 # accepted_range(column=age, max_v=50)
 accepted_range_audit = ModelAudit(
     name="accepted_range",
-    defaults={"min_v": exp.null(), "max_v": exp.null(), "inclusive": exp.true()},
+    defaults={
+        "min_v": exp.null(),
+        "max_v": exp.null(),
+        "inclusive": exp.true(),
+        "condition": exp.null(),
+    },
     query="""
 SELECT *
 FROM @this_model
 WHERE
-  False
-  OR @IF(@min_v IS NOT NULL AND @inclusive, @column < @min_v, False)
-  OR @IF(@min_v IS NOT NULL AND NOT @inclusive, @column <= @min_v, False)
-  OR @IF(@max_v IS NOT NULL AND @inclusive, @column > @max_v, False)
-  OR @IF(@max_v IS NOT NULL AND NOT @inclusive, @column >= @max_v, False)
+  @AND(
+    @OR(
+      @IF(@min_v IS NOT NULL AND @inclusive, @column < @min_v, NULL),
+      @IF(@min_v IS NOT NULL AND NOT @inclusive, @column <= @min_v, NULL),
+      @IF(@max_v IS NOT NULL AND @inclusive, @column > @max_v, NULL),
+      @IF(@max_v IS NOT NULL AND NOT @inclusive, @column >= @max_v, NULL),
+    ),
+    @condition,
+  )
     """,
 )
 
 # at_least_one(column=column_name)
 at_least_one_audit = ModelAudit(
     name="at_least_one",
+    defaults={"condition": exp.null()},
     query="""
 SELECT 1
 FROM @this_model
+@WHERE(@condition) @condition
 GROUP BY 1
 HAVING COUNT(@column) = 0
     """,
@@ -119,11 +139,13 @@ HAVING COUNT(@column) = 0
 # not_constant(column=column_name)
 not_constant_audit = ModelAudit(
     name="not_constant",
+    defaults={"condition": exp.null()},
     query="""
 SELECT 1
 FROM (
   SELECT COUNT(DISTINCT @column) AS t_cardinality
   FROM @this_model
+  @WHERE(@condition) @condition
 ) AS r
 WHERE r.t_cardinality <= 1
     """,
@@ -132,16 +154,18 @@ WHERE r.t_cardinality <= 1
 # not_empty_string(column=column_name)
 not_empty_string_audit = ModelAudit(
     name="not_empty_string",
+    defaults={"condition": exp.null()},
     query="""
 SELECT *
 FROM @this_model
-WHERE @column = ''
+WHERE @AND(@column = '', @condition)
     """,
 )
 
 # not_null_proportion(column=column_name, threshold=0.9)
 not_null_proportion_audit = ModelAudit(
     name="not_null_proportion",
+    defaults={"condition": exp.null()},
     query="""
 SELECT *
 FROM (
@@ -150,6 +174,7 @@ FROM (
     count(@column) as cnt_not_null,
     count(*) - count(@column) as cnt_null
   FROM @this_model
+  @WHERE(@condition) @condition
 ) AS s
 WHERE s.cnt_not_null <= s.cnt_tot * @threshold
     """,
@@ -158,10 +183,11 @@ WHERE s.cnt_not_null <= s.cnt_tot * @threshold
 # not_accepted_values(column=column_name, is_in=(1, 2, 3))
 not_accepted_values_audit = ModelAudit(
     name="not_accepted_values",
+    defaults={"condition": exp.null()},
     query="""
 SELECT *
 FROM @this_model
-WHERE @column IN @is_in
+WHERE @AND(@column IN @is_in, @condition)
 """,
 )
 
@@ -169,7 +195,7 @@ WHERE @column IN @is_in
 # TODO: support grouping
 sequential_values_audit = ModelAudit(
     name="sequential_values",
-    defaults={"interval": exp.Literal.number(1)},
+    defaults={"interval": exp.Literal.number(1), "condition": exp.null()},
     query="""
 WITH windowed AS (
   SELECT
@@ -178,6 +204,7 @@ WITH windowed AS (
       ORDER BY @column
     ) AS prv
   FROM @this_model
+  @WHERE(@condition) @condition
 ), validation_errors AS (
     SELECT *
     FROM windowed
@@ -192,9 +219,11 @@ FROM validation_errors
 # unique_combination_of_columns(columns=(column_1, column_2))
 unique_combination_of_columns_audit = ModelAudit(
     name="unique_combination_of_columns",
+    defaults={"condition": exp.null()},
     query="""
 SELECT @EACH(@columns, c -> c)
 FROM @this_model
+@WHERE(@condition) @condition
 GROUP BY @EACH(@columns, c -> c)
 HAVING COUNT(*) > 1
     """,
@@ -204,7 +233,7 @@ HAVING COUNT(*) > 1
 # TODO: make inclusivity configurable
 mutually_exclusive_ranges_audit = ModelAudit(
     name="mutually_exclusive_ranges",
-    defaults={"partition_clause": exp.false()},
+    defaults={"partition_clause": exp.false(), "condition": exp.null()},
     query="""
 WITH window_functions AS (
   SELECT
@@ -219,6 +248,7 @@ WITH window_functions AS (
       ORDER BY @lower_bound_column desc, @upper_bound_column desc
     ) = 1 AS is_last_record
   FROM @this_model
+  @WHERE(@condition) @condition
 ), calc AS (
   SELECT
     *,
@@ -249,20 +279,26 @@ FROM validation_errors
 # valid_uuid(column=column_name)
 valid_uuid_audit = ModelAudit(
     name="valid_uuid",
+    defaults={"condition": exp.null()},
     query="""
 SELECT *
 FROM @this_model
-WHERE NOT REGEXP_LIKE(LOWER(@column), '^[0-9a-f]{8}-[0-9a-f]{4}-[1-5][0-9a-f]{3}-[89ab][0-9a-f]{3}-[0-9a-f]{12}$')
+WHERE @AND(
+  NOT REGEXP_LIKE(LOWER(@column), '^[0-9a-f]{8}-[0-9a-f]{4}-[1-5][0-9a-f]{3}-[89ab][0-9a-f]{3}-[0-9a-f]{12}$'),
+  @condition,
+)
+
     """,
 )
 
 # valid_url(column=column_name)
 valid_url_audit = ModelAudit(
     name="valid_url",
+    defaults={"condition": exp.null()},
     query=r"""
 SELECT *
 FROM @this_model
-WHERE NOT REGEXP_LIKE(@column, '^(https?|ftp)://[^\s/$.?#].[^\s]*$')
+WHERE @AND(NOT REGEXP_LIKE(@column, '^(https?|ftp)://[^\s/$.?#].[^\s]*$'), @condition)
     """,
 )
 
@@ -279,25 +315,30 @@ WHERE NOT @column IN ('GET', 'POST', 'PUT', 'DELETE', 'PATCH', 'HEAD', 'OPTIONS'
 # valid_email(column=column_name)
 valid_email_audit = ModelAudit(
     name="valid_email",
+    defaults={"condition": exp.null()},
     query=r"""
 SELECT *
 FROM @this_model
-WHERE NOT REGEXP_LIKE(@column, '^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+$')
+WHERE @AND(NOT REGEXP_LIKE(@column, '^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+$'), @condition)
     """,
 )
 
 # match_regex_pattern_list(column=column_name, patterns=('^pattern_1', 'pattern_2$'))
 match_regex_pattern_list_audit = ModelAudit(
     name="match_regex_pattern_list",
+    defaults={"condition": exp.null()},
     query="""
 SELECT *
 FROM @this_model
-WHERE @REDUCE(
-  @EACH(
-    @patterns,
-    c -> NOT REGEXP_LIKE(@column, c)
+WHERE @AND(
+  @REDUCE(
+    @EACH(
+      @patterns,
+      c -> NOT REGEXP_LIKE(@column, c)
+    ),
+    (l, r) -> l OR r
   ),
-  (l, r) -> l OR r
+  @condition,
 )
     """,
 )
@@ -305,15 +346,19 @@ WHERE @REDUCE(
 # not_match_regex_pattern_list(column=column_name, patterns=('^pattern_1', 'pattern_2$'))
 not_match_regex_pattern_list_audit = ModelAudit(
     name="not_match_regex_pattern_list",
+    defaults={"condition": exp.null()},
     query="""
 SELECT *
 FROM @this_model
-WHERE @REDUCE(
-  @EACH(
-    @patterns,
-    c -> REGEXP_LIKE(@column, c)
+WHERE @AND(
+  @REDUCE(
+    @EACH(
+      @patterns,
+      c -> REGEXP_LIKE(@column, c)
+    ),
+    (l, r) -> l OR r
   ),
-  (l, r) -> l OR r
+  @condition,
 )
     """,
 )
@@ -321,15 +366,18 @@ WHERE @REDUCE(
 # match_like_pattern_list(column=column_name, patterns=('%pattern_1%', 'pattern_2%'))
 match_like_pattern_list = ModelAudit(
     name="match_like_pattern_list",
+    defaults={"condition": exp.true()},
     query="""
 SELECT *
 FROM @this_model
-WHERE @REDUCE(
-  @EACH(
-    @patterns,
-    c -> NOT @column LIKE c
-  ),
-  (l, r) -> l OR r
+WHERE @condition AND (
+  @REDUCE(
+    @EACH(
+      @patterns,
+      c -> NOT @column LIKE c
+    ),
+    (l, r) -> l OR r
+  )
 )
     """,
 )
@@ -337,15 +385,19 @@ WHERE @REDUCE(
 # not_match_like_pattern_list(column=column_name, patterns=('%pattern_1%', 'pattern_2%'))
 not_match_like_pattern_list_audit = ModelAudit(
     name="not_match_like_pattern_list",
+    defaults={"condition": exp.null()},
     query="""
 SELECT *
 FROM @this_model
-WHERE @REDUCE(
-  @EACH(
-    @patterns,
-    c -> @column LIKE c
+WHERE @AND(
+  @REDUCE(
+    @EACH(
+      @patterns,
+      c -> @column LIKE c
+    ),
+    (l, r) -> l OR r
   ),
-  (l, r) -> l OR r
+  @condition,
 )
     """,
 )
@@ -353,12 +405,14 @@ WHERE @REDUCE(
 # z_score_audit(column=column_name, threshold=3)
 z_score_audit = ModelAudit(
     name="z_score",
+    defaults={"condition": exp.null()},
     query="""
 WITH stats AS (
   SELECT
     AVG(@column) AS mean_@column,
     STDDEV(@column) AS stddev_@column
   FROM @this_model
+  @WHERE(@condition) @condition
 )
 SELECT
   @column,
@@ -371,70 +425,95 @@ WHERE ABS((@column - mean_@column) / NULLIF(stddev_@column, 0)) > @threshold
 # string_length_between_audit(column=column_name, max_v=22)
 string_length_between_audit = ModelAudit(
     name="string_length_between",
-    defaults={"min_v": exp.null(), "max_v": exp.null(), "inclusive": exp.true()},
+    defaults={
+        "min_v": exp.null(),
+        "max_v": exp.null(),
+        "inclusive": exp.true(),
+        "condition": exp.null(),
+    },
     query="""
 SELECT *
 FROM @this_model
 WHERE
-  False
-  OR @IF(@min_v IS NOT NULL AND @inclusive, LENGTH(@column) < @min_v, False)
-  OR @IF(@min_v IS NOT NULL AND NOT @inclusive, LENGTH(@column) <= @min_v, False)
-  OR @IF(@max_v IS NOT NULL AND @inclusive, LENGTH(@column) > @max_v, False)
-  OR @IF(@max_v IS NOT NULL AND NOT @inclusive, LENGTH(@column) >= @max_v, False)
+  @AND(
+    @OR(
+      @IF(@min_v IS NOT NULL AND @inclusive, LENGTH(@column) < @min_v, NULL),
+      @IF(@min_v IS NOT NULL AND NOT @inclusive, LENGTH(@column) <= @min_v, NULL),
+      @IF(@max_v IS NOT NULL AND @inclusive, LENGTH(@column) > @max_v, NULL),
+      @IF(@max_v IS NOT NULL AND NOT @inclusive, LENGTH(@column) >= @max_v, NULL),
+    ),
+    @condition,
+  )
     """,
 )
 
 # string_length_equal_audit(column=column_name, v=22)
 string_length_equal_audit = ModelAudit(
     name="string_length_equal",
+    defaults={"condition": exp.null()},
     query="""
 SELECT *
 FROM @this_model
-WHERE LENGTH(@column) != @v
+WHERE @AND(LENGTH(@column) != @v, @condition)
     """,
 )
 
 # stddev_in_range(column=age, min_v=2.5, max_v=25)
 stddev_in_range_audit = ModelAudit(
     name="stddev_in_range",
-    defaults={"min_v": exp.null(), "max_v": exp.null(), "inclusive": exp.true()},
+    defaults={
+        "min_v": exp.null(),
+        "max_v": exp.null(),
+        "inclusive": exp.true(),
+        "condition": exp.null(),
+    },
     query="""
 SELECT *
 FROM (
   SELECT STDDEV(@column) AS stddev_@column
   FROM @this_model
+  @WHERE(@condition) @condition
 )
 WHERE
-  False
-  OR @IF(@min_v IS NOT NULL AND @inclusive, stddev_@column < @min_v, False)
-  OR @IF(@min_v IS NOT NULL AND NOT @inclusive, stddev_@column <= @min_v, False)
-  OR @IF(@max_v IS NOT NULL AND @inclusive, stddev_@column > @max_v, False)
-  OR @IF(@max_v IS NOT NULL AND NOT @inclusive, stddev_@column >= @max_v, False)
+  @OR(
+    @IF(@min_v IS NOT NULL AND @inclusive, stddev_@column < @min_v, NULL),
+    @IF(@min_v IS NOT NULL AND NOT @inclusive, stddev_@column <= @min_v, NULL),
+    @IF(@max_v IS NOT NULL AND @inclusive, stddev_@column > @max_v, NULL),
+    @IF(@max_v IS NOT NULL AND NOT @inclusive, stddev_@column >= @max_v, NULL),
+  )
     """,
 )
 
 # mean_in_range(column=age, min_v=2.5, max_v=25)
 mean_in_range_audit = ModelAudit(
     name="mean_in_range",
-    defaults={"min_v": exp.null(), "max_v": exp.null(), "inclusive": exp.true()},
+    defaults={
+        "min_v": exp.null(),
+        "max_v": exp.null(),
+        "inclusive": exp.true(),
+        "condition": exp.null(),
+    },
     query="""
 SELECT *
 FROM (
   SELECT AVG(@column) AS mean_@column
   FROM @this_model
+  @WHERE(@condition) @condition
 )
 WHERE
-  False
-  OR @IF(@min_v IS NOT NULL AND @inclusive, mean_@column < @min_v, False)
-  OR @IF(@min_v IS NOT NULL AND NOT @inclusive, mean_@column <= @min_v, False)
-  OR @IF(@max_v IS NOT NULL AND @inclusive, mean_@column > @max_v, False)
-  OR @IF(@max_v IS NOT NULL AND NOT @inclusive, mean_@column >= @max_v, False)
+  @OR(
+    @IF(@min_v IS NOT NULL AND @inclusive, mean_@column < @min_v, NULL),
+    @IF(@min_v IS NOT NULL AND NOT @inclusive, mean_@column <= @min_v, NULL),
+    @IF(@max_v IS NOT NULL AND @inclusive, mean_@column > @max_v, NULL),
+    @IF(@max_v IS NOT NULL AND NOT @inclusive, mean_@column >= @max_v, NULL),
+  )
     """,
 )
 
 # kl_divergence(column=age, target_column=normalized_age, threshold=0.1)
 kl_divergence_audit = ModelAudit(
     name="kl_divergence",
+    defaults={"condition": exp.null()},
     query="""
 WITH
   table_a AS (
@@ -442,6 +521,7 @@ WITH
       @source_column,
       COUNT(*) AS num_rows
     FROM @this_model
+    @WHERE(@condition) @condition
     GROUP BY @source_column
   ),
   table_b AS (
@@ -449,6 +529,7 @@ WITH
       @target_column,
       COUNT(*) AS num_rows
     FROM @this_model
+    @WHERE(@condition) @condition
     GROUP BY @target_column
   ),
   table_a_with_p AS (
@@ -531,7 +612,7 @@ chi_square_audit = ModelAudit(
         "@def(r, (SELECT COUNT(DISTINCT x_b) FROM contingency_table))",
         "@def(E, (tot_a * tot_b / g_t))",
     ],
-    defaults={"dependent": exp.true()},
+    defaults={"dependent": exp.true(), "condition": exp.null()},
     query="""
 WITH
   samples AS (
@@ -539,7 +620,7 @@ WITH
       @column_a AS x_a,
       @column_b AS x_b,
     FROM @this_model
-    WHERE @column_a IS NOT NULL AND @column_b IS NOT NULL
+    WHERE @AND(@column_a IS NOT NULL AND @column_b IS NOT NULL, @condition)
   ),
   contingency_table AS (
     SELECT
diff --git a/sqlmesh/core/engine_adapter/bigquery.py b/sqlmesh/core/engine_adapter/bigquery.py
index c83ba3e0..9c3a8b8c 100644
--- a/sqlmesh/core/engine_adapter/bigquery.py
+++ b/sqlmesh/core/engine_adapter/bigquery.py
@@ -448,6 +448,11 @@ class BigQueryEngineAdapter(InsertOverwriteWithMergeMixin):
                     comment
                 )
 
+        # An "etag" is BQ versioning metadata that changes when an object is updated/modified. `update_table`
+        # compares the etags of the table object passed to it and the remote table, erroring if the etags
+        # don't match. We set the local etag to None to avoid this check.
+        table_def["etag"] = None
+
         # convert dict back to a Table object
         table = table.from_api_repr(table_def)
 
diff --git a/sqlmesh/core/macros.py b/sqlmesh/core/macros.py
index 255a9b12..ddc3a7f2 100644
--- a/sqlmesh/core/macros.py
+++ b/sqlmesh/core/macros.py
@@ -1053,6 +1053,28 @@ def pivot(
     return aggregates
 
 
+@macro("AND")
+def and_(evaluator: MacroEvaluator, *expressions: t.Optional[exp.Expression]) -> exp.Condition:
+    """Returns an AND statement filtering out any NULL expressions."""
+    conditions = [e for e in expressions if not isinstance(e, exp.Null)]
+
+    if not conditions:
+        return exp.true()
+
+    return exp.and_(*conditions, dialect=evaluator.dialect)
+
+
+@macro("OR")
+def or_(evaluator: MacroEvaluator, *expressions: t.Optional[exp.Expression]) -> exp.Condition:
+    """Returns an OR statement filtering out any NULL expressions."""
+    conditions = [e for e in expressions if not isinstance(e, exp.Null)]
+
+    if not conditions:
+        return exp.true()
+
+    return exp.or_(*conditions, dialect=evaluator.dialect)
+
+
 def normalize_macro_name(name: str) -> str:
     """Prefix macro name with @ and upcase"""
     return f"@{name.upper()}"


Test patch:
diff --git a/tests/core/test_audit.py b/tests/core/test_audit.py
index 3ea50daf..25ab79fd 100644
--- a/tests/core/test_audit.py
+++ b/tests/core/test_audit.py
@@ -496,21 +496,21 @@ def test_accepted_range_audit(model: Model):
     )
     assert (
         rendered_query.sql()
-        == 'SELECT * FROM (SELECT * FROM "db"."test_model" AS "test_model" WHERE "ds" BETWEEN \'1970-01-01\' AND \'1970-01-01\') AS "_q_0" WHERE FALSE OR "a" < 0 OR FALSE OR FALSE OR FALSE'
+        == 'SELECT * FROM (SELECT * FROM "db"."test_model" AS "test_model" WHERE "ds" BETWEEN \'1970-01-01\' AND \'1970-01-01\') AS "_q_0" WHERE "a" < 0'
     )
     rendered_query = builtin.accepted_range_audit.render_query(
         model, column=exp.to_column("a"), max_v=100, inclusive=exp.false()
     )
     assert (
         rendered_query.sql()
-        == 'SELECT * FROM (SELECT * FROM "db"."test_model" AS "test_model" WHERE "ds" BETWEEN \'1970-01-01\' AND \'1970-01-01\') AS "_q_0" WHERE FALSE OR FALSE OR FALSE OR FALSE OR "a" >= 100'
+        == 'SELECT * FROM (SELECT * FROM "db"."test_model" AS "test_model" WHERE "ds" BETWEEN \'1970-01-01\' AND \'1970-01-01\') AS "_q_0" WHERE "a" >= 100'
     )
     rendered_query = builtin.accepted_range_audit.render_query(
         model, column=exp.to_column("a"), min_v=100, max_v=100
     )
     assert (
         rendered_query.sql()
-        == 'SELECT * FROM (SELECT * FROM "db"."test_model" AS "test_model" WHERE "ds" BETWEEN \'1970-01-01\' AND \'1970-01-01\') AS "_q_0" WHERE FALSE OR "a" < 100 OR FALSE OR "a" > 100 OR FALSE'
+        == 'SELECT * FROM (SELECT * FROM "db"."test_model" AS "test_model" WHERE "ds" BETWEEN \'1970-01-01\' AND \'1970-01-01\') AS "_q_0" WHERE "a" < 100 OR "a" > 100'
     )
 
 
@@ -643,10 +643,10 @@ def test_text_diff():
     modified_audit.name = "my_audit_2"
 
     assert (
-        audit.text_diff(modified_audit)
-        == """--- 
+        "\n".join(l.rstrip() for l in audit.text_diff(modified_audit).split("\n"))
+        == """---
 
-+++ 
++++
 
 @@ -1,5 +1,5 @@
 
@@ -663,3 +663,26 @@ def test_non_blocking_builtin():
     assert BUILT_IN_AUDITS["not_null_non_blocking"].blocking is False
     assert BUILT_IN_AUDITS["not_null_non_blocking"].name == "not_null_non_blocking"
     assert BUILT_IN_AUDITS["not_null"].query == BUILT_IN_AUDITS["not_null_non_blocking"].query
+
+
+def test_string_length_between_audit(model: Model):
+    rendered_query = builtin.string_length_between_audit.render_query(
+        model,
+        column=exp.column("x"),
+        min_v=1,
+        max_v=5,
+    )
+    assert (
+        rendered_query.sql()
+        == """SELECT * FROM (SELECT * FROM "db"."test_model" AS "test_model" WHERE "ds" BETWEEN '1970-01-01' AND '1970-01-01') AS "_q_0" WHERE LENGTH("x") < 1 OR LENGTH("x") > 5"""
+    )
+
+
+def test_not_constant_audit(model: Model):
+    rendered_query = builtin.not_constant_audit.render_query(
+        model, column=exp.column("x"), condition=exp.condition("x > 1")
+    )
+    assert (
+        rendered_query.sql()
+        == """SELECT 1 AS "1" FROM (SELECT COUNT(DISTINCT "x") AS "t_cardinality" FROM (SELECT * FROM "db"."test_model" AS "test_model" WHERE "ds" BETWEEN '1970-01-01' AND '1970-01-01') AS "_q_0" WHERE "x" > 1) AS "r" WHERE "r"."t_cardinality" <= 1"""
+    )
diff --git a/tests/core/test_macros.py b/tests/core/test_macros.py
index 2226abfa..ee6c2d9e 100644
--- a/tests/core/test_macros.py
+++ b/tests/core/test_macros.py
@@ -416,6 +416,31 @@ def test_ast_correctness(macro_evaluator):
             "SELECT * FROM (SELECT a, b, c, '2024-01-01' AS stamp)",
             {},
         ),
+        (
+            """@OR(NULL, TRUE)""",
+            "TRUE",
+            {},
+        ),
+        (
+            """@OR(NULL, NULL)""",
+            "TRUE",
+            {},
+        ),
+        (
+            """@OR(NULL)""",
+            "TRUE",
+            {},
+        ),
+        (
+            """@OR()""",
+            "TRUE",
+            {},
+        ),
+        (
+            """@AND(TRUE, NULL)""",
+            "TRUE",
+            {},
+        ),
     ],
 )
 def test_macro_functions(macro_evaluator: MacroEvaluator, assert_exp_eq, sql, expected, args):