Select statement return different row result when include/exclude a certain column.

Bill_Lee

Select statement return different row result when include/exclude a certain column. it may have something to do with our dataset and actual query. we would like to have this looked at and is willing to give you access to the real database i.e. below query doesn't return the same amount of rows. (query run in both vsql and jdbc driver, both have problems.) select table1.a, table2.b, table3.c from table1 left join table2 left join table3 left join table4 where {condtions} select table1.a, -- table2.b, table3.c from table1 left join table2 left join table3 left join table4 where {conditions}

Daniel_Leybovic

Hi! Conditions are important - it filters rows, without them hard to answer. For example, condition could be "chain dependency" (t1.id --> t2.id --> t3.id): t1.id > t2.id and t2.id > t3.id or could be "start dependency" (t3.id <-- t1.id --> t2.id): t1.id > t2.id and t1.id < t3.id or even trivial condition: table2.b != 0 and of cause result can be different. Result have to be same only in case that condition is "equivalence relation"(http://en.wikipedia.org/wiki/Equivalence_relation) otherwise it is not promised.

[Deleted User]

Hi all, Daniel, thanks for the response! Very good points; hopefully that has pointed Bill in the right direction. I just wanted to add in response to Bill's request "we would like to have this looked at" -- If you would like to have it looked at by folks at Vertica, rather than a forum post, you should open up a Support case. That's what Support is there for :-) Also, I recently ran into a report like this. It turned out to be a subtle typo in the query. For example, the following two queries will (correctly) return very different results: SELECT a b FROM t WHERE b < 10; and SELECT * FROM t WHERE b < 10; (The former query was missing a comma between "a" and "b", so didn't mean what its author thought that it meant. The original queries were of course more complicated, so this was hard to spot.) Adam