[MDEV-4292] parse error when selecting on views using dynamic column Created: 2013-03-18  Updated: 2013-03-27  Resolved: 2013-03-27

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.0.1, 5.5.29, 5.5.30, 5.3.12
Fix Version/s: 10.0.2, 5.5.31, 5.3.13

Type: Bug Priority: Major
Reporter: VAROQUI Stephane Assignee: Oleksandr Byelkin
Resolution: Fixed Votes: 0
Labels: None

Attachments: File 5_insertion_dynamic_column.sql    

 Description   

The following query is fine :

SELECT 
  R.splitlot_id,
 
  R.run_id, 
  I.ptest_info_id,
 
  COLUMN_GET(R.dynamic_flags,I.ptest_info_id as CHAR(1)),
 
  COLUMN_GET(R.dynamic_value,I.ptest_info_id as DECIMAL ) 
FROM  
  wt_ptest_results_rows R INNER JOIN
 
  wt_ptest_info I on R.splitlot_id=I.splitlot_id
 
where 
  COLUMN_GET(R.dynamic_value,I.ptest_info_id as DECIMAL) IS NOT NULL;

but 

CREATE VIEW `wt_ptest_results_view`
 
(splitlot_id,run_id,ptest_info_id,flags,value) AS 
SELECT 
  R.splitlot_id,
 
  R.run_id, 
  I.ptest_info_id,
 
  COLUMN_GET(R.dynamic_flags,I.ptest_info_id as CHAR(1)),
 
  COLUMN_GET(R.dynamic_value,I.ptest_info_id as DECIMAL ) 
FROM  
  wt_ptest_results_rows R INNER JOIN
 
  wt_ptest_info I on R.splitlot_id=I.splitlot_id
 
where 
  COLUMN_GET(R.dynamic_value,I.ptest_info_id as DECIMAL) IS NOT NULL;
 
 
 
select * from wt_ptest_results_view limit 1;
 
ERROR 1064 (42000): 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 ') a
 
s char(1) charset utf8) AS `flags`,cast(column_get(`r`.`dynamic_value`,`i`.`p' a
 
t line 1
 
 
 
show create view wt_ptest_results_view;
 
ERROR 1064 (42000): 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 ') a
 
s char(1) charset utf8) AS `flags`,cast(column_get(`r`.`dynamic_value`,`i`.`p' a
 
t line 1

Here is an simplified version of the tables in the test :

CREATE TABLE `wt_ptest_info` (
 
  `splitlot_id` int(10) unsigned NOT NULL DEFAULT '0',
 
  `ptest_info_id` smallint(5) unsigned NOT NULL DEFAULT '0',
 
  KEY `wtptestinfo_splitlot_ptest` (`splitlot_id`,`ptest_info_id`)
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 PACK_KEYS=1
 
 
 
 
 
CREATE TABLE `wt_ptest_results_rows` (
 
  `splitlot_id` int(10) unsigned NOT NULL DEFAULT '0',
 
  `run_id` mediumint(7) unsigned NOT NULL DEFAULT '0',
 
  `dynamic_flags` blob,
 
  `dynamic_value` blob,
 
  PRIMARY KEY (`splitlot_id`,`run_id`)
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1



 Comments   
Comment by VAROQUI Stephane [ 2013-03-18 ]

sample data to insert into Dynamic column table schema

Comment by Oleksandr Byelkin [ 2013-03-19 ]

problem is in incorrect column_get print method.

Comment by Oleksandr Byelkin [ 2013-03-19 ]

New test suite:
create table t1 (i int, d blob);

create view v1 as select i, column_get(d, 1 as binary) as a from t1;
select * from v1;
show create view v1;
drop view v1;

create view v1 as select i, column_get(d, 1 as int) as a from t1;
select * from v1;
show create view v1;
drop view v1;

create view v1 as select i, column_get(d, 1 as unsigned int) as a from t1;
select * from v1;
show create view v1;
drop view v1;

create view v1 as select i, column_get(d, 1 as date) as a from t1;
select * from v1;
show create view v1;
drop view v1;

create view v1 as select i, column_get(d, 1 as time) as a from t1;
select * from v1;
show create view v1;
drop view v1;

create view v1 as select i, column_get(d, 1 as datetime) as a from t1;
select * from v1;
show create view v1;
drop view v1;

create view v1 as select i, column_get(d, 1 as decimal) as a from t1;
select * from v1;
show create view v1;
drop view v1;

create view v1 as select i, column_get(d, 1 as double) as a from t1;
select * from v1;
show create view v1;
drop view v1;

create view v1 as select i, column_get(d, 1 as char) as a from t1;
select * from v1;
show create view v1;
drop view v1;

drop table t1;

Comment by Oleksandr Byelkin [ 2013-03-19 ]

Committed for review.

Comment by Oleksandr Byelkin [ 2013-03-26 ]

pushed

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