mysql 8 窗口函数错误的结果

Question

我在使用 MySQL8 中的窗口函数时遇到问题 - 它们在应用于大型表时会给出不正确的结果(或者:大量的行或大量的列)。

例子:表:来自 https://www.kaggle.com/c/home-credit-default-risk/data 的 bureau.csv(170 万行)

我运行 3 个简单的查询，仅更改要使用的表的行数和要输出的列数。您可以清楚地看到大量行和许多输出列的组合给出了“count(*) over()”(最后一列)的错误结果。

high number of rows, low number of columns - result: OK;

high number of rows, high number of columns - result: INCORRECT;

low number of rows, hight number of columns - result: OK;

Ubuntu 16.04、32GB 内存

非常感谢您的帮助! :)

维托尔德

my.cnf:
[mysqld]

innodb_buffer_pool_size = 26G
default_authentication_plugin=mysql_native_password
thread_cache_size = 50
innodb-flush-method=O_DIRECT
local_infile=ON
innodb_thread_concurrency=2
internal_tmp_mem_storage_engine=MEMORY
join_buffer_size=1G
temptable_max_ram=4G
tmp_table_size=4G
max_heap_table_size=4G
mysqlx_connect_timeout=99999
mysqlx_read_timeout=99999
mysqlx_write_timeout=99999
net_read_timeout=99999
net_write_timeout=99999
regexp_time_limit=99999
mysqlx_port_open_timeout=99999
windowing-use-high-precision=OFF
sort_buffer_size=4G

要运行的代码:

select a.*
from
(
select #b.sk_id_curr,
       b.*,
       count(*) over(partition by b.sk_id_curr) as counter
from (select * from bureau limit 10000000) b
) a
order by a.sk_id_curr desc
limit 100
;

编辑: exlain and indexes picture

还有一件事我注意到:在“INCORRECT”图片中，所有列都不正确(不仅是最后一列)- 与“GOOD”结果的图片进行比较(查看 sk_id_curr)。

根据 Wilson Hauck 的要求:A) 完整(未编辑)my.cnf-ini 的文本结果:B) 显示全局状态； C) 显示全局变量； D) 显示引擎创新状态； SHOW CREATE TABLE局； part1 part2

DB Fiddle 上的 1000 行示例:https://www.db-fiddle.com/f/fzXsN6vFzidhanxeUjWkiB/0

我导入数据到mysql的方式:

首先 我用 python 中的“NULL”替换了 csv 中的“空格”:

import pandas as pd
bureau = pd.read_csv('../input/bureau.csv')
bureau.to_csv('../input/bureau2.csv',index=False,na_rep="NULL",header=True)

其次我使用mysql中的代码:

LOAD DATA LOCAL INFILE '../input/bureau2.csv' INTO TABLE bureau
FIELDS TERMINATED BY ',' ENCLOSED BY '"' 
LINES TERMINATED BY '\n'
IGNORE 1 LINES
;

Answer 1

此版本为我提供了 8.0.11 的预期结果(注意不再有任何文本 Blob )。您还需要禁用 --big_tables 如果您已将其设置为此工作。

CREATE TABLE `bureau` (
 `SK_ID_CURR` int(11) DEFAULT NULL,
 `SK_ID_BUREAU` int(11) DEFAULT NULL,
 `CREDIT_ACTIVE` varchar(20),
 `CREDIT_CURRENCY` varchar(20),
 `DAYS_CREDIT` int(11) DEFAULT NULL,
 `CREDIT_DAY_OVERDUE` int(11) DEFAULT NULL,
 `DAYS_CREDIT_ENDDATE` varchar(20),
 `DAYS_ENDDATE_FACT` varchar(20),
 `AMT_CREDIT_MAX_OVERDUE` varchar(20),
 `CNT_CREDIT_PROLONG` int(11) DEFAULT NULL,
 `AMT_CREDIT_SUM` double DEFAULT NULL,
 `AMT_CREDIT_SUM_DEBT` varchar(20),
 `AMT_CREDIT_SUM_LIMIT` varchar(20),
 `AMT_CREDIT_SUM_OVERDUE` double DEFAULT NULL,
 `CREDIT_TYPE` varchar(20),
 `DAYS_CREDIT_UPDATE` int(11) DEFAULT NULL,
 `AMT_ANNUITY` varchar(20),
 KEY `bureau` (`SK_ID_CURR`,`SK_ID_BUREAU`),
 KEY `bureau_i2` (`SK_ID_BUREAU`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

set session sql_mode='';
load data infile '/export/home/tmp/dag/git/mysql/bureau.csv' into table bureau columns terminated by "," ignore 1 lines;


select a.*
from
(
select #b.sk_id_curr,
       b.*,
       count(*) over(partition by b.sk_id_curr) as counter
from (select * from bureau) b
) a
order by a.sk_id_curr desc
limit 100
;