I have a correct subquery that returns results but when I use it as an "IN" clause I get error 1064 This must be a very simple problem, the subquery returns results alone but the compound query reports:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'table where table in (select table_name from information_schema.tables where tab' at line 1

Here's the query:
select * 
from table 
where table in 
    (select table_name 
     from information_schema.tables 
     where table_schema='my_database' 
     limit 1);

I have also tried it without the limit, that was my first attempt:
select * 
from table 
where table in 
    (select table_name 
     from information_schema.tables 
     where table_schema='my_database');
You can't SELECT like this, where you dynamically SELECT the table name. That's why it's not working. The subselect works, but.. try to replace subselect by a constant (i.e. known) name and it still won't work.
For something like this you either need an if then else flow or dynamic SQL or do it on application side.