Special characters in set names (spaces, dots, ...)

Tools AQL
1

We have some use cases that use spaces or dots in the set-names, e.g. “b b”. It is unclear to us how to query these sets in AQL (if at all supported).

AQL show sets confirms that the set is created correctly:

aql> show sets
+-----------+----------------+----------------------+-----------+--------------------------------------------------------+------------+---------------------+--------+-------+--------------+
| n_objects | set-enable-xdr | set-stop-write-count | ns_name   | set_name                                               | set-delete | set-evict-hwm-count | target | local | intermediate |
+-----------+----------------+----------------------+-----------+--------------------------------------------------------+------------+---------------------+--------+-------+--------------+
| 4         | "use-default"  | 0                    | "test" | "b b"                                                  | "false"    | 0                   |        |       |              |
+-----------+----------------+----------------------+-----------+--------------------------------------------------------+------------+---------------------+--------+-------+--------------+

However, trying to query something from this set through AQL fails (even though it doesn’t throw an error):

aql> select * from test.b b
Un-supported command format with token -  'b'
Type " aql --help " from console or simply "help" from within the aql-prompt.

or

aql> select * from test.b\ b
0 rows in set (0.001 secs)

or

aql> select * from test."b b"
0 rows in set (0.000 secs)

We have the same issue if we have a set with name b.b:

aql> select * from test.b.b
Un-supported command format with token -  '.'
Type " aql --help " from console or simply "help" from within the aql-prompt.
2

aql> INSERT INTO test.‘new test’ (PK, a, b) VALUES (‘xyz’, ‘abc’, 123) OK, 1 record affected.

aql> show sets ±----------±---------------±---------------------±--------±-------------±-----------±--------------------+ | n_objects | set-enable-xdr | set-stop-write-count | ns_name | set_name | set-delete | set-evict-hwm-count | ±----------±---------------±---------------------±--------±-------------±-----------±--------------------+ | 1 | “use-default” | 0 | “test” | “newtest” | “false” | 0 | | 1 | “use-default” | 0 | “test” | “‘new test’” | “false” | 0 | ±----------±---------------±---------------------±--------±-------------±-----------±--------------------+ 2 rows in set (0.001 secs) OK

aql> select * from test.newtest ±------±----+ | a | b | ±------±----+ | “abc” | 123 | ±------±----+ 1 row in set (0.605 secs)

aql> select * from test.new test Un-supported command format with token - ‘test’ Type " aql --help " from console or simply “help” from within the aql-prompt.

aql> select * from test.‘new test’ ±------±----+ | a | b | ±------±----+ | “abc” | 123 | ±------±----+ 1 row in set (0.464 secs)

I created my set dynamically when I inserted data into the set. It works, but only because the single-quotes are part of the set name: “‘new test’” .

Do you have your set defined in aerospike.conf? Would you send that portion of the config file here? I would like to test with that, if possible.

Thank you for your time,

-David Meister

3

Hi Dave,

we inserted the data into the set through the APIs by making use of the C client libraries. I see that in the example above the space between ‘new’ and ‘test’ is magically removed? I guess the difference is as follows (I’ve replicated your insert example:

aql> show sets
+-----------+----------------+----------------------+-----------+----------------------------------+------------+---------------------+
| n_objects | set-enable-xdr | set-stop-write-count | ns_name   | set_name                         | set-delete | set-evict-hwm-count |
+-----------+----------------+----------------------+-----------+----------------------------------+------------+---------------------+
| 4         | "use-default"  | 0                    | "test" | "b b"                            | "false"    | 0                   |
| 1         | "use-default"  | 0                    | "test" | "'b b'"                          | "false"    | 0                   |
+-----------+----------------+----------------------+-----------+----------------------------------+------------+---------------------+

The first line is created through our code by using the C API, the second one is created by executing the example through AQL. Like you said, it works because the single quotes are part of the set name. It would be good if AQL supported escaping of characters (like e.g. the bash shell does).

4

Normally I have seen AQL supporting single quotes and returning the results correctly but if you use double quotes it throws error. Even for a simple query on pk wiould fail if we use:

select * from test.set where pk="1"

instead of

select * from test.set where pk='1'
5

Is there any rules on how to name a set?

6

aql> INSERT INTO test.testset (PK, foo, bar) VALUES (“1”, 123, ‘abc’) OK, 1 record affected.

aql> select * from test.testset where pk=“1” ±----±------+ | foo | bar | ±----±------+ | 123 | “abc” | ±----±------+ 1 row in set (0.000 secs)

aql> INSERT INTO test.testset (PK, foo, bar) VALUES (‘2’, 123, ‘abc’) OK, 1 record affected.

aql> select * from test.testset where pk=‘2’ ±----±------+ | foo | bar | ±----±------+ | 123 | “abc” | ±----±------+ 1 row in set (0.000 secs)

aql> INSERT INTO test.testset (PK, foo, bar) VALUES (3, 123, ‘abc’) OK, 1 record affected.

aql> select * from test.testset where pk=3 ±----±------+ | foo | bar | ±----±------+ | 123 | “abc” | ±----±------+ 1 row in set (0.000 secs)

Test 1 and 2 with different quote marks.

aql> select * from test.testset where pk=‘1’ ±----±------+ | foo | bar | ±----±------+ | 123 | “abc” | ±----±------+ 1 row in set (0.000 secs)

aql> select * from test.testset where pk=“2” ±----±------+ | foo | bar | ±----±------+ | 123 | “abc” | ±----±------+ 1 row in set (0.000 secs)

7

63 characters max for set name. Set name is stored on SSD with each record. So it takes up valuable space on the SSD. Also SSDs read data in 128 byte blocks - so if your total record size is 129 bytes including all overhead, you will occupy 256 bytes on the SSD. Keeping set names small will help you maximize SSD capacity utilization. Haven’t seen explicit description on allowable characters - best practice keep it alpha-numeric without special characters or dots or spaces. I have seen discussion that starting set name with an underscore may also not work. For understanding record sizing, see http://www.aerospike.com/docs/operations/plan/capacity