Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-6021

CONNECT PIVOT impossible when columns contain NULL

Details

    Description

      mysql 10.0.10-MariaDB (root) [test]> show create table t1\G
      		 
      *************************** 1. row ***************************
      		 
             Table: t1
      		 
      Create Table: CREATE TABLE `t1` (
      		 
        `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      		 
        `c1` char(32) DEFAULT NULL,
      		 
        `c2` char(32) DEFAULT NULL,
      		 
        `c3` char(32) DEFAULT NULL,
      		 
        PRIMARY KEY (`id`)
      		 
      ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1
      		 
      1 row in set (0.00 sec)
      		 
       
      		 
      mysql 10.0.10-MariaDB (root) [test]> select * from t1;
      		 
      +----+------+------+------+
      		 
      | id | c1   | c2   | c3   |
      		 
      +----+------+------+------+
      		 
      |  1 | a    | NULL | c    |
      		 
      +----+------+------+------+
      		 
      1 row in set (0.00 sec)
      		 
       
      		 
      mysql 10.0.10-MariaDB (root) [test]> create table t1_pivot engine=connect table_type=pivot tabname=t1;
      		 
      ERROR 1939 (HY000): Engine CONNECT failed to discover table `test`.`t1_pivot` with 'CREATE TABLE whatever (`id` INT(10) NOT NULL,`c1` CHAR(32) NOT NULL,`` CHAR(32) NOT NULL FLAG=1) TABLE_TYPE='pivot' TABNAME='t1''

      This should work differently, or it should be documented and should give a better error message.

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          elenst Elena Stepanova added a comment -

          I think an error is kind of expected – if the contents of the column is supposed to become a name of a column, it cannot really be NULL, right?
          It would be nice indeed to have a better error message if it's possible.
          What I totally agree with is that it should be documented.

          elenst Elena Stepanova added a comment - I think an error is kind of expected – if the contents of the column is supposed to become a name of a column, it cannot really be NULL, right? It would be nice indeed to have a better error message if it's possible. What I totally agree with is that it should be documented.
          kolbe Kolbe Kegel (Inactive) added a comment -

          Sure, maybe it's expected for NULL not to work. Or maybe it should result in a column called NULL (or _NULL_) or something else, but yes I guess mostly I just wish this was caught for some reason other than the empty column name causing a problem and that a better error was generated.

          kolbe Kolbe Kegel (Inactive) added a comment - Sure, maybe it's expected for NULL not to work. Or maybe it should result in a column called NULL (or _ NULL _) or something else, but yes I guess mostly I just wish this was caught for some reason other than the empty column name causing a problem and that a better error was generated.
          elenst Elena Stepanova added a comment - - edited

          It cannot really be called anything, because next thing we'll do is put this 'NULL' or '_NULL_' or whatever alias we chose for NULL as a string value in another row and get results mixed up.
          For the error message, I'll leave it to Olivier to decide what can be done. I guess there could have been a better message if we used some of those fancy table options, but since we asked for a silent discovery, we get an error from the discovery. But maybe it's still possible to improve it.
          In any case, documentation should reflect this.

          elenst Elena Stepanova added a comment - - edited It cannot really be called anything , because next thing we'll do is put this 'NULL' or '_NULL_' or whatever alias we chose for NULL as a string value in another row and get results mixed up. For the error message, I'll leave it to Olivier to decide what can be done. I guess there could have been a better message if we used some of those fancy table options, but since we asked for a silent discovery, we get an error from the discovery. But maybe it's still possible to improve it. In any case, documentation should reflect this.
          bertrandop Olivier Bertrand added a comment -

          Sure enough, the PIVOT column should not contain null values.
          This is now tested and an appropriate error message issued.
          This will be properly documented.

          bertrandop Olivier Bertrand added a comment - Sure enough, the PIVOT column should not contain null values. This is now tested and an appropriate error message issued. This will be properly documented.

          People

            bertrandop Olivier Bertrand
            kolbe Kolbe Kegel (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            4 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.