User variable in MySQL subquery

Question

Query Output:
> 

SELECT
  @foo := 1,
  @foo,
  (SELECT @foo),
  (SELECT foo FROM (SELECT @foo AS foo) subselect)

+ -------------- + --------- + ------------------ + ----------------------------------------------------- +
| @foo := 1      | @foo      | (SELECT @foo)      | (SELECT foo FROM (SELECT @foo AS foo) subselect)      |
+ -------------- + --------- + ------------------ + ----------------------------------------------------- +
| 1              | 1         | 1                  | 0                                                     |
+ -------------- + --------- + ------------------ + ----------------------------------------------------- +
1 rows

Well... I just want to know why fourth column value is 0 instead 1.

Ben English · Accepted Answer · 2011-12-16 16:01:40Z

3

Because this FROM (SELECT @foo AS foo) is evaluated before this @foo := 1. Basically anything in FROM will be evaluated before your SELECT.

It actually should be null but Im guessing you've assigned your session variable a value of zero somewhere else.

answered Dec 16, 2011 at 16:01

Ben English

3,9382 gold badges26 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

LF Bittencourt Over a year ago

Ben, you are right. Zero is the original value of my session variable. Anyway, is there no way to retrieve the value (1)?

Ben English Over a year ago

At the time you are retrieving it, it has not yet been assigned a value of 1. You can assign the value in the FROM clause and it will be assigned first. SELECT @foo, (SELECT subselect.foo FROM (SELECT @foo := 1) AS init, (SELECT @foo AS foo) subselect)

Ben English Over a year ago

FROM (SELECT @foo AS foo) subselect will always take place prior to your select assignment ` SELECT @foo := 1. You could do SELECT (SELECT @foo FROM (SELECT @foo := 1) a), (SELECT foo FROM (SELECT @foo AS foo) subselect)` The earlier FROM clause will assign the value to @foo prior to your subselect.

Collectives™ on Stack Overflow

User variable in MySQL subquery

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related