>Description:
When i want to find the next unused id from a table joining it whith another
table the result is allways 1, even though this is not correct
>How-To-Repeat:
create table a (id int not null primary key);
create table b (id int not null primary key);
insert into a values (1),(2),(3),(4),(5),(6),(7),(8);
insert into b values (1),(2),(3),(5),(6);
select min(a.id)
from a left join b on a.id = b.id
where b.id is null;
+-----------+
| min(a.id) |
+-----------+
| 1 |
+-----------+
>Fix:
The problem can be fixed whith the following select
select min(a.id)
from a left join b on a.id = b.id
where b.id is null
and a.id is not null;
+-----------+
| min(a.id) |
+-----------+
| 4 |
+-----------+
even though the a.id never is null.
>Submitter-Id: <submitter ID>
>Originator:
>Organization:
-----------------------------------------------------
/ \
| Ole Lund Jensen |
| Groenjordsvej 1, st., -1114 |
| DK-2300 Koebenhavn S |
| Tlf.: (+45) 32 87 60 13 |
| e-mail: [EMAIL PROTECTED] |
\ /
-----------------------------------------------------
>
>MySQL support: none
>Synopsis: ??
>Severity: non-critical
>Priority: low
>Category: mysql
>Class: sw-bug
>Release: mysql-3.23.36 (Source distribution)
>Environment:
System: OSF1 gram V4.0 878 alpha
Architecture: alpha
Machine: alpha
Some paths: /usr/local/bin/perl /usr/bin/make /usr/local/bin/gmake /usr/local/bin/gcc
/usr/bin/cc
GCC: Reading specs from /usr/local/lib/gcc-lib/alpha-dec-osf4.0d/2.95.2/specs
gcc version 2.95.2 19991024 (release)
Compilation info: CC='cc -pthread -std1' CFLAGS='' CXX='cxx -pthread -O'
CXXFLAGS='' LDFLAGS=''
LIBC:
lrwxrwxrwx 1 root system 17 Jul 5 1998 /lib/libc.a -> ../ccs/lib/libc.a
lrwxrwxrwx 1 root system 17 Jul 5 1998 /usr/lib/libc.a ->
../ccs/lib/libc.a
Configure command: ./configure '--with-named-thread-libs=-lpthread -lmach -lexc -lc'
--with-low-memory --enable-large-files --prefix=/var/mysql/v3.23.36
--with-mysqld-ldflags=-all-static -disable-shared
Perl: This is perl, version 5.004_04 built for alpha-dec_osf
---------------------------------------------------------------------
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
