This page is part of the book SQL-99 Complete, Really, by Peter Gulutzan & Trudy Pelzer. The authors have graciously allowed us to reproduce the contents of the book here. Because the book is about the SQL-99 standard, the contents of this and other pages in the book may not directly apply to MariaDB. Use the navigation bar to navigate the book.

A <row type> is defined by a descriptor that contains three pieces of information:

  • The <data type>'s name: ROW.
  • The <data type>'s degree: the number of Fields that belong to the row.
  • A descriptor for every Field that belongs to the row. The Field descriptor contains the name of the Field, the Field's ordinal position in the <row type>, the Field's <data type> and nullability attribute (or, if the Field is based on a Domain, the name of that Domain), the Field's Character set and default Collation (for character string <data type>s) and the Field's <reference scope check> (for <reference type>s).

ROW

The required syntax for a <row type> specification is:

<row type> ::=
ROW (<Field definition> [ {,<Field definition>}... ])

   <Field definition> ::=
   <Field name> {<data type> | <Domain name>}
      [ <reference scope check> ]
      [ COLLATE <Collation name> ]

A <row type> specification defines a row of data: it consists of a sequence of one or more parenthesized {<Field name>,<data type>} pairs, known as Fields. The degree of a row is the number of Fields it contains. A value of a row consists of one value for each of its Fields, while a value of a Field is a value of the Field's <data type>. Each Field in a row must have a unique name. Here is an example of a <row type> specification:

ROW (field_1 INT, field_2 DATE, field_3 INTERVAL(4) YEAR)

A <Field name> identifies a Field and is either a <regular identifier> or a <delimited identifier> that is unique (for all Fields and Columns) within the Table it belongs to. You can define a Field's <data type> either by putting a <data type> specification after <Field name> or by putting a <Domain name> after the <Field name>. (The <data type> of a Field can be any type other than a <reference type> in particular, it can itself be a <row type>.) For example, consider this SQL statement, which creates a Domain, called Domain_1, whose defined <data type> is DATE:

CREATE DOMAIN Domain_1 AS DATE;

Based on this definition, these two <row type> specifications are equivalent both define a row with one Field (called FIELD_1) whose defined <data type> is DATE:

ROW (field_1 DATE)
ROW (field_1 domain_1)

[Obscure Rule] If the <data type> of a Field is CHAR, VARCHAR or CLOB, the Character set that the Field's values must belong to is determined as follows:

  • If the <Field definition> contains a <data type> specification that includes a CHARACTER SET clause, the Field's Character set is the Character set named. Your current <AuthorizationID> must have the USAGE Privilege on that Character set.
  • If the <Field definition> does not include a <data type> specification, but the Field is based on a Domain whose definition includes a CHARACTER SET clause, the Field's Character set is the Character set named.
  • If the <Field definition> does not include any CHARACTER SET clause at all either through a <data type> specification or through a Domain definition the Field's Character set is the Character set named in the DEFAULT CHARACTER SET clause of the CREATE SCHEMA statement that defines the Schema that the Field belongs to.

For example, the effect of these two SQL statements:

CREATE SCHEMA bob AUTHORIZATION bob
  DEFAULT CHARACTER SET INFORMATION_SCHEMA.LATIN1;

CREATE TABLE Table_1 (
  column_1 ROW(
      field_1 CHAR(10),
      field_2 INT));

is to create a Table in Schema bob. The Table has a Column with a ROW <data type>, containing two Fields. The character string Field's set of valid values are fixed length character strings, exactly 10 characters long, all of whose characters must be found in the INFORMATION_SCHEMA.LATIN1 Character set the Schema's default Character set. The effect of these two SQL statements:

CREATE SCHEMA bob AUTHORIZATION bob
  DEFAULT CHARACTER SET INFORMATION_SCHEMA.LATIN1;

CREATE TABLE Table_1 (
  column_1 ROW(
      field_1 CHAR(10) CHARACTER SET INFORMATION_SCHEMA.SQL_CHARACTER,
      field_2 INT));

is to create the same Table with one difference: this time, the character string Field's values must consist only of characters found in the INFORMATION_SCHEMA.SQL_CHARACTER Character set the explicit Character set specification in CREATE TABLE constrains the Field's set of values. The Schema's default Character set does not.

[Obscure Rule] If the <data type> of a Field is CHAR, VARCHAR, CLOB, NCHAR, NCHAR VARYING or NCLOB, and your <Field definition> does not include a COLLATE clause, the Field has a coercibility attribute of COERCIBLE but if your <Field definition> includes a COLLATE clause, the Field has a coercibility attribute of IMPLICIT. In either case, the Field's default Collation is determined as follows:

  • If the <Field definition> includes a COLLATE clause, the Field's default Collation is the Collation named. Your current <AuthorizationID> must have the USAGE Privilege on that Collation.
  • If the <Field definition> does not include a COLLATE clause, but does contain a <data type> specification that includes a COLLATE clause, the Field's default Collation is the Collation named. Your current <AuthorizationID> must have the USAGE Privilege on that Collation.
  • If the <Field definition> does not include a COLLATE clause, but the Field is based on a Domain whose definition includes a COLLATE clause, the Field's default Collation is the Collation named.
  • If the <Field definition> does not include any COLLATE clause at all either explicitly, through a <data type> specification or through a Domain definition the Field's default Collation is the default Collation of the Field's Character set.

[Obscure Rule] If the <data type> of a Field is REF(UDT), your current <AuthorizationID> must have the USAGE Privilege on that UDT. If the <data type> of a Field includes REF with a <scope clause>, your <Field definition> must also include this <reference scope check> clause: REFERENCES ARE [NOT] CHECKED ON DELETE NO ACTION to indicate whether references are to be checked or not. Do not add a <reference scope check> clause under any other circumstances.

  • If a Field is defined with REFERENCES ARE CHECKED, and a <scope clause> naming one or more Tables is included in the <Field definition>, then there is an implied DEFERRABLE INITIALLY IMMEDIATE Constraint on the Field. This constraint checks that the Field's values are also found in the corresponding Field of the system generated Column of the Table named in the <scope clause>.
  • [Obscure Rule] If the <data type> of a Field in a row is a UDT, then the current <AuthorizationID> must have the USAGE Privilege on that UDT.
  • A <row type> is a subtype of a <data type> if (a) both are <row type>s with the same degree and (b) for every pair of corresponding <Field definition>s, the <Field name>s are the same and the <data type> of the Field in the first <row type> is a supertype of the <data type> of the Field in the second <row type>.

<row reference>

A <row reference> returns a row. The required syntax for a <row reference> is:

<row reference> ::=
ROW {<Table name> | <query name> | <Correlation name>}

A row of data values belonging to a Table (or a query result, which is also a Table) is also considered to be a <row type>. In a Table, each Column of a data row corresponds to a Field of the <row type>: the Column and Field have the same ordinal positions in the Table and <row type>, respectively. A <row reference> allows you to access a specific row of a Table or a query result. Here is an example of a <row reference> that would return a row of a Table named Table_1:

ROW(Table_1)

<Field reference>

A <Field reference> returns a Field of a row. The required syntax for a <Field reference> is:

<Field reference> ::=
row_argument.<Field name>

A <Field reference> allows you to access a specific Field of a row. It operates on two arguments: the first must evaluate to a <row type> and the second must be the name of a Field belonging to that row. If the value of row_argument is NULL, then the specified Field is also NULL. If row_argument has a non-null value, the value of the Field reference is the value of the specified Field in row_argument. Here is an example of a <Field reference> that would return the value of a Field named field_1 that belongs to a row of Table_1:

ROW(Table_1).field_1

<row value constructor>

An <row value constructor> is used to construct a row of data. The required syntax for a <row value constructor> is:

<row value constructor> ::=
element_expression |
[ ROW ] (element_expression [ {,element_expression}... ]) |
( <query expression> )

   element_expression ::=
   element_expression |
   NULL |
   ARRAY[] |
   ARRAY??(??) |
   DEFAULT

A <row value constructor> allows you to assign values to the Fields of a row, using either a list of element_expressions of the result of a subquery. An element_expression may be any expression that evaluates to a scalar value with a <data type> that is assignable to the corresponding Field's <data type>. A subquery (<query expression>) is discussed in our chapter on complex queries. The result is a row whose n-th Field value is the value of the n-th element_expression (or whose value is the value of the subquery) you specify. If your element_expression is NULL, the corresponding Field is assigned the null value. If your element_expression is ARRAY[] or ARRAY??(??), the corresponding Field is assigned an empty array. If your element_expression is DEFAULT, the corresponding Field is assigned its default value. Here is an example of a <row value constructor>:

ROW ('hello',567,DATE '1994-07-15',NULL,DEFAULT,ARRAY[])

This example constructs a row with six Fields. The first Field has a character string value of 'hello', the second has a numeric value of '567', the third has a date value of '1994-07-15', the fourth has a null value, the fifth has a value that is the fifth Field's default value and the sixth has a value that is an empty array. This <row value constructor> would be valid for this <row type> specification:

ROW (
  field_1 CHAR(5),
  field_2 SMALLINT,
  field_3 DATE,
  field_4 BIT(4),
  field_5 domain_1,
  field_6 INT ARRAY[4])

A <row value constructor> serves the same purpose for a row as a <literal> does for a predefined <data type>. It has the same format as the <row type>'s ROW specification that is, ROW( ) but instead of a series of <Field definition>s inside the size delimiters, it contains comma-delimited values of the correct <data type> for each Field. For example, if your <row type> specification is:

ROW (field_1 INT, field_2 CHAR(5), field_3 BIT(4))

a valid <row value constructor> would be:

ROW(20,'hello',B'1011')

If you construct a row with a subquery, the row takes on the <data type> of the subquery's result. An empty subquery result constructs a one-Field row whose value is NULL. A non-empty subquery result constructs a one-Field row whose value is the subquery result.

If you want to restrict your code to Core SQL, (a) don't use the ROW <data type> or <row reference>s and <Field reference>s and, when using a <row value constructor>, (b) don't use ARRAY[] or ARRAY??(??) as an element_expression, (c) don't construct a row with more than one Field, (d) don't use the ROW <keyword> in front of your element_expression, and (e) don't use a subquery to construct your row.

Comments

Comments loading...