At first, the conclusion seems absurd. You have a tank, which is open
at the top of the pipe,
that is comfortably holding 1000 L of water. Then you add just 1 L
more, albeit in an unusual shape, and it bursts! How can this be?
(At first, we will assume that the tank is perfectly rigid and that
water is perfectly incompressible. Later we will relax those
assumptions.)
To see why, let's first take this to an extreme, and imagine that the
pipe is just one water molecule wide, but still contains 1 L of water.
(The pipe would be so long that Earth's gravitational field can't be
treated as constant along its length, but ignore that. As pointed out
by Philip Roe's answer
and
Dast's answer,
the water molecules would stick to such a narrow pipe; ignore that too,
and assume they can still slide freely in the pipe.)
The weight at
the interface of pipe and tank remains a mere 1 kgf or 10 N. No problem
right?
But think about what's going on at that interface, depicted in this
crude illustration (pretend that water molecules are spherical):

The molecule at the bottom of the pipe (call it A) is trying very hard
to get into the tank. It's got an enormous number of molecules above
it, pushing to get in as well. That 10 N, ordinarily so easy to hold,
is concentrated into a very small area, creating enormous pressure on
the molecule just below it. As suggested in
Dast's answer,
we can think of this column of water as being like an extremely thin
needle being pushed into the tank with a force of 10 N.
The molecule below A (call it B) has to hold up the entire column
itself. If it could just squeeze a little this way or that way, A could
get in. But it can't! B pushes just as hard on all of its neighbors,
trying to make just a tiny bit more room, but its neighbors all push
back just as hard. There is no room!
You might think that B's neighbors might be able to share the load. But
remember that these molecules can easily slide past one another. If
even one of B's neighbors (call it D) is not resisting with the full 10
N, then B, propelled by A and sliding past the other neighbors (even at
a right angle if need be), will be able to push into D and move it out
of the way, thereby making additional room in the tank for A. The chain
of forces from A to B to D is sort of like a plumber's pipe snake: the
end is pushed forward with essentially the same force as at the start
(and for low-viscosity water, rather than pipe snakes, it is the same
force).
Even a distant molecule (call it C) can feel the pressure. If C just
moved a tiny bit, maybe pushed its way into a crevice or crack, or
convinced the tank wall to flex just a little, then there would be room
for A. But it cannot! There is no room! Everywhere in the tank, every
molecule must resist the full 10 N, otherwise it will be pushed aside
since nothing impedes the transmission of forces from A.
The upshot is that column of water really has created enormous pressure
everywhere in the tank despite only weighing 10 N.
More realistic assumptions
Let's imagine actually building this tank and pipe configuration and
filling the pipe gradually. We'll go back to using 99 m of pipe with a
0.1 square centimeter cross-section area, and allow the tank walls to
flex a bit. The tank initially holds 1000
L of water and the pipe is empty. You start pouring water into the
pipe. At first, it fills in the way you expect, with the water level in
the pipe rising.
But after a short time, the pipe water level stops rising! You keep
pouring, but the water level in the pipe doesn't move much. Why? The
pressure has built to the point that the water in the tank is starting
to deform the tank. It's rated for 100 kPa, but some deformation
happens on the way to its maximum pressure. The water molecules also
move slightly closer together as the pressure increases; see
incompressibility of water.
As you keep filling, the water keeps forcing its way into the tank,
flexing the walls ever more to accommodate it. Due to the resulting expansion, you have to pour much more
than 1 L to make the pipe water level rise, even though the pipe itself
is holding much less than that. The water molecules are trying to push
into the tank's crystal lattice, and the tank itself suffers
dislocations as it struggles to keep its shape. Meanwhile the water
level in the pipe is approaching 10 m, meaning 100 kPa of pressure.
Finally, the failure pressure is exceeded and the tank fails. (If it's
rated for 100 kPa then we'd hope it fails at more like 300 kPa, but
that's an engineering detail.) We can't actually fill the pipe to a
height of 100 m unless the tank and pipe are very strong.
Finally, let me acknowledge a few more simplifications I've made:
In a fluid, the molecules are all in motion, rather than being locked
in place. That is part of why pressure is equal in all directions,
as the flow allows local pressure differences to equalize. The
roles of molecules A and B will be played by different physical
molecules at different points in time (and are, anyway, abstractions
of layers of molecules in a realistically sized pipe).
Molecules don't really "touch" each other in the same way that
macroscopic objects appear to. For more a sophisticated treatment of
the microscopic interactions, I'd refer to the question
What does it mean for two objects to "touch"?