#
# 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
Title | # Comments | Views | Activity |
---|---|---|---|
Multilanguage Database Design in MySQL | 5 | 118 | |
selecting date modified field from a table | 2 | 45 | |
RDBMS and No sql database | 4 | 58 | |
mysql update statement | 3 | 23 |
Join the community of 500,000 technology professionals and ask your questions.