Compliance with SQL Three-Valued Logic¶

Starting with Denodo 5.5, Virtual DataPort is fully conformant with the three-valued logic defined in the SQL standard. Previous versions of the Denodo Platform are not conformant with this standard. This change may lead to queries returning different results.

The three-valued logic defines that the data type boolean comprises the values True, False and Unknown, which is represented as the NULL value. Any comparison involving the null value or an Unknown truth will return an Unknown result.

The following example explains how this change can lead to different results. Let us say that we execute a query like this one:

SELECT ... WHERE NOT (a = b)

In one of the rows of the result, a is NULL and b is 1.

In previous versions of Virtual DataPort, (a = b) evaluates to false and NOT(a = b) to true. Therefore, this row is added to the result.

Virtual DataPort, as stated by the SQL standard, evaluates (a = b) to Unknown because a is NULL. The result of evaluating NOT (Unknown) is also Unknown. Therefore, this row is not added to the result.

The section Three-valued Logic of the VQL Guide has more information about this.

Cache - Default Fetch Size¶

Starting with Denodo 5.5, the default fetch size when retrieving data from the cache data source is now set to 1,000. In Denodo 4.7 and earlier, it is 10.

In most cases, the data is retrieved much faster with a fetch size of 1,000. However, when the number of rows is small but very big, we recommend testing if this value meets your performance needs. Rows are big when they contain several fields of type blob that occupy several megabytes. This can happen when the blob fields contain big pictures, long PDF documents, etc.

CAST Function¶

Starting with Denodo 5.5, the function CAST truncates the output when converting a value to a text, when these two conditions are met:

1. You specify a length for the target data type

2. And, this length is lower than the length of the input value.

For example, CAST ("Denodo" AS VARCHAR(2)) returns “De” because the target type specifies a length lower than the length of the input value.

In Virtual DataPort 5.0, the behavior of the CAST function can be altered to avoid truncating the value. In later versions, the behavior of the CAST function cannot be altered and it always truncates strings if necessary.

More Reserved Words¶

Virtual DataPort 5.5 defines new reserved words (i.e. words that cannot be used as a view’s name or a field’s name). Therefore, you may have views in your current version that cannot be created in Virtual DataPort 5.5. The figure Basic elements of VQL statements of the VQL Guide lists the reserved words of Virtual DataPort.

Prohibit Setting Incompatible Source Type Properties¶

Virtual DataPort version 5.0 allows setting the type of a field of a view to a type T and in the “Source type properties” of the field, select a subtype that is incompatible. Starting with Denodo 5.5, Virtual DataPort no longer allows this. I.e. having a field of type text with subtype INTEGER is no longer valid.

Views with fields that have an incompatible subtype can be imported into Virtual DataPort 5.5. However, when you edit these views, the wizard will not allow you to save the changes unless you assign a compatible subtype to the field.