BUG: in SQL between expression

am 19.12.2002 12:38:51 von Ocke Janssen

Hi all,

How-to-repeat :

when executing a statement like

select id from table1 where ( id not between 2 and 3)

the result is different from

select id from table1 where ( not id between 2 and 3)

But that should be the case.

I'm using MySQL version 3.23.48-Max-log under Linux

Best regards,

Ocke

--
dba.openoffice.org
Example isn't another way to teach,
it is the only way to teach.
Albert Einstein

------------------------------------------------------------ ---------
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-thread13326@lists.mysql.com
To unsubscribe, e-mail

Re: BUG: in SQL between expression

am 19.12.2002 12:59:28 von fschmidt

Hi,

Am 19.12.02 schrieb Ocke Janssen:

> select id from table1 where ( id not between 2 and 3)
>
> the result is different from
>
> select id from table1 where ( not id between 2 and 3)
>
> But that should be the case.

Yes, it should be the different. The 2nd statement should produce the
same results as

select id from table1 where not(id) between 2 and 3

which is different from

select id from table1 where not ( id between 2 and 3 )

Ciao,

Fabian.

--
TextTech GmbH
Karl-Heine-Str. 99, 04229 Leipzig
Tel. 0341 / 4912 245

------------------------------------------------------------ ---------
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-thread13327@lists.mysql.com
To unsubscribe, e-mail

Re: BUG: in SQL between expression

am 19.12.2002 13:11:42 von Ocke Janssen

If this should be really true, then Oracle, Access, Adabas D are wrong.
And I can't believe that. :-)

Best regards,

Ocke

Fabian Schmidt wrote:

>Hi,
>
>Am 19.12.02 schrieb Ocke Janssen:
>
>
>
>>select id from table1 where ( id not between 2 and 3)
>>
>>the result is different from
>>
>>select id from table1 where ( not id between 2 and 3)
>>
>>But that should be the case.
>>
>>
>
>Yes, it should be the different. The 2nd statement should produce the
>same results as
>
>select id from table1 where not(id) between 2 and 3
>
>which is different from
>
>select id from table1 where not ( id between 2 and 3 )
>
>
>Ciao,
>
>Fabian.
>
>
>

--
Ocke Janssen Tel: +49 40 23646 661, x66661
Dipl. Inf(FH) Fax: +49 40 23646 550
Star Office GmbH
Sachsenfeld 4 mailto:Ocke.Janssen@sun.com
D-20097 Hamburg http://www.sun.com/staroffice

Example isn't another way to teach,
it is the only way to teach.
Albert Einstein

------------------------------------------------------------ ---------
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-thread13328@lists.mysql.com
To unsubscribe, e-mail

Re: BUG: in SQL between expression

am 19.12.2002 14:56:09 von Sinisa Milivojevic

Ocke Janssen writes:
>
> If this should be really true, then Oracle, Access, Adabas D are wrong.
> And I can't believe that. :-)
>
> Best regards,
>
> Ocke
>
>
>
> --
> Ocke Janssen Tel: +49 40 23646 661, x66661
> Dipl. Inf(FH) Fax: +49 40 23646 550
> Star Office GmbH
> Sachsenfeld 4 mailto:Ocke.Janssen@sun.com
> D-20097 Hamburg http://www.sun.com/staroffice
>

Can you send us the output from the same table from some other RDBMS,
so that we can compare ???

Because results should be truly different.

--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / 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-thread13330@lists.mysql.com
To unsubscribe, e-mail

Re: BUG: in SQL between expression

am 19.12.2002 14:59:10 von Ocke Janssen

1. Create table a table with on column of type integer, insert 3 values
(1,2,3)
2. Execute the statement
SELECT "id" FROM "Table1" "Table1" WHERE ( ( "id" NOT BETWEEN 2 AND
3 ) )
-> result is one row with content "1"
3. Execute the statement
SELECT "id" FROM "Table1" "Table1" WHERE ( ( NOT "id" BETWEEN 2 AND 3 ) )
-> result is one row with content "1"

The argument that not "id" is equal with not("id") can be true because
"id" isn't a boolean column.

Hope this helps.

Best regards,

Ocke

Sinisa Milivojevic wrote:

>Ocke Janssen writes:
>
>
>>If this should be really true, then Oracle, Access, Adabas D are wrong.
>>And I can't believe that. :-)
>>
>>Best regards,
>>
>>Ocke
>>
>>
>>
>>--
>>Ocke Janssen Tel: +49 40 23646 661, x66661
>>Dipl. Inf(FH) Fax: +49 40 23646 550
>>Star Office GmbH
>>Sachsenfeld 4 mailto:Ocke.Janssen@sun.com
>>D-20097 Hamburg http://www.sun.com/staroffice
>>
>>
>>
>
>Can you send us the output from the same table from some other RDBMS,
>so that we can compare ???
>
>Because results should be truly different.
>
>
>

--
Ocke Janssen Tel: +49 40 23646 661, x66661
Dipl. Inf(FH) Fax: +49 40 23646 550
Star Office GmbH
Sachsenfeld 4 mailto:Ocke.Janssen@sun.com
D-20097 Hamburg http://www.sun.com/staroffice

Example isn't another way to teach,
it is the only way to teach.
Albert Einstein

------------------------------------------------------------ ---------
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-thread13331@lists.mysql.com
To unsubscribe, e-mail

Re: BUG: in SQL between expression

am 19.12.2002 16:00:34 von Sinisa Milivojevic

Re: BUG: in SQL between expression

am 19.12.2002 16:07:57 von Sinisa Milivojevic

Ocke Janssen writes:
>
> 1. Create table a table with on column of type integer, insert 3 values
> (1,2,3)
> 2. Execute the statement
> SELECT "id" FROM "Table1" "Table1" WHERE ( ( "id" NOT BETWEEN 2 AND
> 3 ) )
> -> result is one row with content "1"
> 3. Execute the statement
> SELECT "id" FROM "Table1" "Table1" WHERE ( ( NOT "id" BETWEEN 2 AND 3 ) )
> -> result is one row with content "1"
>
> The argument that not "id" is equal with not("id") can be true because
> "id" isn't a boolean column.
>
> Hope this helps.
>
> Best regards,
>
> Ocke
>
> --
> Ocke Janssen Tel: +49 40 23646 661, x66661
> Dipl. Inf(FH) Fax: +49 40 23646 550
> Star Office GmbH
> Sachsenfeld 4 mailto:Ocke.Janssen@sun.com
> D-20097 Hamburg http://www.sun.com/staroffice
>

I have taken a second look at your example and it strikes me that you
are correct to a certain extent.

And indeed MySQL 4.0 and 4.1 do support what you require. All that you
have to do is use braces in the correct place.

Here it is:

select * from nazivi;
+------+------------+
| BROJ | NAZIV |
+------+------------+
| 1 | jedan |
| 2 | dva |
| 3 | tri |
| 4 | xxxxxxxxxx |
| 5 | a |
| 10 | |
| 11 | |
| 12 | |
| 13 | |
+------+------------+
select * from nazivi where broj between 1 and 10;
+------+------------+
| BROJ | NAZIV |
+------+------------+
| 1 | jedan |
| 2 | dva |
| 3 | tri |
| 4 | xxxxxxxxxx |
| 5 | a |
| 10 | |
+------+------------+
select * from nazivi where broj not between 1 and 10;
+------+-------+
| BROJ | NAZIV |
+------+-------+
| 11 | |
| 12 | |
| 13 | |
+------+-------+
select * from nazivi where not (broj between 1 and 10);
+------+-------+
| BROJ | NAZIV |
+------+-------+
| 11 | |
| 12 | |
| 13 | |
+------+-------+

Braces have to be used due to precedence rules and MySQL here follows
ANSI standard.

--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / 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-thread13333@lists.mysql.com
To unsubscribe, e-mail