It is clear that the WLS estimators are consistent if the "wrong" weights used aren't correlated with the explanatory variables. However, I don't know whether this also holds true for unbiasedness.
Solved – Is an WLS estimator unbiased, when wrong weights are used
regressionunbiased-estimatorvarianceweighted-regression
Related Solutions
There are two issues here
You would, ideally, use weights inversely proportional to the variance of the individual $Y_i$. So says the Gauss-Markov Theorem.
You don't know the variance of the individual $Y_i$
If you have deterministic weights $w_i$, you are in the situation that WLS/GLS are designed for. One traditional example is when each observation is an average of multiple measurements, and $w_i$ the number of measurements.
If you have weights that depend on the data through a small number of parameters, you can treat them as fixed and use them in WLS/GLS even though they aren't fixed. For example, you could estimate $\sigma^2(\mu)$ as a function of the fitted $\mu$ and use $w_i=1/\sigma^2(\mu_i)$ -- this seems to be what you are doing in the first example. This is also what happens in linear mixed models, where the weights for the fixed-effects part of the model depend on the variance components, which are estimated from the data.
In this scenario it is possible to prove that although there is some randomness in the weights, it does not affect the large-sample distribution of the resulting $\hat\beta$. It's ok to treat the $w_i$ as if they were known in advance.
If you have weights that are not nearly deterministic, the whole thing breaks down and the randomness in the weights becomes important for both bias and variance. That's what happens in your second example, when you use $w_i=1/r_i^2$. It's an obvious thing to think of, but it doesn't work. The estimating equations (normal equations, score equations) for $\hat\beta$ are $$\sum_i x_iw_i(y_i-x_i\beta)=0$$ With that choice of weights, you get $$\sum_i x_i\frac{(y_i-x_i\beta)}{(y_i-x_i\hat\beta^*)^2}=0$$ where $\hat\beta^*$ is the unweighted estimate. If the new estimate is close to the old one (which should be true for large data sets, because both are consistent), you'd end up with equations like $$\sum_i x_i\frac{1}{(y_i-x_i\beta)}=0$$ which divides by a variable with mean zero, a bad sign.
So:
It's ok to estimate the weights if you have a good mean model (so that the squared residuals are approximately unbiased for the variance) and as long as you don't overfit them. If you do overfit them, you will get a bad estimate of $\beta$ and inaccurate standard errors.
The way all the packages calculate a R square for weighted least square regression is different from the way they do it for ordinary least square regression. So your answer will not match with the results produced by any of the packages. The key change is in the way SS_Total is computed. Instead of using the simple arithmetic mean, the packages use a weighted mean with the same weights which were used to calculate the WLS estimator.
The interpretation of the change in the formula is that the R^2 now tells you the proportion of total variation in weighted Y explained by the weighted X. Which is more intuitive and hence it is used in most packages.
Best Answer
It is unbiased, let's see: Let the linear model be $Y=X\beta +e$, in matrix form, with $E e=0$ and the variance-covariance matrix of the errors $e$ be $\Omega$. We use for weights the matrix $W$. Then the weighted linear least squares estimator is $$ \hat{\beta} = (X'WX))^{-1} X'WX Y $$ and we can calculate its expectation as $$ E \hat{\beta} = (X'WX)^{-1} X\cdot W E Y = (X'WX)^{-1} X\cdot W X\beta =\beta $$ and you can observe that the variance-covariance matrix $\Omega$ do not play any role in the computations!
Of course, if the weight matrix $W$ is estimated from the data in some way, then the above analysis is inadequate! This website Engineering Statistics Handbook discusses this problem, and gives references. Scroll down to "Disadvantages of weighted least squares". Their advice is: