EXISTS expects a single column in subquery result, should support any number

[itzikiusa]

Hi, is there anyway to support FROM DUAL queries? i need it to use WHERE NOT EXISTS upon insert

currently i'm getting an error that number of columns do not match(expected 1 got 22), though all passes when i use mysql docker image(and when i run the service itself infront of actual DB all works as well)

thanks!

[zachmu]

That should work.

If you provide your schema and query we can debug this further.

[itzikiusa]

@zachmu thanks for the response, sure, attaching the data

query:

INSERT INTO MdlGm_tblPlayers(Login, BrandId, Password, Email, CompletReg, SendMail, createTime, PlayerStatus, TestAccount)
 SELECT ?, '1', ?, ?, 0, 0, now(), 'Not verified', ?
 FROM DUAL WHERE NOT EXISTS 
(SELECT * FROM MdlGm_tblPlayers WHERE BrandId = '1' AND (Login = ? OR Email = ?))


schema:

CREATE TABLE `MdlGm_tblPlayers` (
  `ID` int unsigned NOT NULL AUTO_INCREMENT,
  `Login` varchar(100) DEFAULT NULL,
  `Password` varchar(255) DEFAULT NULL,
  `Email` varchar(100) DEFAULT NULL,
  `Country` varchar(100) DEFAULT NULL,
  `Lang` varchar(4) DEFAULT NULL,
  `UILang` varchar(4) DEFAULT NULL,
  `TimeZone` int DEFAULT NULL,
  `PlayerStatus` varchar(100) DEFAULT NULL,
  `CompletReg` tinyint(1) DEFAULT '0',
  `BrandId` int DEFAULT NULL,
  `GameUserId` bigint DEFAULT NULL,
  `SessionID` varchar(40) DEFAULT NULL,
  `SendMail` tinyint(1) DEFAULT '0',
  `StageNum` int DEFAULT NULL,
  `lastLogin` timestamp NULL DEFAULT NULL,
  `createTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `vipLevel` int unsigned NOT NULL DEFAULT '1' COMMENT 'vip tier of player',
  `vipLevelUpUsed` tinyint unsigned DEFAULT '1' COMMENT 'flag - if last tier up deposit bonus was used yet',
  `TestAccount` tinyint(1) DEFAULT '0',
  `force_password_change` timestamp NULL DEFAULT NULL,
  `updateTime` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  KEY `gameUser_index` (`GameUserId`) USING BTREE,
  KEY `vipLevel` (`vipLevel`) USING BTREE,
  KEY `IN_TESTACCOUNT` (`TestAccount`) USING BTREE,
  KEY `IN_LOGIN` (`Login`),
  KEY `idx_email` (`Email`) USING BTREE,
  KEY `updateTime` (`updateTime`),
  KEY `lastlogin` (`lastLogin`),
  KEY `IXD_createTime` (`createTime`),
  KEY `idx_complet_reg` (`CompletReg`) USING BTREE,
  KEY `idx_ststus` (`PlayerStatus`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb3;

[zachmu]

Thanks for the report, this is a bug. We'll fix it.

As a workaround, select a single column in the NOT EXISTS query.

This works:

select 1 where not exists (select 'a' from dual where 0);

This does not:

select 1 where not exists (select 'a', 'b' from dual where 0);
operand should have 1 columns, but has 2

[itzikiusa]

@zachmu thanks, getting different error when choosing single column (tried both with column name and hard coded value) Error 1105: unknown error: cannot convert type LONGTEXT to INT

this is the new query: (i tried also removing 'FROM DUAL' from query, but still same result)

INSERT INTO MdlGm_tblPlayers(Login, BrandId, Password, Email, CompletReg, SendMail, createTime, PlayerStatus, TestAccount)
 SELECT ?, '1', ?, ?, 0, 0, now(), 'Not verified', ?
 FROM DUAL WHERE NOT EXISTS 
(SELECT Email FROM MdlGm_tblPlayers WHERE BrandId = '1' AND (Login = ? OR Email = ?))

[zachmu]

Hard to tell because you have bind variables in your query above, but I think this is happening because you are inserting the string '1' into the BrandId column, rather than the numeral 1. When I make that change it works for (using the Dolt SQL shell)

[zachmu]

This is now fixed.