March 11, 2016 at 11:05 am
TomThomson (3/11/2016)
Jeff Moden (3/11/2016)
TomThomson (3/11/2016)
Certainly we want to get the numbering added to this splitter in SQL 2016, but we ought to find way of avoiding treating NULL as a value rather than as an indicator that we don't have a value. If the splitter were a TVF this would be easy: NULL return means the function was asked to split something that isn't a value, so it can't return any value and must return null (and empty table returned means no rows if we need that result); or maybe an extra parameter indicating what to do with NULL as someone else suggested.If you're suggesting that no rows should be returned if you pass the function a NULL, I have to disagree for the same reason that I think the following produces the correct result...
SELECT LOG(NULL);
No, that's not what I'm suggesting. An empty set for NULL is anathema.
What I'm suggesting is probably pretty radical: since Codd's idea was that we should work in a language that was based on a relational calculus, let's make T-SQL a little bit closer to that and treat relations as first class objects in the language - I know, absolute anathema to the SQL Standards gurus, but in my view disagreeing with them is a probably a good sign.
So if you pass the function a NULL, it returns NULL, and if you pass it aomething other than null it retums a relation (ie a set of rows) - it's a (built in) TVF. Whether the function ever returns a relation containing no rows depends on how it is specified to behave if the string passed in to be split has length zero. To me it seems natural in that case to return one row (sequence number 1, string value the zero length string) but some people think returning an empty relation for that case would be better.
Then you and I are in complete agreement. Pass a NULL, get a NULL back. Pass and empty string, get an empty string back. Pass a single delimiter, get two empty strings back. Etc, etc.
That's what I did with DelimitedSplit8K and they almost got all of that right with the new built in function except they return no rows for a NULL input.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 11, 2016 at 11:19 am
Ed Wagner (3/11/2016)
Jeff Moden (3/11/2016)
TomThomson (3/11/2016)
Certainly we want to get the numbering added to this splitter in SQL 2016, but we ought to find way of avoiding treating NULL as a value rather than as an indicator that we don't have a value. If the splitter were a TVF this would be easy: NULL return means the function was asked to split something that isn't a value, so it can't return any value and must return null (and empty table returned means no rows if we need that result); or maybe an extra parameter indicating what to do with NULL as someone else suggested.If you're suggesting that no rows should be returned if you pass the function a NULL, I have to disagree for the same reason that I think the following produces the correct result...
SELECT LOG(NULL);
I don't think any splitter would ever return a NULL as one of the results because you can't concatenate a NULL with a string and get anything other than NULL,
For example, "1,2,,4" would hopefully return 4 rows: 1, 2, an empty string, 4. This is because there really is an empty string in position 3. And as we all know, an empty string is not a NULL.
I don't think anyone is suggesting NULL should be returned as one of the results; the argument is about what should be returned when there are no valid results. Some people want to return a row containing a NULL in that case and others want to return no rows in that case. I wouldn't like to return no rows in that case; it's nice to preserve nulls, so if NULL were passed in insted of a string maybe NULL should be returned - but that entails a big language change, since the language now has to cope intelligently with a derived relation which is NULL, ie a NULL table, and that's not going to happen this year, I'm pretty sure. But yet another bunch of people are saying that a single row containing NULL should be returned when the string passed in to be split is NULL, instead of not returning any rows because there aren't any (the language can cope with empty relations/tables already, so not returning any rows should be OK) or returning a NULL table (which the language currently has no useful concept of).
I think we're in an impossible situation here. Anything we do is either wrong from a short term language design point of view or just not feasible except perhaps in the very long term. I think quarreling over the choice between two evils (return a row when there are no valid rows to return and pretend that a NULL in one of the columns in the row makes that OK is one evil, return nothing at all so that if in the future we end up with a more general splitter that could deliver no rows for some reason other than the input being NULL we have a mess on our hands is the other evil) is silly. But let's be clear that the two options that we might reasonabl get are both evil. We've been living with one of them ever since we had a decent splitter, maybe we start to should live with the other, maybe not.
Tom
March 11, 2016 at 11:42 am
TomThomson (3/11/2016)
Ed Wagner (3/11/2016)
Jeff Moden (3/11/2016)
TomThomson (3/11/2016)
Certainly we want to get the numbering added to this splitter in SQL 2016, but we ought to find way of avoiding treating NULL as a value rather than as an indicator that we don't have a value. If the splitter were a TVF this would be easy: NULL return means the function was asked to split something that isn't a value, so it can't return any value and must return null (and empty table returned means no rows if we need that result); or maybe an extra parameter indicating what to do with NULL as someone else suggested.If you're suggesting that no rows should be returned if you pass the function a NULL, I have to disagree for the same reason that I think the following produces the correct result...
SELECT LOG(NULL);
I don't think any splitter would ever return a NULL as one of the results because you can't concatenate a NULL with a string and get anything other than NULL,
For example, "1,2,,4" would hopefully return 4 rows: 1, 2, an empty string, 4. This is because there really is an empty string in position 3. And as we all know, an empty string is not a NULL.
I don't think anyone is suggesting NULL should be returned as one of the results; the argument is about what should be returned when there are no valid results. Some people want to return a row containing a NULL in that case and others want to return no rows in that case. I would like to return no rows in that case; but it's nice to preserve nulls, so if NULL were passed in insted of a string maybe NULL should be returned - but that entails a big language change, since the language now has to cope intelligently with a derived relation which is NULL, ie a NULL table, and that's not going to happen this year, I'm pretty sure. But yet another bunch of people are saying that a single row containing NULL should be returned when the string passed in to be split is NULL, instead of not returning any rows because there aren't any (the language can cope with empty relations/tables already, so not returning any rows should be OK) or returning a NULL table (which the language currently has no useful concept of).
Okay, we're in agreement. I've seen the "return null instead of an empty string" before and was hoping you weren't making that argument. 😉
As for getting a NULL versus an empty set back, I can see the pros and cons either way. You're absolutely right in that it would require a big change in the language, so getting a NULL back is probably as good as we're going to get. Even an outer join returns a NULL. From a purist's perspective, the empty set may be better, but for SQL, I'm not sure how it would deal with it. My guess would be that it would deal with it poorly.
March 11, 2016 at 11:45 am
TomThomson (3/11/2016)
I think we're in an impossible situation here. Anything we do is either wrong from a short term language design point of view or just not feasible except perhaps in the very long term. I think quarreling over the choice between two evils (return a row when there are no valid rows to return and pretend that a NULL in one of the columns in the row makes that OK is one evil, return nothing at all so that if in the future we end up with a more general splitter that could deliver no rows for some reason other than the input being NULL we have a mess on our hands is the other evil) is silly. But let's be clear that the two options that we might reasonabl get are both evil. We've been living with one of them ever since we had a decent splitter, maybe we start to should live with the other, maybe not.
I just saw your edit. It isn't an impossible situation. Microsoft will do whatever they want and tell us what they did. They may or may document it in Books Online and they might partially document it. We play in the playground they design. 😛 What we design beyond that it up to us and that's where the fun comes in, but we still have to obey certain rules of the playground.
March 11, 2016 at 11:53 am
Ed Wagner (3/11/2016)
TomThomson (3/11/2016)
I think we're in an impossible situation here. Anything we do is either wrong from a short term language design point of view or just not feasible except perhaps in the very long term. I think quarreling over the choice between two evils (return a row when there are no valid rows to return and pretend that a NULL in one of the columns in the row makes that OK is one evil, return nothing at all so that if in the future we end up with a more general splitter that could deliver no rows for some reason other than the input being NULL we have a mess on our hands is the other evil) is silly. But let's be clear that the two options that we might reasonabl get are both evil. We've been living with one of them ever since we had a decent splitter, maybe we start to should live with the other, maybe not.I just saw your edit. It isn't an impossible situation. Microsoft will do whatever they want and tell us what they did. They may or may document it in Books Online and they might partially document it. We play in the playground they design. 😛 What we design beyond that it up to us and that's where the fun comes in, but we still have to obey certain rules of the playground.
Well, I've voted on the connect item for doing something, and commented that I think the best option in the short term is single row with a NULL as the string to indicate a NULL input. But my comments probably have zero influence on MicroSoft.
Tom
March 11, 2016 at 12:20 pm
Jeff Moden (3/11/2016)
..... I think the following produces the correct result...
SELECT LOG(NULL);
So LOG returns NULL when it has NULL input. It doesn't return a rowset containing a row one of whose columns contains NULL, it returns a scalar NULL.
The splitter returns a rowset. A thing of type table(seq int not null, strng varchar(256) null) or something like that. So when fed NULL it ought to return something like CAST(NULL as table(seq int not null, strng varchar(256) null). But it can't, because such a cast statement is invalid.
I really would like the splitter to behve like log, and return NULL. But your current splitter returns a row, not a NULL rowset but a non-null rowset where the string value in the only row in the set is not present (NULL). So it behaves nothing like log.
Of course what it does is probably a proper thing to do given the inability of T-SQL (indeed for any SQL dialect) to do the right thing. And it shouldn't create any confusion, because that's the only way a NULL can appear anywhere in its results (see my reply to Erikur in this topic 30 March last year). But it's also true that if it returned no rows instead that also not cause any confusion, because there's currently no other way it could return no rows, so that's proably proper too. However, your way is my preference for the short term because that's what I've been using for a long time. For the long time, I'd like to see the language allowing NULL relations.
Tom
March 11, 2016 at 1:48 pm
Jeff Moden (3/11/2016)
@steve-2 Jones. This would be really good for an editorial especially if you included the links and encouraged folks to vote the fixes up. If you want, I could write it up over this weekend and send it to you.
Please do. I'm gone a bit in Mar, so I could use some guests 😉
March 11, 2016 at 11:46 pm
TheSQLGuru (3/11/2016)
Someone needs to benchmark the STRING_SPLIT() function coming out in SQL Server 2016 and update the main article!
Very quick on Azure, only caveat is that it simply returns
Msg 208, Level 16, State 1, Line 5
Invalid object name 'STRING_SPLIT'.
😎
March 12, 2016 at 12:11 am
TomThomson (3/11/2016)
Jeff Moden (3/11/2016)
..... I think the following produces the correct result...
SELECT LOG(NULL);
So LOG returns NULL when it has NULL input. It doesn't return a rowset containing a row one of whose columns contains NULL, it returns a scalar NULL.
Exactly. A scalar function always returns a scalar value, be it NULL or a non-NULL value. It can't return "nothing", outside of returning NULL to mean "nothing".
On the other hand, a set-based function returns a set. Any rows returned indicates a non-empty set, even if it is just a single row containing NULL. An empty set is just that: empty, and a single row of NULL is specifically not an empty set, given that there is one element in that set.
The splitter returns a rowset. A thing of type table(seq int not null, strng varchar(256) null) or something like that. So when fed NULL it ought to return something like CAST(NULL as table(seq int not null, strng varchar(256) null). But it can't, because such a cast statement is invalid.
...
Of course what it does is probably a proper thing to do given the inability of T-SQL (indeed for any SQL dialect) to do the right thing. And it shouldn't create any confusion, because that's the only way a NULL can appear anywhere in its results (see my reply to Erikur in this topic 30 March last year).
No, unfortunately returning a single row of NULL is not a proper thing to do because it is not an empty set, it is a set of 1 element, just as it would be if the splitter were fed an empty string, or any non-empty string with no delimiter. Passing in a NULL means that there is nothing to split, hence nothing in the set to return. This is equivalent to using a WHERE condition of WHERE 1 = 0: you will get a result set structure returned, but no rows.
And I wouldn't say that it creates confusion, but more to the point it's created from confusion. And what is being confused here is what the actual operation is. The main focus of the operation is not the string itself, but the data that is encoded into the string. This operation is not analogous to SUBSTRING or other string-based functions. The CSV string is just a vehicle for transmitting an array over a medium that cannot handle arrays; it could have just as well been VARBINARY, or XML, or JSON. The main focus here is that there is a set of elements that have been serialized into a format that is acceptable to the environmental constraints. So if you pass in a NULL, then you are saying that you have no serialized data to deserialize. And no data to deserialize, once again, means no rows to return.
And while there are not many examples of built-in set-based functions, there is at least one that we can look at to see how it behaves in similar circumstances: XML.nodes().
Just run the following:
DECLARE @collection XML;
SET @collection = N'<set><element>wazzup!</element></set>';
SELECT col.value('.', 'NVARCHAR(50)') AS [Element]
FROM @collection.nodes(N'/set/element') tab(col);
-- Returns: 1 row of "wazzup!"
SET @collection = N'<set><element></element></set>';
SELECT col.value('.', 'NVARCHAR(50)') AS [Element]
FROM @collection.nodes(N'/set/element') tab(col);
-- Returns: 1 empty row
SET @collection = N'<set></set>';
SELECT col.value('.', 'NVARCHAR(50)') AS [Element]
FROM @collection.nodes(N'/set/element') tab(col);
-- Returns: no rows
SET @collection = NULL;
SELECT col.value('.', 'NVARCHAR(50)') AS [Element]
FROM @collection.nodes(N'/set/element') tab(col);
-- Returns: no rows
What this request comes down to is wanting NULL to have a specific meaning, but it doesn't. At least not unless you use SET ANSI_NULLS OFF. So maybe, rather than having an input parameter to determine how to handle NULLs, they should just make its behavior dependent on the ANSI_NULLS session property ;-).
With regards to the Connect suggestion to add element/item numbers, that is definitely useful (I did vote for it) but probably not critical. I posted a work-around on that suggestion showing that using the ROW_NUMBER() windowing function works just fine, although it is an extra step.
Finally, I did post a related suggestion: STRING_SPLIT needs "RemoveEmptyEntries" option, like String.Split in .NET[/url] 🙂
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
March 12, 2016 at 7:01 am
Eirikur Eiriksson (3/11/2016)
TheSQLGuru (3/11/2016)
Someone needs to benchmark the STRING_SPLIT() function coming out in SQL Server 2016 and update the main article!Very quick on Azure, only caveat is that it simply returns
Msg 208, Level 16, State 1, Line 5Invalid object name 'STRING_SPLIT'.
😎
Do you have compatibility mode of 130 for your database? I don't have access to 2016 to test, but the BOL page said it's required.
March 12, 2016 at 7:48 am
Solomon Rutzky (3/12/2016)
TomThomson (3/11/2016)
Jeff Moden (3/11/2016)
..... I think the following produces the correct result...
SELECT LOG(NULL);
So LOG returns NULL when it has NULL input. It doesn't return a rowset containing a row one of whose columns contains NULL, it returns a scalar NULL.
Exactly. A scalar function always returns a scalar value, be it NULL or a non-NULL value. It can't return "nothing", outside of returning NULL to mean "nothing".
On the other hand, a set-based function returns a set. Any rows returned indicates a non-empty set, even if it is just a single row containing NULL. An empty set is just that: empty, and a single row of NULL is specifically not an empty set, given that there is one element in that set.
The splitter returns a rowset. A thing of type table(seq int not null, strng varchar(256) null) or something like that. So when fed NULL it ought to return something like CAST(NULL as table(seq int not null, strng varchar(256) null). But it can't, because such a cast statement is invalid.
...
Of course what it does is probably a proper thing to do given the inability of T-SQL (indeed for any SQL dialect) to do the right thing. And it shouldn't create any confusion, because that's the only way a NULL can appear anywhere in its results (see my reply to Erikur in this topic 30 March last year).
No, unfortunately returning a single row of NULL is not a proper thing to do because it is not an empty set, it is a set of 1 element, just as it would be if the splitter were fed an empty string, or any non-empty string with no delimiter. Passing in a NULL means that there is nothing to split, hence nothing in the set to return. This is equivalent to using a WHERE condition of WHERE 1 = 0: you will get a result set structure returned, but no rows.
And I wouldn't say that it creates confusion, but more to the point it's created from confusion. And what is being confused here is what the actual operation is. The main focus of the operation is not the string itself, but the data that is encoded into the string. This operation is not analogous to SUBSTRING or other string-based functions. The CSV string is just a vehicle for transmitting an array over a medium that cannot handle arrays; it could have just as well been VARBINARY, or XML, or JSON. The main focus here is that there is a set of elements that have been serialized into a format that is acceptable to the environmental constraints. So if you pass in a NULL, then you are saying that you have no serialized data to deserialize. And no data to deserialize, once again, means no rows to return.
And while there are not many examples of built-in set-based functions, there is at least one that we can look at to see how it behaves in similar circumstances: XML.nodes().
Just run the following:
DECLARE @collection XML;
SET @collection = N'<set><element>wazzup!</element></set>';
SELECT col.value('.', 'NVARCHAR(50)') AS [Element]
FROM @collection.nodes(N'/set/element') tab(col);
-- Returns: 1 row of "wazzup!"
SET @collection = N'<set><element></element></set>';
SELECT col.value('.', 'NVARCHAR(50)') AS [Element]
FROM @collection.nodes(N'/set/element') tab(col);
-- Returns: 1 empty row
SET @collection = N'<set></set>';
SELECT col.value('.', 'NVARCHAR(50)') AS [Element]
FROM @collection.nodes(N'/set/element') tab(col);
-- Returns: no rows
SET @collection = NULL;
SELECT col.value('.', 'NVARCHAR(50)') AS [Element]
FROM @collection.nodes(N'/set/element') tab(col);
-- Returns: no rows
What this request comes down to is wanting NULL to have a specific meaning, but it doesn't. At least not unless you use SET ANSI_NULLS OFF. So maybe, rather than having an input parameter to determine how to handle NULLs, they should just make its behavior dependent on the ANSI_NULLS session property ;-).
With regards to the Connect suggestion to add element/item numbers, that is definitely useful (I did vote for it) but probably not critical. I posted a work-around on that suggestion showing that using the ROW_NUMBER() windowing function works just fine, although it is an extra step.
Finally, I did post a related suggestion: STRING_SPLIT needs "RemoveEmptyEntries" option, like String.Split in .NET[/url] 🙂
IMHO, what you have to remember is that, no matter what type of function it is, it's still "just" a string manipulation function, which makes it quite a bit different than most table-valued functions. If you give it a single element, then it should return that single element without modification and that includes NULL elements. That's a part of the basic definition of a splitter, relational theory be damned.
And let's try to not complicate a good thing. Almost everyone who has ever made a splitter function has included two and only two parameters... the string to be split and the delimiter to split it on. Nothing more... nothing less.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 12, 2016 at 8:53 am
Jeff Moden (3/12/2016)
IMHO, what you have to remember is that, no matter what type of function it is, it's still "just" a string manipulation function, which makes it quite a bit different than most table-valued functions. If you give it a single element, then it should return that single element without modification and that includes NULL elements. That's a part of the basic definition of a splitter, relational theory be damned.
Well, I certainly do appreciate you so effectively proving my point ;-). And that point, again, is that this issue is being confused by focusing on the input type being a string. This operation is specifically not string manipulation. Yes, "If you give it a single element, then it should return that single element without modification", but NULL is very specifically not an element. If it were, then a NULL could be represented in more than just the first and only position. Empty strings, whether representing a single element (e.g. '' resulting in 1 row of empty string) or found in one or more elements when there is at least one delimiter (e.g. 'a,,b' resulting in 3 rows, the 2nd of which is an empty string) do work this way. But there is no way to represent a NULL in that second position. This is because a NULL cannot be distinguished from an empty string. XML solved this issue by either tagging an element with xsi:nil or not specifying an attribute. This issue also occurs with how data is stored in data pages, which is why there is a Null Bitmap on each row of each data page. NULL is, by definition, not an element.
And let's try to not complicate a good thing. Almost everyone who has ever made a splitter function has included two and only two parameters... the string to be split and the delimiter to split it on. Nothing more... nothing less.
What others have done, while insightful, has absolutely zero bearing on the correctness or appropriateness of those actions. And adding parameters to increase flexibility is not only a good thing, but it is exactly the thing that we do ALL of the time when creating software. And since this is now a built-in function, Microsoft can quite easily make any additional parameters optional (through overloads) just like they do all of the time, leaving the simple 2 parameter usage just as valid as any other combination of input parameters.
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
March 12, 2016 at 9:09 am
Solomon Rutzky (3/12/2016)
Jeff Moden (3/12/2016)
IMHO, what you have to remember is that, no matter what type of function it is, it's still "just" a string manipulation function, which makes it quite a bit different than most table-valued functions. If you give it a single element, then it should return that single element without modification and that includes NULL elements. That's a part of the basic definition of a splitter, relational theory be damned.Well, I certainly do appreciate you so effectively proving my point ;-). And that point, again, is that this issue is being confused by focusing on the input type being a string. This operation is specifically not string manipulation. Yes, "If you give it a single element, then it should return that single element without modification", but NULL is very specifically not an element. If it were, then a NULL could be represented in more than just the first and only position. Empty strings, whether representing a single element (e.g. '' resulting in 1 row of empty string) or found in one or more elements when there is at least one delimiter (e.g. 'a,,b' resulting in 3 rows, the 2nd of which is an empty string) do work this way. But there is no way to represent a NULL in that second position. This is because a NULL cannot be distinguished from an empty string. XML solved this issue by either tagging an element with xsi:nil or not specifying an attribute. This issue also occurs with how data is stored in data pages, which is why there is a Null Bitmap on each row of each data page. NULL is, by definition, not an element.
And let's try to not complicate a good thing. Almost everyone who has ever made a splitter function has included two and only two parameters... the string to be split and the delimiter to split it on. Nothing more... nothing less.
What others have done, while insightful, has absolutely zero bearing on the correctness or appropriateness of those actions. And adding parameters to increase flexibility is not only a good thing, but it is exactly the thing that we do ALL of the time when creating software. And since this is now a built-in function, Microsoft can quite easily make any additional parameters optional (through overloads) just like they do all of the time, leaving the simple 2 parameter usage just as valid as any other combination of input parameters.
Wow... you certainly know how to make simple things complicated for something so simple. And, what others have done is a strong indication of what the need is. Sometimes, zealous relational purity, although possible correct, isn't always "appropriate". Like I said, relational theory be damned, this is nothing more than a string function. But, if you want to go the route of relational theory, then this would be a good addition to cover a missing hole in SQL Server. As Tom called it, it would be the theory of NULL relationships.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 12, 2016 at 11:07 am
TomThomson (3/11/2016)
Certainly we want to get the numbering added to this splitter in SQL 2016, but we ought to find way of avoiding treating NULL as a value rather than as an indicator that we don't have a value. If the splitter were a TVF this would be easy: NULL return means the function was asked to split something that isn't a value, so it can't return any value and must return null (and empty table returned means no rows if we need that result); or maybe an extra parameter indicating what to do with NULL as someone else suggested.
When treating NULL as an "empty string" instead of "no string" and thus return rows for it, it is going to complicate and limits its use for serialization/de-serialization tasks (which is like its primary use).
I also like to have the ordinal position, the sub-string start position for each item and the sub-string length.
The split/join value should also allow for more then one character in length and for a zero length value as well.
When provided an empty split/join value, treat the space between characters as an invisible split/join value (splitting "abc" returns 3 rows with "a", "b" and "c" as values).
March 12, 2016 at 2:20 pm
I wonder if this new function voids the use of parallelism in the engine like a Scalar or Multi-statement TVF does ...
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 15 posts - 796 through 810 (of 990 total)
You must be logged in to reply to this topic. Login to reply