| Bug #41389 | sub-SELECT and ORDER BY gets NULL | ||
|---|---|---|---|
| Submitted: | 11 Dec 2008 8:56 | Modified: | 11 Dec 2008 9:06 |
| Reporter: | Stefan Jann | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: Parser | Severity: | S3 (Non-critical) |
| Version: | 4.1.13-Max | OS: | Linux (Suse 10.0) |
| Assigned to: | CPU Architecture: | Any | |
[11 Dec 2008 9:06]
Valeriy Kravchuk
I can't repeat this problem with 4.1.22:
C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3306 test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 4.1.22-community-nt-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
Query OK, 0 rows affected (0.09 sec)
mysql>
mysql> CREATE TABLE IF NOT EXISTS `forum` (
-> `ident` bigint(20) unsigned NOT NULL auto_increment,
-> `zuforum` bigint(20) unsigned NOT NULL default '0',
-> `vonuser` bigint(20) unsigned NOT NULL default '0',
-> `titel` varchar(255) collate utf8_bin NOT NULL default '',
-> `beitrag` text collate utf8_bin NOT NULL,
-> `erstellt` datetime NOT NULL default '0000-00-00 00:00:00',
-> PRIMARY KEY (`ident`),
-> KEY `erstellt` (`erstellt`)
-> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=11 ;
Query OK, 0 rows affected (0.34 sec)
mysql>
mysql> INSERT INTO `forum` (`ident`, `zuforum`, `vonuser`, `titel`, `beitrag`, `
erstellt`)
-> VALUES
-> (1, 0, 1, 'Hallo erstmal...','unwichtig', '2008-12-09 11:58:28'),
-> (2, 0, 1, 'Fehlende Daten der Klassenkameraden','unwichtig', '2008-12-10
11:18:17'),
-> (3, 1, 11, 'Bin auch da!','unwichtig', '2008-12-10 11:36:15'),
-> (4, 0, 9, 'Bilder (Klassenfahrten/Klassenfotos/etc.)','unwichtig', '2008-
12-10
'> 12:29:44'),
-> (5, 1, 9, 'Guten Tag','unwichtig', '2008-12-10 12:31:50'),
-> (6, 1, 9, 'Servus','unwichtig', '2008-12-10 12:33:53'),
-> (7, 4, 1, 'Bald...','unwichtig', '2008-12-10 14:03:34'),
-> (8, 1, 2, 'Servus auch','unwichtig', '2008-12-10 14:21:35'),
-> (9, 1, 1, 'So nach und nach...','unwichtig', '2008-12-10 15:08:27'),
-> (10, 1, 18, 'Hellas!','unwichtig', '2008-12-10 21:30:27');
Query OK, 10 rows affected (0.03 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql>
mysql> SELECT f.ident, f.zuforum, f.vonuser, f.titel, DATE_FORMAT(f.erstellt,'%d
.%m.%Y - %H:%i')
-> AS erstellt,
-> (SELECT COUNT(*) FROM forum o WHERE o.zuforum=f.ident LIMIT 1) AS
antworten,
-> (SELECT p.vonuser FROM forum p WHERE p.zuforum=f.ident ORDER BY p.
erstellt DESC
-> LIMIT 1) AS aw1,
-> (SELECT q.vonuser FROM forum q WHERE q.zuforum=f.ident LIMIT 1) AS
aw2
-> FROM forum f
-> WHERE (f.zuforum=0)
-> ORDER BY f.erstellt DESC
-> LIMIT 0, 25;
+-------+---------+---------+-------------------------------------------+-------
-------------+-----------+------+------+
| ident | zuforum | vonuser | titel | erstel
lt | antworten | aw1 | aw2 |
+-------+---------+---------+-------------------------------------------+-------
-------------+-----------+------+------+
| 4 | 0 | 9 | Bilder (Klassenfahrten/Klassenfotos/etc.) | 10.12.
2008 - 12:29 | 1 | 1 | 1 |
| 2 | 0 | 1 | Fehlende Daten der Klassenkameraden | 10.12.
2008 - 11:18 | 0 | NULL | NULL |
| 1 | 0 | 1 | Hallo erstmal... | 09.12.
2008 - 11:58 | 6 | 18 | 11 |
+-------+---------+---------+-------------------------------------------+-------
-------------+-----------+------+------+
3 rows in set (0.03 sec)
Note "18" above. Please, upgrade.
Description: I got a query for a forum. This query should show me alle entrys and the newest answer. Under MySQL 4.1.13-Max the query don't show any answer if I sort the answers with order by to get the newest. Under MySQL 5.0.32 it shows me right. How to repeat: Heres a samll sump to repeat the problem: SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; CREATE TABLE IF NOT EXISTS `forum` ( `ident` bigint(20) unsigned NOT NULL auto_increment, `zuforum` bigint(20) unsigned NOT NULL default '0', `vonuser` bigint(20) unsigned NOT NULL default '0', `titel` varchar(255) collate utf8_bin NOT NULL default '', `beitrag` text collate utf8_bin NOT NULL, `erstellt` datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY (`ident`), KEY `erstellt` (`erstellt`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=11 ; INSERT INTO `forum` (`ident`, `zuforum`, `vonuser`, `titel`, `beitrag`, `erstellt`) VALUES (1, 0, 1, 'Hallo erstmal...','unwichtig', '2008-12-09 11:58:28'), (2, 0, 1, 'Fehlende Daten der Klassenkameraden','unwichtig', '2008-12-10 11:18:17'), (3, 1, 11, 'Bin auch da!','unwichtig', '2008-12-10 11:36:15'), (4, 0, 9, 'Bilder (Klassenfahrten/Klassenfotos/etc.)','unwichtig', '2008-12-10 12:29:44'), (5, 1, 9, 'Guten Tag','unwichtig', '2008-12-10 12:31:50'), (6, 1, 9, 'Servus','unwichtig', '2008-12-10 12:33:53'), (7, 4, 1, 'Bald...','unwichtig', '2008-12-10 14:03:34'), (8, 1, 2, 'Servus auch','unwichtig', '2008-12-10 14:21:35'), (9, 1, 1, 'So nach und nach...','unwichtig', '2008-12-10 15:08:27'), (10, 1, 18, 'Hellas!','unwichtig', '2008-12-10 21:30:27'); SELECT f.ident, f.zuforum, f.vonuser, f.titel, DATE_FORMAT(f.erstellt,'%d.%m.%Y - %H:%i') AS erstellt, (SELECT COUNT(*) FROM forum o WHERE o.zuforum=f.ident LIMIT 1) AS antworten, (SELECT p.vonuser FROM forum p WHERE p.zuforum=f.ident ORDER BY p.erstellt DESC LIMIT 1) AS aw1, (SELECT q.vonuser FROM forum q WHERE q.zuforum=f.ident LIMIT 1) AS aw2 FROM forum f WHERE (f.zuforum=0) ORDER BY f.erstellt DESC LIMIT 0, 25; Server Version: 4.1.13-Max MySQL-Client-Version: 4.1.13 ident | zuforum | vonuser | titel | erstellt | antworten | aw1 | aw2 4 | 0 | 9 | Bilder (Klassenfahrten/Klassenfotos/etc.) | 10.12.2008 - 12:29 | 1 | 1 | 1 2 | 0 | 1 | Fehlende Daten der Klassenkameraden | 10.12.2008 - 11:18 | 0 | NULL | NULL 1 | 0 | 1 | Hallo erstmal... | 09.12.2008 - 11:58 | 6 | NULL | 11 Server Version: 5.0.32-Debian_7etch8-log MySQL-Client-Version: 5.0.32 ident | zuforum | vonuser | titel | erstellt | antworten | aw1 | aw2 4 | 0 | 9 | Bilder (Klassenfahrten/Klassenfotos/etc.) | 10.12.2008 - 12:29 | 1 | 1 | 1 2 | 0 | 1 | Fehlende Daten der Klassenkameraden | 10.12.2008 - 11:18 | 0 | NULL | NULL 1 | 0 | 1 | Hallo erstmal... | 09.12.2008 - 11:58 | 6 | 18 | 11 You'll see at the third line there is under 4.1.13 a "NULL" in aw1 and under 5.0.32 theres a "18". The "18" is the right. I can't explain why under 4.1.13 theres a NULL, cause there are 6 answers an without ORDER BY there is a "11".