August 17, 2023 at 4:52 pm
BEGIN TRY
SELECT * FROM
( SELECT 'int' AS [text] ) AS tmp
ORDER BY
CONVERT (INT, tmp.[text])
PRINT '1'
END TRY
BEGIN CATCH
PRINT '2'
END CATCH
Just had an interesting case today where it seems as if when you unsuccessfully cast text as an INT, then this looks to be a severity level 16 error, which should trigger a TRY-CATCH block, but it just doesn't (it still prints 1 in the above example). My database was in 2008 compatibility mode before, and it worked then (prints 2), but any later compatibility modes, it returns a 1. I haven't managed to find any SQL notes on changes that might have caused this, or any settings I might change to get it to work.
It almost seems like a bug to me, but don't know if anyone has ever encountered this or can explain what is going on here? If I move the conversion to the SELECT part of the statement, then it does trigger the CATCH part, it just doesn't seem to care when it is in the ORDER BY part of the statement.
Any thoughts welcome!
August 17, 2023 at 4:54 pm
Your code does not result in an error, so that explains why the CATCH block code is not executed.
Or is your question 'Why does this code not generate an error?'
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 17, 2023 at 5:00 pm
Ah that's true... my initial case was a bit more complex, and I tried to simplify it to a postable snippet... in my real case, it was actually producing an error when I ran it individually, but in the TRY-CATCH, it wasn't producing the same error. Will need to take a look at creating another case that demonstrates it
August 17, 2023 at 5:09 pm
The reason why the code doesn't produce an error can be found in the execution plan. Apparently, since SQL knows there's only 1 row, it never executes the ORDER BY. There's no sort in the execution plan, which seems to prove that.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 17, 2023 at 5:17 pm
you know, I can't seem to replicate it with a simple example, but the basic premise definitely was this, where my result set ends up saying (I pasted the code in the TRY block again at the bottom of the statement, and then tried to run it, to verify it really did fail:
Warning: Null value is eliminated by an aggregate or other SET operation.
(43 rows affected)
(1 row affected)
1
(43 rows affected)
(1 row affected)
Msg 245, Level 16, State 1, Line 166
Conversion failed when converting the varchar value 'misctext' to data type int.
Completion time: 2023-08-17T18:13:38.8771657+01:00
The fact that it showed a "1" tells me it doesn't fail in the TRY, but then it does fail when outside of the TRY....
Frustrating I can't replicate it with a more simplified example not using my actual database data, maybe I need to look into it more.
August 17, 2023 at 5:23 pm
CREATE TABLE #tmp ([text] VARCHAR(10) )
INSERT INTO #tmp
VALUES ('1')
INSERT INTO #tmp
VALUES ('a3')
CREATE TABLE #tmp2 ([text] varchar(10) )
BEGIN TRY
INSERT #tmp2
SELECT *
FROM #tmp t
ORDER BY
CONVERT (INT, t.[text])
PRINT '1'
END TRY
BEGIN CATCH
PRINT '2'
END CATCH
SELECT *
FROM #tmp t
ORDER BY
CONVERT (INT, t.[text])
DROP TABLE #tmp
DROP TABLE #tmp2
Found it, I apparently need to insert it into another temp table for it to decide to work, even though the base statement doesn't work
so this output gives:
(1 row affected)
(1 row affected)
(2 rows affected)
1
Msg 245, Level 16, State 1, Line 27
Conversion failed when converting the varchar value 'a3' to data type int.
Completion time: 2023-08-17T18:21:51.7008922+01:00
August 17, 2023 at 6:29 pm
The reason why the code doesn't produce an error can be found in the execution plan. Apparently, since SQL knows there's only 1 row, it never executes the ORDER BY. There's no sort in the execution plan, which seems to prove that.
...You're right, I should be looking at the execution plan on this, so it does look like it is just ignoring my ordering statement, which is why the insert works, even though the statement itself fails... I guess I didn't expect it to though, or I at least expected to find some documentation that would say what changed between 2008 and the other versions, where the query plans changed. This functionality seems to persist when there is more than one row, and I think it just logically isn't how I'd expect it to work, I just would have expected that if the base statement made an error, then inserting said statement into a temp table would also not work just as much.
August 17, 2023 at 7:20 pm
Jeff Moden wrote:The reason why the code doesn't produce an error can be found in the execution plan. Apparently, since SQL knows there's only 1 row, it never executes the ORDER BY. There's no sort in the execution plan, which seems to prove that.
...You're right, I should be looking at the execution plan on this, so it does look like it is just ignoring my ordering statement, which is why the insert works, even though the statement itself fails... I guess I didn't expect it to though, or I at least expected to find some documentation that would say what changed between 2008 and the other versions, where the query plans changed. This functionality seems to persist when there is more than one row, and I think it just logically isn't how I'd expect it to work, I just would have expected that if the base statement made an error, then inserting said statement into a temp table would also not work just as much.
You'll get no arguments from me there! 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply