Uploaded image for project: 'MariaDB ColumnStore'
  1. MariaDB ColumnStore
  2. MCOL-73

Wide table formatted display causes frontend to return error

    XMLWordPrintable

Details

    Description

      During regression queries/misc/bug3281.sql shows following behavior

      SQL and the error reported

      select 
      format(c1, 2) c1,
      format(c2, 2) c2,
      format(c3, 2) c3,
      format(c4, 2) c4,
      format(c5, 2) c5,
      format(c6, 2) c6,
      format(c7, 2) c7,
      format(c8, 2) c8,
      format(c1, 2) a,
      format(c2, 2) b,
      format(c3, 2) c,
      format(c4, 2) d,
      format(c5, 2) e,
      format(c6, 2) f,
      format(c7, 2) g,
      format(c8, 2) h
      into outfile '/tmp/tmp.tbl'
      from wide;
       
      ERROR 1118 (42000) at line 2: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
      

      ERROR 1118 - is InnoDB or MyISAM error - It looks like when Select format(c1,2),.... - is creating a temporary table that is either Aria or InnoDB or MyISAM, instead of vtable(MariaDB ColumnStore own temporary table area) and hence giving this error from there

      As can be seen below if there is not format - but simply all the columns selected then the query goes through

      select * from wide limit 1;
      +------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+
      | idx  | c1   | c2   | c3   | c4   | c5   | c6   | c7   | c8   | c9   | c10  | c11  | c12  | c13  | c14  | c15  | c16  | c17  | c18  | c19  | c20  | c21  | c22  | c23  | c24  | c25  | c26  | c27  | c28  | c29  | c30  | c31  | c32  | c33  | c34  | c35  | c36  | c37  | c38  | c39  | c40  | c41  | c42  | c43  | c44  | c45  | c46  | c47  | c48  | c49  | c50  |
      +------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+
      |    1 | 1    | 1    | 1    | 1    | 1    | 1    | 1    | 1    | 1    | 1    | 1    | 1    | 1    | 1    | 1    | 1    | 1    | 1    | 1    | 1    | 1    | 1    | 1    | 1    | 1    | 1    | 1    | 1    | 1    | 1    | 1    | 1    | 1    | 1    | 1    | 1    | 1    | 1    | 1    | 1    | 1    | 1    | 1    | 1    | 1    | 1    | 1    | 1    | 1    | 1    |
      +------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+
      1 row in set (0.69 sec)
      

      Also if there is formatting but number of columns reduced then also query goes through

      select format(c1, 2) c1, format(c2, 2) c2, format(c3, 2) c3, format(c4, 2) c4, format(c5, 2) c5 from wide limit 1;
      +------+------+------+------+------+               
      | c1   | c2   | c3   | c4   | c5   |
      +------+------+------+------+------+
      | 1.00 | 1.00 | 1.00 | 1.00 | 1.00 |
      +------+------+------+------+------+
      1 row in set, 1 warning (1.33 sec)
      

      This implies that when the query result set are passed to MariaDB front end from MariaDB ColumnStore engine, there seems to be intermediate temporary table in InnoDB/Aria/MyISAM - which cannot handle the wide table and gives the error.

      David.Hall Please see the above analysis, let us know if "select format(c1,..) format(c2....)...." is causing intermediate temporary table on MariaDB Server frontend side to be created leading to this error.

      If this is true - this particular error is of lower priority right now - as it is on the display of the formatted result, rather then returning of the result and MariaDB ColumnStore's ability to store wide tables.

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              dshjoshi Dipti Joshi (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.