Thomas,
Thursday, September 12, 2002, 9:58:00 AM, you wrote:
TW> I've a very dirty bug costing me hours uptill now; maybe it is related
TW> with the AND/OR-behavior described in the releasenote of 3.23.52/53;
TW> A query once runs with release 3.23.53 (my own PC); same query don't
TW> work with release 3.23.51 (my Host-Provider).
TW> Here is the Query:
TW> ----
TW> SELECT * FROM tb_category cat1
TW> LEFT JOIN tb_category cat2 ON
TW> cat1.parent = cat2.id
TW> WHERE cat2.id IS NULL
TW> OR cat1.parent = 0
TW> ORDER BY cat1.name;
TW> ----
TW> The actual right result is a set of rows, where at least cat1.parent >=0
and cat1.id >=0;
TW> I need all rows, who don't have an parent (parent=0) **or** a given
TW> parent doesn't exist (cat2.id is null).
TW> The actual false result is a set of rows, where **all** columns are
TW> NULL.
TW> So, this query works fine with 3.23.53, also woked fine in the past with
TW> older realse of MySQL.
TW> Now, it don't work since weeks at my old Webspaceprovider and at my
TW> current one. I took the action to change the provider (now i've to pay
TW> for!) to get a solution. The new provider has release 3.23.51.
TW> I can't belive that this is config-related.
TW> Please, would you be so kind and check some possible bugs??
I checked your example on 3.23.51 and 3.23.52 and got the same result
on both versions:
+----+------+--------+---------+---------------------+------------+------+------+--------+---------+---------+------------+
| id | name | parent | updated | created | nameauthor | id | name |
|parent | updated | created | nameauthor |
+----+------+--------+---------+---------------------+------------+------+------+--------+---------+---------+------------+
| 1 | News | 0 | NULL | 2002-09-11 23:42:27 | god | NULL | NULL |
|NULL | NULL | NULL | NULL |
+----+------+--------+---------+---------------------+------------+------+------+--------+---------+---------+------------+
1 row in set (0.05 sec)
and it's a correct result.
mysql> SELECT * FROM tb_category cat1
-> LEFT JOIN tb_category cat2 ON
-> cat1.parent = cat2.id;
+----+--------+--------+---------+---------------------+------------+------+------+--------+---------+---------------------+------------+
| id | name | parent | updated | created | nameauthor | id | name |
|parent | updated | created | nameauthor |
+----+--------+--------+---------+---------------------+------------+------+------+--------+---------+---------------------+------------+
| 1 | News | 0 | NULL | 2002-09-11 23:42:27 | god | NULL | NULL |
|NULL | NULL | NULL | NULL |
| 3 | das | 1 | NULL | 2002-09-12 01:19:08 | god | 1 | News |
| 0 | NULL | 2002-09-11 23:42:27 | god |
| 4 | sausua | 1 | NULL | 2002-09-12 02:05:21 | god | 1 | News |
| 0 | NULL | 2002-09-11 23:42:27 | god |
+----+--------+--------+---------+---------------------+------------+------+------+--------+---------+---------------------+------------+
3 rows in set (0.00 sec)
In this result set there is only one row where cat2.id is NULL or cat1.parent=0
So, what is exectly wrong with LEFT JOIN and NULL?
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Egor Egorov
/ /|_/ / // /\ \/ /_/ / /__ [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
