windtalker
commented
1 month ago simplest reproduce sql
SELECT
subq_0.c3 as c1
FROM
(
select
ref_0.c_a90ol as c3,
ref_0.c_a90ol as c4,
var_pop(cast(ref_0.c__qy as double)) over (partition by ref_0.c_a90ol, ref_0.c_s order by ref_0.c_z) as c5
from
t_jg8o as ref_0
limit 65
) as subq_0
LIMIT 37The root cause of this issue is
- the sql will generate a plan that contains projection the same column multiple times
mysql> source /tmp/test.sql +----------------------------------------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +----------------------------------------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Projection_14 | 37.00 | root | | issuedb.t_jg8o.c_a90ol | | └─Limit_17 | 37.00 | root | | offset:0, count:37 | | └─Limit_20 | 40.00 | root | | offset:0, count:65 | | └─Shuffle_27 | 40.00 | root | | execution info: concurrency:5, data sources:[Projection_23] | | └─Window_21 | 40.00 | root | | var_pop(Column#15)->Column#16 over(partition by issuedb.t_jg8o.c_a90ol, issuedb.t_jg8o.c_s order by issuedb.t_jg8o.c_z range between unbounded preceding and current row) | | └─Sort_26 | 40.00 | root | | issuedb.t_jg8o.c_a90ol, issuedb.t_jg8o.c_s, issuedb.t_jg8o.c_z | | └─ShuffleReceiver_28 | 40.00 | root | | | | └─Projection_23 | 40.00 | root | | issuedb.t_jg8o.c_a90ol, issuedb.t_jg8o.c_a90ol, issuedb.t_jg8o.c_s, issuedb.t_jg8o.c_z, cast(issuedb.t_jg8o.c__qy, double BINARY)->Column#15 | | └─TableReader_25 | 40.00 | root | | data:TableFullScan_24 | | └─TableFullScan_24 | 40.00 | cop[tikv] | table:ref_0 | keep order:false, stats:pseudo | +----------------------------------------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+Note that
Projection_23project columnissuedb.t_jg8o.c_a90oltwice - In projection exec, if projection project the same column multiple times, in the result chunk, the column will be reused
https://github.com/pingcap/tidb/blob/1dc385103dccf67f40318eb6063bcea38c04d926/pkg/expression/evaluator.go#L26-L50
So the chunk output by
Projection_23will be something likecol1, col1, col3, col4, ... - In
LimitExec, it may contain inlined projection by setting the output column incolumnIdxsUsedByChildhttps://github.com/pingcap/tidb/blob/1dc385103dccf67f40318eb6063bcea38c04d926/pkg/executor/executor.go#L1363-L1364 - In
LimitExec.Next, it useSwapColumnto do the inline projection, which will got wrong result if the inline projection index actually pointing to the same column in chunk https://github.com/pingcap/tidb/blob/1dc385103dccf67f40318eb6063bcea38c04d926/pkg/executor/executor.go#L1426-L1430 For example, ifcolumnIdxsUsedByChildis [0, 1], and the input chunk iscol1, col1, col3, col4, ...After swap column 0, the result chunk iscol1, niland the input chunk isnil, nil, col3, col4, ...After swap column 1, the result chunk iscol1, niland the input chunk isnil, nil, col3, col4, ...So the final result chunk iscol1, nil, and the expected result chunk iscol1, col1
Bug Report
Please answer these questions before submitting your issue. Thanks!
1. Minimal reproduce step (Required)
SQL to init database
```sql create table t_jg8o ( c_otj13 int unique , c_foveoe text , c_jbb text , c_s int not null unique , c_ovz0 double , c_m0qqv_cl4x text , c_cz text not null , c__qy double , c_z int not null , c_a90ol text not null , primary key(c_z) CLUSTERED) pre_split_regions=2; alter table t_jg8o add column c_tazb9 int; create table t__9r63 ( c_g7eofzlxn int , c_r58lkh double , c_x2erxo10w int , c_wsr tinyint , c_hd2v4v0 double , c_tb3u text , c_onfeptr2q tinyint unique , primary key(c_g7eofzlxn, c_x2erxo10w) CLUSTERED) pre_split_regions=7; alter table t__9r63 add column c_o8tsf double; alter table t_jg8o add column c_mgjb text; insert into t__9r63 (c_g7eofzlxn, c_r58lkh, c_x2erxo10w, c_wsr, c_hd2v4v0, c_tb3u, c_onfeptr2q, c_o8tsf) values (1275540443, 9223372036854775806.5, -1407328577, (NOT NOT(cast((-945767894 <= -8247562720541611887) as unsigned))), 35.15, 'l7ngfavtf2', (NOT NOT(cast((cast(cast(null as signed) as signed) > cast(-1581230237 as signed)) as unsigned))), 22.55), (0, 6.4, -23, (-4 is not NULL), -256.4, 'nrqgupym', (NOT NOT(cast((cast(null as char) < cast(null as char)) as unsigned))), 49.2), (-2005512947, 14.81, 1215057043, ((NOT NOT(cast((cast(-4255 as signed) <=> cast(-9223372036854775807.2 as double)) as unsigned)))) or ((1928377790 is NULL)), 93.95, 'ycra', (NOT NOT(cast((-4902061839617880638 < cast(null as signed)) as unsigned))), 53.76), (-865548105, 27.38, -932309610, 1=1, 65.29, cast(null as char), (NOT NOT(cast((cast(cast(null as char) as char) != cast(cast(null as char) as char)) as unsigned))), 2.71); insert into t_jg8o (c_otj13, c_foveoe, c_jbb, c_s, c_ovz0, c_m0qqv_cl4x, c_cz, c__qy, c_z, c_a90ol, c_tazb9, c_mgjb) values (-1125135259, cast(null as char), 'yv0lmxlfk', -975033779, 37.68, 'bza6', 'cfvnwio', 85.65, -355481284, 'gnip', 1483367478, 'qb60'), (1880243065, cast(null as char), 'i2qqh305', -2018599732, 32767.3, 'oro', 'it5a4_xpb', 85.86, 1617093413, 'm', -322122295, 'sfvnji04t3'), (-4, 'yuv', 'aqx', -960107027, -2147483646.5, 'o8fnlpv', 'm2pb', 4.6, -2042358076, 'y1q', -2127191042, 'p'), (-1974234281, 'lsw', 'mkdf7_y', -3, 31.13, 'gk45', 'i830ous8', 38.1, -1528586343, 'ex_2', cast(cast(null as signed) as signed), 'm7z'); insert into t_jg8o (c_otj13, c_foveoe, c_jbb, c_s, c_ovz0, c_m0qqv_cl4x, c_cz, c__qy, c_z, c_a90ol, c_tazb9, c_mgjb) values (-277118542, 'k', 'ghm1uf2f2', 69386953, cast(null as double), 'u9n4y2', 'qt', 32768.0, -62220810, 'tfkxjj5c', -627116719, 'dmgjz'), (1565474881, 'min0', 'ml_ba1n', 587181689, 33.58, 'jz9rotuhlh', 'uo8lf', -9223372036854775806.3, -1666156943, 'queemvgj', 1778222362, 'fg3c8gyszj'), (-589629632, 'pxcx', 'kkscy', -218561796, 71.80, 'chknth', 'nl2', 85.2, -670390288, 'nf990nol', -1306461501, 'f'), (127, 'g5of_oy', 'uoxa', 858419954, 7.39, 'q', 'z0paksyd_8', 2147483646.0, -1649362344, 'won_9', -1363570695, 'mf196l7n'); insert into t_jg8o (c_otj13, c_foveoe, c_jbb, c_s, c_ovz0, c_m0qqv_cl4x, c_cz, c__qy, c_z, c_a90ol, c_tazb9, c_mgjb) values (1241131098, 'ohukstr8z6', 'o8lc2gnw', -1120115215, 25.45, 'i5', 'jl6gpk', 22.100, 1509989939, 'w', cast(cast(null as signed) as signed), 'u5cmakw59'), (32767, 'cluk1', 'vwf0c', -1388119356, 43.38, 'd', 'k2k', 94.32, -1694148464, 'gu4i4knyhm', 2133825849, 'tk'), (-1649650590, 'srv', 'sw', -1016230734, cast(null as double), 'epf0', 'zgntl', -4294967295.8, 1430313391, 's', cast(null as signed), 'c63s91'), (625818595, 'b598_', 'k09g474', -1861825796, -128.8, 'fma', 'vpwh7bhpeg', 36.52, -1457928755, 'j', -1081926301, 'fi'); insert into t_jg8o (c_otj13, c_foveoe, c_jbb, c_s, c_ovz0, c_m0qqv_cl4x, c_cz, c__qy, c_z, c_a90ol, c_tazb9, c_mgjb) values (cast(null as signed), 'q4z87vft', 'y_1oiurq', 1963621165, 32769.7, 'sx17rkl7ru', 't45zvd_', 88.87, 18928603, 'gxbsloff', -2034290070, 'xb'), (-972441022, 'oe', cast(null as char), 1492879828, 39.59, 'r', 'hyj_2', cast(null as double), 759883041, 'zwue', 1697880045, 'e'), (cast(cast(null as signed) as signed), 'l4_3', 'i4h7dg', -1607192175, cast(null as double), 'z8voqwznkh', 'l_mk', 12.36, 1669523024, 'qt5zch71a', 1249617332, 'i1'), (-846888054, 'm1jbwdl', 'rrrum4j6', 1534068569, 27.67, 'il', 'j_hlg0_3rp', 46.79, -392085130, 'bc', 16, 'w3lw6'); insert into t_jg8o (c_otj13, c_foveoe, c_jbb, c_s, c_ovz0, c_m0qqv_cl4x, c_cz, c__qy, c_z, c_a90ol, c_tazb9, c_mgjb) values (cast(cast(null as signed) as signed), cast(null as char), 'g', 155707446, 256.3, 'mx1qj0', 'j3', 9223372036854775809.4, 1727199557, 'qyghenu9t6', 744205327, 'z1_che8qi'), (39, 'fnm2krnb', 'wxqm_zq3a', -1524976778, 31.59, 'gdv27x', 'n69m7fymt1', 75.99, 335492222, 'sdgde0z', cast(cast(null as signed) as signed), 'ykr5erlg5'), (1355656665, '__eyi', 'mfr5', 175403335, 87.17, 'jj', 'oz', cast(null as double), -69711503, 'ja', 14424161, cast(null as char)), (218845689, 'u2u8egda6', 'o8', -272715456, -9223372036854775807.1, 'szwj7begac', 'm', 48.62, 753928713, 'ur', cast(cast(null as signed) as signed), 'o_hzkvf2h'); insert into t_jg8o (c_otj13, c_foveoe, c_jbb, c_s, c_ovz0, c_m0qqv_cl4x, c_cz, c__qy, c_z, c_a90ol, c_tazb9, c_mgjb) values (325010661, 'gv15a', cast(null as char), -2035825967, 9223372036854775807.3, 'bd', 'c3kvy', 257.3, -1598426762, 'lmqmn', 814499588, 'gwqmly7q'), (-233415102, 'hs2xue1', 'efbj7o4p', -1178957955, 58.9, 'y04g', 'qi68fwpdwo', 2147483648.100000, 1432554380, 'dqpb210', 2, 'a'), (-2, 'brrhd', 's6mk', -2056628646, cast(null as double), 'gi', '_v7eaafvx', 254.5, -1476177588, 'k41ajpt7x', -599714096, 'paeld1s_gk'), (cast(cast(null as signed) as signed), 'o23', 'bqdf52', -914210874, 1.75, 'd041c76', 'd6', 126.7, -421919910, 'x57ud7oy1', -537365618, 'srrn'); insert into t_jg8o (c_otj13, c_foveoe, c_jbb, c_s, c_ovz0, c_m0qqv_cl4x, c_cz, c__qy, c_z, c_a90ol, c_tazb9, c_mgjb) values (145245111, 'v66uhowf9g', 'ai4', -88586773, 43.77, 'byh', 'hazwego', 1.2, 1568247510, 'drmxi8', -1707580186, 'te3'), (100663045, 'blfgrhpmo', 'pswozf1q2', -834563269, 86.9, 'gpwkf932fe', 'pnomakvyp', -4294967296.7, 1163133933, 'wp', -964848632, 'lfrx2gtt'), (1498701970, cast(null as char), 'i89en7s8z', -84490060, 74.53, 'bx', 'xs6344kmb0', 54.13, -630289437, '_3_twecg5h', -2054873600, 'eu62_'), (-522428484, 'euwmtnx1f', 'a_', 267700893, 63.46, 'n2s', 'mx8wzrf3z', 54.75, 370343042, 'n72', -1749867774, 'zmz8efxh'); insert into t_jg8o (c_otj13, c_foveoe, c_jbb, c_s, c_ovz0, c_m0qqv_cl4x, c_cz, c__qy, c_z, c_a90ol, c_tazb9, c_mgjb) values (-905893719, 'm7d8j', 'ml', 552106333, 15.76, 'a', 'pyq1x9ums', 32766.2, 2365745, 's7tt', 38, 'bat'), (1231160405, 'h_pkv', cast(null as char), 643440707, cast(null as double), 'mxk3co7', 'v60l0it', 65536.8, -850412592, 'wmluxa9a', -3, cast(null as char)), (1986094882, 'tn2lly', 't2wczw', 1709853766, 256.6, 'nowm1x', 's07dvcbf', -4294967296.5, -21041749, 'obqj0uu5v', 1779379786, 'pgi'), (-1821883740, 'j37djo2lth', 'fv', -7, 9223372036854775807.7, 'cde8k', 'jylbt', 80.88, 528792379, 'n5qr9m26i', 434269354, 'l'); insert into t_jg8o (c_otj13, c_foveoe, c_jbb, c_s, c_ovz0, c_m0qqv_cl4x, c_cz, c__qy, c_z, c_a90ol, c_tazb9, c_mgjb) values (-961030073, cast(null as char), 'hzacfknf', -456431629, 95.4, 'j', 'l', 28.43, 1958788149, 'b', 30, 'svw3'), (1650387673, 'rw8fqp6v', 'pvrb_y8', -28841240, 16.59, 'jmp5', 'f7jcx7', 11.86, -1089765168, 'pqg', 1336711866, 'i'), (593387683, 'rtavaa', 'hu95fi27', -807839288, 15.55, 'o', 'dniac62ej', 25.89, 504535500, 'cs3tkhs', -355956957, 'e2u_e'), (1925910886, 'yc6f2b8sx', cast(null as char), -52910064, -18446744073709551616.6, 'p55j0qfh', 'k7p587ii', 85.16, 354032882, '_ffjo67yxe', cast(null as signed), cast(null as char)); insert into t_jg8o (c_otj13, c_foveoe, c_jbb, c_s, c_ovz0, c_m0qqv_cl4x, c_cz, c__qy, c_z, c_a90ol, c_tazb9, c_mgjb) values (-324790424, 'l7tkij97', 'jpsz1afty', 1919869830, 47.51, 'cjep', 'ysinvie', 81.81, -272247558, 'aj', -1364386190, 'b9pv_pswu'), (-1176143795, 'mhx', cast(null as char), 165434725, 22.67, 'y', 'wg_ccu', -2147483648.0, 11, 'xxnsf5', -33, 'n9a'), (43882083, cast(null as char), 'sb8q1qk93', 3, 58.12, '_nq5o3o4h', 'p', -2147483648.7, 1616632952, 'g7t8tqyi', 1149176707, cast(null as char)), (683197795, 'xv38gos', 'he', 1851859144, -2147483647.6, 'f', 'sqbww', 70.73, -1105664209, 'qjfhjr', 1736785989, 'm'); ```SQL that causes error
```sql WITH cte_0 AS ( SELECT subq_0.c3 as c1 FROM (select ref_0.c_a90ol as c0, ref_0.c_mgjb as c1, (select c_g7eofzlxn from t__9r63 order by c_g7eofzlxn limit 1 offset 5) as c2, ref_0.c_a90ol as c3, ref_0.c_a90ol as c4, var_pop( cast(ref_0.c__qy as double)) over (partition by ref_0.c_a90ol, ref_0.c_s order by ref_0.c_z) as c5, 1867837693 as c6, -1397297856 as c7 from t_jg8o as ref_0 where (substring( cast(ref_0.c_m0qqv_cl4x as char), cast((select c_wsr from t__9r63 order by c_wsr limit 1 offset 1) as signed), cast(-21766 as signed)) is not NULL) limit 65) as subq_0 LIMIT 37 ) SELECT ref_29.c1 as c96 FROM cte_0 as ref_29 LIMIT 142; ```2. What did you expect to see? (Required)
Expect no crashes
3. What did you see instead (Required)
4. What is your TiDB version? (Required)
We are the BASS team from the School of Cyber Science and Technology at Beihang University. Our main focus is on system software security, operating systems, and program analysis research, as well as the development of automated program testing frameworks for detecting software defects. Using our self-developed database vulnerability testing tool, we have identified the above-mentioned vulnerabilities in TiDB that may lead to database crashes.