The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.

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

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}

Comments

  • 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.
  • 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

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.