This article (Hughes 1983, "On Seeing Stars (especially up chimneys") from the Quarterly Journal of the Royal Astronomical Society seems like a pretty good account of the "phenomenon", including both classical and literary claims (e.g., in stories by Dickens and Kipling) and actual experiments. It appears that the Aristotle reference ("people in pits and wells sometimes see stars", from Generation of Animals) is not an eyewitness report by Aristotle himself -- and, given that Aristotle claimed men had more teeth than women, one should always be a bit skeptical about his claims; there is also the possibility that these were sightings of planets, which can sometimes be seen in the daytime. The Herschel reference is to an "optician" Herschel knew who claimed to have seen a star this way once or twice, not something Herschel himself verified.
In Section 2 of the article there are discussions of actual serious (and unsuccessful) attempts to test this, including numerous attempts by Alexander von Humboldt and well-described attempts by astronomers like J. Allen Hynek, who tried (with "several members of my astronomy class") to observe Vega from the bottom of an abandoned smokestack, and some measurements taken at the botton of a tall chimney which verified that the brightness of the sky was unchanged from that seen outside.
There are also some discussions of the sensitivity of the human eye. The conclusion I get from this is that it is barely possible that someone with good eyes and an extremely restricted field of view (much smaller than "from the bottom of a well" or a "cardboard tube") might be able to make out Sirius, but probably nothing fainter.
Edited to add: This article (Können et al. 2015) reports a daytime sighting of Sirius from the La Palma observatory. They note that there were able to do this because they had pointed their telescope at Sirius, and looked along the finder scope (knowing the offset), so they knew exactly where to look. (Even then, it was difficult: "Both of them saw Sirius, but it was difficult; as Wallis put it during his attempt: 'It comes and it goes.'") Knowing exactly where to look is important, because
the limiting magnitude of photopic vision decreases linearly and
rapidly with distance from the center of field of the human eye: at 2°
from the center the limiting magnitude has worsened by 1 magnitude;
at 4° by 2 magnitudes . This means that for an unintentional
sighting, a star should be roughly 2 magnitudes brighter than the
limiting magnitude. Venus meets this requirement, but for Sirius there
is little hope for an unprepared sighting.
(Short answer: Generally speaking, you cannot see stars from the bottom of a well, chimney, mining pit, etc., during the daytime. And you certainly aren't going to see multiple stars from the bottom of a canyon, even if there's a lovely evocation of that idea at one point in The Lord of the Rings.)