Thread: OTICE: adding missing FROM-clause entry for table
I just experienced some bad SQL causing quite unexpected results. I used a statement like this: SELECT t1.a, t1.b, t2.d FROM test1 t1, test2 t2 WHERE t1.a = test2.a; Where I should have used this instead: SELECT t1.a, t1.b, t2.d FROM test1 t1, test2 t2 WHERE t1.a = t2.a; When I looked into it and tried it from psql, I got this notice: NOTICE: adding missing FROM-clause entry for table "test2" Now, I understand that postgresql is adding "test2" to the list of tables, I am selecting from as it is missing. However the result is quite different from what I expected. Is there a way to change this behaviour to generate an error instead of just a notice? -- Christian Laursen
# christian@pil.dk / 2005-08-12 10:17:50 +0200: > I just experienced some bad SQL causing quite unexpected results. > > I used a statement like this: > SELECT t1.a, t1.b, t2.d FROM test1 t1, test2 t2 WHERE t1.a = test2.a; > > Where I should have used this instead: > SELECT t1.a, t1.b, t2.d FROM test1 t1, test2 t2 WHERE t1.a = t2.a; > > When I looked into it and tried it from psql, I got this notice: > NOTICE: adding missing FROM-clause entry for table "test2" > > Now, I understand that postgresql is adding "test2" to the list of > tables, I am selecting from as it is missing. However the result > is quite different from what I expected. > > Is there a way to change this behaviour to generate an error instead > of just a notice? set add_missing_from = false in postgresql.conf -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991
Christian Laursen wrote: > When I looked into it and tried it from psql, I got this notice: > NOTICE: adding missing FROM-clause entry for table "test2" > > Now, I understand that postgresql is adding "test2" to the list of > tables, I am selecting from as it is missing. However the result > is quite different from what I expected. > > Is there a way to change this behaviour to generate an error instead > of just a notice? Yes - there is a setting in postgresql.conf that allows you to turn this off (and I do so). The only reason it's left on is for compatibility with older versions. It's called "add_missing_from" or similar - see the "runtime configuration" chapter of the manuals for details. -- Richard Huxton Archonet Ltd