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
:
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
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
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