Just to chime in - yes, the SQL is already correct for the reasons Blama explained. It is not minimal, and we don't make an attempt to generate minimal SQL, since:
1. query optimizers in modern databases already remove redundant checks
2. with all the options for field-level customized SQL, trying to minimize SQL across different fields would have lots of tricky cases where it is unclear whether the SQL can be minimized safely
Announcement
Collapse
No announcement yet.
X
-
Originally posted by edulid View PostExample:
Code:(t_vertrag.f_vertrag_beginn <= '20141103' OR t_vertrag.f_vertrag_beginn IS NULL) AND (t_vertrag.f_vertrag_beginn IS NOT NULL)
The SQL should be:
Code:t_vertrag.f_vertrag_beginn <= '20141103' AND t_vertrag.f_vertrag_beginn IS NOT NULL
But lets wait for Isomorphic to answer. Out of curiosity I'm also interested in this.
Best regards,
Blama
Leave a comment:
-
Originally posted by edulid View PostThe first part of the "vertragGekuendigtCriteria" is generating :
Code:t_vertrag.f_vertrag_beginn <= '20141103' OR t_vertrag.f_vertrag_beginn IS NULL
Code:t_vertrag.f_vertragsende >= '20141103' AND t_vertrag.f_vertragsende IS NOT NULL
- < and <= are amended with OR IS NULL
- > and >= and = are amended with AND IS NOT NULL
That way the NULL-rows appear once (and not twice as it would be with the simple consistent behaviour of adding OR IS NULL every time).
I don't know why this "AND IS NOT NULL" is happening - perhaps it is already there for many years and was added in order to help (very) old database versions to use indexes. From a logical point of view it is useless - although I don't think that it will have any negative impact in production.
Best regards,
Blama
Leave a comment:
- < and <= are amended with OR IS NULL
-
I changed the method to:
Code:private AdvancedCriteria getSchuelerCriteria(Date stichtag) { OrCriterion vertragGekuendigtCriteria = new OrCriterion( new Criterion[] { new IsNullCriterion("f_vertrag_gekuendigt"), new AndCriterion(new SimpleCriterion( "f_vertrag_gekuendigt", DefaultOperators.LessThan.getID(), stichtag), new NotNullCriterion("f_vertrag_gekuendigt")) }); AndCriterion vertragZeitraumCriteria = new AndCriterion( new Criterion[] { new AndCriterion( new SimpleCriterion("f_vertrag_beginn", DefaultOperators.LessOrEqual.getID(), stichtag), new NotNullCriterion( "f_vertrag_beginn")), new AndCriterion(new SimpleCriterion("f_vertragsende", DefaultOperators.GreaterOrEqual.getID(), stichtag), new NotNullCriterion( "f_vertragsende")) }); AdvancedCriteria schuelerCriteria = new AdvancedCriteria( DefaultOperators.And.getID(), vertragGekuendigtCriteria, vertragZeitraumCriteria); return schuelerCriteria; }
Code:SELECT t_vertrag.f_id, t_vertrag.f_vertrag_gekuendigt, t_vertrag.f_vertrag_beginn, t_vertrag.f_vertragsende FROM t_vertrag WHERE (( (t_vertrag.f_vertrag_gekuendigt IS NULL) OR ((t_vertrag.f_vertrag_gekuendigt < '20141103' OR t_vertrag.f_vertrag_gekuendigt IS NULL) AND (t_vertrag.f_vertrag_gekuendigt IS NOT NULL))) AND (((t_vertrag.f_vertrag_beginn <= '20141103' OR t_vertrag.f_vertrag_beginn IS NULL) AND (t_vertrag.f_vertrag_beginn IS NOT NULL)) AND ((t_vertrag.f_vertragsende >= '20141103' AND t_vertrag.f_vertragsende IS NOT NULL) AND (t_vertrag.f_vertragsende IS NOT NULL))))
Code:(t_vertrag.f_vertrag_beginn <= '20141103' OR t_vertrag.f_vertrag_beginn IS NULL) AND (t_vertrag.f_vertrag_beginn IS NOT NULL)
The SQL should be:
Code:t_vertrag.f_vertrag_beginn <= '20141103' AND t_vertrag.f_vertrag_beginn IS NOT NULL
Leave a comment:
-
The first part of the "vertragGekuendigtCriteria" is generating :
Code:t_vertrag.f_vertrag_beginn <= '20141103' OR t_vertrag.f_vertrag_beginn IS NULL
Code:t_vertrag.f_vertragsende >= '20141103' AND t_vertrag.f_vertragsende IS NOT NULL
I read the posts you linked and I am not sure why these criteria are changed from the framework.
Leave a comment:
-
Hello edulid,
that's on purpose. You'll have to add a NOT_NULL-criterion to your (Advanced)Criteria in order to get rid of the NULL-value rows.
See this two threads.
@Isomorphic: You could amend the OperatorID-docs regarding this in order to show that it is on purpose.
Best regards,
Blama
Leave a comment:
-
Incorrect advancedCriteria query
I am getting incorrect results from the server-side AdvancedCriteria:
EntryPoint:
Code:public class TestingModule implements EntryPoint { @Override public void onModuleLoad() { ListGrid lg = new ListGrid(); lg.setDataSource(DataSource.get("table")); ListGridField idField = new ListGridField("f_id"); idField.setTitle("ID"); lg.setFields(idField); lg.setWidth100(); lg.setHeight100(); final VStack layout = new VStack(10); layout.setWidth100(); layout.setHeight100(); layout.setMembers(lg); lg.fetchData(); layout.draw(); } }
Code:<DataSource ID="table" serverType="sql" tableName="t_vertrag"> <fields> <field name="f_id" type="sequence" primaryKey="true" /> <field name="f_vertrag_gekuendigt" type="date" /> <field name="f_vertrag_beginn" type="date" /> <field name="f_vertragsende" type="date" /> </fields> <operationBindings> <operationBinding operationType="fetch"> <serverObject className="de.mks_infofabrik.kids.server.dmi.TestDMIHandler" methodName="doFetch" /> </operationBinding> </operationBindings> </DataSource>
Code:public class TestDMIHandler { public DSResponse doFetch(DSRequest dsRequest) throws Exception { dsRequest.setAdvancedCriteria(getSchuelerCriteria(new Date())); DSResponse response = dsRequest.execute(); return response; } private AdvancedCriteria getSchuelerCriteria(Date stichtag) { AdvancedCriteria vertragGekuendigtCriteria = new AdvancedCriteria( DefaultOperators.Or.getID(), new Criterion[] { new IsNullCriterion("f_vertrag_gekuendigt"), new SimpleCriterion("f_vertrag_gekuendigt", DefaultOperators.LessThan.getID(), stichtag) }); AdvancedCriteria vertragZeitraumCriteria = new AdvancedCriteria( DefaultOperators.And.getID(), new Criterion[] { new SimpleCriterion("f_vertrag_beginn", DefaultOperators.LessOrEqual.getID(), stichtag), new SimpleCriterion("f_vertragsende", DefaultOperators.GreaterOrEqual.getID(), stichtag) }); AdvancedCriteria schuelerCriteria = new AdvancedCriteria( DefaultOperators.And.getID(), vertragGekuendigtCriteria.asCriterion(), vertragZeitraumCriteria.asCriterion()); return schuelerCriteria; } }
Code:SELECT t_vertrag.f_id, t_vertrag.f_vertrag_gekuendigt, t_vertrag.f_vertrag_beginn, t_vertrag.f_vertragsende FROM t_vertrag WHERE (((t_vertrag.f_vertrag_gekuendigt IS NULL) OR (t_vertrag.f_vertrag_gekuendigt < '20141103' OR t_vertrag.f_vertrag_gekuendigt IS NULL)) AND ((t_vertrag.f_vertrag_beginn <= '20141103' OR t_vertrag.f_vertrag_beginn IS NULL) AND (t_vertrag.f_vertragsende >= '20141103' AND t_vertrag.f_vertragsende IS NOT NULL)))
Code:SELECT t_vertrag.f_id, t_vertrag.f_vertrag_gekuendigt, t_vertrag.f_vertrag_beginn, t_vertrag.f_vertragsende FROM t_vertrag WHERE (((t_vertrag.f_vertrag_gekuendigt IS NULL) OR (t_vertrag.f_vertrag_gekuendigt < '20141103' AND t_vertrag.f_vertrag_gekuendigt IS NOT NULL)) AND ((t_vertrag.f_vertrag_beginn <= '20141103' AND t_vertrag.f_vertrag_beginn IS NOT NULL) AND (t_vertrag.f_vertragsende >= '20141103' AND t_vertrag.f_vertragsende IS NOT NULL)))
Using SmartGWT 4.1p power v9.1p_2014-10-28/PowerEdition Deployment (built 2014-10-28) with MSSQL 2014.Tags: None
Leave a comment: