Some part of this parser is too powerful?

[jadetang]

for example, the where clause parser:

def cmp_expr: Parser[SqlExpr] =
    add_expr ~ rep(
      ("=" | "<>" | "!=" | "<" | "<=" | ">" | ">=") ~ add_expr ^^ {
        case op ~ rhs => (op, rhs)
      } |
      "between" ~ add_expr ~ "and" ~ add_expr ^^ {
        case op ~ a ~ _ ~ b => (op, a, b)
      } |
      opt("not") ~ "in" ~ "(" ~ (select | rep1sep(expr, ",")) ~ ")" ^^ {
        case n ~ op ~ _ ~ a ~ _ => (op, a, n.isDefined)
      } |
      opt("not") ~ "like" ~ add_expr ^^ { case n ~ op ~ a => (op, a, n.isDefined) }
    ) ^^ {
      case lhs ~ elems =>
        elems.foldLeft(lhs) {
          case (acc, (("=", rhs: SqlExpr))) => Eq(acc, rhs)
          case (acc, (("<>", rhs: SqlExpr))) => Neq(acc, rhs)
          case (acc, (("!=", rhs: SqlExpr))) => Neq(acc, rhs)
          case (acc, (("<", rhs: SqlExpr))) => Lt(acc, rhs)
          case (acc, (("<=", rhs: SqlExpr))) => Le(acc, rhs)
          case (acc, ((">", rhs: SqlExpr))) => Gt(acc, rhs)
          case (acc, ((">=", rhs: SqlExpr))) => Ge(acc, rhs)
          case (acc, (("between", l: SqlExpr, r: SqlExpr))) => And(Ge(acc, l), Le(acc, r))
          case (acc, (("in", e: Seq[_], n: Boolean))) => In(acc, e.asInstanceOf[Seq[SqlExpr]], n)
          case (acc, (("in", s: SelectStmt, n: Boolean))) => In(acc, Seq(Subselect(s)), n)
          case (acc, (("like", e: SqlExpr, n: Boolean))) => Like(acc, e, n)
        }
    } |
    "not" ~> cmp_expr ^^ (Not(_)) |
    "exists" ~> "(" ~> select <~ ")" ^^ { case s => Exists(Subselect(s)) }

could parser a simple sql "select * from user where user=20=20" which actually violates the sql grammar,here is test case:

"try sql" in {
      val str = """ select * from user where user.age=20=20"""
      val parser = new SQLParser
      val r = parser.parse(str)
      r should beSome
    }

[stephentu]

Hi,

I actually think this is valid sql. For instance, sqlite agrees with me:

sqlite> select * from user where user.age=26=0; 
stephentu|27
stephentu|1

[jadetang]

ha, mysql accept this, but oracle don't

[jadetang]

i tried this sql, it also pass the test

select user and name from table;

[stephentu]

Once again, I think this is valid sql as well:

sqlite> create table t (user varchar(10), name varchar(10));
sqlite> select user and name from t;
sqlite>

[jadetang]

try this sql

select user and name from table;

in three database in production, the result is: vertica: wrong oracle: wrong mysql: sql not return column 'name' and 'user',instead return 0 for each row.

select user and name from table where max(user)=1;

is wrong in oracle, because the 'group function is not allowed'. I guess it is same for other databases.