sql-server - example - select coalesce sql server

El valor se devuelve en lugar de NULL cuando se usa la función con APLICACIÓN EXTERNA

Question

Estoy obteniendo resultados extraños cuando uso la función en línea. Aquí está el código:

IF EXISTS (
SELECT * FROM sys.objects AS o WHERE name = ''vendor_relation_users''
) DROP FUNCTION dbo.vendor_relation_users;
GO
CREATE FUNCTION [dbo].[vendor_relation_users]
(
@user_name CHAR(12)
)
RETURNS TABLE
AS
RETURN (SELECT @user_name AS user_name WHERE @user_name NOT LIKE ''06%'');
GO
DECLARE @u CHAR(12) = ''066BDLER''
SELECT a.user_name, is_v.user_name
FROM (SELECT @u AS user_name) a
OUTER APPLY [dbo].[vendor_relation_users](@u) AS is_v
SELECT a.user_name, is_v.user_name
FROM (SELECT @u AS user_name) a
OUTER APPLY (SELECT @u AS user_name WHERE @u NOT LIKE ''06%'') AS is_v
SELECT * FROM [dbo].[vendor_relation_users](@u)

Así que en la primera instrucción SELECT acabo de APLICAR LA FUNCIÓN y devuelve el resultado.

En la siguiente declaración, tomé el código de la función y lo puse directamente en la instrucción OUTER APPLY.

Y la última afirmación es solo la llamada a la función directa.

No puedo entender por qué la PRIMERA consulta devuelve el valor ...

Answer 1

Esta es una consulta muy interesante. El comportamiento de su primera consulta depende de si utiliza OPTION (RECOMPILE) o no.

Como usted señala, esto:

DECLARE @u CHAR(12) = ''066BDLER''
SELECT a.user_name, is_v.user_name
FROM (SELECT @u AS user_name) a
OUTER APPLY [dbo].[vendor_relation_users](@u) AS is_v

devuelve esto:

user_name user_name
066BDLER 066BDLER

pero si agrega OPTION (RECOMPILE) esta manera:

SELECT a.user_name, is_v.user_name
FROM (SELECT @u AS user_name) a
OUTER APPLY [dbo].[vendor_relation_users](@u) AS is_v
OPTION (RECOMPILE) 

correctamente obtienes esto:

user_name user_name
066BDLER NULL

Sospecho que esto se debe a un error en la forma en que el optimizador de consultas cortocircuita estas funciones en línea debido a las estimaciones de cardinalidad. Si observa el plan de consultas para las dos consultas, verá que el que no tiene OPTION RECOMPILE solo devuelve una constante.

Answer 2

Supongo que es un viejo bicho descrito here . Y está Closed as Won''t Fix

Por favor use esta función como:

SELECT a.user_name, is_v.user_name
FROM (SELECT @u AS user_name) a
OUTER APPLY (
SELECT *
FROM [dbo].[vendor_relation_users](a.user_name)
) AS is_v

ACTUALIZACIÓN # 1

Solo lee los comentarios:

Podría ser el mismo problema básico que aquí .com/a/32414450/73226 - Martin Smith

¡Eso es! Mismo problema, mismo enlace que he proporcionado al sitio de MS Connect.

ACTUALIZACIÓN # 2

En lugar:

RETURN (SELECT @user_name AS user_name WHERE @user_name NOT LIKE ''06%'');

Necesitas usar:

RETURN (SELECT CASE WHEN @user_name LIKE ''06%'' THEN NULL ELSE @user_name END)

Answer 3

CREATE FUNCTION [dbo].[vendor_relation_users]
(
@user_name CHAR(12)
)
RETURNS TABLE
AS
RETURN (SELECT CASE WHEN @user_name NOT LIKE ''06%'' THEN @user_name ELSE NULL END as [user_name]);
GO

Bien, https://docs.microsoft.com/en-us/sql/t-sql/language-elements/select-local-variable-transact-sql

Si la instrucción SELECT no devuelve ninguna fila, la variable conserva su valor presente. Si expresión es una subconsulta escalar que no devuelve ningún valor, la variable se establece en NULL.

Por ejemplo

DECLARE @u CHAR(12) = ''066BDLER''
SELECT @u = ''AAAA'' WHERE 1 <> 1
SELECT @u

Bajo cualquier condición, el valor de SELECT @variable no cambiará, y DB ENGINE simplifica la declaración para

| - Exploración constante

Si usa RECOMPILE sql, cree un nuevo plan con condiciones y cambios en el plan

 |--Compute Scalar(DEFINE:([Expr1001]=''066BDLER ''))
|--Filter(WHERE:(STARTUP EXPR(NOT ''066BDLER '' like ''06%'')))
|--Constant Scan

Pero RECOMPILE requiere costos de compilación, luego use CASE para la devolución

ps. Si no cambia la función, puede omitir la asignación, de lo contrario, ¿por qué se APLICARÁ :)

DECLARE @u CHAR(12) = ''066BDLER''
SELECT a.user_name, is_v.user_name
FROM (SELECT @u AS user_name) a
OUTER APPLY [dbo].[vendor_relation_users](a.user_name) AS is_v