Login/Register
is there a limit to what sql fiddle can handle? sql fiddle doesn't compile anything and returns no error messages
Asked Answered
Solved mysqlerror-handlingcompilationlimitsqlfiddle
B

1

6

I recently created a query that compiles successfully and returns the desired result. When I used that piece of code as a subquery in another piece of code that a user on stackoverflow came up with for me, I encountered a few problems, which were ultimately solved. I attempted to use this query as a subquery in that piece of code given to me. However, sql fiddle doesn't return anything. No errors or compiled messages. When I tried putting in a syntax error on purpose-like a random + sign, nothing happened. is it because the query is too long?

schema

CREATE TABLE sampleData 
    (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
      timecode int, 
     count int,
      PRIMARY KEY (id)
    )
#ENGINE=MyISAM
;

INSERT INTO sampleData
(timecode, count)
VALUES
(1344893440, 1), ( 1346014720, 1),( 1344898688,1),( 1345654784,1),( 1345978368,1),
( 1345959296,1), (1345064704,1), ( 1345156352,1),( 1345225600,1),
(1345017984,1),( 1345640960,1),( 1346019968,1),( 1345834752,1),
( 1345438464,1),( 1344986880,1),( 1345045632,1),( 1345557888,1),( 1344973056,1),( 1345087232,1),( 1345433216,1),( 1345691008,1),
( 1344917760,1),( 1345253248,1),( 1344934912,1),( 1345890048,1),( 1345272448,1), (1345829504,1),( 1345798400,1),( 1345203200,1),( 1344741120,1),
( 1345175552,1),( 1344824192,1),( 1344926336,1),( 1345571712,1),( 1344931584,1),( 1345211776,1),( 1345059456,1),( 1345516288,1),( 1345441920,1),( 1346009472,1)

query 

select t_0.*,
           (coalesce(t_3.average_number_of_votes_per_previous_period_days, 0) - coalesce(t_4.average_number_of_votes_per_previous_period_days, 0)) * 100.0
    from 
        (select t.*,
           (coalesce(t_1.count, 0) - coalesce(t_2.count, 0)) * 100.0 as "percentage increase in count in %"
    from  
    (
      SELECT sum(1) AS ordr,
      t1.id,t1.day, t1.count, SUM(t2.count) as aggregate, (SUM(t2.count)-t1.count)/(sum(1)-1) as "average_number_of_votes_per_previous_period_days"

      FROM 
        (SELECT  id, date(FROM_UNIXTIME( timecode))  AS day,(FROM_UNIXTIME( timecode)) AS original, COUNT(1)  as 'count'
        FROM sampleData
        GROUP BY DAY) t1
        INNER JOIN 
        (SELECT date(FROM_UNIXTIME( timecode) ) AS day,(FROM_UNIXTIME( timecode)) AS original, COUNT(1)  as 'count'
        FROM sampleData
        GROUP BY DAY) t2 
        on t1.day >= t2.day                                                                                                                                
        GROUP BY t1.day, t1.count
        ORDER BY t1.day
        )t 
      left outer join
         (
          SELECT sum(1) AS ordr,
            t1.id,t1.day, t1.count, SUM(t2.count) as aggregate, (SUM(t2.count)-t1.count)/(sum(1)-1) as "average_number_of_votes_per_previous_period_days"
          FROM 
            (SELECT  id, date(FROM_UNIXTIME( timecode))  AS day,(FROM_UNIXTIME( timecode)) AS original, COUNT(1)  as 'count'
            FROM sampleData
            GROUP BY DAY) t1
          INNER JOIN 
            (SELECT date(FROM_UNIXTIME( timecode) ) AS day,(FROM_UNIXTIME( timecode)) AS original, COUNT(1)  as 'count'
            FROM sampleData
            GROUP BY DAY) t2 
          on t1.day >= t2.day                                                                                                                                
          GROUP BY t1.day, t1.count
          ORDER BY t1.day
          )t_1
       on t.ordr = t_1.ordr + 1 left outer join
         (
          SELECT sum(1) AS ordr,
            t1.id,t1.day, t1.count, SUM(t2.count) as aggregate, (SUM(t2.count)-t1.count)/(sum(1)-1) as "average_number_of_votes_per_previous_period_days"
          FROM 
              (SELECT  id, date(FROM_UNIXTIME( timecode))  AS day,(FROM_UNIXTIME( timecode)) AS original, COUNT(1)  as 'count'
              FROM sampleData
              GROUP BY DAY) t1
            INNER JOIN 
              (SELECT date(FROM_UNIXTIME( timecode) ) AS day,(FROM_UNIXTIME( timecode)) AS original, COUNT(1)  as 'count'
              FROM sampleData
              GROUP BY DAY) t2 
            on t1.day >= t2.day                                                                                                                                
            GROUP BY t1.day, t1.count
            ORDER BY t1.day
            ) t_2
        on t.ordr = t_2.ordr + 2)t_0 
    left outer join
         (select t.*,
           (coalesce(t_1.count, 0) - coalesce(t_2.count, 0)) * 100.0 as "percentage increase in count in %"
    from  
    (
      SELECT sum(1) AS ordr,
      t1.id,t1.day, t1.count, SUM(t2.count) as aggregate, (SUM(t2.count)-t1.count)/(sum(1)-1) as "average_number_of_votes_per_previous_period_days"

      FROM 
        (SELECT  id, date(FROM_UNIXTIME( timecode))  AS day,(FROM_UNIXTIME( timecode)) AS original, COUNT(1)  as 'count'
        FROM sampleData
        GROUP BY DAY) t1
        INNER JOIN 
        (SELECT date(FROM_UNIXTIME( timecode) ) AS day,(FROM_UNIXTIME( timecode)) AS original, COUNT(1)  as 'count'
        FROM sampleData
        GROUP BY DAY) t2 
        on t1.day >= t2.day                                                                                                                                
        GROUP BY t1.day, t1.count
        ORDER BY t1.day
        )t 
      left outer join
         (
          SELECT sum(1) AS ordr,
            t1.id,t1.day, t1.count, SUM(t2.count) as aggregate, (SUM(t2.count)-t1.count)/(sum(1)-1) as "average_number_of_votes_per_previous_period_days"
          FROM 
            (SELECT  id, date(FROM_UNIXTIME( timecode))  AS day,(FROM_UNIXTIME( timecode)) AS original, COUNT(1)  as 'count'
            FROM sampleData
            GROUP BY DAY) t1
          INNER JOIN 
            (SELECT date(FROM_UNIXTIME( timecode) ) AS day,(FROM_UNIXTIME( timecode)) AS original, COUNT(1)  as 'count'
            FROM sampleData
            GROUP BY DAY) t2 
          on t1.day >= t2.day                                                                                                                                
          GROUP BY t1.day, t1.count
          ORDER BY t1.day
          )t_1
       on t.ordr = t_1.ordr + 1 left outer join
         (
          SELECT sum(1) AS ordr,
            t1.id,t1.day, t1.count, SUM(t2.count) as aggregate, (SUM(t2.count)-t1.count)/(sum(1)-1) as "average_number_of_votes_per_previous_period_days"
          FROM 
              (SELECT  id, date(FROM_UNIXTIME( timecode))  AS day,(FROM_UNIXTIME( timecode)) AS original, COUNT(1)  as 'count'
              FROM sampleData
              GROUP BY DAY) t1
            INNER JOIN 
              (SELECT date(FROM_UNIXTIME( timecode) ) AS day,(FROM_UNIXTIME( timecode)) AS original, COUNT(1)  as 'count'
              FROM sampleData
              GROUP BY DAY) t2 
            on t1.day >= t2.day                                                                                                                                
            GROUP BY t1.day, t1.count
            ORDER BY t1.day
            ) t_2
        on t.ordr = t_2.ordr + 2) t_3
         on t.ordr = t_3.ordr + 1 
    left outer join
         (select t.*,
           (coalesce(t_1.count, 0) - coalesce(t_2.count, 0)) * 100.0 as "percentage increase in count in %"
    from  
    (
      SELECT sum(1) AS ordr,
      t1.id,t1.day, t1.count, SUM(t2.count) as aggregate, (SUM(t2.count)-t1.count)/(sum(1)-1) as "average_number_of_votes_per_previous_period_days"

      FROM 
        (SELECT  id, date(FROM_UNIXTIME( timecode))  AS day,(FROM_UNIXTIME( timecode)) AS original, COUNT(1)  as 'count'
        FROM sampleData
        GROUP BY DAY) t1
        INNER JOIN 
        (SELECT date(FROM_UNIXTIME( timecode) ) AS day,(FROM_UNIXTIME( timecode)) AS original, COUNT(1)  as 'count'
        FROM sampleData
        GROUP BY DAY) t2 
        on t1.day >= t2.day                                                                                                                                
        GROUP BY t1.day, t1.count
        ORDER BY t1.day
        )t 
      left outer join
         (
          SELECT sum(1) AS ordr,
            t1.id,t1.day, t1.count, SUM(t2.count) as aggregate, (SUM(t2.count)-t1.count)/(sum(1)-1) as "average_number_of_votes_per_previous_period_days"
          FROM 
            (SELECT  id, date(FROM_UNIXTIME( timecode))  AS day,(FROM_UNIXTIME( timecode)) AS original, COUNT(1)  as 'count'
            FROM sampleData
            GROUP BY DAY) t1
          INNER JOIN 
            (SELECT date(FROM_UNIXTIME( timecode) ) AS day,(FROM_UNIXTIME( timecode)) AS original, COUNT(1)  as 'count'
            FROM sampleData
            GROUP BY DAY) t2 
          on t1.day >= t2.day                                                                                                                                
          GROUP BY t1.day, t1.count
          ORDER BY t1.day
          )t_1
       on t.ordr = t_1.ordr + 1 left outer join
         (
          SELECT sum(1) AS ordr,
            t1.id,t1.day, t1.count, SUM(t2.count) as aggregate, (SUM(t2.count)-t1.count)/(sum(1)-1) as "average_number_of_votes_per_previous_period_days"
          FROM 
              (SELECT  id, date(FROM_UNIXTIME( timecode))  AS day,(FROM_UNIXTIME( timecode)) AS original, COUNT(1)  as 'count'
              FROM sampleData
              GROUP BY DAY) t1
            INNER JOIN 
              (SELECT date(FROM_UNIXTIME( timecode) ) AS day,(FROM_UNIXTIME( timecode)) AS original, COUNT(1)  as 'count'
              FROM sampleData
              GROUP BY DAY) t2 
            on t1.day >= t2.day                                                                                                                                
            GROUP BY t1.day, t1.count
            ORDER BY t1.day
            ) t_2
        on t.ordr = t_2.ordr + 2) t_4
        on t_0.ordr = t_4.ordr + 2
Blearyeyed answered 15/8, 2012 at 21:15 Comment(5)
Adding the query to your question could help. SQLFiddle example could help even more. – Wideman
problem is the sql fiddle doesn't compile. so i don't get a link to the query. but i'll post the query. its really long though – Blearyeyed
Maybe you should also include the create table + insert statements too. (Especially because SQLFiddle creator Jake Feasel will probably find this question.) BTW, does your query work under for example phpMyAdmin? – Torto
There should be a message if you exceed the 8000 character limit for schema creation. Also, there should be an error message if you schema fails to build for other reasons. – Chiasmus
@JakeFeasel the schema is added and it isn't too long – Blearyeyed
C
6

I've plugged your query in this fiddle, and I now see the problem. Your query is over 8000 characters long (8423 to be exact), and I'm not showing that message on the result panel. Basically, this is a display bug in SQL Fiddle that I've not noticed before (so, thanks for bringing this to my attention!).

In the mean time, you could try cutting out some of the characters to make it fit within the 8000 character limit.

Chiasmus answered 15/8, 2012 at 22:0 Comment(1)
Interestingly, if I just hit "Format SQL" with your query in there, it reduces the number of characters to be within the 8000 limit: sqlfiddle.com/#!2/97a57/1 – Chiasmus

© 2022 - 2024 — McMap. All rights reserved.