Thread: BUG #3822: Nonstandard precedence for comparison operators
The following bug has been logged online: Bug reference: 3822 Logged by: Pedro Gimeno Email address: pgsql-001@personal.formauri.es PostgreSQL version: 8.2.5 Operating system: Any Description: Nonstandard precedence for comparison operators Details: The operators <>, <= and >= are expected to have the same precedence as =, < and >, but according to the docs (and matching actual behaviour) they have the same precedence as e.g. ||. This leads to surprises such as this one: SELECT 'a' = 'b' || 'c'; SELECT 'a' < 'b' || 'c'; SELECT 'a' > 'b' || 'c'; return either FALSE or TRUE, but SELECT 'a' <> 'b' || 'c'; SELECT 'a' <= 'b' || 'c'; SELECT 'a' >= 'b' || 'c'; give an error because they are equivalent to: SELECT ('a' <> 'b') || 'c'; SELECT ('a' <= 'b') || 'c'; SELECT ('a' >= 'b') || 'c'; respectively, which try to concatenate a boolean value with 'c'. On the other hand, the following work as expected: SELECT 'b' || 'c' <> 'a'; SELECT 'b' || 'c' >= 'a'; SELECT 'b' || 'c' <= 'a'; That's because, having || and e.g. <> the same priority, they are evaluated left-to-right. Of course the same applies to != since it is converted to <>. Now being picky, the precedence of < and > should be the same as that of = (for comparison, not for assignment), which makes a difference in rare cases like this: SELECT false = false < false; which in PostgreSQL returns true instead of false because it is equivalent to this: SELECT false = (false < false); -- Pedro Gimeno
>>> On Mon, Dec 17, 2007 at 1:58 PM, in message <200712171958.lBHJwOBb037317@wwwmaster.postgresql.org>, "Pedro Gimeno" <pgsql-001@personal.formauri.es> wrote:=20 =20 > Description: Nonstandard precedence for comparison operators =20 > The operators <>, <=3D and >=3D are expected to have the same precedence = as =3D, < > and >, but according to the docs (and matching actual behaviour) they have > the same precedence as e.g. ||. >=20 > This leads to surprises =20 > Of course the same applies to !=3D since it is converted to <>. >=20 > Now being picky, the precedence of < and > should be the same as that of = =3D > (for comparison, not for assignment), which makes a difference in rare ca= ses =20 Everything Pedro said in this post matched my understanding of the SQL specification. I went back and read those sections closely, and I can't find any basis for interpreting the spec any other way. This seems like a pretty serious point of non-compliance to me. =20 That said, bringing PostgreSQL into compliance with the standard would undoubtedly break some people's existing applications. It seems to call for the same phased approach as the standard conforming string literals, with GUCs to control warnings for problem constructs and legacy versus standard runtime behavior. =20 -Kevin =20
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes: > That said, bringing PostgreSQL into compliance with the standard > would undoubtedly break some people's existing applications. The spec seems to barely have a notion of operator precedence at all --- for example, all the variants of <predicate> are at the same precedence level, and if I'm reading it right they actively disallow ambiguous cases by requiring parentheses; note the way that <boolean primary> is defined. This entire arrangement breaks down as soon as you consider user-defined operators that yield boolean results. So I'm not particularly excited about the idea of slavish compliance with the spec in this area. To the extent that you do believe the spec, there are more problems with our precedence rules than just where <= fits --- it looks to me like IS [NOT] NULL is at the wrong precedence, too. And then there's the whole question of associativity. > It seems to call for the same phased approach as the standard > conforming string literals, with GUCs to control warnings for problem > constructs and legacy versus standard runtime behavior. Good luck implementing that --- the precedence is hard-wired into the bison grammar rules. There are also extremely good reasons for not having GUC variables that affect parsing behavior. Given that it's been this way for ten years and no one has complained before, I'm disinclined to change it, and even more disinclined to invest the effort that would be involved in letting the behavior vary at runtime. regards, tom lane
>>> On Thu, Dec 27, 2007 at 4:22 PM, in message <21242.1198794130@sss.pgh.= pa.us>, Tom Lane <tgl@sss.pgh.pa.us> wrote:=20 > "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes: > To the extent that you do believe the spec, there are more problems with > our precedence rules than just where <=3D fits --- it looks to me like IS > [NOT] NULL is at the wrong precedence, too. And then there's the whole > question of associativity. =20 Well, surely, the fact that there is more than one problem isn't, by itself, an argument not to fix any of them. =20 >> It seems to call for the same phased approach as the standard >> conforming string literals, with GUCs to control warnings for problem >> constructs and legacy versus standard runtime behavior. >=20 > Good luck implementing that --- the precedence is hard-wired into the > bison grammar rules. There are also extremely good reasons for not > having GUC variables that affect parsing behavior. =20 But we have done so before, in the interests of converging on the standard. =20 > Given that it's been this way for ten years and no one has complained > before, I'm disinclined to change it, and even more disinclined to > invest the effort that would be involved in letting the behavior vary > at runtime. =20 That's a fair point. It's very unlikely to bite an existing PostgreSQL user very hard. The pain could come in migrating a large set of complex queries from another DBMS. I don't recall seeing any migration document to help people identify these issues up front, but we should probably have one (if we don't) and this should probably be mentioned. =20 Based on this discussion, I'll remind our staff to be generous in the use of parentheses when doing ad hoc queries. =20 -Kevin =20
Tom Lane wrote: > "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes: >> That said, bringing PostgreSQL into compliance with the standard >> would undoubtedly break some people's existing applications. I wonder how that compares to broken queries while migrating databases from other systems. I'd bet there are more of the latter. We ran into that while running a query like: SELECT ... WHERE column <> another_column || 'literal';, variants of which I think can be relatively common compared to e.g. applications that build a boolean array using expr1 <> expr2 || boolean_value. > The spec seems to barely have a notion of operator precedence at all --- The precedence is given by the parse tree and is well defined. Perhaps it may vary for a given operator depending on the context but it's clearly different to the one PostgreSQL is using in the examples I gave. > for example, all the variants of <predicate> are at the same precedence > level, and if I'm reading it right they actively disallow ambiguous > cases by requiring parentheses; note the way that <boolean primary> > is defined. This entire arrangement breaks down as soon as you consider > user-defined operators that yield boolean results. So I'm not > particularly excited about the idea of slavish compliance with the spec > in this area. Note that we considered PostgreSQL precisely because of its high standards compliance and this problem has been a bit disappointing, even more given that I looked for information on what nonstandard bits did PostgreSQL have and didn't see this. In the case of user defined operators I'd expect them to have a fixed precedence regardless of the semantics and that can be different depending on the operator. > Given that it's been this way for ten years and no one has complained > before, I'm disinclined to change it, and even more disinclined to > invest the effort that would be involved in letting the behavior vary > at runtime. It has often happened that a new version has caused the need of porting code, that's not new. Users will very likely appreciate compliance with the standard even with the hassle of porting the applications, specially when the fixes, if they're necessary, can easily be made backwards compatible by using parentheses. For that reason I don't think a runtime selection of behaviour would be neecessary in this case. -- Pedro Gimeno
On Dec 29, 2007, at 14:09 , Pedro Gimeno wrote: > variants of which I think can be > relatively common compared to e.g. applications that build a boolean > array using expr1 <> expr2 || boolean_value. I'm probably being dense, but I don't see how this is an issue. || is string concatenation, not a logical OR. You're going to throw an error because || isn't a boolean operator, not because of any strange precedence rules. test=# select 1 <> 2 || true; ERROR: operator does not exist: boolean || boolean LINE 1: select 1 <> 2 || true; ^ HINT: No operator matches the given name and argument type(s). You may need to add explicit type casts. test=# select 'foo'::text <> 'bar'::text || true; ERROR: operator does not exist: boolean || boolean LINE 1: select 'foo'::text <> 'bar'::text || true; ^ HINT: No operator matches the given name and argument type(s). You may need to add explicit type casts. Michael Glaesemann grzm seespotcode net
Michael Glaesemann <grzm@seespotcode.net> writes: > I'm probably being dense, but I don't see how this is an issue. That's just a bug in his example ;-) The real question is whether there is enough of a problem here to justify creating new problems, in the form of backwards-compatibility hazards. One complaint in ten years tells me there's not. We know what the SQL-portability hot-button issues are, because we have gotten repeated complaints about them --- identifier case-folding and backslashes in string literals are two that spring to mind instantly. If operator precedence were an issue worth spending time on, it would have come up before, repeatedly. I would not actually object to making small tweaks in the precedence rules to move a little closer to spec compliance; it undoubtedly is just plain weird that = and <> have their own precedence rules. However, "closer to spec" is a lot weaker argument than "matches spec", and I really don't think that we want to try to match the spec exactly in this area. What I do *not* want to do is invest the level of effort suggested by Kevin, with two grammars or whatever it would take to make that happen. This problem is not worth that. regards, tom lane
Tom Lane wrote: > Michael Glaesemann <grzm@seespotcode.net> writes: >> I'm probably being dense, but I don't see how this is an issue. > > That's just a bug in his example ;-) Right, sorry. I meant boolean_array rather than boolean_value: => SELECT 'a' <> 'a' || '{true}'; ?column? ---------- {f,t} (1 row) -- Pedro Gimeno
This has been saved for the 8.4 release: http://momjian.postgresql.org/cgi-bin/pgpatches_hold --------------------------------------------------------------------------- Pedro Gimeno wrote: > Tom Lane wrote: > > > "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes: > >> That said, bringing PostgreSQL into compliance with the standard > >> would undoubtedly break some people's existing applications. > > I wonder how that compares to broken queries while migrating databases > from other systems. I'd bet there are more of the latter. We ran into > that while running a query like: SELECT ... WHERE column <> > another_column || 'literal';, variants of which I think can be > relatively common compared to e.g. applications that build a boolean > array using expr1 <> expr2 || boolean_value. > > > The spec seems to barely have a notion of operator precedence at all --- > > The precedence is given by the parse tree and is well defined. Perhaps > it may vary for a given operator depending on the context but it's > clearly different to the one PostgreSQL is using in the examples I gave. > > > for example, all the variants of <predicate> are at the same precedence > > level, and if I'm reading it right they actively disallow ambiguous > > cases by requiring parentheses; note the way that <boolean primary> > > is defined. This entire arrangement breaks down as soon as you consider > > user-defined operators that yield boolean results. So I'm not > > particularly excited about the idea of slavish compliance with the spec > > in this area. > > Note that we considered PostgreSQL precisely because of its high > standards compliance and this problem has been a bit disappointing, even > more given that I looked for information on what nonstandard bits did > PostgreSQL have and didn't see this. > > In the case of user defined operators I'd expect them to have a fixed > precedence regardless of the semantics and that can be different > depending on the operator. > > > Given that it's been this way for ten years and no one has complained > > before, I'm disinclined to change it, and even more disinclined to > > invest the effort that would be involved in letting the behavior vary > > at runtime. > > It has often happened that a new version has caused the need of porting > code, that's not new. Users will very likely appreciate compliance with > the standard even with the hassle of porting the applications, specially > when the fixes, if they're necessary, can easily be made backwards > compatible by using parentheses. For that reason I don't think a runtime > selection of behaviour would be neecessary in this case. > > -- Pedro Gimeno > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Pedro Gimeno wrote: > > The spec seems to barely have a notion of operator precedence at all --- > > The precedence is given by the parse tree and is well defined. Perhaps > it may vary for a given operator depending on the context but it's > clearly different to the one PostgreSQL is using in the examples I gave. It would be interesting if someone could work out an operator precedence table that the spec would require, so we can contrast that with the one we currently implement. At the moment I couldn't really tell how extensive the problem is. Even if we change nothing, this would be useful for documentation. -- Peter Eisentraut http://developer.postgresql.org/~petere/
Added to TODO: * Fix inconsistent precedence of =, >, and < compared to <>, >=, and <= http://archives.postgresql.org/pgsql-bugs/2007-12/msg00145.php --------------------------------------------------------------------------- Pedro Gimeno wrote: > Tom Lane wrote: > > > "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes: > >> That said, bringing PostgreSQL into compliance with the standard > >> would undoubtedly break some people's existing applications. > > I wonder how that compares to broken queries while migrating databases > from other systems. I'd bet there are more of the latter. We ran into > that while running a query like: SELECT ... WHERE column <> > another_column || 'literal';, variants of which I think can be > relatively common compared to e.g. applications that build a boolean > array using expr1 <> expr2 || boolean_value. > > > The spec seems to barely have a notion of operator precedence at all --- > > The precedence is given by the parse tree and is well defined. Perhaps > it may vary for a given operator depending on the context but it's > clearly different to the one PostgreSQL is using in the examples I gave. > > > for example, all the variants of <predicate> are at the same precedence > > level, and if I'm reading it right they actively disallow ambiguous > > cases by requiring parentheses; note the way that <boolean primary> > > is defined. This entire arrangement breaks down as soon as you consider > > user-defined operators that yield boolean results. So I'm not > > particularly excited about the idea of slavish compliance with the spec > > in this area. > > Note that we considered PostgreSQL precisely because of its high > standards compliance and this problem has been a bit disappointing, even > more given that I looked for information on what nonstandard bits did > PostgreSQL have and didn't see this. > > In the case of user defined operators I'd expect them to have a fixed > precedence regardless of the semantics and that can be different > depending on the operator. > > > Given that it's been this way for ten years and no one has complained > > before, I'm disinclined to change it, and even more disinclined to > > invest the effort that would be involved in letting the behavior vary > > at runtime. > > It has often happened that a new version has caused the need of porting > code, that's not new. Users will very likely appreciate compliance with > the standard even with the hassle of porting the applications, specially > when the fixes, if they're necessary, can easily be made backwards > compatible by using parentheses. For that reason I don't think a runtime > selection of behaviour would be neecessary in this case. > > -- Pedro Gimeno > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +