[MDEV-32843] main.subselect, main.subselect_no_exists_to_in often fails on CI with ER_SUBQUERY_NO_1_ROW Created: 2023-11-20  Updated: 2024-01-06  Resolved: 2023-11-30

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Subquery, Tests
Affects Version/s: 10.6, 10.10, 10.11
Fix Version/s: 10.4.33, 10.5.24, 10.6.17, 10.11.7, 11.0.5, 11.1.4, 11.2.3

Type: Bug Priority: Major
Reporter: Vladislav Vaintroub Assignee: Daniel Black
Resolution: Fixed Votes: 0
Labels: CI

Issue Links:
Relates
relates to MDEV-7565 Server crash with Signal 6 (part 2) Closed

 Description   

Often failed on kvm-fulltest2 (e.g https://buildbot.mariadb.net/buildbot/builders/kvm-fulltest2/builds/43965/steps/mtr_opttrace/logs/stdio)
on amd64-ubuntu-2004-fulltest, in appveyor build)

Cross-reference link lists 100 failures within 3 weeks. https://buildbot.mariadb.net/ci/reports/cross_reference#branch=&revision=&platform=&fail_name=main.subselect&fail_variant=&fail_info_full=ER_SUBQUERY_NO_1_ROW&typ=&info=&dt=&limit=100&fail_info_short=

The error is new, first recent occurrence, according to cross-reference was on Oct 11 2023 on kvm-fulltest2 here

main.subselect_no_exists_to_in started to fail earler , first recent occurence 2023-09-22

main.subselect                           w1 [ fail ]
 
        Test ended at 2023-11-20 06:30:11
 
 
 
CURRENT_TEST: main.subselect
 
/mnt/buildbot/build/mariadb-10.11.7/client//mariadb-test: Error on delete of '/mnt/buildbot/build/mariadb-10.11.7/mysql-test/var/1/tmp/subselect.out.file.1' (Errcode: 2 "No such file or directory")
 
mysqltest: At line 6003: query 'Select 
(Select Sum(`TestCase`.Revenue) From mysql.slow_log E           
Where TestCase.TemplateID not in (Select 1 from mysql.slow_log where 2=2)
 
) As `ControlRev`
 
From 
(Select  3 as Revenue, 4 as TemplateID) As `TestCase` 
Group By  TestCase.Revenue, TestCase.TemplateID' failed: ER_SUBQUERY_NO_1_ROW (1242): Subquery returns more than 1 row
 
::::



 Comments   
Comment by Daniel Black [ 2023-11-30 ]

So the reason for the error is more than 2 rows are in the slow_log table.

Is this too simplistic?

diff --git a/mysql-test/main/subselect.result b/mysql-test/main/subselect.result
 
index 32652244353..f122719bca8 100644
 
--- a/mysql-test/main/subselect.result
 
+++ b/mysql-test/main/subselect.result
 
@@ -7163,7 +7163,7 @@ drop table t1;
 
 #
 
 Select 
 (Select Sum(`TestCase`.Revenue) From mysql.slow_log E           
-Where TestCase.TemplateID not in (Select 1 from mysql.slow_log where 2=2)
 
+Where TestCase.TemplateID not in (Select max(1) from mysql.slow_log where 2=2)
 
 ) As `ControlRev`
 
 From 
 (Select  3 as Revenue, 4 as TemplateID) As `TestCase`

Comment by Daniel Black [ 2023-11-30 ]

Rex, can I get a review on https://github.com/MariaDB/server/pull/2883 please.

Comment by Rex Johnston [ 2023-11-30 ]

https://github.com/MariaDB/server/pull/2883 Looks good to me!

Comment by Otto Kekäläinen [ 2024-01-06 ]

Confirmation: This https://github.com/MariaDB/server/pull/2883 was applied in Debian in https://salsa.debian.org/mariadb-team/mariadb-server/-/commit/f7554dac59e002768847e686d58b3bad8efd7a9c and uploaded with MariaDB 1:10.11.6-2. Based on autopkgtest results at https://ci.debian.net/packages/m/mariadb/ all the new 1:10.11.6-2 CI runs are passing.

Generated at Thu Feb 08 10:34:27 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.