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

alasql/src/60createtable.js

Line 16 in ca7ecb4

if (this.dbtypeid) {

[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

[choia4]

hi, I was wondering if this issue got resolved. If not, can I pick it up?

[paulrutter]

It's not resolved as far as i know, so you can work on it for sure. @choia4

[znenuko]

Verify Subqueries Individually:

Subquery 1:
SELECT DISTINCT city FROM countries WHERE population IN (
    SELECT DISTINCT number FROM population
);

Ensure this subquery returns expected city values.

Subquery 2:

SELECT DISTINCT number FROM population;

Ensure this subquery returns the correct numbers.

Execute the Main Query:

With verified subqueries, combine them into the main query:

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)");

// Execute the query
let result = alasql(`
    SELECT * FROM cities WHERE city IN (
        SELECT DISTINCT city FROM countries WHERE population IN (
            SELECT DISTINCT number FROM population
        )
    )
`);

console.log(result);  // Expected to show 'Rome' and 'Paris'