[MDEV-12385] Bundling insert with group concat with create statement for memory table fails to cast properly Created: 2017-03-28  Updated: 2017-05-01  Resolved: 2017-05-01

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Temporary, Storage Engine - Memory
Affects Version/s: 10.0.30
Fix Version/s: N/A

Type: Bug Priority: Minor
Reporter: Mark Punak Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

CentOS 7.1


 Description   

The following Create/Insert statement fails with Error Code 1163

CREATE TEMPORARY TABLE blobtest (
 
   `GROUPING` varchar(45) DEFAULT NULL
 
) ENGINE=MEMORY
 
select
 
	group_concat(ITEM)
 
from 
	(
 
		select "item1" ITEM
 
        union
 
        select "item2"
 
    ) temp;

The following code, succeeds

CREATE TEMPORARY TABLE blobtest (
 
   `GROUPING` varchar(45) DEFAULT NULL
 
) ENGINE=MEMORY;
 
insert into blobtest
 
select
 
	group_concat(ITEM)
 
from 
	(
 
		select "item1" ITEM
 
        union
 
        select "item2"
 
    ) temp;

It appears the bundled statement is ignoring the DDL of the table creation statement, or failing to cast to varchar(45)

 Comments   
Comment by Elena Stepanova [ 2017-05-01 ]

If you want the result of GROUP_CONCAT to end up in the column GROUPING, you should name the result as such. Otherwise your DDL attempts to create two columns, one `GROUPING` varchar(45) and another one which is the result of GROUPING. To see how it works, consider a much simpler example:

MariaDB [test]> CREATE TEMPORARY TABLE blobtest1 (`GROUPING` varchar(45)) ENGINE=MEMORY select 'item';
 
Query OK, 1 row affected (0.00 sec)
 
Records: 1  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> show create table blobtest1;
 
+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Table     | Create Table                                                                                                                                                                  |
 
+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| blobtest1 | CREATE TEMPORARY TABLE `blobtest1` (
 
  `GROUPING` varchar(45) DEFAULT NULL,
 
  `item` varchar(4) CHARACTER SET utf8 NOT NULL DEFAULT ''
 
) ENGINE=MEMORY DEFAULT CHARSET=latin1 |
 
+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
1 row in set (0.00 sec)


MariaDB [test]> CREATE TEMPORARY TABLE blobtest2 (`GROUPING` varchar(45)) ENGINE=MEMORY select 'item' AS `GROUPING`;
 
Query OK, 1 row affected (0.01 sec)
 
Records: 1  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> show create table blobtest2;
 
+-----------+-------------------------------------------------------------------------------------------------------------------+
 
| Table     | Create Table                                                                                                      |
 
+-----------+-------------------------------------------------------------------------------------------------------------------+
 
| blobtest2 | CREATE TEMPORARY TABLE `blobtest2` (
 
  `GROUPING` varchar(45) DEFAULT NULL
 
) ENGINE=MEMORY DEFAULT CHARSET=latin1 |
 
+-----------+-------------------------------------------------------------------------------------------------------------------+
 
1 row in set (0.00 sec)

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