Nested SubQueries do not work as expected

[bc-saranya]

Description - Nested subqueries leads to an error TypeError: Cannot read properties of undefined (reading '0')

I have created three tables like below:

alasql("CREATE TABLE cities (city string, population number)");
alasql("INSERT INTO cities VALUES ('Rome',2863223), ('Paris',2249975), ('Berlin',3517424),  ('Madrid',3041579),('Easingwold',4627)");

alasql("CREATE TABLE countries (name string, population number, city string)");
alasql("INSERT INTO countries VALUES ('Italy', 89764679009, 'Rome'), ('France', 165247191, 'Paris'), ('Germany', 346186257, 'Berlin')");

alasql("CREATE TABLE population (number int)");
alasql("INSERT INTO population VALUES (89764679009), (165247191)");

I use a SELECT query with nested subqueries, e.g -

SELECT * FROM cities WHERE city IN (SELECT DISTINCT city FROM countries WHERE population IN (SELECT DISTINCT number from population))

Expected output - Rome, Paris

Actual Output - TypeError: Cannot read properties of undefined (reading '0')

[SuvitsonHarrese]

alasql("CREATE TABLE cities (city string, population number)"); alasql("INSERT INTO cities VALUES ('Rome',2863223), ('Paris',2249975), ('Berlin',3517424), ('Madrid',3041579),('Easingwold',4627)");

alasql("CREATE TABLE countries (name string, population number, city string)"); ^^^^^^ -> population column is declared as number which is not a valid type replcaing it with int has yield the expected output

alasql("INSERT INTO countries VALUES ('Italy', 89764679009, 'Rome'), ('France', 165247191, 'Paris'), ('Germany', 346186257, 'Berlin')");

alasql("CREATE TABLE population (number int)"); alasql("INSERT INTO population VALUES (89764679009), (165247191)");

SELECT * FROM cities WHERE city IN (SELECT DISTINCT city FROM countries WHERE population IN (SELECT DISTINCT number from population))

[paulrutter]

@SuvitsonHarrese https://github.com/AlaSQL/alasql#traditional-sql-table does show number as possible column value though.

@bc-saranya we can check this out.

It would be nice if the parser would throw an error on unknown data types though.

Somewhere here probably https://github.com/AlaSQL/alasql/blob/ca7ecb4d38a5810d22d05ea7eac7f60f7f10ba75/src/60createtable.js#L16

[paulrutter]

@SuvitsonHarrese I tried by changing the data type to int, but still the same error occurs. What did you try to resolve it?

[SuvitsonHarrese]

yeah now i checked again I got the exact same error dont know how it worked on that day

[SuvitsonHarrese]

@paulrutter i think the problem is with the nesting in subqueries that means a query can have one subquery and no subquery within subquery of main query

alasql("CREATE TABLE t1 (points int)");
alasql("INSERT INTO  t1 (1),(2),(3),(4)");

alasql("CREATE TABLE t2 (height int)");

alasql("INSERT INTO t2 VALUES (2), (3), (4)");

alasql("CREATE TABLE t3 (breadth int)");
alasql("INSERT INTO t3 VALUES (3), (4)");
console.log(
  alasql("SELECT * FROM t2 WHERE height IN (SELECT breadth from t3)")
);

console.log(alasql("SELECT * FROM t2 WHERE height IN (SELECT points from t1)"));

console.log(
  alasql("SELECT * FROM t3 WHERE breadth IN (SELECT points from t1)")
);
console.log(
  alasql("SELECT * FROM t3 WHERE breadth IN (SELECT breadth from t3)")
);

console.log(
  alasql(
    "SELECT * FROM t3 WHERE breadth IN (SELECT * FROM t3 WHERE breadth IN (SELECT breadth from t3))"
  )
);
// last line of code is returning the same error whereas other query with different combination of subqueries are working fine