correlation is computed from covariance so how come covariance can pick up non-linear relationships between variables $X$ and $Y$ but (Pearson's) correlation can't?
Covariance and correlation (which is simply scaled covariance) only pick up linear relationships, but this does not mean that a linear relationships only exists if a variable is a linear transformation of another variable.
Strictly speaking, a linear relationship is a relationship of direct proportionality: any given change in an independent variable $x$ will always produce a corresponding change in the dependent variable $y$ , e.g. a 10 percent increase or decrease in $x$ will result in a 10 percent increase or decreas in $y$, that is $y$ is a linear (more technically: affine) transformation of $x$, $y=a+bx$.
This is a perfect linear relationship, for example:
> x <- 1:10 > y <- 3 + 2*x > cor(x,y)  1
However, there is some linear relationship, or linear dependance, when increasing or decreasing one variable will cause a corresponding increase or decrease in the other variable, even if $y$ is not a linear transformation of $x$, for example:
> x <- 1:10 > y <- 3 + 2*x^2 > cor(x,y)  0.9745586
Notice that correlation is less than one because the linear relationship is not perfect.
There is a linear relationship even if $y$ will tend to increase when $x$ increases, but can occasionally decrease when $x$ increases, for example:
> x <- 1:100 > y <- x + tan(x) > cor(x,y)  0.7940153
There is no linear relationship if $y$ can equally increase or decrease when $x$ increases (or decreases), for example:
> x <- -10:10 # x is increasing > y <- x^2 # y is decreasing when x < 0, then increasing > cor(x,y)  0 > cov(x,y)  0
As you can see, when there is no linear relationship, both correlation and covariance are null.