[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-1310) NULLs in UNION doesn't work in Informix

[Razvan Petrescu (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-1310?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=44634#comment-44634 ] 

Razvan Petrescu commented on HHH-1310:
--------------------------------------

Actually this has nothing to do with SELECT * FROM (SELECT * FROM t), but with Informix dialect - at last as of 2011.
Informix, as other databases requires a type cast for SELECT NULL FROM TABLE

The Informix Dialect has to override the "getSelectClauseNullString" method. The following code works and was
adapted from Terradata dialect:

	@Override
	public String getSelectClauseNullString(int sqlType) {
		String v = "null";
		
		switch (sqlType) {
			case Types.BIT:
				v = "cast(null as boolean)";
				break;
			case Types.TINYINT:
			case Types.SMALLINT:
			case Types.INTEGER:
			case Types.BIGINT:
				v = "cast(null as integer)";
				break;				
			case Types.FLOAT:
			case Types.REAL:
			case Types.DOUBLE:
				v = "cast(null as float)";
				break;
			case Types.NUMERIC:
			case Types.DECIMAL:
				v = "cast(null as decimal)";
				break;
			case Types.CHAR:
			case Types.VARCHAR:
			case Types.LONGVARCHAR:
				v = "cast(null as varchar(255))";
				break;
			case Types.DATE:
				v = "cast(null as date)";
				break;
			case Types.TIME:
			case Types.TIMESTAMP:
				v = "cast(null as datetime year to fraction)";
				break;
			case Types.BINARY:
			case Types.VARBINARY:
			case Types.LONGVARBINARY:
			case Types.NULL:
			case Types.OTHER:
			case Types.JAVA_OBJECT:
			case Types.DISTINCT:
			case Types.STRUCT:
			case Types.ARRAY:
			case Types.BLOB:
			case Types.CLOB:
			case Types.REF:
			case Types.DATALINK:
			case Types.BOOLEAN:
				break;
		}
		return v;
	}

> NULLs in UNION doesn't work in Informix
> ---------------------------------------
>
>                 Key: HHH-1310
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-1310
>             Project: Hibernate Core
>          Issue Type: Bug
>          Components: core
>    Affects Versions: 3.1
>         Environment: Informix 10
>            Reporter: Tomasz Bech
>            Priority: Critical
>
> Informix simply doesn't support phrase: 'select null from table'
> So UNION in inheritance doesn't work.

--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira