Warning: Long post.
Here's what happens with canoes and similar boats that are symmetrical or nearly so, and I think this is quite instructive regarding the principle that's at work here. I also see the same thing happen with any basic, small rowboat/motorboat when the outboard motor is raised, if there is one. These boats will invariably orient themselves at a right angle to the wind and drift directly sideways (with any slight variations from this orientation being the result of a tendency toward one of the modified situations explained below). This is a result of there being a point of balance between opposing forces as the boat drifts sideways. These are the opposing forces of both wind and water, which also are balanced according to the characteristics of the parts of the boat which are forward and rearward the center point. Making a boat drift with one end facing the wind and the other end pointing downwind (with no attachment to something other than the water that supports it, such as with an anchor line) is absolutely impossible without making drastic changes to the boat's underwater profile, the above-water profile, and usually both, thus creating a very unsymmetrical boat, as explained below.
In the case of a canoe or similar craft which is basically symmetrical, if the load is shifted toward one end, that end will sink more deeply and experience more drag as the craft moves sideways through the water as a result of the wind, and the opposite end will rise and have less drag. At the same time, the raising of the light end and lowering of the heavy end will cause the wind to "get a better grip" on the light end of the craft. The result of this combination of factors is that the boat pivots to some extent so that the lighter end faces more downwind than before, but the boat will still be drifting essentially sideways through the water, still moving essentially straight downwind (actually, in this case it will be drifting slightly diagonal to a straight-downwind direction, and this is explained below for the motorboat example). Only by unbalancing the trim so drastically that the stern is overloaded and the bow is high up out of the water will the boat turn to face almost straight downwind as it drifts in that direction. Clearly, it takes a large difference in drag between one end of the boat and the other to make it pivot to the point of being well lined-up with the wind as it drifts, and this applies both to the drag of the in-water portion of the boat, AND the drag of the wind on the out-of-water portion. However, experience shows that the profile and drag distribution of the underwater portion of the boat is more important.
In the case of the "basic little motorboat" mentioned above, tilting the motor down to the operating position will create more drag at the stern of the boat than at the front as the boat is drifting sideways, and the boat will pivot slightly toward a downwind heading as it drifts, and even to the point of having a mostly-downwind heading of there's enough drag at the stern via the motor in proportion to that of the rest of the hull. However, I've never seen one of these boats point straight downwind as a result of putting the motor down. It would take a much larger rudder-like object at the stern than the drive unit of the outboard motor to cause that to happen (a bit off topic but illustrating the principle at work here, dragging a substantial sea anchor with the rope attached to one extreme end of the boat WILL cause the drifting boat to orient itself in direct alignment with the wind). This is a trait that most old-school fishermen (those who fished back in the days before everyone had electric trolling motors on their boats) know how to use to their advantage when drifting, because the boat will drift at a slight angle to the direction that the wind is blowing when the motor is in the down position and the boat is oriented slightly diagonal to the wind.
Since a small motorboat drifting in this diagonal orientation can be made to "balance" its orientation with the bow pointed either slightly to the left or slightly to the right of the straight-downwind orientation, a savvy fisherman can make his boat drift slightly diagonally to to the wind in either direction as well, by manipulating his boat into one diagonal orientation or the other before beginning his intended drift.
I've been away from the study of physics for too many decades to use proper terminology to describe the combination of forces involved in these situations, but likewise, decades of very frequent experience in small boats and windy condition has taught me how this works and the mechanism behind it. As noted above, it requires a very asymmetrical hull design to result in a boat that drifts with the wind while actually pointed in the downwind direction. However, there's another aspect that could be at work here. The balance point between front and rear which is referenced above, moves progressively forward with increased speed. In a sailboat, that balance point must match the location where the force of the wind is transmitted to the hull, and that is likely near the center of the boat. A sailboat that is designed for high-speed operation will have the balance point near the center of the boat when moving at high speed, but when stationary, that point would be significantly astern of center. Thus, a boat having that kind of design might have a balance point that is far enough toward the rear that the boat naturally assumes a downwind heading when drifting. For more on this pivot point and its forward shift with increasing speed, see below. You can see that the pivot point is not a simple thing, and not the same under all conditions.
https://southernjointbranch.co.uk/wp-content/uploads/2017/07/Paradigm-Shift-in-SH20150526-for-Web-via-AB.pdf
https://adventurecurated.com/tips/canoe-skills-worth-mastering/
While not directly referenced in these articles, the pivot point that is described is also the balance point when drifting with the wind. Thus, for a centrally-located pivot point, the result is just as described at the beginning of this post, resulting in a boat orienting itself sideways to the wind and moving sideways through the water.
