Month: October 2022


Data type conversion and SQLSTATE

A while ago we got a question about data type conversion and SQLSTATE …


Suppose we have data types T1 and T2 (either built-in or user defined).

  • This pair is not explicitly listed in the Store Assignment rules.
  • There is no a CREATE CAST for this pair defined.

Reading the standard, I have an impression that these statements should fail:

UPDATE t1 SET T1_col=T2_col;
INSERT INTO t1 (T1_col) SELECT T2_col FROM t1;
SET T1_var=T2_var; — inside an SP

But I could not find which exactly error should be raised:

  • what should be SQLSTATE
  • what should be error message text

A related question:
Is it possible to do CREATE CAST for a pair of two built-in data types?

The answer was …

I will be quoting 9075-1:2011 Framework and 9075-2:2016 Foundation.

Framework says:
“In the Syntax Rules, the term shall defines conditions that are required to be true of syntactically conforming SQL language. When such conditions depend on the contents of one or more schemas, they are required to be true just before the actions specified by the General Rules are performed. The treatment of language that does not conform to the SQL Formats and Syntax Rules is implementation-dependent. If any condition required by Syntax Rules is not satisfied when the evaluation of Access or General Rules is attempted and the implementation is neither processing non-conforming SQL language nor processing conforming SQL language in a non-conforming manner, then an exception condition is raised: syntax error or access rule violation.”
… Therefore: if you see the word “shall” in a syntax rule, and the condition is not true, it is a syntax error or access violation.

Foundation says: re set clause list:
“Otherwise, the Syntax Rules of Subclause 9.2, “Store assignment”, are applied with the column of T identified by the <object column> as TARGET and the <update source> of the <set clause> as VALUE.”

Foundation says: re store assignment: syntax rules: where SD = source data type and TD = target data type:
“If TD is character string, binary string, numeric, boolean, datetime, interval, or a user-defined type, then either SD shall be assignable to TD or there shall exist an appropriate user-defined cast function UDCF from SD to TD.”
… Therefore: since the word “shall” has been used and this is a syntax rule: if the condition is not true, it is a syntax error or access violation.

Foundation says: re definition of assignable:
“A binary string is assignable only to sites of binary string type.”
“A number is assignable only to sites of numeric type.”
… And so on. That is, for each data type, there’s a note about what assignable means for that type.
Now take this as an example:
… SET binary_string_target = numeric_source …
According to “A binary string is assignable only to sites of binary string type”, this SD is not assignable to this TD.
Therefore, unless there is an appropriate user-defined cast function, the partial condition “SD shall be assignable to TD” has not been met.

For a moment ignore the other partial condition, “there shall exist an appropriate user-defined cast function”. In that case, now we know that the rule for store assignment, which is required by the rule for set clause list, has not been met. And that rule is a syntax rule that includes the word “shall”.
So it is a syntax error or access rule violation.
Table 38 “SQLSTATE class and subclass codes” says that the class for syntax error or access rule violation is 42.
Thus it’s clear — at last, eh? — that the SQLSTATE class is 42. What about the subclass?
Foundation says:
“NOTE 767 — One consequence of this is that an SQL-implementation may, but is not required by ISO/IEC 9075 to, provide subclasses for exception condition syntax error or access rule violation that distinguish between the syntax error and access rule violation cases.”
In other words, you can say SQLSTATE is 42000 but you can say other things too.
Here I recommend that you look at what DB2 for z/OS has.

“42806 A value cannot be assigned to a variable, because the data types are not compatible.” or
“42821 A data type for an assignment to a column or variable is not compatible with the data type.”

Conclusion: the SQLSTATE can be 42xxx, e.g. 42821, syntax error or access violation.

But there are two loopholes! Long ago, when my job was to say how MySQL was compatible with standard SQL, I used them both.

Loophole #1: See above: “The treatment of language that does not conform to the SQL Formats and Syntax Rules is implementation-dependent.”
Well, whenever MySQL/MariaDB has a type that is not mentioned in the official rules, such as TEXT rather than CHARACTER VARYING, that means you can declare there is no error.

Loophole #2: See above: “or there shall exist an appropriate user-defined cast function”.
Well, there is a cast function whenever MySQL/MariaDB says there is. That is, if Monty declares that you can assign a number to a varchar, while smiling, I would explain: the meaning of the word “user” is implementation-dependent. In this case, the user who created the function is Monty. That means you can declare there is no error.

But, if you declare that you are using the loopholes, then CREATE CAST will be illegal because Foundation requires: “There shall be no user-defined cast for SDT and TDT.” and, alas, where user = Monty, there is a user-defined cast.

Thanks to Alexander Barkov for the question.

ocelotgui explorer

On an unrelated note, the Ocelot Graphical User Interface has an explorer.

Sometimes called a navigator, and vaguely like what people are used to with Windows Explorer, this is a widget that appears (usually on the left but detachable) showing objects in all schemas that the user can access. If the user right-clicks on an object, an in-context menu appears with SQL statements or client actions that can be performed by clicking. Customizable.

Currently it’s only in source code which you can download from github, as always.

It and its documentation will be in the next release and/or in the next post of this blog.