I'm baffled at this one...
When I supply values to a WHERE clause in my DQL that are below 1.0e-7
or (0.0000001) Symfony/Doctrine fails to run my query and returns 0
rows (there are supposed to be 54). No error or warning is generated
in the logs. When I use the resulting SQL from doctrine and manually
run in the corresponding MySQL DB, the query runs perfectly.
When I supply values above 1.-0e-7 the query runs perfectly in Symfony/
Doctrine (returning values that are as low as 1.0e-74).
Code:
//==================================
$tq = Doctrine_Query::create()
-> select('t.trait_name, ts.study_id, st.study_trait_id,
s.study_name,ps.snp,snp.rsnumber,tr.p_value')
-> from('TraitResult tr')
//...
-> andwhere('tr.p_value <?', $this -> pvalue);
$this -> results = $tq->execute();
//==================================
schema.yml for column in question:
TraitResult:
connection: doctrine
tableName: trait_result
columns:
p_value:
type: double
unsigned: true
primary: false
notnull: false
autoincrement: false
//==================================
//Sample generated query that DOES NOT RUN:
SELECT t.study_trait_id AS t__study_trait_id, t.strata_id AS
t__strata_id, t.analysis_program_id AS t__analysis_program_id,
t.model_id AS t__model_id, t.platform_snp AS t__platform_snp,
t.p_value AS t__p_value, s.study_trait_id AS s__study_trait_id,
s.study_id AS s__study_id, p.platform_snp AS p__platform_snp, p.snp AS
p__snp, s2.snp AS s2__snp, s2.rsnumber AS s2__rsnumber, s3.study_id AS
s3__study_id, s3.study_name AS s3__study_name, t2.trait_id AS
t2__trait_id, t2.trait_name AS t2__trait_name
FROM trait_result t INNER JOIN study_trait s ON t.study_trait_id =
s.study_trait_id INNER JOIN platform_snp p ON t.platform_snp =
p.platform_snp INNER JOIN snp s2 ON p.snp = s2.snp INNER JOIN platform
p2 ON p.platform_id = p2.platform_id INNER JOIN study s3 ON s.study_id
= s3.study_id INNER JOIN trait t2 ON s.trait_id = t2.trait_id INNER
JOIN model m ON t.model_id = m.model_id
WHERE (t.p_value < '1.0E-7' AND t2.trait_id IN ('81') AND m.model_id
IN ('1') AND s3.study_id IN ('1')) ORDER BY t2.trait_name
0.00s, "doctrine" connection
//==================================
//Sample generated query that DOES RUN:
#
SELECT t.study_trait_id AS t__study_trait_id, t.strata_id AS
t__strata_id, t.analysis_program_id AS t__analysis_program_id,
t.model_id AS t__model_id, t.platform_snp AS t__platform_snp,
t.p_value AS t__p_value, s.study_trait_id AS s__study_trait_id,
s.study_id AS s__study_id, p.platform_snp AS p__platform_snp, p.snp AS
p__snp, s2.snp AS s2__snp, s2.rsnumber AS s2__rsnumber, s3.study_id AS
s3__study_id, s3.study_name AS s3__study_name, t2.trait_id AS
t2__trait_id, t2.trait_name AS t2__trait_name
FROM trait_result t INNER JOIN study_trait s ON t.study_trait_id =
s.study_trait_id INNER JOIN platform_snp p ON t.platform_snp =
p.platform_snp INNER JOIN snp s2 ON p.snp = s2.snp INNER JOIN platform
p2 ON p.platform_id = p2.platform_id INNER JOIN study s3 ON s.study_id
= s3.study_id INNER JOIN trait t2 ON s.trait_id = t2.trait_id INNER
JOIN model m ON t.model_id = m.model_id
WHERE (t.p_value < '1.0E-6' AND t2.trait_id IN ('81') AND m.model_id
IN ('1') AND s3.study_id IN ('1')) ORDER BY t2.trait_name
23.65s, "doctrine" connection
How can I prevent this behavior?
Thanks,
Quince.
N.B. The largest table "trait_result" is a large (MyISAM) table 60
Million rows so 0.00s execution time is highly questionable for un-
cached query.
--
If you want to report a vulnerability issue on symfony, please send it to
security at symfony-project.com
You received this message because you are subscribed to the Google
Groups "symfony users" group.
To post to this group, send email to symfony-users@googlegroups.com
To unsubscribe from this group, send email to
symfony-users+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/symfony-users?hl=en
