Similar queries return different results with different orderings of the where clause

[algotr34]

Is dolt running mysql or just an emulated mysql? because I get strange behavior from dolt sometimes, on very simple queries.

I am running these sample queries against the database at https://www.dolthub.com/repositories/post-no-preference/stocks

The test queries are:

1. SELECT *,LENGTH(o.act_symbol) FROM stocks.ohlcv o WHERE LENGTH(o.act_symbol)<5 AND o.date>"2023-11-01" AND o.act_symbol='ACHR';
2. SELECT *,LENGTH(o.act_symbol) FROM stocks.ohlcv o WHERE o.date>"2023-11-01" AND o.act_symbol='ACHR' AND LENGTH(o.act_symbol)<5;
3. SELECT *,LENGTH(act_symbol) FROM stocks.ohlcv WHERE LENGTH(act_symbol)<5 AND date>"2023-11-01" AND act_symbol='ACHR';
4. SELECT *,LENGTH(act_symbol) FROM stocks.ohlcv WHERE date>"2023-11-01" AND act_symbol='ACHR' AND LENGTH(act_symbol)<5;

Please notice that these 4 queries are actually the same query. And they all should return data!

• Query 1 and 2 are using "o" table alias, and the only difference between them is the position of the checks inside the WHERE clause.
• Query 3 and 4 arent using table alias, and the only difference between them is the position of the checks inside the WHERE clause.

Now, when I run these queries through dbeaver (after loading the server by running: dolt sql-server), i get the following results:

1. I get results !
2. 0 results
3. 0 results
4. 0 results

Wtf?
When i try dolt sql -q "(query)", even the first query is getting stuck, and eventually returns no data.
Huh?

I'm guessing that its not a mysql bug... but you will be able to tell me...

[timsehn]

It's emulated MySQL.

We will look into this Monday.

[timsehn]

I am unable to repro if I change the double quotes to single quotes:

$ dolt clone post-no-preference/stocks
cloning https://doltremoteapi.dolthub.com/post-no-preference/stocks
$ cd stocks 
$ dolt sql -q "SELECT *,LENGTH(o.act_symbol) FROM stocks.ohlcv o WHERE LENGTH(o.act_symbol)<5 AND o.date>"2023-11-01" AND o.act_symbol='ACHR';"
^C
$ dolt sql -q "SELECT *,LENGTH(o.act_symbol) FROM stocks.ohlcv o WHERE LENGTH(o.act_symbol)<5 AND o.date>'2023-11-01' AND o.act_symbol='ACHR';"
+------------+------------+------+------+------+-------+---------+----------------------+
| date       | act_symbol | open | high | low  | close | volume  | LENGTH(o.act_symbol) |
+------------+------------+------+------+------+-------+---------+----------------------+
| 2023-11-02 | ACHR       | 4.93 | 5.20 | 4.88 | 5.13  | 6424569 | 4                    |
| 2023-11-03 | ACHR       | 5.20 | 5.50 | 5.20 | 5.47  | 4632925 | 4                    |
| 2023-11-06 | ACHR       | 5.52 | 5.61 | 5.23 | 5.35  | 4554518 | 4                    |
| 2023-11-07 | ACHR       | 5.32 | 5.49 | 5.21 | 5.36  | 3422180 | 4                    |
| 2023-11-08 | ACHR       | 5.36 | 5.51 | 5.20 | 5.21  | 4770496 | 4                    |
| 2023-11-09 | ACHR       | 5.27 | 5.40 | 5.12 | 5.26  | 6423177 | 4                    |
| 2023-11-10 | ACHR       | 5.11 | 5.29 | 4.78 | 4.97  | 6779428 | 4                    |
| 2023-11-13 | ACHR       | 4.99 | 5.20 | 4.88 | 5.20  | 6193570 | 4                    |
| 2023-11-14 | ACHR       | 5.48 | 5.99 | 5.48 | 5.98  | 9265805 | 4                    |
| 2023-11-15 | ACHR       | 6.10 | 6.58 | 6.03 | 6.45  | 9699982 | 4                    |
| 2023-11-16 | ACHR       | 6.39 | 6.41 | 6.10 | 6.26  | 7903983 | 4                    |
| 2023-11-17 | ACHR       | 6.37 | 6.44 | 6.17 | 6.32  | 5141332 | 4                    |
| 2023-11-20 | ACHR       | 6.44 | 6.65 | 6.13 | 6.24  | 6745410 | 4                    |
| 2023-11-21 | ACHR       | 6.15 | 6.22 | 5.83 | 5.90  | 6232083 | 4                    |
| 2023-11-22 | ACHR       | 5.96 | 6.09 | 5.86 | 6.02  | 3017880 | 4                    |
| 2023-11-24 | ACHR       | 5.86 | 6.34 | 5.85 | 6.12  | 2910114 | 4                    |
+------------+------------+------+------+------+-------+---------+----------------------+

$ dolt sql -q "SELECT *,LENGTH(o.act_symbol) FROM stocks.ohlcv o WHERE o.date>'2023-11-01' AND o.act_symbol='ACHR' AND LENGTH(o.act_symbol)<5;"
+------------+------------+------+------+------+-------+---------+----------------------+
| date       | act_symbol | open | high | low  | close | volume  | LENGTH(o.act_symbol) |
+------------+------------+------+------+------+-------+---------+----------------------+
| 2023-11-02 | ACHR       | 4.93 | 5.20 | 4.88 | 5.13  | 6424569 | 4                    |
| 2023-11-03 | ACHR       | 5.20 | 5.50 | 5.20 | 5.47  | 4632925 | 4                    |
| 2023-11-06 | ACHR       | 5.52 | 5.61 | 5.23 | 5.35  | 4554518 | 4                    |
| 2023-11-07 | ACHR       | 5.32 | 5.49 | 5.21 | 5.36  | 3422180 | 4                    |
| 2023-11-08 | ACHR       | 5.36 | 5.51 | 5.20 | 5.21  | 4770496 | 4                    |
| 2023-11-09 | ACHR       | 5.27 | 5.40 | 5.12 | 5.26  | 6423177 | 4                    |
| 2023-11-10 | ACHR       | 5.11 | 5.29 | 4.78 | 4.97  | 6779428 | 4                    |
| 2023-11-13 | ACHR       | 4.99 | 5.20 | 4.88 | 5.20  | 6193570 | 4                    |
| 2023-11-14 | ACHR       | 5.48 | 5.99 | 5.48 | 5.98  | 9265805 | 4                    |
| 2023-11-15 | ACHR       | 6.10 | 6.58 | 6.03 | 6.45  | 9699982 | 4                    |
| 2023-11-16 | ACHR       | 6.39 | 6.41 | 6.10 | 6.26  | 7903983 | 4                    |
| 2023-11-17 | ACHR       | 6.37 | 6.44 | 6.17 | 6.32  | 5141332 | 4                    |
| 2023-11-20 | ACHR       | 6.44 | 6.65 | 6.13 | 6.24  | 6745410 | 4                    |
| 2023-11-21 | ACHR       | 6.15 | 6.22 | 5.83 | 5.90  | 6232083 | 4                    |
| 2023-11-22 | ACHR       | 5.96 | 6.09 | 5.86 | 6.02  | 3017880 | 4                    |
| 2023-11-24 | ACHR       | 5.86 | 6.34 | 5.85 | 6.12  | 2910114 | 4                    |
+------------+------------+------+------+------+-------+---------+----------------------+

$ dolt sql -q "SELECT *,LENGTH(act_symbol) FROM stocks.ohlcv WHERE LENGTH(act_symbol)<5 AND date>'2023-11-01' AND act_symbol='ACHR'"         
+------------+------------+------+------+------+-------+---------+--------------------+
| date       | act_symbol | open | high | low  | close | volume  | LENGTH(act_symbol) |
+------------+------------+------+------+------+-------+---------+--------------------+
| 2023-11-02 | ACHR       | 4.93 | 5.20 | 4.88 | 5.13  | 6424569 | 4                  |
| 2023-11-03 | ACHR       | 5.20 | 5.50 | 5.20 | 5.47  | 4632925 | 4                  |
| 2023-11-06 | ACHR       | 5.52 | 5.61 | 5.23 | 5.35  | 4554518 | 4                  |
| 2023-11-07 | ACHR       | 5.32 | 5.49 | 5.21 | 5.36  | 3422180 | 4                  |
| 2023-11-08 | ACHR       | 5.36 | 5.51 | 5.20 | 5.21  | 4770496 | 4                  |
| 2023-11-09 | ACHR       | 5.27 | 5.40 | 5.12 | 5.26  | 6423177 | 4                  |
| 2023-11-10 | ACHR       | 5.11 | 5.29 | 4.78 | 4.97  | 6779428 | 4                  |
| 2023-11-13 | ACHR       | 4.99 | 5.20 | 4.88 | 5.20  | 6193570 | 4                  |
| 2023-11-14 | ACHR       | 5.48 | 5.99 | 5.48 | 5.98  | 9265805 | 4                  |
| 2023-11-15 | ACHR       | 6.10 | 6.58 | 6.03 | 6.45  | 9699982 | 4                  |
| 2023-11-16 | ACHR       | 6.39 | 6.41 | 6.10 | 6.26  | 7903983 | 4                  |
| 2023-11-17 | ACHR       | 6.37 | 6.44 | 6.17 | 6.32  | 5141332 | 4                  |
| 2023-11-20 | ACHR       | 6.44 | 6.65 | 6.13 | 6.24  | 6745410 | 4                  |
| 2023-11-21 | ACHR       | 6.15 | 6.22 | 5.83 | 5.90  | 6232083 | 4                  |
| 2023-11-22 | ACHR       | 5.96 | 6.09 | 5.86 | 6.02  | 3017880 | 4                  |
| 2023-11-24 | ACHR       | 5.86 | 6.34 | 5.85 | 6.12  | 2910114 | 4                  |
+------------+------------+------+------+------+-------+---------+--------------------+

$ dolt sql -q "SELECT *,LENGTH(act_symbol) FROM stocks.ohlcv WHERE date>'2023-11-01' AND act_symbol='ACHR' AND LENGTH(act_symbol)<5;"
+------------+------------+------+------+------+-------+---------+--------------------+
| date       | act_symbol | open | high | low  | close | volume  | LENGTH(act_symbol) |
+------------+------------+------+------+------+-------+---------+--------------------+
| 2023-11-02 | ACHR       | 4.93 | 5.20 | 4.88 | 5.13  | 6424569 | 4                  |
| 2023-11-03 | ACHR       | 5.20 | 5.50 | 5.20 | 5.47  | 4632925 | 4                  |
| 2023-11-06 | ACHR       | 5.52 | 5.61 | 5.23 | 5.35  | 4554518 | 4                  |
| 2023-11-07 | ACHR       | 5.32 | 5.49 | 5.21 | 5.36  | 3422180 | 4                  |
| 2023-11-08 | ACHR       | 5.36 | 5.51 | 5.20 | 5.21  | 4770496 | 4                  |
| 2023-11-09 | ACHR       | 5.27 | 5.40 | 5.12 | 5.26  | 6423177 | 4                  |
| 2023-11-10 | ACHR       | 5.11 | 5.29 | 4.78 | 4.97  | 6779428 | 4                  |
| 2023-11-13 | ACHR       | 4.99 | 5.20 | 4.88 | 5.20  | 6193570 | 4                  |
| 2023-11-14 | ACHR       | 5.48 | 5.99 | 5.48 | 5.98  | 9265805 | 4                  |
| 2023-11-15 | ACHR       | 6.10 | 6.58 | 6.03 | 6.45  | 9699982 | 4                  |
| 2023-11-16 | ACHR       | 6.39 | 6.41 | 6.10 | 6.26  | 7903983 | 4                  |
| 2023-11-17 | ACHR       | 6.37 | 6.44 | 6.17 | 6.32  | 5141332 | 4                  |
| 2023-11-20 | ACHR       | 6.44 | 6.65 | 6.13 | 6.24  | 6745410 | 4                  |
| 2023-11-21 | ACHR       | 6.15 | 6.22 | 5.83 | 5.90  | 6232083 | 4                  |
| 2023-11-22 | ACHR       | 5.96 | 6.09 | 5.86 | 6.02  | 3017880 | 4                  |
| 2023-11-24 | ACHR       | 5.86 | 6.34 | 5.85 | 6.12  | 2910114 | 4                  |
+------------+------------+------+------+------+-------+---------+--------------------+

$

Potentially this is a hell issue with the query terminating at the first double quote? Notice how I had to ctrl-c out of the query with the next double quotes...

[max-hoffman]

Hi @algotr34. Using dolt v1.28.1, all of these have the same execution plan and result set:

stocks> Explain SELECT *,LENGTH(o.act_symbol) FROM ohlcv o WHERE LENGTH(o.act_symbol)<5 AND o.date>"2023-11-01" AND o.act_symbol='ACHR';
+-----------------------------------------------------------------------------------------------------+
| plan                                                                                                |
+-----------------------------------------------------------------------------------------------------+
| Project                                                                                             |
|  ├─ columns: [o.date, o.act_symbol, o.open, o.high, o.low, o.close, o.volume, length(o.act_symbol)] |
|  └─ Filter                                                                                          |
|      ├─ (((length(o.act_symbol) < 5) AND (o.date > '2023-11-01')) AND (o.act_symbol = 'ACHR'))      |
|      └─ TableAlias(o)                                                                               |
|          └─ IndexedTableAccess(ohlcv)                                                               |
|              ├─ index: [ohlcv.date,ohlcv.act_symbol]                                                |
|              ├─ filters: [{(2023-11-01, ∞), [ACHR, ACHR]}]                                          |
|              └─ columns: [date act_symbol open high low close volume]                               |
+-----------------------------------------------------------------------------------------------------+
9 rows in set (0.00 sec)

stocks> SELECT *,LENGTH(act_symbol) FROM ohlcv WHERE date>"2023-11-01" AND act_symbol='ACHR' AND LENGTH(act_symbol)<5;
+------------+------------+------+------+------+-------+---------+--------------------+
| date       | act_symbol | open | high | low  | close | volume  | LENGTH(act_symbol) |
+------------+------------+------+------+------+-------+---------+--------------------+
| 2023-11-02 | ACHR       | 4.93 | 5.20 | 4.88 | 5.13  | 6424569 | 4                  |
| 2023-11-03 | ACHR       | 5.20 | 5.50 | 5.20 | 5.47  | 4632925 | 4                  |
| 2023-11-06 | ACHR       | 5.52 | 5.61 | 5.23 | 5.35  | 4554518 | 4                  |
| 2023-11-07 | ACHR       | 5.32 | 5.49 | 5.21 | 5.36  | 3422180 | 4                  |
| 2023-11-08 | ACHR       | 5.36 | 5.51 | 5.20 | 5.21  | 4770496 | 4                  |
| 2023-11-09 | ACHR       | 5.27 | 5.40 | 5.12 | 5.26  | 6423177 | 4                  |
| 2023-11-10 | ACHR       | 5.11 | 5.29 | 4.78 | 4.97  | 6779428 | 4                  |
| 2023-11-13 | ACHR       | 4.99 | 5.20 | 4.88 | 5.20  | 6193570 | 4                  |
| 2023-11-14 | ACHR       | 5.48 | 5.99 | 5.48 | 5.98  | 9265805 | 4                  |
| 2023-11-15 | ACHR       | 6.10 | 6.58 | 6.03 | 6.45  | 9699982 | 4                  |
| 2023-11-16 | ACHR       | 6.39 | 6.41 | 6.10 | 6.26  | 7903983 | 4                  |
| 2023-11-17 | ACHR       | 6.37 | 6.44 | 6.17 | 6.32  | 5141332 | 4                  |
| 2023-11-20 | ACHR       | 6.44 | 6.65 | 6.13 | 6.24  | 6745410 | 4                  |
| 2023-11-21 | ACHR       | 6.15 | 6.22 | 5.83 | 5.90  | 6232083 | 4                  |
| 2023-11-22 | ACHR       | 5.96 | 6.09 | 5.86 | 6.02  | 3017880 | 4                  |
| 2023-11-24 | ACHR       | 5.86 | 6.34 | 5.85 | 6.12  | 2910114 | 4                  |
+------------+------------+------+------+------+-------+---------+--------------------+
16 rows in set (0.18 sec)

I do see timeouts in the DoltHub UI, but all of the plans are the same and LIMIT 1 returns results for all. I am asking the DoltHub team to gather more info about this. Is there a specific version of Dolt/specific database commit that you see correctness issues? Or are the DoltHub timeouts the issue?

[algotr34]

I still get a different behavior between the queries.
I think one small issue with dolt sql command was due to " escaping (i think), but anyway the main issue still remains, and there are differences between the queries. only the first query works.

INFO

Dolt version 1.24.2
The database is updated until 2023-11-10

QUERIES

stocks>dolt sql -q "SELECT *,LENGTH(o.act_symbol) FROM stocks.ohlcv o WHERE LENGTH(o.act_symbol)<5 AND o.date>'2023-11-01' AND o.act_symbol='ACHR';"
+------------+------------+------+------+------+-------+---------+----------------------+
| date       | act_symbol | open | high | low  | close | volume  | LENGTH(o.act_symbol) |
+------------+------------+------+------+------+-------+---------+----------------------+
| 2023-11-02 | ACHR       | 4.93 | 5.20 | 4.88 | 5.13  | 6424569 | 4                    |
| 2023-11-03 | ACHR       | 5.20 | 5.50 | 5.20 | 5.47  | 4632925 | 4                    |
| 2023-11-06 | ACHR       | 5.52 | 5.61 | 5.23 | 5.35  | 4554518 | 4                    |
| 2023-11-07 | ACHR       | 5.32 | 5.49 | 5.21 | 5.36  | 3422180 | 4                    |
| 2023-11-08 | ACHR       | 5.36 | 5.51 | 5.20 | 5.21  | 4770496 | 4                    |
| 2023-11-09 | ACHR       | 5.27 | 5.40 | 5.12 | 5.26  | 6423177 | 4                    |
| 2023-11-10 | ACHR       | 5.11 | 5.29 | 4.78 | 4.97  | 6779428 | 4                    |
+------------+------------+------+------+------+-------+---------+----------------------+


stocks>dolt sql -q "SELECT *,LENGTH(o.act_symbol) FROM stocks.ohlcv o WHERE o.date>'2023-11-01' AND o.act_symbol='ACHR' AND LENGTH(o.act_symbol)<5;"
(Nothing. No results.)

stocks>dolt sql -q "SELECT *,LENGTH(act_symbol) FROM stocks.ohlcv WHERE LENGTH(act_symbol)<5 AND date>'2023-11-01' AND act_symbol='ACHR';"
(Nothing. No results.)

stocks>dolt sql -q "SELECT *,LENGTH(act_symbol) FROM stocks.ohlcv WHERE date>'2023-11-01' AND act_symbol='ACHR' AND LENGTH(act_symbol)<5;"
(Nothing. No results.)

EXPLAIN QUERIES

stocks>dolt sql -q "EXPLAIN SELECT *,LENGTH(o.act_symbol) FROM stocks.ohlcv o WHERE LENGTH(o.act_symbol)<5 AND o.date>'2023-11-01' AND o.act_symbol='ACHR';"
+-----------------------------------------------------------------------------------------------------+
| plan                                                                                                |
+-----------------------------------------------------------------------------------------------------+
| Project                                                                                             |
|  ├─ columns: [o.date, o.act_symbol, o.open, o.high, o.low, o.close, o.volume, length(o.act_symbol)] |
|  └─ Filter                                                                                          |
|      ├─ (((length(o.act_symbol) < 5) AND (o.date > '2023-11-01')) AND (o.act_symbol = 'ACHR'))      |
|      └─ TableAlias(o)                                                                               |
|          └─ IndexedTableAccess(ohlcv)                                                               |
|              ├─ index: [ohlcv.date,ohlcv.act_symbol]                                                |
|              ├─ filters: [{(2023-11-01, ∞), [NULL, ∞)}]                                             |
|              └─ columns: [date act_symbol open high low close volume]                               |
+-----------------------------------------------------------------------------------------------------+


stocks>dolt sql -q "EXPLAIN SELECT *,LENGTH(o.act_symbol) FROM stocks.ohlcv o WHERE o.date>'2023-11-01' AND o.act_symbol='ACHR' AND LENGTH(o.act_symbol)<5;"
+-----------------------------------------------------------------------------------------------------+
| plan                                                                                                |
+-----------------------------------------------------------------------------------------------------+
| Project                                                                                             |
|  ├─ columns: [o.date, o.act_symbol, o.open, o.high, o.low, o.close, o.volume, length(o.act_symbol)] |
|  └─ Filter                                                                                          |
|      ├─ (((o.date > '2023-11-01') AND (o.act_symbol = 'ACHR')) AND (length(o.act_symbol) < 5))      |
|      └─ TableAlias(o)                                                                               |
|          └─ IndexedTableAccess(ohlcv)                                                               |
|              ├─ index: [ohlcv.date,ohlcv.act_symbol]                                                |
|              ├─ filters: [{(∞, ∞), (∞, ∞)}]                                                         |
|              └─ columns: [date act_symbol open high low close volume]                               |
+-----------------------------------------------------------------------------------------------------+


stocks>dolt sql -q "EXPLAIN SELECT *,LENGTH(act_symbol) FROM stocks.ohlcv WHERE LENGTH(act_symbol)<5 AND date>'2023-11-01' AND act_symbol='ACHR';"
+-------------------------------------------------------------------------------------------------------------------------------------+
| plan                                                                                                                                |
+-------------------------------------------------------------------------------------------------------------------------------------+
| Project                                                                                                                             |
|  ├─ columns: [ohlcv.date, ohlcv.act_symbol, ohlcv.open, ohlcv.high, ohlcv.low, ohlcv.close, ohlcv.volume, length(ohlcv.act_symbol)] |
|  └─ Filter                                                                                                                          |
|      ├─ ((ohlcv.date > '2023-11-01') AND (ohlcv.act_symbol = 'ACHR'))                                                               |
|      └─ IndexedTableAccess(ohlcv)                                                                                                   |
|          ├─ index: [ohlcv.date,ohlcv.act_symbol]                                                                                    |
|          ├─ filters: [{(∞, ∞), (∞, ∞)}]                                                                                             |
|          └─ columns: [date act_symbol open high low close volume]                                                                   |
+-------------------------------------------------------------------------------------------------------------------------------------+


stocks>dolt sql -q "EXPLAIN SELECT *,LENGTH(act_symbol) FROM stocks.ohlcv WHERE date>'2023-11-01' AND act_symbol='ACHR' AND LENGTH(act_symbol)<5;"
+-------------------------------------------------------------------------------------------------------------------------------------+
| plan                                                                                                                                |
+-------------------------------------------------------------------------------------------------------------------------------------+
| Project                                                                                                                             |
|  ├─ columns: [ohlcv.date, ohlcv.act_symbol, ohlcv.open, ohlcv.high, ohlcv.low, ohlcv.close, ohlcv.volume, length(ohlcv.act_symbol)] |
|  └─ Filter                                                                                                                          |
|      ├─ ((ohlcv.date > '2023-11-01') AND (ohlcv.act_symbol = 'ACHR'))                                                               |
|      └─ IndexedTableAccess(ohlcv)                                                                                                   |
|          ├─ index: [ohlcv.date,ohlcv.act_symbol]                                                                                    |
|          ├─ filters: [{(∞, ∞), (∞, ∞)}]                                                                                             |
|          └─ columns: [date act_symbol open high low close volume]                                                                   |
+-------------------------------------------------------------------------------------------------------------------------------------+

[max-hoffman]

@algotr34 I can repro your observations with v1.24.2. I think the related fixes are these PRs: dolthub/go-mysql-server#2134, dolthub/go-mysql-server#2146, dolthub/go-mysql-server#2152, dolthub/go-mysql-server#2149. Index scan ranges should be greedy, order invariant, and logically equivalent to the filters they replace. The newer versions of Dolt have made progress here, if you find issues in main we are happy to follow up with more fixes and better testing.

[algotr34]

I checked again with dolt version 1.28.2 and it seems to be fixed.
amazing progress for just 3 weeks. nice.

[timsehn]

Great. Keep any bugs you find coming.