A question came up as to what is the best practice to assign nulls to columns in a record type in CCLScript. There are several ways to achieve this but some are more efficient than others. Consider the example below which shows a snippet of a CCL function inside a declare block that illustrates four different ways to assign a null value to a column in a record type.
DECLARE boolean BOOL_NULL := null; integer myFunction() { //Declare a record type variable [integer Key1; | string Val1; boolean Val2;] rec1, rec2, rec3, rec4; //Method 1: Explicitly set a column to a null rec1 := [Key1=10;| Val1=left('some_value', 4); Val2 = null;]; //Method 2: Don't include the column(s) that needs to be assigned a null value rec2 := [Key1=10;| Val1=left('some_value', 4);]; //Method 3: Cast the null to the type of the column rec3 := [Key1=10;| Val1=left('some_value', 4); Val2 = to_boolean(null);]; //Method 4: Create a boolean variable, assign it a NULL and use it for a null assignment rec4 := [Key1=10;| Val1=left('some_value', 4); Val2 = BOOL_NULL;]; //... return 0; } END; |
Among the four methods described above methods 4 and 3 are preferable in that order. Methods 1 and 2 should be avoided as it causes the compiler to generate less efficient code, which performs a record cast operation.
To understand why the compiler generates inefficient code one has to understand what the compiler does when a record assignment is made. When a record assignment is made the compiler first generates code to create the implicit record, which will then be assigned to the record type variable i.e. recX in this case. The compiler determines the types of the columns in the implicit record from the expressions assigned to each column.
Taking Method 1 as an example the compiler determines that column "Key1" is of type integer based on the integer constant "10" and column Val1 is of type string because of the string expression "left('some_value', 4)". For Val2 the expression is a null and the type of null is unknown. As the compiler needs a type for every column it assumes the column is an integer.
Next the compiler tries to assign the implicit record to the record type variable rec1, which has Val2 defined as a boolean. At this point the compiler does a type check and verifies that the datatypes of the matching columns (match is determined by the column name) are the same, compatible or incompatible. When the types are the same there are no issues and when the types are not compatible the compiler generates an error and the compilation fails.
However, when the types are not the same but are compatible the compiler is accommodating and performs a record cast operation, which implicitly casts the values of the compatible types to the value of the target column types. In this case the null value for "Val2", which the compiler assumed to be an integer, is cast to a boolean and it copies over the rest of the column values to create a new record before assigning it to "rec1". This is an expensive operation especially when the record is large and should be avoided if possible.
A similar record cast operation happens when there are columns in receiving variable record type that is not present in the implicit record and vice-verse. Method 2 is inefficient for this reason because the column 'Val2' exists in the receiving variable type but does exist in the implicit record.
In methods 3 and 4 the implicit column type of "Val2" is determined to be a boolean and therefore there is no need for a record cast operation. The reason why method 4 is preferred over method 3 is that in method 4 the null variable is assigned once and reused and in the later case it is computed every time. This inefficiency is marginal but hey every bit counts when it comes to performance.
A similar issue arises when the type of a constant does not exactly match the type of a target column. In the above example if "Key1" is of type long it would have caused a similar record cast operation because '10' is considered to be an integer constant. To avoid a record cast the integer constant '10" must be explicitly cast to a long using either "to_long(10)" or "cast(long, 10)".
When assigning nulls to variables with primitive types one can just assign a null value just like how the BOOL_NULL variable is assigned a null in the above example. This is because the compiler uses the type of the receiving variable as a hint.
