Note that the sql contains “ORDER BY similarity DESC” in the end, but the output isn’t really ordered by similarity desc: 0.78 - 0.71 - 0.75. I got the same issue running the query myself. I guess this may be related to how snowflake runs CTE.
@pang.luo @hawraa.salami
I would like to see what the size of combined_all for you, is this 10 or 15 ( 5 audio + 10 slides) ?
2d point in the final ORDER , you may add a LIMIT to force the correct ordering of the slides ( ORDER BY similarity DESC
LIMIT 10 appears in a subquery and is not necessarily preserved in combined_all)
after the fix introduced to the session creation earlier today
I compared the previous code and the current code in the “Cross-Modal Search” section and there is no difference. If there were a “fix” maybe they changed something else.
I’m still puzzled why it produces 10 results and not 15.
I guess that’s probably Snowflake’s eccentricity. If you change “LIMIT 10” into “LIMIT 4” you’ll only see 4 rows in the output, which seems different from standard SQL behaviour. It would be great for the lecturer to clarify if this is a bug of snowflake.
I also found that the ordering in the output is not deterministic. For the first couple of runs I got the same input as your last screenshot, which is nice. But then I ran it again and got the result as below. You can see that it is not ordered by similarity. The 8th figure is 0.78.
I agree , it would be helpgul if the tutor could provide some explaination on the logic of his query , including the LIMIT 5 and LIMIT 10.
I reran and got a different order ,
Then, by adding a LIMIT 10 in the last ORDER BY , and rerunning several times , I’m getting repeatable results in the right order.