Uploaded image for project: 'XWiki Platform'
  1. XWiki Platform
  2. XWIKI-10606

Filter in Livetable macro does not work under MSSQL database

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Reopened
    • Priority: Minor
    • Resolution: Unresolved
    • Affects Version/s: 5.4.2
    • Fix Version/s: None
    • Component/s: LiveTable
    • Labels:
    • Environment:
      XWiki 5.4.2 with MS SQL 2012 as backend.
    • Difficulty:
      Easy
    • Documentation:
      N/A
    • Documentation in Release Notes:
      N/A
    • Pull Request Status:
      Awaiting Contributor feedback
    • Similar issues:

      Description

      We recognized, that the filter function in the livetable Macro is not working on MS SQL. The macro function livetable_addColumnToQuery (defined in XWiki.LiveTableResultsMacros) uses in an HQL Statement the function str (Line 576) with an string parameter. This function call is not correctly mapped in to the SQL statement.

      The resulting SQL statement:

      select top 15 xwikidocum0_.XWD_FULLNAME as col_0_0_ from DE_0000_041_TEST.de.xwikidoc xwikidocum0_ 
      where (xwikidocum0_.XWD_HIDDEN<>1 or xwikidocum0_.XWD_HIDDEN is null) 
        and (xwikidocum0_.XWD_LANGUAGE is null or xwikidocum0_.XWD_LANGUAGE='' or xwikidocum0_.XWD_LANGUAGE='de') 
        and 1=1 
        and (upper(str(xwikidocum0_.XWD_NAME)) like upper(?)) order by xwikidocum0_.XWD_NAME asc
      

      produces the follwoing SQL error:

      Error converting data type varchar to float.

      Why tried it with the hibernate dialects

      org.hibernate.dialect.SQLServer2008Dialect
      org.hibernate.dialect.SQLServerDialect

      Both produce the same result.

      Is the str function here necessary? When removing it the code works fine.

      It seems that the call of the str function is in the SQL statement not necessary at all.

      We know MS SQL is not officially supported, however this is so far the only issue we detected when using MS SQL.

        Attachments

          Activity

            People

            Assignee:
            jhaimerl Josef Haimerl
            Reporter:
            rhierlmeier Richard Hierlmeier
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

              Dates

              Created:
              Updated:
              Date of First Response: