SQL Question

[zlander79]

I updated a view, the only thing I changed was I added this statement:

		`os`.`site_distance` AS `Distance`,

I didn't change the join statements, where statements or anything. I'm getting different numbers of rows returned between the view with the added statement and without. Any idea why this is happening?

[OS-Wiz]

Hmmm, need version of SQL (like Oracle, MySQL, whatever) and the complete statement. My first guess puntuation error. Or the ending comma changed the query but passed parsing/syntax checking.

[zlander79]

view with the statement in it:

CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `rpokeefe`@`%` 
    SQL SECURITY DEFINER
VIEW `METAGENO`.`COMP_REPORT_2` AS
    select 
        `g`.`id` AS `MH_gene_id`,
        `g`.`Name` AS `MH_name`,
        `f`.`Path` AS `MH_file`,
        `sc`.`Name` AS `MH_scaffold`,
        `g`.`Operon` AS `Operon`,
        `o`.`prime_gene` AS `Operon_Prime_Gene`,
        `os`.`site` AS `Site`,
        `os`.`site_type` AS `Site_Type`,
		`os`.`site_distance` AS `Site_Distance`,	
        `s`.`Score` AS `Site_Score`,
        `m`.`name` AS `Motif`,
        `gort`.`ortho` AS `ORTHO_NCBI_gi`,
        `gort`.`eval_coef` AS `GENE_ORTHO_E_coef`,
        `gort`.`eval_power` AS `GENE_ORTHO_E_power`,
        `rgi`.`gi` AS `COG_NCBI_gi`,
        `ortcp`.`eval_coef` AS `ORTHO_COG_E_coef`,
        `ortcp`.`eval_power` AS `ORTHO_COG_E_power`,
        `rgi`.`accession` AS `NCBI_Accession`,
        `rgi`.`description` AS `NCBI_Description`,
        `rgi`.`product` AS `NCBI_Product`,
        `cpc`.`cog` AS `COG`,
        `c`.`Name` AS `COG_Description`,
        `cpc`.`cogprot` AS `COG_Protein`,
        `cpc`.`cog_org` AS `COG_Protein_organism`,
        `cfg`.`id` AS `COG_Group`,
        `cfg`.`Desc` AS `COG_Group_Description`,
        `co`.`taxid` AS `Tax_id`,
        `co`.`name` AS `Organism_Name`,
        `s`.`Sequence` AS `Site_Sequence`
    from
        (((((((((((((((`METAGENO`.`GENE` `g`
        join `METAGENO`.`FILE` `f`)
        join `METAGENO`.`SCAFFOLD` `sc`)
        join `METAGENO`.`OPERON` `o`)
        join `METAGENO`.`OPERON_SITE` `os`)
        join `METAGENO`.`SITE` `s`)
        join `METAGENO`.`MOTIF` `m`)
        join `METAGENO`.`GENE_ORTHO` `gort`)
        join `METAGENO`.`ORTHO` `ort`)
        join `METAGENO`.`ORTHO_COGPROT` `ortcp`)
        join `METAGENO`.`COGPROT_COG` `cpc`)
        join `METAGENO`.`COG` `c`)
        join `METAGENO`.`COG_GROUP` `cg`)
        join `METAGENO`.`COG_FUNCTIONAL_GROUP` `cfg`)
        join `METAGENO`.`REF_GENE_INFO` `rgi`)
        join `METAGENO`.`COG_ORG` `co`)
    where
        ((`g`.`Operon` = `o`.`id`)
            and (`o`.`File` = `f`.`id`)
            and (`o`.`Scaffold` = `sc`.`id`)
            and (`o`.`id` = `os`.`operon`)
            and (`os`.`site` = `s`.`id`)
            and (`s`.`Motif` = `m`.`id`)
            and (`g`.`id` = `gort`.`gene`)
            and (`gort`.`ortho` = `ort`.`ortho`)
            and (`ort`.`ortho` = `ortcp`.`ortho`)
            and (`ortcp`.`cogprot` = `cpc`.`gi`)
            and (`cpc`.`gi` = `rgi`.`gi`)
            and (`cpc`.`cog` = `c`.`id`)
            and (`cpc`.`cog` = `c`.`id`)
            and (`c`.`id` = `cg`.`COG`)
            and (`cg`.`GROUP` = `cfg`.`id`)
            and (`cpc`.`cog_org` = `co`.`cog_org`))
    group by `gort`.`ortho`
    having max(`os`.`site`)

SQL query on view:

select * from METAGENO.COMP_REPORT_2 where  (Motif = "Gamma")

I'm using mysql

Additional Comment:

The only addition is the statement I had in the first post, everything else is the same. I really don't understand why this would change. It is sort of worrying me because I thought I had a fairly firm grasp on how this view was working

Rows returned without statement: 2700
Rows returned with statement : 2691

It might seem like a small amount but this isn't exactly data where we can fudge things.

Additional Comment:

I've checked through all of the data returned be each version of the view with the above SQL query. The vast majority of results were the same however each version brought back data that the other one did not.

I've pulled up everything on those entries and the "Site_Distance" value for the different entries isn't null or anything.

I'm really lost on this.

Additional Comment:

Even weirder, I changed the group by statement

from

group by `gort`.`ortho`

to

`g`.`id`

And now the query on the view brings back the same data regardless of which version of the view I used. Oh well, I've technically solved the issue but it still is a bit strange why that would happen.

[OS-Wiz]

Hmmm, does column name 'Motif' exist in any of the other tables in the query?
The two different GROUP BY clauses might return (probably would return) a different number of rows.
I see no reason why simply adding another column to the select clause would change the number of rows returned (in Oracle and DB2 it would NOT). You sure the two different views executed against the same schema on the same host?