[MDEV-5762] CONNECT PIVOT generates nonsense SQL Created: 2014-02-27  Updated: 2014-03-25  Resolved: 2014-03-13

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

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


 Description   

The CONNECT engine is generating nonsense SQL for a PIVOT table I created:

drop table if exists t1;
 
CREATE TABLE `t1` (  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,  `name` varchar(32) DEFAULT NULL,  `cnt` int(11) DEFAULT NULL,  PRIMARY KEY (`id`) ) ENGINE=InnoDB;
 
INSERT INTO `t1` VALUES (1,'apple',1),(2,'banana',1),(3,'apple',2),(4,'cherry',4),(5,'durazno',2);
 
drop table if exists fruit_pivot;
 
CREATE TABLE fruit_pivot ENGINE=CONNECT TABLE_TYPE=pivot TABNAME=t1;
 
select * from fruit_pivot;
 

ERROR 1296 (HY000): Got error 174 '(1064) You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'id, nameSELECT , SUM(cnt) cnt FROM t1 GROUP BY' at line ' from CONNECT

From the general query log:

                   10 Query     SELECT id, nameSELECT , SUM(cnt) cnt FROM t1 GROUP BY id, nameSELECT id, nameSELECT , SUM(cnt) cnt FROM t1 GROUP BY



 Comments   
Comment by Olivier Bertrand [ 2014-02-28 ]

This was also fixed while fixing MDEV-5734

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

In what version is this actually fixed? The JIRA task says "Fix Version/s: 10.0.9, 10.0.10" and it definitely seems to still be broken in 10.0.9. I guess we wait for 10.0.10 to see if it is fixed there?

Comment by Olivier Bertrand [ 2014-03-13 ]

All I know is that I pushed the fix some time ago but I have no control on when and what version it is included.

Comment by Olivier Bertrand [ 2014-03-13 ]

BTW you can temporarily bypass this bug for this particular table by changing the table name from t1 to tt1 (for instance)

Comment by Elena Stepanova [ 2014-03-13 ]

The bug MDEV-5734 was fixed before 10.0.9 release, but the bugfix was not merged into the main tree by the time it was released, hence the confusion. It will be released with 10.0.10.

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

Is this still not merged into main MariaDB 10.0.10? Is there a merge schedule for CONNECT fixes? I'm worried this isn't going to make it into 10.0.0 at this point...

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

Also, the workaround of using a table name other than "t1" does not seem to work...

mysql 10.0.9-MariaDB (root) [test] db1> CREATE TABLE fruit_pivot ENGINE=CONNECT TABLE_TYPE=pivot TABNAME=fruit_table;
Query OK, 0 rows affected (0.04 sec)

mysql 10.0.9-MariaDB (root) [test] db1> select * from fruit_pivot;
140324 23:10:45 mysqld_safe Number of processes running now: 0
140324 23:10:45 mysqld_safe mysqld restarted
ERROR 2013 (HY000): Lost connection to MySQL server during query

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

On OS X, built from revno 4081:

mysql 10.0.10-MariaDB (root) [test]> alter table fruity_fruits rename fruit_table;
Query OK, 0 rows affected (0.01 sec)

mysql 10.0.10-MariaDB (root) [test]> CREATE TABLE fruit_pivot ENGINE=CONNECT TABLE_TYPE=pivot TABNAME=fruit_table;
Query OK, 0 rows affected (0.01 sec)

mysql 10.0.10-MariaDB (root) [test]> select * from fruit_pivot;
ERROR 1296 (HY000): Got error 174 '(1064) You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'id, nameSELECT , SUM(cnt) cnt FROM fruit_table GROUP BY'' from CONNECT

Comment by Elena Stepanova [ 2014-03-25 ]

Is this still not merged into main MariaDB 10.0.10? Is there a merge schedule for CONNECT fixes? I'm worried this isn't going to make it into 10.0.0 at this point...

Here is the merge task: https://mariadb.atlassian.net/browse/MDEV-5937
It is a blocker, so, it should make it to the release all right.

Meanwhile, if you want, you could try 10.0-connect tree.

Comment by Olivier Bertrand [ 2014-03-25 ]

Bypass: sorry about that, in fact it does not work. But changing the length of one of the column names possibly works!

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