mysql> select @@sql_mode;
+---------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                  |
+---------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> create table test_strtodate
    -> (
    ->   col1    decimal(4) not null,
    ->   col2    date not null,
    ->   col3    char(4) not null,
    ->   col4    date default null
    -> );
Query OK, 0 rows affected (0.14 sec)

mysql> insert into test_strtodate values(1000,'2012-01-01','ABCD','2012-02-01'),(1000,'2012-01-02','ABCD','2012-02-02'),(1000,'2012-01-03','UVWX','2012-02-02'),(1001,'2012-01-01','UVWX','2012-02-01');
Query OK, 4 rows affected (0.03 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from test_strtodate;
+------+------------+------+------------+
| col1 | col2       | col3 | col4       |
+------+------------+------+------------+
| 1000 | 2012-01-01 | ABCD | 2012-02-01 |
| 1000 | 2012-01-02 | ABCD | 2012-02-02 |
| 1000 | 2012-01-03 | UVWX | 2012-02-02 |
| 1001 | 2012-01-01 | UVWX | 2012-02-01 |
+------+------------+------+------------+
4 rows in set (0.00 sec)

mysql> select * from test_strtodate where col3='ABCD' and col2=str_to_date('00000000','%Y%m%d'); 
Empty set (0.00 sec)

mysql> select * from test_strtodate where col3='ABCD' and col2>str_to_date('00000000','%Y%m%d'); 
+------+------------+------+------------+
| col1 | col2       | col3 | col4       |
+------+------------+------+------------+
| 1000 | 2012-01-01 | ABCD | 2012-02-01 |
| 1000 | 2012-01-02 | ABCD | 2012-02-02 |
+------+------------+------+------------+
2 rows in set (0.00 sec)

mysql> Alter table test_strtodate add primary key p_key(col1,col2,col3);
Query OK, 0 rows affected (0.23 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from test_strtodate where col3='ABCD' and col2>str_to_date('00000000','%Y%m%d'); 
+------+------------+------+------------+
| col1 | col2       | col3 | col4       |
+------+------------+------+------------+
| 1000 | 2012-01-01 | ABCD | 2012-02-01 |
| 1000 | 2012-01-02 | ABCD | 2012-02-02 |
+------+------------+------+------------+
2 rows in set (0.00 sec)

mysql> Alter table test_strtodate drop primary key;
Query OK, 4 rows affected (0.25 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> Alter table test_strtodate add primary key p_key(col3,col1,col2);
Query OK, 0 rows affected (0.25 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from test_strtodate where col3='ABCD' and col2>str_to_date('00000000','%Y%m%d'); 
Empty set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+---------------------------------------------------------------+
| Level   | Code | Message                                                       |
+---------+------+---------------------------------------------------------------+
| Warning | 1411 | Incorrect datetime value: '00000000' for function str_to_date |
+---------+------+---------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from test_strtodate where col2>str_to_date('00000000','%Y%m%d'); 
+------+------------+------+------------+
| col1 | col2       | col3 | col4       |
+------+------------+------+------------+
| 1000 | 2012-01-01 | ABCD | 2012-02-01 |
| 1000 | 2012-01-02 | ABCD | 2012-02-02 |
| 1000 | 2012-01-03 | UVWX | 2012-02-02 |
| 1001 | 2012-01-01 | UVWX | 2012-02-01 |
+------+------------+------+------------+
4 rows in set (0.00 sec)

mysql> select * from test_strtodate where col2>str_to_date('00000000','%Y%m%d') and col1=1000; 
+------+------------+------+------------+
| col1 | col2       | col3 | col4       |
+------+------------+------+------------+
| 1000 | 2012-01-01 | ABCD | 2012-02-01 |
| 1000 | 2012-01-02 | ABCD | 2012-02-02 |
| 1000 | 2012-01-03 | UVWX | 2012-02-02 |
+------+------------+------+------------+
3 rows in set (0.00 sec)

mysql> notee;