>Description:
3 "select" with little difference (2 with strange result, and last correct)
======================================================================
select
Schedule.PTRScheme as Scheme_ID,
Subject.TID as Subject_TID,
Scheme.PTRSubject as Subject_ID,
if(count(distinct Lesson.ID),1,0) as cancelF
from Root
left join Lesson on Lesson.PTRFlow in (43,44)
left join Schedule on Schedule.ID=Lesson.PTRSchedule
left join Scheme on Scheme.ID=Schedule.PTRScheme
left join Subject on Subject.ID=Scheme.PTRSubject
where Status=1
group by Scheme_ID
order by Subject_TID;
+-----------+-------------+------------+---------+
| Scheme_ID | Subject_TID | Subject_ID | cancelF |
+-----------+-------------+------------+---------+
| 412 | NULL | 542 | 1 |
| NULL | Subject290 | 290 | 1 |
| NULL | Subject802 | 802 | 1 |
| NULL | Subject825 | 825 | 1 |
+-----------+-------------+------------+---------+
4 rows in set (0.00 sec)
=======================================================================
select
Schedule.PTRScheme as Scheme_ID,
Subject.TID as Subject_TID,
Scheme.PTRSubject as Subject_ID,
if(count(distinct 1),1,0) as cancelF
from Root
left join Lesson on Lesson.PTRFlow in (43,44)
left join Schedule on Schedule.ID=Lesson.PTRSchedule
left join Scheme on Scheme.ID=Schedule.PTRScheme
left join Subject on Subject.ID=Scheme.PTRSubject
where Status=1
group by Scheme_ID
order by Subject_TID;
+-----------+-------------+------------+---------+
| Scheme_ID | Subject_TID | Subject_ID | cancelF |
+-----------+-------------+------------+---------+
| NULL | Subject290 | 290 | 1 |
| NULL | Subject542 | 542 | 1 |
| NULL | Subject802 | 802 | 1 |
| NULL | Subject825 | 825 | 1 |
+-----------+-------------+------------+---------+
4 rows in set (0.00 sec)
=========================================================================
select
Schedule.PTRScheme as Scheme_ID,
Subject.TID as Subject_TID,
Scheme.PTRSubject as Subject_ID,
if(count(Lesson.ID),1,0) as cancelF
from Root
left join Lesson on Lesson.PTRFlow in (43,44)
left join Schedule on Schedule.ID=Lesson.PTRSchedule
left join Scheme on Scheme.ID=Schedule.PTRScheme
left join Subject on Subject.ID=Scheme.PTRSubject
where Status=1
group by Scheme_ID
order by Subject_TID;
+-----------+-------------+------------+---------+
| Scheme_ID | Subject_TID | Subject_ID | cancelF |
+-----------+-------------+------------+---------+
| 485 | Subject290 | 290 | 1 |
| 412 | Subject542 | 542 | 1 |
| 413 | Subject802 | 802 | 1 |
| 411 | Subject825 | 825 | 1 |
+-----------+-------------+------------+---------+
4 rows in set (0.00 sec)
>How-To-Repeat:
DataBaze with problem...
http://andrew.ort.spb.ru/unix/mysql/db.tgz (3k)
>Fix:
if remove "destinct","Order by" or change order of fields
problem solved.
>Submitter-Id: <submitter ID>
>Originator: Andrew Shirayev
>Organization:
>MySQL support: none
>Synopsis: Grange NULL in result
>Severity: serious
>Priority: low
>Category: mysql
>Class: sw-bug
>Release: mysql-3.23.49 (Source distribution)
>Server: /usr/local/bin/mysqladmin Ver 8.23 Distrib 3.23.49, 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.49
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /tmp/mysql.sock
Uptime: 24 min 57 sec
Threads: 1 Questions: 66 Slow queries: 0 Opens: 18 Flush tables: 1 Open tables: 8
Queries per second avg: 0.044
>Environment:
<2xPentiumIII , linux 2.2, libc-2.2.2>
System: Linux ipschool 2.2.18 #2 SMP Sun Apr 15 03:13:23 MSD 2001 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/i386-slackware-linux/egcs-2.91.66/specs
gcc version egcs-2.91.66 19990314/Linux (egcs-1.1.2 release)
Compilation info: CC='gcc' CFLAGS='' CXX='c++' CXXFLAGS='' LDFLAGS=''
LIBC:
lrwxrwxrwx 1 root root 13 Apr 3 2001 /lib/libc.so.6 -> libc-2.2.2.so
-rwxr-xr-x 1 root root 1008844 Sep 10 1999 /lib/libc-2.1.2.so
-rwxr-xr-x 1 root root 4746015 Feb 19 2001 /lib/libc-2.2.2.so
-rw-r--r-- 1 root root 24336626 Feb 19 2001 /usr/lib/libc.a
-rw-r--r-- 1 root root 178 Feb 19 2001 /usr/lib/libc.so
Configure command: ./configure --with-charset=koi8_ru --localstatedir=/var/spool/mysql
Perl: This is perl, version 5.005_03 built for i386-linux
---------------------------------------------------------------------
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
