Simple mysql query with "AND" does not work, but each side or variations do

Question

Having a hard time with this seemingly VERY simple mysql query:

This does not work (valid, but returns no rows):

select * from events where type = 'Disposition' AND startDT > '2012-01-01'

All of the following queries DO work off the same data:

select * from events where type = 'Eval' AND startDT > '2012-01-01'    
-- (change the value to Eval it works!)

select * from events where type = 'Disposition' AND addedDT > '2012-01-01' 
-- (search a different date field it works!)

select * from events where type = 'Disposition'  
-- (get rid of the AND it works!)

select * from events where startDT > '2012-01-01'   
-- (again, no AND it works!)

I have tried variations on the bad query, that also do NOT work:

select * from events where `type` = 'Disposition' AND `startDT` > '2012-01-01'
select * from events where `type` = 'Disposition' AND `startDT` > '2012-01-01 00:00:00'
select events.* from events where `type` = 'Disposition' 
    AND `startDT` > '2012-01-01 00:00:00'

(also tried variations on the date, lower case disposition)

I did not have an index on these fields, so I added an index on startDT, then type+startDT (addedDT has one). NO DIFFERENCE.

startDT and addedDT are DATETIME fields. type is varchar 30

Any ideas?

-- If this helps, here is the explain output:

BAD ONE:

explain select * FROM `events` WHERE `startDT` > '2012-01-11 00:00:01' AND `type` = "Disposition" limit 1000
"id"    "select_type"   "table" "type"  "possible_keys" "key"   "key_len"   "ref"   "rows"  "Extra"
"1" "SIMPLE"    "events"    "range" "startDT,type"  "type"  "40"    NULL    "1" "Using where"

RIGHT SIDE:

explain select * FROM `events` WHERE `type` = "Disposition" limit 1000
"id"    "select_type"   "table" "type"  "possible_keys" "key"   "key_len"   "ref"   "rows"  "Extra"
"1" "SIMPLE"    "events"    "ref"   "type"  "type"  "32"    "const" "13760" "Using where"

LEFT SIDE:

explain select * FROM `events` WHERE `startDT` > '2012-01-11 00:00:01' limit 1000
"id"    "select_type"   "table" "type"  "possible_keys" "key"   "key_len"   "ref"   "rows"  "Extra"
"1" "SIMPLE"    "events"    "range" "startDT"   "startDT"   "8" NULL    "13760" "Using where"

SCHEMA:

CREATE TABLE `events` (
    `id` VARCHAR(80) NOT NULL,
    `pk` INT(11) NOT NULL AUTO_INCREMENT,
    `addedDT` DATETIME NOT NULL,
    `type` VARCHAR(30) NOT NULL,
    `startDT` DATETIME NOT NULL,
    PRIMARY KEY (`pk`),
    INDEX `addedDT` (`addedDT`),
    INDEX `startDT` (`startDT`),
    INDEX `type` (`type`, `startDT`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=29407;

SAMPLE ROWS:

INSERT INTO `events` (`id`, `pk`, `addedDT`, `type`, `startDT`) VALUES ('FBF8CE96-AE86-36C6-3C27-E2AAD042476B', 29406, '2012-12-28 12:59:03', 'Disposition', '2012-12-28 12:59:00');
INSERT INTO `events` (`id`, `pk`, `addedDT`, `type`, `startDT`) VALUES ('12577DB6-9B22-FA73-89BD-E2A5C1764B6D', 29405, '2012-12-28 12:53:32', 'Disposition', '2012-12-28 12:53:00');

It is not fetching any rows because it doesn't have any row with `type='Disposition'` — Sashi Kant, Jan 20 '13 at 17:06
Sorry for not formatting (i get votes down for that?? that makes it not a valid question??). Sashi - please re-read, the query for type='Disposition' *IS* working, 8440 rows returned. — Scott Szretter, Jan 20 '13 at 17:08
@ScottSzretter Could you give us the schema of the table, and an (existing) sample row that isn't found and should be? Preferably as an SQLfiddle. — Joachim Isaksson, Jan 20 '13 at 17:13
Sorry for the silly question, but have you verified that there are in fact rows that match both of those criteria at once? You haven't said so in your question. — JLRishe, Jan 20 '13 at 17:16
@ScottSzretter could you provide us with a schema from your table? It could really help, perhaps your are using the wrong data type in your query. — Mr. Radical, Jan 20 '13 at 17:20
YES, I demonstrate above that both sides of they query work on their own - the date or the string query. I just added the schema and two sample rows from the data that should be found using that query. These rows ARE found if I query just 'Disposition' or > '2012-01-01' — Scott Szretter, Jan 20 '13 at 17:22
For the heck of it, I restarted all my services, and it's working now!! ?? Not sure how to explain why that fixed it. — Scott Szretter, Jan 20 '13 at 17:33
@ScottSzretter Does it work when you add DATE to the where clause or does it work with your original query? — Mr. Radical, Jan 20 '13 at 17:57
@ScottSzretter great that you solved your question. Very strange that a reset helped. — Mr. Radical, Jan 24 '13 at 12:35

score 0 · Accepted Answer · answered Jan 20 '13 at 17:11

0

column startDT is of date type in your table, try comparing date as-

select * from events where `type` = 'Disposition' AND `startDT` > DATE('2012-01-01');

answered Jan 20 '13 at 17:11

sourcecode

1,802
2
15
17

Good idea, but no, it returned 0 rows. Just to clarify, select count(*) from events where `startDT` > DATE('2012-01-01'); returns 15572 and select count(*) from events where `type` = 'Disposition' returns 8440 rows. (you can see in my original post 2 sample rows that contain both Disposition and a 2012 date. – Scott Szretter Jan 20 '13 at 17:30
you had checked data in your table for the given constraints?? because this syntax is working fine for me. – sourcecode Jan 21 '13 at 07:30

score 0 · Answer 2 · edited May 23 '17 at 12:21

0

Add date to where clause

WHERE `startDT` >  DATE('2012-01-01')

OR

WHERE DATE(`startDT`) >  ('2012-01-01')

http://www.sqlfiddle.com/#!2/4c879/7

A similar post can be found here How to select date from datetime column?

edited May 23 '17 at 12:21

Community

1
1

answered Jan 20 '13 at 17:28

Mr. Radical

1,847
1
19
29

Simple mysql query with "AND" does not work, but each side or variations do

2 Answers2