Dear SQL wizards,
I'm having a little problem with sub queries under mysql and I'd like
to know if you can help me out.
The example is streight out from the ref manual:
http://www.doctaur.com/dtdocs/databa...anual_437.html
The following is what I get when I run that simple query:
$ cat test.sql
DROP DATABASE test;
CREATE DATABASE test;
connect test;
CREATE TABLE t1 (s1 INT);
INSERT INTO t1 VALUES (1);
CREATE TABLE t2 (s1 INT);
INSERT INTO t2 VALUES (2);
SELECT * FROM t1;
SELECT * FROM t2;
SELECT (SELECT s1 FROM t2) FROM t1;
$ /usr/local/mysql/bin/mysql -t -u root -p < test.sql
Enter password:
+------+
| s1 |
+------+
| 1 |
+------+
+------+
| s1 |
+------+
| 2 |
+------+
ERROR 1064 at line 14: You have an error in your SQL syntax. Check
the manual that corresponds to your MySQL server version for the right
syntax to use near 'SELECT s1 FROM t2) FROM t1' at line 1
Now some details on my box:
mysql> status
--------------
/usr/local/mysql/bin/mysql Ver 12.22 Distrib 4.0.18, for pc-linux
(i686)
Connection id: 229
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Server version: 4.0.18-log
Protocol version: 10
Connection: Localhost via UNIX socket
Client characterset: latin1
Server characterset: latin1
UNIX socket: /tmp/mysql.sock
Uptime: 1 day 24 min 21 sec
Threads: 1 Questions: 5389 Slow queries: 0 Opens: 109 Flush
tables: 1 Open tables: 30 Queries per second avg: 0.061
Note that you are running in safe_update_mode:
UPDATEs and DELETEs that don't use a key in the WHERE clause are not
allowed.
(One can force an UPDATE/DELETE by adding LIMIT # at the end of the
command.)
SELECT has an automatic 'LIMIT 1000' if LIMIT is not used.
Max number of examined row combination in a join is set to: 1000000
--------------
PIII 900 Mhz / 512 RAM / Slack 9.1 / Kernel (vanilla) 2.6.4
How it mysql was compiled... if I can trust my notes:
# CFLAGS="-O3 -mcpu=pentiumpro " CXX=gcc CXXFLAGS="-O3
-mcpu=pentiumpro -felide-constructors -fno-exceptions -fno-rtti"
../configure --prefix=/usr/local/mysql --enable-assembler
--enable-safe-thread-client --with-mysql-user=mysql --with-openssl
--with-unix-socket-path=/tmp/mysql.sock
# make
# make install
Many thanks for your help
Ed.
