none
התנהגות לא ברורה במיון לפי עמודה כשמשנים collation של אותה עמודה. RRS feed

  • שאלה

  • נתקלתי בתופעה מוזרה :

    יצרתי טבלה  - בעלת עמודה עם collate hebrew bin , הכנסתי מספר ערכים , מיון לפי אותה עמודה מתנהג תקין. המקף מופיע בסוף הרשימה.

    ביצעתי עדכון לcollate של העמודה ל - HEBREW_CI_AS והרצתי את אותה שאילתא עם המיון, ולהפתעתי זה מתנהג מוזר. המקף נמצא באמצע הרשימה למרות שלכאורה הוא אמור להופיע בסופה.

    האם מישהו נתקל בתופעה/יש לו הסבר להתנהגות הזו?

    תודה וסופ"ש נעים,

    מירי

     create table test_a (a varchar(100))
    alter table test_a alter column a varchar (100) collate HEBREW_BIN
    insert into test_a values ('   ')
    insert into test_a values ('---')
    insert into test_a values (' A')
    insert into test_a values ('                         A    !')
    insert into test_a values ('                         -A    !')
    insert into test_a values ('                         a    !')
    insert into test_a values ('-- ')

    select ascii (left (a, 1)),ascii (a),a from test_a order by a
    alter table test_a alter column a varchar (100) collate HEBREW_CI_AS

    select ascii (left (a, 1)),ascii (a),a from test_a order by a

    32 32   
    45 45 --
    45 45 ---
    32 32                          A    !
    32 32                          a    !
    32 32                          -A    !
    32 32  A


    • נערך על-ידי miri.l יום חמישי 30 אפריל 2015 15:30
    יום חמישי 30 אפריל 2015 15:29

תשובות

  • היי מירי,

    Collation קובע איך SQL Server מציג, משווה וממיין טקסט.

    לכן כשמשנים Collation, לפעמים גם משפיעים על המיון.

    במקרה הזה, כשעובדים עם Collation בינארי, ממיינים לפי ערך ה-Ascii של העמודה.

    כשמשנים ל-Hebrew_CI_AS, המיון הופך להיות לקסיקוגרפי, כלומר לפי טקסט, ושם החוקים הם שמקף הוא לפני האותיות.

    באופן כללי, שימוש ב-Collation בינארי הוא פחות נפוץ, למרות שיש גם מצבים שמשתמשים בו.

    בברכה,

    מתן

    • סומן כתשובה על-ידי Eran Sharvit יום שלישי 05 מאי 2015 17:39
    יום שישי 01 מאי 2015 03:43
  • אהלן

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

    אז הנה כמה קישורים להתעמק אם ממש מעניין (כבר שמתי מעל את הקישור המרכזי לתקן):

    http://www.unicode.org/reports/tr10/#Main_Algorithm
    http://en.wikipedia.org/wiki/ASCII

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

    insert into test_b values ('@b')

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

    -- Table Variable Declaration
    declare @tbl table(Name varchar(50));
    
    declare @I INT = 0
    while (@I < 20) 
    BEGIN
    	insert into @tbl values(@I),('-' + CONVERT(VARCHAR,@I))
    	set @i = @i + 1
    END
    
    -- Get Results.
    select Name, LEN(Name), DATALENGTH(Name)
    from @tbl 
    order by Name
    COLLATE Latin1_General_CI_AS;
     
    select Name, LEN(Name), DATALENGTH(Name)
    from @tbl 
    order by Name
    COLLATE Latin1_General_BIN;

    ** נקודה חשובה!
    אני מאוד ממליץ שלא תשמשי אף פעם בסידור לפי מספר!
    תמיד תעשי שימוש בשמות מפורשים של הטורים. זה מקור לבעיות גדולות מאוד (למרות שזה חוקי לפי חוקי T-SQL הרשמיים).

    שימי לב לדוגמה הבא של שימוש בסידור לפי מספר (נסי להסביר את התוצאה.. זה לא באג אלא התנהגות מוסברת ומורכבת מעט)

    -- Batch 1:
    DECLARE @i INT = 0
    SELECT @i = @i + a
      FROM (SELECT 1 a UNION ALL SELECT 2 UNION ALL SELECT 3) x
      ORDER BY a
    SELECT @i
    GO
    -- Batch 2:
    DECLARE @i INT = 0
    SELECT @i = @i + a
      FROM (SELECT 1 a UNION ALL SELECT 2 UNION ALL SELECT 3) x
      ORDER BY 1
    SELECT @i


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


    • נערך על-ידי pituachMVP, Editor יום ראשון 03 מאי 2015 22:23
    • סומן כתשובה על-ידי Eran Sharvit יום שלישי 05 מאי 2015 17:39
    יום ראשון 03 מאי 2015 18:46
    מנחה דיון

כל התגובות

  • היי מירי,

    Collation קובע איך SQL Server מציג, משווה וממיין טקסט.

    לכן כשמשנים Collation, לפעמים גם משפיעים על המיון.

    במקרה הזה, כשעובדים עם Collation בינארי, ממיינים לפי ערך ה-Ascii של העמודה.

    כשמשנים ל-Hebrew_CI_AS, המיון הופך להיות לקסיקוגרפי, כלומר לפי טקסט, ושם החוקים הם שמקף הוא לפני האותיות.

    באופן כללי, שימוש ב-Collation בינארי הוא פחות נפוץ, למרות שיש גם מצבים שמשתמשים בו.

    בברכה,

    מתן

    • סומן כתשובה על-ידי Eran Sharvit יום שלישי 05 מאי 2015 17:39
    יום שישי 01 מאי 2015 03:43
  • ערב טוב מירי,

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

    * דרך אגב, סידור לפי אוסף בינארי הוא מהיר יותר (ההבדל יכול להיות משמעותי, ולכן יש לו עדיפות במקרים מסויימים), הוא תמיד רגיש לאותיות גדולות או קטנות (CS), הוא רגיש לניקוד (AS), אבל הוא מוביל בהרבה מקרים לסידור לא מצופה (מפני שאינו עונה על הכללים של התווים, המוכרים לנו מכללי השפה, אלא הוא פועל ברמה בינארית).

    * אוסף בינארי כשמו עובד ברמה הבינארית והוא מתבסס על תבנית הביטים של כל תוו. אוסף בינארי לא בודק את ערך אסקי, אם כי זה נראה כך אולי בתוצאה כאשר עובדים עם נתונים שהם אסקי ולא יוניקוד, מכיוון שכל אוסף בינארי בשרתי SQL ממפה שפה מסויימת וקוד עמוד אסקי.
    https://technet.microsoft.com/en-us/library/ms143350(v=sql.105).aspx

    כאמור HEBREW_BIN מבצע סידור לפי הערכים הבינאריים ולעומת שאת  HEBREW_CI_AS מבצע סידור לפי הערכים המתאימים להגדרות העברית תוך כדי הפעלת ההוראות המתאימות
    CI=Case Insensitive מציין שאינו רגיש להבדלים בין אותיות רגילות לסופיות
    AS=Accent Sensitive מציין שהוא רגיש להבדלים בין אותיות לא מנוקדות למנוקדות
    KS=Kanatype Sensitive מציין רגישות לתווי kana בשפה היפנית
    WS=width sensitive מציין רגישות לרוחב התוו מבחינת מספר הבייטים (יש תווים שניתן לרשום עם בייט אחד או שני בייטים או יותר, בהתאם לקידוד).
    עוד אפשר לקרוא בקישור הבא:
    https://msdn.microsoft.com/en-us/library/ms143515(v=sql.105).aspx
    http://info.lionbridge.com/rs/lionbridge/images/Lionbridge%20FAQ_encoding_2013.pdf

    * כל החוקים הנ"ל שקשורים לאוספים הם חלק מתקנים שונים. אני לא בטוח אם אוספים של SQL שומרים על כל התקנים הרשמיים אבל ניתן לחפש מידע יותר מעמיק על התקנים באתרים הרשמיים כמו 
    http://www.unicode.org
    למשל מסמכים כמו המסמך הבא:
    http://www.unicode.org/reports/tr10/tr10-1.html

    * חשוב לדעתי לציין לאור הדוגמה שלך, שניתן לקבוע את הסידור ברמת השאילתה ולא היית צריכה להשקיע מאמץ על שינוי הטבלה עצמה. למשל יכולת להשתמש בקוד הבא:

    select ascii (left (a, 1)),ascii (a),a 
    from test_a 
    order by a COLLATE HEBREW_BIN
    GO
    
    select ascii (left (a, 1)),ascii (a),a 
    from test_a 
    order by a COLLATE HEBREW_CI_AS
    GO

    ** לסיום: יכול להיות שפספסתי משהו, אם יש נקודה מסויימת שניראית לך מוזרה או לא מתאימה בסידור אנא צייני אותה. תגידי מה היית מצפה לקבל ולמה התוצאה ניראית לך לא מתאימה לאוסף שבחרת.



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



    יום שישי 01 מאי 2015 21:12
    מנחה דיון
  • מתן ורונן תודה על התגובה המהירה,
    אולם הבעיה שאני מצביעה עליה היא על מיון טקסטואלי לכאורה שגוי.
    אם אכן מדובר במיון טקסטואלי -
    הרי שכל מה שמתחיל ב space היה אמור להופיע ראשון ולאחריו המקף , ואחריהם האותיות,
    לא כך הוא המקרה  בשאילתא שלי.
    ה space מופיע בתחילה אח"כ המקף , ושוב רשומות עם space  - זה לא מיון טקסטואלי.

    אם מבוצע triming עדין לא מוסברת ההתנהגות בהמשך, בו הרשומות עם ה space מופיעות לפני אלה עם האותיות.

    האם מדובר בבאג ?

    המחשה טובה יותר:

    create table test_b (col varchar(100))
    alter table test_b alter column col varchar (100) collate HEBREW_CI_AS
    insert into test_b values ('-')
    insert into test_b values ('b')
    insert into test_b values ('-b')

    select * from test_b order by 1

    מניבה את התוצאה:

    -
    b
    -b

    המיון שגוי לחלוטין אם ההגדרה שמקף הוא לפני אותיות וגם אם לא.

    אנא עזרתכם,

    יום ראשון 03 מאי 2015 08:34
  • אהלן

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

    אז הנה כמה קישורים להתעמק אם ממש מעניין (כבר שמתי מעל את הקישור המרכזי לתקן):

    http://www.unicode.org/reports/tr10/#Main_Algorithm
    http://en.wikipedia.org/wiki/ASCII

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

    insert into test_b values ('@b')

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

    -- Table Variable Declaration
    declare @tbl table(Name varchar(50));
    
    declare @I INT = 0
    while (@I < 20) 
    BEGIN
    	insert into @tbl values(@I),('-' + CONVERT(VARCHAR,@I))
    	set @i = @i + 1
    END
    
    -- Get Results.
    select Name, LEN(Name), DATALENGTH(Name)
    from @tbl 
    order by Name
    COLLATE Latin1_General_CI_AS;
     
    select Name, LEN(Name), DATALENGTH(Name)
    from @tbl 
    order by Name
    COLLATE Latin1_General_BIN;

    ** נקודה חשובה!
    אני מאוד ממליץ שלא תשמשי אף פעם בסידור לפי מספר!
    תמיד תעשי שימוש בשמות מפורשים של הטורים. זה מקור לבעיות גדולות מאוד (למרות שזה חוקי לפי חוקי T-SQL הרשמיים).

    שימי לב לדוגמה הבא של שימוש בסידור לפי מספר (נסי להסביר את התוצאה.. זה לא באג אלא התנהגות מוסברת ומורכבת מעט)

    -- Batch 1:
    DECLARE @i INT = 0
    SELECT @i = @i + a
      FROM (SELECT 1 a UNION ALL SELECT 2 UNION ALL SELECT 3) x
      ORDER BY a
    SELECT @i
    GO
    -- Batch 2:
    DECLARE @i INT = 0
    SELECT @i = @i + a
      FROM (SELECT 1 a UNION ALL SELECT 2 UNION ALL SELECT 3) x
      ORDER BY 1
    SELECT @i


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


    • נערך על-ידי pituachMVP, Editor יום ראשון 03 מאי 2015 22:23
    • סומן כתשובה על-ידי Eran Sharvit יום שלישי 05 מאי 2015 17:39
    יום ראשון 03 מאי 2015 18:46
    מנחה דיון
  • הי מירי,

    האם קיבלת מענה לשאלתך?

    תודה!

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

    יום שני 11 מאי 2015 05:44
    מנחה דיון