Strange query behavior
Whilst trying to figure out why one of my queries wasn't working I ran into the following phenomenon which I'd be grateful if someone could explain. (I'm running mySQL 4.0.17 on a win2003 server, querying using the 0.9.4 mysql control center) The following query returns the expected result SELECT distinct(PO.error_type) AS error_type, COUNT(PO.error_type) AS occurrence FROM problematic_odds AS PO WHERE NOT (PO.error_type = 100) GROUP BY PO.error_type it returns 1008 ,212 for error_type and occurrence respectively. When I change the query to SELECT distinct(PO.error_type) AS error_type, COUNT(PO.error_type) AS occurrence FROM problematic_odds AS PO, active_odds AS AO WHERE NOT (PO.error_type = 100) GROUP BY PO.error_type the query executes BUT doesn't return anything at all. I can't quite figure out why. The only difference between the two queries is that the second one has active_odds AS AO added to the FROM clause. Even though I don't use AO at all in the query the results still differ. Anyone have an explanation ? Having a superflous table in the WHERE clause shouldn't affect the query outcome should it ? //Linus Nikander - [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange query behavior
Linus Nikander wrote: Whilst trying to figure out why one of my queries wasn't working I ran into the following phenomenon which I'd be grateful if someone could explain. (I'm running mySQL 4.0.17 on a win2003 server, querying using the 0.9.4 mysql control center) The following query returns the expected result SELECT distinct(PO.error_type) AS error_type, COUNT(PO.error_type) AS occurrence FROM problematic_odds AS PO WHERE NOT (PO.error_type = 100) GROUP BY PO.error_type it returns 1008 ,212 for error_type and occurrence respectively. When I change the query to SELECT distinct(PO.error_type) AS error_type, COUNT(PO.error_type) AS occurrence FROM problematic_odds AS PO, active_odds AS AO This cartesian product will be empty if active_odds is empty. WHERE NOT (PO.error_type = 100) GROUP BY PO.error_type the query executes BUT doesn't return anything at all. I can't quite figure out why. The only difference between the two queries is that the second one has active_odds AS AO added to the FROM clause. Even though I don't use AO at all in the query the results still differ. Anyone have an explanation ? Having a superflous table in the WHERE clause shouldn't affect the query outcome should it ? //Linus Nikander - [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: very strange query behavior
select count(*) from members where country_id = 1 and read_array is NULL ; Daren Cotter wrote: can anybody explain this to me? mysql select count(*) from members where country_id = 1 and read_array not like '%20270%'; +--+ | count(*) | +--+ |34884 | +--+ mysql select count(*) from members where country_id = 1 and read_array like '%20270%'; +--+ | count(*) | +--+ |13554 | +--+ mysql select count(*) from members where country_id = 1; +--+ | count(*) | +--+ |63546 | +--+ 34884 + 13554 63546 This is a very important aspect of our business, can anyone offer any insight as to what may be wrong? TIA, Sincerely, Daren Cotter CEO, InboxDollars.com [EMAIL PROTECTED] http://www.inboxdollars.com (507) 382-0435 - 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 -- Gerald L. Clark [EMAIL PROTECTED] - 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
very strange query behavior
can anybody explain this to me? mysql select count(*) from members where country_id = 1 and read_array not like '%20270%'; +--+ | count(*) | +--+ |34884 | +--+ mysql select count(*) from members where country_id = 1 and read_array like '%20270%'; +--+ | count(*) | +--+ |13554 | +--+ mysql select count(*) from members where country_id = 1; +--+ | count(*) | +--+ |63546 | +--+ 34884 + 13554 63546 This is a very important aspect of our business, can anyone offer any insight as to what may be wrong? TIA, Sincerely, Daren Cotter CEO, InboxDollars.com [EMAIL PROTECTED] http://www.inboxdollars.com (507) 382-0435 - 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
RE: very strange query behavior
I would have to guess that you have a broken index. bill -Original Message- From: Daren Cotter [mailto:[EMAIL PROTECTED]] Sent: Monday, September 24, 2001 3:50 PM To: [EMAIL PROTECTED] Subject: very strange query behavior can anybody explain this to me? mysql select count(*) from members where country_id = 1 and read_array not like '%20270%'; +--+ | count(*) | +--+ |34884 | +--+ mysql select count(*) from members where country_id = 1 and read_array like '%20270%'; +--+ | count(*) | +--+ |13554 | +--+ mysql select count(*) from members where country_id = 1; +--+ | count(*) | +--+ |63546 | +--+ 34884 + 13554 63546 This is a very important aspect of our business, can anyone offer any insight as to what may be wrong? TIA, Sincerely, Daren Cotter CEO, InboxDollars.com [EMAIL PROTECTED] http://www.inboxdollars.com (507) 382-0435 - 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 - 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
