Incorrect output for the desired question

Question

The aim is to query all CITIES with both the starting and ending alphabet being a vowel. I tried the code below which doesn't seem to work. Can you explain why it doesn't work? Is there a better way?

I have tried to first get the cities which end in a vowel and then try to use it as a subquery for selecting cities that start in a vowel, as below

I have already tried implementing this code:

SELECT DISTINCT(CITY)
FROM STATION
WHERE CITY LIKE 'a%'
  OR CITY LIKE 'e%'
  OR CITY LIKE 'i%'
  OR CITY LIKE 'o%'
  OR CITY LIKE 'u%'
AND CITY IN (
  SELECT DISTINCT(CITY)
  FROM STATION
  WHERE CITY LIKE '%a'
    OR CITY LIKE '%e'
    OR CITY LIKE '%i'
    OR CITY LIKE '%o'
    OR CITY LIKE '%u'
);

The needed output is something like:

Oslo
Upperco
Amazonia
...
...
...

I am getting these as output as well as those cities that don't end in a vowel like:

Arlington 
Albany 
Upperco 
Aguanga 
Odin 
East China 
Algonac 
Onaway 
Irvington 
Arrowsmith 
Oakfield 
Elkton 
East Irvine 
Amo 
...
...
...

AND binds tighter than OR. Your code means ... OR ... OR (... AND ...). You want (... OR ... OR ...) AND ... PS Please in code questions give a [mcve]--cut & paste & runnable code; example input with desired & actual output (including verbatim error messages); tags & clear specification & explanation. That includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) — philipxy, Jun 03 '19 at 06:38
@Tim Biegeleisen That is not a duplicate of this question. This question is asking about what their bug is, not ways to code their goal. — philipxy, Jun 03 '19 at 06:41
Possible duplicate of [SQL Logic Operator Precedence: And and Or](https://stackoverflow.com/q/1241142/3404097) — philipxy, Jun 03 '19 at 06:47

Tim Biegeleisen · Answer 1 · 2019-06-03T06:51:06.417

0

A better way might be to use REGEXP:

SELECT DISTINCT CITY
FROM STATION
WHERE CITY REGEXP '^[aeiou].*[aeiou]$';

Note that REGEXP is not case sensitive, so we only need to use [aeiou] in a single case, in the regular expression.

Your current query is actually logically correct, but won't give you the behavior your want due to incorrect use/understanding of AND and OR. AND has higher precedence than OR, so you are missing some parentheses in your query. Try this version:

SELECT DISTINCT CITY
FROM STATION
WHERE
    (LOWER(CITY) LIKE 'a%' OR
     LOWER(CITY) LIKE 'e%' OR
     LOWER(CITY) LIKE 'i%' OR
     LOWER(CITY) LIKE 'o%' OR
     LOWER(CITY) LIKE 'u%') AND
    (LOWER(CITY) LIKE '%a' OR
     LOWER(CITY) LIKE '%e' OR
     LOWER(CITY) LIKE '%i' OR
     LOWER(CITY) LIKE '%o' OR
     LOWER(CITY) LIKE '%u') ;

edited Jun 03 '19 at 06:51

answered Jun 03 '19 at 05:13

Tim Biegeleisen

502,043
27
286
360

What is "logically correct" supposed to mean? PS Why do you hammer a post to prevent others from answering yet answer? Why do you answer about precedence when that is an obvious duplicate? – philipxy Jun 03 '19 at 06:48
I don't understand. – philipxy Jun 03 '19 at 06:55
You click up arrow, I get points. It is simple. – Tim Biegeleisen Jun 03 '19 at 06:57
This answer post merits a downvote since the question is a duplicate (but not of the one you linked) & so does hammering & answering. – philipxy Jun 03 '19 at 06:58

Incorrect output for the desired question

1 Answers1