[dbsrgits/SQL-Abstract.git] / t / 11parser.t

use strict;
use warnings;

use Test::More;
use Test::Warn;
use SQL::Abstract::Tree;

my $sqlat = SQL::Abstract::Tree->new;
is_deeply($sqlat->parse("SELECT a, b.*, * FROM foo WHERE foo.a =1 and foo.b LIKE 'station'"), [
  [
    "SELECT",
    [
      [
        "-LIST",
        [
          [
            "-LITERAL",
            [
              "a"
            ]
          ],
          [
            "-LITERAL",
            [
              "b.*"
            ]
          ],
          [
            "-LITERAL",
            [
              "*"
            ]
          ]
        ]
      ]
    ]
  ],
  [
    "FROM",
    [
      [
        "-LITERAL",
        [
          "foo"
        ]
      ]
    ]
  ],
  [
    "WHERE",
    [
      [
        "AND",
        [
          [
            "=",
            [
              [
                "-LITERAL",
                [
                  "foo.a"
                ]
              ],
              [
                "-LITERAL",
                [
                  1
                ]
              ]
            ]
          ],
          [
            "LIKE",
            [
              [
                "-LITERAL",
                [
                  "foo.b"
                ]
              ],
              [
                "-LITERAL",
                [
                  "'station'"
                ]
              ]
            ]
          ]
        ]
      ]
    ]
  ]
], 'simple statement parsed correctly');

is_deeply($sqlat->parse( "SELECT * FROM (SELECT * FROM foobar) foo WHERE foo.a =1 and foo.b LIKE 'station'"), [
  [
    "SELECT",
    [
      [
        "-LITERAL",
        [
          "*"
        ]
      ]
    ]
  ],
  [
    "FROM",
    [
      [
        "-MISC",
        [
          [
            "-PAREN",
            [
              [
                "SELECT",
                [
                  [
                    "-LITERAL",
                    [
                      "*"
                    ]
                  ]
                ]
              ],
              [
                "FROM",
                [
                  [
                    "-LITERAL",
                    [
                      "foobar"
                    ]
                  ]
                ]
              ]
            ]
          ],
          [
            "-LITERAL",
            [
              "foo"
            ]
          ]
        ]
      ]
    ]
  ],
  [
    "WHERE",
    [
      [
        "AND",
        [
          [
            "=",
            [
              [
                "-LITERAL",
                [
                  "foo.a"
                ]
              ],
              [
                "-LITERAL",
                [
                  1
                ]
              ]
            ]
          ],
          [
            "LIKE",
            [
              [
                "-LITERAL",
                [
                  "foo.b"
                ]
              ],
              [
                "-LITERAL",
                [
                  "'station'"
                ]
              ]
            ]
          ]
        ]
      ]
    ]
  ]
], 'subquery statement parsed correctly');

is_deeply($sqlat->parse( "SELECT [screen].[id], [screen].[name], [screen].[section_id], [screen].[xtype] FROM [users_roles] [me] JOIN [roles] [role] ON [role].[id] = [me].[role_id] JOIN [roles_permissions] [role_permissions] ON [role_permissions].[role_id] = [role].[id] JOIN [permissions] [permission] ON [permission].[id] = [role_permissions].[permission_id] JOIN [permissionscreens] [permission_screens] ON [permission_screens].[permission_id] = [permission].[id] JOIN [screens] [screen] ON [screen].[id] = [permission_screens].[screen_id] WHERE ( [me].[user_id] = ? ) GROUP BY [screen].[id], [screen].[name], [screen].[section_id], [screen].[xtype]"), [
  [
    "SELECT",
    [
      [
        "-LIST",
        [
          [
            "-LITERAL",
            [
              "[screen].[id]"
            ]
          ],
          [
            "-LITERAL",
            [
              "[screen].[name]"
            ]
          ],
          [
            "-LITERAL",
            [
              "[screen].[section_id]"
            ]
          ],
          [
            "-LITERAL",
            [
              "[screen].[xtype]"
            ]
          ]
        ]
      ]
    ]
  ],
  [
    "FROM",
    [
      [
        "-MISC",
        [
          [
            "-LITERAL",
            [
              "[users_roles]"
            ]
          ],
          [
            "-LITERAL",
            [
              "[me]"
            ]
          ]
        ]
      ]
    ]
  ],
  [
    "JOIN",
    [
      [
        "-MISC",
        [
          [
            "-LITERAL",
            [
              "[roles]"
            ]
          ],
          [
            "-LITERAL",
            [
              "[role]"
            ]
          ]
        ]
      ]
    ]
  ],
  [
    "ON",
    [
      [
        "=",
        [
          [
            "-LITERAL",
            [
              "[role].[id]"
            ]
          ],
          [
            "-LITERAL",
            [
              "[me].[role_id]"
            ]
          ]
        ]
      ]
    ]
  ],
  [
    "JOIN",
    [
      [
        "-MISC",
        [
          [
            "-LITERAL",
            [
              "[roles_permissions]"
            ]
          ],
          [
            "-LITERAL",
            [
              "[role_permissions]"
            ]
          ]
        ]
      ]
    ]
  ],
  [
    "ON",
    [
      [
        "=",
        [
          [
            "-LITERAL",
            [
              "[role_permissions].[role_id]"
            ]
          ],
          [
            "-LITERAL",
            [
              "[role].[id]"
            ]
          ]
        ]
      ]
    ]
  ],
  [
    "JOIN",
    [
      [
        "-MISC",
        [
          [
            "-LITERAL",
            [
              "[permissions]"
            ]
          ],
          [
            "-LITERAL",
            [
              "[permission]"
            ]
          ]
        ]
      ]
    ]
  ],
  [
    "ON",
    [
      [
        "=",
        [
          [
            "-LITERAL",
            [
              "[permission].[id]"
            ]
          ],
          [
            "-LITERAL",
            [
              "[role_permissions].[permission_id]"
            ]
          ]
        ]
      ]
    ]
  ],
  [
    "JOIN",
    [
      [
        "-MISC",
        [
          [
            "-LITERAL",
            [
              "[permissionscreens]"
            ]
          ],
          [
            "-LITERAL",
            [
              "[permission_screens]"
            ]
          ]
        ]
      ]
    ]
  ],
  [
    "ON",
    [
      [
        "=",
        [
          [
            "-LITERAL",
            [
              "[permission_screens].[permission_id]"
            ]
          ],
          [
            "-LITERAL",
            [
              "[permission].[id]"
            ]
          ]
        ]
      ]
    ]
  ],
  [
    "JOIN",
    [
      [
        "-MISC",
        [
          [
            "-LITERAL",
            [
              "[screens]"
            ]
          ],
          [
            "-LITERAL",
            [
              "[screen]"
            ]
          ]
        ]
      ]
    ]
  ],
  [
    "ON",
    [
      [
        "=",
        [
          [
            "-LITERAL",
            [
              "[screen].[id]"
            ]
          ],
          [
            "-LITERAL",
            [
              "[permission_screens].[screen_id]"
            ]
          ]
        ]
      ]
    ]
  ],
  [
    "WHERE",
    [
      [
        "-PAREN",
        [
          [
            "=",
            [
              [
                "-LITERAL",
                [
                  "[me].[user_id]"
                ]
              ],
              [
                "-PLACEHOLDER",
                [
                  "?"
                ]
              ]
            ]
          ]
        ]
      ]
    ]
  ],
  [
    "GROUP BY",
    [
      [
        "-LIST",
        [
          [
            "-LITERAL",
            [
              "[screen].[id]"
            ]
          ],
          [
            "-LITERAL",
            [
              "[screen].[name]"
            ]
          ],
          [
            "-LITERAL",
            [
              "[screen].[section_id]"
            ]
          ],
          [
            "-LITERAL",
            [
              "[screen].[xtype]"
            ]
          ]
        ]
      ]
    ]
  ]
], 'real life statement 1 parsed correctly');

is_deeply($sqlat->parse("SELECT x, y FROM foo WHERE x IN (?, ?, ?, ?)"), [
  [
    "SELECT",
    [
      [
        "-LIST",
        [
          [
            "-LITERAL",
            [
              "x"
            ]
          ],
          [
            "-LITERAL",
            [
              "y"
            ]
          ]
        ]
      ]
    ]
  ],
  [
    "FROM",
    [
      [
        "-LITERAL",
        [
          "foo"
        ]
      ]
    ]
  ],
  [
    "WHERE",
    [
      [
        "IN",
        [
          [
            "-LITERAL",
            [
              "x"
            ]
          ],
          [
            "-PAREN",
            [
              [
                "-LIST",
                [
                  [
                    "-PLACEHOLDER",
                    [
                      "?"
                    ]
                  ],
                  [
                    "-PLACEHOLDER",
                    [
                      "?"
                    ]
                  ],
                  [
                    "-PLACEHOLDER",
                    [
                      "?"
                    ]
                  ],
                  [
                    "-PLACEHOLDER",
                    [
                      "?"
                    ]
                  ]
                ]
              ]
            ]
          ]
        ]
      ]
    ]
  ]
], 'Lists parsed correctly');

is_deeply($sqlat->parse("SELECT * * FROM (SELECT *, FROM foobar baz buzz) foo bar WHERE NOT NOT NOT EXISTS (SELECT 'cr,ap') AND foo.a = ? and not (foo.b LIKE 'station') and x = y and a = b and GROUP BY , ORDER BY x x1 x2 y asc, max(y) desc x z desc"), [
  [
    "SELECT",
    [
      [
        "*",
        [
          [
            "-LITERAL",
            [
              "*"
            ]
          ]
        ]
      ]
    ]
  ],
  [
    "FROM",
    [
      [
        "-MISC",
        [
          [
            "-PAREN",
            [
              [
                "SELECT",
                [
                  [
                    "-LIST",
                    [
                      [
                        "-LITERAL",
                        [
                          "*"
                        ]
                      ],
                      []
                    ]
                  ]
                ]
              ],
              [
                "FROM",
                [
                  [
                    "-MISC",
                    [
                      [
                        "-LITERAL",
                        [
                          "foobar"
                        ]
                      ],
                      [
                        "-LITERAL",
                        [
                          "baz"
                        ]
                      ],
                      [
                        "-LITERAL",
                        [
                          "buzz"
                        ]
                      ]
                    ]
                  ]
                ]
              ]
            ]
          ],
          [
            "-LITERAL",
            [
              "foo"
            ]
          ],
          [
            "-LITERAL",
            [
              "bar"
            ]
          ]
        ]
      ]
    ]
  ],
  [
    "WHERE",
    [
      [
        "AND",
        [
          [
            "NOT",
            []
          ],
          [
            "NOT",
            []
          ],
          [
            "NOT EXISTS",
            [
              [
                "-PAREN",
                [
                  [
                    "SELECT",
                    [
                      [
                        "-LIST",
                        [
                          [
                            "-LITERAL",
                            [
                              "'cr"
                            ]
                          ],
                          [
                            "-LITERAL",
                            [
                              "ap'"
                            ]
                          ]
                        ]
                      ]
                    ]
                  ]
                ]
              ]
            ]
          ],
          [
            "=",
            [
              [
                "-LITERAL",
                [
                  "foo.a"
                ]
              ],
              [
                "-PLACEHOLDER",
                [
                  "?"
                ]
              ]
            ]
          ],
          [
            "NOT",
            [
              [
                "-PAREN",
                [
                  [
                    "LIKE",
                    [
                      [
                        "-LITERAL",
                        [
                          "foo.b"
                        ]
                      ],
                      [
                        "-LITERAL",
                        [
                          "'station'"
                        ]
                      ]
                    ]
                  ]
                ]
              ]
            ]
          ],
          [
            "=",
            [
              [
                "-LITERAL",
                [
                  "x"
                ]
              ],
              [
                "-LITERAL",
                [
                  "y"
                ]
              ]
            ]
          ],
          [
            "=",
            [
              [
                "-LITERAL",
                [
                  "a"
                ]
              ],
              [
                "-LITERAL",
                [
                  "b"
                ]
              ]
            ]
          ]
        ]
      ]
    ]
  ],
  [
    "GROUP BY",
    [
      [
        "-LIST",
        [
          [],
          []
        ]
      ]
    ]
  ],
  [
    "ORDER BY",
    [
      [
        "-LIST",
        [
          [
            "-MISC",
            [
              [
                "-LITERAL",
                [
                  "x"
                ]
              ],
              [
                "-LITERAL",
                [
                  "x1"
                ]
              ],
              [
                "-LITERAL",
                [
                  "x2"
                ]
              ],
              [
                "-LITERAL",
                [
                  "y"
                ]
              ],
              [
                "-LITERAL",
                [
                  "asc"
                ]
              ]
            ]
          ],
          [
            "max",
            [
              [
                "-MISC",
                [
                  [
                    "-DESC",
                    [
                      [
                        "-PAREN",
                        [
                          [
                            "-LITERAL",
                            [
                              "y"
                            ]
                          ]
                        ]
                      ]
                    ]
                  ],
                  [
                    "-LITERAL",
                    [
                      "x"
                    ]
                  ],
                  [
                    "-LITERAL",
                    [
                      "z"
                    ]
                  ],
                  [
                    "-LITERAL",
                    [
                      "desc"
                    ]
                  ]
                ]
              ]
            ]
          ]
        ]
      ]
    ]
  ]
], 'Deliberately malformed SQL parsed "correctly"');


# test for recursion warnings on huge selectors
my @lst = ('AA' .. 'zz');
#@lst = ('AAA' .. 'zzz'); # if you really want to wait a while
warnings_are {
  my $sql = sprintf 'SELECT %s FROM foo', join (', ',  (map { qq|( "$_" )| } @lst), (map { qq|"$_"| } @lst), (map { qq|"$_", ( "$_" )| } @lst) );
  my $tree = $sqlat->parse($sql);

  is_deeply( $tree, [
    [
      "SELECT",
      [
        [
          "-LIST",
          [
            (map { [ -PAREN => [ [ -LITERAL => [ qq|"$_"| ] ] ] ] } @lst),
            (map { [ -LITERAL => [ qq|"$_"| ] ] } @lst),
            (map { [ -LITERAL => [ qq|"$_"| ] ], [ -PAREN => [ [ -LITERAL => [ qq|"$_"| ] ] ] ] } @lst),
          ]
        ]
      ]
    ],
    [
      "FROM",
      [
        [
          "-LITERAL",
          [
            "foo"
          ]
        ]
      ]
    ]
  ], 'long list parsed correctly');

  is( $sqlat->unparse($tree), $sql, 'roundtrip ok');
} [], 'no recursion warnings on insane SQL';

done_testing;
Commit	Line	Data
d49e5323	1	use strict;
	2	use warnings;
	3
3be357b0	4	use Test::More;
0f9a26cb	5	use Test::Warn;
d49e5323	6	use SQL::Abstract::Tree;
d49e5323	7
408cbda3	8	my $sqlat = SQL::Abstract::Tree->new;
6f2a5b66	9	is_deeply($sqlat->parse("SELECT a, b., FROM foo WHERE foo.a =1 and foo.b LIKE 'station'"), [
958f1ce4	10	[
6f2a5b66	11	"SELECT",
958f1ce4	12	[
958f1ce4	13	[
6f2a5b66	14	"-LIST",
958f1ce4	15	[
958f1ce4	16	[
6f2a5b66	17	"-LITERAL",
958f1ce4	18	[
6f2a5b66	19	"a"
	20	]
	21	],
	22	[
	23	"-LITERAL",
958f1ce4	24	[
6f2a5b66	25	"b.*"
	26	]
	27	],
	28	[
	29	"-LITERAL",
958f1ce4	30	[
6f2a5b66	31	"*"
958f1ce4	32	]
	33	]
	34	]
	35	]
6f2a5b66	36	]
	37	],
	38	[
	39	"FROM",
958f1ce4	40	[
958f1ce4	41	[
6f2a5b66	42	"-LITERAL",
958f1ce4	43	[
6f2a5b66	44	"foo"
958f1ce4	45	]
	46	]
	47	]
	48	],
	49	[
	50	"WHERE",
	51	[
	52	[
	53	"AND",
	54	[
	55	[
	56	"=",
	57	[
	58	[
6f2a5b66	59	"-LITERAL",
958f1ce4	60	[
	61	"foo.a"
	62	]
	63	],
	64	[
6f2a5b66	65	"-LITERAL",
958f1ce4	66	[
	67	1
	68	]
	69	]
	70	]
	71	],
	72	[
	73	"LIKE",
	74	[
	75	[
6f2a5b66	76	"-LITERAL",
958f1ce4	77	[
	78	"foo.b"
	79	]
	80	],
	81	[
6f2a5b66	82	"-LITERAL",
958f1ce4	83	[
	84	"'station'"
	85	]
	86	]
	87	]
	88	]
	89	]
	90	]
	91	]
	92	]
	93	], 'simple statement parsed correctly');
54750c7a	94
6f2a5b66	95	is_deeply($sqlat->parse( "SELECT * FROM (SELECT * FROM foobar) foo WHERE foo.a =1 and foo.b LIKE 'station'"), [
958f1ce4	96	[
6f2a5b66	97	"SELECT",
958f1ce4	98	[
958f1ce4	99	[
6f2a5b66	100	"-LITERAL",
958f1ce4	101	[
6f2a5b66	102	"*"
958f1ce4	103	]
958f1ce4	104	]
6f2a5b66	105	]
	106	],
	107	[
	108	"FROM",
958f1ce4	109	[
958f1ce4	110	[
6f2a5b66	111	"-MISC",
958f1ce4	112	[
958f1ce4	113	[
6f2a5b66	114	"-PAREN",
958f1ce4	115	[
	116	[
	117	"SELECT",
	118	[
	119	[
6f2a5b66	120	"-LITERAL",
958f1ce4	121	[
	122	"*"
	123	]
	124	]
	125	]
	126	],
	127	[
	128	"FROM",
	129	[
	130	[
6f2a5b66	131	"-LITERAL",
958f1ce4	132	[
	133	"foobar"
	134	]
	135	]
	136	]
	137	]
	138	]
6f2a5b66	139	],
	140	[
	141	"-LITERAL",
	142	[
	143	"foo"
	144	]
958f1ce4	145	]
	146	]
	147	]
	148	]
	149	],
	150	[
	151	"WHERE",
	152	[
	153	[
	154	"AND",
	155	[
	156	[
	157	"=",
	158	[
	159	[
6f2a5b66	160	"-LITERAL",
958f1ce4	161	[
	162	"foo.a"
	163	]
	164	],
	165	[
6f2a5b66	166	"-LITERAL",
958f1ce4	167	[
	168	1
	169	]
	170	]
	171	]
	172	],
	173	[
	174	"LIKE",
	175	[
	176	[
6f2a5b66	177	"-LITERAL",
958f1ce4	178	[
	179	"foo.b"
	180	]
	181	],
	182	[
6f2a5b66	183	"-LITERAL",
958f1ce4	184	[
	185	"'station'"
	186	]
	187	]
	188	]
	189	]
	190	]
	191	]
	192	]
	193	]
	194	], 'subquery statement parsed correctly');
54750c7a	195
6f2a5b66	196	is_deeply($sqlat->parse( "SELECT [screen].[id], [screen].[name], [screen].[section_id], [screen].[xtype] FROM [users_roles] [me] JOIN [roles] [role] ON [role].[id] = [me].[role_id] JOIN [roles_permissions] [role_permissions] ON [role_permissions].[role_id] = [role].[id] JOIN [permissions] [permission] ON [permission].[id] = [role_permissions].[permission_id] JOIN [permissionscreens] [permission_screens] ON [permission_screens].[permission_id] = [permission].[id] JOIN [screens] [screen] ON [screen].[id] = [permission_screens].[screen_id] WHERE ( [me].[user_id] = ? ) GROUP BY [screen].[id], [screen].[name], [screen].[section_id], [screen].[xtype]"), [
958f1ce4	197	[
6f2a5b66	198	"SELECT",
958f1ce4	199	[
958f1ce4	200	[
6f2a5b66	201	"-LIST",
958f1ce4	202	[
958f1ce4	203	[
6f2a5b66	204	"-LITERAL",
	205	[
	206	"[screen].[id]"
	207	]
	208	],
	209	[
	210	"-LITERAL",
	211	[
	212	"[screen].[name]"
	213	]
	214	],
	215	[
	216	"-LITERAL",
	217	[
	218	"[screen].[section_id]"
	219	]
	220	],
	221	[
	222	"-LITERAL",
	223	[
	224	"[screen].[xtype]"
	225	]
958f1ce4	226	]
	227	]
	228	]
6f2a5b66	229	]
	230	],
	231	[
	232	"FROM",
958f1ce4	233	[
958f1ce4	234	[
6f2a5b66	235	"-MISC",
958f1ce4	236	[
958f1ce4	237	[
6f2a5b66	238	"-LITERAL",
	239	[
	240	"[users_roles]"
	241	]
	242	],
	243	[
	244	"-LITERAL",
	245	[
	246	"[me]"
	247	]
958f1ce4	248	]
	249	]
	250	]
	251	]
	252	],
	253	[
6f2a5b66	254	"JOIN",
958f1ce4	255	[
958f1ce4	256	[
6f2a5b66	257	"-MISC",
958f1ce4	258	[
958f1ce4	259	[
6f2a5b66	260	"-LITERAL",
958f1ce4	261	[
6f2a5b66	262	"[roles]"
958f1ce4	263	]
	264	],
	265	[
6f2a5b66	266	"-LITERAL",
958f1ce4	267	[
6f2a5b66	268	"[role]"
958f1ce4	269	]
	270	]
	271	]
	272	]
	273	]
6f2a5b66	274	],
958f1ce4	275	[
6f2a5b66	276	"ON",
958f1ce4	277	[
958f1ce4	278	[
6f2a5b66	279	"=",
958f1ce4	280	[
958f1ce4	281	[
6f2a5b66	282	"-LITERAL",
958f1ce4	283	[
6f2a5b66	284	"[role].[id]"
958f1ce4	285	]
	286	],
	287	[
6f2a5b66	288	"-LITERAL",
958f1ce4	289	[
6f2a5b66	290	"[me].[role_id]"
958f1ce4	291	]
958f1ce4	292	]
6f2a5b66	293	]
	294	]
	295	]
	296	],
	297	[
	298	"JOIN",
	299	[
	300	[
	301	"-MISC",
958f1ce4	302	[
958f1ce4	303	[
6f2a5b66	304	"-LITERAL",
958f1ce4	305	[
6f2a5b66	306	"[roles_permissions]"
958f1ce4	307	]
6f2a5b66	308	],
958f1ce4	309	[
6f2a5b66	310	"-LITERAL",
958f1ce4	311	[
6f2a5b66	312	"[role_permissions]"
958f1ce4	313	]
	314	]
	315	]
	316	]
6f2a5b66	317	]
	318	],
	319	[
	320	"ON",
958f1ce4	321	[
958f1ce4	322	[
6f2a5b66	323	"=",
958f1ce4	324	[
958f1ce4	325	[
6f2a5b66	326	"-LITERAL",
958f1ce4	327	[
6f2a5b66	328	"[role_permissions].[role_id]"
	329	]
	330	],
	331	[
	332	"-LITERAL",
	333	[
	334	"[role].[id]"
958f1ce4	335	]
	336	]
	337	]
	338	]
	339	]
	340	],
	341	[
6f2a5b66	342	"JOIN",
958f1ce4	343	[
958f1ce4	344	[
6f2a5b66	345	"-MISC",
958f1ce4	346	[
958f1ce4	347	[
6f2a5b66	348	"-LITERAL",
958f1ce4	349	[
6f2a5b66	350	"[permissions]"
958f1ce4	351	]
	352	],
	353	[
6f2a5b66	354	"-LITERAL",
	355	[
	356	"[permission]"
	357	]
	358	]
	359	]
	360	]
	361	]
	362	],
	363	[
	364	"ON",
	365	[
	366	[
	367	"=",
	368	[
	369	[
	370	"-LITERAL",
	371	[
	372	"[permission].[id]"
	373	]
	374	],
	375	[
	376	"-LITERAL",
	377	[
	378	"[role_permissions].[permission_id]"
	379	]
	380	]
	381	]
	382	]
	383	]
	384	],
	385	[
	386	"JOIN",
	387	[
	388	[
	389	"-MISC",
	390	[
	391	[
	392	"-LITERAL",
	393	[
	394	"[permissionscreens]"
	395	]
	396	],
	397	[
	398	"-LITERAL",
	399	[
	400	"[permission_screens]"
	401	]
	402	]
	403	]
	404	]
	405	]
	406	],
	407	[
	408	"ON",
	409	[
	410	[
	411	"=",
	412	[
	413	[
	414	"-LITERAL",
	415	[
	416	"[permission_screens].[permission_id]"
	417	]
418	],
419	[
420	"-LITERAL",
421	[
422	"[permission].[id]"
423	]
424	]
425	]
426	]
427	]
428	],
429	[
430	"JOIN",
431	[
432	[
433	"-MISC",
434	[
435	[
436	"-LITERAL",
437	[
438	"[screens]"
439	]
440	],
441	[
442	"-LITERAL",
443	[
444	"[screen]"
445	]
446	]
447	]
448	]
449	]
450	],
451	[
452	"ON",
453	[
454	[
455	"=",
456	[
457	[
458	"-LITERAL",
459	[
460	"[screen].[id]"
461	]
462	],
463	[
464	"-LITERAL",
465	[
466	"[permission_screens].[screen_id]"
467	]
468	]
469	]
470	]
471	]
472	],
473	[
474	"WHERE",
475	[
476	[
477	"-PAREN",
478	[
479	[
480	"=",
481	[
482	[
483	"-LITERAL",
484	[
485	"[me].[user_id]"
486	]
487	],
488	[
489	"-PLACEHOLDER",
490	[
491	"?"
492	]
493	]
494	]
495	]
496	]
497	]
498	]
499	],
500	[
501	"GROUP BY",
502	[
503	[
504	"-LIST",
505	[
506	[
507	"-LITERAL",
508	[
509	"[screen].[id]"
510	]
511	],
512	[
513	"-LITERAL",
958f1ce4	514	[
	515	"[screen].[name]"
	516	]
	517	],
	518	[
6f2a5b66	519	"-LITERAL",
958f1ce4	520	[
	521	"[screen].[section_id]"
	522	]
	523	],
	524	[
6f2a5b66	525	"-LITERAL",
958f1ce4	526	[
	527	"[screen].[xtype]"
	528	]
	529	]
	530	]
	531	]
	532	]
	533	]
	534	], 'real life statement 1 parsed correctly');
1bb3956e	535
6f2a5b66	536	is_deeply($sqlat->parse("SELECT x, y FROM foo WHERE x IN (?, ?, ?, ?)"), [
958f1ce4	537	[
6f2a5b66	538	"SELECT",
958f1ce4	539	[
958f1ce4	540	[
6f2a5b66	541	"-LIST",
958f1ce4	542	[
958f1ce4	543	[
6f2a5b66	544	"-LITERAL",
958f1ce4	545	[
6f2a5b66	546	"x"
	547	]
	548	],
	549	[
	550	"-LITERAL",
958f1ce4	551	[
6f2a5b66	552	"y"
958f1ce4	553	]
	554	]
	555	]
	556	]
6f2a5b66	557	]
	558	],
	559	[
	560	"FROM",
958f1ce4	561	[
958f1ce4	562	[
6f2a5b66	563	"-LITERAL",
958f1ce4	564	[
6f2a5b66	565	"foo"
958f1ce4	566	]
	567	]
	568	]
	569	],
	570	[
	571	"WHERE",
	572	[
	573	[
	574	"IN",
	575	[
	576	[
6f2a5b66	577	"-LITERAL",
958f1ce4	578	[
	579	"x"
	580	]
	581	],
	582	[
6f2a5b66	583	"-PAREN",
958f1ce4	584	[
958f1ce4	585	[
6f2a5b66	586	"-LIST",
958f1ce4	587	[
958f1ce4	588	[
6f2a5b66	589	"-PLACEHOLDER",
958f1ce4	590	[
	591	"?"
	592	]
	593	],
	594	[
6f2a5b66	595	"-PLACEHOLDER",
958f1ce4	596	[
	597	"?"
	598	]
	599	],
	600	[
6f2a5b66	601	"-PLACEHOLDER",
958f1ce4	602	[
	603	"?"
	604	]
	605	],
	606	[
6f2a5b66	607	"-PLACEHOLDER",
958f1ce4	608	[
	609	"?"
	610	]
	611	]
	612	]
	613	]
	614	]
	615	]
	616	]
	617	]
	618	]
	619	]
	620	], 'Lists parsed correctly');
7f2dd81e	621
6f2a5b66	622	is_deeply($sqlat->parse("SELECT * * FROM (SELECT *, FROM foobar baz buzz) foo bar WHERE NOT NOT NOT EXISTS (SELECT 'cr,ap') AND foo.a = ? and not (foo.b LIKE 'station') and x = y and a = b and GROUP BY , ORDER BY x x1 x2 y asc, max(y) desc x z desc"), [
	623	[
	624	"SELECT",
	625	[
	626	[
	627	"*",
	628	[
	629	[
	630	"-LITERAL",
	631	[
	632	"*"
	633	]
	634	]
	635	]
	636	]
	637	]
	638	],
	639	[
	640	"FROM",
	641	[
	642	[
	643	"-MISC",
	644	[
	645	[
	646	"-PAREN",
	647	[
	648	[
	649	"SELECT",
	650	[
	651	[
	652	"-LIST",
	653	[
	654	[
	655	"-LITERAL",
	656	[
	657	"*"
	658	]
	659	],
	660	[]
	661	]
	662	]
	663	]
	664	],
	665	[
	666	"FROM",
	667	[
	668	[
	669	"-MISC",
	670	[
	671	[
	672	"-LITERAL",
	673	[
	674	"foobar"
	675	]
	676	],
	677	[
	678	"-LITERAL",
	679	[
	680	"baz"
	681	]
	682	],
	683	[
	684	"-LITERAL",
	685	[
686	"buzz"
687	]
688	]
689	]
690	]
691	]
692	]
693	]
694	],
695	[
696	"-LITERAL",
697	[
698	"foo"
699	]
700	],
701	[
702	"-LITERAL",
703	[
704	"bar"
705	]
706	]
707	]
708	]
709	]
710	],
711	[
712	"WHERE",
713	[
714	[
715	"AND",
716	[
717	[
718	"NOT",
719	[]
720	],
721	[
722	"NOT",
723	[]
724	],
725	[
726	"NOT EXISTS",
727	[
728	[
729	"-PAREN",
730	[
731	[
732	"SELECT",
733	[
734	[
735	"-LIST",
736	[
737	[
738	"-LITERAL",
739	[
740	"'cr"
741	]
742	],
743	[
744	"-LITERAL",
745	[
746	"ap'"
747	]
748	]
749	]
750	]
751	]
752	]
753	]
754	]
755	]
756	],
757	[
758	"=",
759	[
760	[
761	"-LITERAL",
762	[
763	"foo.a"
764	]
765	],
766	[
767	"-PLACEHOLDER",
768	[
769	"?"
770	]
771	]
772	]
773	],
774	[
775	"NOT",
776	[
777	[
778	"-PAREN",
779	[
780	[
781	"LIKE",
782	[
783	[
784	"-LITERAL",
785	[
786	"foo.b"
787	]
788	],
789	[
790	"-LITERAL",
791	[
792	"'station'"
793	]
794	]
795	]
796	]
797	]
798	]
799	]
800	],
801	[
802	"=",
803	[
804	[
805	"-LITERAL",
806	[
807	"x"
808	]
809	],
810	[
811	"-LITERAL",
812	[
813	"y"
814	]
815	]
816	]
817	],
818	[
819	"=",
820	[
821	[
822	"-LITERAL",
823	[
824	"a"
825	]
826	],
827	[
828	"-LITERAL",
829	[
830	"b"
831	]
832	]
833	]
834	]
835	]
836	]
837	]
838	],
839	[
840	"GROUP BY",
841	[
842	[
843	"-LIST",
844	[
845	[],
846	[]
847	]
848	]
849	]
850	],
851	[
852	"ORDER BY",
853	[
854	[
855	"-LIST",
856	[
857	[
858	"-MISC",
859	[
860	[
861	"-LITERAL",
862	[
863	"x"
864	]
865	],
866	[
867	"-LITERAL",
868	[
869	"x1"
870	]
871	],
872	[
873	"-LITERAL",
874	[
875	"x2"
876	]
877	],
878	[
879	"-LITERAL",
880	[
881	"y"
882	]
883	],
884	[
885	"-LITERAL",
886	[
887	"asc"
888	]
889	]
890	]
891	],
892	[
893	"max",
894	[
895	[
896	"-MISC",
897	[
898	[
899	"-DESC",
900	[
901	[
902	"-PAREN",
903	[
904	[
905	"-LITERAL",
906	[
907	"y"
908	]
909	]
910	]
911	]
912	]
913	],
914	[
915	"-LITERAL",
916	[
917	"x"
918	]
919	],
920	[
921	"-LITERAL",
922	[
923	"z"
924	]
925	],
926	[
927	"-LITERAL",
928	[
929	"desc"
930	]
931	]
932	]
933	]
934	]
935	]
936	]
937	]
938	]
939	]
940	], 'Deliberately malformed SQL parsed "correctly"');
941
942
0f9a26cb	943	# test for recursion warnings on huge selectors
ad591616	944	my @lst = ('AA' .. 'zz');
ad591616	945	#@lst = ('AAA' .. 'zzz'); # if you really want to wait a while
0f9a26cb	946	warnings_are {
ad591616	947	my $sql = sprintf 'SELECT %s FROM foo', join (', ', (map { qq\|( "$_" )\| } @lst), (map { qq\|"$_"\| } @lst), (map { qq\|"$_", ( "$_" )\| } @lst) );
0f9a26cb	948	my $tree = $sqlat->parse($sql);
	949
	950	is_deeply( $tree, [
	951	[
	952	"SELECT",
	953	[
	954	[
6f2a5b66	955	"-LIST",
0f9a26cb	956	[
ad591616	957	(map { [ -PAREN => [ [ -LITERAL => [ qq\|"$_"\| ] ] ] ] } @lst),
6f2a5b66	958	(map { [ -LITERAL => [ qq\|"$_"\| ] ] } @lst),
ad591616	959	(map { [ -LITERAL => [ qq\|"$_"\| ] ], [ -PAREN => [ [ -LITERAL => [ qq\|"$_"\| ] ] ] ] } @lst),
0f9a26cb	960	]
	961	]
	962	]
	963	],
	964	[
	965	"FROM",
	966	[
	967	[
6f2a5b66	968	"-LITERAL",
0f9a26cb	969	[
	970	"foo"
	971	]
	972	]
	973	]
	974	]
	975	], 'long list parsed correctly');
	976
	977	is( $sqlat->unparse($tree), $sql, 'roundtrip ok');
	978	} [], 'no recursion warnings on insane SQL';
	979
3be357b0	980	done_testing;