1

I am reading someone else sql and his code was like this

There is view called user_v with column path as Array

select * from user_v where 'USER_TYPE'=path[2]

can't i use

path[2] = 'USER_TYPE'

einpoklum
  • 118,144
  • 57
  • 340
  • 684
Mirage
  • 30,868
  • 62
  • 166
  • 261
  • 3
    You can use will not be a problem... – Azar Jun 27 '14 at 08:10
  • Can anyone point to documentation? We all know that `a=b` is equal to `b=a` in SQL, but shouldn't this be documented somewhere in PostgreSQL or SQL standard? – Tomas Greif Jun 27 '14 at 08:59
  • @Tomas Greif: what exactly "this"? Operator `=` compares 2 expressions on equality. It doesn't matter what operand comes first. – zerkms Jun 27 '14 at 09:00
  • @zerkms I expect your comment should be in documentation: "Operator `=` compares 2 expressions on equality. It doesn't matter what operand comes first." If it is not documented somewhere than how do we know that? Based on our experience? – Tomas Greif Jun 27 '14 at 09:05
  • @Tomas Greif: I don't understand what exactly you want to see there. Could you be more clear so that we were able to point you to the exact place in documentation? – zerkms Jun 27 '14 at 10:10
  • @Tomas Greif: http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt see section 8.2. Is there something unclear left for you? – zerkms Jun 27 '14 at 10:17

3 Answers3

4

This is a precaution taken by some programmers in languages where assignment and comparison can be easily confused, such as C or PHP, where the following statement looks innocent:

 if ( $foo = 1 )

but it is actually assigning 1 to $foo, and the if will always evaluate to true (in PHP, at least, where 1 is true). What was meant was if ( $foo == 1 ).

If you reverse the arguments, the error becomes obvious sooner:

if ( 1 = $foo ) # SYNTAX ERROR
if ( 1 == $foo ) # Desired behaviour

This is sometimes known as "yoda coding", and is in the coding standards of Wordpress, for example.

See also: Why put the constant before the variable in a comparison?

In SQL, there is less chance of such a muddle, since although = can mean either assignment or comparison, there are rarely situations where a typo would select the wrong meaning.

However, if the coding standards for every other language used by a project mandate it, it would make sense to reinforce the habit by also using it in SQL, since I can't think of a specific reason not to write it that way.

Community
  • 1
  • 1
IMSoP
  • 89,526
  • 13
  • 117
  • 169
1

There is no difference at all.

zerkms
  • 249,484
  • 69
  • 436
  • 539
  • Is it more to do with coding style because that coder is very cautious about coding style – Mirage Jun 27 '14 at 08:19
  • It's probably a habit from [safeguarding againts unwanted variable assignment](http://stackoverflow.com/questions/370366/why-put-the-constant-before-the-variable-in-a-comparison), even though it's (mostly) unnecessary in SQL. – JJJ Jun 27 '14 at 08:24
  • 3
    @Juhana: I'd say it's entirely unnecessary for sql, and mostly pointless for programming languages. – zerkms Jun 27 '14 at 08:27
  • @user3646965: that coder is very cautious about following some weird guidelines without a real reason :-) – zerkms Jun 27 '14 at 08:28
  • 1
    Well in theory you could have `UPDATE foo SET bar = 1 WHERE baz = 2` and get SET and WHERE mixed up. – JJJ Jun 27 '14 at 08:30
  • @Juhana: in theory you could confuse `UPDATE` and `SELECT`? Are you serious now? – zerkms Jun 27 '14 at 08:31
  • No, mix up SET and WHERE, not UPDATE and SELECT. I was just giving an example of a query where it could be used. I wasn't talking about the OP's query. – JJJ Jun 27 '14 at 08:32
  • @Juhana: "where it could be used" --- and where? If one may confuse `UPDATE` and `SELECT` - then swapping operands won't help them. – zerkms Jun 27 '14 at 08:33
  • 1
    If you have `UPDATE foo SET 2 = baz WHERE bar = 1` it will throw an error. If you have `UPDATE foo SET baz = 2 WHERE bar = 1` it will run the update and break the database if you meant to say `...SET bar = 1 WHERE baz = 2` (or `...WHERE 2 = baz`) instead. – JJJ Jun 27 '14 at 08:34
  • @Juhana: so the point is to write a syntactically invalid query to protect yourself from incorrect queries? I have a better solution then - not to write queries at all. This way it will guarantee you will never break anything. – zerkms Jun 27 '14 at 08:36
  • You're missing the point and I'm not sure there's any point in trying to explain it any further, so let's just leave it here. – JJJ Jun 27 '14 at 08:40
  • @Juhana: that's right - I'm missing a point in doing something that is pointless. Especially in writing syntactically incorrect queries deliberately. – zerkms Jun 27 '14 at 08:40
  • Yes, it's equally pointless as the `if( 'bar' == foo )` style. Note that I'm not saying that you should deliberately write incorrect queries, I'm saying that it *safeguards* you when you make a mistake (similar to when you write `if( 'bar' == foo )`). – JJJ Jun 27 '14 at 08:44
  • @Juhana: how writing incorrect query is a safeguard? `if( 'bar' == foo )` is a valid statement. `SET bar = 1` is an invalid clause. I'm seriously asking: how writing incorrect query is better than no writing a query at all? From the result point of view they behave equally - just nothing will be changed. – zerkms Jun 27 '14 at 08:56
  • 1
    @zerkms Nobody is suggesting you write `SET 1 = bar`, the suggestion is to write `WHERE 1 = bar` so that you cannot *accidentally* put it in the `SET` clause. It's more of a stretch than in C or PHP, but the same principle applies: nobody suggests writing an assignment as `1 = $foo`, because you can't, *and that's the whole point*. – IMSoP Jun 27 '14 at 09:18
  • @IMSoP: "Nobody is suggesting you write" --- I was suggested here: http://stackoverflow.com/questions/24446970/what-is-the-difference-between-keeping-column-on-left-of-in-sql/24447003?noredirect=1#comment37829786_24447003 "so that you cannot accidentally put it in the SET clause" --- guys, are you kidding or you are really can accept that you can make such a mistake? Honestly? In my whole 10+ years career I have never made such a mistake. Have you? – zerkms Jun 27 '14 at 09:23
  • I don't know how realistic the situation is; to recap: "**in theory** you could ..." This is just one **theoretical** situation where it could be useful. Not I nor anyone else is seriously advocating doing this in practice. But I hope we can at least move past the "deliberately write incorrect query" argument because no-one has ever suggested that. – JJJ Jun 27 '14 at 09:29
  • In theory you could write `TRUNCATE TABLE tbl` instead of `SELECT * FROM tbl` – zerkms Jun 27 '14 at 09:30
  • @zerkms That was not a suggestion to write the invalid SQL. It was an example of invalid SQL that would result if you made a mistake and had used this defensive practice, the `2 = baz` having been *accidentally* transposed from the `WHERE` clause, where it would have been valid, to the `SET` clause, where it is not. And no, I don't think that mistake is likely, but every time you mention deliberately writing an invalid query, you are punching a straw man. – IMSoP Jun 27 '14 at 09:45
  • @IMSoP: "I don't think that mistake is likely" --- if so - what this protection is against of? Against a mistake that no any developer would do? :-) Probably the most useless "best practice" I've ever seen so far. – zerkms Jun 27 '14 at 10:09
  • @zerkms See my answer, which gives a different theory as to why someone might standardise on this. – IMSoP Jun 27 '14 at 11:54
  • @IMSoP: I see some (still not meaningful enough for me personally) reasons to do that for programming languages. For sql there is no reason for that at all. The "coding standards" explanation looks weak: the standard must emphasize the important things and must make usage of a tool easier. And definitely a coding standard must take into account specifics of a particular language. I personally think it's a *stupid* idea to have a single conding standard for everything instead of having a standard per language. – zerkms Jun 27 '14 at 12:01
  • @zerkms I'm not sure why you're so defensive about this. If the question is "why might someone do this?", here are some answers. If the question was "should I do this?", your opinions would be relevant, but off-topic for this site. – IMSoP Jun 27 '14 at 12:02
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/56418/discussion-between-zerkms-and-imsop). – zerkms Jun 27 '14 at 12:04
  • @zerkms Sorry, I have more important things to do. Let's just settle for the fact that you think this practice is indefensible, but others evidently disagree. Bear in mind that the person actually advocating this practice is not here to defend themselves - we don't even know who they are. – IMSoP Jun 27 '14 at 12:06
  • @IMSoP: I will copy it here then: "If the question is "why might someone do this?"". The thing that is not. So you guys were answering some other question that haven't been even asked. The original question is - "what is the difference". And the answer is "there is no difference at all". And it's funny that someone didn't like the correct answer :-D – zerkms Jun 27 '14 at 12:08
0

It's psychology. You would want to read someone else's code out laud and say:

Where my column equals 2.

When you read:

Where 2 equals my column

you have to stop for a while, return, explain it to yourself.

We maintain all of these rules that seem rubish at first glance just to make other people lives easier.

jjaskulowski
  • 2,524
  • 3
  • 26
  • 36
  • This is true, but actually leads to the *opposite* decision from the one asked about. – IMSoP Jun 27 '14 at 11:56
  • @IMSoP: and that just proves my point of view: the `column = constant` is a natural way of writing it :-D People do that unconsciously. Hence a standard must follow that, to make life of developers easier. – zerkms Jun 27 '14 at 12:02
  • @zerkms It disagrees with your answer that "there is no difference at all", however. – IMSoP Jun 27 '14 at 12:11
  • @IMSoP: it's not. For `WHERE` clause there is no any difference at all. You can use either and **NOTHING** will change. The original question is not about readability, but about how `postgresql` behaves. And it was not me who started discussing yoda-style comparisons – zerkms Jun 27 '14 at 12:13
  • @zerkms Then perhaps your question should read "there is no difference **in functionality**. – IMSoP Jun 27 '14 at 12:16
  • @IMSoP: no, it shouldn't. It should be written exactly as it currently is. If the question was about readability, then it would be closed with the "primarily based-opinion" reason. Don't overcomplicate simple things (as you tend to do with pointless coding standards) – zerkms Jun 27 '14 at 12:20
  • 2
    @zerkms For the last time, **I do not advocate this coding standard**. – IMSoP Jun 27 '14 at 13:08