none
Bug in Creating more Columns in SharePoint List Filters?

    Question

  • Hi all,

    Did anyone came across this situation? I had created more than 16 User columns in SharePoint List. Consider for example exactly 17 User (People & Groups) columns.  After creation I create the lists if we check the Field's SchemaXML file each fields has a ColName attribute starting from "int1" to "int16" with RowOrdinal=0. The 17th item, has ColName="int1" (again) and RowOrdinal=1.

    It is quiet clear that they are breaking the columns into rows (after it reaches maximum), in User it is 16 I guess. Now the problem comes: Now we have columns 1 - 17. I am entering name "ABC" in Column 1. that's it. Nothing else. Then create a view with filter "Column 17 = [Me]". Ideally, it should not show any data becoz we have not entered anything in Column 17. But, unfortunately it shows 1 record. :( (It internally searches column 1 also)

    I could correlate this with ColName="int1". I hope this is a bug. I checked by creating all the columns manually and using features. In all scenarios I could see the bug. Also, if we use CAML to filter the records (not with [Me] operator). It gives the correct data.

    Now, I dont know whether it is the bug in sharepoint. Can anyone came across such situation?

    Regards,
    Jagannathan. S
    Tuesday, December 2, 2008 9:24 AM

All replies

  • Hi,

    I an unable to reproduce this problem with 17 new created columns (user/group field type) to OOTB list.

    When you say you could correlate this with ColName="int1", what do you mean by that? which column (1 to 17) you have to correlate to make it work?

    Since RowOrdinal gets incremented after the generated ColName has reached "int16", Column 17 (ColName="int1" RowOrdinal="1") should still stored in different and unique place in DB

    SharePoint will display error message if you reached number of custom columns limit.

    -James


    James Tsai | MCPD | SharePoint Consultant
    My SharePoint Blog www.jamestsai.net/blog
    Wednesday, December 3, 2008 6:35 PM
  • Hi,

    To replicate the problem,

    1. Create a Custom List with 17 (or more) same Columns Type (User type).

    2. Internally, it creates ColName and RowOrdinal.

    3. Like 1st column we have created, it creates ColName="int1" and RowOrdinal=0

    4. 2nd Column we have created, it creates ColName="int2" and RowOrdinal=0
    ......
    5. 17th Column we have created, internally sharepoint creates ColName which is not equals to int17 but ColName="int1" but RowOrdinal=1.

    6. So, now create a small application n check if this is right (Chck the schemaxml).

    7. Create a view (in UI) and put a filter, Column 17 =[Me].

    8. Add a Row, add a user to only Column1 (we hv created). Rest of the columns to be blank.

    9. So, in the view that we have created, item should ideally not appear. But it is apearing.

    This is because, what I feel is, sharepoint internally filters based on the ColName (only). Ideally, it should filter based on ColName and RowOrdinal both.

    Simply, Consider Column Names A, B, C and colname 1, 1, 1 respectively. If we apply filter that is A=[Me] (only), it filters something like this: A=[Me] or B=[Me] or C=[Me]. This I feel is a bug.

    Regards,
    Jagannathan. S
    Friday, December 5, 2008 5:24 PM
  •  I forgot to answer your question:

    the first column you have created and the 17th column you have created both are internally related.

    A1 (ColName = "int1" RowOrdinal=0)
    A2 (ColName = "int2" RowOrdinal=0)
    A3 (ColName = "int3" RowOrdinal=0)
    A4 (ColName = "int4" RowOrdinal=0)
    A5 (ColName = "int5" RowOrdinal=0)
    A6 (ColName = "int6" RowOrdinal=0)
    A7 (ColName = "int7" RowOrdinal=0)
    A8 (ColName = "int8" RowOrdinal=0)
    A9 (ColName = "int9" RowOrdinal=0)
    A10 (ColName = "int10" RowOrdinal=0)
    A11 (ColName = "int11" RowOrdinal=0)
    A12 (ColName = "int12" RowOrdinal=0)
    A13 (ColName = "int13" RowOrdinal=0)
    A14 (ColName = "int14" RowOrdinal=0)
    A15 (ColName = "int15" RowOrdinal=0)
    A16 (ColName = "int16" RowOrdinal=0)
    B1 (ColName = "int1" RowOrdinal=1)

    A1          A2 A3 A4 A5 A6 A7 A8 A9 A10 A11 A12 A13 A14 A15 A16     B1
    -----------------------------------------------------------------------------
    Value                                                                                                  Value


    The bolded columns are interrelated. In the sense, when you apply filter (in UI) in views. If you apply, B1 = [Me] which is empty, it throws 1 record.


    Regards,
    Jagannathan. S
    Friday, December 5, 2008 5:40 PM
  • This is quite an old thread, however I hope the info I provide is enough to close it.

    The issue you are experiencing is introduced by "SQL Row Wrapping" feature. For *Person* type in a list, SQL Server row wrapping occurs after each 16 columns in a SharePoint list. The default row wrapping value of six allows for a maximum of 96 Person or Group columns per SharePoint list (6 * 16 = 96).

    SharePoint Server 2010 capacity management: Software boundaries and limits

    What it's basically saying is, on the 17th user column you creatd (with Person type), the data will actually be written to the first column of next row in the database table.

    Therefor if you keep on creating columns with Person type, on the 33rd column, you would then see Colname="int1" RowOrdinal=2

    Therefore this is not a bug, but a new design.

    Cheers,

    Allen


    Allen Wang | http://blogs.msdn.com/allenwang


    • Edited by AllenWang Tuesday, April 3, 2012 12:33 PM
    Tuesday, April 3, 2012 12:22 PM
  • I know this is an old thread but I found this issue still exists in SharePoint 2010, I just confirmed it. 
    Monday, March 11, 2013 5:24 PM
  • This is most definitely a bug (you can call it poor design if you want, but it's a bug IMO because you expect the query engine to be able to generate the correct SQL against data that spans multiple rows) in the system in the sense that the query that's generated in SQL will ONLY match against tp_RowOrdinal = 0.

    In my case, the data wraps 3 rows and one of the user fields (mapped to int type) wraps to the second row.

    CAML queries retrieve no results.

    I traced the query in SQL Profiler and found the following:

    AND (UserData.tp_RowOrdinal=0) AND ((UserData.[int10] = N''34'') 

    I've omitted a big chunk of the SQL, but you can see here that "34" is my user ID.  "int10" is the column. 

    The query engine does not offer a way to specify the RowOrdinal nor does the converted query consider non-zero RowOrdinal values in the query (34 does not show up anywhere else in the generated SQL).  In other words, the query will only execute against rows with tp_RowOrdinal="0".

    In my case, I worked around this by changing the order of the content types on my list in the Schema.xml file to force the field to appear in the first row, but this is most assuredly a bug or a huge design flaw in SharePoint that it provides this row wrapping but does not allow query filters on rows that have been wrapped.  Specifying RowOrdinal in the field CAML definition does nothing and there appears to be no way to specify the RowOrdinal when writing the CAML query either.

    Same queries worked fine once I forced the field to appear on the first row. In the case of the OP, the solution is to ensure that the fields you want to query on appear on RowOrdinal 1 by changing their order in the content type or the order in which they are added to the list.

    It seems that it would be trivially easy to write the correct SQL to include all rows in the set, but what do I know.

    Thursday, March 28, 2013 10:19 PM
  • While this was unproposed as an answer by Microsoft, I do want to reassure others that if you are experiencing this problem in SharePoint, you can definitely fix it by shifting the columns that you want to filter on higher in your content type at the time of definition or you can move your content type up in the Schema.xml file for your list if you Schema.xml file has multiple content types.
    • Edited by chrlschn Friday, April 12, 2013 1:00 PM
    Friday, April 12, 2013 1:00 PM