subject:"NULL sometimes joins to NULL"

re: NULL sometimes joins to NULL

2002-10-17 Thread Victoria Reznichenko


jfield,
Thursday, October 10, 2002, 1:33:10 AM, you wrote:

jazdc Description:
jazdc After adding a key to a nullable column, null values will
jazdc successfully join through to null values in other tables.
jazdc This only seems to happen when the index is added after
jazdc the row contains null values.  This affects both MyISAM
jazdc and InnoDB table types and both binary versions 3.23.42
jazdc and 4.0.4.

jazdc How-To-Repeat:
jazdc Run the following queries.  The first select will return 0
jazdc rows, which is correct.  The second select will return a
jazdc match - but all that was changed was the adding of an index.

jazdc   mysql create table foo (id int);
jazdc   Query OK, 0 rows affected (0.01 sec)

jazdc   mysql insert into foo values (null), (0);
jazdc   Query OK, 2 rows affected (0.01 sec)
jazdc   Records: 2  Duplicates: 0  Warnings: 0

jazdc   mysql create table bar (id int);
jazdc   Query OK, 0 rows affected (0.00 sec)

jazdc   mysql insert into bar values (null);
jazdc   Query OK, 1 row affected (0.00 sec)

jazdc   mysql select * from foo, bar where foo.id = bar.id;
jazdc   Empty set (0.00 sec)

jazdc   mysql alter table foo add key id (id);
jazdc   Query OK, 2 rows affected (0.01 sec)
jazdc   Records: 2  Duplicates: 0  Warnings: 0

jazdc   mysql select * from foo, bar where foo.id = bar.id;
jazdc   +--+--+
jazdc   | id   | id   |
jazdc   +--+--+
jazdc   | NULL | NULL |
jazdc   +--+--+
jazdc   1 row in set (0.00 sec)

jazdc   mysql

Hi!

Thank you for bug report!

This bug is already fixed in latest 4.0 BK tree.


-- 
For technical support contracts, goto https://order.mysql.com/?ref=ma02-010c
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Victoria Reznichenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   ___/   www.mysql.com





-
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 [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

re: NULL sometimes joins to NULL

2002-10-12 Thread Victoria Reznichenko


jfield,
Thursday, October 10, 2002, 1:33:10 AM, you wrote:

jazdc After adding a key to a nullable column, null values will
jazdc successfully join through to null values in other tables.
jazdc This only seems to happen when the index is added after
jazdc the row contains null values.  This affects both MyISAM
jazdc and InnoDB table types and both binary versions 3.23.42
jazdc and 4.0.4.

[skip]

jazdc   mysql select * from foo, bar where foo.id = bar.id;
jazdc   +--+--+
jazdc   | id   | id   |
jazdc   +--+--+
jazdc   | NULL | NULL |
jazdc   +--+--+
jazdc   1 row in set (0.00 sec)


Thanks for bug report!


-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Victoria Reznichenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   ___/   www.mysql.com





-
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 [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: NULL sometimes joins to NULL

2002-10-10 Thread Jan Steinman


From: [EMAIL PROTECTED]

After adding a key to a nullable column, null values will
successfully join through to null values in other tables.

And this is a problem because? :-)

Seriously, it may be a bug, but IMHO it is a design flaw to index a NULL field. 
Indexed fields should always be NOT NULL, with the possible exception of FULLTEXT 
indeces.

But if one DOES have NULLs in an indexed field, I'd fully expect them to match and 
join other NULLs.

 SQL SQL SQL SQL SQL SQL SQL SQL SQL SQL 

-- 
: Jan Steinman -- nature photography: http://www.Bytesmiths.com
: Bytesmiths -- artists' services: http://www.Bytesmiths.com/Services
: Join the forums at http://www.Bytesmiths.com/wiki


-
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 [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: NULL sometimes joins to NULL

2002-10-10 Thread Jianliang Zhao

I am not convinced that index on nullable field is a design flaw. This is not the 
behavior in the major databases. To match and join two nulls, IS NULL should be 
expected to be used instead of a.col1 = b.col2.

-Original Message-
From: Jan Steinman [mailto:[EMAIL PROTECTED]]
Sent: Thursday, October 10, 2002 9:45 AM
To: [EMAIL PROTECTED]
Subject: Re: NULL sometimes joins to NULL

From: [EMAIL PROTECTED]

After adding a key to a nullable column, null values will
successfully join through to null values in other tables.

And this is a problem because? :-)

Seriously, it may be a bug, but IMHO it is a design flaw to index a NULL field. 
Indexed fields should always be NOT NULL, with the possible exception of FULLTEXT 
indeces.

But if one DOES have NULLs in an indexed field, I'd fully expect them to match and 
join other NULLs.

 SQL SQL SQL SQL SQL SQL SQL SQL SQL SQL 

-- 
: Jan Steinman -- nature photography: http://www.Bytesmiths.com
: Bytesmiths -- artists' services: http://www.Bytesmiths.com/Services
: Join the forums at http://www.Bytesmiths.com/wiki

-
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 [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

-
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 [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: NULL sometimes joins to NULL

2002-10-10 Thread Artem Koltsov

It looks like a bug. I was able to repeat it and I had different results for the same 
join depending on when index was added and values of actual data in tables.

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, October 09, 2002 6:33 PM
To: [EMAIL PROTECTED]
Subject: NULL sometimes joins to NULL

Description:
After adding a key to a nullable column, null values will
successfully join through to null values in other tables.
This only seems to happen when the index is added after
the row contains null values.  This affects both MyISAM
and InnoDB table types and both binary versions 3.23.42
and 4.0.4.

How-To-Repeat:
Run the following queries.  The first select will return 0
rows, which is correct.  The second select will return a
match - but all that was changed was the adding of an index.

  mysql create table foo (id int);
  Query OK, 0 rows affected (0.01 sec)

  mysql insert into foo values (null), (0);
  Query OK, 2 rows affected (0.01 sec)
  Records: 2  Duplicates: 0  Warnings: 0

  mysql create table bar (id int);
  Query OK, 0 rows affected (0.00 sec)

  mysql insert into bar values (null);
  Query OK, 1 row affected (0.00 sec)

  mysql select * from foo, bar where foo.id = bar.id;
  Empty set (0.00 sec)

  mysql alter table foo add key id (id);
  Query OK, 2 rows affected (0.01 sec)
  Records: 2  Duplicates: 0  Warnings: 0

  mysql select * from foo, bar where foo.id = bar.id;
  +--+--+
  | id   | id   |
  +--+--+
  | NULL | NULL |
  +--+--+
  1 row in set (0.00 sec)

  mysql

Fix:

Submitter-Id:  submitter ID
Originator:
Organization:

MySQL support: none
Synopsis:  adding key makes null columns join
Severity:  
Priority:  
Category:  mysql
Class: 
Release:   mysql-4.0.4-beta (Source distribution)

Environment:

System: Linux db2 2.4.18-64GB-SMP #1 SMP Wed Mar 27 13:58:12 UTC 2002 i686 unknown
Architecture: i686

Some paths:  /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i486-suse-linux/2.95.3/specs
gcc version 2.95.3 20010315 (SuSE)
Compilation info: CC='gcc'  CFLAGS='-O6 -mpentiumpro -fomit-frame-pointer'  CXX='gcc'  
CXXFLAGS='-O6 -mpentiumpro -fomit-frame-pointer -felide-constructors -fno-exceptions 
-fno-rtti'  LDFLAGS=''
LIBC: 
-rwxr-xr-x1 root root  1394238 Jul 12 08:29 /lib/libc.so.6
-rw-r--r--1 root root 25361424 Jul 12 06:58 /usr/lib/libc.a
-rw-r--r--1 root root  178 Mar 23  2002 /usr/lib/libc.so
Configure command: ./configure --prefix=/opt/mysql --enable-assembler 
--with-mysqld-ldflags=-all-static --disable-shared -with-extra-charsets=none 
'CFLAGS=-O6 -mpentiumpro -fomit-frame-pointer' 'CXXFLAGS=-O6 -mpentiumpro 
-fomit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti' CXX=gcc

-
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 [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Attention:
Any views expressed in this message are those of the individual sender, except where 
the message states otherwise and the sender is authorized to state them to be the 
views of any such entity.

The information contained in this message and or attachments is intended only for the 
person or entity to which it is addressed and may contain confidential and/or 
privileged material. Any review, retransmission, dissemination or other use of, or 
taking of any action in reliance upon, this information by persons or entities other 
than the intended recipient is prohibited. If you received this in error, please 
contact the sender and delete the material from any system and destroy any copies.

-
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 [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

NULL sometimes joins to NULL

2002-10-09 Thread jfield


Description:
After adding a key to a nullable column, null values will
successfully join through to null values in other tables.
This only seems to happen when the index is added after
the row contains null values.  This affects both MyISAM
and InnoDB table types and both binary versions 3.23.42
and 4.0.4.

How-To-Repeat:
Run the following queries.  The first select will return 0
rows, which is correct.  The second select will return a
match - but all that was changed was the adding of an index.

  mysql create table foo (id int);
  Query OK, 0 rows affected (0.01 sec)

  mysql insert into foo values (null), (0);
  Query OK, 2 rows affected (0.01 sec)
  Records: 2  Duplicates: 0  Warnings: 0

  mysql create table bar (id int);
  Query OK, 0 rows affected (0.00 sec)

  mysql insert into bar values (null);
  Query OK, 1 row affected (0.00 sec)

  mysql select * from foo, bar where foo.id = bar.id;
  Empty set (0.00 sec)

  mysql alter table foo add key id (id);
  Query OK, 2 rows affected (0.01 sec)
  Records: 2  Duplicates: 0  Warnings: 0

  mysql select * from foo, bar where foo.id = bar.id;
  +--+--+
  | id   | id   |
  +--+--+
  | NULL | NULL |
  +--+--+
  1 row in set (0.00 sec)

  mysql

Fix:


Submitter-Id:  submitter ID
Originator:
Organization:
 
MySQL support: none
Synopsis:  adding key makes null columns join
Severity:  
Priority:  
Category:  mysql
Class: 
Release:   mysql-4.0.4-beta (Source distribution)

Environment:

System: Linux db2 2.4.18-64GB-SMP #1 SMP Wed Mar 27 13:58:12 UTC 2002 i686 unknown
Architecture: i686

Some paths:  /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i486-suse-linux/2.95.3/specs
gcc version 2.95.3 20010315 (SuSE)
Compilation info: CC='gcc'  CFLAGS='-O6 -mpentiumpro -fomit-frame-pointer'  CXX='gcc'  
CXXFLAGS='-O6 -mpentiumpro -fomit-frame-pointer -felide-constructors -fno-exceptions 
-fno-rtti'  LDFLAGS=''
LIBC: 
-rwxr-xr-x1 root root  1394238 Jul 12 08:29 /lib/libc.so.6
-rw-r--r--1 root root 25361424 Jul 12 06:58 /usr/lib/libc.a
-rw-r--r--1 root root  178 Mar 23  2002 /usr/lib/libc.so
Configure command: ./configure --prefix=/opt/mysql --enable-assembler 
--with-mysqld-ldflags=-all-static --disable-shared -with-extra-charsets=none 
'CFLAGS=-O6 -mpentiumpro -fomit-frame-pointer' 'CXXFLAGS=-O6 -mpentiumpro 
-fomit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti' CXX=gcc


-
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 [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

re: NULL sometimes joins to NULL

re: NULL sometimes joins to NULL

Re: NULL sometimes joins to NULL

RE: NULL sometimes joins to NULL

RE: NULL sometimes joins to NULL

NULL sometimes joins to NULL

6 matches

Site Navigation

Mail list logo

Footer information