Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Странная ошибка в табличной функции  [new]
m71
Member

Откуда: Красноярск
Сообщений: 122
Подскажите, куда можно копать в такой ситуации.

Есть табличная функция, которая иногда стала выдавать не то, что должна:

ALTER FUNCTION [dbo].[fnRKCAccrEvidInd](@UOrgID decimal(16,4), @BldID bigint, @D1 smalldatetime, @D2 smalldatetime)
RETURNS @R TABLE (
...
)
AS 
BEGIN  
  INSERT INTO @R
  SELECT ...

  RETURN;
END

Делаем запрос

SELECT * FROM dbo.fnRKCAccrEvidInd(2461224479, 2400001000083204900, '20171201', '20171231')

Получаем результат, но вместо значений в некоторых строках почему-то нули.

Начали разбираться, убили много времени.
Определили, что на ошибку влияет входной параметр @D2 smalldatetime, который как-то не так попадает в функцию

Делаем такой бессмысленный финт: параметр @D2 переименовываем на @D2x,
декларируем @D2 в теле функции, делаем SET @D2 = @D2x

ALTER FUNCTION [dbo].[fnRKCAccrEvidInd](@UOrgID decimal(16,4), @BldID bigint, @D1 smalldatetime, @D2x smalldatetime)
RETURNS @R TABLE (
...
)
AS 
BEGIN  
  DECLARE @D2 smalldatetime
  SET @D2 = @D2x

  --дальше все тоже самое, ничего не меняем
  INSERT INTO @R
  SELECT ...

  RETURN;
END

Вот теперь все работает правильно!
Добавили в функцию бредовое ненужное действие и получили правильный результат.
Как такое может быть?

Таких функций в проекте очень много. И где оно опять выстрелит...
Подскажите, что делать и в чем может быть ошибка?
6 мар 18, 10:51    [21239823]     Ответить | Цитировать Сообщить модератору
 Re: Странная ошибка в табличной функции  [new]
iap
Member

Откуда: Москва
Сообщений: 46977
Не совсем по теме, но если в функции один SELECT, то почему не сделать инлайн-функцию?
6 мар 18, 11:02    [21239856]     Ответить | Цитировать Сообщить модератору
 Re: Странная ошибка в табличной функции  [new]
m71
Member

Откуда: Красноярск
Сообщений: 122
В проекте много таких функций, они иногда меняются. Где-то было много Select-ов, стал один, где-то наоборот.
В критичных местах переделываем. Ну да, наверное это не по теме.
6 мар 18, 11:35    [21239967]     Ответить | Цитировать Сообщить модератору
 Re: Странная ошибка в табличной функции  [new]
buser
Member

Откуда: Санкт-Петербург
Сообщений: 4537
m71, Каст между varchar'ами и различными date/datetime/smalldatetime... имеет место быть?
6 мар 18, 11:47    [21239996]     Ответить | Цитировать Сообщить модератору
 Re: Странная ошибка в табличной функции  [new]
Гигабайт Мегабайтович Килобайтов
Member [заблокирован]

Откуда:
Сообщений: 5975
действительно нет ли где не явного преоброзования (и не обязательно из varchar)
6 мар 18, 11:52    [21240019]     Ответить | Цитировать Сообщить модератору
 Re: Странная ошибка в табличной функции  [new]
Кусочек
Guest
SELECT * FROM dbo.fnRKCAccrEvidInd(2461224479, 2400001000083204900, N'20171201', N'20171231')


А такой вызов как-то влияет на результат?
6 мар 18, 12:08    [21240067]     Ответить | Цитировать Сообщить модератору
 Re: Странная ошибка в табличной функции  [new]
хм-хм-хм
Guest
дайте уже полный текст этой функции
6 мар 18, 12:09    [21240071]     Ответить | Цитировать Сообщить модератору
 Re: Странная ошибка в табличной функции  [new]
m71
Member

Откуда: Красноярск
Сообщений: 122
Кусочек
SELECT * FROM dbo.fnRKCAccrEvidInd(2461224479, 2400001000083204900, N'20171201', N'20171231')


А такой вызов как-то влияет на результат?


Не влияет
6 мар 18, 13:16    [21240302]     Ответить | Цитировать Сообщить модератору
 Re: Странная ошибка в табличной функции  [new]
m71
Member

Откуда: Красноярск
Сообщений: 122
хм-хм-хм
дайте уже полный текст этой функции


+
CREATE FUNCTION [dbo].[fnRKCAccrEvidInd](@UOrgID decimal(16,4), @BldID bigint, @D1 smalldatetime, @D2 smalldatetime)
RETURNS @R TABLE 
(
  cdate date NOT NULL,
  acc_id decimal(19,7) NOT NULL,
  ptype_id tinyint NOT NULL,
  cvol2_11 decimal(12,6),
  cvol2_21 decimal(12,6),
  cvol2_22 decimal(12,6),
  cvol2_31 decimal(12,6),
  cvol2_32 decimal(12,6),
  cvol2_33 decimal(12,6),
  cvol2 AS ISNULL(cvol2_11,0.0) + ISNULL(cvol2_21,0.0) + ISNULL(cvol2_22,0.0) + ISNULL(cvol2_31,0.0) + ISNULL(cvol2_32,0.0) + ISNULL(cvol2_33,0.0)
  PRIMARY KEY CLUSTERED (cdate, acc_id, ptype_id)
)
AS 
BEGIN  

  INSERT INTO @R

  SELECT 

  T4.cdate,  
  T4.acc_id,
  T4.ptype_id,
  cvol2_11 = SUM(T4.dvol_11) OVER (PARTITION BY T4.acc_id, T4.ptype_id ORDER BY T4.acc_id, T4.ptype_id),
  cvol2_21 = SUM(T4.dvol_21) OVER (PARTITION BY T4.acc_id, T4.ptype_id ORDER BY T4.acc_id, T4.ptype_id),
  cvol2_22 = SUM(T4.dvol_22) OVER (PARTITION BY T4.acc_id, T4.ptype_id ORDER BY T4.acc_id, T4.ptype_id),
  cvol2_31 = SUM(T4.dvol_32) OVER (PARTITION BY T4.acc_id, T4.ptype_id ORDER BY T4.acc_id, T4.ptype_id),
  cvol2_32 = SUM(T4.dvol_32) OVER (PARTITION BY T4.acc_id, T4.ptype_id ORDER BY T4.acc_id, T4.ptype_id),
  cvol2_33 = SUM(T4.dvol_33) OVER (PARTITION BY T4.acc_id, T4.ptype_id ORDER BY T4.acc_id, T4.ptype_id)

  FROM (
 
  SELECT
  T3.cdate,
  T3.acc_id,
  T3.ptype_id,
  points_count = COUNT(T3.point_id),
  evid_count = SUM(CASE WHEN ctar_id IS NULL THEN 0 ELSE 1 END),
  
  dvol_11 = SUM(CASE WHEN T3.ctar_id = 1 THEN CASE WHEN T3.clevel_id = 2 THEN T3.dvol_1 * (CASE WHEN T3.INHS = 0 THEN T3.ashare_a ELSE CAST(T3.INH as decimal(5,1)) / CAST(T3.INHS as decimal(5,1)) END) ELSE T3.dvol_1 END ELSE NULL END),
  dvol_21 = SUM(CASE WHEN T3.ctar_id = 2 THEN CASE WHEN T3.clevel_id = 2 THEN T3.dvol_1 * (CASE WHEN T3.INHS = 0 THEN T3.ashare_a ELSE CAST(T3.INH as decimal(5,1)) / CAST(T3.INHS as decimal(5,1)) END) ELSE T3.dvol_1 END ELSE NULL END),
  dvol_22 = SUM(CASE WHEN T3.ctar_id = 2 THEN CASE WHEN T3.clevel_id = 2 THEN T3.dvol_2 * (CASE WHEN T3.INHS = 0 THEN T3.ashare_a ELSE CAST(T3.INH as decimal(5,1)) / CAST(T3.INHS as decimal(5,1)) END) ELSE T3.dvol_2 END ELSE NULL END),
  dvol_31 = SUM(CASE WHEN T3.ctar_id = 3 THEN CASE WHEN T3.clevel_id = 2 THEN T3.dvol_1 * (CASE WHEN T3.INHS = 0 THEN T3.ashare_a ELSE CAST(T3.INH as decimal(5,1)) / CAST(T3.INHS as decimal(5,1)) END) ELSE T3.dvol_1 END ELSE NULL END),
  dvol_32 = SUM(CASE WHEN T3.ctar_id = 3 THEN CASE WHEN T3.clevel_id = 2 THEN T3.dvol_2 * (CASE WHEN T3.INHS = 0 THEN T3.ashare_a ELSE CAST(T3.INH as decimal(5,1)) / CAST(T3.INHS as decimal(5,1)) END) ELSE T3.dvol_2 END ELSE NULL END),
  dvol_33 = SUM(CASE WHEN T3.ctar_id = 3 THEN CASE WHEN T3.clevel_id = 2 THEN T3.dvol_3 * (CASE WHEN T3.INHS = 0 THEN T3.ashare_a ELSE CAST(T3.INH as decimal(5,1)) / CAST(T3.INHS as decimal(5,1)) END) ELSE T3.dvol_3 END ELSE NULL END)

  FROM (

  SELECT
  CL.cdate,
  PA.flat_id,
  T2.acc_id,
  T2.ptype_id,
  T2.point_id,
  T2.clevel_id,
  T2.ctar_id,
  T2.dvol_1,
  T2.dvol_2, 
  T2.dvol_3,
  PR.INH,
  PR.INHS,
  ashare_a = ISNULL(PP.pacc_share,1.0),
  pacc_share = ISNULL(PP.pacc_share,1.0)

  FROM Calendar CL

  INNER JOIN (

  SELECT 
  T1.acc_id,
  T1.point_id,
  T1.clevel_id,
  T1.ptype_id,
  T1.pbeg_date,
  T1.pend_date,
  T1.levi_date,
  T1.evid_date,
  T1.ctar_id,   
  dvol_1 = CASE WHEN T1.rep_date = @D1 THEN CAST(T1.vol_1 / T1.days_cnt AS decimal(20,11)) ELSE 0.0 END, 
  dvol_2 = CASE WHEN T1.rep_date = @D1 THEN CAST(T1.vol_2 / T1.days_cnt AS decimal(20,11)) ELSE 0.0 END,  
  dvol_3 = CASE WHEN T1.rep_date = @D1 THEN CAST(T1.vol_3 / T1.days_cnt AS decimal(20,11)) ELSE 0.0 END 
  FROM (
  
  SELECT
  PA.acc_id,
  CP.point_id,
  CP.clevel_id,
  CP.ptype_id,
  pbeg_date = CP.beg_date, 
  pend_date = ISNULL(CP.end_date,'20400101'), 
  CE.ctar_id,   
  CE.levi_date,  
  rep_date = dbo.fnMonthFirstDay(CE.evid_date),
  CE.evid_date,
  CE.vol_1,
  CE.vol_2, 
  CE.vol_3,
  days_cnt = DATEDIFF(day,dbo.MaxDate(CE.levi_date,dbo.fnMonthFirstDay(CE.evid_date)),CE.evid_date) + 1
  FROM Pers_accounts PA 
  INNER JOIN (--так быстрее в 10 раз
  SELECT 
  PA.acc_id,
  CP.point_id
  FROM Counter_points CP
  INNER JOIN Pers_accounts PA ON (CP.clevel_id = 2 AND CP.build_id = PA.build_id AND CP.flat_id = PA.flat_id)
  UNION
  SELECT 
  PA.acc_id,
  CP.point_id
  FROM Counter_points CP
  INNER JOIN Pers_accounts PA ON (CP.clevel_id = 3 AND CP.acc_id = PA.acc_id)
  ) MP ON (PA.acc_id = MP.acc_id)
  INNER JOIN Counter_points CP ON (MP.point_id = CP.point_id)
  LEFT OUTER JOIN (    
	SELECT 
    CN.point_id,
    CN.count_id,
	CS.ctar_id,
    levi_date = LAG(CE.evid_date,1,CN.beg_date) OVER (PARTITION BY CE.count_id ORDER BY CE.count_id),
    CE.evid_date,
	CE.vol_1,
    CE.vol_2, 
    CE.vol_3
    FROM Counter_evid CE
	INNER JOIN Counters CN ON (CE.count_id = CN.count_id)
	INNER JOIN Counter_points CP ON (CP.point_id = CN.point_id)
	INNER JOIN Counter_series CS ON (CN.cseries_id = CS.cseries_id)
	WHERE CE.evid_date >= @D1
	  AND CE.evid_date <= ISNULL(CN.ncheck_date,'20400101') --дата поверки
	  AND CE.evid_date BETWEEN CP.beg_date AND ISNULL(CP.end_date,'20400101')
	  AND CE.evid_date BETWEEN CN.beg_date AND ISNULL(CN.end_date,'20400101')
	  AND CN.beg_date <= @D2 AND ISNULL(CN.end_date,'20400101') >= @D1	  	  
    ) CE ON (CP.point_id = CE.point_id)

  WHERE (CP.beg_date <= @D2) AND (ISNULL(CP.end_date,'20400101') >= @D1)
    AND (PA.build_id = @BldID)	
   
  ) T1
  
  ) T2 ON ((T2.evid_date IS NULL) OR (CL.cdate BETWEEN T2.levi_date AND T2.evid_date))
	AND (CL.cdate BETWEEN T2.pbeg_date AND T2.pend_date)

  INNER JOIN Pers_accounts PA ON (T2.acc_id = PA.acc_id)
    AND (PA.beg_date <= CL.cdate AND ISNULL(PA.end_date,'20400101') >= CL.cdate)
  
  LEFT OUTER JOIN PAcc_params PP ON (
	(T2.acc_id = PP.acc_id) AND	
	(PP.beg_date = (SELECT MAX(beg_date) FROM PAcc_params WHERE (acc_id = T2.acc_id) AND beg_date <= CL.cdate))
	) 
	
  LEFT OUTER JOIN (
    SELECT
	PR.cdate,
	PR.acc_id,
	INH = ISNULL(PR.INH,0),
    INHS = ISNULL((SUM(INH) over(PARTITION BY PR.cdate, PA.flat_id)),0)
	FROM dbo.fnRKCAccrRegM(@UOrgID,@BldID,@D1,@D2) PR
	INNER JOIN Pers_accounts PA ON (PR.acc_id = PA.acc_id)
	) PR ON (CL.CDate = PR.cdate) AND (T2.acc_id = PR.acc_id)  

  WHERE (CL.cdate BETWEEN @D1 AND @D2)
    AND (PA.org_id = @UOrgID) 

  ) T3

  GROUP BY T3.cdate, T3.acc_id, T3.ptype_id

  ) T4

  WHERE T4.points_count = T4.evid_count

  RETURN;
END


Сообщение было отредактировано: 6 мар 18, 13:53
6 мар 18, 13:18    [21240306]     Ответить | Цитировать Сообщить модератору
 Re: Странная ошибка в табличной функции  [new]
хм-хм-хм
Guest
ну и код функции dbo.fnRKCAccrRegM тоже давайте
6 мар 18, 13:21    [21240314]     Ответить | Цитировать Сообщить модератору
 Re: Странная ошибка в табличной функции  [new]
buser
Member

Откуда: Санкт-Петербург
Сообщений: 4537
и главное, где то, что "в некоторых строках почему-то нули"
6 мар 18, 13:26    [21240325]     Ответить | Цитировать Сообщить модератору
 Re: Странная ошибка в табличной функции  [new]
m71
Member

Откуда: Красноярск
Сообщений: 122
Результат правильный:
2017-12-01 2461224479.3000090 1 2.000000 NULL NULL NULL NULL NULL 2.000000
2017-12-01 2461224479.3000090 2 1.000000 NULL NULL NULL NULL NULL 1.000000
2017-12-02 2461224479.3000090 1 2.000000 NULL NULL NULL NULL NULL 2.000000
2017-12-02 2461224479.3000090 2 1.000000 NULL NULL NULL NULL NULL 1.000000
2017-12-03 2461224479.3000090 1 2.000000 NULL NULL NULL NULL NULL 2.000000
2017-12-03 2461224479.3000090 2 1.000000 NULL NULL NULL NULL NULL 1.000000
2017-12-04 2461224479.3000090 1 2.000000 NULL NULL NULL NULL NULL 2.000000
2017-12-04 2461224479.3000090 2 1.000000 NULL NULL NULL NULL NULL 1.000000

Результат, который выдает функция:
2017-12-01 2461224479.3000090 1 0.000000 NULL NULL NULL NULL NULL 0.000000
2017-12-01 2461224479.3000090 2 1.000000 NULL NULL NULL NULL NULL 1.000000
2017-12-02 2461224479.3000090 1 0.000000 NULL NULL NULL NULL NULL 0.000000
2017-12-02 2461224479.3000090 2 1.000000 NULL NULL NULL NULL NULL 1.000000
2017-12-03 2461224479.3000090 1 0.000000 NULL NULL NULL NULL NULL 0.000000
2017-12-03 2461224479.3000090 2 1.000000 NULL NULL NULL NULL NULL 1.000000
2017-12-04 2461224479.3000090 1 0.000000 NULL NULL NULL NULL NULL 0.000000
2017-12-04 2461224479.3000090 2 1.000000 NULL NULL NULL NULL NULL 1.000000

Кол-во строк совпадает, но через строчку данные обнуляются

Вообще, я самого главного не могу понять. Чем @D2 smalldatetime в заголовке функции отличается от того же самого в ее теле
6 мар 18, 13:33    [21240352]     Ответить | Цитировать Сообщить модератору
 Re: Странная ошибка в табличной функции  [new]
m71
Member

Откуда: Красноярск
Сообщений: 122
хм-хм-хм
ну и код функции dbo.fnRKCAccrRegM тоже давайте


CREATE FUNCTION dbo.fnRKCAccrRegM(@UOrgID decimal(16,4), @BldID bigint, @D1 date, @D2 date)
RETURNS @R TABLE 
(
    cdate date NOT NULL,
	acc_id decimal(19,7) NOT NULL,
	INH smallint --проживающих на лицевом
	PRIMARY KEY CLUSTERED (cdate, acc_id)
)
AS 
BEGIN  
  INSERT INTO @R
    
  SELECT 
  CL.cdate,
  PA.acc_id,
  INH = CASE WHEN ISNULL(T2.INH,0) = 0 THEN ISNULL(T1.INH,0) ELSE ISNULL(T2.INH,0) END
  FROM Calendar CL
  INNER JOIN Pers_accounts PA ON (CL.cdate BETWEEN PA.beg_date AND ISNULL(PA.end_date,'20400101'))

  LEFT OUTER JOIN (
  SELECT 
  CL.cdate,
  PA.acc_id,
  INH = (COUNT(ISNULL(PR.man_id,0)))
  FROM Calendar CL
  INNER JOIN Pers_accounts PA ON (CL.cdate BETWEEN PA.beg_date AND ISNULL(PA.end_date,'20400101'))
  INNER JOIN PAcc_own PO ON (PA.acc_id = PO.acc_id)
  INNER JOIN PAcc_own_rec PR ON (PO.own_id = PR.own_id) AND (CL.cdate BETWEEN PR.beg_date AND ISNULL(PR.end_date,'20400101'))
  WHERE (CL.cdate BETWEEN @D1 AND @D2) AND (PA.build_id = @BldID)
    AND (PA.org_id = @UOrgID)
    AND (PA.beg_date <= CL.cdate AND ISNULL(PA.end_date,'20400101') >= CL.cdate)
    AND (PR.deleted = 0)
  GROUP BY CL.cdate, PA.acc_id
  ) T1 ON CL.cdate = T1.cdate AND PA.acc_id = T1.acc_id

  LEFT OUTER JOIN (
  SELECT 
  CL.cdate,
  PA.acc_id,
  INH = (COUNT(ISNULL(RR.man_id,0)))
  FROM Calendar CL
  INNER JOIN Pers_accounts PA ON (CL.cdate BETWEEN PA.beg_date AND ISNULL(PA.end_date,'20400101'))
  INNER JOIN PAcc_reg PR ON (PA.acc_id = PR.acc_id)
  INNER JOIN PAcc_reg_rec RR ON (PR.reg_id = RR.reg_id) 
    AND (CL.cdate BETWEEN RR.beg_date AND (CASE WHEN RR.estat_id = 1 THEN ISNULL(RR.end_date,'20400101') ELSE '20400101' END))
    AND NOT EXISTS (
	   SELECT * FROM PAcc_reg_rec_miss RM
	   WHERE (RR.reg_id = RM.reg_id)
		AND (RM.beg_date <= CL.cdate AND ISNULL(RM.end_date,'20400101') >= CL.cdate)
	   )	
  WHERE (CL.cdate BETWEEN @D1 AND @D2) AND (PA.build_id = @BldID)  
    AND (PA.beg_date <= CL.cdate AND ISNULL(PA.end_date,'20400101') >= CL.cdate)
    AND (RR.deleted = 0)
	AND (RR.rstat_id = 1)
  GROUP BY CL.cdate, PA.acc_id
  ) T2 ON CL.cdate = T2.cdate AND PA.acc_id = T2.acc_id

  WHERE (CL.cdate BETWEEN @D1 AND @D2) AND (PA.build_id = @BldID)

  RETURN
END
GO 
6 мар 18, 13:41    [21240378]     Ответить | Цитировать Сообщить модератору
 Re: Странная ошибка в табличной функции  [new]
хм-хм-хм
Guest
а если сделать вот так: sp_recompile 'dbo.fnRKCAccrEvidInd', ошибка будет повторяться?
6 мар 18, 13:56    [21240440]     Ответить | Цитировать Сообщить модератору
 Re: Странная ошибка в табличной функции  [new]
лолл
Member

Откуда:
Сообщений: 450
Прошу прощения за оффтоп, но это жуткий говнокод... по три раза обращаемся к одним и тем же таблицам и делаем одни и те же соединения, затем все это конкатенируем и выводим результат в виде табличной функции.... почему бы одинаковые куски кода не оформить в виде представлений/табличных переменных/inline-функций? Зачем читать одни и те же данные многократно? И зачем на каждой таблице проверки дат? неужели нельзя определить минимальный отрезок и один раз его проверить, да еще через сомнительные преобразования ISNULL(...,'20400101').. почему бы не писать IS NULL OR ... ?
6 мар 18, 16:30    [21241019]     Ответить | Цитировать Сообщить модератору
 Re: Странная ошибка в табличной функции  [new]
Кусочек
Guest
m71
Кусочек
SELECT * FROM dbo.fnRKCAccrEvidInd(2461224479, 2400001000083204900, N'20171201', N'20171231')


А такой вызов как-то влияет на результат?


Не влияет


SELECT @@VERSION


Покажите результат запроса.
6 мар 18, 16:37    [21241046]     Ответить | Цитировать Сообщить модератору
 Re: Странная ошибка в табличной функции  [new]
Гигабайт Мегабайтович Килобайтов
Member [заблокирован]

Откуда:
Сообщений: 5975
нус... с таким кодом только "выворачивать фарш назад", т.е. писать тесткейсы, которые отловят параметры, при которых появляются "не правильные данные", а потом "разворачивать назад" всё функции.
6 мар 18, 16:38    [21241048]     Ответить | Цитировать Сообщить модератору
 Re: Странная ошибка в табличной функции  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31355
m71
Определили, что на ошибку влияет входной параметр @D2 smalldatetime, который как-то не так попадает в функцию
А точно @D2, не @D1?
6 мар 18, 23:39    [21242071]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить