Sql - What does it mean to set a value?

Sql - What does it mean to set a value?

Using t-sql in Sql Server it's perfectly acceptable to use a SET statement to set a value
for a variable. For example, this batch sets the variable @MyRows to the number of rows in
the Orders table from the Northwind sample database:

DECLARE @MyRows int;
SET @MyRows = (SELECT COUNT(*) FROM Orders);
PRINT LTRIM(Str(@MyRows)); -- Returns 830

Now this operation makes perfect sense. Use an aggregate function (count) to derive a scalar
value and set it to a variable.

In the relational language of Dataphor, D4, the same operation can be expressed as:

var MyRows:Integer:=Count(Orders);

In D4 we can confirm that the expression Count(Orders) returns a scalar value of type
integer by using the Clintonian 'is' construct which tests for a particular type and
returns a boolean (true/false).

select Count(Orders) is Integer;//Returns True

We can also dispense with the Integer declaration and allow the integer type for variable
MyRows to be derived from the expression:

var MyRows:=Count(Orders);

Now lets suppose we want to set a value based on the value of a column in the Orders table
for a particular row. The following batch makes use of OrderID as a primary key and targets
a particular row for its Freight value:

DECLARE @MyFrt MONEY;
SET @MyFrt = (SELECT Freight FROM Orders WHERE OrderID=10249);
PRINT CAST(@MyFrt AS VARCHAR(8)); -- Returns 11.61

So the same operation that sets an aggregate value, a value derived about a table, also
sets a value from within a table. This state of affairs seems to be accepted without
question by sql folks. Well lets take a closer look at this from a relational perspective.
The closest D4 construction to the sql batch would be:

var MyFrt:=(Orders where OrderID=10249 {Freight});

And if the D4 operation is the same as the sql operation the MyFrt variable should be a
scalar value of type Money.

var MyFrt:=(Orders where OrderID=10249 {Freight});
select MyFrt is Money //Returns False.

Of course MyFrt isn't a scalar, it's a table!

var MyFrt:=(Orders where OrderID=10249 {Freight});
select MyFrt;

Freight //A table with a single row and column.
-------
$11.61  

select MyFrt is table{Freight:Money}; //Returns True confirming MyFrt is a table.

Variable MyFrt is a table with a single column named Freight with a type of Money. The table
happens to have only a single row but it is still very much a table! ☺ Now what could lie
between a table and a scalar value from within the table? There's only one possible thing,
a row ☺ What's needed is to extract out a row from the table, to transform the table type
to a row type. We do this with a thingie called a pure row extractor, '[]'.

var MyFrt:=(Orders where OrderID=10249 {Freight})[];
select MyFrt;

Freight //A row with a single column whose name is Freight and type is Money.
-------
$11.61

select MyFrt is row{Freight:Money}; //Returns True confirming MyFrt is a type of row.

Once we have zeroed in on a row we can get to a scalar value, a value of a column in the row.
We do this by using a column extractor which is the dot ('.') followed by the column name.
So to finally set the variable to the Freight value of the row we have:

var MyFrt:=(Orders where OrderID=10249 {Freight})[].Freight;
select MyFrt;

$11.61 //A scalar value of type Money.

select MyFrt is Money; //Returns True confirming the type of MyFrt is a scalar type (Money). 

We can set the value using a more succinct expression by using the primary key value (OrderID)
to directly extract the row (eliminating the WHERE clause) and then extracting the Freight value:

var MyFrt:=Orders[10249].Freight;
select MyFrt; //Return $11.61

Going back to the sql version we can even assign the Freight value to a string:

DECLARE @MyFrt varchar(10);
SET @MyFrt = (SELECT Freight FROM Orders WHERE OrderID=10249);
PRINT @MyFrt; -- Returns 11.61

What we have here is an example of three implicit conversions in one statement! To accept
the idea that sql is even relational like is to accept the implicit conversion of a
table to a row followed by a row to a scalar (money) followed by a money type to a string.
This of course is logically ridiculous. There is no such thing as an implicit conversion in
a relational system. There is no principle in any theory or in contemporary computer science
to derive a scalar value from a column(s) in a table without explicit transformations. There
is no logic to what sql is doing here only pure exigency. Which is what you get when essential
types like table and row are missing. In Sql when there are multiple rows that satisfy a
a WHERE clause the variable is left undefined and an error generated:

DECLARE @MyFrt MONEY;
SET @MyFrt = (SELECT Freight FROM Orders WHERE OrderID<=10249);
-- Subquery returned more than 1 value. This is not permitted when the subquery follows
-- =, !=, <, <= , >, >= or when the subquery is used as an expression.

This is really a valid logical operation, an attempt to set (assign) a table to a variable.
This is precisely what D4 does:

var MyFrt:=Orders where OrderID<=10249 {Freight};

select MyFrt;

Freight //A table.
-------
$11.61
$32.38

select MyFrt is table{Freight:Money};//Returns True confirming MyFrt is a table type.

The sql situation is nothing but logical confusion. The operation has nothing to do with
setting a scalar value. It is a table assignment. But sql doesn't know from such a thing
and makes up a spurious error. The so called aggregate concatenation query
(http://support.microsoft.com/default.aspx/kb/287515) is also illogical. That a query, in
the absence of a dedicated aggregate function (ie. concatenate), that returns a table is
supposed to behave like looping over a cursor and concatenating values to a variable is silly.

Perhaps the best that can be said of sql in situations like this is that results, even a
correct result, is based on whimsy instead of a logical foundation. But it's not hard to
understand the attraction of the whimsical nature of sql ☺ The prevalence of sql based on
the susceptibility of users to science fiction, as opposed to computer science, and the
spreading of the infection is why sql is the unchallenged viral language of databases.
Hopefully logic and common sense will halt its spread. But its arrest remains a challenge ☺