snowflake_broken_using.md

We have an issue where Snowflake returns a different value for a column depending on the order of joins and usage of using vs on:

join vendors last, with using

this incorrectly returns product_types.created_at_utc in the place of products.created_at_utc.

query id: ef66f8a8-70b2-44eb-a612-f41afb8b5aa2

snowsql>select products__intermediate.product_id, product_types.created_at_utc as product_type, products__intermediate.created_at_utc as product,
        vendors.created_at_utc as vendor from temp.products__intermediate left join staging.product_types on products__intermediate.product_type
        = product_types.product_type_id left join staging.vendors using (vendor_id) where product_id = '58e53d1a08275d000a51900c';
***************************[ 1 ]***************************                     
PRODUCT_ID   | 58e53d1a08275d000a51900c
PRODUCT_TYPE | 2016-04-22 21:16:14.140
PRODUCT      | 2016-04-22 21:16:14.140
VENDOR       | 2015-04-12 18:56:37.897

1 Row(s) produced. Time Elapsed: 1.202s

join vendors first, with using

this returns the correct values for all fields, just by moving the join around.

query id: bb2f1da7-9a74-45ef-b55a-528dfb3287c3

snowsql> select products__intermediate.product_id, product_types.created_at_utc as product_type, products__intermediate.created_at_utc as product,
         vendors.created_at_utc as vendor from temp.products__intermediate left join staging.vendors using (vendor_id) left join staging.product_
         types on products__intermediate.product_type = product_types.product_type_id where product_id = '58e53d1a08275d000a51900c';
***************************[ 1 ]***************************                     
PRODUCT_ID   | 58e53d1a08275d000a51900c
PRODUCT_TYPE | 2016-04-22 21:16:14.140
PRODUCT      | 2017-04-05 18:53:14.029
VENDOR       | 2015-04-12 18:56:37.897

1 Row(s) produced. Time Elapsed: 1.272s

join vendors last, with on

this returns the correct values for all fields, just by changing the using to on.

query id: d0724f31-925c-450a-8b70-c7bd2e524738

snowsql>select products__intermediate.product_id, product_types.created_at_utc as product_type, products__intermediate.created_at_utc as product,
        vendors.created_at_utc as vendor from temp.products__intermediate left join staging.product_types on products__intermediate.product_type
        = product_types.product_type_id left join staging.vendors on products__intermediate.vendor_id = vendors.vendor_id where product_id = '58
        e53d1a08275d000a51900c';
***************************[ 1 ]***************************                     
PRODUCT_ID   | 58e53d1a08275d000a51900c
PRODUCT_TYPE | 2016-04-22 21:16:14.140
PRODUCT      | 2017-04-05 18:53:14.029
VENDOR       | 2015-04-12 18:56:37.897

1 Row(s) produced. Time Elapsed: 2.740s