幻象值轉換中查詢結果的奇怪優化

-- DB collation is German_PhoneBook_BIN
CREATE TABLE [dbo].[partition_tbl](
   [tbl_name] [varchar](255) NOT NULL,
) ON [PRIMARY]
INSERT INTO partition_tbl VALUES ('some_table_{num}')
INSERT INTO partition_tbl VALUES ('other_table_{num}')

假設您的數據庫中有以下表格

some_table_123
some_table_444
some_table_hist
some_table_555
other_table_100
other_table_4321

以下聲明

SELECT a.tbl_name, REVERSE(LEFT(REVERSE(b.name),PATINDEX('%[_]%',REVERSE(b.name))-1)) AS number
FROM partition_tbl a
INNER JOIN dbo.sysobjects b 
ON (
   b.name COLLATE German_PhoneBook_BIN LIKE REPLACE(REPLACE(a.tbl_name,'_','[_]'),'{num}','[0-9][0-9][0-9]')
   OR
   b.name COLLATE German_PhoneBook_BIN LIKE REPLACE(REPLACE(a.tbl_name,'_','[_]'),'{num}','[0-9][0-9][0-9][0-9]')
) AND b.xtype='U'

應該返回

tbl_name          | number
------------------|-------
some_table_{num}  | 123
some_table_{num}  | 444
some_table_{num}  | 555
other_table_{num} | 100
other_table_{num} | 4321

可以看到，some_table_hist省略了。但是為什麼我不能將列number（此時是 a ）轉換為使用以下兩個語句NVARCHAR鍵入？INT

SELECT a.tbl_name, CAST(REVERSE(LEFT(REVERSE(b.name),PATINDEX('%[_]%',REVERSE(b.name))-1)) AS INT) AS number
FROM partition_tbl a
INNER JOIN dbo.sysobjects b 
ON (
   b.name COLLATE German_PhoneBook_BIN LIKE REPLACE(REPLACE(a.tbl_name,'_','[_]'),'{num}','[0-9][0-9][0-9]')
   OR
   b.name COLLATE German_PhoneBook_BIN LIKE REPLACE(REPLACE(a.tbl_name,'_','[_]'),'{num}','[0-9][0-9][0-9][0-9]')
) AND b.xtype='U'

和

SELECT tbl_name,CAST(number AS INT) AS number FROM (
   SELECT a.tbl_name, REVERSE(LEFT(REVERSE(b.name),PATINDEX('%[_]%',REVERSE(b.name))-1)) AS number
   FROM partition_tbl a
   INNER JOIN dbo.sysobjects b 
   ON (
       b.name COLLATE German_PhoneBook_BIN LIKE REPLACE(REPLACE(a.tbl_name,'_','[_]'),'{num}','[0-9][0-9][0-9]')
       OR
       b.name COLLATE German_PhoneBook_BIN LIKE REPLACE(REPLACE(a.tbl_name,'_','[_]'),'{num}','[0-9][0-9][0-9][0-9]')
   ) AND b.xtype='U'
) x

這兩個語句都會導致錯誤：將 nvarchar 值轉換hist為 int 數據類型失敗（德語中的真正錯誤說：）Fehler beim Konvertieren des nvarchar-Wertes 'hist' in den int-Datentyp。嗯？我在上面的結果集中看不到任何hist價值！？MSSQL 是否做了錯誤的語句優化？

（順便說一句：我現在正在使用另一個解決此問題的語句。我只對出現這種奇怪效果的原因感興趣）

正如預期的那樣。

SQL 是聲明性的，沒有隱式的執行順序。您將看到 CAST 在過濾器之前發生，因此您會收到錯誤消息。也就是說，您假設只有 int 值會通過 CAST。是

您應該假設列中的所有值都將轉換為 int。這裡的值是已經解析的“hist”。

有幾種方法可以確保只有 CASTable 值會呈現給 CAST

1. 實現 JOIN，因此它必須在 CAST 之前執行
2. 使用 CASE 管理 CAST（見下文）
3. 使用表變數/臨時表來記憶體結果

例子。您不能依賴派生表之後發生的 CAST。

SELECT
   tbl_name,
   CASE WHEN num NOT LIKE '[^0-9]' THEN CAST(num AS int) END
FROM
   (
   SELECT 
       a.tbl_name,
       CAST(REVERSE(LEFT(REVERSE(b.name),PATINDEX('%[_]%',REVERSE(b.name))-1)) AS num
   FROM 
       partition_tbl a
       INNER JOIN 
       sys.objects b ON
                b.name COLLATE German_PhoneBook_BIN 
                       NOT LIKE REPLACE(REPLACE(a.tbl_name,'_','[_]'),'{num}','[^0-9]')
   WHERE
       b.xtype='U'
   ) T

引用自：https://dba.stackexchange.com/questions/10932