mysql> select @@sql_mode;
+--------------------------------------------------------------------------------+
| @@sql_mode                                                                     |
+--------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_ZERO_IN_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.09 sec)

mysql> show create table test_strtodate \G
*************************** 1. row ***************************
       Table: test_strtodate
Create Table: CREATE TABLE `test_strtodate` (
  `col1` decimal(4,0) NOT NULL,
  `col2` date NOT NULL,
  `col3` char(4) NOT NULL,
  `col4` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 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.06 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.27 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.22 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'); 
+------+------------+------+------------+
| 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>  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;