Solved: WFA SQL does it support character classes?

jauling_chou · ‎2016-04-19

I am looking to develop a (seemingly) simple WFA filter that finds aggregates that match a specific pattern.

I can't seem to find specific documentation to say what WFA supports with regards to SQL queries.

I found this page:

https://library.netapp.com/ecmdocs/ECMP1644818/html/GUID-25449353-2C25-4FBB-BA36-6152D97E1D4E.html

Which has a SELECT link to mysql 5.1:

http://dev.mysql.com/doc/refman/5.1/en/select.html

I'd assume that if the WFA documentation references MySQL 5.1, that it should support REGEXP and RLIKE, but it doesnt seem that it does.

I was trying to do pattern matching: LIKE 'aggr[13][0-9]%'

Which simply translates to a string that looks like aggr10-19* or aggr30-39*. It sounds like I may need to make a 20 element OR query??

sinhaa · ‎2016-04-21

@jauling_chou

Your query to get the result you want is not right. It should be:

AND aggregate.name RLIKE 'aggr[13]{1}[0-9]{1}.*'

As I said, WFA support any SQL feature that mysql supports. Its not different than any MYsql client.

I would suggest to connect to WFA DB using any MYSQL clients like workbench, SQL Yog, DB visualizer etc. and run your queries. This would easily allow you to test the validity of your queries keeping WFA GUI aside.

For WFA DB login credentials etc. , See the WFA documentations.

And yes SQL query AUTOcomplete doesn't include everything that Mysql has to offer. But they will work.

sinhaa

If this post resolved your issue, help others by selecting ACCEPT AS SOLUTION or adding a KUDO.

View solution in original post

sinhaa · ‎2016-04-19

@jauling_chou

I'd assume that if the WFA documentation references MySQL 5.1, that it should support REGEXP and RLIKE, but it doesnt seem that it does.

----

WFA does support both REGEXP and RLIKE. It supports everything that SQL supports.

What is the exact query that you are trying to create? What is the error being thrown?

sinhaa

If this post resolved your issue, help others by selecting ACCEPT AS SOLUTION or adding a KUDO.

jauling_chou · ‎2016-04-20

@sinhaa wrote:
@jauling_chou

I'd assume that if the WFA documentation references MySQL 5.1, that it should support REGEXP and RLIKE, but it doesnt seem that it does.
----
WFA does support both REGEXP and RLIKE. It supports everything that SQL supports.

What is the exact query that you are trying to create? What is the error being thrown?

sinhaa

No errors are thrown, it just gives me no data returned. I've commented out the expanded version in the code below. If I switch to that code, then I get a bunch of results, as intended. FWIW, typing RLIKE or REGEXP in the WFA query builder DOES NOT automatically autocomplete the keywords. Maybe its the WFA 3.0 version I'm running that's not cooperating?

SELECT
    aggregate.name,
    node.name AS 'node.name',
    cluster.primary_address AS 'node.cluster.primary_address'
FROM
    cm_storage.aggregate,
    cm_storage.node,
    cm_storage.cluster
WHERE
    node.id = aggregate.node_id
    AND cluster.id = node.cluster_id
    AND aggregate.name RLIKE 'aggr[13][0-9]%'
/*    AND (
    aggregate.name LIKE 'aggr10%'
    OR aggregate.name LIKE 'aggr11%'
    OR aggregate.name LIKE 'aggr12%'
    OR aggregate.name LIKE 'aggr13%'
    OR aggregate.name LIKE 'aggr14%'
    OR aggregate.name LIKE 'aggr15%'
    OR aggregate.name LIKE 'aggr16%'
    OR aggregate.name LIKE 'aggr17%'
    OR aggregate.name LIKE 'aggr18%'
    OR aggregate.name LIKE 'aggr19%'
    OR aggregate.name LIKE 'aggr30%'
    OR aggregate.name LIKE 'aggr31%'
    OR aggregate.name LIKE 'aggr32%'
    OR aggregate.name LIKE 'aggr33%'
    OR aggregate.name LIKE 'aggr34%'
    OR aggregate.name LIKE 'aggr35%'
    OR aggregate.name LIKE 'aggr36%'
    OR aggregate.name LIKE 'aggr37%'
    OR aggregate.name LIKE 'aggr38%'
    OR aggregate.name LIKE 'aggr39%'
) */

sinhaa · ‎2016-04-21

@jauling_chou

Your query to get the result you want is not right. It should be:

AND aggregate.name RLIKE 'aggr[13]{1}[0-9]{1}.*'

As I said, WFA support any SQL feature that mysql supports. Its not different than any MYsql client.

I would suggest to connect to WFA DB using any MYSQL clients like workbench, SQL Yog, DB visualizer etc. and run your queries. This would easily allow you to test the validity of your queries keeping WFA GUI aside.

For WFA DB login credentials etc. , See the WFA documentations.

And yes SQL query AUTOcomplete doesn't include everything that Mysql has to offer. But they will work.

sinhaa

If this post resolved your issue, help others by selecting ACCEPT AS SOLUTION or adding a KUDO.

jauling_chou · ‎2016-04-21

@sinhaa wrote:
@jauling_chou

Your query to get the result you want is not right. It should be:
AND aggregate.name RLIKE 'aggr[13]{1}[0-9]{1}.*'
As I said, WFA support any SQL feature that mysql supports. Its not different than any MYsql client.

I would suggest to connect to WFA DB using any MYSQL clients like workbench, SQL Yog, DB visualizer etc. and run your queries. This would easily allow you to test the validity of your queries keeping WFA GUI aside.

For WFA DB login credentials etc. , See the WFA documentations.

And yes SQL query AUTOcomplete doesn't include everything that Mysql has to offer. But they will work.

sinhaa

Actually, the {1} is entirely optional, given that [01] and [0-9] character classes already mean that they match one of the characters. Regardless, even if I try your version, it does not work. I've resorted to using the ginormous OR conditional instead.

I played around a bit more using the built-in mysql.exe in WFA, and finally figured it out. Its because RLIKE/REGEXP doesn't take % symbols! Once I switched over to using proper wildcard regexp, it works as intended. Thanks for the tip! FWIW, I completely at first overlooked the .* in your example.

C:\Program Files\NetApp\WFA\mysql\bin>mysql -u wfa -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 97110
Server version: 5.6.23-enterprise-commercial-advanced MySQL Enterprise Server -Advanced Edition (Commercial)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT aggregate.name FROM cm_storage.aggregate WHERE aggregate.name LIKE 'aggr3%';
+--------------------+
| name               |
+--------------------+
| aggr31_1a          |
| aggr31_1b          |
| aggr31_4a_reserved |
| aggr3_1a           |
+--------------------+
4 rows in set (0.00 sec)

mysql> SELECT aggregate.name FROM cm_storage.aggregate WHERE aggregate.name RLIKE 'aggr3%';
Empty set (0.00 sec)

mysql> SELECT aggregate.name FROM cm_storage.aggregate WHERE aggregate.name RLIKE 'aggr31_1a';
+-----------+
| name      |
+-----------+
| aggr31_1a |
+-----------+
1 row in set (0.00 sec)

mysql> SELECT aggregate.name FROM cm_storage.aggregate WHERE aggregate.name RLIKE 'aggr3[0-9]%';
Empty set (0.00 sec)

mysql> SELECT aggregate.name FROM cm_storage.aggregate WHERE aggregate.name RLIKE 'aggr3[0-9].*';
+--------------------+
| name               |
+--------------------+
| aggr31_1a          |
| aggr31_1b          |
| aggr31_4a_reserved |
+--------------------+
3 rows in set (0.00 sec)

mysql> SELECT aggregate.name FROM cm_storage.aggregate WHERE aggregate.name RLIKE 'aggr[13][0-9].*';
+--------------------+
| name               |
+--------------------+
| aggr10_1a          |
| aggr10_1a          |
| aggr10_1b          |
| aggr10_1b          |
| aggr10_1b          |
| aggr10_1b          |
| aggr10_1b          |
| aggr10_1b          |
| aggr10_1b          |
| aggr10_2b          |
| aggr10_2b_reserved |
| aggr10_5b          |
| aggr11_1a          |
| aggr11_1b          |
| aggr11_1b          |
| aggr11_2a          |
| aggr11_2b          |
| aggr11_2b          |
| aggr12_2a          |
| aggr12_2b          |
| aggr15k_1b         |
| aggr31_1a          |
| aggr31_1b          |
| aggr31_4a_reserved |
+--------------------+
24 rows in set (0.02 sec)