
-- needs to do outer join as well, and properly handle NULL
CREATE VIEW blast_hit_symmetric2 AS 
select bh0.br_id AS br_id, 
bh0.seq_id_0 AS seq_id_0,
bh0.seq_id_1 AS seq_id_1,
greatest(bh0.bit_score,bh1.bit_score) AS bit_score,
least(bh0.e_value,bh1.e_value) AS e_value 
FROM blast_hit bh0 
JOIN blast_hit bh1 on ((bh1.seq_id_0 = bh0.seq_id_1) 
			and (bh1.seq_id_1 = bh0.seq_id_0) 
			and (bh0.br_id = bh1.br_id) 
			and (bh0.hit_rank = 0) 
			and (bh1.hit_rank = 0))



SELECT yg0.ygob_gidx AS ygob_gidx0,
yg0.chromosome-1,
yg0.genome_pos,
yg1.ygob_gidx AS ygob_gidx1,
yg1.chromosome-1,
yg1.genome_pos,
bit_score
FROM blast_hit
JOIN ygob_gene AS yg0 ON (seq_id_0 = yg0.seq_id)
JOIN ygob_gene AS yg1 ON (seq_id_1 = yg1.seq_id)
WHERE br_id=60
AND yg0.gb_tax_id='33169'
AND yg1.gb_tax_id='28985'


AND e_value < 10E-10
ORDER BY yg0.genome_pos, bit_score

|     33169 | Ashbya gossypii          |          1 |
|     28985 | Kluyveromyces lactis     |          1 |

not null
|      4932 |     5477 |
|      5478 |     5153 |
|     28985 |     5289 |
|     33169 |     4710 |

null
| gb_tax_id | count(*) |
+-----------+----------+
|      4914 |     5230 |
|      4932 |      139 |
|      4934 |     2970 |
|      5478 |       27 |
|     28985 |       38 |
|     33169 |        6 |
|    340169 |     5596 |
+-----------+----------+
7 rows in set (0.11 sec)

select * from ygob_gene as yg0
JOIN blast_hit ON (yg0.seq_id = seq_id_0)
JOIN ygob_gene as yg1 ON (yg1.seq_id = seq_id_1)
WHERE yg0.gb_tax_id=33169
AND br_id=60
limit 10;


create view blast_hit_symmetric5 as
select bh0.br_id, bh0.seq_id_0, bh0.seq_id_1, 
IF(bh0.bit_score IS NULL or bh1.bit_score IS NULL,
COALESCE(bh0.bit_score,bh1.bit_score), GREATEST(bh0.bit_score,bh1.bit_score)) as bit_score,
IF(bh0.bit_score IS NULL or bh1.bit_score IS NULL,
COALESCE(bh0.e_value,bh1.e_value), LEAST(bh0.e_value,bh1.e_value)) as e_value,
GREATEST(bh0.bit_score, bh1.bit_score) as bit_score1,
LEAST(bh0.e_value, bh1.e_value) as e_value1 from
blast_hit as bh0
left join blast_hit as bh1 on (bh1.seq_id_0 = bh0.seq_id_1 and 
bh1.seq_id_1 = bh0.seq_id_0 and 
bh0.br_id = bh1.br_id and bh0.hit_rank=0 and bh1.hit_rank=0)
UNION
select bh1.br_id, bh1.seq_id_0, bh1.seq_id_1, 
IF(bh0.bit_score IS NULL or bh1.bit_score IS NULL,
COALESCE(bh0.bit_score,bh1.bit_score), GREATEST(bh0.bit_score,bh1.bit_score)) as bit_score,
IF(bh0.bit_score IS NULL or bh1.bit_score IS NULL,
COALESCE(bh0.e_value,bh1.e_value), LEAST(bh0.e_value,bh1.e_value)) as e_value,
GREATEST(bh0.bit_score, bh1.bit_score) as bit_score1,
LEAST(bh0.e_value, bh1.e_value) as e_value1 from
blast_hit as bh0
right join blast_hit as bh1 on (bh1.seq_id_0 = bh0.seq_id_1 and 
bh1.seq_id_1 = bh0.seq_id_0 and 
bh0.br_id = bh1.br_id and bh0.hit_rank=0 and bh1.hit_rank=0)
where bh0.seq_id_0 is null


create view blast_hit_symmetric6 as
select bh0.br_id, bh0.seq_id_0, bh1.seq_id_1,
IF(bh0.bit_score IS NULL or bh1.bit_score IS NULL,
COALESCE(bh0.bit_score,bh1.bit_score), GREATEST(bh0.bit_score,bh1.bit_score)) as bit_score
from blast_hit as bh0
left join blast_hit as bh1 ON (bh1.seq_id_0 = bh0.seq_id_1 and 
bh1.seq_id_1 = bh0.seq_id_0 and 
bh0.br_id = bh1.br_id and bh0.hit_rank=0 and bh1.hit_rank=0)
UNION
select bh1.br_id, bh1.seq_id_0, bh1.seq_id_1, bh1.bit_score
from blast_hit as bh1
left join blast_hit as bh0 ON (bh1.seq_id_0 = bh0.seq_id_1 and 
bh1.seq_id_1 = bh0.seq_id_0 and 
bh0.br_id = bh1.br_id and bh0.hit_rank=0 and bh1.hit_rank=0)
where bh0.bit_score is NULL


GREATEST(bh0.bit_score,bh1.bit_score) as bit_score

-- this successfully fetches all scores where the query is present
-- it does not do the reverse. eg., 3348964 |  6473061
select bh0.br_id, bh0.seq_id_0, bh0.seq_id_1, bh1.seq_id_1, bh1.seq_id_0, 
bh0.bit_score as bh0,
bh1.bit_score as bh1,
IF(bh0.bit_score IS NULL or bh1.bit_score IS NULL,
   COALESCE(bh0.bit_score,bh1.bit_score), 
   GREATEST(bh0.bit_score,bh1.bit_score)) as bit_score
FROM blast_hit AS bh0
left JOIN blast_hit AS bh1 ON (bh1.seq_id_0 = bh0.seq_id_1
                           AND bh1.seq_id_1 = bh0.seq_id_0
                           AND bh0.br_id = bh1.br_id
                           AND bh0.hit_rank = 0
                           AND bh1.hit_rank = 0)
WHERE bh0.br_id=41



-- this adds the reverse if not already present
select count(*) from (
select bh0.br_id, bh0.seq_id_1 as seq_id_0, bh0.seq_id_0 as seq_id_1,
bh0.bit_score
FROM blast_hit AS bh0
LEFT JOIN blast_hit AS bh1 ON (bh1.seq_id_0 = bh0.seq_id_1
                           AND bh1.seq_id_1 = bh0.seq_id_0
                           AND bh0.br_id = bh1.br_id
                           AND bh0.hit_rank = 0
                           AND bh1.hit_rank = 0)
WHERE bh1.br_id IS NULL
and bh0.br_id=1) as foo  -- 124908
and bh0.seq_id_1=3348964

select count(*) from (

--this query works
select count(*) from 
(SELECT bh0.br_id, bh0.seq_id_0, bh0.seq_id_1,
IF(bh0.bit_score IS NULL or bh1.bit_score IS NULL,
   COALESCE(bh0.bit_score,bh1.bit_score), 
   GREATEST(bh0.bit_score,bh1.bit_score)) as bit_score
FROM blast_hit AS bh0
left JOIN blast_hit AS bh1 ON (bh1.seq_id_0 = bh0.seq_id_1
                          AND bh1.seq_id_1 = bh0.seq_id_0
                          AND bh0.br_id = bh1.br_id
                          AND bh1.hit_rank = 0)
where bh0.hit_rank=0
and bh0.br_id=75

union

select bh0.br_id, bh0.seq_id_1 as seq_id_0, bh0.seq_id_0 as seq_id_1,
bh0.bit_score
FROM blast_hit AS bh0
LEFT JOIN blast_hit AS bh1 ON (bh1.seq_id_0 = bh0.seq_id_1
                           AND bh1.seq_id_1 = bh0.seq_id_0
                           AND bh0.br_id = bh1.br_id
                           AND bh1.hit_rank = 0)
WHERE bh0.hit_rank=0
and bh0.br_id=75
and bh1.br_id IS NULL
) as foo








select * from 
(select seq_id_0 as seq_id_1, seq_id_1 as seq_id_0, bit_score
from blast_hit
where br_id=63
and hit_rank=0
union
select seq_id_0, seq_id_1, bit_score
from blast_hit
where br_id=63
and hit_rank=0) as foo
limit 10;

138531
+-------+----------+
| br_id | count(*) |
+-------+----------+
|    41 |   297147 |
|    51 |  1535125 |
|    52 |  1522055 |
|    57 |  1543329 |
|    60 |  1206403 |
+-------+----------+
