Is it normal for a connection piece at outdoor HVAC unit to sweat heavily

I'd suspect it's a secondary drain. It's possible that the main drain was simply overwhelmed, and the condensate level backed up to the point of the secondary drain. If it was really humid, a large volume of water could have been removed from the air. If the main drain is slow, it could have simply not been able to keep up.

Without inspecting the system, it's impossible to say for sure what's going on. If you can follow the pipe back to the air handler, you could confirm that it's the secondary drain. You may also be able to determine if the main line is clogged, and possibly fix it.

If you go poking around, you could post some photos here, and somebody might be able to offer more help.

From the looks of the pipe, it doesn't look like it would cause any water damage. But I can't see if it's dripping on anything important.

Sounds like you are concerned about the placement of the 'return air vents' to these systems (one upstairs and one downstairs). Normally, if you have only a single Air Conditioner, the upstairs gets hotter than the downstairs (like my house! in Texas), and careful placement of the 'return vents' can help reduce this issue (put the upstairs return vent high on the wall, so it pulls in the warmest air).

Based on your description, I would not be concerned. Since you have a dedicated unit for upstairs, really you are golden. The fact that the downstairs return is 'near the stairs' means it is probably in a central location (which is perfect). You said the upstairs return would go 'in the ceiling' which is exactly where it should go since that will pull in the warmest air, and your air handler is in the attic anyway.