Oracle Oddity

Oracle:

Why do some of the USER_* views such as USER_CONSTRAINTS include the OWNER column, but USER_TAB_COLUMNS does not? The world may never know...

Year 0 in Oracle

While troubleshooting a customer's date-related issue, it turned out he had a year 0000 date in his table. He showed me this query of his data looking good, however two-digit years always smell fishy to me:

SQL> select trunc(min(time_stamp_h), 'MONTH') as t_min from mytable;

T_MIN
---------
01-SEP-00

At any rate the issue was resolved, but I couldn't even figure out how year 0 would have gotten in the database in the first place. Oracle seems to have a validation that does not allow year 0:

SQL> select to_date('9/1/0000', 'mm/dd/yyyy') from dual;
select to_date('9/1/0000', 'mm/dd/yyyy') from dual
*
ERROR at line 1:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

But...This blog post revealed that there's more to the story - (Short summary: Oracle doesn't always check for year 0. Only sometimes.)

Brief chat with a buddy about MySQL index size limitation

(2:44:19 PM) Buddy: so many hacks in mysql :) like, you can't use functions as a default value for a column unless that function is called CURRENT_TIMESTAMP which is a single exception
(2:44:59 PM) Me: ug :)
(2:45:59 PM) Buddy: index lengths are a major limit..I've been creating columns with the value of unhex(sha1(value)) and creating my unique indexes on that
(2:46:57 PM) Me: what's that expression?
(2:47:11 PM) Me: i think i have an idea... :)
(2:47:19 PM) Buddy: returns a 20 byte binary sha1 hash
(2:47:25 PM) Me: yeah..bleh :)
(2:48:59 PM) Me: i guess that's just for enforcing the uniqueness? Or would you look up by that too? (thus you have to remember to call unhex(...)) :)
(2:49:33 PM) Buddy: if I wanted to lookup using that index, then it would be lookup too
(2:49:47 PM) Me: yeah
(2:49:59 PM) Buddy: so my queries are like select value from table where valuesha1=unhex(sha1(thingIwant));
(2:50:14 PM) Me: bleh :)
(2:54:09 PM) Buddy: as long as your index is less then 767 bytes yer ok :)
(2:54:32 PM) Me: what are you indexing on that's bigger? you might have told me before...
(2:54:57 PM) Buddy: URLs :)
(2:55:00 PM) Me: ahh