Bug on empty string condition

[webigorkiev]

Describe the bug

 
SELECT COUNT(*) FROM test WHERE lo = '';  
  
 ----------   
| count(*) |  
 ----------   
|        0 |  
 ----------   
1 row in set (0.000 sec)  
  
SELECT COUNT(*) FROM test WHERE lo != '';  
  
 ----------   
| count(*) |  
 ----------   
|     2526 |  
 ----------   
1 row in set (0.000 sec)  
  
SELECT COUNT(*) FROM test;  
  
 ----------   
| count(*) |  
 ----------   
|     5000 |  
 ----------   
1 row in set (0.000 sec)  
  

Some strange results in the select

To Reproduce

tar -xOzf test.tar.gz dump.sql | mysql -P9306
FLUSH RAMCHUNK test;

And then follow the section Describe the bug.

Without FLUSH RAMCHUNK test; How long is the behavior normal
Appears only if the table has a lot of string fields and enough records

Describe the environment:
Manticore 6.0.2 89c7a51@230210 (columnar 2.0.0 a7c703d@230130) (secondary 2.0.0 a7c703d@230130)

test.tar.gz

[webigorkiev]

common {
    plugin_dir = /usr/local/lib/manticore
    lemmatizer_base = /var/www/manticore
}

searchd {
    listen = 9306:mysql
    listen = 127.0.0.1:9308:http
    log = /var/log/manticore/searchd.log
    query_log = /var/log/manticore/query.log
    query_log_mode = 750
    query_log_min_msec = 5000
    pid_file = /var/run/manticore/searchd.pid
    data_dir = /var/lib/manticore
    query_log_format = sphinxql

    qcache_max_bytes = 134217728
    qcache_thresh_msec = 0
    qcache_ttl_sec = 60
    pseudo_sharding = 1
    max_packet_size = 128M
    network_timeout = 60
    rt_flush_period = 1800
    rt_merge_iops = 500
    binlog_max_log_size = 16M
}

[webigorkiev]

It also affects the result of selecting records.

`
SELECT * FROM test WHERE lo = '' LIMIT 1000, 1000 OPTION max_matches = 5000;

Empty set (0.000 sec)
`

[webigorkiev]

Try
Manticore 6.0.3 91f8438@230221 dev (columnar 2.0.0 a7c703d@230130) (secondary 2.0.0 a7c703d@230130)

behavior is the same

[sanikolaev]

tar -xOzf test.tar.gz dump.sql | mysql -P9306 may fail. I could reproduce the issue with:

(echo "drop table test;"; tar -xOzf test.tar.gz)|mysql -P9306 -h0

The MRE one-liner is:

# c=1024; mysql -v -P9306 -h0 -e "drop table if exists t; create table t(s string);"; for n in `seq 1 $c`; do mysql -P9306 -h0 -e "insert into t values(0,'')"; done; echo "adding $c non-empty and empty strings..."; for n in `seq 1 $c`; do mysql -P9306 -h0 -e "insert into t values(0,'1')"; done; mysql -v -P9306 -h0 -e "flush ramchunk t; select count(*) from t; select count(*) from t where s!=''; select count(*) from t where s=''; select count(*) from t where s='' /*+ NO_SecondaryIndex(s) */;";
--------------
drop table if exists t
--------------

--------------
create table t(s string)
--------------

adding 1024 non-empty and empty strings...
--------------
flush ramchunk t
--------------

--------------
select count(*) from t
--------------

+----------+
| count(*) |
+----------+
|     2048 |
+----------+
--------------
select count(*) from t where s!=''
--------------

+----------+
| count(*) |
+----------+
|     1024 |
+----------+
--------------
select count(*) from t where s=''
--------------

+----------+
| count(*) |
+----------+
|        0 |
+----------+
--------------
select count(*) from t where s='' /*+ NO_SecondaryIndex(s) */
--------------

+----------+
| count(*) |
+----------+
|     1024 |
+----------+

As you can see adding the CBO hint to disable the secondary index helps.

Lowering the count from 1024 to 1023 also solves the issue.

[githubmanticore]

➤ Ilya Kuznetsov commented:

Fixed in MCL e32ff846