Setting variable in FROM clause causes JOIN error if implicitly declared after table

Question

Here is an oddity I cannot explain:

FROM (
   SELECT @report_date := 
     IF (DAYOFWEEK(CURDATE()) BETWEEN 3 AND 7, DATE_SUB(CURDATE(), INTERVAL 1 DAY), 
       IF (DAYOFWEEK(CURDATE()) = 1, DATE_SUB(CURDATE(), INTERVAL 2 DAY),
         IF (DAYOFWEEK(CURDATE()) = 2, DATE_SUB(CURDATE(), INTERVAL 3 DAY), NULL)))
) AS dt
 , t1
LEFT JOIN t2 ON t1.id = t2.id

http://sqlfiddle.com/#!2/9e0c4/3

Works.

FROM t1
 , (
   SELECT @report_date := 
     IF (DAYOFWEEK(CURDATE()) BETWEEN 3 AND 7, DATE_SUB(CURDATE(), INTERVAL 1 DAY), 
       IF (DAYOFWEEK(CURDATE()) = 1, DATE_SUB(CURDATE(), INTERVAL 2 DAY),
         IF (DAYOFWEEK(CURDATE()) = 2, DATE_SUB(CURDATE(), INTERVAL 3 DAY), NULL)))
) AS dt
LEFT JOIN t2 ON t1.id = t2.id

http://sqlfiddle.com/#!2/9e0c4/2

Doesn't work, error:

Unknown column 't1.id' in 'on clause': ...

Why? Note the order of where t1 is declared in the FROM clause.

Note, I'm not necessarily looking for alternatives, I would simply like to understand why the order here causes an error on a JOIN that I would have thought would occur following when the FROM clause is completed.

If someone were interested in how I ended up here, see this broken fiddle and this working fiddle for a basic example of the real query.

Note, a very similar question: http://stackoverflow.com/a/761599/451969 — Jared Farrish, Jul 28 '13 at 16:33

score 3 · Accepted Answer · edited Jul 28 '13 at 16:53

3

It's because you are mixing ANSI and NON-ANSI join. Instead of using comma, replace it with CROSS JOIN.

SQLFiddle Demo

The issue is precedence, whereas the JOIN has higher precedence than the implicit ,:

Previously, the comma operator (,) and JOIN both had the same precedence, so the join expression t1, t2 JOIN t3 was interpreted as ((t1, t2) JOIN t3). Now JOIN has higher precedence, so the expression is interpreted as (t1, (t2 JOIN t3)). This change affects statements that use an ON clause, because that clause can refer only to columns in the operands of the join, and the change in precedence changes interpretation of what those operands are.

http://dev.mysql.com/doc/refman/5.0/en/join.html

Use CROSS JOIN to replace the implicit JOIN:

FROM t1
CROSS JOIN (
   SELECT @report_date := 
     IF (DAYOFWEEK(CURDATE()) BETWEEN 3 AND 7, DATE_SUB(CURDATE(), INTERVAL 1 DAY), 
       IF (DAYOFWEEK(CURDATE()) = 1, DATE_SUB(CURDATE(), INTERVAL 2 DAY),
         IF (DAYOFWEEK(CURDATE()) = 2, DATE_SUB(CURDATE(), INTERVAL 3 DAY), NULL)))
) AS dt
LEFT JOIN t2 ON t1.id = t2.id

http://sqlfiddle.com/#!2/9e0c4/21

edited Jul 28 '13 at 16:53

Jared Farrish

48,585
17
95
104

answered Jul 28 '13 at 16:09

John Woo

258,903
69
498
492

This solves my underlying problem (thank you for that), but what does the first sentence mean in practice? Why does it work if I flip them so the latter comes second? I just don't see why this would cause an error... Feel free to confuse me with details, if possible. – Jared Farrish Jul 28 '13 at 16:13
`1.` [ANSI vs. non-ANSI SQL JOIN syntax](http://stackoverflow.com/questions/1599050/ansi-vs-non-ansi-sql-join-syntax) `2.` It breaks because the scope of the `ON` condition is limited only within the comma. When you flip it up, it does not break because you have not specified any condition on `dt`. It breaks when you define a condition like this one: [Click Here.](http://sqlfiddle.com/#!2/9e0c4/30) – John Woo Jul 28 '13 at 16:25
1

Ok, that makes sense (as well as [Bill Karwin's answer here](http://stackoverflow.com/a/761599/451969), which I just unearthed). If you can edit that explanation into the answer, I will accept it. – Jared Farrish Jul 28 '13 at 16:28
@JaredFarrish yeah i'm finding that too `:D` – John Woo Jul 28 '13 at 16:29
Well, I suppose I should have found this before posting the question. Visit [the MySQL `JOIN` docs page](http://dev.mysql.com/doc/refman/5.0/en/join.html) and scroll down to the item beginning in *Previously, the comma operator (,)...* In all honestly, there lies the answer. Not sure if I should delete the question or add as an answer (I generally don't like pure manual quotes as answers). – Jared Farrish Jul 28 '13 at 16:43
you can simply delete it if you are not satisfied with the answers `:)` – John Woo Jul 28 '13 at 16:45
If the ANSI folks were looking to put a bullet in the head of `,` syntax, this would be a decent way to do so. Thanks again. – Jared Farrish Jul 28 '13 at 16:55

score 0 · Answer 2 · answered Jul 28 '13 at 16:17

0

you can also use this without left join. with separate all tables by comma

-fiddle here

EDIT:

either use only joins or use only separated comma tables but not mixed.

answered Jul 28 '13 at 16:17

echo_Me

37,078
5
58
78

I'm not interested in the removing the `JOIN`(`JOIN`s in the real code's case). This doesn't really explain why it breaks, either. – Jared Farrish Jul 28 '13 at 16:18
it breaks because it mixed between sperated tables by comma and using joins – echo_Me Jul 28 '13 at 16:19

Setting variable in FROM clause causes JOIN error if implicitly declared after table

2 Answers2