Thank you. I am not sure if I ran into sort of "bug" in Impala, here is what I like to share here and I am waiting for Cloudera's reply:

I am so sorry that I forgot to mention that I am working in Cloudera and Netezza, I post my question here because I know this is the best forum...

After closely looked into every sub set of query result, I was able to fix the query:

The client (business side) might be using sort of fancy auto tool...

Thanks both same suggestion, now I get the right result with the revised script below for later reference:

with CancerTypes as
(
  select distinct tr.CancerType
   , x.Sex
  from tmpRate...

Sorry I have to come back again:

When I am doing another similar stats query, my adapted script doesn't produce the right result, here is my queries and results:

with...

I am to generate the result using the adapted script below:

Create Proc spListSurvivalRate
As
Set rowcount 0;
With CTE AS
(
SELECT
  ID