Nulls and unique indexes.
Description: If a null column is used in a unique index then mysql fails to throw a key violation when multiple identical inserts (with nulls) are performed. How-To-Repeat: create table test (a int null, b int null); alter table test add unique index index_ab (a,b); insert into test (a,b) values(1,null); insert into test (a,b) values(1,null); insert into test (a,b) values(1,null); select * from test; +--+--+ | a| b| +--+--+ |1 | NULL | |1 | NULL | |1 | NULL | +--+--+ 3 rows in set (0.00 sec) insert into test (a,b) values(1,1); insert into test (a,b) values(1,1); ERROR 1062: Duplicate entry '1-1' for key 1 Fix: No fix. Submitter-Id: submitter ID Originator:Jon Barker Organization: MySQL support: none Synopsis: NULLs don't work correctly in unique indexes. Severity: serious Priority: Category: mysql Class: Release: mysql-3.23.41 (Official MySQL RPM) Environment: System: Linux x.catchword.co.uk 2.4.18ptixfs #11 Thu May 2 14:00:21 GMT 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/i586-mandrake-linux/2.96/specs gcc version 2.96 2731 (Linux-Mandrake 8.0 2.96-0.48mdk) Compilation info: CC='egcs' CFLAGS='-O6 -fno-omit-frame-pointer -mpentium' CXX='egcs' CXXFLAGS='-O6 -fno-omit-frame-pointer-felide-constructors -fno-exceptions -fno-rtti -mpentium' LDFLAGS='' LIBC: lrwxrwxrwx1 root root 13 Sep 28 2001 /lib/libc.so.6 - libc-2.2.2.so -rwxr-xr-x1 root root 1216268 Feb 21 2001 /lib/libc-2.2.2.so -rw-r--r--1 root root 26366908 Feb 21 2001 /usr/lib/libc.a -rw-r--r--1 root root 178 Feb 21 2001 /usr/lib/libc.so Configure command: ./configure --disable-shared --with-mysqld-ldflags=-all-static --with-client-ldflags=-all-static --without-berkeley-db --without-innodb --enable-assembler --with-mysqld-user=mysql --with-unix-socket-path=/var/lib/mysql/mysql.sock --prefix=/ --with-extra-charsets=complex --exec-prefix=/usr --libexecdir=/usr/sbin --sysconfdir=/etc --datadir=/usr/share --localstatedir=/var/lib/mysql --infodir=/usr/info --includedir=/usr/include --mandir=/usr/man '--with-comment=Official MySQL RPM' - 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: Nulls and unique indexes.
Doh! Blindsided by an Oracle DBA, the only spec I could get my hands on is an old draft thing and I have to admit from that it looks like the correct behaviour.. NULL NULL and all that.. Thanks, Jon On Tue, 18 Jun 2002 10:54:15 -0500 Gerald Clark [EMAIL PROTECTED] wrote: That is correct. Use NOT NULL in the definition to force unique keys. [EMAIL PROTECTED] wrote: Description: If a null column is used in a unique index then mysql fails to throw a key violation when multiple identical inserts (with nulls) are performed. How-To-Repeat: create table test (a int null, b int null); alter table test add unique index index_ab (a,b); insert into test (a,b) values(1,null); insert into test (a,b) values(1,null); insert into test (a,b) values(1,null); select * from test; +--+--+ | a| b| +--+--+ |1 | NULL | |1 | NULL | |1 | NULL | +--+--+ 3 rows in set (0.00 sec) insert into test (a,b) values(1,1); insert into test (a,b) values(1,1); ERROR 1062: Duplicate entry '1-1' for key 1 Fix: No fix. Submitter-Id: submitter ID Originator: Jon Barker Organization: MySQL support: none Synopsis: NULLs don't work correctly in unique indexes. Severity: serious Priority: Category: mysql Class: Release:mysql-3.23.41 (Official MySQL RPM) Environment: - 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: Load Data Problem
Hi, I'm having a problem when using Load Data where the single and double quotes in the text file are getting nuked. It appears that the escape character is not being respected but I'm not sure why. It is also knocking out characters near the quotes. Your single and double quotes come out strangely in my (linux based) e-mail client as well, I suspect you're mixing the windows quote chars without realising it. Check out:- http://www.bbsinc.com/iso8859.html ..scroll down to the bit about 'Microsoft latin-1 added characters' Jon sql,query - 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: outer joins: tried some sql code myself but doesn't work - should be easy i think ?
I came up with the following sql statement, but I still don't see any records that are missing in table active_monitors, I assume i am abusing the 'join syntax' but I don't know how to fix it: select D.monitor, D.endpoint ,A.monitor from endpoints_defined_monitors D left outer join active_monitors A on A.monitor_id = D.monitor_ID and A.endpoint = D.endpoint and A.sentry_profile = D.sentry_profile Where D.monitor_id is null following the outer join, d.endpoint will also be null hence a.endpoint = d.endpoint evaluates to false. Chuck a few 'or is null's in and away you go. Jon - 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
LEFT OUTER JOIN where not null datetime column is null gives incorrect result.
Description: Left outer join on null datetime column produces incorrect result. The 2nd select below produces no rows, which is incorrect and inconsistent with the fourth select which produces 1 row. I get the same results with server version 3.23.41 - binary version from mysql.com How-To-Repeat: create table foo (a int); insert into foo values(1); insert into foo values(2); create table bar1 (a int, b datetime not null); insert into bar1 values(1, '25-12-2001'); create table bar2 (a int, b datetime); insert into bar2 values(1, '25-12-2001'); create table bar3 (a int, b char(20) not null); insert into bar3 values(1, 'Wensleydale'); select * from foo left outer join bar1 on foo.a=bar1.a; select * from foo left outer join bar1 on foo.a=bar1.a where b is null; select * from foo left outer join bar2 on foo.a=bar2.a where b is null; select * from foo left outer join bar3 on foo.a=bar3.a where b is null; drop table foo; drop table bar1; drop table bar2; drop table bar3; Fix: Convert the datetime column to nullable? Not really a fix since it breaks the schema. Submitter-Id: submitter ID Originator:Jon Barker Organization: Ingenta PLC MySQL support: none Synopsis: LEFT OUTER JOIN where not null datetime column is null fails. Severity: non-critical Priority: low Category: mysql Class: sw-bug Release: mysql-3.23.39 (Official MySQL RPM) Server: /usr/bin/mysqladmin Ver 8.21 Distrib 3.23.39, for pc-linux-gnu on i686 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 3.23.39 Protocol version10 Connection Localhost via UNIX socket UNIX socket /var/lib/mysql/mysql.sock Uptime: 37 days 20 hours 51 min 15 sec Threads: 1 Questions: 1855361 Slow queries: 4 Opens: 279 Flush tables: 3 Open tables: 8 Queries per second avg: 0.567 Environment: System: Linux ninetyseven 2.2.19-7.0.1 #2 SMP Fri Sep 28 18:00:32 GMT 2001 i686 unknown Architecture: i686 Also on Linux Mandrake release 8.0 (Traktopel) for i586 Kernel 2.4.3-20mdk on an i686 (AMD 1 CPU) Some paths: /usr/local/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs gcc version 2.96 2731 (Red Hat Linux 7.1 2.96-85) Compilation info: CC='egcs' CFLAGS='-O6 -fno-omit-frame-pointer -mpentium' CXX='egcs' CXXFLAGS='-O6 -fno-omit-frame-pointer-felide-constructors -fno-exceptions -fno-rtti -mpentium' LDFLAGS='' LIBC: lrwxrwxrwx1 root root 13 Nov 28 13:00 /lib/libc.so.6 - libc-2.2.4.so -rwxr-xr-x1 root root 5724759 Oct 3 21:05 /lib/libc-2.2.4.so -rw-r--r--1 root root 27260368 Oct 3 21:03 /usr/lib/libc.a -rw-r--r--1 root root 178 Oct 3 21:03 /usr/lib/libc.so Configure command: ./configure --disable-shared --with-mysqld-ldflags=-all-static --with-client-ldflags=-all-static --without-berkeley-db --without-innodb --enable-assembler --with-mysqld-user=mysql --with-unix-socket-path=/var/lib/mysql/mysql.sock --prefix=/ --with-extra-charsets=complex --exec-prefix=/usr --libexecdir=/usr/sbin --sysconfdir=/etc --datadir=/usr/share --localstatedir=/var/lib/mysql --infodir=/usr/info --includedir=/usr/include --mandir=/usr/man '--with-comment=Official MySQL RPM' - 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
LEFT JOIN problem.
Hi All, I have a curious problem with a left join:- create table foo (a int); insert into foo values(1); insert into foo values(2); create table bar1 (a int, b datetime not null); insert into bar1 values(1, '25-12-2001'); create table bar2 (a int, b datetime); insert into bar2 values(1, '25-12-2001'); create table bar3 (a int, b char(20) not null); insert into bar3 values(1, 'Wensleydale'); select * from foo left outer join bar1 on foo.a=bar1.a; select * from foo left outer join bar1 on foo.a=bar1.a where b is null; select * from foo left outer join bar2 on foo.a=bar2.a where b is null; select * from foo left outer join bar3 on foo.a=bar3.a where b is null; drop table foo; drop table bar1; drop table bar2; drop table bar3; The selects produce these results:- select * from foo left outer join bar1 on foo.a=bar1.a -- a a b 1 1 2025-12-20 01:00:00 2 NULLNULL -- select * from foo left outer join bar1 on foo.a=bar1.a where b is null -- -- select * from foo left outer join bar2 on foo.a=bar2.a where b is null -- a a b 2 NULLNULL -- select * from foo left outer join bar3 on foo.a=bar3.a where b is null -- a a b 2 NULLNULL -- The problem is that surely the second select should return some rows, is this a bug or am I doing something dumb? I've tried this on mysql Ver 11.15 Distrib 3.23.41, for pc-linux-gnu (i686) (AMD 1 CPU) and mysql Ver 11.15 Distrib 3.23.39, for pc-linux-gnu (i686) (Intel SMP 2 CPU) Thanks for any assistance, Jon Barker - 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
