0

I have a table with the primary key param "code" typed as string. The key could be numeric or alphanumeric. The idea is to write a query that gives the max value among the numeric values of the keys inside a range.

Consider a table called FooTable with these values as key for these records: "abc", "def", "ghi", "10", "12", "30".

A first try without the range clause works and it's the following:

SELECT MAX(Ex.code) AS maxValue FROM
    (SELECT CAST(code AS int) AS code FROM FooTable WHERE ISNUMERIC(code) = 1) AS Ex

Result: 1 record with value 30 under column maxValue.

Then I inserted the WHERE clause with the range and SQL Server printed this error:

SELECT MAX(Ex.code) FROM
    (SELECT CAST(code AS int) AS code FROM FooTable WHERE ISNUMERIC(code) = 1) AS Ex
WHERE Ex.code < 13

Error: Error: Msg 245 – Conversion failed when converting the varchar value ‘abc’ to data type int.

How is it possible that the external query uses the FooTable and not the FROM nested query result set?

Note: This alternative solution works but it uses more than one operation.

DECLARE @Ex TABLE (code int);
INSERT INTO @Ex SELECT CAST(code AS int) AS code FROM FooTable WHERE ISNUMERIC(code) = 1;
SELECT max(code) AS maxValue FROM @Ex WHERE code < 13;

Result: 1 record with value 12 under column maxValue.

Thanks in advance for the answers.

Dale K
  • 25,246
  • 15
  • 42
  • 71
BurnAsIce
  • 25
  • 4
  • 1
    Rather than `ISNUMERIC`, which is a *terrible* function, use `TRY_CONVERT` (in the `SELECT`). – Thom A Mar 03 '23 at 10:57
  • 1
    As for why, this is because SQL Server is applying the `WHERE` from the outer query (`Ex.code < 13`) prior to the evaluation of the derived table. This results in an implicit conversion, which then fails. In truth, if you want to treat numerical data as a numerical value, then you should not be using a `varchar`. – Thom A Mar 03 '23 at 10:58
  • `ISNUMERIC()` != `ISRELIABLE()`. Consider stupid cases such as `select ISNUMERIC('.')` returning 1. You'd be better off using `TRY_CAST()` or `TRY_CONVERT()` instead of `CAST()`. – AlwaysLearning Mar 03 '23 at 11:01
  • @AlwaysLearning this is clear. The problem is the nested query returns the right result set, the numeric codes casted as integer. – BurnAsIce Mar 03 '23 at 11:10

1 Answers1

1

When I use TRY_CAST it works:

DECLARE @FooTable TABLE
(
    code VARCHAR(10)
);

INSERT INTO @FooTable
(
    code
)
VALUES
('abc'),
('def'),
('ghi'),
('10'),
('12'),
('30');

SELECT MAX(Ex.code) AS maxValue
FROM
(
    SELECT TRY_CAST(code AS INT) AS code
    FROM @FooTable
) AS Ex
WHERE ex.code < 13
Peter
  • 475
  • 1
  • 8
  • Amazing, it works. A question: why my solution doesn't work? – BurnAsIce Mar 03 '23 at 11:07
  • I think because of the reliability of the is_number, but I am not sure about that, while TRY_CAST in this case always gives back a value, the CAST can give an error. – Peter Mar 03 '23 at 11:09
  • 2
    SQL Server does not execute a query verbatim to how it is written, the optimizer can and does re-order how it evalutes operations in order to reduce rows as soon as possible; the predicate is being applied to rows *before* your cast, forcing an implicit conversion and the error. – Stu Mar 03 '23 at 11:20
  • Sounds legit for me :) – Peter Mar 03 '23 at 11:20
  • @stu, I have heard about that before. Do you have any links to sources describing the issue? (I have searched without any success.) – jarlh Mar 03 '23 at 12:05
  • @jarlh https://stackoverflow.com/a/73138433/61305 – Aaron Bertrand Mar 03 '23 at 12:40
  • @AaronBertrand, thank you for the link. Are there are any (official) MS pages describing this? – jarlh Mar 04 '23 at 20:58
  • Look at articles by Paul White who knows the internals better than the back of his hand and writes some excellent articles [such as these](https://www.sql.kiwi/2012/04/query-optimizer-deep-dive-part-1.html) – Stu Mar 04 '23 at 21:10
  • @jarlh [This one maybe](https://learn.microsoft.com/en-us/sql/relational-databases/performance/subqueries?view=sql-server-ver16), is probably as close as you're going to get from official sources (you get more out of Paul's to be quite honest). It says: `(In some Transact-SQL statements, the subquery can be evaluated as if it were an independent query. Conceptually, the subquery results are substituted into the outer query (although this isn't necessarily how SQL Server actually processes Transact-SQL statements with subqueries).` – Aaron Bertrand Mar 04 '23 at 21:14
  • @jarlh [This post is not official either](https://sqlblog.org/2020/03/19/illogical-errors-can-be-a-factor-of-the-plan) and [neither is this one](https://dba.stackexchange.com/a/115952/1186), but both reference a response from Microsoft [about the issue here](https://feedback.azure.com/d365community/idea/d54d50d8-6f25-ec11-b6e6-000d3a4f0da0) - the previous version of that bug report was a lot more useful, but it got "cleaned up" as part of the migration from death of Connect. – Aaron Bertrand Mar 04 '23 at 21:20