Small Read Consistency Oddity

Here is a small oddity concerning read consistency.

I doubt it is relevant at all, however, it’s quite amusing.

Statement level read consistency guarantees that every statement sees exactly the data as of the point in time when the statement was opened.

Consider following example:

sokrates@11.2 > select table_name from user_tables where table_name = 'NOT_YET_EXISTENT';

no rows selected

Hence, we expect, that the following CTAS , which I call (T)

sokrates@11.2 > create table NOT_YET_EXISTENT
as
select table_name from user_tables where table_name = 'NOT_YET_EXISTENT';

Table created.

creates a table with no rows in it, since at the point in time when (T) started, there was no table in our schema named NOT_YET_EXISTENT.

sokrates@11.2 > select count(*) from NOT_YET_EXISTENT;

COUNT(*)
----------
0

As expected, (T) respected read consistency.

Now, let’s repeat the same experiment with one small variation.

sokrates@11.2 > drop table NOT_YET_EXISTENT;

Table dropped.

sokrates@11.2 > select object_name from user_objects where object_type = 'TABLE' and object_name = 'NOT_YET_EXISTENT';

no rows selected

So, the following CTAS called (O)

sokrates@11.2 > create table NOT_YET_EXISTENT
as
select object_name from user_objects where object_type = 'TABLE' and object_name = 'NOT_YET_EXISTENT';

Table created.

should also create an empty table.

sokrates@11.2 > select count(*) from NOT_YET_EXISTENT;

COUNT(*)
----------
1

!

Amusing, when (O) started, NOT_YET_EXISTENT was not yet existent, however, its select-part falsely returned one row (which only existed at the point in time when this SQL was finished !)

Obviously, read consistency was violated by (O).