[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 foo FROM bar ORDER BY x + ? DESC, oomph, y - ? DESC, unf, baz.g / ? ASC, buzz * 0 DESC, foo LIKE ? DESC, ickk ASC'), [
  [
    "SELECT",
    [
      [
        "-LITERAL",
        [
          "foo"
        ]
      ]
    ]
  ],
  [
    "FROM",
    [
      [
        "-LITERAL",
        [
          "bar"
        ]
      ]
    ]
  ],
  [
    "ORDER BY",
    [
      [
        "-LIST",
        [
          [
            "-DESC",
            [
              [
                "-MISC",
                [
                  [
                    "-LITERAL",
                    [
                      "x"
                    ]
                  ],
                  [
                    "-LITERAL",
                    [
                      "+"
                    ]
                  ],
                  [
                    "-PLACEHOLDER",
                    [
                      "?"
                    ]
                  ]
                ]
              ],
            ]
          ],
          [
            "-LITERAL",
            [
              "oomph"
            ]
          ],
          [
            "-DESC",
            [
              [
                "-MISC",
                [
                  [
                    "-LITERAL",
                    [
                      "y"
                    ]
                  ],
                  [
                    "-LITERAL",
                    [
                      "-"
                    ]
                  ],
                  [
                    "-PLACEHOLDER",
                    [
                      "?"
                    ]
                  ],
                ]
              ],
            ]
          ],
          [
            "-LITERAL",
            [
              "unf"
            ]
          ],
          [
            "-ASC",
            [
              [
                "-MISC",
                [
                  [
                    "-LITERAL",
                    [
                      "baz.g"
                    ]
                  ],
                  [
                    "-LITERAL",
                    [
                      "/"
                    ]
                  ],
                  [
                    "-PLACEHOLDER",
                    [
                      "?"
                    ]
                  ],
                ]
              ],
            ]
          ],
          [
            "-DESC",
            [
              [
                "-MISC",
                [
                  [
                    "-LITERAL",
                    [
                      "buzz"
                    ]
                  ],
                  [
                    "-LITERAL",
                    [
                      "*"
                    ]
                  ],
                  [
                    "-LITERAL",
                    [
                      0
                    ]
                  ]
                ]
              ]
            ]
          ],
          [
            "-DESC",
            [
              [
                "LIKE",
                [
                  [
                    "-LITERAL",
                    [
                      "foo"
                    ]
                  ],
                  [
                    "-PLACEHOLDER",
                    [
                      "?"
                    ]
                  ],
                ],
              ],
            ]
          ],
          [
            "-ASC",
            [
              [
                "-LITERAL",
                [
                  "ickk"
                ]
              ]
            ]
          ]
        ]
      ]
    ]
  ]
], 'Crazy ORDER BY parsed correctly');

is_deeply( $sqlat->parse("META SELECT * * FROM (SELECT *, FROM foobar baz buzz) foo bar WHERE NOT NOT NOT EXISTS (SELECT 'cr,ap') AND foo.a = ? STUFF moar(stuff) and not (foo.b LIKE 'station') and x = y and z in ((1, 2)) and a = b and GROUP BY , ORDER BY x x1 x2 y asc, max(y) desc x z desc"), [
  [
    "-LITERAL",
    [
      "META"
    ]
  ],
  [
    "SELECT",
    [
      [
        "-MISC",
        [
          [
            "-LITERAL",
            [
              "*"
            ]
          ],
          [
            "-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'"
                            ]
                          ]
                        ]
                      ]
                    ]
                  ]
                ]
              ]
            ]
          ],
          [
            "-MISC",
            [
              [
                "=",
                [
                  [
                    "-LITERAL",
                    [
                      "foo.a"
                    ]
                  ],
                  [
                    "-PLACEHOLDER",
                    [
                      "?"
                    ]
                  ],
                ],
              ],
              [
                "-LITERAL",
                [
                  "STUFF"
                ]
              ],
            ],
          ],
          [
            'moar',
            [
              [
                '-PAREN',
                [
                  [
                    '-LITERAL',
                    [
                      'stuff'
                    ]
                 ]
                ]
              ]
            ]
          ],
          [
            "NOT",
            [
              [
                "-PAREN",
                [
                  [
                    "LIKE",
                    [
                      [
                        "-LITERAL",
                        [
                          "foo.b"
                        ]
                      ],
                      [
                        "-LITERAL",
                        [
                          "'station'"
                        ]
                      ]
                    ]
                  ]
                ]
              ]
            ]
          ],
          [
            "=",
            [
              [
                "-LITERAL",
                [
                  "x"
                ]
              ],
              [
                "-LITERAL",
                [
                  "y"
                ]
              ]
            ]
          ],
          [
            'IN',
            [
              [
                '-LITERAL',
                [
                  'z',
                ],
              ],
              [
                '-PAREN',
                [
                  [
                    '-PAREN',
                    [
                      [
                        '-LIST',
                        [
                          [
                            '-LITERAL',
                            [
                              '1'
                            ]
                          ],
                          [
                            '-LITERAL',
                            [
                              '2'
                            ]
                          ],
                        ],
                      ],
                    ],
                  ],
                ],
              ],
            ],
          ],
          [
            "=",
            [
              [
                "-LITERAL",
                [
                  "a"
                ]
              ],
              [
                "-LITERAL",
                [
                  "b"
                ]
              ]
            ]
          ]
        ]
      ]
    ]
  ],
  [
    "GROUP BY",
    [
      [
        "-LIST",
        [
          [],
          []
        ]
      ]
    ]
  ],
  [
    "ORDER BY",
    [
      [
        "-LIST",
        [
          [
            "-ASC",
            [
              [
                "-MISC",
                [
                  [
                    "-LITERAL",
                    [
                      "x"
                    ]
                  ],
                  [
                    "-LITERAL",
                    [
                      "x1"
                    ]
                  ],
                  [
                    "-LITERAL",
                    [
                      "x2"
                    ]
                  ],
                  [
                    "-LITERAL",
                    [
                      "y"
                    ]
                  ]
                ]
              ],
            ],
          ],
          [
                "-DESC",
                [
                  [
                    "-MISC",
                    [
                      [
                        "-DESC",
                        [
                          [
                            "max",
                            [
                              [
                                "-PAREN",
                                [
                                  [
                                    "-LITERAL",
                                    [
                                      "y"
                                    ]
                                  ]
                                ]
                              ]
                            ],
                          ]
                        ]
                      ],
                      [
                        "-LITERAL",
                        [
                          "x"
                        ]
                      ],
                  [
                    "-LITERAL",
                    [
                      "z"
                    ]
                  ]
                ]
              ]
            ]
          ]
        ]
      ]
    ]
  ]
], '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
c84a4321	622	is_deeply($sqlat->parse('SELECT foo FROM bar ORDER BY x + ? DESC, oomph, y - ? DESC, unf, baz.g / ? ASC, buzz * 0 DESC, foo LIKE ? DESC, ickk ASC'), [
1ec9b9e3	623	[
	624	"SELECT",
	625	[
	626	[
	627	"-LITERAL",
	628	[
	629	"foo"
	630	]
	631	]
	632	]
	633	],
	634	[
	635	"FROM",
	636	[
	637	[
	638	"-LITERAL",
	639	[
	640	"bar"
	641	]
	642	]
	643	]
	644	],
	645	[
	646	"ORDER BY",
	647	[
	648	[
	649	"-LIST",
	650	[
	651	[
	652	"-DESC",
	653	[
	654	[
	655	"-MISC",
	656	[
	657	[
	658	"-LITERAL",
	659	[
	660	"x"
	661	]
	662	],
	663	[
	664	"-LITERAL",
	665	[
	666	"+"
	667	]
b4085a1a	668	],
	669	[
	670	"-PLACEHOLDER",
	671	[
	672	"?"
	673	]
1ec9b9e3	674	]
	675	]
	676	],
1ec9b9e3	677	]
	678	],
	679	[
	680	"-LITERAL",
	681	[
	682	"oomph"
	683	]
	684	],
	685	[
	686	"-DESC",
	687	[
	688	[
	689	"-MISC",
	690	[
	691	[
	692	"-LITERAL",
	693	[
	694	"y"
	695	]
	696	],
	697	[
	698	"-LITERAL",
	699	[
	700	"-"
	701	]
b4085a1a	702	],
	703	[
	704	"-PLACEHOLDER",
	705	[
	706	"?"
	707	]
	708	],
1ec9b9e3	709	]
1ec9b9e3	710	],
1ec9b9e3	711	]
	712	],
	713	[
	714	"-LITERAL",
	715	[
	716	"unf"
	717	]
	718	],
	719	[
	720	"-ASC",
	721	[
	722	[
	723	"-MISC",
	724	[
	725	[
	726	"-LITERAL",
	727	[
	728	"baz.g"
	729	]
	730	],
	731	[
	732	"-LITERAL",
	733	[
	734	"/"
	735	]
b4085a1a	736	],
	737	[
	738	"-PLACEHOLDER",
	739	[
	740	"?"
	741	]
	742	],
1ec9b9e3	743	]
1ec9b9e3	744	],
1ec9b9e3	745	]
	746	],
	747	[
	748	"-DESC",
	749	[
	750	[
	751	"-MISC",
	752	[
	753	[
	754	"-LITERAL",
	755	[
	756	"buzz"
	757	]
	758	],
	759	[
	760	"-LITERAL",
	761	[
	762	"*"
	763	]
	764	],
	765	[
	766	"-LITERAL",
	767	[
	768	0
	769	]
	770	]
	771	]
	772	]
	773	]
	774	],
	775	[
	776	"-DESC",
	777	[
	778	[
c84a4321	779	"LIKE",
1ec9b9e3	780	[
c84a4321	781	[
	782	"-LITERAL",
	783	[
	784	"foo"
	785	]
	786	],
	787	[
	788	"-PLACEHOLDER",
	789	[
	790	"?"
	791	]
	792	],
	793	],
	794	],
1ec9b9e3	795	]
	796	],
	797	[
	798	"-ASC",
	799	[
	800	[
	801	"-LITERAL",
	802	[
	803	"ickk"
	804	]
	805	]
	806	]
	807	]
	808	]
	809	]
	810	]
	811	]
	812	], 'Crazy ORDER BY parsed correctly');
	813
7d273452	814	is_deeply( $sqlat->parse("META SELECT * * FROM (SELECT *, FROM foobar baz buzz) foo bar WHERE NOT NOT NOT EXISTS (SELECT 'cr,ap') AND foo.a = ? STUFF moar(stuff) and not (foo.b LIKE 'station') and x = y and z in ((1, 2)) and a = b and GROUP BY , ORDER BY x x1 x2 y asc, max(y) desc x z desc"), [
6f30911f	815	[
	816	"-LITERAL",
	817	[
	818	"META"
	819	]
	820	],
6f2a5b66	821	[
	822	"SELECT",
	823	[
	824	[
1ec9b9e3	825	"-MISC",
6f2a5b66	826	[
	827	[
	828	"-LITERAL",
	829	[
	830	"*"
	831	]
1ec9b9e3	832	],
	833	[
	834	"-LITERAL",
	835	[
	836	"*"
	837	]
6f2a5b66	838	]
	839	]
	840	]
	841	]
	842	],
	843	[
	844	"FROM",
	845	[
	846	[
	847	"-MISC",
	848	[
	849	[
	850	"-PAREN",
	851	[
	852	[
	853	"SELECT",
	854	[
	855	[
	856	"-LIST",
	857	[
	858	[
	859	"-LITERAL",
	860	[
	861	"*"
	862	]
	863	],
	864	[]
	865	]
	866	]
	867	]
	868	],
	869	[
	870	"FROM",
	871	[
	872	[
	873	"-MISC",
	874	[
	875	[
	876	"-LITERAL",
	877	[
	878	"foobar"
	879	]
	880	],
	881	[
	882	"-LITERAL",
	883	[
	884	"baz"
	885	]
	886	],
	887	[
	888	"-LITERAL",
	889	[
	890	"buzz"
	891	]
	892	]
	893	]
	894	]
	895	]
	896	]
	897	]
	898	],
	899	[
	900	"-LITERAL",
	901	[
902	"foo"
903	]
904	],
905	[
906	"-LITERAL",
907	[
908	"bar"
909	]
910	]
911	]
912	]
913	]
914	],
915	[
916	"WHERE",
917	[
918	[
919	"AND",
920	[
921	[
922	"NOT",
923	[]
924	],
925	[
926	"NOT",
927	[]
928	],
929	[
930	"NOT EXISTS",
931	[
932	[
933	"-PAREN",
934	[
935	[
936	"SELECT",
937	[
938	[
939	"-LIST",
940	[
941	[
942	"-LITERAL",
943	[
944	"'cr"
945	]
946	],
947	[
948	"-LITERAL",
949	[
950	"ap'"
951	]
952	]
953	]
954	]
955	]
956	]
957	]
958	]
959	]
960	],
961	[
b4085a1a	962	"-MISC",
6f2a5b66	963	[
6f2a5b66	964	[
b4085a1a	965	"=",
6f2a5b66	966	[
6f30911f	967	[
b4085a1a	968	"-LITERAL",
6f30911f	969	[
b4085a1a	970	"foo.a"
6f30911f	971	]
	972	],
	973	[
b4085a1a	974	"-PLACEHOLDER",
6f30911f	975	[
b4085a1a	976	"?"
6f30911f	977	]
b4085a1a	978	],
6f30911f	979	],
e2a120ce	980	],
e2a120ce	981	[
b4085a1a	982	"-LITERAL",
	983	[
	984	"STUFF"
	985	]
	986	],
	987	],
	988	],
	989	[
	990	'moar',
	991	[
	992	[
	993	'-PAREN',
e2a120ce	994	[
e2a120ce	995	[
b4085a1a	996	'-LITERAL',
e2a120ce	997	[
b4085a1a	998	'stuff'
e2a120ce	999	]
b4085a1a	1000	]
e2a120ce	1001	]
6f2a5b66	1002	]
	1003	]
	1004	],
	1005	[
	1006	"NOT",
	1007	[
	1008	[
	1009	"-PAREN",
	1010	[
	1011	[
	1012	"LIKE",
	1013	[
	1014	[
	1015	"-LITERAL",
	1016	[
	1017	"foo.b"
	1018	]
	1019	],
	1020	[
	1021	"-LITERAL",
	1022	[
	1023	"'station'"
	1024	]
	1025	]
	1026	]
	1027	]
	1028	]
	1029	]
	1030	]
	1031	],
	1032	[
	1033	"=",
	1034	[
	1035	[
	1036	"-LITERAL",
	1037	[
	1038	"x"
	1039	]
	1040	],
	1041	[
	1042	"-LITERAL",
	1043	[
	1044	"y"
	1045	]
	1046	]
	1047	]
	1048	],
	1049	[
7d273452	1050	'IN',
	1051	[
	1052	[
	1053	'-LITERAL',
	1054	[
	1055	'z',
	1056	],
	1057	],
	1058	[
	1059	'-PAREN',
	1060	[
	1061	[
	1062	'-PAREN',
	1063	[
	1064	[
	1065	'-LIST',
	1066	[
	1067	[
	1068	'-LITERAL',
	1069	[
	1070	'1'
	1071	]
	1072	],
	1073	[
	1074	'-LITERAL',
	1075	[
	1076	'2'
	1077	]
	1078	],
	1079	],
	1080	],
	1081	],
	1082	],
	1083	],
	1084	],
	1085	],
	1086	],
	1087	[
6f2a5b66	1088	"=",
	1089	[
	1090	[
	1091	"-LITERAL",
	1092	[
	1093	"a"
	1094	]
	1095	],
	1096	[
	1097	"-LITERAL",
	1098	[
	1099	"b"
	1100	]
	1101	]
	1102	]
	1103	]
	1104	]
	1105	]
	1106	]
	1107	],
	1108	[
	1109	"GROUP BY",
	1110	[
	1111	[
	1112	"-LIST",
	1113	[
	1114	[],
	1115	[]
	1116	]
	1117	]
	1118	]
	1119	],
	1120	[
	1121	"ORDER BY",
	1122	[
	1123	[
	1124	"-LIST",
	1125	[
	1126	[
1ec9b9e3	1127	"-ASC",
6f2a5b66	1128	[
	1129	[
	1130	"-MISC",
	1131	[
	1132	[
1ec9b9e3	1133	"-LITERAL",
6f2a5b66	1134	[
1ec9b9e3	1135	"x"
6f2a5b66	1136	]
	1137	],
	1138	[
	1139	"-LITERAL",
	1140	[
1ec9b9e3	1141	"x1"
6f2a5b66	1142	]
	1143	],
	1144	[
	1145	"-LITERAL",
	1146	[
1ec9b9e3	1147	"x2"
6f2a5b66	1148	]
	1149	],
	1150	[
	1151	"-LITERAL",
	1152	[
1ec9b9e3	1153	"y"
	1154	]
	1155	]
	1156	]
	1157	],
	1158	],
	1159	],
	1160	[
1ec9b9e3	1161	"-DESC",
	1162	[
	1163	[
	1164	"-MISC",
	1165	[
	1166	[
c84a4321	1167	"-DESC",
1ec9b9e3	1168	[
1ec9b9e3	1169	[
c84a4321	1170	"max",
1ec9b9e3	1171	[
1ec9b9e3	1172	[
6f30911f	1173	"-PAREN",
1ec9b9e3	1174	[
6f30911f	1175	[
	1176	"-LITERAL",
	1177	[
	1178	"y"
	1179	]
	1180	]
1ec9b9e3	1181	]
1ec9b9e3	1182	]
c84a4321	1183	],
c84a4321	1184	]
1ec9b9e3	1185	]
	1186	],
	1187	[
	1188	"-LITERAL",
	1189	[
c84a4321	1190	"x"
1ec9b9e3	1191	]
c84a4321	1192	],
	1193	[
	1194	"-LITERAL",
	1195	[
	1196	"z"
6f2a5b66	1197	]
	1198	]
	1199	]
	1200	]
	1201	]
	1202	]
	1203	]
	1204	]
	1205	]
	1206	]
	1207	], 'Deliberately malformed SQL parsed "correctly"');
	1208
	1209
0f9a26cb	1210	# test for recursion warnings on huge selectors
ad591616	1211	my @lst = ('AA' .. 'zz');
ad591616	1212	#@lst = ('AAA' .. 'zzz'); # if you really want to wait a while
0f9a26cb	1213	warnings_are {
ad591616	1214	my $sql = sprintf 'SELECT %s FROM foo', join (', ', (map { qq\|( "$_" )\| } @lst), (map { qq\|"$_"\| } @lst), (map { qq\|"$_", ( "$_" )\| } @lst) );
0f9a26cb	1215	my $tree = $sqlat->parse($sql);
	1216
	1217	is_deeply( $tree, [
	1218	[
	1219	"SELECT",
	1220	[
	1221	[
6f2a5b66	1222	"-LIST",
0f9a26cb	1223	[
ad591616	1224	(map { [ -PAREN => [ [ -LITERAL => [ qq\|"$_"\| ] ] ] ] } @lst),
6f2a5b66	1225	(map { [ -LITERAL => [ qq\|"$_"\| ] ] } @lst),
ad591616	1226	(map { [ -LITERAL => [ qq\|"$_"\| ] ], [ -PAREN => [ [ -LITERAL => [ qq\|"$_"\| ] ] ] ] } @lst),
0f9a26cb	1227	]
	1228	]
	1229	]
	1230	],
	1231	[
	1232	"FROM",
	1233	[
	1234	[
6f2a5b66	1235	"-LITERAL",
0f9a26cb	1236	[
	1237	"foo"
	1238	]
	1239	]
	1240	]
	1241	]
	1242	], 'long list parsed correctly');
	1243
	1244	is( $sqlat->unparse($tree), $sql, 'roundtrip ok');
	1245	} [], 'no recursion warnings on insane SQL';
	1246
3be357b0	1247	done_testing;