Re: [SQL] Text->Date conversion in a WHERE clause

[cadiolis]

As an example:

CREATE TABLE userdata (
    userdata_id serial NOT NULL,
    user_id smallint,
    data text
);

CREATE TABLE users (
    user_id serial NOT NULL,
    name text,
    "type" smallint
);

INSERT INTO userdata (userdata_id, user_id, data) VALUES (1, 1,
'2005-01-01');
INSERT INTO userdata (userdata_id, user_id, data) VALUES (2, 2,
'2005-10-10');
INSERT INTO userdata (userdata_id, user_id, data) VALUES (3, 3,
'052-44-5863');
INSERT INTO userdata (userdata_id, user_id, data) VALUES (4, 4,
'052-44-5863');

INSERT INTO users (user_id, name, "type") VALUES (1, 'Jim', 1);
INSERT INTO users (user_id, name, "type") VALUES (2, 'John', 1);
INSERT INTO users (user_id, name, "type") VALUES (3, 'Bob', 2);
INSERT INTO users (user_id, name, "type") VALUES (4, 'Bill', 2);


Then run the query:
SELECT *
FROM
(
        SELECT u.user_id, ud.data
        FROM users u, userdata ud
        WHERE u.user_id = ud.user_id
        AND u.type = 1
) subusers
WHERE subusers.data::text::date <  now();

Returns the message: ERROR:  date/time field value out of range:
"052-44-5863"

So my question is how does this query ever even SEE the row containing
"052-44-5863"?  The sub-query doesn't return that row so I don't see
how it can get this error.

Regards,
Collin Peters


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match