Issue 117278 - CAST shows non existing data in query results
Summary: CAST shows non existing data in query results
Status: CONFIRMED
Alias: None
Product: Base
Classification: Application
Component: code (show other issues)
Version: DEV300m101
Hardware: PC Windows NT
: P3 Normal (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2011-03-08 10:56 UTC by r4zoli
Modified: 2013-01-29 21:47 UTC (History)
2 users (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---


Attachments
bugdoc with CAST in query (5.10 KB, application/vnd.sun.xml.base)
2011-03-08 10:58 UTC, r4zoli
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description r4zoli 2011-03-08 10:56:38 UTC
I created table with, ID(integr), timestamps, date, time data types in fields.
I added data into it adding NULL into some fields, it inserted default data into these fields, other fields remains empty.

SELECT CAST( "timestamp" AS DATE ) FROM "Table"
SELECT CAST( "date" AS DATE ) AS "one" FROM "Table"

When these queries run instead of empty fields I get, 1-01-02 date value instead of empty field.

Similar happens with 
SELECT CAST( "timestamp" AS TIME ) FROM "Table"
SELECT CAST( "time" AS TIME ) FROM "Table"

When these queries run instead of empty fields I get, 00:00:00 time value instead of empty field.

If I run hsqldb server and run same queries in database manager I get empty fields when no data in table.

This happens same in DEV300m101, OOo 3.3 and cws dba34d from qa-upload.
Comment 1 r4zoli 2011-03-08 10:58:34 UTC
Created attachment 76049 [details]
bugdoc with CAST in query
Comment 2 Frank Schönheit 2011-04-04 12:47:04 UTC
can confirm.

@oj: sounds a little bit like a duplicate of the HSQL bug we found lately, in another context: HSQL claiming that a column can't be NULL, so we respect that and deliver some non-NULL value.
Comment 3 ocke.janssen 2011-04-05 05:29:57 UTC
@fs: Yes, but not duplicate. It has to be fixed in the database itself. CAST can return NULL values so the resultmetadata must be fixed to return the correct value ;-)
Comment 4 Oliver-Rainer Wittmann 2012-06-13 12:17:32 UTC
getting rid of value "enhancement" for field "severity".
For enhancement the field "issue type" shall be used.