requested from development and results
select calsettrace(1); select option_id from option_onelot where market_date = '2017-12-01' and option_id = 818449 and active = 1; select calgettrace(0);
select calsettrace(1); select option_id from option_onelot where market_date = '2017-12-01' and option_id = 818449 and active = 1; select calgettrace(0);
----------------
| calsettrace(1) |
----------------
| 0 |
----------------
1 row in set (0.001 sec)
Empty set, 1 warning (0.262 sec)
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| calgettrace(0) |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Desc Mode Table TableOID ReferencedColumns PIO LIO PBE Elapsed Rows
BPS PM option_onelot 5255 (active,market_date,option_id) 0 9128 87048 0.210 0
TNS UM - - - - - - 0.197 0
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.001 sec)
More info from customer:
I have a hunch that it could have something to do with that default value being added for each record when adding that column but not quite everywhere.
It is also interesting to me that those records affected by this weird behavior are the ones that are the OLDEST in the table. We are augmenting the table every day and we started from December 2017. Those records that sit there from beginnning of December up until Dec. 18 2017 are returning these weird results. The newer ones behave nicer - meaning we did not see a behavior like this for those rows. This is what scares me when I am thinking about our production DB as reliable data-store. It might relate to the entire extension (did not actually check if it is full 8 mil. of rows or not but it could be because daily amounts suggest it could fill up 8 mil. rows until Dec. 18.
Maybe there is no significance in that ^^^ paragraph but I thought it is going to be useful to mention.
To add more context, below is output from calgettrace.
Also, just fyi, selecting the option_id col while also filtering on it isn't the actual query we use in application but was what we ended up using for debugging purposes which shows the same issue. Wanted to point that out in case you were wondering why we were doing that in the first place 
MariaDB [dds_main]> select calgettrace(1);
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| TBPS: TupleBPS ses:154 txn:0 ver: SCN: 31230 Txns: st:0 tb/col:5255/5704 alias:option_onelot not del (0x7f045a365e00[19]) nf:1 in: UUID: 2d62019e-f6c0-428b-b728-60bc53158224 Query UUID: fce476c6-863e-4b90-a46b-edcd2fb2e1cc BatchPrimitiveProcessorJL: – scan driven 3 filter steps: ColumnCommandJL: 1 filters colwidth=1 oid=5704 name=active (scan) ColumnCommandJL: 1 filters colwidth=8 oid=5256 name=option_id ColumnCommandJL: 1 filters colwidth=4 oid=5257 name=market_date 1 projection steps: ColumnCommandJL: 0 filters colwidth=8 oid=5256 name=option_id |
ses:154 st: 0 finished at 2019-09-30 15:26:48.269941; PhyI/O-0; CacheI/O-9128; MsgsSent-1024; MsgsRvcd-1024; BlocksTouched-9128; BlockedFifoIn/Out-0/0; output size-0
PartitionBlocksEliminated-87048; MsgBytesIn-110KB; MsgBytesOut-2KB; TotalMsgs-1024
1st read 2019-09-30 15:26:48.055804; EOI 2019-09-30 15:26:48.269913; runtime-0.214109s
UUID 2d62019e-f6c0-428b-b728-60bc53158224
Query UUID fce476c6-863e-4b90-a46b-edcd2fb2e1cc
Job completion status 0
TNS: ses:154 st: 1 finished at Mon Sep 30 15:26:48 2019; total rows returned-0
1st read 2019-09-30 15:26:48.068199; EOI 2019-09-30 15:26:48.270236; runtime-0.202037s;
UUID a8774db1-28a9-4f1d-b1b4-fb3713c2ad82
Job completion status 0
1 row in set (0.001 sec)
I tried to reproduce with list 1 row and couldn't.
select * from option_onelot;
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| option_id | market_date | t_time | side | size | bsize | asize | exch | bexch | aexch | bid | ask | price | spot | spot_chg | open_int | volume | cond | ivol | ivol_chg | theo | delta | vega | theta | shares | nonstd | batch_id |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 818449 | 2017-12-01 | 00:00:00 | 1 | 11 | 12 | 12 | a | B | c | 1.1000 | 2.1000 | 3.1000 | 4.1000 | 5.1000 | 20 | 20 | h | 1.1000000 | 2.1000000 | 3.1000 | 4.1000000000 | 5.1000000 | 6.10000000 | 5 | 1 | 10 |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.151 sec)
MariaDB [DataDock]> select count(1) from option_onelot where option_id = 818449 and market_date = '2017-12-01';
----------
| count(1) |
----------
| 1 |
----------
1 row in set (0.014 sec)
MariaDB [DataDock]> alter table option_onelot add column active tinyint(1) DEFAULT 1;
Query OK, 0 rows affected (0.585 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [DataDock]> select option_id from option_onelot where market_date = '2017-12-01' and option_id = 818449 and active = 1;
-----------
| option_id |
-----------
| 818449 |
-----------
1 row in set (0.062 sec)
MariaDB [DataDock]>
This ticket was opened prior to convergence with the server. It may have been rendered obsolete. If this issue still exists in a modern version, please open a new request.