quick_are_queries_identical macros is not working as expected when there is a empty query result/table is involved

[mahilaeshwar]

Describe the bug

quick_are_queries_identical macros is not working as expected when there is a empty query result/table is involved.

Steps to reproduce

Scenario 1:
when both the query_a and query_b returns no result(empty output) then the quick_are_queries_identical returns false but ideally it should return true since both the query returns empty result.

Expected results True

Actual results False

{% set old_query %}
  select
    *
from `bigquery-public-data.faa.us_airports` LIMIT 0
{% endset %}
{% set new_query %}
  select
    *
  from `bigquery-public-data.faa.us_airports` LIMIT 0
{% endset %}
{{ 
  audit_helper.quick_are_queries_identical(
    old_query, 
    new_query, 
    columns=['object_id','global_id','faa_identifier','name','latitude','longitude','airport_geom','elevation','icao_id','airport_type','service_city','state_abbreviation','country','oper_status','airport_use','iap_exists','dod_hiflip','far_91','far_93','mil_code','airspace_analysis','us_high','us_low','ak_high','ak_low','us_area','pacific']
  )
}}

Screenshots and log output

Scenario 2:
when one of the query_a and query_b returns no result(empty output) then the quick_are_queries_identical returns true but ideally it should return false since one of the query returns value and other return empty result.

Expected results False

Actual results True

{% set old_query %}
  select
    *
from `bigquery-public-data.faa.us_airports`
{% endset %}
{% set new_query %}
  select
    *
  from `bigquery-public-data.faa.us_airports` LIMIT 0
{% endset %}
{{ 
  audit_helper.quick_are_queries_identical(
    old_query, 
    new_query, 
    columns=['object_id','global_id','faa_identifier','name','latitude','longitude','airport_geom','elevation','icao_id','airport_type','service_city','state_abbreviation','country','oper_status','airport_use','iap_exists','dod_hiflip','far_91','far_93','mil_code','airspace_analysis','us_high','us_low','ak_high','ak_low','us_area','pacific']
  )
}}

Screenshots and log output

System information

The contents of your packages.yml file:

packages:
  - package: dbt-labs/dbt_external_tables
    version: [">=0.8.0", "<0.9.0"]
  
  - package: dbt-labs/dbt_utils
    version: [">=0.9.0", "<1.0.0"]

  - package: dbt-labs/codegen
    version: [">=0.8.0", "<=0.9.0"]

  - package: dbt-labs/audit_helper
    version: [">=0.6.0", "<0.12.1"]

Which database are you using dbt with?

• postgres
• redshift
• bigquery
• snowflake
• other (specify: ____________)

The output of dbt --version:

latest

Additional context

I believe the reason this is happening is because bit_xor of no result will be NULL and when we do count(distinct hash_result) the NULL value won't be taken into account.

    select count(distinct hash_result) = 1 as are_tables_identical
    from (
        select bit_xor(farm_fingerprint(to_json_string(query_a))) as hash_result
        from query_a

        union all
        
        select bit_xor(farm_fingerprint(to_json_string(query_b))) as hash_result
        from query_b
    ) as hashes

to handle this we can add IFNULL() to the hash_result

Are you interested in contributing the fix?

Yes!