Mysql – Subquery in INSERT works in SQL but not PDO

I want to insert a new dataset into a MySQL table tab with external data, but also with data from another table otherTab using the others' table primary key and another condition. However, it could be that the requested row simply does not exist (anymore) or the result set is empty due to a mismatch in the other supplied data. All columns are forbidden to be NULL.

My first attempt was:

INSERT INTO tab (id, extid1, extid2, value)
SELECT 1,
       (SELECT id FROM otherTab WHERE id = 12 AND data = 'TXT'),
       (SELECT id FROM otherTab WHERE id = 34 AND data = 'JPG'),
       1234

but the problem with it is that a returned empty result set is cast to the type of the column in tab, leading to a 0 as entry data.

The query shall be efficient and avoid unnecessary querying. This is how I achieve it with four subqueries:

INSERT INTO tab (id, extid1, extid2, value)
SELECT 1,
       (SELECT id FROM otherTab WHERE id = 12 AND data = 'TXT'),
       (SELECT id FROM otherTab WHERE id = 34 AND data = 'JPG'),
       1234
WHERE EXISTS (SELECT id FROM otherTab WHERE id = 12 AND data = 'TXT')
  AND EXISTS (SELECT id FROM otherTab WHERE id = 34 AND data = 'JPG')

Is this, or a JOIN, or a transaction with a temporary variable and IF construct the only option?

I tried with other constructs, e.g. (SELECT IFNULL(SELECT id FROM otherTab WHERE id = 12 AND data = 'TXT', NULL)) to enforce NULL or even a string into the target column, but it also gets casted to a 0 or some value instead.

EDIT
Here is the code for dbFiddle:

code

CREATE TABLE `tab` (
  `id` int NOT NULL,
  `seUuid4` binary(16) NOT NULL,
  `rxUuid4` binary(16) NOT NULL,
  `text` varchar(16)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `otherTab` (
  `uuid4` binary(16) NOT NULL,
  `lgUuid4` binary(16) NOT NULL,
  `data` varchar(16)
) ENGINE = InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

ALTER TABLE `otherTab`
  ADD PRIMARY KEY(`uuid4`);
ALTER TABLE `tab`
  ADD CONSTRAINT `tab_ibfk_1` FOREIGN KEY (`rxUuid4`) REFERENCES `otherTab` (`uuid4`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  ADD CONSTRAINT `tab_ibfk_2` FOREIGN KEY (`seUuid4`) REFERENCES `otherTab` (`uuid4`) ON DELETE RESTRICT ON UPDATE RESTRICT;

INSERT INTO `otherTab` (uuid4, lgUuid4, data) VALUES
(UNHEX("22224444aaaa49c782408b2fe8c4dee0"), UNHEX("00001234aaaa4444aaaa432187654321"), "JPG"),
(UNHEX("11113333aaaa49c782408b2fe8c4dee0"), UNHEX("12340000bbbb6666bbbb432187654321"), "TXT");

INSERT INTO tab (id, seUuid4, rxUuid4, text)
SELECT
    1,
    (SELECT uuid4 FROM otherTab WHERE lgUuid4 = UNHEX('00001234aaaa4444aaaa432187654321') AND data = 'JPK' LIMIT 0,1),
    (SELECT uuid4 FROM otherTab WHERE lgUuid4 = UNHEX('12340000bbbb6666bbbb432187654321') AND data = 'TXT' LIMIT 0,1),
    'some text'

This interestingly works exactly as expected. I verified my code (PHP – I know..) and the PDO prepared statement fires out exactly the same command, but it gets inserted as INSERT INTO tab (id, seUuid4, rxUuid4) VALUES (1, 0x00000000000000000000000000000000, 0x00000000000000000000000000000000, 'datatext'); while the SQL client and phpMyadmin deliver the expected cannot insert null error message.

I am off to check the PDO options – sorry for the confusion 🙂

EDIT2: changed the title name and will leave it open if someone has an idea by chance. Otherwise, I will ask the PHP/PDO experts.