Databases
StackOverflow2013
Postsdbo
POWhy does SELECT results differ between mysql and sqlite?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POWhy does SELECT results differ between mysql and sqlite?
    primarykey
    data
    text
    <p>I'm re-asking this <a href="https://stackoverflow.com/questions/10161153/sqlite-exists-keyword-how-to-query-the-highest-average">question</a> in a simplified and expanded manner.</p> <p>Consider these sql statements:</p> <pre><code>create table foo (id INT, score INT); insert into foo values (106, 4); insert into foo values (107, 3); insert into foo values (106, 5); insert into foo values (107, 5); select T1.id, avg(T1.score) avg1 from foo T1 group by T1.id having not exists ( select T2.id, avg(T2.score) avg2 from foo T2 group by T2.id having avg2 &gt; avg1); </code></pre> <p>Using sqlite, the <code>select</code> statement returns:</p> <pre><code>id avg1 ---------- ---------- 106 4.5 107 4.0 </code></pre> <p>and mysql returns:</p> <pre><code>+------+--------+ | id | avg1 | +------+--------+ | 106 | 4.5000 | +------+--------+ </code></pre> <p>As far as I can tell, mysql's results are correct, and sqlite's are incorrect. I tried to cast to <code>real</code> with sqlite as in the following but it returns two records still:</p> <pre><code>select T1.id, cast(avg(cast(T1.score as real)) as real) avg1 from foo T1 group by T1.id having not exists ( select T2.id, cast(avg(cast(T2.score as real)) as real) avg2 from foo T2 group by T2.id having avg2 &gt; avg1); </code></pre> <p>Why does sqlite return two records?</p> <p><strong>Quick update</strong>:</p> <p>I ran the statement against the latest sqlite version (3.7.11) and still get two records.</p> <p><strong>Another update</strong>:</p> <p>I sent an email to sqlite-users@sqlite.org about the issue.</p> <p>Myself, I've been playing with VDBE and found something interesting. I split the execution trace of each loop of <code>not exists</code> (one for each avg group).</p> <p>To have three avg groups, I used the following statements:</p> <pre><code>create table foo (id VARCHAR(1), score INT); insert into foo values ('c', 1.5); insert into foo values ('b', 5.0); insert into foo values ('a', 4.0); insert into foo values ('a', 5.0); PRAGMA vdbe_listing = 1; PRAGMA vdbe_trace=ON; select avg(score) avg1 from foo group by id having not exists ( select avg(T2.score) avg2 from foo T2 group by T2.id having avg2 &gt; avg1); </code></pre> <p>We clearly see that somehow what should be <code>r:4.5</code> has become <code>i:5</code>:</p> <p><img src="https://i.stack.imgur.com/lIdWx.png" alt="enter image description here"></p> <p>I'm now trying to see why that is.</p> <p><strong>Final edit</strong>:</p> <p>So I've been playing enough with the sqlite source code. I understand the beast much better now, although I'll let the <a href="http://sqlite.org/support.html" rel="nofollow noreferrer">original developer</a> sort it out as he seems to already be doing it:</p> <p><a href="http://www.sqlite.org/src/info/430bb59d79" rel="nofollow noreferrer">http://www.sqlite.org/src/info/430bb59d79</a></p> <p>Interestingly, to me at least, it seems that the newer versions (some times after the version I'm using) supports inserting multiple records as used in a test case added in the aforementioned commit:</p> <pre><code>CREATE TABLE t34(x,y); INSERT INTO t34 VALUES(106,4), (107,3), (106,5), (107,5); </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. This table or related slice is empty.
    plurals
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POWhy does SELECT results differ between mysql and sqlite?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POWhy does SELECT results differ between mysql and sqlite?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POWhy does SELECT results differ between mysql and sqlite?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COJust for kicks I ran this to SQL what SQL Server would produce and it complained `avg2` and `avg1` didn't exist. I replaced them with `MAX(T2.score)` and `MAX(T1.score)` and it gave the SQLite result. When I created the table with `score REAL` it gave the MySQL result. Perhaps your MySQL schema is different to sqlites?
      singulars
      1. POWhy does SELECT results differ between mysql and sqlite?
      1. USta.speot.is
    2. CO@ta.speot.is: can you try adding `as` as in `avg(T2.score) as avg2` (two occurences)?
      singulars
      1. POWhy does SELECT results differ between mysql and sqlite?
      1. This table or related slice is empty.
    3. CODoesn't work. Pretty sure SQL Server doesn't play dice when it comes to using aliases in `WHERE`, `GROUP BY` or `HAVING`.
      singulars
      1. POWhy does SELECT results differ between mysql and sqlite?
      1. USta.speot.is
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload