Accroding to the "Vertica 6.1.x Enterprise Edition Release Notes" document, the following change was made to Vertica 6.1:
Vertica 6.0:CREATE TABLE AS SELECT would generate a zero-width column (VARCHAR(0)) if given an empty query result (such as CREATE TABLE example AS SELECT '' AS X;).
This column could cause issues (for example, when exporting the table's design), because VARCHAR columns must be at least 1 character wide. CREATE TABLE AS SELECT now converts any VARCHAR(0) columns into a VARCHAR(80).
Code: Select all
dbadmin=> select version();
version
------------------------------------
Vertica Analytic Database v6.0.0-3
(1 row)
dbadmin=> create table t1 as select '' c1 from dual;
CREATE TABLE
dbadmin=> \d t1;
List of Fields by Tables
Schema | Table | Column | Type | Size | Default | Not Null | Primary Key | Foreign Key
--------------+-------+--------+------------+------+---------+----------+-------------+-------------
intersect_wh | t1 | c1 | varchar(0) | 0 | | f | f |
(1 row)
Code: Select all
dbadmin=> select version();
version
------------------------------------
Vertica Analytic Database v6.1.0-0
(1 row)
dbadmin=> create table t1 as select '' c1 from dual;
CREATE TABLE
dbadmin=> \d t1;
List of Fields by Tables
Schema | Table | Column | Type | Size | Default | Not Null | Primary Key | Foreign Key
--------------+-------+--------+-------------+------+---------+----------+-------------+-------------
intersect_wh | t1 | c1 | varchar(80) | 80 | | f | f |
(1 row)
In MySQL we see that a '' will default to a data type of CHAR with a length of 0.
Code: Select all
MySQL> create table t1 as select '' c1;
Query OK, 1 row affected (0.09 sec)
Records: 1 Duplicates: 0 Warnings: 0
(mydbadmin@localhost) [jim]> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c1 | char(0) | NO | | | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
SQL Server uses a data type of VARCHAR having a length of 1:
Oracle:
Oracle treats the empty string '' as a CHAR(0):
Code: Select all
SQL> create view t1 as select '' c1 from dual;
View created.
SQL> select data_type, data_length from user_tab_columns where table_name = 'T1';
DATA_TYPE DATA_LENGTH
--------- -----------
CHAR 0