Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Match Vertex parent types #1403

Closed
tmcsys opened this issue Dec 27, 2023 · 3 comments
Closed

Match Vertex parent types #1403

tmcsys opened this issue Dec 27, 2023 · 3 comments
Assignees
@lvca
Labels
bug Something isn't working fixed
Milestone
24.1.1

Comments

@tmcsys
Copy link

tmcsys commented Dec 27, 2023

ArcadeDB Server v23.12.1-SNAPSHOT

MacOS Ventura Version 13.6 (22G120
OpenJDK Runtime Environment Temurin-17.0.9+9 (build 17.0.9+9)

This has been languishing on Discord for a few days with no response.

I'm running queries from OrientDB documentation against copies of the OrientDB demodb on ArcadeDB. The database can be imported from arcadedb-datasets:orientdb/demodb.gz.

In Studio, I'm able to retrieve inherited types
MATCH {type: Customers, as: customer, where: (OrderedId=1)}--{type: Restaurants}
RETURN $pathelements

but not parent types
MATCH {type: Customers, as: customer, where: (OrderedId=1)}--{type: Locations}
RETURN $pathelements

What am I missing?

Here's part of the schema showing the inheritance:

Locations
-- + Services
----       + Hotels
----       + Restaurants
-- + Attractions
----       + Monuments
----       + Castles
----       + Theatres
----       + Archaeological Sites

The inheritance is confirmed by select from schema:types

select from Locations
in Studio returns appropriate results

Below is the output from a select:

select OUT() from Customers where OrderedId = 1 

{
  "user": "root",
  "version": "23.12.1-SNAPSHOT (build 0a8972dec07cb5a0d1cce0127fe9154e140e0b5e/1703095456042/main)",
  "serverName": "ArcadeDB_0",
  "result": {
    "vertices": [],
    "edges": [],
    "records": [
      {
        "OUT()": [
          {
            "Id": 65,
            "Name": "Teatro Consorziale",
            "Type": "theatre",
            "@cat": "v",
            "@type": "Theatres",
            "@rid": "#425:8"
          },
          {
            "Id": 62,
            "Name": "Statua del Savonarola",
            "Type": "monument",
            "@cat": "v",
            "@type": "Monuments",
            "@rid": "#344:7"
          },
          {
            "Id": 47,
            "Name": "Incisioni rupestri",
            "Type": "archaeological_site",
            "@cat": "v",
            "@type": "ArchaeologicalSites",
            "@rid": "#491:5"
          },
          {
            "Id": 127,
            "Name": "Château de Montmayeur",
            "Type": "castle",
            "@cat": "v",
            "@type": "Castles",
            "@rid": "#395:15"
          },
          {
            "Id": 9,
            "Name": "Colin",
            "Surname": "OrientDB",
            "Gender": "Male",
            "Email": "colin@example.com",
            "Bio": "OrientDB Team",
            "@cat": "v",
            "@type": "Profiles",
            "@rid": "#49:1"
          },
          {
            "Id": 1738,
            "Name": "Locanda della Luna",
            "Type": "restaurant",
            "@cat": "v",
            "@type": "Restaurants",
            "@rid": "#244:217"
          },
          {
            "Id": 1099,
            "Name": "Baita GAM Piovene Rocchette",
            "Type": "restaurant",
            "@cat": "v",
            "@type": "Restaurants",
            "@rid": "#247:137"
          },
          {
            "Id": 1834,
            "Name": "Einsiedeln",
            "Type": "pub",
            "@cat": "v",
            "@type": "Restaurants",
            "@rid": "#244:229"
          },
          {
            "Id": 730,
            "Name": "Carrel",
            "Type": "alpine_hut",
            "@cat": "v",
            "@type": "Hotels",
            "@rid": "#196:91"
          },
          {
            "Id": 233,
            "Code": "US",
            "Name": "United States",
            "@cat": "v",
            "@type": "Countries",
            "@rid": "#9:29"
          }
        ]
      }
    ]
  },
  "explain": "+ FETCH FROM INDEX Customers[OrderedId]\n  OrderedId = 1\n+ EXTRACT VALUE FROM INDEX ENTRY\n  filtering buckets [533,536,527,530,539,542,521,524]\n+ FILTER ITEMS BY TYPE \n  Customers\n+ LIMIT ( LIMIT 25)\n+ CALCULATE PROJECTIONS\n  OUT()"
}```
@tmcsys
Copy link
Author

tmcsys commented Jan 12, 2024

Test case

import com.arcadedb.database.Database;
import com.arcadedb.database.DatabaseFactory;

import com.arcadedb.query.sql.executor.Result;
import com.arcadedb.query.sql.executor.ResultSet;

public class MatchInheritanceTest {

  	public static void main(String[] argv) {
	  
  		DatabaseFactory factory = new DatabaseFactory("/tmp/adbdatabases/mydb");
	  
  		Database db = null;
  		
  		try { 
  			if (factory.exists())
			   factory.open().drop();
  			
			db = factory.create();

			StringBuilder sb = new StringBuilder();
			
			sb.append("BEGIN;");			
/*			
			-- Locations 
			--    + Services 
			--        + Hotels 
			--        + Restaurants
			--    + Attractions
			--        + Monuments
			--        + Castles
			--        + Theatres 
			--        + Archaeological Sites
*/					
			sb.append("CREATE VERTEX TYPE Locations;");
			sb.append("CREATE PROPERTY Locations.Id LONG;");
			sb.append("CREATE PROPERTY Locations.Type STRING;");
			sb.append("CREATE PROPERTY Locations.Name STRING;");
	
			sb.append("CREATE INDEX ON Locations (Type) NOTUNIQUE;");
			sb.append("CREATE INDEX ON Locations (Name) FULL_TEXT;");

			sb.append("CREATE VERTEX TYPE Services EXTENDS Locations;");
			sb.append("CREATE VERTEX TYPE Hotels EXTENDS Services;");
			sb.append("CREATE INDEX ON Hotels (Id) UNIQUE;");
			
			sb.append("CREATE VERTEX TYPE Restaurants EXTENDS Services;\n");
			sb.append("CREATE INDEX ON Restaurants(Id) UNIQUE;\n");

			sb.append("CREATE VERTEX TYPE Attractions EXTENDS Locations;\n");
			sb.append("CREATE VERTEX TYPE Monuments EXTENDS Attractions;\n");
			sb.append("CREATE INDEX ON Monuments (Id) UNIQUE;\n");

			sb.append("CREATE VERTEX TYPE Castles EXTENDS Attractions;\n");
			sb.append("CREATE INDEX ON Castles(Id) UNIQUE;\n");

			sb.append("CREATE VERTEX TYPE Theatres EXTENDS Attractions;\n");
			sb.append("CREATE INDEX ON Theatres(Id) UNIQUE;\n");

			sb.append("CREATE VERTEX TYPE ArchaeologicalSites EXTENDS Attractions;\n");
			sb.append("CREATE INDEX ON ArchaeologicalSites(Id) UNIQUE;\n");

			sb.append("CREATE VERTEX TYPE Customers;");
			sb.append("CREATE PROPERTY Customers.OrderedId LONG;");

			sb.append("CREATE VERTEX TYPE Orders;");
			sb.append("CREATE PROPERTY Orders.Id LONG;");
			sb.append("CREATE PROPERTY Orders.Amount LONG;");
			sb.append("CREATE PROPERTY Orders.OrderDate DATE;");
				
			sb.append("CREATE INDEX ON Customers(OrderedId) UNIQUE;");

			sb.append("CREATE INDEX ON Orders(Id) UNIQUE;");
					
			sb.append("CREATE EDGE TYPE HasUsedService;");
			sb.append("CREATE PROPERTY HasUsedService.out LINK OF Customers;");

			sb.append("CREATE EDGE TYPE HasStayed EXTENDS HasUsedService;");
			sb.append("CREATE PROPERTY HasStayed.in LINK OF Hotels;");

			sb.append("CREATE EDGE TYPE HasEaten EXTENDS HasUsedService;");
			sb.append("CREATE PROPERTY HasEaten.in LINK OF Restaurants;");

			sb.append("CREATE EDGE TYPE HasVisited;");
			sb.append("CREATE PROPERTY HasVisited.out LINK OF Customers;");
			sb.append("CREATE PROPERTY HasVisited.in LINK;");
			sb.append("CREATE INDEX ON HasVisited (`in`, `out`) UNIQUE;");

			sb.append("CREATE EDGE TYPE HasCustomer;");
			sb.append("CREATE PROPERTY HasCustomer.in LINK OF Customers;");
			sb.append("CREATE PROPERTY HasCustomer.out LINK OF Orders ;");

			sb.append("INSERT INTO Customers SET OrderedId = 1, Phone = '+1400844724';");
			sb.append("INSERT INTO Orders SET Id = 1, Amount = 536, OrderDate = '2013-05-23';");
			
			sb.append("INSERT INTO Hotels SET Id = 730, Name = 'Toules', Type = 'alpine_hut';");
				
			sb.append("INSERT INTO Restaurants SET Id = 1834, Name = 'Uliassi', Type = 'restaurant';");
			sb.append("INSERT INTO Restaurants SET Id = 1099, Name = 'L\\'Angelo d\\'Oro', Type = 'restaurant';");

			sb.append("INSERT INTO Restaurants SET Id = 1738, Name = 'Johnny Paranza', Type = 'fast_food';");
			
			sb.append("INSERT INTO Castles SET Id = 127, Name = 'Haselburg', Type = 'castle';");
			sb.append("INSERT INTO ArchaeologicalSites SET Id = 47, Name = 'Villa Romana', Type = 'archaeological_site';");
			sb.append("INSERT INTO Monuments  SET Id = 62, Name = 'Giuseppe Garibaldi', Type = 'monument';");
			sb.append("INSERT INTO Theatres SET Id = 65, Name = 'Teatro Civico', Type = 'theatre';");
		
			sb.append("CREATE EDGE HasStayed FROM (SELECT FROM Customers WHERE OrderedId=1) TO (SELECT FROM Hotels WHERE Id=730);");
			
			sb.append("CREATE EDGE HasEaten FROM (SELECT FROM Customers WHERE OrderedId=1) TO (SELECT FROM Restaurants WHERE Id=1834);");
			sb.append("CREATE EDGE HasEaten FROM (SELECT FROM Customers WHERE OrderedId=1) TO (SELECT FROM Restaurants WHERE Id=1099);");
			sb.append("CREATE EDGE HasEaten FROM (SELECT FROM Customers WHERE OrderedId=1) TO (SELECT FROM Restaurants WHERE Id=1738);");
			
			sb.append("CREATE EDGE HasCustomer FROM (SELECT FROM Orders WHERE Id=1) TO (SELECT FROM Customers WHERE OrderedId=1);");
			
			sb.append("CREATE EDGE HasVisited FROM (SELECT FROM Customers WHERE OrderedId=1) TO (SELECT FROM Castles WHERE Id=127);");
			sb.append("CREATE EDGE HasVisited FROM (SELECT FROM Customers WHERE OrderedId=1) TO (SELECT FROM ArchaeologicalSites WHERE Id=47);");
			sb.append("CREATE EDGE HasVisited FROM (SELECT FROM Customers WHERE OrderedId=1) TO (SELECT FROM Monuments WHERE Id=62);");
			sb.append("CREATE EDGE HasVisited FROM (SELECT FROM Customers WHERE OrderedId=1) TO (SELECT FROM Theatres WHERE Id=65);");
				
			sb.append("COMMIT;");
			
			db.command("SQLScript", sb.toString());
			
			ResultSet result = null;
			
			String sql = "SELECT FROM Services";
			
			System.out.println("\n1) Select Services\n");
			System.out.println(sql + "\n");
			result = db.command("SQL", sql);
			while (result.hasNext()) {
			    Result record = result.next();
			    System.out.println(record.toMap());
			 }
			
			sql = "SELECT FROM Attractions";
			System.out.println("\n2) Select Attractions\n");
			System.out.println(sql + "\n");
			result = db.command("SQL", sql);
			while (result.hasNext()) {
			    Result record = result.next();
			    System.out.println(record.toMap());
			 }
			
			sql = "SELECT FROM Locations";
			System.out.println("\n3) Select Locations\n");
			System.out.println(sql + "\n");
			result = db.command("SQL", sql );
			while (result.hasNext()) {
			    Result record = result.next();
			    System.out.println(record.toMap());
			 }
					
			sql = "MATCH {type: Customers, as: customer, where: (OrderedId=1)}--{type: Monuments} "
			    + "RETURN $pathelements";
			System.out.println("\n4) Match Monuments type\n");
			System.out.println(sql + "\n");
			result = db.query("SQL", sql);
			if (result.hasNext() == false)
			    System.out.println("No results");
			else
			    result.stream().forEach(
			        (t) -> { System.out.println(t.toMap().toString());} );
			
			sql = "MATCH {type: Customers, as: customer, where: (OrderedId=1)}--{type: Services} "
			    + "RETURN $pathelements";
			System.out.println("\n5) Match Services type\n");
			System.out.println(sql + "\n");
			result = db.query("SQL", sql);
			if (result.hasNext() == false)
			    System.out.println("No results");
			else
			    result.stream().forEach(
			       (t) -> { System.out.println(t.toMap().toString());} );
			
			sql = "MATCH {type: Customers, as: customer, where: (OrderedId=1)}--{type: Attractions} "
			    + "RETURN $pathelements";
			System.out.println("\n6) Match Attractions type\n");
			System.out.println(sql + "\n");
			result = db.query("SQL", sql);
			if (result.hasNext() == false)
			    System.out.println("No results");
			else
			    result.stream().forEach(
			        (t) -> { System.out.println(t.toMap().toString());} );
			
			sql = "MATCH {type: Customers, as: customer, where: (OrderedId=1)}--{type: Locations} "
			    + "RETURN $pathelements";
			System.out.println("\n7) Match Locations type\n");
			System.out.println(sql + "\n");
			result = db.query("SQL", sql);
			if (result.hasNext() == false)
			    System.out.println("No results");
			else
			    result.stream().forEach(
		                (t) -> { System.out.println(t.toMap().toString());} );
			
			db.close();

  		} finally {
  			if (db.isOpen())
  				db.close();
  		}
  	}
}

Output below

Match fails to match Parent types

1) Select Services

SELECT FROM Services

{Id=730, Name=Toules, Type=alpine_hut, @cat=v, @type=Hotels, @rid=#81:0}
{Id=1834, Name=Uliassi, Type=restaurant, @cat=v, @type=Restaurants, @rid=#129:0}
{Id=1099, Name=L'Angelo d'Oro, Type=restaurant, @cat=v, @type=Restaurants, @rid=#132:0}
{Id=1738, Name=Johnny Paranza, Type=fast_food, @cat=v, @type=Restaurants, @rid=#135:0}

2) Select Attractions

SELECT FROM Attractions

{Id=62, Name=Giuseppe Garibaldi, Type=monument, @cat=v, @type=Monuments, @rid=#217:0}
{Id=127, Name=Haselburg, Type=castle, @cat=v, @type=Castles, @rid=#265:0}
{Id=65, Name=Teatro Civico, Type=theatre, @cat=v, @type=Theatres, @rid=#313:0}
{Id=47, Name=Villa Romana, Type=archaeological_site, @cat=v, @type=ArchaeologicalSites, @rid=#361:0}

3) Select Locations

SELECT FROM Locations

{Id=730, Name=Toules, Type=alpine_hut, @cat=v, @type=Hotels, @rid=#81:0}
{Id=1834, Name=Uliassi, Type=restaurant, @cat=v, @type=Restaurants, @rid=#129:0}
{Id=1099, Name=L'Angelo d'Oro, Type=restaurant, @cat=v, @type=Restaurants, @rid=#132:0}
{Id=1738, Name=Johnny Paranza, Type=fast_food, @cat=v, @type=Restaurants, @rid=#135:0}
{Id=62, Name=Giuseppe Garibaldi, Type=monument, @cat=v, @type=Monuments, @rid=#217:0}
{Id=127, Name=Haselburg, Type=castle, @cat=v, @type=Castles, @rid=#265:0}
{Id=65, Name=Teatro Civico, Type=theatre, @cat=v, @type=Theatres, @rid=#313:0}
{Id=47, Name=Villa Romana, Type=archaeological_site, @cat=v, @type=ArchaeologicalSites, @rid=#361:0}

4) Match Monuments type

MATCH {type: Customers, as: customer, where: (OrderedId=1)}--{type: Monuments} RETURN $pathelements

{OrderedId=1, Phone=+1400844724, @cat=v, @type=Customers, @rid=#409:0}
{Id=62, Name=Giuseppe Garibaldi, Type=monument, @cat=v, @type=Monuments, @rid=#217:0}

5) Match Services type

MATCH {type: Customers, as: customer, where: (OrderedId=1)}--{type: Services} RETURN $pathelements

No results

6) Match Attractions type

MATCH {type: Customers, as: customer, where: (OrderedId=1)}--{type: Attractions} RETURN $pathelements

No results

7) Match Locations type

MATCH {type: Customers, as: customer, where: (OrderedId=1)}--{type: Locations} RETURN $pathelements

No results

@lvca
Copy link
Contributor

lvca commented Jan 14, 2024

Reproduced.

@lvca lvca self-assigned this Jan 14, 2024
@lvca lvca added the bug Something isn't working label Jan 14, 2024
@lvca lvca added this to the 24.1.1 milestone Jan 14, 2024
lvca added a commit that referenced this issue Jan 14, 2024
@lvca
fix: inheritance on edges was nto working in the SQL MATCH
c386985 
Fixed issue #1403
@lvca lvca added the fixed label Jan 14, 2024
@lvca
Copy link
Contributor

lvca commented Jan 14, 2024

Fixed. Thanks for the detailed test case!

@lvca lvca closed this as completed Jan 14, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@lvca lvca

Labels
bug Something isn't working fixed
Projects
None yet
Milestone
24.1.1
Development

No branches or pull requests
2 participants
@lvca @tmcsys