none
באג ב-SQL? RRS feed

  • שאלה

  • ערב טוב

    נתקלתי בסיטואציה מאד מוזרה ובעייתית שגרמה לי נזק לא קטן

    יש לי 2 מסדי נתונים A ו-B והאפליקציה ניגשת תמיד רק ל-B וכתוצאה מכך יצרו Views מ-B שמציגים טבלאות שנמצאים ב-A.

    היה תהליך שעדכן טבלה שנמצאת ב-A דרך ה-View  שנמצא ב-B אלא שהעדכון היה שב-Update קראו לשם המלא של הטבלה a..TbleName וב-From עשו Join דרך ה-View שנמצא ב-B ומפנה לאותה טבלה.


    update a..tableName set ... from b..ViewName INNER JOIN ...



    התוצאה היתה ש-SQL לא זיהה שה-View מתייחס לאותה טבלה שנמצאת בכותרת ה-Update ועידכן את כל הטבלה. ורק כשהגדרתי בשני המופעים את אותה הפניה זה עבד נכון (ב-Plan הוא התייחס לטבלה ול-View כשני אובייקטים נפרדים וממילא מכיוון שהטבלה אינה נמצאת ב-Join הוא עדכן את כל הטבלה!!!!)

    אני מצרף סקריפט לדגומא שמתבססת על טבלאות Northwind

    create database a
    go
    create database b
    go
    use a
    go
    select * 
    into orders
    from northwind.dbo.orders
    go
    use b
    go
    select * 
    into customers
    from northwind.dbo.customers
    go
    use b
    go
    create view orders
    as
    select OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry
    from a.dbo.orders
    go
    
    use b
    go
    begin tran
    
    update a.dbo.orders
    SET [ShipName] = c.CompanyName
    FROM a.dbo.orders o INNER JOIN customers c
    	ON o.CustomerID = c.CustomerID
    where c.CustomerID = 'ALFKI'
    go
    select @@ROWCOUNT--6 rows
    go
    update a.dbo.orders
    SET [ShipName] = c.CompanyName
    FROM orders o /* Local View */ INNER JOIN customers c
    	ON o.CustomerID = c.CustomerID
    where c.CustomerID = 'ALFKI'
    go
    select @@ROWCOUNT--830 rows
    go
    rollback



    • נערך על-ידי Yakovma יום ראשון 23 נובמבר 2014 16:24
    יום ראשון 23 נובמבר 2014 15:59

תשובות

  • הי,

    זאת נקודה מעניינת. אני לא יודע אם זה נחשב באג או לא מבחינת מיקרוסופט או מבחינת ANSI-SQL, אבל זאת בהחלט לא התוצאה הרצויה. הייתי מצפה מה-Query Processor להבין שמדובר באותה טבלה ולייצר Plan כמו בדוגמא הראשונה שלך. דרך אגב, זה קורה גם כשה-View והטבלה נמצאים באותו בסיס נתונים.

    בכל אופן, אם אתה רוצה שהאפליקציה תעבוד רק מול בסיס נתונים B, אז כמו שאתה פונה רק לטבלה מ-A, אתה יכול לפנות רק ל-View ב-B.


    update o
    SET [ShipName] = c.CompanyName
    FROM orders o INNER JOIN customers c
    	ON o.CustomerID = c.CustomerID
    where c.CustomerID = 'ALFKI'

    -----------------------------
    גיא גלנצר
    יועץ ומדריך SQL Server
    Madeira - SQL Server Services
    http://www.madeirasql.com


    • נערך על-ידי Guy GlantserMVP, Moderator יום שני 24 נובמבר 2014 06:35 עיצוב
    • הוצע כתשובה על-ידי Eran Sharvit יום שלישי 25 נובמבר 2014 08:05
    • סומן כתשובה על-ידי Eran Sharvit יום שני 08 דצמבר 2014 08:39
    יום שני 24 נובמבר 2014 06:34
    מנחה דיון
  • * יש הרבה מוקשים בשאילתות מורכבות. ככל שעושים שימוש ביותר אלמנטים מכוננים (אלמנט בתוך אלמנט בתוך אלמנט... וכן הלאה) המורכבות עולה ומנוע המיטות של השרת מספק תוצאה פחות טובה הרבה פעמים ועל אחת כמה וכמה אולי לא התוצאה שהמשורר התכוון אליה. זה המזל שלנו! אחרת המכונות כבר היו מחליפות אותנו ולא היה צריך DBA :-)

    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]

    • סומן כתשובה על-ידי Eran Sharvit יום שני 08 דצמבר 2014 08:40
    יום חמישי 27 נובמבר 2014 23:23
    מנחה דיון

כל התגובות

  • הי,

    זאת נקודה מעניינת. אני לא יודע אם זה נחשב באג או לא מבחינת מיקרוסופט או מבחינת ANSI-SQL, אבל זאת בהחלט לא התוצאה הרצויה. הייתי מצפה מה-Query Processor להבין שמדובר באותה טבלה ולייצר Plan כמו בדוגמא הראשונה שלך. דרך אגב, זה קורה גם כשה-View והטבלה נמצאים באותו בסיס נתונים.

    בכל אופן, אם אתה רוצה שהאפליקציה תעבוד רק מול בסיס נתונים B, אז כמו שאתה פונה רק לטבלה מ-A, אתה יכול לפנות רק ל-View ב-B.


    update o
    SET [ShipName] = c.CompanyName
    FROM orders o INNER JOIN customers c
    	ON o.CustomerID = c.CustomerID
    where c.CustomerID = 'ALFKI'

    -----------------------------
    גיא גלנצר
    יועץ ומדריך SQL Server
    Madeira - SQL Server Services
    http://www.madeirasql.com


    • נערך על-ידי Guy GlantserMVP, Moderator יום שני 24 נובמבר 2014 06:35 עיצוב
    • הוצע כתשובה על-ידי Eran Sharvit יום שלישי 25 נובמבר 2014 08:05
    • סומן כתשובה על-ידי Eran Sharvit יום שני 08 דצמבר 2014 08:39
    יום שני 24 נובמבר 2014 06:34
    מנחה דיון
  • תודה גיא על התשובה

    אני מסכים איתך שמבחינת התחביר זה איננו תחביר אופטימלי, אך הוא אמור להיתמך ולא להגיע למצב שבמקום 2 אובייקטים שקיימים בשאילתה, המנוע מתיחחס לזה כשלושה אובייקטים וליצור בלגן בנתונים (רפואיים... קריטיים למדי!!!)

    עכשיו לך תמצא בכל הקוד איפה כתבו בתחביר הזה שהוא חוקי לחלוטין.

    לפתוח connect? לפנות למיקרוסופט?

    יום שלישי 25 נובמבר 2014 08:47
  • גיא, עוד נקודה מאד חשובה לאור הערתך שזה קורה גם באותו DB

    הבעיה חמורה הרבה יותר. תאר לך שיש לך VIEW שעושה בתוכו JOIN ל-orders ואתה משתמש בו כחלק מ-join של update  וב-update אתה לא מעדכן את ה-view אלא ישירות את הטבלה, במצב זה שוב חוזרת התופעה שהוא לא מזהה שזו אותה טבלה ומתעדכנים לך נתונים בלתי צפויים.

    מצב כזה הוא מאד שכיח כי אתה הרבה פעמים ניגש ל-View כי אתה יודע שהוא עושה כל מיני joinים כדי לוודא תקינות כמו בדוגמא המצורפת שהוא בודק שקיים customer בטבלת customers אבל אתה מעדכן בפועל את הטבלה.

    CREATE view v_orders
    as
    select o.*
    FROM orders o INNER JOIN customers c
    	ON o.CustomerID = c.CustomerID
    go
    
    update orders
    set [ShipName] = 'ggg'
    from v_orders v
    where v.CustomerID = 'ALFKI' --830 rows instead of 6 rows

    יום שלישי 25 נובמבר 2014 10:46
  • ערב טוב Yakovma

    אני לא בטוח היכן הסתבכתם :-)
    אין כאן שום באג אלא התנהגות פשוטה וברורה (בהנחה שאני מבין את הבעיה שאתה מציג והנקודה שמפריעה שלך, עברתי על השרשור בצורה מהירה). אתה יכול לחקור את תוכנית ההרצה ולהבין מה קורה מאחורי הקלעים. אני אנסה להסביר את הדברים בפשטות אם כי היה לי הרבה יותר קל להראות לך את הדברים. אין לי כוח לכתוב עכשיו בלוג שלם עם תמונות וציורים בנושא. אני מקווה שההסבר המקוצר יהיה ברור.

    * אני אדגים את הנושא עם מצב מעט יותר פשוט אבל זהה לחלוטין.

    ------------------------------------------ DDL+DML
    create table T1 (id int)
    Insert T1 select top 1000 n from _ArielyAccessoriesDatabase.dbo.ArielyNumbers -- This is just a numbers table
    GO
    
    create view V1 as 
    select id from T1 inner join _ArielyAccessoriesDatabase.dbo.ArielyNumbers t2 on t1.id = t2.n
    GO
    
    select * from V1

    עתה נריץ שלוש שאילתות עדכון פשוטות

    begin tran
    	update V1
    	set id = 9999 
    	from T1 
    	inner join _ArielyAccessoriesDatabase.dbo.ArielyNumbers t2 on t1.id = t2.n
    	where t2.n = 4 -- This will update all rows in the table!
    	select * from V1 with (nolock)
    rollback
    
    begin tran
    	update V1
    	set id = 9999 
    	from T1 
    	inner join _ArielyAccessoriesDatabase.dbo.ArielyNumbers t2 on t1.id = t2.n
    	where T1.id = 4 -- This will update all rows in the table!
    
    	select * from V1 with (nolock)
    rollback
    
    begin tran
    	update V1
    	set id = 9999 
    	from T1 
    	inner join _ArielyAccessoriesDatabase.dbo.ArielyNumbers t2 on t1.id = t2.n
    	where V1.id = 4 -- UPDATE only 1 row as you probably expected
    	
    	select * from V1 with (nolock)
    rollback

    אז מה ההבדל בין 2 השאילתות הראשונות שמעדכנות את כל הרושמות והשאילתה השלישית? פשוט מאוד :-) בשאילתות הראשונות אתה מבצע סינון על אלמנט אחר ולא על האלמנט שאתה מעדכן ולכן אתה נשאר עם עדכון של כל הרשומות בלי סינון. לעומת זאת בשאיןלתה האחרונה אתה מבצע את הסינון על האלמנט שאתה מעדכן ולכן אתה מבצע סינון של העדכון.

    הנה דוגמה פשוטה להבין את הנושא של סינון על אלמנט שאינו קשור לעדכון. בשאילתה הבאה אני מבצע עדכו לכל הרשומות למרות שאני לוקח רק רשומה אחת בחלק של ה from.

    begin tran
    	update V1
    	set id = 9999 
    	from (select 1 as a) T
    
    	select * from V1 with (nolock)
    rollback

    זה בדיוק מה שקורה לנו בסינון של המקרים הראשון והשני בדוגמאות שלי מעל.

    אני מקווה שזה מבהיר את הדברים מעט
    אני ממלית לבדוק את תוכנית ההרצה ושם תוכל לזהות בדיוק את הסינון שמבוצע למשל באלמנט בשם Hash mask. ניתן לראות שנכנסים אליו 1000 רשומות והוא מחזיר רק אחת במקרה של השאילתה השלישית. אבל הוא אינו קיים בתוכנית של השאילתה הראשונה השנייה (יש שם רק SORT).


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]



    יום רביעי 26 נובמבר 2014 00:55
    מנחה דיון
  • הי,

    זה נכון שהרבה פעמים אתה ניגש ל-View כדי לוודא תקינות, ובפועל אתה מעדכן רק טבלה אחת מתוך ה-View. אבל אז אתה צריך לכתוב את הפקודה בצורה הזאת:

    UPDATE
    	Orders
    SET
    	ShipName = 'ggg'
    FROM
    	Orders
    INNER JOIN
    	V_Orders
    ON
    	Orders.OrderId = V_Orders.OrderId
    WHERE
    	V_Orders.CustomerId = 'ALFKI';
    

    הבעיה היא שאז ה-Execution Plan פונה לאותה טבלה פעמיים, וזה בזבוז מיותר. זאת הסיבה שאני לא אוהב להשתמש ב-Views בצורה הזאת. יותר יעיל לכתוב את הפקודה ככה:

    UPDATE
    	Orders
    SET
    	ShipName = 'ggg'
    FROM
    	Orders
    INNER JOIN
    	Customers
    ON
    	Orders.CustomerId = Customers.CustomerId
    WHERE
    	Customers.CustomerId = 'ALFKI';
    

    -----------------------------
    גיא גלנצר
    יועץ ומדריך SQL Server
    Madeira - SQL Server Services
    http://www.madeirasql.com

    יום רביעי 26 נובמבר 2014 02:47
    מנחה דיון
  • אני מסכים איתך שהתחביר שאתה כותב הוא נכון יותר וכך צריך לכתוב.

    אבל בסופו של דבר התחביר הוא חוקי ויש מתכנתים שכותבים כך, ומשום מה המנוע של SQL לא מזהה שהמדובר באותו אובייקט למרות שאם אני אציין רק את ה-view ב-update הוא כן מזהה את הטבלה שמתחתיה .

    UPDATE
    	V_Orders
    SET
    	ShipName = 'ggg'
    FROM
    	V_Orders
    WHERE
    	V_Orders.CustomerId = 'ALFKI';

    יום רביעי 26 נובמבר 2014 06:06
  • עברת על ההסבר שלי?!?

    * מנוע המיטוב "מנסה לנחש" מה התכוון המשורר, אבל לא קיים עדיין בעולם בינה מלאכותית אמיתי (אם כי אנחנו כניראה קרובים לטענת כמה חברות). בסופו של דבר גם במשפט בשפה העברית אם רשום אותו בלי סימון של פיסיקים אז הקורא יוכל להבין את המשפט בכמה משמעויות.

    האחריות של כתיבת המשפט עם פיסוק נכון ובצורה שלא יהיה ניתן להבין אותו בשני דרכים הוא של מי שכותב את המשפט ולא של מי שקורא את המשפט! כך הדבר גם בכתיבת קוד. שאילתות ה SQL בשופו של דבר מתפרקות ועוברות תהליך עיבוד מחדש (בהקבלה לתהליך העיבוד הראש של מי שקורא המשפט בעברית). ההסבר לתוצאות שאתה מקבל פשוט מאוד כפי שהסברתי, והדרך להימנע מכך היא כתיבה בפורמט שונה (למשל בצורה שמציע גיא).

    * כאמור אין כאן באג ויש הרבה מאוד דוגמאות בהן כתיבה לא ברורה יכולה לגרור החלפה של סדר פעולות, ותוצאה שונה בסיום.



    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]

    יום רביעי 26 נובמבר 2014 08:59
    מנחה דיון
  • לרונן.

    תודה על תשובתך, אבל זה לא עונה על שאלתי כי אתה מתמקד ב-where ואני מתמקד ב-from ושאלתי היא מדוע המנוע של sql לא מזהה שה-view מתייחס לאותה טבלה שבה מדובר בכותרת ה-update (ב-plan אני רואה שהוא עושה no join predicate) ואת ה-where רק הוספתי לדוגמא כדי להראות את השפעת השורות (תסתכל גם בדוגמא הנוספת שהדגמתי לגיא באותו מסד נתונים).

    ושוב אני חוזר, אני מסכים שהשאילתה לא כתובה בצורה מיטבית אך עדיין יש מוקש גדול בתוך הביצוע של SQL

    יום רביעי 26 נובמבר 2014 09:24
  • * יש הרבה מוקשים בשאילתות מורכבות. ככל שעושים שימוש ביותר אלמנטים מכוננים (אלמנט בתוך אלמנט בתוך אלמנט... וכן הלאה) המורכבות עולה ומנוע המיטות של השרת מספק תוצאה פחות טובה הרבה פעמים ועל אחת כמה וכמה אולי לא התוצאה שהמשורר התכוון אליה. זה המזל שלנו! אחרת המכונות כבר היו מחליפות אותנו ולא היה צריך DBA :-)

    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]

    • סומן כתשובה על-ידי Eran Sharvit יום שני 08 דצמבר 2014 08:40
    יום חמישי 27 נובמבר 2014 23:23
    מנחה דיון
  • הי,

    אני מסכים איתך שיש כאן מוקש, ושזה עלול להטעות, וכדאי להעיר את תשומת ליבם של מתכנתים וגם של מיקרוסופט לסוגיה הזאת. אבל אני לא בטוח אם זה באג או שזה מה שהמנוע הרלציוני אמור לעשות במצב כזה. בשביל זה צריך לבדוק מה כתוב בתקן (ANSI-SQL). אולי כדאי לפתוח Bug או Suggestion ב-Connect, ולחכות לתגובת מיקרוסופט...

    תודה!

    -----------------------------
    גיא גלנצר
    יועץ ומדריך SQL Server
    Madeira - SQL Server Services
    http://www.madeirasql.com

    יום שני 01 דצמבר 2014 05:06
    מנחה דיון
  • שוב תודה על כל ההערות של כולם.

    ולשאלותיכם:

    1. אכן פתחתי קריאה במיקרוסופט והם בודקים את הנושא.

    2. לגבי ANSI. למיטב ידיעתי, הפורמט של update... from הוא יחודי ל-TSQL ואינו נתמך בתקן בגלל הבעיה שהתוצאות יכולות להיות לא דטרמינסטיות כשאני מעדכן רבים לאחד והתחביר הנתמך ב-ANSI הוא כדלהלן:

    Update Orders
    SET ShipName = (select CompanyName FROM Customer c
    WHERE c.CustomerID = o.CustomerID )
    בכלל, התחביר הזה יחודי בגלל שיש לי 2 מקומות למקם את ה-Source, בכותרת של ה-Update וב-From וזה שורש כל הצרות.

    יום שני 01 דצמבר 2014 14:58
  • צודק!

    זה באמת לא UPDATE תקני לפי ANSI-SQL.

    כל מה שנשאר זה לחכות לתגובת מיקרוסופט ולשמוע מה יש להם להגיד בנושא...

    תודה!

    -----------------------------
    גיא גלנצר
    יועץ ומדריך SQL Server
    Madeira - SQL Server Services
    http://www.madeirasql.com

    יום שלישי 02 דצמבר 2014 06:14
    מנחה דיון
  • חזרו אלי ממיקרוסופט.

    הם בדקו את הנושא מול צוות הפיתוח והם מודעים לזה שיש בעיה וזה חלק מהבעיה שיש בכלל עם התחביר המיוחד הזה שהוא יחודי למיקרוסופט (ולכן מומלץ לעבוד עם merge שאין בו את התופעות האלה והוא ANSI)אבל זה by design...

    זוהי התשובה:

    The behaviour you
    are seeing is by design, there is no predicate against the table being
    updated
    updatea.dbo.orders
    SET[ShipName]
    =c.CompanyName
    FROMorders o
    /* Local View */INNERJOINcustomers
    c
    ON
    o
    .CustomerID =c.CustomerID
    where
    c
    .CustomerID ='ALFKI'
    go
    The predicate is
    against the orders view joining on the customers table, if you look at the query
    plan you will see that there is a missing predicate
    warning
    and as there is no
    predicate we update all the rows in the table.

    יום רביעי 24 דצמבר 2014 07:34
  • תודה על העדכון!

    -----------------------------
    גיא גלנצר
    יועץ ומדריך SQL Server
    Madeira - SQL Server Services
    http://www.madeirasql.com

    יום חמישי 25 דצמבר 2014 06:43
    מנחה דיון
  • תודה על העדכון :-)

    +1


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]

    יום חמישי 25 דצמבר 2014 09:03
    מנחה דיון