Behavior of IN in Oracle

Question

I have written a subquery to join two tables and fetch the data, where one ID column is common for both to join. But the column name is different as below.

This one I have written:

SELECT parent_id ,name   
FROM parent_table 
WHERE parent_id IN (SELECT parent_id 
                    FROM child_table 
                    WHERE country IN ('US'));

It's giving all of the rows from the parent table as the subquery SELECT parent_id FROM child_table WHERE country IN ('US') seems incorrect. There is no parent_id column in the table.

Below query is correct one:

SELECT parent_id ,name   
FROM parent_table 
WHERE parent_id IN (SELECT child_id 
                    FROM child_table 
                    WHERE country IN ('US'));

Now my question is: Why the first query is not giving any error since the subquery is incorrect?

The subquery executed in isolation will return ORA-00904.

Answer 1

Oracle is using the parent_id column from the outer query.

If you qualify the columns with the tables they are referencing then Oracle is doing:

SELECT parent_table.parent_id,
       parent_table.name   
FROM   parent_table
WHERE  parent_table.parent_id IN (
  SELECT parent_table.parent_id
--       ^^^^^^
  FROM   child_table
  WHERE  child_table.country IN ('US')
);

Which will return all rows and will not raise an exception.

But you were expecting:

SELECT parent_table.parent_id,
       parent_table.name   
FROM   parent_table
WHERE  parent_table.parent_id IN (
  SELECT child_table.parent_id
--       ^^^^^
  FROM   child_table
  WHERE  child_table.country IN ('US')
);

Which would raise an exception as there is no parent_id column in child_table.

Answer 2

I had to upgrade my whole project from older flutter version (1.22) to the latest one. Took a bit of work to update all the packages and implement all their changes, but it works now.

Answer 3

What got it working for me was upgrading to the latest version of flutter and using these package versions

  http: 0.12.2
  firebase_core: ^0.7.0
  cloud_firestore: ^0.16.0