[
https://issues.apache.org/jira/browse/CALCITE-1554?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15773553#comment-15773553
]
Julian Hyde commented on CALCITE-1554:
--------------------------------------
I would support this.
This is non-standard SQL, so it would have to be controlled by a new
SqlConformance method. (See CALCITE-1120 and CALCITE-1472 for examples of
adding SqlConformance methods.) We should allow naked nulls in DEFAULT and
ORACLE_xx conformance levels, not in STRICT_xx conformance levels.
Naked nulls will have type {{SqlTypeName.NULL}}. When you compute the lowest
common denominator with a non-NULL type, the other type will win. For example,
if {{x.age}} has type {{INTEGER NOT NULL}} in the above {{UNION}} query, then
the resulting {{age}} column will have type {{INTEGER}}.
If {{SqlConformance#emptyStringIsNull}} is enabled (per CALCITE-815) then the
type of NULL will be {{CHAR(0)}}. Oracle seems to be more lenient in converting
CHAR(0) than CHAR(1) or larger; the following experiments show that while
Oracle will not allow a column whose only possible value is NULL, it treats
{{CHAR(0)}} is union-compatible with other types such as {{NUMBER}} and
{{DATE}}:
{noformat}$ sqlplus scott/tiger as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Fri Dec 23 14:31:30 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing
options
SQL> create table t0 as select null from dual;
create table t0 as select null from dual
*
ERROR at line 1:
ORA-00998: must name this expression with a column alias
SQL> create table t1 as select null n from dual;
create table t1 as select null n from dual
*
ERROR at line 1:
ORA-01723: zero-length columns are not allowed
SQL> create table t2 as select null n from dual union select empno from
scott.emp;
Table created.
SQL> describe t2;
Name Null? Type
----------------------------------------- -------- ----------------------------
N NUMBER
SQL> create table t3 as select null n from dual union select hiredate from
scott.emp;
Table created.
SQL> describe t3;
Name Null? Type
----------------------------------------- -------- ----------------------------
N DATE
SQL> create table t4 as select null n from dual union select ename from
scott.emp;
Table created.
SQL> describe t4;
Name Null? Type
----------------------------------------- -------- ----------------------------
N VARCHAR2(10)
SQL> create table t5 as select '1' n from dual union select empno from
scott.emp;
create table t5 as select '1' n from dual union select empno from scott.emp
*
ERROR at line 1:
ORA-01790: expression must have same datatype as corresponding expression
{noformat}
In situations where the type of the NULL can be derived from context, the type
of the NULL will be that derived type. This is the only case we currently
allow, and the only case the SQL standard allows, and we should not change our
behavior in this case. The best known example of contextually typed expressions
is {{INSERT}}; the following SQL examples comply with the standard:
{code}INSERT INTO Emp (empno, commission)
VALUES (100, NULL), (200, NULL), (300, 12345);
INSERT INTO Emp (empno, commission)
SELECT deptno, NULL FROM Dept;{code}
If NULL is an argument to a function or operator, it's complicated. In some
cases, such as {{myInteger + NULL}}, it's clear what type the NULL should have.
But I doubt that we can do it in general, when there are multiple overloads and
implicit conversions are possible.
> Supporting NULL as column in SELECT Statement
> ---------------------------------------------
>
> Key: CALCITE-1554
> URL: https://issues.apache.org/jira/browse/CALCITE-1554
> Project: Calcite
> Issue Type: Wish
> Reporter: Julian Stenzel
> Assignee: Julian Hyde
>
> it would be cool to support null as an possible column in the SELECT
> statement.
> Use Case:
> UNION two tables x,y with a different number of columns e.g.
> {code}
> Select id, name, age from x
> Union
> Select id, name, Null as age from y
> {code}
> this should be a useful use case for adressing NoSQL databases where our data
> exist in different schemes
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
