Comparing symbol columns

Community
,
1

Hi,
I am encountering a strange issue when comparing 2 symbol columns. for example:

select * from table1 where symbol1 != symbol2

returns rows where symbol1 is exactly the same value as symbol2. If I cast either one of them to varchar then it works as expected.

select * from table1 where cast(symbol1 as varchar) != symbol2 → works correctly

Am I missing something?

thanks.

2

Hey @ack22

Could you post your schema, and run the query with EXPLAIN to show the query plan?

This will help us to investigate.

3

Following are the details :

CREATE TABLE 'sample' (
  tdate TIMESTAMP,
  reg SYMBOL capacity 8 CACHE,
  id INT,
  symbol1 SYMBOL capacity 65536 CACHE,
  symbol2 SYMBOL capacity 131072 CACHE,
  pk SYMBOL capacity 65536 CACHE,
  cur SYMBOL capacity 128 CACHE,
  px FLOAT,
  qty LONG
) timestamp (tdate) PARTITION BY DAY WAL DEDUP UPSERT KEYS(tdate, id, symbol1);

the query explain :

Async JIT Filter workers: 6
     filter: symbol1!=symbol2
         PageFrame
             Row forward scan
             Frame forward scan on: sample
4

Any thoughts here? thanks

5

Hi,

Do you have a CSV or similar we can import? I’ve been trying to reproduce, but no luck. This is my reproducer

CREATE TABLE 'sample' (
  tdate TIMESTAMP,
  reg SYMBOL capacity 8 CACHE,
  id INT,
  symbol1 SYMBOL capacity 65536 CACHE,
  symbol2 SYMBOL capacity 131072 CACHE,
  pk SYMBOL capacity 65536 CACHE,
  cur SYMBOL capacity 128 CACHE,
  px FLOAT,
  qty LONG
) timestamp (tdate) PARTITION BY DAY WAL DEDUP UPSERT KEYS(tdate, id, symbol1);

truncate table 'sample';


INSERT INTO 'sample'
SELECT
    timestamp_sequence(
        to_timestamp('2023-01-01', 'yyyy-MM-dd'),
        100000000L 
    ) AS tdate,
    rnd_symbol('AAA', 'BBB', 'CCC', 'DDD', 'EEE') AS reg,
    x AS id,
    rnd_symbol('BBB', 'CCC', 'DDD', 'EEE', 'AAA') AS symbol1,
    rnd_symbol('CCC', 'DDD', 'EEE', 'AAA', 'BBB') AS symbol2,
    rnd_symbol('DDD', 'EEE', 'AAA', 'BBB', 'CCC') AS pk,
    rnd_symbol('EEE', 'AAA', 'BBB', 'CCC', 'DDD') AS cur,
    rnd_float(0) * 100 AS px, 
    rnd_long(1, 10000, 0) AS qty 
FROM
    long_sequence(5000); 

select * from 'sample';

select * from 'sample' where symbol1 != symbol2 order by symbol1, symbol2;
6

Hi Javier,
Strange… I just tried it now and it works fine as expected. Was there any fix between 8.2 and 8.2.1? The only difference is that I restarted the instance over the weekend and I upgrade to 8.2.1.

Given either of us are not able to reproduce - we move on to next problem.

Thanks.

1 Like