[MCOL-1553] Filtering of rows (like, in , '>' etc.) does not work for information_schema Created: 2018-07-10  Updated: 2022-11-05  Resolved: 2022-11-05

Status: Closed
Project: MariaDB ColumnStore
Component/s: MDB Plugin
Affects Version/s: 1.1.5
Fix Version/s: Icebox

Type: Bug Priority: Major
Reporter: Ravi Prakash (Inactive) Assignee: Unassigned
Resolution: Won't Do Votes: 0
Labels: beginner-friendly
Environment:

Centos release 7.4


 Description   

A number of operators in where clause filtering do not work and return wrong results. For example:
MariaDB [information_schema]> select table_schema, table_name, object_id, column_count from columnstore_tables where table_name in ('t1','t2');
---------------------------------------------+

table_schema table_name object_id column_count

---------------------------------------------+

test t1 24442 1

---------------------------------------------+
1 row in set (0.04 sec)

MariaDB [information_schema]> select table_schema, table_name, object_id, column_count from columnstore_tables where table_name in ('t2');
---------------------------------------------+

table_schema table_name object_id column_count

---------------------------------------------+

test t2 24446 2
datos t2 25221 2

---------------------------------------------+
2 rows in set (0.06 sec)

MariaDB [information_schema]> select table_schema, table_name, object_id, column_count from columnstore_tables where table_name like 't%';
Empty set (0.01 sec)
MariaDB [information_schema]> select table_schema, table_name, object_id, column_count from columnstore_tables where table_schema > 't';
Empty set (0.02 sec)

This worked:
MariaDB [information_schema]> select table_schema, table_name, object_id, column_count from columnstore_tables where table_name = 't1';
---------------------------------------------+

table_schema table_name object_id column_count

---------------------------------------------+

test t1 24442 1

---------------------------------------------+
1 row in set (0.04 sec)

This prevents one from developing "admin" scripts" to do analysis on objects in the database.

 Comments   
Comment by Keita Yaegashi [ 2019-01-09 ]

This problem is still reproduced in the latest version 1.2.2.

For example, given the following condition:

MariaDB [databasetest]> CREATE TABLE table_a (test int) ENGINE=Columnstore DEFAULT CHARSET=utf8;
 
Query OK, 0 rows affected (0.18 sec)
 
 
 
MariaDB [databasetest]> CREATE TABLE table_b (test int) ENGINE=Columnstore DEFAULT CHARSET=utf8;
 
Query OK, 0 rows affected (0.17 sec)
 
 
 
MariaDB [databasetest]> CREATE TABLE table_c (test int) ENGINE=Columnstore DEFAULT CHARSET=utf8;
 
Query OK, 0 rows affected (0.15 sec)
 
 
 
MariaDB [databasetest]> CREATE TABLE table_d (test int) ENGINE=Columnstore DEFAULT CHARSET=utf8;
 
Query OK, 0 rows affected (0.15 sec)
 
 
 
MariaDB [databasetest]> CREATE TABLE table_e (test int) ENGINE=Columnstore DEFAULT CHARSET=utf8;
 
Query OK, 0 rows affected (0.15 sec)
 
 
 
MariaDB [databasetest]> CREATE TABLE table_f (test int) ENGINE=Columnstore DEFAULT CHARSET=utf8;
 
Query OK, 0 rows affected (0.15 sec)
 
 
 
MariaDB [databasetest]> CREATE TABLE table_g (test int) ENGINE=Columnstore DEFAULT CHARSET=utf8;
 
Query OK, 0 rows affected (0.14 sec)
 
 
 
MariaDB [databasetest]> SELECT table_name FROM information_schema.columnstore_tables WHERE table_schema = 'databasetest';
 
+------------+
 
| table_name |
 
+------------+
 
| table_a    |
 
| table_b    |
 
| table_c    |
 
| table_d    |
 
| table_e    |
 
| table_f    |
 
| table_g    |
 
+------------+
 
7 rows in set (0.15 sec)
 
 
 
MariaDB [databasetest]> SELECT table_name FROM information_schema.columnstore_tables WHERE table_schema = 'databasetest' AND (table_name = 'table_a' OR table_name = 'table_b' OR table_name = 'table_c' OR table_name = 'table_d' OR table_name = 'table_e' OR table_name = 'table_f' OR table_name = 'table_g');
 
+------------+
 
| table_name |
 
+------------+
 
| table_a    |
 
| table_b    |
 
| table_c    |
 
| table_d    |
 
| table_e    |
 
| table_f    |
 
| table_g    |
 
+------------+
 
7 rows in set (0.11 sec)

the following queries fail:

MariaDB [databasetest]> SELECT table_name FROM information_schema.columnstore_tables WHERE table_schema = 'databasetest' AND table_name LIKE 'table\_%';
 
Empty set (0.01 sec)
 
 
 
MariaDB [databasetest]> SELECT table_name FROM information_schema.columnstore_tables WHERE table_schema = 'databasetest' AND table_name LIKE 'table%';
 
Empty set (0.01 sec)
 
 
 
MariaDB [databasetest]> SELECT table_name FROM information_schema.columnstore_tables WHERE table_schema = 'databasetest' AND table_name IN ('table_a', 'table_b', 'table_c', 'table_d', 'table_e', 'table_f', 'table_g');
 
+------------+
 
| table_name |
 
+------------+
 
| table_a    |
 
+------------+
 
1 row in set (0.02 sec)
 
 
 
MariaDB [databasetest]> SELECT table_name FROM information_schema.columnstore_tables WHERE table_schema = 'databasetest' AND LEFT(table_name, 5) = 'table';
 
ERROR 2013 (HY000): Lost connection to MySQL server during query

In the cases above,

+------------+
 
| table_name |
 
+------------+
 
| table_a    |
 
| table_b    |
 
| table_c    |
 
| table_d    |
 
| table_e    |
 
| table_f    |
 
| table_g    |
 
+------------+

is expected for all SELECT example queries.

Comment by Roman [ 2019-03-15 ]

Greetings,
Many thanks for a crash report. We are still working on I_S tables filtering.

Comment by Roman [ 2019-04-09 ]

Revising the issue this one isn't very beginner-friendly but it is suitable for the next GSoC.

Comment by Todd Stoffel (Inactive) [ 2022-11-05 ]

This item is being closed because it was well passed the expiration date with no activity. If you suspect this was done in error please create a new ticket.

Generated at Thu Feb 08 02:29:37 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.