create a table but can't select from it

[l1t1]

:) create table a engine=Memory as select 1 a;

0.11995077133178711
:) select * from a;
Code: 60. DB::Exception: Table _local.a does not exist. (UNKNOWN_TABLE)

0.11473512649536133

[l1t1]

CHDB VERSION: 1.1.0

[l1t1]

the mergetree engine table is ok, memory engine table is wrong.

>>> from chdb import session as chs
>>>
>>> sess = chs.Session()
>>>
>>> sess.query("CREATE DATABASE test_db ENGINE = Atomic;")

>>> sess.query("CREATE TABLE test_db.test_table (x String, y String) ENGINE = MergeTree ORDER BY tuple()")
>>> sess.query("INSERT INTO test_db.test_table (x, y) VALUES ('A', 'B')")

>>>
>>> print("Original values:")
Original values:
>>> print(sess.query("SELECT * FROM test_db.test_table"))
"A","B"

>>>
>>> sess.query("ALTER TABLE test_db.test_table UPDATE y = 'updated' WHERE x = 'A' AND y = 'B'")

>>>
>>> print('Values after UPDATE (expected "A", "updated"):')
Values after UPDATE (expected "A", "updated"):
>>> print(sess.query("SELECT * FROM test_db.test_table"))
"A","updated"

>>> sess.query("create table a engine=Memory as select 1 a;")

>>> sess.query("select * from a")
Code: 60. DB::Exception: Table _local.a does not exist. (UNKNOWN_TABLE)
 

>>> sess.query("create table test_db.a engine=Memory as select 1 a;")

>>> sess.query("select * from test_db.a")

>>> print(sess.query("select * from test_db.a"))

[l1t1]

the memory engine table is created, but can not select it's data. Log engine table is ok too.

>>> sess.query("create table test_db.a engine=Log as select 1 a;")
Code: 57. DB::Exception: Table test_db.a already exists. (TABLE_ALREADY_EXISTS)

>>> sess.query("create table test_db.a2 engine=Log as select 1 a;")

>>> print(sess.query("select * from test_db.a2"))
1

[auxten]

Which CLI are you using?

[l1t1]

I use Python cli

[auxten]

Current chDB session relys on temp disk storage to keep the session data.
As a consequence, Memory engine could not keep state in session.

Possible fix could be re-implement the session with "interactive mode" of clickhouse-local.
It's not an easy fix. I will think about it.

[auxten]

Issue created for this #197