Hi Ivan, thanks for your thoughts. IMO however you cannot compare those two.
In the constraints case you are dealing with logical boolean operands instead of a mathematical formula. Also in constraints there is no need for parenthesis between AND operations. So automatically encapsulating OR expressions has no drawback. Furthermore especially in SQL there is really very very rarely the need for an OR operator at all. In many cases you should rather use BEWEEN or IN anyway. In the case you mentioned e.g. you would rather write SELECT t1.* FROM T t1 WHERE t1.A in (1, 2, 3) AND t1.B = 10 Regards Rainer > from: ivan nemeth [mailto:[email protected]] > to: [email protected] > subject: Re: Bug with DBColumnExpr.multiplyWith and divide > > Hi Rainer, > > ok, I see, just one last remark, that at the same time the same logic doesn't > apply for OR and AND expressions, I mean you can't write > > SELECT t1.* FROM T t1 > WHERE *(t1.A = 1 OR t1.A = 2 OR t1.A = 3) AND t1.B = 10* > > instead the resulting sql will have a lot of parenthesis for each DBExpr. > > Regards, > Ivan > > > > Rainer Döbele <[email protected]> ezt írta (időpont: 2016. szept. 9., P, > 10:01): > > > Hi Ivan, > > > > originally it was like you are proposing, but we had to change it and > > introduce the parentheses() function. > > > > I cannot remember exactly when this was done (2-3 years at least), but > > it was a design decision, that the developer must have full control > > over the generated SQL, even if that means that the Java code gets more > verbose. > > > > How else would you e.g. achieve be able to achieve this: > > > > SELECT (t1.A + t1.B + t1.C + t1.D) * 2.0 > > > > Regards, > > Rainer > > > > ________________________________________ > > from: Ivan Nemeth [[email protected]] > > to: [email protected] > > subject: Re: Bug with DBColumnExpr.multiplyWith and divide > > > > Hi Rainer, > > > > (JIRA is now ok, it was my fault.) > > > > Ok, I see that parenthesis solves my problem, but I think that an > > expression builder written in Java should follow Java rules. If I > > write the same expression with BigDecimals > > > > A.add(B).multiply(C) it is translated to (A + B) * C > > > > As a Java developer I would expect that DBColumnExpr works the same > > way. At the time when I write an Empire expression in *Java *I don't > > want to take care how it is translated to *SQL*. I understand that too > > many parenthesis may lead to a hard-to-read sql (have you seen any > Hibernate generated sql? > > :D), but with parenthesis() the Java code will be much more verbose. > > The main advantage of a query builder is that you have to work with > > the generated sql only a few times. Not to mention that my query is > > already hard to read with 2-3 subqueries and joins, so I have to use > > some sql formatter to check the generated code. > > > > Moreover what I've suggested is only needed if 1. the expression is a > > DBCalcExpr and 2. the op is multiply or divide. > > > > Regards, > > Ivan > > … > > > > > > > > > > > > > > > > > > > Rainer Döbele <[email protected]> ezt írta (időpont: 2016. szept. > > > 8., > > Cs, > > > 21:49): > > > > > >> Hi Ivan, > > >> > > >> I don't know about the JIRA issue. As far as I know anybody who is > > logged > > >> in can create issues. > > >> I have no knowledge about granting or refusing access to anybody. > > >> > > >> But I do have knowledge about the parenthesis issue. > > >> We do not automatically create parenthesis as it imposes some > > >> restrictions or at least might make complex expressions unreadable. > > >> Hence, just as in real life, you have to specifically make your > > >> parentheses where you need them. > > >> > > >> In your case I guess > > >> > > >> COL = T.A.plus(T.B).parenthesis().multiplyWith(2.0); > > >> > > >> would be the solutions you are looking for. > > >> > > >> Regards > > >> Rainer > > >> > > >> > > >> > from: Ivan Nemeth [mailto:[email protected]] > > >> > to: [email protected] > > >> > subject: Bug with DBColumnExpr.multiplyWith and divide > > >> > > > >> > Hi, > > >> > > > >> > (Rainer, couldn't create an issue in Jira, please can you check it? > > >> (missing > > >> > permission maybe?)) > > >> > > > >> > Empire generates wrong sql if you multiply a DBCalcExpr with some > > value, > > >> > example: > > >> > > > >> > DBDatabase db = new DBDatabase() { }; db.open(new > > >> > DBDatabaseDriverHSql(), null); class Test extends DBTable > > { > > >> > > > >> > final DBTableColumn A; > > >> > final DBTableColumn B; > > >> > public Test(String name, DBDatabase db) { super(name, db); A = > > >> > addColumn("A", DataType.INTEGER, 64, true); B = addColumn("B", > > >> > DataType.INTEGER, 64, true); } } > > >> > > > >> > Test T = new Test("test", db); > > >> > DBCommand cmd = db.createCommand(); *DBColumnExpr COL = > > >> > T.A.plus(T.B).multiplyWith(2.0);* > > >> > > > >> > cmd.select(COL); > > >> > > > >> > System.out.println(cmd.getSelect()); > > >> > > > >> > The expected sql would be: > > >> > > > >> > *SELECT (t1.A + t1.B) * 2.0 FROM test t1* > > >> > > > >> > but Empire generates it with no parentheses > > >> > > > >> > *SELECT t1.A + t1.B * 2.0 FROM test t1* > > >> > > > >> > Solution would be to append parentheses in DBCalcExpr.addSql > > >> > method something like this (it is required only for * and / operators): > > >> > > > >> > *buf.append("(");* > > >> > expr.addSQL(buf, context); > > >> > *buf.append(")"); * > > >> > buf.append(op); > > >> > // Special treatment for adding days to dates ... > > >> > > > >> > What do you think? > > >> > > > >> > > > >> > Regards, > > >> > Ivan > > >> > > >
