[MDEV-6021] CONNECT PIVOT impossible when columns contain NULL Created: 2014-04-03  Updated: 2014-04-04  Resolved: 2014-04-04

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.0.10
Fix Version/s: 10.0.11

Type: Bug Priority: Major
Reporter: Kolbe Kegel (Inactive) Assignee: Olivier Bertrand
Resolution: Fixed Votes: 0
Labels: connect-engine


 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.

 Comments   
Comment by Elena Stepanova [ 2014-04-03 ]

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.

Comment by Kolbe Kegel (Inactive) [ 2014-04-03 ]

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.

Comment by Elena Stepanova [ 2014-04-03 ]

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.

Comment by Olivier Bertrand [ 2014-04-04 ]

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.

Generated at Thu Feb 08 07:08:47 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.