With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.
#
# In both cases below consider all instances of "a@b" to also sometimes be of the form "a"
#
#
# Case 1: Does not return row in case of "a@b", does work in case of "a"
#
SELECT
`pwd`
FROM `accts`
WHERE
IF (
INSTR('@', 'a@b') != 0,
`user` = 'a@b' AND `realm` IS NULL,
CONCAT_WS('@', `user`, `realm`) = 'a@b'
)
#
# Case 2: Gives undefined column error
#
SELECT
`pwd`,
CONCAT_WS('@', `user`, `realm`) AS email
FROM `accts`
WHERE
IF (
INSTR('@', 'a@b') != 0,
`user` = 'a@b' AND `realm` IS NULL,
`email` = 'a@b'
)
#
# In both cases below consider all instances of "a@b" to also sometimes be of the form "a"
#
#
# Case 1: Does not return row in case of "a@b", does work in case of "a"
#
SELECT
`pwd`
FROM `accts`
WHERE
IF (
INSTR('@', 'a@b') = 0,
`user` = 'a@b' AND `realm` IS NULL,
CONCAT_WS('@', `user`, `realm`) = 'a@b'
);
#
# Case 2: Gives undefined column error
#
SELECT
`pwd`,
CONCAT_WS('@', `user`, `realm`) AS email
FROM `accts`
WHERE
IF (
INSTR('@', 'a@b') = 0,
`user` = 'a@b' AND `realm` IS NULL,
`email` = 'a@b'
);
SELECT
`pwd`,
CONCAT_WS('@', `user`, `realm`) AS email
FROM `accts`
WHERE
IF (
INSTR('@', 'a@b') = 0,
`user` = 'a@b' AND `realm` IS NULL,
CONCAT_WS('@', `user`, `realm`) = 'a@b'
);
Given the data:
user realm pwd
a b c
a NULL d
In the case of:
WHERE
IF (
INSTR('@', 'a@b') = 0,
`user` = 'a@b' AND `realm` IS NULL,
CONCAT_WS('@', `user`, `realm`) = 'a@b'
);
returns: 0 rows (should be 1 row)
And in the case of:
WHERE
IF (
INSTR('@', 'a') = 0,
`user` = 'a' AND `realm` IS NULL,
CONCAT_WS('@', `user`, `realm`) = 'a'
);
returns: 1 row (just as it should.)
If you are experiencing a similar issue, please ask a related question
Join the community of 500,000 technology professionals and ask your questions.