MySQL: Subquery: Warning Message Meaning

Question

I wrote a query to report credit-cards that were due to expire in the year 2016. It runs, but I do receive a warning, and I'd like to know more about why. I assume it's because of the subquery.

WARNING: Incorrect data value: '2016%' for column 'exp_date' at row 1

I only have one value that meets the requirement of 2016, but is the warning appearing because of possible future values that may meet the same condition?

SELECT customer_id as 'Customer'
FROM customers
WHERE credit_card_id = (
        SELECT credit_card_id
        FROM credit_cards
        WHERE exp_date LIKE '2016%'
        LIMIT 1
    );

Credit-Card values:

INSERT INTO credit_cards VALUES 
(1, '0025184796520000', '2016-08-13', 'Sarah', 'Jones', 3351, '2490 Paseo Verde parkway, suite 150', 'San Diego','CA',92124),
(2, '7896541232548526', '2017-09-21', 'Desmond', 'Lowell', 1204, '3201 Kelsey Street, suite 109', 'San Diego','CA',92174),
(3, '1234567890123456', '2018-02-11', 'Mark', 'Jefferson', 1591, '876 Silverado Street, suite 304', 'Henderson','NV',89162),
(4, '4001330852539605', '2017-01-10', 'Jaime', 'Evans', 8879, '924 Shady Pines Circle, suite 120', 'Summerlin','NV',89074);

They aren't lol. It's fake data made for an assignment. If any of it is correct I'll quit everything and try to win the lottery instead. — Sierra, Apr 12 '16 at 22:20
Oh, thank you very much. That's excellent to know...and it looks much better. — Sierra, Apr 12 '16 at 22:23

score 1 · Accepted Answer · edited May 23 '17 at 12:24

1

The problem is with datatypes DATE <> TEXT and implicit conversion from '2016%' toDATE` which has to fail.

You could use (not SARGable):

SELECT customer_id as 'Customer'
FROM customers
WHERE credit_card_id = (
        SELECT credit_card_id
        FROM credit_cards
        WHERE YEAR(exp_date) =  2016
    );

Second improvement is to use JOIN:

SELECT DISTINCT c.customer_id AS `Customer`
FROM customers c
JOIN credit_cards cc
  ON cc.credit_card_id = c.credit_card_id
WHERE YEAR(cc.exp_date) = 2016;

And finaly to make it SARGable you could use:

WHERE cc.exp_date >= '2016-01-01' AND cc.exp_date < '2017-01-01'

edited May 23 '17 at 12:24

Community

1
1

answered Apr 12 '16 at 22:27

Lukasz Szozda

162,964
23
234
275

As much as I'd prefer to use a join, I have to use a subquery somewhere in this forsaken assignment, and this was my escape. But I think I'm going to have to choose something else that just makes more logical sense... But thank you for the solution and the YEAR(exp_date) =... part, that's very helpful for future occasions. – Sierra Apr 12 '16 at 22:29

MySQL: Subquery: Warning Message Meaning

1 Answers1