[MCOL-767] Mariadb columnstore CAST() not working for VARCHAR Created: 2017-06-10  Updated: 2017-06-11  Resolved: 2017-06-10

Status: Closed
Project: MariaDB ColumnStore
Component/s: MariaDB Server
Affects Version/s: None
Fix Version/s: Icebox

Type: Bug Priority: Major
Reporter: Kleyson Rios Assignee: Andrew Hutchings (Inactive)
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

github.com/mariadb-corporation/mariadb-columnstore-docker.gi‌​t
mariadb-columnstore-1.0.9-1-centos7.x86_64.rpm.tar.gz


 Description   

I'm using Pentho Kettle to load data into the DW.

Some pdi steps generate SQL queries for lookup data.

The following is a snippet of a generated SQL where the CAST() function throw a exception if we try to cast for VARCHAR:

MariaDB [dbtest]> SELECT dpro_id
 
    -> FROM dbtest.d_prov
 
    -> WHERE ( CAST('NotNull' AS VARCHAR(256)) IS NULL ) 
    -> ;
 
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 'VARCHAR(256)) IS NULL )' at line 3

But the same SQL works if we try to cast for CHAR:

MariaDB [dbtest]> SELECT dpro_id
 
    -> FROM dbtest.d_prov
 
    -> WHERE ( CAST('NotNull' AS CHAR) IS NULL ) 
    -> ;
 
Empty set (0.04 sec)

Best Regards.

 Comments   
Comment by Andrew Hutchings (Inactive) [ 2017-06-10 ]

VARCHAR(255) is not a valid cast for MariaDB. Please see: https://mariadb.com/kb/en/mariadb/convert/

Comment by Kleyson Rios [ 2017-06-10 ]

@Andrew, this link seems to be for the CONVERT() and not CAST() function.

Based on the CAST() documentation ( https://mariadb.com/kb/en/mariadb/cast/ ). The first line of Description says "The CAST() function takes a value of one type ..." and if we click in the "type hyperlink ( https://mariadb.com/kb/en/mariadb/data-types/ )" , VARCHAR is a valid type for cast.

Best Regards.

Comment by David Thompson (Inactive) [ 2017-06-10 ]

varchar is not a valid datatype for cast in either mysql or mariadb at this time. However https://jira.mariadb.org/browse/MDEV-11283 is being worked on in the server to add support for this for compatibility reasons.

This is probably better filed as a bug with pentaho as this is not current valid syntax for either mysql or mariadb.

Comment by Andrew Hutchings (Inactive) [ 2017-06-11 ]

A documentation bug should also probably be filed against MariaDB Server to copy the text from CONVERT to the CAST documentation page. I believe the CONVERT page originally covered both which is why it mentions the valid types for both near the top.

Generated at Thu Feb 08 02:23:40 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.