Bug with user defined variables (all versions)

Hi,

(tested with 3.23, 4.0.9 and 4.1)

how-to-repeat:

mysql> create table a (a int);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into a values (1),(2),(3),(4),(5);
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> set @a:=1;
Query OK, 0 rows affected (0.00 sec)

mysql> select @a:=a from a where @a = 1000;
+-------+
| @a:=a |
+-------+
| 1 |
| 2 |
| 3 |
| 4 |
+-------+
4 rows in set (0.00 sec)

mysql> select @a:=a from a where @a <> 1000;
+-------+
| @a:=a |
+-------+
| 1 |
| 2 |
| 3 |
| 4 |
+-------+
4 rows in set (0.00 sec)

Why does WHERE Clause always return true?!

Regards

Georg

P.S: Another sample, http://www.sc-leinfelden.de/mysql/t186.html works well

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13523@lists.mysql.com
To unsubscribe, e-mail

Re: Bug with user defined variables (all versions)

On Monday 20 January 2003 20:43, Sinisa Milivojevic wrote:
> Paul DuBois writes:

> > It sounded like you were proposing a change to the manual that would
> > indicate @variables are treated as constants everywhere in the query.
>
> No, of course not.
>
> Variables can not be treated as constants in assignments.
>
> Only in comparing expressions.
>
> Hope that this is clear enough.

Hmm.. I don't understand why the sample
http://www.sc-leinfelden.de/mysql/t186.html works...

Anyway this behaviour should be better documented....

Georg

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13536@lists.mysql.com
To unsubscribe, e-mail

Re: Bug with user defined variables (all versions)

Georg Richter writes:
> Hi,
>
> (tested with 3.23, 4.0.9 and 4.1)
>
> how-to-repeat:
>
> mysql> create table a (a int);
> Query OK, 0 rows affected (0.02 sec)
>
> mysql> insert into a values (1),(2),(3),(4),(5);
> Query OK, 5 rows affected (0.00 sec)
> Records: 5 Duplicates: 0 Warnings: 0
>
> mysql> set @a:=1;
> Query OK, 0 rows affected (0.00 sec)
>
> mysql> select @a:=a from a where @a = 1000;
> +-------+
> | @a:=a |
> +-------+
> | 1 |
> | 2 |
> | 3 |
> | 4 |
> +-------+
> 4 rows in set (0.00 sec)
>
> mysql> select @a:=a from a where @a <> 1000;
> +-------+
> | @a:=a |
> +-------+
> | 1 |
> | 2 |
> | 3 |
> | 4 |
> +-------+
> 4 rows in set (0.00 sec)
>
> Why does WHERE Clause always return true?!
>
> Regards
>
> Georg
>
> P.S: Another sample, http://www.sc-leinfelden.de/mysql/t186.html works well

HI!

MySQL will support only constant values for user variables.

That means for any expression in the query MySQL will use user
variables only as a constant in the expression.

Thus the above WHERE expression will be always treated as:

constant equal constant

So it will always resolve to 0 or 1 and will be optimized away.

It is possible that our manual should be updated on this.

--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com

Join MySQL Users Conference and Expo:
http://www.mysql.com/events/uc2003/


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13531@lists.mysql.com
To unsubscribe, e-mail

Re: Bug with user defined variables (all versions)

>Georg Richter writes:
>> Hi,
>>
>> (tested with 3.23, 4.0.9 and 4.1)
>>
>> how-to-repeat:
>>
>> mysql> create table a (a int);
>> Query OK, 0 rows affected (0.02 sec)
>>
>> mysql> insert into a values (1),(2),(3),(4),(5);
>> Query OK, 5 rows affected (0.00 sec)
>> Records: 5 Duplicates: 0 Warnings: 0
>>
>> mysql> set @a:=1;
>> Query OK, 0 rows affected (0.00 sec)
>>
>> mysql> select @a:=a from a where @a = 1000;
>> +-------+
>> | @a:=a |
>> +-------+
>> | 1 |
>> | 2 |
>> | 3 |
>> | 4 |
>> +-------+
>> 4 rows in set (0.00 sec)
>>
>> mysql> select @a:=a from a where @a <> 1000;
>> +-------+
>> | @a:=a |
>> +-------+
>> | 1 |
>> | 2 |
>> | 3 |
>> | 4 |
>> +-------+
>> 4 rows in set (0.00 sec)
>>
>> Why does WHERE Clause always return true?!
>>
>> Regards
>>
>> Georg
>>
>> P.S: Another sample, http://www.sc-leinfelden.de/mysql/t186.html works well
>
>HI!
>
>MySQL will support only constant values for user variables.
>
>That means for any expression in the query MySQL will use user
>variables only as a constant in the expression.

In the WHERE clause, yes...but I thought you could modify the
variable in the column output list (the list of columns following
the SELECT keyword). The variable gets set for every row, which is
useful for doing things like displaying the row number).

>
>Thus the above WHERE expression will be always treated as:
>
>constant equal constant
>
>So it will always resolve to 0 or 1 and will be optimized away.
>
>It is possible that our manual should be updated on this.
>
>--
> __ ___ ___ ____ __
> / |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
>/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
> <___/ www.mysql.com
>
>Join MySQL Users Conference and Expo:
>http://www.mysql.com/events/uc2003/


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13532@lists.mysql.com
To unsubscribe, e-mail

Re: Bug with user defined variables (all versions)

At 21:34 +0200 1/20/03, Sinisa Milivojevic wrote:
>Paul DuBois writes:
>>
>> In the WHERE clause, yes...but I thought you could modify the
>> variable in the column output list (the list of columns following
>> the SELECT keyword). The variable gets set for every row, which is
>> useful for doing things like displaying the row number).
>>
>
>I hope I understood you correctly.
>
>We do set and re-set user variable at each row that is passed.
>
>So, in this case value of @a will be 4, i.e. the last value in the
>result set.

What I meant was that I think Monty (on #dev) was referring to @variables
being treated as constants only in the WHERE clause.

It sounded like you were proposing a change to the manual that would
indicate @variables are treated as constants everywhere in the query.

But they can be modified in the column output list. Example:

mysql> create table t (i int);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t values(3),(5),(7);
Query OK, 3 rows affected (0.05 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> set @num = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> select @num:=@num+1 AS rownum, i FROM t;
+--------+------+
| rownum | i |
+--------+------+
| 1 | 3 |
| 2 | 5 |
| 3 | 7 |
+--------+------+
3 rows in set (0.00 sec)



>
>--
> __ ___ ___ ____ __
> / |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
>/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
> <___/ www.mysql.com
>
>Join MySQL Users Conference and Expo:
>http://www.mysql.com/events/uc2003/


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13534@lists.mysql.com
To unsubscribe, e-mail

Re: Bug with user defined variables (all versions)

Paul DuBois writes:
>
> In the WHERE clause, yes...but I thought you could modify the
> variable in the column output list (the list of columns following
> the SELECT keyword). The variable gets set for every row, which is
> useful for doing things like displaying the row number).
>

I hope I understood you correctly.

We do set and re-set user variable at each row that is passed.

So, in this case value of @a will be 4, i.e. the last value in the
result set.

--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com

Join MySQL Users Conference and Expo:
http://www.mysql.com/events/uc2003/


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13533@lists.mysql.com
To unsubscribe, e-mail

Re: Bug with user defined variables (all versions)

Paul DuBois writes:
> At 21:34 +0200 1/20/03, Sinisa Milivojevic wrote:
>
> What I meant was that I think Monty (on #dev) was referring to @variables
> being treated as constants only in the WHERE clause.
>
> It sounded like you were proposing a change to the manual that would
> indicate @variables are treated as constants everywhere in the query.
>

No, of course not.

Variables can not be treated as constants in assignments.

Only in comparing expressions.

Hope that this is clear enough.

--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com

Join MySQL Users Conference and Expo:
http://www.mysql.com/events/uc2003/


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13535@lists.mysql.com
To unsubscribe, e-mail

Re: Bug with user defined variables (all versions)

Georg Richter writes:
> On Monday 20 January 2003 20:43, Sinisa Milivojevic wrote:
> > Paul DuBois writes:
>
>
> Hmm.. I don't understand why the sample
> http://www.sc-leinfelden.de/mysql/t186.html works...
>
> Anyway this behaviour should be better documented....
>
> Georg


It works, because @search=parent inolves a column which is evaluated
against a constant !!!

Yes, it should be better documented.

--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com

Join MySQL Users Conference and Expo:
http://www.mysql.com/events/uc2003/


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13537@lists.mysql.com
To unsubscribe, e-mail

Re: Bug with user defined variables (all versions)

Hi.

On Mon 2003-01-20 at 21:11:38 +0200, sinisa@mysql.com wrote:
> Georg Richter writes:
[...]
> > mysql> select @a:=a from a where @a = 1000;
> > +-------+
> > | @a:=a |
> > +-------+
> > | 1 |
> > | 2 |
> > | 3 |
> > | 4 |
> > +-------+
> > 4 rows in set (0.00 sec)
> >
> > mysql> select @a:=a from a where @a <> 1000;
> > +-------+
> > | @a:=a |
> > +-------+
> > | 1 |
> > | 2 |
> > | 3 |
> > | 4 |
> > +-------+
> > 4 rows in set (0.00 sec)
> >
> > Why does WHERE Clause always return true?!
[...]
> MySQL will support only constant values for user variables.
>
> That means for any expression in the query MySQL will use user
> variables only as a constant in the expression.
>
> Thus the above WHERE expression will be always treated as:
>
> constant equal constant

Okay, but

1 = 1000

is always false, so the first example should return no rows. Or in
other words: if @a is treated a constant in the WHERE clause above,
why do two where clauses which contract each other return the same
result? (It does not matter that @a is not reset, because 1000 is not
part of the table).

Another example (I do not provide create table, because the table is
irrelevant for the point I want to make) using 3.23.49:

mysql> select @a:=4;
+-------+
| @a:=4 |
+-------+
| 4 |
+-------+
1 row in set (0.01 sec)

mysql> select @a:=gid from config where @a=3 limit 1;
+---------+
| @a:=gid |
+---------+
| 2 |
+---------+
1 row in set (0.00 sec)

As you can see, though @a=3 should be false (if @a is a constant in
the WHERE clause, we get 4=3) should be false, I get some result
set. It works correctly, if @a is not assigned in the SELECT clause:

mysql> select @a:=4;
+-------+
| @a:=4 |
+-------+
| 4 |
+-------+
1 row in set (0.00 sec)

mysql> select gid from config where @a=3 limit 1;
Empty set (0.00 sec)


So either @a is considered constant in the WHERE clause, than my
example shows a bug, or it isn't and your explanation above is not
correct.

Or did I miss something?

Regards,

Benjamin.

--
benjamin-mysql@pflugmann.de

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13538@lists.mysql.com
To unsubscribe, e-mail

Re: Bug with user defined variables (all versions)

Benjamin Pflugmann writes:
> Hi.
>

[skip]

>
> Or did I miss something?
>
> Regards,
>
> Benjamin.
>

Hi!

4.0.10 behaves just fine:


mysql> set @a=1;
Query OK, 0 rows affected (0.00 sec)

mysql> select @a:=broj from nazivi where @a=1;
+----------+
| @a:=broj |
+----------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 10 |
| 11 |
| 12 |
| 13 |
+----------+
9 rows in set (0.31 sec)

mysql> select @a;
+------+
| @a |
+------+
| 13 |
+------+
1 row in set (0.00 sec)

mysql> select @a:=broj from nazivi where @a=1;
Empty set (0.00 sec)


But , you are correct 3.23 does not behave correctly. As 4.0 will soon
become stable, and this is not a critical or crushing bug, I doubt we
shall correct 3.23's behaviour.

--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com

Join MySQL Users Conference and Expo:
http://www.mysql.com/events/uc2003/


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13542@lists.mysql.com
To unsubscribe, e-mail

Re: Bug with user defined variables (all versions)

Hi,

I still doesn't understand this bug.

For example in 4.1 :

SET @a=2;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT a FROM (SELECT 1 a) a WHERE @a=3;
Empty set (0.00 sec)

mysql> SELECT a FROM (SELECT 1 a) a WHERE @a=2;
+---+
| a |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

mysql> SELECT a FROM (SELECT 1 a) a WHERE @a=1;
Empty set (0.00 sec)

=> it works ok

However :

SELECT @a:=2 FROM (SELECT 1 a) a WHERE @a=3;
+-------+
| @a:=2 |
+-------+
| 2 |
+-------+

Should not return any result, AFAIK (since SELECT 1 FROM (SELECT 1 a) a
WHERE @a=3; returns nothing)

Regards,
Jocelyn




----- Original Message -----
From: "Sinisa Milivojevic"
To:
Cc: ;
Sent: Monday, January 20, 2003 8:03 PM
Subject: Re: Bug with user defined variables (all versions)


> Georg Richter writes:
> > On Monday 20 January 2003 20:43, Sinisa Milivojevic wrote:
> > > Paul DuBois writes:
> >
> >
> > Hmm.. I don't understand why the sample
> > http://www.sc-leinfelden.de/mysql/t186.html works...
> >
> > Anyway this behaviour should be better documented....
> >
> > Georg
>
>
> It works, because @search=parent inolves a column which is evaluated
> against a constant !!!
>
> Yes, it should be better documented.
>
> --
> __ ___ ___ ____ __
> / |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
> /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
> <___/ www.mysql.com
>
> Join MySQL Users Conference and Expo:
> http://www.mysql.com/events/uc2003/
>
>
> ------------------------------------------------------------ ---------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail bugs-thread13537@lists.mysql.com
> To unsubscribe, e-mail
>
>


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13549@lists.mysql.com
To unsubscribe, e-mail