Ok I think I figured this out,
The order by operator is executed after the 'like' operator so it doesn't
take the case insensitive index into account.
To output the expected result I set the output from the select statement to
lower case and then the "order by" works correctly. (I replicate the name
field in order to display it with the right case-ing)
select name as displayname, name.toLowerCase() order from person where
name like 'l%' order by name asc
hope this helps someone.
On Monday, March 31, 2014 10:40:18 AM UTC-4, Ivan Plaza wrote:
>
> Hi,
>
> I'm new to OrientDB and so far quite impressed.
>
> I'm performing some tests and came across what I think may be a bug.
> Apologies if this is in fact the expected behavior.
>
> I have created a simple database which holds two users: Luca (capitalized)
> and laura (lowercase). I created a case-insensitive NOTUNIQUE index on the
> name property. When I query the database for all users and order by name,
> the result is as expected: laura comes before Luca
>
> However, if I query the database for users that start with the letter 'l',
> the order no longer holds and Luca comes before laura in the result.
>
> *Here are the details:*
>
> *Release*: orientdb-community-1.7-rc2
>
> *Build*: March, 25th 2014 (couldn't find build file)
>
> *Steps to Reproduce:*
>
> 1. Create Class
>
> create class Person extends V
>
> 2. Create users
>
> create vertex Person set name = 'Luca'
> create vertex Person set name = 'laura'
>
> 3. Create index
>
> CREATE PROPERTY Person.name STRING
> CREATE INDEX NAME_INDEX on Person (name COLLATE CI) NOTUNIQUE
>
> *Side Note: The index creation actually failed using this method but I was
> able to create the index using the 'editclass' window in the studio.*
>
> *Here's the error on the sql call:*
> *com.orientechnologies.orient.core.sql.OCommandSQLParsingException: Error
> on parsing command at position #33: Illegal field name format, should be '
> [by key|value]' but was '[Person.name] COLLATE CI' Command: CREATE INDEX
> NAME_INDEX on Person ([Person.name] COLLATE CI) NOTUNIQUE
> -----------------------------------------^*
>
>
> 4. Query the database for all Persons and order by name
>
> select * from person order by name asc
>
> *Result*:
> "result": [
> {
> "@type": "d",
> "@rid": "#11:1",
> "@version": 1,
> "@class": "Person",
> "name": "laura"
> },
> {
> "@type": "d",
> "@rid": "#11:0",
> "@version": 1,
> "@class": "Person",
> "name": "Luca"
> }
>
> *Expected Result*: This is as expected
>
> 5. Query the database for names starting with 'l' and order by name
>
> select * from person where name like 'l%' order by name asc
>
> *Result:*
> "result": [
> {
> "@type": "d",
> "@rid": "#11:0",
> "@version": 1,
> "@class": "Person",
> "name": "Luca"
> },
> {
> "@type": "d",
> "@rid": "#11:1",
> "@version": 1,
> "@class": "Person",
> "name": "laura"
> }
>
> *Expected Result*: Identical to Step 4: laura should come before Luca
>
> *Side note: I also tried this query and the result is the same as that in
> step 5.*
> *select * from (select * from person where name like 'l%' order by name
> asc) order by name asc*
>
> Thanks
> Yvan
>
>
--
---
You received this message because you are subscribed to the Google Groups
"OrientDB" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
