Bug on rt_attr_timestamp, different results for "same" conditions.

[ivanghisleni]

Describe the bug

I'm facing a strange behavior with a query on Manticore v3.6.3, if I execute the following queries I obtain a different result even if the condition it is the same

To Reproduce Steps to reproduce the behavior:

Case 1: (CORRECT)

SELECT count(*)
FROM rt1 
WHERE deleted = 0 
AND customer = 52 
AND ts_sent <= 1567174413 
AND ts_sent >= 1672531200 
AND MATCH('') 
Result:
+----------+
| count(*) |
+----------+
|        0 |
+----------+

Case 2: (take only last ts_sent <= 1567174413 )

SELECT count(*)
FROM rt1 
WHERE deleted = 0 
AND customer = 52 
AND ts_sent <= 1567174413 
AND ts_sent >= 1672531200 
AND MATCH('') 
AND ts_sent <= 1567174413
+----------+
| count(*) |
+----------+
|    11466 |
+----------+

Case 3: (take only last ts_sent <= 1567174413 )

SELECT count(*)
FROM rt1 
WHERE deleted = 0 
AND customer = 52 
AND ts_sent <= 1567174413 
AND ts_sent >= 1672531200 
AND MATCH('') 
AND ts_sent <= 1567174413
AND ts_sent >= 1672531200 
+----------+
| count(*) |
+----------+
|    11466 |
+----------+

Case 4: (CORRECT)

SELECT count(*)
FROM rt1 
WHERE deleted = 0 
AND customer = 52 
AND MATCH('') 
AND ts_sent <= 1567174413
AND ts_sent >= 1672531200
Result:
+----------+
| count(*) |
+----------+
|        0 |
+----------+

Expected behavior Basically I'm doing the same search, but I don't know why I obtain different result

Describe the environment:

• Server version: 6.2.12 dc5144d35@230822 (columnar 2.2.4 5aec342@230822) (secondary 2.2.4 5aec342@230822) git branch manticore-6.2.12...origin/manticore-6.2.12
• OS version: centos-release-7-9.2009.1.el7.centos.x86_64

Messages from log files:

Additional context

[ivanghisleni]

The issue was identified at first on Manticore v3.6.3 and tested on v6.2.12 with the same behavior.

[sanikolaev]

Thanks for the bug report. I can't reproduce it on a simple synthetic case like:

mysql> drop table if exists t; create table t(a int); insert into t(a) values(1),(2),(3),(4),(5); flush ramchunk t; select count(*) from t where a < 2 and a > 4 and a < 2 and match('');
--------------
drop table if exists t
--------------

Query OK, 0 rows affected (0.01 sec)

--------------
create table t(a int)
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
insert into t(a) values(1),(2),(3),(4),(5)
--------------

Query OK, 5 rows affected (0.00 sec)

--------------
flush ramchunk t
--------------

Query OK, 0 rows affected (0.01 sec)

--------------
select count(*) from t where a < 2 and a > 4 and a < 2 and match('')
--------------

+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

Can you share your table files and your config (https://manual.manticoresearch.com/Reporting_bugs#Uploading-your-data) or, best of all, provide a script like the above which reproduces the issue from scratch?

[ivanghisleni]

Uploaded (issue-1523):

• insert_RT.sql (Sample of how to populate DB)
• manticore.conf

[sanikolaev]

I get:

snikolaev@dev2:~/issue-1523/repro$ mysql -P9315 -h0 < insert_RT.sql
ERROR 1064 (42000) at line 1: unknown column: 'folders'

when I try to load the sample data to the table. If I remove the folder* columns from the sql file I get:

snikolaev@dev2:~/issue-1523/repro$ mysql -P9315 -h0 < insert_RT.sql
ERROR 1064 (42000) at line 1: INTERNAL ERROR: plugin archiver_sphinx_integration.so:untokenize_dot_at loaded ok but lookup fails, error: dlopen() failed: ./archiver_sphinx_integration.so: cannot open shared object file: No such file or directory

If it's important to reproduce the issue, please provide the .so files or the sources.

If I disable using the udf, I can populate the table, but I can't reproduce the issue:

snikolaev@dev2:~/issue-1523/repro$ mysql -P9315 -h0
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 772
Server version: 6.2.12 dc5144d35@230822 (columnar 2.2.4 5aec342@230822) (secondary 2.2.4 5aec342@230822) git branch manticore-6.2.12...origin/manticore-6.2.12

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select count(*) from rt1 where deleted = 0  AND customer = 1 AND ts_sent = 1672531199 AND MATCH('');
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT count(*) FROM rt1  WHERE deleted = 0  AND customer = 1  AND ts_sent <= 1567174413  AND ts_sent >= 1672531198  AND MATCH('');
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT count(*) FROM rt1  WHERE deleted = 0  AND customer = 1  AND ts_sent <= 1567174413  AND ts_sent >= 1672531198  AND MATCH('') AND ts_sent <= 1567174413;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT count(*) FROM rt1  WHERE deleted = 0  AND customer = 1  AND ts_sent <= 1567174413  AND ts_sent >= 1672531198  AND MATCH('') AND ts_sent <= 1567174413 AND ts_sent >= 1672531198;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

[ivanghisleni]

File uploaded

[tomatolog]

I unable to reproduce the issue you described.

I tried with .so file you provided and insert_RT.sql where I deleted folders, folder_test, folder_test2 and these do not mapped into rt index at manticore.conf and have correct reply

mysql> select count(*) from rt1 where deleted = 0  AND customer = 1 AND ts_sent = 1672531199 AND MATCH('');
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)
--- 1 out of 1 results in 0ms ---

mysql> SELECT count(*) FROM rt1  WHERE deleted = 0  AND customer = 1  AND ts_sent <= 1567174413  AND ts_sent >= 1672531198  AND MATCH('');
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)
--- 0 out of 0 results in 0ms ---

mysql>  SELECT count(*) FROM rt1  WHERE deleted = 0  AND customer = 1  AND ts_sent <= 1567174413  AND ts_sent >= 1672531198  AND MATCH('') AND ts_sent <= 1567174413 AND ts_sent >= 1672531198;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.01 sec)
--- 0 out of 0 results in 0ms ---

Please provide Dockerfile with all files needs for reproduce issue locally here. That I could run and see inside the container some queries produce correct result set and other produce incorrect result set.

[ivanghisleni]

Ok, I'll do it.

[ivanghisleni]

@sanikolaev In order to create a dockerfile I can to start from Debian image ?

[tomatolog]

you could use any image which do you want to create reproducible case.