Second Subquery Inside INSERT Into saves int 0

Question

Read it carefully, we have this query which is inserting values in the table called users. For the value member_id we are running a subquery to select from the table admin_users the id of the member. The reason why there are single quotes with +, it's because we are trying to manipulate the query. At this moment this first subquery works correctly but what happends with the second subquery?

The second subquery selects the pass from the table settings, the table settings and the value pass totally exists and there is only one record, but this second query inside the INSERT INTO is not returning nothing. When the execution of the query INSERT INTO finishs, all the values are stored correctly except notes column which finally inserts 0. I don't know why but if you delete all the ''+ it works correctly the whole sql statement but in this time we can not delete ''+ because we are altering the query. I need a solution for this issue.

INSERT INTO `users` (`username`,`password`,`number`,`member_id`,`exp_date`,`notes`)
    VALUES
       ('balvin','sjeneoeoe','3',
         ''+(select id from `admin_users` where username = 'TEST')+'',
        '1644622354',
        '' + (select pass from `settings`));#;');

Also i have tried modifying the second subquery like this but it didn't work.

'' + (select pass from `settings` LIMIT 1)
'' + (select pass from `settings` GROUP BY pass LIMIT 1)
'' + (select pass from `settings` where id = 1 LIMIT 1)

Perhaps the error it's the datatype of the column value pass in settings or the column notes in users

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `member_id` int(11) DEFAULT NULL,
  `username` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `password` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `exp_date` int(11) DEFAULT NULL,
  `notes` mediumtext COLLATE utf8_unicode_ci NOT NULL,
  `number` int(11) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  KEY `member_id` (`member_id`),
  KEY `exp_date` (`exp_date`),
  KEY `username` (`username`),
  KEY `password` (`password`),
) ENGINE=InnoDB AUTO_INCREMENT=1702894 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

    
CREATE TABLE `settings` (
  `id` int(11) NOT NULL,
  `name` mediumtext COLLATE utf8_unicode_ci NOT NULL,
  `pass` mediumtext COLLATE utf8_unicode_ci NOT NULL,
  ...
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Answer 1

+ is only for arithmetic. '' is treated as 0 when used in arithmetic. Ditto for strings that don't start with a number.

You don't need CONCAT() either.

Simply have ... , ( SELECT ... ), ...

On the other hand, it is usually better to do

INSERT INTO t (a,b,c)
    SELECT ... AS a,
           ... AS b,
           ... AS c
       FROM ...
       WHERE ...

And that SELECT can contain a JOIN if necessary.

On to your second attempt:

'' + (select pass from `settings` LIMIT 1)

First get rid of the arithmetic,...

(select pass from `settings` LIMIT 1)

Now think about what is in settings. Are there multiple rows? If so, which one with the "limit 1" give you? You cannot predict. Perhaps you need a WHERE clause instead of LIMIT 1?