Re: Strange error in DELETE query

2004-11-11 Thread Michael Stassen 
Ronan Lucio wrote:

The problem is that if I use OR in the where clause, MySQL won't
use the indexes in the row_id column.
Yes, it will, as long as the OR conditions are on the *same* column.
  WHERE row_id IN (2,5,7)
and
  WHERE (row_id = 2 OR row_id = 5 OR row_id = 7)
are equivalent.  I prefer IN because it is easier to type and read, but 
mysql should treat them the same.

If you tried this with OR and mysql did not use the index, my guess is that 
you forgot the parentheses:

  SELECT * FROM table
  WHERE client_id = 1
  AND row_id = 2 OR row_id = 5 OR row_id = 7;
AND has higher precedence than OR, so mysql would see that as
  WHERE (client_id = 1 AND row_id = 2)
OR row_id = 5 OR row_id = 7;
which won't use an index and isn't what you meant.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Strange error in DELETE query

2004-11-11 Thread Paul DuBois 
At 8:18 -0600 11/11/04, Jay Blanchard wrote:
[snip]
When I run the follow query:
DELETE
FROM table
WHERE client_id = 1
AND row_id IN (2,5,7)
only the first record is deleted.
Am I doing something wrong or is it a MySQL bug?
[/snip]
It is not a bug, just say it out loud
"AND row_id is 2 OR 5 OR 7"
Once the OR condition is satisfied once, the query will halt.
Why would it do that?  DELETE doesn't execute just until it succeeds
in deleting one row. (Not without a LIMIT clause, at least.)
Anyway, here's my test:
DROP TABLE IF EXISTS t;
CREATE TABLE t (client_id INT, row_id INT);
INSERT INTO t VALUES(1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,7),(1,8);
SELECT * FROM t;
DELETE
FROM t
WHERE client_id = 1
AND row_id IN (2,5,7);
SELECT * FROM t;
Result:
+---++
| client_id | row_id |
+---++
| 1 |  1 |
| 1 |  2 |
| 1 |  3 |
| 1 |  4 |
| 1 |  5 |
| 1 |  6 |
| 1 |  7 |
| 1 |  8 |
+---++
+---++
| client_id | row_id |
+---++
| 1 |  1 |
| 1 |  3 |
| 1 |  4 |
| 1 |  6 |
| 1 |  8 |
+---++
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Strange error in DELETE query

2004-11-11 Thread Ronan Lucio 
Michael,

> What are you talikng about?  Queries don't halt on the first row matched.
> For example:

It´s my thought, too. But it isn´t happen in my MySQL Server.

Now, doing the same tests you did I got the same results of you.
Well, I´ll inspect my code again looking for some error that I
didn´t see, yet.

thanks,
Ronan



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Strange error in DELETE query

2004-11-11 Thread Ronan Lucio 
Jay,

> It is not a bug, just say it out loud
> "AND row_id is 2 OR 5 OR 7"
>
> Once the OR condition is satisfied once, the query will halt. 

The problem is that if I use OR in the where clause, MySQL wont
use the indexes in the row_id column.

One important thing that I forgot to say is I run a SELECT with
the same where clause:

SELECT *
FROM table
WHERE client_id = 1
AND row_id IN (2,5,7)

and it returns me three rows,

thanks,
Ronan



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Strange error in DELETE query

2004-11-11 Thread Michael Stassen 

Jay Blanchard wrote:
[snip]
When I run the follow query:
DELETE
FROM table
WHERE client_id = 1
AND row_id IN (2,5,7)
only the first record is deleted.
Am I doing something wrong or is it a MySQL bug?
[/snip]
It is not a bug, just say it out loud
"AND row_id is 2 OR 5 OR 7"
Once the OR condition is satisfied once, the query will halt. 
What are you talikng about?  Queries don't halt on the first row matched. 
For example:

  mysql> SELECT * FROM t;
  +---++
  | client_id | row_id |
  +---++
  | 1 |  1 |
  | 1 |  2 |
  | 1 |  3 |
  | 1 |  4 |
  | 1 |  5 |
  | 1 |  6 |
  | 1 |  7 |
  | 2 |  1 |
  | 2 |  2 |
  | 2 |  3 |
  | 2 |  4 |
  | 2 |  5 |
  | 2 |  6 |
  | 2 |  7 |
  +---++
  14 rows in set (0.00 sec)
  mysql> SELECT * FROM t
  -> WHERE client_id = 1
  -> AND row_id IN (2,5,7);
  +---++
  | client_id | row_id |
  +---++
  | 1 |  2 |
  | 1 |  5 |
  | 1 |  7 |
  +---++
  3 rows in set (0.01 sec)
  mysql> DELETE FROM t
  -> WHERE client_id = 1
  -> AND row_id IN (2,5,7);
  Query OK, 3 rows affected (0.00 sec)
  mysql> SELECT * FROM t
  -> WHERE client_id = 1
  -> AND row_id IN (2,5,7);
  Empty set (0.00 sec)
The question is: what do you (Ronan) mean by "only the first record is 
deleted"?  If you run

  SELECT * FROM table
  WHERE client_id = 1
  AND row_id IN (2,5,7)
how many rows do you get?
Michael

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Strange error in DELETE query

2004-11-11 Thread Jay Blanchard 
[snip]
When I run the follow query:

DELETE
FROM table
WHERE client_id = 1
AND row_id IN (2,5,7)

only the first record is deleted.
Am I doing something wrong or is it a MySQL bug?
[/snip]

It is not a bug, just say it out loud
"AND row_id is 2 OR 5 OR 7"

Once the OR condition is satisfied once, the query will halt. 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]