I am having problems with mySQL in windows. I have a query that when ran
causes the server to die. I have selected to debug the program and it tells
me of an unhandled exception wiring to a file. The specifics I can provide
on more detail if it's actually a bug.
I have the following query generated by a PHP script that causes problems:
SELECT statistics.*
FROM profiles, answers, statistics
WHERE 1=1 AND profiles.sport = 1
AND answers.profile IN(
SELECT profiles.id
FROM profiles, answers AS a0, answers AS a1
WHERE 1=1 AND profiles.sport = 1 AND a0.profile = profiles.id
AND a0.statistic = 4 AND a0.data + 0 >= 2
AND a1.profile = profiles.id
AND a1.statistic = 8
AND a1.data REGEXP 'left'
) AND answers.statistic = statistics.id
GROUP BY statistics.id;
I have ran the select statment in the "IN" and it returns one row with id =
6. I have replaced the select in the "IN" with the number 6 and it works
fine.
To see if it was a problem with the subselect I have simplified the query
to:
SELECT statistics.*
FROM profiles, answers, statistics
WHERE 1=1 AND profiles.sport = 1
AND answers.profile IN(
SELECT profiles.id
FROM profiles
) AND answers.statistic = statistics.id
GROUP BY statistics.id;
and it returns the propoer data. I can work around the issues by creating a
monster SQL statment of somewhat equivalance like:
SELECT statistics.*
FROM profiles, answers, statistics, answers AS a0, answers AS a1
WHERE 1=1 AND profiles.sport = 1
AND profiles.sport = 1 AND a0.profile = profiles.id
AND a0.statistic = 4 AND a0.data + 0 >= 2
AND a1.profile = profiles.id
AND a1.statistic = 8
AND a1.data REGEXP 'left'
AND answers.statistic = statistics.id
GROUP BY statistics.id;
but find the first IS(SELECT) a little easier to follow. Please let me know
if I have a SQL syntax flaw in the original SQL query, or if I have stumbled
upon a true bug. I feel the SQL statment above is valid (even noting
redundencies) through executing the two select sepratly and combining the
results as mySQL should.
Please feel free to respond to me directly (through my email) relating to
this issue.
Thanks,
Shawn Zernik
Internetwork Consutling
www.internetworkconsulting.net
--- Sample Output ---
mysql> use hsss;
Database changed
mysql> SELECT statistics.*
-> FROM profiles, answers, statistics
-> WHERE 1=1 AND profiles.sport = 1
-> AND answers.profile IN(
-> SELECT profiles.id
-> FROM profiles, answers AS a0, answers AS a1
-> WHERE 1=1 AND profiles.sport = 1 AND a0.profile =
profiles.id
-> AND a0.statistic = 4 AND a0.data + 0 >= 2
-> AND a1.profile = profiles.id
-> AND a1.statistic = 8
-> AND a1.data REGEXP 'left'
-> ) AND answers.statistic = statistics.id
-> GROUP BY statistics.id;
ERROR 2006: MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 1
Current database: hsss
ERROR 2013: Lost connection to MySQL server during query
mysql> use hsss;
Database changed
mysql> SELECT statistics.*
-> FROM profiles, answers, statistics, answers AS a0, answers AS a1
-> WHERE 1=1 AND profiles.sport = 1
-> AND profiles.sport = 1 AND a0.profile = profiles.id
-> AND a0.statistic = 4 AND a0.data + 0 >= 2
-> AND a1.profile = profiles.id
-> AND a1.statistic = 8
-> AND a1.data REGEXP 'left'
-> AND answers.statistic = statistics.id
-> GROUP BY statistics.id;
ERROR 2006: MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 1
Current database: hsss
+----+-------+-------------+--------+
| id | sport | caption | units |
+----+-------+-------------+--------+
| 3 | 1 | Sticks | number |
| 4 | 1 | Blades | number |
| 5 | 2 | Balls | number |
| 7 | 2 | Shoe Size | number |
| 8 | 1 | Position | text |
| 9 | 1 | Avalibility | |
| 10 | 2 | Position | text |
| 11 | 2 | Avalible | |
+----+-------+-------------+--------+
8 rows in set (0.22 sec)
mysql> SELECT statistics.*
-> FROM profiles, answers, statistics
-> WHERE 1=1 AND profiles.sport = 1
-> AND answers.profile IN(
-> SELECT profiles.id
-> FROM profiles
-> ) AND answers.statistic = statistics.id
-> GROUP BY statistics.id;
+----+-------+-------------+--------+
| id | sport | caption | units |
+----+-------+-------------+--------+
| 3 | 1 | Sticks | number |
| 4 | 1 | Blades | number |
| 5 | 2 | Balls | number |
| 7 | 2 | Shoe Size | number |
| 8 | 1 | Position | text |
| 9 | 1 | Avalibility | |
| 10 | 2 | Position | text |
| 11 | 2 | Avalible | |
+----+-------+-------------+--------+
8 rows in set (0.02 sec)
mysql>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
