Weird result of using CTE

Question

Could somebody explain me why this script returns 'some_word' but not error about not existed schema trying to retrieve data from schema_that_doesnt_exist.tab?

with tab as 
(
     select 'some_word' str 
     from dual
)
select * 
from schema_that_doesnt_exist.tab;

Some URL on Oracle documentation about this question will help me too.

Answer 1

I guess it has connection with qualified names bypass with:

MariaDB Demo

Oracle Demo

SQLite Demo -- no such table: schema_that_doesnt_exists.tab

PostgreSQL Demo -- relation "schema_that_doesnt_exists.tab" does not exist

SQLServer Demo -- Invalid object name 'schema_that_doesnt_exists.tab'.

---

Same as:

^{Image from: https://modern-sql.com/blog/2018-04/mysql-8.0}

Anyway it could be useful when you need to mock some data for database unit tests (read only queries).

For example:

SELECT *
FROM schema.table_name  -- here goes real data (lots of records)
WHERE col = 'sth';

If I want to prepare input dataset for test I have to work with actual data. Using WITH I could rewrite it as:

WITH table_name AS (
    SELECT 'sth'  AS col, ... FROM dual UNION ALL
    SELECT 'sth2' AS col, ... FROM dual...
)
SELECT *
FROM schema.table_name  -- cte is closer and data is taken from it
WHERE col = 'sth';

More: Unit Tests on Transient Data